SQL/MySQL/Procedure Function/Call
Содержание
Call another function
<source lang="sql">
mysql> mysql> delimiter $$ mysql> mysql> CREATE FUNCTION isodd(input_number int)
-> RETURNS int -> BEGIN -> DECLARE v_isodd INT; -> -> IF MOD(input_number,2)=0 THEN -> SET v_isodd=FALSE; -> ELSE -> SET v_isodd=TRUE; -> END IF; -> -> RETURN(v_isodd); -> -> END$$
Query OK, 0 rows affected (0.01 sec) mysql> mysql> CREATE PROCEDURE myProc(aNumber int)
-> Begin -> IF (isodd(aNumber)) THEN -> SELECT " is odd"; -> ELSE -> SELECT " is even"; -> END IF; -> END$$
Query OK, 0 rows affected (0.02 sec) mysql> mysql> delimiter ; mysql> mysql> mysql> call myProc(2); +----------+ | is even | +----------+ | is even | +----------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.01 sec) mysql> mysql> call myProc(1); +---------+ | is odd | +---------+ | is odd | +---------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> mysql> call myProc(4); +----------+ | is even | +----------+ | is even | +----------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> mysql> mysql> drop procedure myProc; Query OK, 0 rows affected (0.00 sec) mysql> drop function isodd; Query OK, 0 rows affected (0.00 sec) mysql> mysql>
</source>
Call another procedure
<source lang="sql">
mysql> mysql> delimiter $$ mysql> mysql> CREATE PROCEDURE myProc()
-> BEGIN -> -> SELECT "Alpha release of MySQL"; -> END$$
Query OK, 0 rows affected (0.00 sec) mysql> mysql> CREATE PROCEDURE myProc1()
-> BEGIN -> -> call myProc(); -> END$$
Query OK, 0 rows affected (0.00 sec) mysql> mysql> delimiter ; mysql> mysql> mysql> call myProc1(); +------------------------+ | Alpha release of MySQL | +------------------------+ | Alpha release of MySQL | +------------------------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> mysql> drop procedure myProc; Query OK, 0 rows affected (0.02 sec) mysql> drop procedure myProc1; Query OK, 0 rows affected (0.00 sec) mysql> mysql>
</source>
Call another procedure to pass the error code
<source lang="sql">
mysql> mysql> delimiter $$ mysql> mysql> CREATE PROCEDURE myProc()
-> MODIFIES SQL DATA -> BEGIN -> DECLARE l_status VARCHAR(20); -> -> CALL myProc1(l_status); -> IF l_status="Duplicate Entry" THEN -> SELECT CONCAT("Warning: using existing definition for location ") AS warning; -> END IF; -> END$$
Query OK, 0 rows affected (0.00 sec) mysql> mysql> CREATE PROCEDURE myProc1(OUT out_status VARCHAR(30))
-> BEGIN -> set out_status = "Duplicate Entry"; -> END$$
Query OK, 0 rows affected (0.00 sec) mysql> mysql> mysql> delimiter ; mysql> call myProc(); +--------------------------------------------------+ | warning | +--------------------------------------------------+ | Warning: using existing definition for location | +--------------------------------------------------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.01 sec) mysql> drop procedure myProc; Query OK, 0 rows affected (0.00 sec) mysql> drop procedure myProc1; Query OK, 0 rows affected (0.00 sec) mysql> mysql>
</source>
Call another procedure with parameter
<source lang="sql">
mysql> mysql> delimiter $$ mysql> mysql> CREATE PROCEDURE myProc(id int)
-> BEGIN -> -> SELECT "in proc"+ id; -> END$$
Query OK, 0 rows affected (0.00 sec) mysql> mysql> CREATE PROCEDURE myProc1()
-> BEGIN -> -> call myProc(1); -> END$$
Query OK, 0 rows affected (0.00 sec) mysql> mysql> delimiter ; mysql> mysql> call myProc1(); +---------------+ | "in proc"+ id | +---------------+ | 1 | +---------------+ 1 row in set (0.00 sec) Query OK, 0 rows affected, 1 warning (0.02 sec) mysql> mysql> drop procedure myProc; Query OK, 0 rows affected (0.00 sec) mysql> drop procedure myProc1; Query OK, 0 rows affected (0.00 sec) mysql>
</source>