010—存儲過程和函數

一.對待存儲過程和函數的態度

在實際項目中應該盡量少用存儲過程和函數，理由如下：

1.移植性差，在MySQL中的存儲過程移植到sqlsever上就不一定可以用了。

2.調試麻煩，在db中報一個錯誤和在應用層報一個錯誤不是一個概念，那將是毀滅性打擊，直接一個error：1045什么的更本毫無頭緒。

3.擴展性不高。

所以在互聯網時代大型項目應該盡量少使用（不使用）存儲過程和函數！！！

二.創建存儲過程

2.1什么是存儲過程？

存儲過程和存儲函數都是一組sql語句的集合。這些語句集合被當做一個整體存入數據庫中。

2.2創建存儲過程的語法：

create procedure 存儲過程名(參數列表)

                    sql語句

.示例1：

delimiter //

create procedure pro()reads sql databegin select * from stu;end

//

那么我們現在就有一個存儲過程pro了，但是這個存儲過程他是沒有參數的，他只是執行一次查詢操作。

我們現在來講解一下這個存儲過程的結構：

delimiter //  是將分號轉化為//   因為在sql執行時當他遇到分號 ； 時他就講停止所以我們必須將其轉化為 //直到最后一行才會停止執行。

reads sql  data   解釋characteristic的狀態在這里是只讀模式，其他的模式還有：no sql 沒有sql語句 ， ins  sql 不包含讀和寫的語句 ， modifies sql data   包含寫入數據的語句等等。

begin /***/  end   在存儲過程中當有多條語句集合時我們必須使用begin和end。

//   結束整個存儲過程

2.3使用存儲過程

現在只是創建了一個存儲過程，那么我們怎么來使用這個存儲過程呢？

語法：call  存儲過程名()

將上一個存儲過程pro使用的例子：

call pro();

2.4創建一個帶參數的存儲過程

參數列表：存儲過程的參數有三種類型：in,out,inout 分別表示傳入參數和傳出參數，和既傳入也傳出參數。

例子：首先我們來創建兩張表：課程表是學生表的從表。

create table stu(

stu_id bigint primary key auto_increment,#學號

stu_name varchar(10) not null,#姓名

stu_major int not null,#專業號

stu_sex char,#性別

stu_in date,#入學日期

stu_birth date,#出生日期

foreign key (stu_major) references major(ma_id)#專業外鍵設置

);

create table major(

ma_id int primary key,

ma_name varchar(15),

ma_boss varchar(10)

);

insert into major values(1,"信管","張三");

insert into major values(2,"電子商務","李四");

insert into stu values(1,"小明",1,"男","2017-09-01","1998-12-23");

insert into stu values(2,"小高",1,"男","2017-09-01","1998-05-01");

insert into stu values(3,"小李",2,"男","2017-09-01","1999-04-01");

我們再來創建一個帶有參數的存儲過程找到學生的主修課的名字，代碼如下：

delimiter //

create procedure pro1(in sname varchar(10),out ma varchar(10))

reads sql data

begin

select ma_name into ma from major where ma_id = (select stu_major from stu where stu_name=sname);

end

//

使用這個存儲過程：代碼如下：

set @ma="沒查詢之前";

call pro1("小李",@ma);

select @ma;

解釋一下代碼：首先使用set @ma 定義一個全局變量，然后在使用call 存儲過程名 語法調用存儲過程，同時全局變量ma的值也改變了。

三.創建一個存儲函數

3.1存儲過程和存儲函數的不同。

1.在函數中必須要有return返回值

2.在存儲過程中參數有in  out   inout三種，默認為in類型，但是在函數中只有一種in類型。

3.2創建一個函數

語法：create  function 函數名()

　　　      return 返回類型

　　　　　　　　  sql語句集合

示例2：

delimiter //

create function fun1(num int)

returns int

begin

return num+1000;

end

//

顯然函數與存儲過程的最大的區別就是在于return

3.3調用函數

使用語法不在使用關鍵字call，而是關鍵字select ，select  函數名

例子：

select fun1(100);

四.刪除存儲函數和存儲過程

 語法：drop   procedure  |   function  存儲過程名或者是函數名

 例子：

drop procedure pro;

 注意：此時的存儲過程或者函數名稱是不帶括號的！！！

五.在存儲過程和存儲函數中使用游標

5.1為什么需要游標？

當我們在使用存儲過程的時候可能用到多條數據，那么我們就需要用到游標來存放多條數據。

5.2使用游標的注意點

游標不能單獨存在，必須在存儲過程或者是存儲函數中使用。

5.3使用游標

語法：

1.創建游標：declare  游標名  cursor  for  select語句  

2.打開游標：open  游標名

3.使用游標：fetch  游標名  into  變量名

4.關閉光標：close 游標名

示例3：

delimiter //

create function fun3(id int)

returns intreads sql data

Begin

declare cur cursor for select stu_id from stu;

open cur;

fetch cur into id;

close cur;

return id;

End

//

使用

set @id=0;

select fun3(@id);

可以發現游標只是將第一個值給了變量。