mysql 函數和預存程序的學習

標籤：

#建立儲存子程式需要CREATE ROUTINE許可權。#· 提醒或移除儲存子程式需要ALTER ROUTINE許可權。這個許可權自動授予子程式的建立者。#· 執行子程式需要EXECUTE許可權。然而，這個許可權自動授予子程式的建立者。同樣，子程式預設的SQL SECURITY 特徵是DEFINER，它允許用該子程式訪問資料庫的使用者與執行子程式聯絡到一起#-------------------------------------------------------------------------------------------------------##mysql函數即使重啟mysqld服務後函數依然會存在，只存在指定的資料庫，不會跨資料庫drop database if exists test_1;create database test_1;use test_1;drop table if  exists test1;#create table test1 (user_id int(11) auto_increment,user_sn int() not null,primary key(user_id));select  year(now()),month(now()),dayofmonth(now());  #年月日select  concat(year(now()),month(now()),dayofmonth(now()));  #年月日create table userorder (userorder_id int(11) auto_increment,primary key(userorder_id),order_sn bigint(15) not null);#使用者訂單表#sql自訂函數的總結delimiter $$ #定義結束符  $$drop function if exists hello;  #判斷hello函數是否存在,如果存在則刪除create function test_1.hello (name char(20),age int(2))  returns  char(225)    #sql 顧名思義 是建立函數的意思 ， 注意：語言是強型別語言因此要聲明參數的類型   和傳回值的類型    begin                                                   #函數體的開始    #set @i=0;                                              #聲明一個全域變數@i  sql也有局部和全域變數                                 declare greeting char(20);                              #聲明一個局部變數  注意：函數內部不能同時有局部變數和全域變數的存在    declare num      int(20);    declare str      char(20);    declare restr    char(225);    declare max      int(20);    if  hour(now()) < 12 then        set greeting =‘早上好‘;                                #set 一般用來賦值   賦值給局部變數 全域變數一樣    elseif hour(now()) >12 then        set greeting =‘下午好‘;    end if;                                                 #end if這裡一定要分開    if (age < 18)    then                                    #判斷條件可以加上括弧        set str=‘未成年‘;        else        set str=‘成年‘;    end if;    set  num=0;    while num < age do        set num=age+1;    end while;                                              #注意分開 end while    set max =0;    w:while age < 100 do        set age=age+1;                   if age = 4  then            #leave w;                                       #leave  相當於break 跳出迴圈    w 是指明關鍵字leave跳出那個迴圈的            iterate w;                                      #iterate  相當於continue 跳過迴圈            end if;        set max=max+1;    end while w;    #select concat(name,greeting,‘你的幸運數字是‘) into restr;    select concat(name,greeting,‘你的幸運數字是‘,max,str) into restr;    return restr;    end$$delimiter ;select test_1.hello(‘huangyanxiong‘,12); #函數調用    #-------------------------------------------------------------------------------------------------------------------------------------##建立一個自動產生訂單序號的函數20140103001#作用：可以減少串連資料庫的次數，減少資料庫的負擔，加快程式的的運行drop function if exists create_sn;delimiter $$create function test_1.create_sn() returns bigint(15)   #編寫程式時要注意資料類型    begin    declare order_sn bigint(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(year(prev),month(prev),dayofmonth(prev)) into prevdatetime;    select right(prev,4) into  sn;    select concat(year(now()),month(now()),dayofmonth(now())) into nowdate;    #if isnull(prev) &&  nowdate = prevdatetime then mysql不支援這樣寫    if isnull(prev)   then        select concat(nowdate,‘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();#---------------------------------------------------------------------------------------------------#產生隨機字串，用於測試資料庫drop function if exists  randstr;delimiter $$create function  test_1.randstr(num int(11))  returns char(255)     #為了容易區分那個函數或者預存程序是那個資料庫的，可以在函數名中加上資料庫首碼test_randstr;    begin    declare str char(255)  default ‘q1we23r4t5y6u7i8o9p0asdfghjklzxcvbnm‘;    declare nums int(11);    declare returnstr char(255);                    #SQL變數名不能和列名一樣    declare i int(11) default  0;                   #在聲明變數時一定要在begin語句之後，除begin外的任何語句之前    select floor(truncate(rand(),1)*36)+1 into nums;   #加1時為了防止產生隨機數產生0的情況    select substring(str,nums,1)  into returnstr;    #declare i int(11) default  0;                  #在聲明變數時一定要在begin語句之後，除begin外的任何語句之前，像這個語句是不允許的    while  i <num do    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%‘; #查看函數的狀態 包括：函數所屬資料庫，函數名，類型，建立時間，建立者，安全類型 注釋 ，資料庫字元集，用戶端字元集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 into ceshistr;  #ERROR 1222 (21000): The used SELECT statements have a different number of columns    select  order_sn,userorder_id into  ceshistr1,ceshistr2 from userorder limit 1 ;  #在mysql中一個列的資料必須佔用一個變數，否則會出現上面的錯誤    select concat(ceshistr1,ceshistr2) into ceshistr;    #select * from  userorder;          #儲存函數的限制：不能再儲存函數中返回整個表的資料ERROR 1415 (0A000): Not allowed to return a result set from a function    return ceshistr;                    #而預存程序可以返回整張表的資料    end    $$    delimiter ;#-------------------------------------------------------------------------------------------------------------------------------------------#drop procedure if exists simpleproc;delimiter $$    CREATE PROCEDURE simpleproc (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 is 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‘;                              #在預存程序中聲明全域變數並賦值，   注意：函數內不能同時有局部變數和全域變數    #select * from userorder;    select @color as colors;                            #一般採用這種方式輸出到終端    end    $$    delimiter ;    call test2();#--------------------------------------------------------------------------------------##預存程序傳遞參數drop procedure  if exists test3;delimiter $$create procedure test3(in username char(50))    begin    select username as user_name;    end    $$    delimiter ;    call test3(‘huangyanxiong‘);drop procedure if exists test4;delimiter $$#----------------------------------------------------------------------------------#create procedure test4(username char(50))    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 only allowed in a FUNCTION  return 語句只能在函數中使用    end;    $$delimiter ;call test4(‘huangyanxiong‘);#------------------------------------------------------------------------------------#drop procedure if exists test5;delimiter $$create procedure test5 (username char(50))  #預設使用in    begin    set @age=12;    select username as usernames,@age as age;   #使用同一張表返回    end$$delimiter ;call test5(‘huangyanxiong‘);#-------------------------------------------------------------------------------------##把函數改變為預存程序很簡單，把函數改改就可以#把上面的訂單序號改為預存程序drop procedure if exists create_sn;delimiter $$create procedure create_sn()    begin    declare order_sn bigint(15);    declare prev 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(year(now()),month(now()),dayofmonth(now())) into nowdate;           #if isnull(prev) &&  nowdate = prevdatetime then mysql不支援這樣寫    if isnull(prev)   then        select concat(nowdate,‘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();#-------------------------------------------------------------------------------##產生隨機字串，用於測試資料庫drop procedure if exists  randstr;delimiter $$create procedure  randstr(num int(11))     #為了容易區分那個函數或者預存程序是那個資料庫的，可以在函數名中加上資料庫首碼test_randstr;    begin    declare str char(255)  default ‘q1we23r4t5y6u7i8o9p0asdfghjklzxcvbnm‘;    declare nums int(11);    declare returnstr char(255);                    #SQL變數名不能和列名一樣    declare i int(11) default  0;                   #在聲明變數時一定要在begin語句之後，除begin外的任何語句之前    select floor(truncate(rand(),1)*36)+1 into nums;   #加1時為了防止產生隨機數產生0的情況    select substring(str,nums,1)  into returnstr;    #declare i int(11) default  0;                  #在聲明變數時一定要在begin語句之後，除begin外的任何語句之前，像這個語句是不允許的    while  i <num do    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);#----------------------------------------------------------------------#

mysql 函數和預存程序的學習