萬盛學電腦網

 萬盛學電腦網 >> 數據庫 >> mysql教程 >> mysql存儲過程的簡單介紹

mysql存儲過程的簡單介紹

在mysql高版本中存儲過程非常的受用了,我們今天來了解一下mysql存儲過程的一些簡單用法也就是基礎知識哦,具體如下。

MySQL中,創建存儲過程的基本形式如下:

CREATE PROCEDURE sp_name ([proc_parameter[,...]]) 
        [characteristic ...] routine_body
其中,sp_name參數是存儲過程的名稱;proc_parameter表示存儲過程的參數列表; characteristic參數指定存儲過程的特性;routine_body參數是SQL代碼的內容,可以用BEGIN…END來標志SQL代碼的開始和結束。

proc_parameter中的每個參數由3部分組成。這3部分分別是輸入輸出類型、參數名稱和參數類型。其形式如下:

[ IN | OUT | INOUT ] param_name type

其中,IN表示輸入參數;OUT表示輸出參數; INOUT表示既可以是輸入,也可以是輸出; param_name參數是存儲過程的參數名稱;type參數指定存儲過程的參數類型,該類型可以是MySQL數據庫的任意數據類型。

characteristic參數有多個取值。其取值說明如下:

LANGUAGE SQL:說明routine_body部分是由SQL語言的語句組成,這也是數據庫系統默認的語言。

[NOT] DETERMINISTIC:指明存儲過程的執行結果是否是確定的。DETERMINISTIC表示結果是確定的。每次執行存儲過程時,相同的輸入會得到相同的輸出。NOT DETERMINISTIC表示結果是非確定的,相同的輸入可能得到不同的輸出。默認情況下,結果是非確定的。

{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }:指明子程序使用SQL語句的限制。CONTAINS SQL表示子程序包含SQL語句,但不包含讀或寫數據的語句;NO SQL表示子程序中不包含SQL語句;READS SQL DATA表示子程序中包含讀數據的語句;MODIFIES SQL DATA表示子程序中包含寫數據的語句。默認情況下,系統會指定為CONTAINS SQL。

SQL SECURITY { DEFINER | INVOKER }:指明誰有權限來執行。DEFINER表示只有定義者自己才能夠執行;INVOKER表示調用者可以執行。默認情況下,系統指定的權限是DEFINER。

COMMENT 'string':注釋信息。

技巧:創建存儲過程時,系統默認指定CONTAINS SQL,表示存儲過程中使用了SQL語句。但是,如果存儲過程中沒有使用SQL語句,最好設置為NO SQL。而且,存儲過程中最好在COMMENT部分對存儲過程進行簡單的注釋,以便以後在閱讀存儲過程的代碼時更加方便。

存儲過程的好處:

業務邏輯被“隱藏”在數據庫裡面,從而避免敏感的數據被透露

能簡化應用程序代碼

跨平台性

如果您必須一次執行幾個語句,一個存儲過程比客戶端程序逐條地執行語句要快的多,因為不需要任何的來往返數據

CREATE

[DEFINER = {user | CURRENT_USER}]

PROCEDURE<name> (PARAMETER(S)…)

[characteristic(s)…] routine_body

The syntax for creating a function is:

CREATE

[DEFINER = {user | CURRENT_USER }]

FUNCTION sp_name ([parameter])

RETURNS type

[characteristic(s)…] routine_body

CREATE 創建

DEFINER 定義者,表示存儲過程或者函數的所有者,如果省略,講使用默認所有者(當前用戶)

RPOCEDURE<name> 表示創建一個名字為name的存儲過程

FUNCTION<name> 表示創建一個名稱為name的函數

【IN | OUT | INOUT】<parameter name> type

IN 指參數是一個輸入變量,它僅向過程提供一個值

OUT 指參數是只是用來存儲返回值

INOUT 指參數可用於輸入變量和返回值

parameter name(參數名稱)是Type的類型名

RETURNS 表示返回數據類型

DETERMINISTIC 確定性

NOT DETERMINISTIC 非確定性

實例一、

CREATE PROCEDURE user_avg(OUT average NUMERIC(5,2))

BEGIN

SELECT AVG(age) INTO average FROM users

END;

執行該存儲過程:

CALL user_avg(@a);

SELECT @a;

實例二、

MySQL存儲過程

數據庫存在的admin1的時候返回1

MySQL存儲過程

數據庫不存在的admin11的時候返回0

例子

將下面的語句復制粘貼可以一次性執行完,我已經測試過,沒有問題!

MySql存儲過程簡單實例:
           /********************* 創建表 *****************************/
delimiter //

DROP TABLE if exists test //

CREATE TABLE test(
  id int(11) NULL
) //

/********************** 最簡單的一個存儲過程 **********************/
drop procedure if exists sp//
 CREATE PROCEDURE sp() select 1 //
 
 call sp()//
 
/********************* 帶輸入參數的存儲過程  *******************/

drop procedure if exists sp1 //

create procedure sp1(in p int)
comment 'insert into a int value'
begin
  /* 定義一個整形變量 */
  declare v1 int;
 
  /* 將輸入參數的值賦給變量 */
  set v1 = p;
 
  /* 執行插入操作 */
  insert into test(id) values(v1);
end
//

/* 調用這個存儲過程  */
call sp1(1)//

/* 去數據庫查看調用之後的結果 */
select * from test//

 /****************** 帶輸出參數的存儲過程 ************************/

drop procedure if exists sp2 //
create procedure sp2(out p int)
/*這裡的DETERMINISTIC子句表示輸入和輸出的值都是確定的,不會再改變.我一同事說目前mysql並沒有實現該功能,因此加不加都是NOT DETERMINISTIC的*/
DETERMINISTIC
begin
  select max(id) into p from test;
end
//

/* 調用該存儲過程,注意:輸出參數必須是一個帶@符號的變量 */
call sp2(@pv)//

/* 查詢剛剛在存儲過程中使用到的變量 */
select @pv//

/******************** 帶輸入和輸出參數的存儲過程 ***********************/

drop procedure if exists sp3 //
create procedure sp3(in p1 int , out p2 int)
begin

  if p1 = 1 then
    /* 用@符號加變量名的方式定義一個變量,與declare類似 */
    set @v = 10;
  else
    set @v = 20;
  end if;
 
  /* 語句體內可以執行多條sql,但必須以分號分隔 */
  insert into test(id) values(@v);
  select max(id) into p2 from test;
 
end
//

/* 調用該存儲過程,注意:輸入參數是一個值,而輸出參數則必須是一個帶@符號的變量 */
call sp3(1,@ret)//

select @ret//

/***************** 既做輸入又做輸出參數的存儲過程 ***************************************/

drop procedure if exists sp4 //
create procedure sp4(inout p4 int)
begin
   if p4 = 4 then
      set @pg = 400;
   else
      set @pg = 500;
   end if;
  
   select @pg;
  
end//

call sp4(@pp)//

/* 這裡需要先設置一個已賦值的變量,然後再作為參數傳入 */
set @pp = 4//
call sp4(@pp)//


/********************************************************/

copyright © 萬盛學電腦網 all rights reserved