MAKING, CHANGE, DELETING FUNCTION AND PROCEDURE IN MYSQL

Hai friends… this article is the next from function and procedure… C’mon read this.. previosuly I giving you tutorial about “function and procedure in MySQL”, that is introduction, and now I want giving you method making it (function and procedure)… 

And tutorial for this article :

1. Making Function And Procedure

2. Change Function And Procedure

3. Deleting Function And Procedure

Yes, that is material for today.. Ok.. I want start from number 1 (one)..

1. Making Function And Procedure

For making Function And Procedure we can using the syntax “create”. And this general form :

Example 1 :

CREATE

[ DEFINER = { user | CURRENT_USER } ]

FUNCTION select_sp_name ( [ select_function_paremeter [ , . . . ] ] )

RETURNS type

[ select_characteristic …] routine_body

And this example  procedure :

Example 2 :

DELIMETER $$

CREATE PROCEDURE hardware_count()

BEGIN

SELECT COUNT (*) FROM hardware;

END$$

DELIMETER;

And for working (execute) we are can used “call” and this query :

CALL hardware_count(@hardware)

Then

SELECT @hardware AS ‘hardware_count’ ;

hardware_count

7

2. Change Function And Procedure

Following function to change the function and procedure. This function is used apabaila we want to change the function and procedure without removing it first. And this is the general form :

ALTER [ PROCEDURE | FUNCTION ] select_pf_name

[ characteristic … ]

Example 3 :

DELIMETER $$

ALTER PROCEDURE hardware_count()

BEGIN

SELECT MAX (*) FROM hardware;

END$$

DELIMETER;

3. Deleting Function And Procedure

In accordance with the command (DML) are: insert, update, delete. then the function and procedure there is also a command to remove it. and this general form :

DROP [ PROCEDURE | FUNCTION ] [ IF EXISTS ] select_fp_name

Explanation :

Drop

>> syntax to remove the function and procedure

Procedure

>> if you are going to remove is the procedure the procedure to select the function you do not need to write

Function

>> opposite of the procedure, ie if you are going to remove is a function then select the function you do not need to write the procedure

If Exists

>> to see where the file is there or not

Select_fp_name

>> to select a function or procedure to be deleted

Ok.. just it…

See you later friends