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;
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;
解决方案 »
- Oracle RAC 中使用虚拟IP 是如何连接到实际主机的
- where子句
- 关于数据迁移出现 ORA-12154: TNS: 无法解析指定的连接标识符(救命啊)
- 初学oracle,有个sql语句弄了好久没弄出来,好心人帮忙看下,谢谢了!
- SQL查询结果拼接问题
- 请教:Io 异常: Software caused connection abort: socket write error ??
- 初学Oracle的疑问
- 身份验证问题
- 我建的DBLINK为什么总是报”ORA-12154: TNS: 无法处理服务名“的错呢?代码如下:
- ORACLE SQL 数据合并
- 在安装10G时出错
- 求推荐oralce放面的书籍。
你的语句太长,给你示例,参照补充上即可。set serveroutput ondeclare
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;