我在sybase写了一个存储过和,转换到oracle如下:
CREATE OR REPLACE PROCEDURE PROC_ADD_INFO(
v_AlarmID IN NUMBER ,
v_NEFDN IN VARCHAR2 ,
v_NEType IN NUMBER )
AS
v_kk_sql CLOB;
v_kk_count INT;
BEGIN
IF v_AlarmID NOT IN ( SELECT MINLID FROM infoTable WHERE MAXLID = RTRIM(v_NEFDN)) THEN
INSERT INTO infoTable
VALUES (0,v_AlarmID, 1, v_NEType,5); END IF;END;但在 IF v_AlarmID NOT IN ( SELECT MINLID FROM infoTable WHERE MAXLID = RTRIM(v_NEFDN)) THEN这一行报错:PLS-00405: 此上下文中不支持子查询,请问如何修改?
CREATE OR REPLACE PROCEDURE PROC_ADD_INFO(
v_AlarmID IN NUMBER ,
v_NEFDN IN VARCHAR2 ,
v_NEType IN NUMBER )
AS
v_kk_sql CLOB;
v_kk_count INT;
BEGIN
IF v_AlarmID NOT IN ( SELECT MINLID FROM infoTable WHERE MAXLID = RTRIM(v_NEFDN)) THEN
INSERT INTO infoTable
VALUES (0,v_AlarmID, 1, v_NEType,5); END IF;END;但在 IF v_AlarmID NOT IN ( SELECT MINLID FROM infoTable WHERE MAXLID = RTRIM(v_NEFDN)) THEN这一行报错:PLS-00405: 此上下文中不支持子查询,请问如何修改?
and minlid=v_alarmid;
if v_count=0 then
INSERT INTO infoTable
VALUES (0,v_AlarmID, 1, v_NEType,5);
end if;前面要定义一个变量v_count number
v_AlarmID IN NUMBER ,
v_NEFDN IN VARCHAR2 ,
v_NEType IN NUMBER )
AS
v_kk_sql CLOB;
v_kk_count INT;
BEGIN
IF v_AlarmID != ( SELECT MINLID FROM infoTable WHERE MAXLID = RTRIM(v_NEFDN)) THEN
INSERT INTO infoTable
VALUES (0,v_AlarmID, 1, v_NEType,5); END IF; END;