MySQL stored procedures, storage functions, and Process Control

Tag:procedure    trigger    create   mysql   

                    Stored procedure One, basic syntax create PROCEDURE sp_name ([proc_parameter[,...]) [Characteristic ...] Routine_body Beginendsp_name represents the name of the stored procedure proc_parameter the stored procedure parameter example table [in the Out INOUT] three parts consisting of a parameter that is passed in, where out represents the outgoing parameter where the INOUT The parameter routine_body, which is passed in but eventually passed back, is the contents of the SQL code (for each row similar to the trigger) begin: End flag start and end of SQL code two, an example of in-out inpout the in parameter example delimiter &&create procedure alvin1 (in p_in int) #  Set the incoming parameter type and variable Beginselect p_in;   #查询第一次传入的参数值set p_in=2;  #内部重新赋值给p_in变量select p_in;  #赋值后在此查询end &&delimiter; set @p_in = 1; #开始传入参数1call alvin1 (@p_in); #调用存储过程, view and compare output values out parameters Example delimiter &&create procedure alvin2 (out p_out int) beginselect P_out;set p_out=2; Select P_out;end &&delimiter; set @p_out = 1; After the parameter 1 is passed in, call Alvin2 (@p_out) is called after. Does it show up like in? Or is it invalid? INOUT parameter Example delimiter &&create procedure alvin3 (INOUT p_inout int) beginselect P_inout;set p_inout=2;select p_ Inout;end &&delimiter; iii. Examples of requirements: Create a stored procedure that requires (return MySQL version, user's database, user name) delimiter &&create procedure Zy1 (out GetvErsion varchar (+), out userversion varchar (+), out userdatabase varchar (+), out userconnection int) reads SQL Databeginselect version () into Getversion;select user () to userversion;select database () into Userdatabase;select CONNECTION_ID () into userconnection;end &&delimiter; demand two, statistics vendors vend_id number total how many? out Zycount intSELECT COUNT (*) into Zycount from vendors#==================================================== storage function C reate function Sp_name ([func_parameter[,.....]]) return type[characteristic ...] Routine_bodybegin...end the name of the Sp_name store function func_parameter function parameter list return type specifies the type of the parameter returned routine_body SQL code content begin: End flag start and end of SQL code Note: Unlike stored procedures, 1, parameter only input type 2, return result value to caller example: Delimiter &&create function alvin11 (bb_id int) returns varchar (beginreturn) (select Vend_name from vendors where vend_id=bb_id); end &&delimiter; Practice requirements: Write a storage function, When you ask for access to cust_id, return to order_date. The Value table for this field is named: orders#==================================================== Process Control ( 1) SaveHow to use the IF statement in a stored procedure delimiter &&create procedure zyif (in AA int,out BB int) beginif aa>20 thenset Bb=30;elseif aa=20the Nset bb=20;elseset bb=15;end if;end &&delimiter;(2) Stored procedure case Usage delimiter &&create procedure zy_case (in AA int,inout BB int) begincasewhen aa=20 then set Bb=20;when aa>20 and aa<=50 then set Bb=30;when aa>51 then set b B=60;else set Bb=15;end case;end &&delimiter;(3) While loop is used, insert 10,000 data to create a table creation table ZYBB (user_id int,name varchar (10)); Query OK, 0 rows affected (0.10 sec) delimiter &&create procedure Zy_while () begindeclare count int default 0;while Count < 100000 doinsert into ZYBB (user_id,name) VALUES (count, ' Aaa1 '); set Count=count + 1;end while;end && delimiter; call Zy_while () calls the stored procedure #=========================================================== (1) Call the stored procedure call+ stored procedure name + parameters such as: Call Alvin_name (@p_inout) (2) Query Results Select @p_inout (3) query stored procedure Show procedure Status\g; (4) query A stored procedure detailed show create Procedure Alvin1\g; (5) query store function show function status\g;6) Query a detailed storage function show Create function alvin10\g; (7) Delete the stored procedure drop procedure alvin1; (8) Delete the stored functions drop function alvin1;