首页 » MySQL存储过程

DELIMITER //

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

DELIMITER ;

call get_current_date();

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);

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;

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 ;