Create Or Replace Procedure pppp() As
sql Varchar2(8000);
Begin
sql:='';
sql:='select b.name,b.sex,trunc((to_char(sysdate,''yyyyMMdd'')-to_char(birthdate,''yyyyMMdd''))/10000) age,';
set sql=sql+'b.idcard,b.homephone,a.vipid,count(*) num,sum(realmoney) realmoney,';
cursor c_main is
select couternid from resa_counter;
c_main c_mainf%rowtype;
open c_main;
loop
fetch c_main into c_mainf;
cid := c_mainf.counterid;
@sql=@sql+'case when counterid='''+cid+''' then sum(realmoney) else 0 end g'''+cid+''' ';
end loop;
close c_main;
@sql=@sql+'from zx_prt_resa_09 a,RESA_CUSTOMER_def b,resa_vip c where ';
@sql=@sql+'useday>=to_date(''2004-12-31'',''yyyy-mm-dd'') and useday<=to_date(''2005-1-30'',''yyyy-mm-dd'') ';
@sql=@sql+'and b.rsacustomerid=c.rsacustomerid and a.vipid=c.vipid and rownum<10 group by b.name,b.sex,';
@sql=@sql+'trunc((to_char(sysdate,''yyyyMMdd'')-to_char(birthdate,''yyyyMMdd''))/10000),b.idcard,b.homephone,a.vipid,counterid';print @sql;
end pppp;
这是一个从Sql Server拷过来的,请大家帮忙看看转成oracle的。具体功能是列转行,中间那个游标就是为了把sql中的拼成串。
sql Varchar2(8000);
Begin
sql:='';
sql:='select b.name,b.sex,trunc((to_char(sysdate,''yyyyMMdd'')-to_char(birthdate,''yyyyMMdd''))/10000) age,';
set sql=sql+'b.idcard,b.homephone,a.vipid,count(*) num,sum(realmoney) realmoney,';
cursor c_main is
select couternid from resa_counter;
c_main c_mainf%rowtype;
open c_main;
loop
fetch c_main into c_mainf;
cid := c_mainf.counterid;
@sql=@sql+'case when counterid='''+cid+''' then sum(realmoney) else 0 end g'''+cid+''' ';
end loop;
close c_main;
@sql=@sql+'from zx_prt_resa_09 a,RESA_CUSTOMER_def b,resa_vip c where ';
@sql=@sql+'useday>=to_date(''2004-12-31'',''yyyy-mm-dd'') and useday<=to_date(''2005-1-30'',''yyyy-mm-dd'') ';
@sql=@sql+'and b.rsacustomerid=c.rsacustomerid and a.vipid=c.vipid and rownum<10 group by b.name,b.sex,';
@sql=@sql+'trunc((to_char(sysdate,''yyyyMMdd'')-to_char(birthdate,''yyyyMMdd''))/10000),b.idcard,b.homephone,a.vipid,counterid';print @sql;
end pppp;
这是一个从Sql Server拷过来的,请大家帮忙看看转成oracle的。具体功能是列转行,中间那个游标就是为了把sql中的拼成串。
sqloracle Varchar2(8000);
cid varchar(200);
cursor c_main is select couternid from resa_counter;;
c_mainf c_main%rowtype;
Begin
sqloracle:='';
sqloracle:='select b.name,b.sex,trunc((to_char(sysdate,''yyyyMMdd'')-to_char(birthdate,''yyyyMMdd''))/10000) age,';
sqloracle:=sqloracle+'b.idcard,b.homephone,a.vipid,count(*) num,sum(realmoney) realmoney,';
open c_main;
fetch c_main into c_mainf;
while c_main%found
loop
cid := c_mainf.counterid; sqloracle:=sqloracle||'case when counterid='''||cid||''' then sum(realmoney) else 0 end g'''||cid||''' ';
exit when c_main%notfound;
end loop;
close c_main;
sqloracle:=sqloracle||'from zx_prt_resa_09 a,RESA_CUSTOMER_def b,resa_vip c where ';
sqloracle:=sqloracle||'useday>=to_date(''2004-12-31'',''yyyy-mm-dd'') and useday <=to_date(''2005-1-30'',''yyyy-mm-dd'') ';
sqloracle:=sqloracle||'and b.rsacustomerid=c.rsacustomerid and a.vipid=c.vipid and rownum <10 group by b.name,b.sex,';
sqloracle:=sqloracle||'trunc((to_char(sysdate,''yyyyMMdd'')-to_char(birthdate,''yyyyMMdd''))/10000),b.idcard,b.homephone,a.vipid,counterid';dbms_output.putline(sqloracle);
commit;
end;
sql Varchar2(8000); //变量名最好加个前缀,比如var_等,避免不知道的情况下与关键字冲突了
Begin
sql:='';
sql:='select b.name,b.sex,trunc((to_char(sysdate,''yyyyMMdd'')-to_char(birthdate,''yyyyMMdd''))/10000) age,';
//age的算法有问题。
set sql=sql+'b.idcard,b.homephone,a.vipid,count(*) num,sum(realmoney) realmoney,';
//没有用set方式的,用 sql :=sql|| ....; 来连接两个字符串
cursor c_main is
select couternid from resa_counter;
c_main c_mainf%rowtype;
open c_main;
loop
fetch c_main into c_mainf;
//这里应加上 exit when c_main%notfound; 来检测游标滚到末尾时退出,否则你这个循环到最后会报错。
cid := c_mainf.counterid; //这里写错了,少了个n,并且cid也没有定义
@sql=@sql+'case when counterid='''+cid+''' then sum(realmoney) else 0 end g'''+cid+''' ';
//这句一样,直接sql:=sql||来连接
end loop;
close c_main;
@sql=@sql+'from zx_prt_resa_09 a,RESA_CUSTOMER_def b,resa_vip c where '; //同上修改赋值语句
@sql=@sql+'useday>=to_date(''2004-12-31'',''yyyy-mm-dd'') and useday <=to_date(''2005-1-30'',''yyyy-mm-dd'') ';//同上修改赋值语句
@sql=@sql+'and b.rsacustomerid=c.rsacustomerid and a.vipid=c.vipid and rownum <10 group by b.name,b.sex,';//同上修改赋值语句
@sql=@sql+'trunc((to_char(sysdate,''yyyyMMdd'')-to_char(birthdate,''yyyyMMdd''))/10000),b.idcard,b.homephone,a.vipid,counterid';//同上修改赋值语句print @sql; //应用dbms_output.put_line(sql);来打印。需要在执行前set serveroutput on,才能看到。
end pppp;
dbms_output.put_line最长支持255个字符,你的太长。怎么改不晓得。
纠正--不懂的不要乱说 :
dbms_output.put_line最长支持255个字符
你自己查查再跟别人讲好么......
不对哦
YY频道:26831170