declare
qrq date ;
hrq date;
SQL1 VARCHAR2(130);
SQL2 VARCHAR2(13000);
SQL3 VARCHAR2(13000);
SQL4 VARCHAR2(30000);
begin
QRQ:=to_date('2016-12-26','yyyy-mm-dd');
HRQ:=to_date('2017-01-25','yyyy-mm-dd');
SQL1:='select a.ryxm as 姓名,';
sql2:='count(CASE WHEN B.TXNDATESN=to_number(to_char(qrq,"yyyymmdd"),"99999999") THEN b.posseq END) AS substr(to_char(qrq,"yyyymmdd"),5,4) || to_char(qrq,"day")';
while QRQ < hRQ
loop
sql2:=sql2 || 'count(CASE WHEN B.TXNDATESN=to_number(to_char(qrq,"yyyymmdd"),"99999999") THEN b.posseq END) AS substr(to_char(qrq,"yyyymmdd"),5,4) || to_char(qrq,"day")';
QRQ:=qrq+1;
end loop;
SQL3:=' from gjzg a,TDCXTXNDETAILTB20170101 b where a.ygkh=b.cardid and a.txntype=31 and to_char(b.busid)=055555 and b.TXNDATESN>=to_number(to_char(qrq,"yyyymmdd"),"99999999") and b.TXNDATESN<=to_number(to_char(hrq,"yyyymmdd"),"99999999") group by a.ryxm';
SQL4:=SQL1 || SQL2 || sql3;
end;
uj5u.com熱心網友回復：
當然沒結果，你只是變數賦值，根本沒列印之類的操作，在end前加一行列印看看結果dbms_output.put_line(SQL4);
uj5u.com熱心網友回復：
用游標打開，然后遍歷print。你的陳述句太長，給你示例，參照補充上即可。
set serveroutput on
declare
sql1 varchar2(1000);
mycursor1 sys_refcursor;
e EMP%ROWTYPE;
begin
sql1 := 'select * from emp ';
open mycursor1 for sql1;
loop
fetch mycursor1 into e;
exit when mycursor1%notfound;
dbms_output.put_line(e.ename || ' : ' || e.job);
end loop;
end;
uj5u.com熱心網友回復：
使用游標，然后for回圈dbms_output.put_line() 輸出uj5u.com熱心網友回復：
set serveroutput on這個別忘記轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/90802.html
標籤：基礎和管理