Oracle存储过程传参数问题
在存储过程当中传参数的时候可以使用
IF SDATE IS NOT NULL OR EDATE IS NOT NULL THEN V_SQL := V_SQL || ' AND K.LJFRQ BETWEEN ''' || SDATE ||''' AND ''' || EDATE || ''''; END IF;
PROCEDURE QUERY_PAYFEE_REPEAL(V_YWLSH IN VARCHAR2,V_YHBH IN VARCHAR2,V_BIZ_TYPE IN NUMBER,V_SDATE IN VARCHAR2,V_EDATE IN VARCHAR2,VPAGES IN NUMBER, VPAGEE IN NUMBER, VRS OUT PAY_CURSOR) IS V_SQL VARCHAR2(20000) := 'SELECT * FROM (SELECT A.*,ROWNUM RN FROM ( '; BEGIN IF V_BIZ_TYPE =1 THEN V_SQL:=V_SQL|| 'SELECT FXZT,AGENT_ID,JFRQ,TERM_ID,YWLSH,YHMC,OPER_TYPE,YHBH,SJJE,OPTOR_ID FROM T_PAYFEE_POWER T WHERE (YWLSH=:V_YWLSH OR :V_YWLSH IS NULL) AND (YHBH=:V_YHBH OR :V_YHBH IS NULL) AND JFRQ BETWEEN :V_SDATE AND :V_EDATE AND RESULT_CODE= 0 AND FXZT <>0 '; END IF; V_SQL:=V_SQL||') A WHERE ROWNUM <:VPAGEE ) WHERE RN >=:VPAGES '; DBMS_OUTPUT.PUT_LINE(V_SQL); OPEN VRS FOR V_SQL USING V_YWLSH,V_YWLSH,V_YHBH,V_YHBH,V_SDATE,V_EDATE,VPAGEE,VPAGES; END QUERY_PAYFEE_REPEAL;
