Learning about MySQL functions and stored procedures

#创建存储子程序需要CREATE routine permissions. #· Alter routine permission is required to remind or remove a stored subroutine. This permission automatically grants the creator of the subroutine. #· Execute permission is required for executing the subroutine. However, this permission is automatically granted to the creator of the subroutine. Similarly, the default SQL SECURITY feature of the subroutine is Definer, which allows the user accessing the database with the subroutine to associate with the executing subroutine #--------------------------------------------------- ----------------------------------------------------# #mysql函数即使重启mysqld服务后函数依然会存在, only the specified database is present and will not be drop across the database If exists test_1;create database Test_1;use test_1;drop table if exists test1; #create table test1 (user_id int) auto_i  NCREMENT,USER_SN int () not null,primary key (user_id)), select Year (now ()), Month (now ()), and DayOfMonth (now ());  #年月日select concat (now ()), Month (now ()), DayOfMonth (now ())); #年月日create table Userorder (userorder_id int) auto_increment,primary key (userorder_id), order_sn bigint (not NULL)  ; #用户订单表 #sql The summary of the custom function delimiter $$ #定义结束符 $ $drop function if exists hello; #判断hello函数是否存在, delete the CREATE function Test_1.hello if it exists (name char (), age int (2)) returns char (225) #sql as the name implies, the meaning of creating functions,              Note: The language is strongly typed so the type of the argument and the type of the return value are declared begin                                     #函数体的开始 #set @i=0;                              #声明一个全局变量 @i SQL also has local and global variables declare greeting char (20);    #声明一个局部变量 Note: The function cannot have both local variables and global variables present declare num INT (20);    Declare str char (20);    Declare restr char (225);    DECLARE max Int (20);                                If hour (now ()) < greeting set = ' Good morning ';    #set is commonly used to assign values to local variable global variables like ElseIf hour (now ()) >12 then Set greeting = ' Good afternoon ';                                                 End If;        #end If you must separate if (age <) then #判断条件可以加上括号 set str= ' underage ';    else set str= ' adult ';    End If;    Set num=0;    While Num < age do set num=age+1;                                              End while;    #注意分开 End While set max = 0;                   W:while < do set age=age+1; If age = 4 Then #leave W;                                      #leave equivalent to break out of the loop W is the iterate w that indicates the keyword leave jumps out of that loop;        #iterate equivalent to continue skipping the loop end if;    Set max=max+1;    End While W;    #select concat (name,greeting, ' Your Lucky number is ') into restr;    Select Concat (name,greeting, ' Your Lucky number is ', max,str) into restr;    return restr; end$ $delimiter; Select Test_1.hello (' Huangyanxiong ', 12); #函数调用 #--------------------------------------------------------------------------------------------------------- ----------------------------# #创建一个自动生成订单序号的函数20140103001 # role: You can reduce the number of connections to the database, reduce the burden on the database, speed up the operation of the program drop function if exists Create_sn;delimiter $ $create Function test_1.create_sn () returns bigint (#编写程序时要注意数据类型) BEGIN declare ORDER_SN b    Igint (15);    DECLARE prev bigint (15);    DECLARE prevdatetime bigint (15);    DECLARE SN bigint (15);    DECLARE nowdate bigint (15); Select Order_sn from Userorder ORDER BY userorder_id DESC limit 1 into prev;    #赋值prev Select Concat (prev), month (prev), DayOfMonth (prev)) into Prevdatetime;    Select Right (prev,4) into SN;    Select Concat (now ()), Month (now ()), DayOfMonth (now ())) to Nowdate; #if isnull (prev) && nowdate = Prevdatetime then MySQL does not support this write if IsNull (prev) then select Concat (nowdat            E, ' 0001 ') into ORDER_SN;        return ORDER_SN;            ElseIf nowdate = Prevdatetime then select concat (nowdate, ' 0001 ') into ORDER_SN;        return ORDER_SN;        else Select Concat (Prevdatetime, (sn+1)) into ORDER_SN;        return ORDER_SN;    End If; End $$ delimiter; select CREATE_SN (); #---------------------------------------------------------------------------- -----------------------#产生随机字符串 for testing the database drop function if exists Randstr;delimiter $ $create function test_1.randstr (num    Int (one)) returns char (255) #为了容易区分那个函数或者存储过程是那个数据库的, you can add the database prefix test_randstr in the function name; Begin declare Str char (255) Default 'Q1we23r4t5y6u7i8o9p0asdfghjklzxcvbnm ';    DECLARE nums int (11);                    Declare returnstr char (255);                   #SQL变量名不能和列名一样 declare i int (one) default 0;   #在声明变量时一定要在begin语句之后, select Floor before any statements except begin (Truncate (rand (), 1) *36) +1 into nums;    #加1时为了防止产生随机数生成0的情况 Select substring (str,nums,1) into returnstr;                  #declare I int (one) default 0;    #在声明变量时一定要在begin语句之后, except for any statements outside of begin, like this statement is not allowed while I <num does select floor (Truncate (rand (), 1) *36) +1 into nums;    Select Concat (substring (str,nums,1), returnstr) into returnstr;    #set returnstr=concat (substring (str,nums,1), returnstr);    Set i=i+1;    End while;    return returnstr; End $$ delimiter; #---------------------------------------------------------------------------------------------- -------------------------#show function status like '%rand% ';  #查看函数的状态 include: function database, function name, type, creation time, creator, security type comment, database character set, client character set show procedure status like '%procedure_name '; #同上 #------------------------------------------------------------------------------------------------------------------------------#insert into Userorder values (NULL,TEST_1.CREATE_SN ()); #---------------------------------------------------------------- ---------------------------------------------------------------------#drop function if exists ceshi;delimiter $$    Create function Ceshi () returns char (255) BEGIN declare CESHISTR1 char (255);    Declare ceshistr2 char (255);    Declare ceshistr char (255);  #select order_sn,userorder_id from Userorder limit 1 to Ceshistr;  #ERROR 1222 (21000): The used SELECT statements has a different number of columns SELECT order_sn,userorder_id into  CESHISTR1,CESHISTR2 from Userorder limit 1;    #在mysql中一个列的数据必须占用一个变量, otherwise the above error will occur select CONCAT (CESHISTR1,CESHISTR2) into ceshistr;          #select * from Userorder;                    #存储函数的限制: can no longer store data returned from the entire table in the function error 1415 (0a000): Not allowed to return a result set from a function return ceshistr; #而存储过程可以返回整张表The data end $$ delimiter; #------------------------------------------------------------------------------------------ -------------------------------------------------#drop procedure if exists simpleproc;delimiter $$ CREATE procedure si           Mpleproc (out param1 int.) BEGIN SELECT * from Userorder;             #而存储过程可以返回整张表的数据 END $$ delimiter; call Simpleproc (); #调用存储过程 #---------------------------------------------------------------------------------------drop procedure if exists pr_param_in;    Delimiter $ $create Procedure pr_param_in (in ID int) BEGIN IF (ID was not NULL) then Set id = id + 1;    End If;     Select ID as Id_inner; end;$ $delimiter; #--------------------------------------------------------------------------------------------- -------------------------------------------------------##------------------------------------------------------ ----------------------------------------------------------------# #第一个自己的存储过程 drop procedure if exists test;    #判断一个存储过程是否存在存在则删除 delimiter $$ CREATE PROCEDURE test () begin SELECT ' Hello world! ' as HelloWorld;    End    $$ delimiter; Call Test (); #-------------------------------------------------------------------------------------------------- ------# #存储过程学习声明变量drop Procedure if exists test2;delimiter $ $create Procedure test2 () BEGIN declare str char (255)  Default ' Huangyanxiong ';                              #在存储过程声明局部变量并赋值 Set @color = ' red ';    #在存储过程中声明全局变量并赋值, Note: The function cannot have both local and global variables #select * from Userorder;                            Select @color as Colors;    #一般采用这种方式输出到终端 End $$ delimiter; Call Test2 (); #--------------------------------------------------------------------------------------# # Stored procedure pass parameter drop procedure if exists Test3;delimiter $ $create procedure test3 (in username char ()) BEGIN select Userna    me as user_name;    End $$ delimiter; Call Test3 (' Huangyanxiong ');d ROP procedure if exists Test4;delimiter $$#----------------------------------------------------------------------------------#create procedure test4 (    Username char (+)) BEGIN declare str char (50);    Select Concat (username, ' xxxxxx ') into str;                       Select Str As String;    #设置别名返回 #select ' DDS ' as D; #return STRs;    # ERROR 1313 (42000): Return is just allowed in a function RETURN statement can only use end in functions; $ $delimiter; call test4 (' Huangyanxiong '); #----------------------------------------------------------------------    --------------#drop procedure if exists test5;delimiter $ $create Procedure test5 (username char) #默认使用in begin    Set @age = 12;   Select username as usernames, @age as age; #使用同一张表返回 end$ $delimiter; call Test5 (' Huangyanxiong '); #---------------------------------------------------------- ---------------------------# #把函数改变为存储过程很简单, change the function to # change the order number above to the stored procedure drop procedure if exists create_sn;delimiter $$    CREATE PROCEDURE CREATE_SN () BEGIN DECLARE ORDER_SN bigint (15); DeclarePrev bigint (15);    DECLARE prevdatetime bigint (15);    DECLARE SN bigint (15);    DECLARE nowdate bigint (15);       Select Order_sn into Prev from Userorder ORDER BY userorder_id desc LIMIT 1;    #赋值prev SELECT prev;    Select Concat (Year (prev), month (prev), DayOfMonth (prev)) into Prevdatetime;    Select Right (prev,4) into SN;           Select Concat (now ()), Month (now ()), DayOfMonth (now ())) to Nowdate; #if isnull (prev) && nowdate = Prevdatetime then MySQL does not support this write if IsNull (prev) then select Concat (nowdat            E, ' 0001 ') into ORDER_SN;        Select Order_sn as ORDERSN;            ElseIf nowdate! = Prevdatetime then select concat (nowdate, ' 00011 ') into ORDER_SN;        Select Order_sn as ORDERSN;        else Select Concat (Prevdatetime, (sn+1)) into ORDER_SN;        Select Order_sn as ORDERSN;    End If;    End $$ delimiter; SELECT * from Userorder;call create_sn (); #-------------------------------------------------------------------------------# #产生随机字符串, used to test the database drop procedure if exists Randstr;delimiter $ $create procedure randstr (num int (11))    #为了容易区分那个函数或者存储过程是那个数据库的, the database prefix test_randstr can be added to the function name;    Begin declare Str char (255) Default ' Q1WE23R4T5Y6U7I8O9P0ASDFGHJKLZXCVBNM ';    DECLARE nums int (11);                    Declare returnstr char (255);                   #SQL变量名不能和列名一样 declare i int (one) default 0;   #在声明变量时一定要在begin语句之后, select Floor before any statements except begin (Truncate (rand (), 1) *36) +1 into nums;    #加1时为了防止产生随机数生成0的情况 Select substring (str,nums,1) into returnstr;                  #declare I int (one) default 0;    #在声明变量时一定要在begin语句之后, except for any statements outside of begin, like this statement is not allowed while I <num does select floor (Truncate (rand (), 1) *36) +1 into nums;    Select Concat (substring (str,nums,1), returnstr) into returnstr;    #set returnstr=concat (substring (str,nums,1), returnstr);    Set i=i+1;    End while;    Select Returnstr as Randstr; End $$ delimiter; call Randstr (5); #----------------------------------------------------------------------# 

Learning about MySQL functions and stored procedures