总体来说可以,但是需要对日期进行转换。 V_SQL := V_SQL||' AND A. DATE1 = TO_DATE('||P_FAULTDATE || ',''YYYY-MM-DD'')';
CREATE OR REPLACE PROCEDURE XXXX(cur_OUT OUT ISDEV.T_CURSOR,P_TYPENAME IN varchar2, P_AREANAME IN varchar2, P_FAULTDATE IN DATE, P_DEALDATE IN DATE) ISV_SQL varchar2(200):='SELECT A.MID,A.ED,A.USERNAME,A. TYPE,A. DESP,A. LEVEL,A. NAME,A. DATE1,A. DATE2,A.CMAN,A.MPLACE,A.REMARK,B.ENAME FROM table1 A,table2 B,table3 C WHERE A.ED = B.DD AND B.ED = C.ED AND C.ETNAME = P_TYPENAME AND A.IE = ''0'''; BEGIN IF P_AREANAME IS NOT NULL THEN V_SQL := V_SQL||' AND A. NAME = '||P_AREANAME; END IF IF P_FAULTDATE IS NOT NULL THEN V_SQL := V_SQL||' AND A. DATE1 = '||P_FAULTDATE; END IF; IF P_DEALDATE IS NOT NULL THEN V_SQL := V_SQL||' AND A. DATE2 = '|| P_DEALDATE; END IF; OPEN PCURSOR FOR V_SQL;END XXXX; --传入的参数的两个字符类型不是string 是varchar/varchar2/char 还有申明的v_sql
不行啊。报了一个错,ora-00933 命令行未正确结束
IF P_DEALDATE IS NOT NULL THEN V_SQL := V_SQL||' AND A. DATE2 = '|| P_DEALDATE;这个地方的日期要转化下: V_SQL := V_SQL||' AND A. DATE2 = to_date('''|| P_DEALDATE||''',''yyyy-mm-dd'');
V_SQL := V_SQL||' AND A. DATE1 = TO_DATE('||P_FAULTDATE || ',''YYYY-MM-DD'')';
CREATE OR REPLACE PROCEDURE XXXX(cur_OUT OUT ISDEV.T_CURSOR,P_TYPENAME IN varchar2,
P_AREANAME IN varchar2,
P_FAULTDATE IN DATE,
P_DEALDATE IN DATE) ISV_SQL varchar2(200):='SELECT A.MID,A.ED,A.USERNAME,A. TYPE,A. DESP,A. LEVEL,A. NAME,A. DATE1,A. DATE2,A.CMAN,A.MPLACE,A.REMARK,B.ENAME FROM table1 A,table2 B,table3 C WHERE A.ED = B.DD AND B.ED = C.ED AND C.ETNAME = P_TYPENAME AND A.IE = ''0''';
BEGIN
IF P_AREANAME IS NOT NULL THEN
V_SQL := V_SQL||' AND A. NAME = '||P_AREANAME;
END IF
IF P_FAULTDATE IS NOT NULL THEN
V_SQL := V_SQL||' AND A. DATE1 = '||P_FAULTDATE;
END IF;
IF P_DEALDATE IS NOT NULL THEN
V_SQL := V_SQL||' AND A. DATE2 = '|| P_DEALDATE;
END IF;
OPEN PCURSOR FOR V_SQL;END XXXX;
--传入的参数的两个字符类型不是string 是varchar/varchar2/char
还有申明的v_sql
V_SQL := V_SQL||' AND A. DATE2 = '|| P_DEALDATE;这个地方的日期要转化下:
V_SQL := V_SQL||' AND A. DATE2 = to_date('''|| P_DEALDATE||''',''yyyy-mm-dd'');
在pl/sql有这种类型吗?和varchar()有关系吗?
[不行啊。报了一个错,ora-00933 命令行未正确结束]
已经报的很明确了,干嘛不看Oracle自己报的错误。