Question

任何人都可以帮助我为什么我不能创建临时表关于存储程序。

这是我的存储程序脚本

DELIMITER $$

DROP PROCEDURE IF EXISTS getProductionItem$$
CREATE DEFINER=pvtuser@`%` PROCEDURE getProductionItem(projectID BIGINT(20))


BEGIN

  CREATE TEMPORARY TABLE tempProdItem(
    prodType SET( JOB , BATCH , DOCGROUP ),
    taskQueueId BIGINT(20),
    jobBatchDocId BIGINT(20),
    jobBatchDocName VARCHAR(255),
    lockStatus SET( LOCKED , OPEN )
  );


  DECLARE done INT DEFAULT 0;


  DECLARE prodType VARCHAR(10);
  DECLARE taskQueueId BIGINT(20);
  DECLARE jobBatchDocId BIGINT(20);
  DECLARE jobBatchDocName VARCHAR(255);
  DECLARE lockStatus VARCHAR(10);


  DECLARE docCursor CURSOR
      FOR SELECT  DOCGROUP ,
                 jq.taskqueueid,
                 jq.documentgroupid,
                 dgd.documentGroupName,
                 COALESCE(jql.lockStatus, OPEN )
            FROM jobdetails jd
           INNER JOIN jobqueue jq
              ON jd.jobid=jq.jobid
           INNER JOIN documentgroupdetails dgd
              ON jq.documentgroupid=dgd.documentgroupid
            LEFT JOIN jobqueuelocked jql
              ON jq.taskqueueid=jql.taskqueueid
           GROUP BY jq.taskqueueid;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

  OPEN docCursor;

  Loop1: LOOP
    FETCH docCursor INTO prodType,taskQueueId,jobBatchDocId,jobBatchDocName,lockStatus;
    IF done=1 THEN
      LEAVE Loop1;
    ELSE
       /* TODO INSERT HERE IN TEMP TABLE */
    END IF;

  END LOOP;

  CLOSE docCursor;

  SELECT * FROM tempProdItem;

END

$$
DELIMITER;

Answer 1

DELIMITER $$

DROP PROCEDURE IF EXISTS getProductionItem$$
CREATE DEFINER=pvtuser@`%` PROCEDURE getProductionItem(projectID BIGINT(20))

BEGIN

  DECLARE done INT DEFAULT 0;
  DECLARE prodType VARCHAR(10);
  DECLARE taskQueueId BIGINT(20);
  DECLARE jobBatchDocId BIGINT(20);
  DECLARE jobBatchDocName VARCHAR(255);
  DECLARE lockStatus VARCHAR(10);

  DECLARE docCursor CURSOR
      FOR SELECT  DOCGROUP ,
                 jq.taskqueueid,
                 jq.documentgroupid,
                 dgd.documentGroupName,
                 COALESCE(jql.lockStatus, OPEN )
            FROM jobdetails jd
           INNER JOIN jobqueue jq
              ON jd.jobid=jq.jobid
           INNER JOIN documentgroupdetails dgd
              ON jq.documentgroupid=dgd.documentgroupid
            LEFT JOIN jobqueuelocked jql
              ON jq.taskqueueid=jql.taskqueueid
           GROUP BY jq.taskqueueid;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

  CREATE TEMPORARY TABLE tempProdItem(
    prodType SET( JOB , BATCH , DOCGROUP ),
    taskQueueId BIGINT(20),
    jobBatchDocId BIGINT(20),
    jobBatchDocName VARCHAR(255),
    lockStatus SET( LOCKED , OPEN )
  );


  OPEN docCursor;

  Loop1: LOOP
    FETCH docCursor INTO prodType,taskQueueId,jobBatchDocId,jobBatchDocName,lockStatus;
    IF done=1 THEN
      LEAVE Loop1;
    ELSE
      INSERT INTO tempProdItem
             (prodType,taskQueueId,jobBatchDocId,jobBatchDocName,lockStatus)
      VALUES (prodType,taskQueueId,jobBatchDocId,jobBatchDocName,lockStatus);
    END IF;

  END LOOP;

  CLOSE docCursor;

  SELECT * FROM tempProdItem;

END

$$
DELIMITER;

友情链接