в 13:46, 26 мая 2010

2010-05-26T13:46:00Z

Admin: 1 версия

2010-05-26T10:17:01Z

1 версия

Новая страница

==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>

← Предыдущая	Версия 13:46, 26 мая 2010
(нет различий)

SQL/MySQL/Procedure Function/Call - История изменений

в 13:46, 26 мая 2010

Admin: 1 версия