cursor is oracle 日期_oracle 存储过程日期参数的疑问

CREATE OR REPLACE PROCEDURE R_CAPITAL_BUDGET

(

Department in varchar2,   ----部门

Term in varchar2,          ----经营期间

Account in varchar2,       ----预算科目

bDate in date,            ----开始时间

eDate in date,             ----结束时间

Capital_CURSOR OUT REPORT_PACKAGE.REPORT_CURSOR

)

IS

v_dep varchar2(200);

V_depnew varchar2(200);

v_term varchar2(100);

v_termnew varchar2(200);

v_account varchar2(200);

v_accountnew varchar2(200);

v_bdate varchar2(100);

v_edate varchar2(100);

v_sql varchar2(4000);

BEGIN

v_dep:=Department;

V_depnew:=v_dep||V_depnew;

v_term:=Term;

v_termnew:=v_term||v_termnew;

v_account:=Account;

v_accountnew:=v_account||v_accountnew;

v_bdate:=to_char(bdate,'YYYY-MM-DD');

v_edate:=to_char(edate,'YYYY-MM-DD');

v_sql:= 'SELECT DISTINCT A.STRUCTUREID,

C.TOTALCODE,

C.OBJECTCODE,

C.OBJECTNAME AS ACCOUNT,

D.OBJECTNAME AS DEPARTMENT,

E.OBJECTNAME AS TERM,

G.OBJECTNAME AS YEAR,

NVL(SUM(B.TOTALYEAR),0) AS BUDGETYear,

NVL(SUM(H.BUDGETADJUSTMENT),0) AS BUDGETADJUSTMENT,

NVL(SUM(I.BALANCE),0) AS BALANCE,

NVL(SUM(J.ACTUAL),0) AS ACTUAL

FROM T_CC_STRUCTURE A

INNER JOIN (SELECT A.STRUCTUREID,A.TOTALYEAR,B.OBJECTNAME,A.YEARID FROM T_CC_BUDGETDATA A

INNER JOIN (SELECT OBJECTID,OBJECTNAME FROM T_CC_OBJECT WHERE OBJECTTYPEID=2) B ON A.YEARID=B.OBJECTID

WHERE A.DATATYPE=0 AND B.OBJECTNAME>=SUBSTR('''||v_bdate||''',0,4) AND B.OBJECTNAME<=SUBSTR('''||v_edate||''',0,4)) B ON B.StructureID=A.StructureID

INNER JOIN (SELECT OBJECTID,OBJECTNAME,TOTALCODE,OBJECTCODE FROM T_CC_OBJECT WHERE OBJECTTYPEID=0 AND TOTALCODE like ''0002,1011,2%'') C ON C.OBJECTID=A.DIMACCOUNTID

INNER JOIN (SELECT OBJECTID,OBJECTNAME FROM T_CC_OBJECT WHERE OBJECTTYPEID=1) D ON D.OBJECTID=A.DIMSUBJECTID

INNER JOIN T_CC_OBJECT E ON E.OBJECTID=A.DIM01ID

INNER JOIN T_CC_OBJECT G ON G.OBJECTID=B.YEARID

LEFT JOIN (SELECT DISTINCT A.STRUCTUREID,B.TOTALYEAR AS BUDGETAdjustment

FROM T_CC_STRUCTURE A

INNER JOIN (SELECT * FROM T_CC_BUDGETDATA WHERE DATATYPE=0) B ON B.StructureID=A.StructureID

INNER JOIN (SELECT OBJECTID,OBJECTNAME,TOTALCODE,OBJECTCODE FROM T_CC_OBJECT WHERE OBJECTTYPEID=0 AND TOTALCODE like ''0002,1011,2%'') C ON C.OBJECTID=A.DIMACCOUNTID

INNER JOIN (SELECT OBJECTID,OBJECTNAME FROM T_CC_OBJECT WHERE OBJECTTYPEID=1) D ON D.OBJECTID=A.DIMSUBJECTID

WHERE A.DIM02ID IN (SELECT OBJECTID FROM T_CC_OBJECT WHERE OBJECTNAME=''追加调整'')) H ON H.STRUCTUREID=A.STRUCTUREID

LEFT JOIN (SELECT STRUCTUREID,ENDNATURALAMOUNT AS BALANCE FROM T_CC_ACCOUNTBALANCE WHERE YEAR>=SUBSTR('''||v_bdate||''',0,4) AND YEAR<=SUBSTR('''||v_edate||''',0,4)

UNION ALL

SELECT STRUCTUREID,ENDNATURALAMOUNT AS BALANCE FROM T_CC_ACCOUNTBALANCE_BZ WHERE YEAR>=SUBSTR('''||v_bdate||''',0,4) AND YEAR<=SUBSTR('''||v_edate||''',0,4)) I ON I.STRUCTUREID=A.STRUCTUREID

LEFT JOIN (SELECT A.STRUCTUREID,A.NATURALCURRENCY AS ACTUAL FROM T_CC_BILLDETAILDATA A

INNER JOIN T_CC_BILLMAINDATA B ON A.BILLMAINDATAID=B.BILLMAINDATAID

WHERE B.OCCURTIME>=to_date('''||v_bdate||''',''YYYY-MM-DD'') AND B.OCCURTIME<=to_date('''||v_edate||''',''YYYY-MM-DD'')

AND A.STRUCTUREID IS NOT NULL) J ON J.STRUCTUREID=A.STRUCTUREID

WHERE A.DIM02ID IN (SELECT OBJECTID FROM T_CC_OBJECT WHERE OBJECTNAME=''年初预算'')

AND INSTR('''||v_accountnew||''',C.OBJECTNAME)>0

AND INSTR('''||V_depnew||''',D.OBJECTNAME)>0

AND INSTR('''||v_termnew||''',E.OBJECTNAME)>0

GROUP BY A.STRUCTUREID,C.TOTALCODE,C.OBJECTCODE,C.OBJECTNAME,D.OBJECTNAME,E.OBJECTNAME,G.OBJECTNAME

ORDER BY C.TOTALCODE ASC' ;

OPEN Capital_CURSOR FOR v_sql;

END R_CAPITAL_BUDGET;