(
ProgramId VARCHAR2,
FileId int,
PCursor out DATATYPE.MYCURSOR
)
AS
BEGIN
open PCursor for
SELECT
A.PROGRAM_ID,
B.FILEID
from TABLE1 A
INNER JOIN TABLE2 B on A.PROGRAM_ID=B.PROGRAM_ID
where 1=1 ;
IF ProgramId <> "" then
AND A.PROGRAM_ID = ProgramId
end IF;
if FileId <> "" then
AND B.FILEID = FileId
end if;
END这样写编译错误,应该怎么修改呢?
ProgramId VARCHAR2,
FileId int,
PCursor out DATATYPE.MYCURSOR
)
AS
BEGIN
open PCursor for
SELECT
A.PROGRAM_ID,
B.FILEID
from TABLE1 A
INNER JOIN TABLE2 B on A.PROGRAM_ID=B.PROGRAM_ID
where 1=1 ;
IF ProgramId <> "" then
AND A.PROGRAM_ID = ProgramId
end IF;
if FileId <> "" then
AND B.FILEID = FileId
end if;
END这样写编译错误,应该怎么修改呢?
ProgramId VARCHAR2,
FileId int,
PCursor out DATATYPE.MYCURSOR
)
AS
BEGIN
IF ProgramId is not null then
open PCursor for
SELECT A.PROGRAM_ID,B.FILEID
from TABLE1 A
INNER JOIN TABLE2 B on A.PROGRAM_ID=B.PROGRAM_ID
where 1=1 AND A.PROGRAM_ID = ProgramId ;
end IF;
if FileId is not null and then
open PCursor for
SELECT A.PROGRAM_ID,B.FILEID
from TABLE1 A
INNER JOIN TABLE2 B on A.PROGRAM_ID=B.PROGRAM_ID
where 1=1 AND B.FILEID = FileId ;
end if;
END ;
create or replace PROCEDURE 名字(
ProgramId VARCHAR2,
FileId int,
PCursor out DATATYPE.MYCURSOR
)
AS
BEGIN
IF ProgramId is not null then
open PCursor for
SELECT A.PROGRAM_ID,B.FILEID
from TABLE1 A
INNER JOIN TABLE2 B on A.PROGRAM_ID=B.PROGRAM_ID
where 1=1 AND A.PROGRAM_ID = ProgramId ;
end IF;
if FileId is not null then
open PCursor for
SELECT A.PROGRAM_ID,B.FILEID
from TABLE1 A
INNER JOIN TABLE2 B on A.PROGRAM_ID=B.PROGRAM_ID
where 1=1 AND B.FILEID = FileId ;
end if;
END ;
IF ProgramId is not null and FileId is not null then
......
end if;
......
end if; 还有
http://tech.sina.com.cn/s/2004-10-22/1410445174.shtml
SQL> select 1 from dual where 1=(case when 1>0 then 1 else 2 end); 1
----------
1SQL> select 1 from dual where 1=(case when 1>2 then 1 else 2 end);未选定行SQL>
CREATE OR REPLACE PROCEDURE XXXX(
ProgramId VARCHAR2,
FileId int,
PCursor out DATATYPE.MYCURSOR
)
AS
V_SQL := 'SELECT A.PROGRAM_ID, B.FILEID FROM TABLE1 A INNER JOIN TABLE2 B ON A.PROGRAM_ID = B.PROGRAM_ID WHERE 1=1 AND ';
BEGIN
IF ProgramId IS NULL THEN
V_SQL := V_SQL||'B.PROGRAM_ID = '||ProgramId;
ELSE IF FileId IS NOT NULL THEN
V_SQL := V_SQL||'B.FILEID = '||FILEID
END IF;
OPEN PCURSOR FOR V_SQL;
--EXCEPTION
--
END;
CREATE OR REPLACE PROCEDURE XXXX(
ProgramId VARCHAR2,
FileId int,
PCursor out DATATYPE.MYCURSOR
)
AS
V_SQL := 'SELECT A.PROGRAM_ID, B.FILEID FROM TABLE1 A INNER JOIN TABLE2 B ON A.PROGRAM_ID = B.PROGRAM_ID WHERE 1=1';
BEGIN
IF ProgramId IS NULL THEN
V_SQL := V_SQL||' AND B.PROGRAM_ID = '||ProgramId;
ELSE IF FileId IS NOT NULL THEN
V_SQL := V_SQL||' AND B.FILEID = '||FILEID
END IF;
OPEN PCURSOR FOR V_SQL;
--EXCEPTION
--
END;
V_SQL := V_SQL||' AND B.PROGRAM_ID = '||ProgramId;
ELSE IF FileId IS NOT NULL THEN
V_SQL := V_SQL||' AND B.FILEID = '||FILEID
END IF;
不是应该这样吗?
V_SQL := V_SQL||' AND B.PROGRAM_ID = '||ProgramId;
END IF;
IF FileId IS NOT NULL THEN
V_SQL := V_SQL||' AND B.FILEID = '||FILEID
END IF;