Mysql的存储过程摘要

 

MySQL 5.0 版本开始支持存储过程。

存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。

存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。

存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

一、存储过程的创建和调用

• 存储过程就是具有名字的一段代码，用来完成一个特定的功能。
• 创建的存储过程保存在数据库的数据字典中。

　　MYSQL 存储过程中的关键语法

　　声明语句结束符，可以自定义:

　　DELIMITER $$
　　或
　　DELIMITER //

声明存储过程:

CREATE PROCEDURE demo_in_parameter(IN p_in int)

存储过程开始和结束符号:

BEGIN .... END

变量赋值:

SET @p_in=1

变量定义:

DECLARE l_int int unsigned default 4000000;

创建mysql存储过程、存储函数:

create procedure 存储过程名(参数)

存储过程体:

create function 存储函数名(参数)

实例

创建数据库，备份数据表用于示例操作：

mysql> create database db1; mysql> use db1; mysql> create table PLAYERS as select * from TENNIS.PLAYERS; mysql> create table MATCHES as select * from TENNIS.MATCHES;

下面是存储过程的例子，删除给定球员参加的所有比赛：

mysql> delimiter $$　　#将语句的结束符号从分号;临时改为两个$$(可以是自定义) mysql> CREATE PROCEDURE delete_matches(IN p_playerno INTEGER) -> BEGIN -> 　　DELETE FROM MATCHES -> WHERE playerno = p_playerno; -> END$$ Query OK, 0 rows affected (0.01 sec) mysql> delimiter;　　#将语句的结束符号恢复为分号

解析：默认情况下，存储过程和默认数据库相关联，如果想指定存储过程创建在某个特定的数据库下，那么在过程名前面加数据库名做前缀。 在定义过程时，使用 DELIMITER $$ 命令将语句的结束符号从分号 ; 临时改为两个 $$，使得过程体中使用的分号被直接传递到服务器，而不会被客户端（如mysql）解释。

调用存储过程：

call sp_name[(传参)];

二、存储过程的参数

MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT,形式如：

CREATEPROCEDURE 存储过程名([[IN |OUT |INOUT ] 参数名 数据类形...])

• IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）
• OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
• INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

mysql> create procedure in_param(in p_in int)
    -> begin
    -> 　　select p_in;
    -> 　　set p_in=2;
    ->    select P_in;
    -> end$$
mysql> delimiter ;
mysql> set @p_out=1; 
mysql> call out_param(@p_out);   #调用存储过程

2、out输出参数

mmysql> delimiter // mysql> create procedure out_param(out p_out int) -> begin -> select p_out; -> set p_out=2; -> select p_out; -> end -> //

3、inout输入参数

mysql> delimiter $$
mysql> create procedure inout_param(inout p_inout int)
    ->   begin
    ->     select p_inout;
    ->     set p_inout=2;
    ->     select p_inout;
    ->   end
    -> $$
mysql> delimiter ;
 
mysql> set @p_inout=1;
 
mysql> call inout_param(@p_inout);
mysql> select @p_inout; 


#调用了inout_param存储过程，接受了输入的参数，也输出参数，改变了变量

 

注意：

1、如果过程没有参数，也必须在过程名后面写上小括号例：

CREATE PROCEDURE sp_name ([proc_parameter[,...]]) ……

2、确保参数的名字不等于列的名字，否则在过程体中，参数名被当做列名来处理

三、变量

1. 变量定义

局部变量声明一定要放在存储过程体的开始：

DECLARE variable_name [,variable_name...] datatype [DEFAULT value];

 

2. 变量赋值

SET 变量名 = 表达式值 [,variable_name = expression ...]

3. 用户变量

在MySQL客户端使用用户变量:

mysql > SELECT 'Hello World' into @x;  
mysql > SELECT @x;  
+-------------+  
|   @x        |  
+-------------+  
| Hello World |  
+-------------+  
mysql > SET @y='Goodbye Cruel World';  
mysql > SELECT @y;  

 

在存储过程中使用用户变量

ysql > CREATE PROCEDURE GreetWorld( ) SELECT CONCAT(@greeting,' World');  
mysql > SET @greeting='Hello';  
mysql > CALL GreetWorld( );  

在存储过程间传递全局范围的用户变量

mysql> CREATE PROCEDURE p1()   SET @last_procedure='p1';  
mysql> CREATE PROCEDURE p2() SELECT CONCAT('Last procedure was ',@last_procedure);  
mysql> CALL p1( );  
mysql> CALL p2( );  

MySQL存储过程的调用

用call和你过程名以及一个括号，括号里面根据需要，加入参数，参数包括输入参数、输出参数、输入输出参数。具体的调用方法可以参看上面的例子。

示例：

CREATE DEFINER=`root`@`%` PROCEDURE `p_asset_setItManager`(`uuid` varchar(50),`user_id_par` varchar(20),`apply_no_par` varchar(20), `table_name` varchar(50))
BEGIN
--CREATE DEFINER=`root`@`%` PROCEDURE  定义用户权限

DECLARE s int DEFAULT 0;

DECLARE num int DEFAULT 1;
DECLARE asset_user_city_par varchar(20);
DECLARE it_user_id varchar(50);
DECLARE executeSql varchar(1000);

--DECLARE 声明变量
DECLARE itUserIdList CURSOR FOR select user_id from t_fin_it_city where manage_city = (select belong_city from t_fin_user_city where user_id = `user_id_par`);

--CURSOR FOR  游标 查询的结果集
DECLARE CONTINUE HANDLER FOR NOT FOUND SET s=1;
        
        open itUserIdList;  --打开游标
        
        FETCH NEXT FROM itUserIdList INTO it_user_id;
        
        while s<>1 do
        set executeSql = CONCAT("update ", `table_name`," set approve_asset_user_it",num,"= '",it_user_id,"' where id = '",`apply_no_par`,"'");   
    set @sql = executeSql; --变量赋值
    PREPARE stmt FROM @sql;         
    EXECUTE stmt ;                        
    deallocate prepare stmt;
        if `table_name` = 't_fin_fixed_asset_apply' then
            -- 资产领用-it资产管理员审批提醒
            insert into c_email_message (apply_no,receive_user_id,temp_code,send_flag,retry_num) select `apply_no_par`,it_user_id,'1002','0',0 from dual;
        end if;
        if `table_name` = 't_fin_fixed_asset_transfer_apply' then
            -- 资产转移-it资产管理员审批提醒
            insert into c_email_message (apply_no,receive_user_id,temp_code,send_flag,retry_num) select `apply_no_par`,it_user_id,'2003','0',0 from dual;
        end if;
         set num = num+1;
        
        FETCH NEXT FROM itUserIdList INTO it_user_id;
        
        end while;
        
        close itUserIdList;
END