存儲過程 函數 1、create procedure 創建存儲過程 create function 創建函數 2、兩者區別： （1）、函數調用有返回值 （2）、存儲過程調用用call語句，調用函數直接引用函數名+參數即可。 3、in、out、inout三個參數前的關鍵詞只適用于存儲過程，對函數而言所有的參數默認都是輸入參數。 in 用于把數值傳入到存儲過程 out輸出參數把數值傳遞到調用者，初始值為null inout輸入輸出參數，把數據傳入到存儲過程，在存儲過程中修改之后再傳遞到調用者。 Delimiter命令是改變語句的結束符，MySQL默認的結束符為;號，由于procedure和function中的;號并不代表創建的結束，所以要替換成另外的結束符以便表示創建的結束 4、創建存儲過程示例 ~~~ mysql> delimiter // mysql> CREATE PROCEDURE simpleproc (IN param1 int,OUT param2 INT) -> BEGIN -> SELECT COUNT(*) INTO param2 FROM students where sid>param1; -> END// mysql> delimiter ; CALL simpleproc(1, @a); select @a; ~~~ 5、創建函數： ~~~ delimiter // create function hello(s char(20)) returns char(50) deterministic return concat('kkkk,',s,'!');// delimiter ; select hello('fedf'); select * from students where sname=hello(sname); ###字段被變量使用 ~~~ 6、通過 begin...end將多個sql語句包含在一起。 comment子句用來寫入對存儲過程和函數的注釋。 language子句用來表示此存儲過程和函數的創建語言。 存儲過程和函數被標注為deterministic表明當輸入相同的參數是會返回相同的結果，反之如果是not deterministic則表示相同參數不會是相同結果，默認是not deterministic ~~~ 示例： mysql> delimiter // mysql> create function simplefunc(param1 int) -> returns int -> begin -> update students set sex=1 where sid=param1; -> select count(*) into @a from students where sid>param1; -> return @a; -> end; -> // delimiter ; ~~~ #######returns 后跟返回值類型 7、Drop procedure/function語句用來刪除指定名稱的存儲過程或函數 If exists關鍵詞用來避免在刪除一個本身不存在的存儲過程或函數時，MySQL返回錯誤 示例： `drop procedure simpleproc;` 8、Begin…end語句通常出現在存儲過程、函數和觸發器中，其中可以包含一個或多個語句，每個語句用;號隔開 9、標簽label可以加在begin…end語句以及loop, repeat和while語句 語句中通過iterate和leave來控制流程，iterate表示返回指定標簽位置，leave表示跳出標簽 ~~~ delimiter // CREATE PROCEDURE doiterate(IN p1 INT, OUT p2 int) BEGIN label1: LOOP SET p1 = p1 + 1; IF p1 < 10 THEN ITERATE label1; END IF; LEAVE label1; END LOOP label1; set p2=p1; END; // delimiter ; call doiterate (2,@x); ~~~ 10、Declare語句通常用來聲明本地變量、游標、條件或者handler Declare語句只允許出現在begin … end語句中而且必須出現在第一行 Declare的順序也有要求，通常是先聲明本地變量，再是游標，然后是條件和handler 本地變量可以通過declare語句進行聲明 聲明后的變量可以通過select … into var_list進行賦值，或者通過set語句賦值，或者通過定義游標并使用fetch … into var_list賦值 示例： declare var_name [ , var_name] ... type [default value] 使用default指定變量的默認值，如果沒有指定默認值則初始值為NULL。 Type指明該變量的數據類型。 聲明的變量作用范圍為被聲明的begin … end語句塊之間 聲明的變量和被引用的數據表中的字段名要區分開來，否則會報錯 ~~~ delimiter // CREATE PROCEDURE sp1 (v_sid int) BEGIN DECLARE xname VARCHAR(50) DEFAULT 'bob'; DECLARE xsex INT; SELECT sname, gender INTO xname, xsex FROM students WHERE sid=v_sid; SELECT xname,xsex; END; // delimiter ; ~~~ 課程練習： 1、創建一個過程，將男生和女生的學生數據分別存儲到男生表和女生表中。 ~~~ create table students_man like students; create table students_woman like students; delimiter // create procedure sp2 () begin insert into students_man select * from students where gender=0; insert into students_woman select * from students where gender=1; end// delimiter ; ~~~ 2、創建一個過程，將每個學生的課程數，平均成績，及格課程數，非及格課程數都存放在單獨的表中 ~~~ create table temp1(course_id int,avg_score int,cou_score int); delimiter // Create procedure proc2() Begin Insert into temp1 select Sid,count(*),avg(score),sum(case when score>=60 then 1 else 0 end),sum(case when score<60 then 1 else 0 end)From score Group by Sid; End// delimiter ; ~~~ 3、創建一個過程，將學生sid作為輸入參數，結果展示出該學生的課程數和平均成績 delimiter // ~~~ create procedure sp3 (in a1 int) begin select count(course_id) ,avg(score) from score where sid=a1; end// delimiter ; ~~~ 4、創建一個函數，以學生sid作為輸入參數，將該學生的課程數和平均成績存放在單獨的表中，并返回平均成績 ~~~ create table temp2(course_nu int,avg_score int); delimiter // create function f1( a1 int) returns int begin Declare avg_score int； insert into temp2 select count(course_id) ,avg(score) from score where sid=a1 group by sid; select avg(score) into @x from score where sid=a1 group by sid; return @x; end// delimiter ; ~~~