定义存储过程

DELIMITER //

CREATE PROCEDURE get_current_date()  
BEGIN 
SELECT now();  
END 
 //  

DELIMITER ;

执行存储过程

call get_current_date();

输入输出参数

1、输入参数

DELIMITER //

CREATE PROCEDURE show_param_in(IN p_in int)
BEGIN
SELECT p_in; 
SET p_in = 2;
SELECT p_in;
END;
//

DELIMITER ;

call show_param_in(1);

2、输出参数

DELIMITER //  

CREATE PROCEDURE show_param_out(OUT p_out int)  
BEGIN 
SELECT p_out;  
SET p_out = 2;  
SELECT p_out;  
END;  
//  

DELIMITER ; 
SET @p_out=1;
call show_param_out(@p_out);
select @p_out;

3、输入输出参数

DELIMITER //   

CREATE PROCEDURE show_param_inout(INOUT p_inout int)   
BEGIN 
SELECT p_inout;  
SET p_inout = 2;  
SELECT p_inout;   
END;  
//   

DELIMITER ; 
SET @p_inout = 1;
call show_param_inout(@p_inout);
select @p_inout;

使用游标处理数据

DELIMITER $$

DROP PROCEDURE IF EXISTS update_src_owner$$
CREATE PROCEDURE update_src_owner()
     READS SQL DATA
BEGIN
     DECLARE l_src_id char(36);
     DECLARE l_owner_id varchar(100);
     DECLARE l_creation_date varchar(100);
     DECLARE done INT DEFAULT 0;
     DECLARE cur1 CURSOR FOR SELECT src_id, owner_id, creation_date FROM uni_proj_src ps inner join uni_proj p on ps.proj_id = p.proj_id;
     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

     OPEN cur1;
     emp_loop: LOOP
         FETCH cur1 INTO l_src_id, l_owner_id, l_creation_date;
         IF done=1 THEN
             LEAVE emp_loop;
         END IF;
         update uni_src set owner_id=l_owner_id, creation_date=l_creation_date where src_id=l_src_id;
     END LOOP emp_loop;
     CLOSE cur1;
END$$
DELIMITER ;

[ 编辑 | 历史 ]
最近由“jilili”在“2015-10-11 18:33:44”修改