【MYSQL】存储过程示例

GROUPEMP_EXISTS：

CREATE DEFINER=`ucheck`@`%` PROCEDURE `GROUPEMP_EXISTS`(IN `projectype` varchar(50), IN `groupid` int(11), IN `empid` int(11))
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
DECLARE projectid VARCHAR(10) DEFAULT NULL;
DECLARE result VARCHAR(200) DEFAULT '';
DECLARE project CURSOR FOR SELECT project_id FROM u_project WHERE u_project.project_type=projectype;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET projectid=NULL;
OPEN project;
    search_Loop:LOOP
        FETCH project INTO projectid;
        IF projectid IS NOT NULL THEN
            SET @num = 0;
            SET @sqlstr = CONCAT("SELECT COUNT(*) into @num FROM project_",projectid,".u_project_group_emp WHERE group_id=",groupid," AND emp_id=",empid); 
            PREPARE stmt FROM @sqlstr;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
            IF @num!=0 THEN
                IF result='' 
                THEN
                    SET result =projectid;
                ELSE
                    SET result=CONCAT(result,',',projectid);
                END IF;
            END IF;
        ELSE
            LEAVE search_loop;
        END IF;
    END LOOP search_loop;
CLOSE project;
SELECT result;
END

GROUP_EXISTS：

CREATE DEFINER=`ucheck`@`%` PROCEDURE `GROUP_EXISTS`(IN `projectype` varchar(50), IN `groupid` int(11))
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT '根据指定的组id，查询所有库，返回存在该组id的项目id (1,2,3,4)'
BEGIN
DECLARE projectid VARCHAR(10) DEFAULT NULL;
DECLARE result VARCHAR(200) DEFAULT '';

DECLARE project CURSOR FOR SELECT project_id FROM u_project WHERE u_project.project_type=projectype;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET projectid=NULL;
OPEN project;
    REPEAT
        FETCH project INTO projectid;
        IF projectid IS NOT NULL
        THEN
            SET @num = 0;
            SET @sqlstr = CONCAT("SELECT COUNT(*) into @num FROM project_",projectid,".u_project_group_emp WHERE group_id=",groupid); 
            PREPARE stmt FROM @sqlstr;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
            IF @num!=0 THEN
                IF result='' 
                THEN
                    SET result =projectid;
                ELSE
                    SET result=CONCAT(result,',',projectid);
                END IF;
            END IF;
        END IF;
    UNTIL projectid IS NULL END REPEAT;
CLOSE project;
SELECT result;
END

UPDATE_REVIEWOK

CREATE DEFINER=`ucheck`@`%` PROCEDURE `UPDATE_REVIEWOK`(IN `projectId` int, IN `shotId` int, OUT `result` int)
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN

    DECLARE EXIT HANDLER FOR SQLEXCEPTION SET result=-1;
    SET result=1;
    
    SET @reel='';
    SET @shot=0;
    set @sql=concat("SELECT shot_lock,shot_reel INTO @shot,@reel  FROM project_",projectId,".u_shot WHERE shot_id=",shotId," FOR UPDATE");
    prepare stmt from @sql;
    execute stmt;
    deallocate prepare stmt;

IF @shot=1 THEN
    set @sql=concat("UPDATE project_",projectId,".u_mastershotlist_part,project_",projectId,".u_mastershotlist SET dmap_review_ok=NULL WHERE u_mastershotlist.shot_id=",shotId," AND u_mastershotlist.id=u_mastershotlist_part.mastershotlist_id");
    prepare stmt from @sql;
    execute stmt;
    deallocate prepare stmt;
ELSE
    SET @picstatus=0;
    set @sql=concat("SELECT status_id INTO @picstatus FROM uPlatform.u_status WHERE status_name='Review OK' AND status_type=0");
    prepare stmt from @sql;
    execute stmt;
    deallocate prepare stmt;
    SET @reviewoknum=0;
    SET @totalnum=0;
    set @sql=concat("SELECT SUM(IF(picture_status=",@picstatus,",1,0))AS reviewok,COUNT(*)AS totalNum INTO @reviewoknum,@totalnum FROM reel_",projectId,"_",@reel,".u_picture WHERE picture_shot=",shotId," AND picture_lock=0");
    prepare stmt from @sql;
    execute stmt;
    deallocate prepare stmt;
    IF @totalNum=0 THEN
        SET @per=NULL;
        set @sql=concat("UPDATE project_",projectId,".u_mastershotlist_part,project_",projectId,".u_mastershotlist SET dmap_review_ok=NULL WHERE u_mastershotlist.shot_id=",shotId," AND u_mastershotlist.id=u_mastershotlist_part.mastershotlist_id");
    ELSE
        SET @per=@reviewoknum /@totalnum;
        set @sql=concat("UPDATE project_",projectId,".u_mastershotlist_part,project_",projectId,".u_mastershotlist SET dmap_review_ok=",@per," WHERE u_mastershotlist.shot_id=",shotId," AND u_mastershotlist.id=u_mastershotlist_part.mastershotlist_id");
    END IF;
    prepare stmt from @sql;
    execute stmt;
    deallocate prepare stmt;

END IF;

END

mysql> PREPARE prod FROM "INSERT INTO examlple VALUES(?,?)";   
mysql> SET @p='1';   
mysql> SET @q='2';   
mysql> EXECUTE prod USING @p,@q;   
mysql> SET @name='3';   
mysql> EXECUTE prod USING @p,@name;   
mysql> DEALLOCATE PREPARE prod;  

#EXECUTE stmt USING @a;这样的语句USING后面的变量也只能用set @var=value这种，declare和参数传入的变量不行。

 使用 PREPARE 的几个注意点： 
A：PREPARE stmt_name FROM preparable_stmt;预定义一个语句，并将它赋给 stmt_name ，tmt_name 是不区分大小写的。
B： 即使 preparable_stmt 语句中的 ? 所代表的是一个字符串，你也不需要将 ? 用引号包含起来。 
C： 如果新的 PREPARE 语句使用了一个已存在的 stmt_name ，那么原有的将被立即释放！ 即使这个新的 PREPARE 语句因为错误而不能被正确执行。
D： PREPARE stmt_name 的作用域是当前客户端连接会话可见。 
E： 要释放一个预定义语句的资源，可以使用 DEALLOCATE PREPARE 句法。 
F： EXECUTE stmt_name 句法中，如果 stmt_name 不存在，将会引发一个错误。 
G： 如果在终止客户端连接会话时，没有显式地调用 DEALLOCATE PREPARE 句法释放资源，服务器端会自己动释放它。 
H： 在预定义语句中，CREATE TABLE, DELETE, DO, INSERT, REPLACE, SELECT, SET, UPDATE, 和大部分的 SHOW 句法被支持。
I： PREPARE 语句不可以用于存储过程，自定义函数！但从 MySQL 5.0.13 开始，它可以被用于存储过程，仍不支持在函数中使用！