The BEGIN

DECLARE aa VARCHAR (50);
DECLARE flag INT.
Declare cur1 CURSOR for the select ` date ` from sp_work GROUP BY ` date `;
DECLARE the CONTINUE HANDLER FOR the NOT FOUND SET flag=1;
SET the flag=0;

DROP TABLE IF the EXISTS ` temp_work `;
CREATE TEMPORARY TABLE temp_work (
Today a VARCHAR (10),
Total INTEGER,
Status1 INT,
Status2 INT,
Status3 INT,
Status4 INT,
Status5 INT
) ENGINE=MEMORY;
INSERT INTO temp_work (today, total) SELECT ` date `, count (*) from sp_work where companyno=company_no GROUP BY ` date ` ORDER BY ` date ` desc LIMIT firstRow, listRow;

The OPEN cur1;
REPEAT
The FETCH cur1 INTO aa;
The UPDATE temp_work SET status1=(SELECT count (*) FROM sp_work WHERE ` date `=aa and ` status `=1 and companyno=company_no) WHERE today=aa;
The UPDATE temp_work SET status2=(SELECT count (*) FROM sp_work WHERE ` date `=aa and ` status `=2 and companyno=company_no) WHERE today=aa;
The UPDATE temp_work SET status3=(SELECT count (*) FROM sp_work WHERE ` date `=aa and ` status `=3 and companyno=company_no) WHERE today=aa;
The UPDATE temp_work SET status4=(SELECT count (*) FROM sp_work WHERE ` date `=aa and ` status `=4 and companyno=company_no) WHERE today=aa;
The UPDATE temp_work SET status5=(SELECT count (*) FROM sp_work WHERE ` date `=aa and ` status `=5 and companyno=company_no) WHERE today=aa;
UNTIL flag

END REPEAT;
The CLOSE cur1;
SELECT * from temp_work;
END

CodePudding user response:

Syntax differences too big, change place too much, you can simply look at grammar, change, and then come up to ask
For example, ORACLE cannot directly execute the SELECT * from temp_work; To pass the cursor to receive

CodePudding user response:

The original poster is the way, the specific problems;

CodePudding user response:

Now I want to put a mysql stored procedures for oracle