в 13:46, 26 мая 2010

2010-05-26T13:46:00Z

Admin: 1 версия

2010-05-26T10:17:22Z

1 версия

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

==Assign value to a variable with set command==



<source lang="sql">

mysql>
mysql>
mysql> delimiter $$
mysql> CREATE PROCEDURE myProc()
-> BEGIN
-> DECLARE my_integer INT; /* 32-bit integer */
->
-> set my_integer = 12;
->
-> select "my_integer = " + 12;
-> END$$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql>
mysql> call myProc();
+----------------------+
| "my_integer = " + 12 |
+----------------------+
| 12 |
+----------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql>
mysql> drop procedure myProc;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
</source>

==Concatenate string with integer==



<source lang="sql">

mysql>
mysql> delimiter $$
mysql> CREATE PROCEDURE myProc()
-> BEGIN
->
-> DECLARE a INT;
-> DECLARE b VARCHAR(20);
-> DECLARE c INT;
->
-> SET a=99;
-> SET b="AAA";
-> SET c=CONCAT(a," ",b);
-> SELECT c;
-> END$$
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> delimiter ;
mysql>
mysql> call myProc();
+------+
| c |
+------+
| 99 |
+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql>
mysql> drop procedure myProc;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
</source>

==Math operators==



<source lang="sql">

mysql>
mysql> delimiter $$
mysql>
mysql> CREATE PROCEDURE myProc()
-> BEGIN
-> DECLARE a INT DEFAULT 2;
-> DECLARE b INT DEFAULT 3;
-> DECLARE c FLOAT;
->
-> SET c=a+b; SELECT "a+b=",c;
-> SET c=a/b; SELECT "a/b=",c;
-> SET c=a*b; SELECT "a*b=",c;
->
-> END$$
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> delimiter ;
mysql>
mysql> call myProc();
+------+------+
| a+b= | c |
+------+------+
| a+b= | 5 |
+------+------+
1 row in set (0.00 sec)
+------+----------+
| a/b= | c |
+------+----------+
| a/b= | 0.666667 |
+------+----------+
1 row in set (0.02 sec)
+------+------+
| a*b= | c |
+------+------+
| a*b= | 6 |
+------+------+
1 row in set (0.02 sec)
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql> drop procedure myProc;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
</source>

==Using Set to declare variable in a procedure==



<source lang="sql">

mysql>
mysql> delimiter $$
mysql>
mysql> CREATE PROCEDURE execute_immediate(in_sql varchar(4000))
-> BEGIN
->
-> SET @tmp_sql=in_sql;
-> PREPARE s1 FROM @tmp_sql;
-> EXECUTE s1;
-> DEALLOCATE PREPARE s1;
->
-> END$$
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> delimiter ;
mysql> call execute_immediate("select 1+1");
+-----+
| 1+1 |
+-----+
| 2 |
+-----+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.02 sec)
mysql> drop procedure execute_immediate;
Query OK, 0 rows affected (0.00 sec)
</source>

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

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

в 13:46, 26 мая 2010

Admin: 1 версия