CREATE PROCEDURE HA_ST01,PRO_TEST01_LNJ(IN ID INTEGER,IN DAY_STR NVARCHAR(8),OUT MESSAGE NVARCHAR(50)) LANGUAGE sqlscript as begin DECLARE DAY_DATE DATE; DECLARE COUNT_ID INTEGER; RS_1=SELECT * FROM "HA_ST01"."DATE_INFO_LNJ" WHERE ID =:ID; SELECT COUNT(1) INTO COUNT_ID FROM :RS_1; SELECT TO_DATE(:DAY_STR)INTO DAY_DATE FROM DUMMY; IF :COUNT_ID >=1 THEN :MESSAGE SELECT'错误:主键重复' INTO MESSAGE FROM DUMMY; ELSEIF :COUNT_ID<1 insert into "HA_ST01"."DATE_INFO_LNJ"(ID,DAY,WEEK_NUM)VALUES(:ID,:DAY_DATE,WEEKDAY(:DAY_DATE)); SELECT'插入成功' INTO MESSAGE FROM DUMMY; END IF; end;有啥错误
时间: 2024-02-23 21:56:31 浏览: 51
G–D_测试报告评审表格V1.0.0.500211
在第3行,RS_1应该使用DECLARE关键字声明为变量,应该改为:DECLARE RS_1 CURSOR FOR SELECT * FROM "HA_ST01"."DATE_INFO_LNJ" WHERE ID =:ID;
在第4行,COUNT_ID应该先声明为变量,应该改为:DECLARE COUNT_ID INTEGER;
在第7行,应该使用SELECT COUNT(*) INTO COUNT_ID FROM (SELECT * FROM "HA_ST01"."DATE_INFO_LNJ" WHERE ID =:ID);
在第8行,应该使用SELECT TO_DATE(:DAY_STR,'YYYY-MM-DD') INTO DAY_DATE FROM DUMMY;
在第10行,应该将:MESSAGE SELECT'错误:主键重复' INTO MESSAGE FROM DUMMY; 改为 SET MESSAGE = '错误:主键重复';
在第13行,应该将:MESSAGE SELECT'插入成功' INTO MESSAGE FROM DUMMY; 改为 SET MESSAGE = '插入成功';
正确的存储过程如下:
CREATE PROCEDURE HA_ST01.PRO_TEST01_LNJ (IN ID INTEGER, IN DAY_STR NVARCHAR(8), OUT MESSAGE NVARCHAR(50))
LANGUAGE sqlscript AS
BEGIN
DECLARE RS_1 CURSOR FOR SELECT * FROM "HA_ST01"."DATE_INFO_LNJ" WHERE ID =: ID;
DECLARE COUNT_ID INTEGER;
SELECT COUNT(*) INTO COUNT_ID FROM (SELECT * FROM "HA_ST01"."DATE_INFO_LNJ" WHERE ID =: ID);
DECLARE DAY_DATE DATE;
SELECT TO_DATE(:DAY_STR, 'YYYY-MM-DD') INTO DAY_DATE FROM DUMMY;
IF COUNT_ID >= 1 THEN
SET MESSAGE = '错误:主键重复';
ELSEIF COUNT_ID < 1 THEN
INSERT INTO "HA_ST01"."DATE_INFO_LNJ" (ID, DAY, WEEK_NUM) VALUES (: ID, : DAY_DATE, WEEKDAY(: DAY_DATE));
SET MESSAGE = '插入成功';
END IF;
END;
阅读全文