Mysql Storage Programs (stored procedure) Brief notes

1. What is a stored procedure

Mysql> delimiter;mysql> CREATE PROCEDURE Proc1 (out s int)-select COUNT (*) into-s from MySQL.   User -End---//mysql> delimiter;mysql> call Proc1 (@s);mysql> select @s;

2. Create a stored procedure

CREATE PROCEDURE ([[in] |out |inout])

2.1. In values

mysql> delimiter;mysql> CREATE PROCEDURE pin (in p_in int), begin, select p_in;   Set p_in=2;   Select P_in; -End--//mysql> delimiter;mysql> set @p_in =1;mysql> call Pin (@p_in);mysql> select @p_in;

2.2. Out values

Example 1:

mysql> delimiter;mysql> CREATE PROCEDURE pout (out p_out int), begin, select P_out;   Set p_out=2;   Select P_out; -End--//mysql> delimiter;mysql> set @p_out =1;mysql> call pout (@p_out);mysql> Select @p_out

Example 2:

mysql> delimiter;mysql> CREATE PROCEDURE pinout (inout p_out int), begin, select P_inout;   Set p_inout=2;   Select P_inout; -End--//mysql> delimiter;mysql> set @p_inout =1;mysql> Select @p_inoutmysql > Call pinout (@p_inout) ;mysql> Select @p_inout

2.3. Null values

mysql> delimiter;mysql> Create definer= ' root ' @ ' localhost ' procedure ' test1 ' (n1 int), begin;   Set @x=0;   -Repeat set @[email protected]+1;   INSERT into WORLD.T3 values (@x);   -Until @x>n1, end pepeat-end; -//mysql> delimiter;mysql> call Test1 (Ten) mysql> select @p_inout

2. Loop INSERT statement

Mysql> Show Create tables t3\g;mysql> delimiter//mysql> CREATE PROCEDURE t3 (n1 int), begin, set @x=0  ;  -Repeat set @[email protected]+1;  , insert int t3 values (@x);  -Until @x>n1, end; -//mysql> delimiter;mysql> call T3 (TEN);mysql> select * from T3;

3. Variable definition

Declare variable_name [, variable_name ...] datatype [default value];

Datatype:int,float,date,varchar (length)

Decalare l_int int unsigned default 4000000;decalare l_numeric number (8,2) default 9.95;decalare l_datetime datetime DEFAU Lt ' 1999-12-31 23:59:59 ';d ecalare l_varchar varchar (255) Default ' This won't be padded ';

4. Assigning values to variables

Set Variable name = expression Value [, Variable_name = ' expression ...]

Mysql> CREATE PROCEDURE P1 () Set @last_procedure = ' P1 ';mysql> create PROCEDURE P2 () Set Selcet coucat (' Last Procedur E is ', @last_procedure);mysql> call P1 ();mysql> call P2 ();

5. Scope of variables

6. Annotations

--

/*...*/

7. Structured

7.1. If

MySQL > Delimiter//mysql > CREATE PROCEDURE proc2 (in P1 int)-----DECLARE var int;  --Set var=p1+1;  -If Var=1 then inserts into T values (11);  --End If;  -If var=2 then inserts into T values (22);  --Else INSERT INTO T varlues (33);  --End If;  --end; -//mysql > delimiter;

7.2. Case

MySQL > Delimiter//mysql > CREATE PROCEDURE proc3 (in P1 int)-----DECLARE var int;  --Set var=p1+1;  --Case Var--1 then insert into T values (17);  -When 2 then inserts into T values (18);  -Else insert into T values (19);  --end case;  --end; -//mysql > delimiter;

7.3. While

MySQL > Delimiter//mysql > CREATE PROCEDURE proc4 (in P1 int)-----DECLARE var int;  --Set var=0;  --While var<6 do-and insert into T values (VAR);  --Set var=var+1--and end while;  --end; -//mysql > delimiter;

7.4, repeat

MySQL > Delimiter//mysql > CREATE PROCEDURE proc5 (in P1 int)------Declare v int;  --Set v=0;  --Repeat INSERT into T values (v);  --Set v=v+1;  --until v>=5--and end repeat;  --end; -//mysql > delimiter;

7.5. Loop

MySQL > Delimiter//mysql > CREATE PROCEDURE proc6 (in P1 int)------Declare v int;  --Set v=0;  --Loop_lable:loop INSERT into T values (v);  --Set v=v+1;  --If v >=5 then--and leave loop_lable;  --End If;  --end loop;  --end; -//mysql > delimiter;

7.6, iterate

MySQL > Delimiter//mysql > CREATE PROCEDURE Proc7 ()----Declare v int default 0;  --Loop_lable:loop set v=v+1;  --If v<10 then insert loop_lable;  --ElseIf v>20 then leave loop_lable;  --Else Select ' V is between and 20 ';  --End If;  --end loop loop_lable;  --end; -//mysql > delimiter;

8. View stored Procedure Status

MySQL > Show procedure status like ' p% ' \g;mysql > select * from INFORMATION_SCHEMA. Routines, where routine_name = ' P1 ' and->routing_type= ' procedure ' \g;

9. View the contents of the stored procedure

MySQL > Show create PROCEDURE p2\g;

10. Modifying Stored Procedures

Alter procedure proc_name [characteristic ...]

Characteristic:

Comment ' Sting '

| Language SQL

| {contains SQL | no SQL | reads SQL data | Modifies SQL data}

| SQL Security {definer | invoker}

11. Delete stored Procedures

mysql > drop procedure p2;

12. Summary

Currently, MySQL does not support modifying code for existing stored procedures, only the first drop-down rewrite.

Stored procedures can call other stored procedures

The parameters of the stored procedure do not have the same name as the fields in the datasheet

The parameters of the stored procedure can be used in Chinese, regardless of the need to add character set GBK in the definition, for example:

MySQL > CREATE procedure p1 (in U_name varchar () character set gbk,out U_score int)

This article is from the "Pragmatic People" blog, please be sure to keep this source http://tanzhenchao.blog.51cto.com/2420395/1696843

Mysql Storage Programs (stored procedure) Brief notes