oracle存储过程第一个实际应用

linux_kaige

分享一下：
Oracle存储过程从入门到精通.pdf (5.64 MB, 下载次数: 697)

2013-01-16 17:23 上传

点击文件名下载附件

oracle存储过程学习经典[语法+实例+调用].pdf (2.66 MB, 下载次数: 292)

2013-01-16 17:24 上传

点击文件名下载附件

create or replace procedure pro_xuqiu_yeshu
is

yy1 number;
yy2 number;
yy3 number;
yy4 number;
yy5 number;
yy6 number;
yy7 number;
yy8 number;
yy9 number;
yy10 number;
yy11 number;
yy12 number;
yy13 number;
yy14 number;
yy15 number;
yy16 number;
yy17 number;
yy18 number;
yy19 number;

yy20 number;
yy21 number;
yy22 number;
yy23 number;
yy24 number;
yy25 number;
yy26 number;
yy27 number;
yy28 number;
yy29 number;
yy30 number;
yy31 number;
yy32 number;
yy33 number;
yy34 number;
yy35 number;
yy36 number;
yy37 number;
yy38 number;

yy39 number;
yy40 number;
yy41 number;
yy42 number;
yy43 number;
yy44 number;
yy45 number;
yy46 number;
yy47 number;
yy48 number;
yy49 number;
yy50 number;
yy51 number;
yy52 number;
yy53 number;
yy54 number;
yy55 number;
yy56 number;
yy57 number;

begin
dbms_output.put_line('----------------0到10页---------------------------------');
select count(*) into yy1 from xuqiu t where t.xzilingyu='发卡业务' and t.xpage_number<11;
select count(*) into yy2 from xuqiu t where t.xzilingyu='收单业务' and t.xpage_number<11;
select count(*) into yy3 from xuqiu t where t.xzilingyu='风险管理' and t.xpage_number<11;
select count(*) into yy4 from xuqiu t where t.xzilingyu='客户服务' and t.xpage_number<11;
select count(*) into yy5 from xuqiu t where t.xzilingyu='数据仓库' and t.xpage_number<11;
select count(*) into yy6 from xuqiu t where t.xlingyu='零售客户关系管理' and t.xpage_number<11;
select count(*) into yy7 from xuqiu t where t.xlingyu='收单业务' and t.xpage_number<11;
select count(*) into yy8 from xuqiu t where t.xlingyu='柜面业务' and t.xpage_number<11;
select count(*) into yy9 from xuqiu t where t.xlingyu='自助设备' and t.xpage_number<11;
select count(*) into yy10 from xuqiu t where t.xlingyu='个人网银' and t.xpage_number<11;
select count(*) into yy11 from xuqiu t where t.xlingyu='一网通' and t.xpage_number<11;
select count(*) into yy12 from xuqiu t where t.xlingyu='电话银行' and t.xpage_number<11;
select count(*) into yy13 from xuqiu t where t.xlingyu='通知平台' and t.xpage_number<11;
select count(*) into yy14 from xuqiu t where t.xlingyu='零售中间业务' and t.xpage_number<11;
select count(*) into yy15 from xuqiu t where t.xlingyu='零售负债' and t.xpage_number<11;
select count(*) into yy16 from xuqiu t where t.xlingyu='零售支付结算' and t.xpage_number<11;
select count(*) into yy17 from xuqiu t where t.xlingyu='零售基础支持' and t.xpage_number<11;
select count(*) into yy18 from xuqiu t where t.xlingyu='I理财' and t.xpage_number<11;
select count(*) into yy19 from xuqiu t where t.xlingyu='电子商务' and t.xpage_number<11;
----
if yy1!=0 then dbms_output.put_line('信用卡发卡业务0到10页--'||yy1);end if;
if yy2!=0 then dbms_output.put_line('信用卡收单业务0到10页--'||yy2);end if;
if yy3!=0 then dbms_output.put_line('信用卡风险管理0到10页--'||yy3);end if;
if yy4!=0 then dbms_output.put_line('信用卡客户服务0到10页--'||yy4);end if;
if yy5!=0 then dbms_output.put_line('信用卡数据仓库0到10页--'||yy5);end if;
if yy6!=0 then dbms_output.put_line('零售客户关系管理0到10页--'||yy6);end if;
if yy7!=0 then dbms_output.put_line('收单业务0到10页--'||yy7);end if;
if yy8!=0 then dbms_output.put_line('柜面业务0到10页--'||yy;end if;
if yy9!=0 then dbms_output.put_line('自助设备0到10页--'||yy9);end if;
if yy10!=0 then dbms_output.put_line('个人网银0到10页--'||yy10);end if;
if yy11!=0 then dbms_output.put_line('一网通0到10页--'||yy11);end if;
if yy12!=0 then dbms_output.put_line('电话银行0到10页--'||yy12);end if;
if yy13!=0 then dbms_output.put_line('通知平台0到10页--'||yy13);end if;
if yy14!=0 then dbms_output.put_line('零售中间业务0到10页--'||yy14);end if;
if yy15!=0 then dbms_output.put_line('零售负债0到10页--'||yy15);end if;
if yy16!=0 then dbms_output.put_line('零售支付结算0到10页--'||yy16);end if;
if yy17!=0 then dbms_output.put_line('零售基础支持0到10页--'||yy17);end if;
if yy18!=0 then dbms_output.put_line('I理财0到10页--'||yy1;end if;
if yy19!=0 then dbms_output.put_line('电子商务0到10页--'||yy19);end if;
-------
dbms_output.put_line('----------------11到20页---------------------------------');
select count(*) into yy20 from xuqiu t where t.xzilingyu='发卡业务' and t.xpage_number>10 and t.xpage_number<21;
select count(*) into yy21 from xuqiu t where t.xzilingyu='收单业务' and t.xpage_number>10 and t.xpage_number<21;
select count(*) into yy22 from xuqiu t where t.xzilingyu='风险管理' and t.xpage_number>10 and t.xpage_number<21;
select count(*) into yy23 from xuqiu t where t.xzilingyu='客户服务' and t.xpage_number>10 and t.xpage_number<21;
select count(*) into yy24 from xuqiu t where t.xzilingyu='数据仓库' and t.xpage_number>10 and t.xpage_number<21;
select count(*) into yy25 from xuqiu t where t.xlingyu='零售客户关系管理' and t.xpage_number>10 and t.xpage_number<21;
select count(*) into yy26 from xuqiu t where t.xlingyu='收单业务' and t.xpage_number>10 and t.xpage_number<21;
select count(*) into yy27 from xuqiu t where t.xlingyu='柜面业务' and t.xpage_number>10 and t.xpage_number<21;
select count(*) into yy28 from xuqiu t where t.xlingyu='自助设备' and t.xpage_number>10 and t.xpage_number<21;
select count(*) into yy29 from xuqiu t where t.xlingyu='个人网银' and t.xpage_number>10 and t.xpage_number<21;
select count(*) into yy30 from xuqiu t where t.xlingyu='一网通' and t.xpage_number>10 and t.xpage_number<21;
select count(*) into yy31 from xuqiu t where t.xlingyu='电话银行' and t.xpage_number>10 and t.xpage_number<21;
select count(*) into yy32 from xuqiu t where t.xlingyu='通知平台' and t.xpage_number>10 and t.xpage_number<21;
select count(*) into yy33 from xuqiu t where t.xlingyu='零售中间业务' and t.xpage_number>10 and t.xpage_number<21;
select count(*) into yy34 from xuqiu t where t.xlingyu='零售负债' and t.xpage_number>10 and t.xpage_number<21;
select count(*) into yy35 from xuqiu t where t.xlingyu='零售支付结算' and t.xpage_number>10 and t.xpage_number<21;
select count(*) into yy36 from xuqiu t where t.xlingyu='零售基础支持' and t.xpage_number>10 and t.xpage_number<21;
select count(*) into yy37 from xuqiu t where t.xlingyu='I理财' and t.xpage_number>10 and t.xpage_number<21;
select count(*) into yy38 from xuqiu t where t.xlingyu='电子商务' and t.xpage_number>10 and t.xpage_number<21;
if yy20!=0 then dbms_output.put_line('信用卡发卡业务11到20页--'||yy20);end if;
if yy21!=0 then dbms_output.put_line('信用卡收单业务11到20页--'||yy21);end if;
if yy22!=0 then dbms_output.put_line('信用卡风险管理11到20页--'||yy22);end if;
if yy23!=0 then dbms_output.put_line('信用卡客户服务11到20页--'||yy23);end if;
if yy24!=0 then dbms_output.put_line('数据仓库11到20页--'||yy24);end if;
if yy25!=0 then dbms_output.put_line('信用卡零售客户关系管理11到20页--'||yy25);end if;
if yy26!=0 then dbms_output.put_line('收单业务11到20页--'||yy26);end if;
if yy27!=0 then dbms_output.put_line('柜面业务11到20页--'||yy27);end if;
if yy28!=0 then dbms_output.put_line('自助设备11到20页--'||yy2;end if;
if yy29!=0 then dbms_output.put_line('个人网银11到20页--'||yy29);end if;
if yy30!=0 then dbms_output.put_line('一网通11到20页--'||yy30);end if;
if yy31!=0 then dbms_output.put_line('电话银行11到20页--'||yy31);end if;
if yy32!=0 then dbms_output.put_line('通知平台11到20页--'||yy32);end if;
if yy33!=0 then dbms_output.put_line('零售中间业务11到20页--'||yy33);end if;
if yy34!=0 then dbms_output.put_line('零售负债11到20页--'||yy34);end if;
if yy35!=0 then dbms_output.put_line('零售支付结算11到20页--'||yy35);end if;
if yy36!=0 then dbms_output.put_line('零售基础支持11到20页--'||yy36);end if;
if yy37!=0 then dbms_output.put_line('I理财11到20页--'||yy37);end if;
if yy38!=0 then dbms_output.put_line('电子商务11到20页--'||yy3;end if;
dbms_output.put_line('----------------20页以上---------------------------------');
select count(*) into yy39 from xuqiu t where t.xzilingyu='发卡业务' and t.xpage_number>20;
select count(*) into yy40 from xuqiu t where t.xzilingyu='收单业务' and t.xpage_number>20;
select count(*) into yy41 from xuqiu t where t.xzilingyu='风险管理' and t.xpage_number>20;
select count(*) into yy42 from xuqiu t where t.xzilingyu='客户服务' and t.xpage_number>20;
select count(*) into yy43 from xuqiu t where t.xzilingyu='数据仓库' and t.xpage_number>20;
select count(*) into yy44 from xuqiu t where t.xlingyu='零售客户关系管理' and t.xpage_number>20;
select count(*) into yy45 from xuqiu t where t.xlingyu='收单业务' and t.xpage_number>20;
select count(*) into yy46 from xuqiu t where t.xlingyu='柜面业务' and t.xpage_number>20;
select count(*) into yy47 from xuqiu t where t.xlingyu='自助设备' and t.xpage_number>20;
select count(*) into yy48 from xuqiu t where t.xlingyu='个人网银' and t.xpage_number>20;
select count(*) into yy49 from xuqiu t where t.xlingyu='一网通' and t.xpage_number>20;
select count(*) into yy50 from xuqiu t where t.xlingyu='电话银行' and t.xpage_number>20;
select count(*) into yy51 from xuqiu t where t.xlingyu='通知平台' and t.xpage_number>20;
select count(*) into yy52 from xuqiu t where t.xlingyu='零售中间业务' and t.xpage_number>20;
select count(*) into yy53 from xuqiu t where t.xlingyu='零售负债' and t.xpage_number>20;
select count(*) into yy54 from xuqiu t where t.xlingyu='零售支付结算' and t.xpage_number>20;
select count(*) into yy55 from xuqiu t where t.xlingyu='零售基础支持' and t.xpage_number>20;
select count(*) into yy56 from xuqiu t where t.xlingyu='I理财' and t.xpage_number>20;
select count(*) into yy57 from xuqiu t where t.xlingyu='电子商务' and t.xpage_number>20;
if yy39!=0 then dbms_output.put_line('信用卡发卡业务大于20页--'||yy39);end if;
if yy40!=0 then dbms_output.put_line('信用卡收单业务大于20页--'||yy40);end if;
if yy41!=0 then dbms_output.put_line('信用卡风险管理大于20页--'||yy41);end if;
if yy42!=0 then dbms_output.put_line('信用卡客户服务大于20页--'||yy42);end if;
if yy43!=0 then dbms_output.put_line('信用卡数据仓库大于20页--'||yy43);end if;
if yy44!=0 then dbms_output.put_line('零售客户关系管理大于20页--'||yy44);end if;
if yy45!=0 then dbms_output.put_line('收单业务大于20页--'||yy45);end if;
if yy46!=0 then dbms_output.put_line('柜面业务大于20页--'||yy46);end if;
if yy47!=0 then dbms_output.put_line('自助设备大于20页--'||yy47);end if;
if yy48!=0 then dbms_output.put_line('个人网银大于20页--'||yy4;end if;
if yy49!=0 then dbms_output.put_line('一网通大于20页--'||yy49);end if;
if yy50!=0 then dbms_output.put_line('电话银行大于20页--'||yy50);end if;
if yy51!=0 then dbms_output.put_line('通知平台大于20页--'||yy51);end if;
if yy52!=0 then dbms_output.put_line('零售中间业务大于20页--'||yy52);end if;
if yy53!=0 then dbms_output.put_line('零售负债大于20页--'||yy53);end if;
if yy54!=0 then dbms_output.put_line('零售支付结算大于20页--'||yy54);end if;
if yy55!=0 then dbms_output.put_line('零售基础支持大于20页--'||yy55);end if;
if yy56!=0 then dbms_output.put_line('I理财大于20页--'||yy56);end if;
if yy57!=0 then dbms_output.put_line('电子商务大于20页--'||yy57);end if;
end;

linux_kaige

采用select sum(t.xdeal_time)  into  tt1 from table where t.department='xxx'赋值;如果tt1等于null的话，使用if tt1!=0 then dbms_output.put_line(tt1);end if;这样也可以。
如果使用if tt1!=null then dbms_output.put_line(tt1);end if;（等于if tt1!=null then dbms_output.put_line(tt1);else dbms_output.put_line('0')end if;）这样是不行的。任何值不能跟null进行对比，只能说某个值是null或者不是null。
如果想这样做，可以：if tt1 is not null then dbms_output.put_line(tt1);else dbms_output.put_line('0')end if;

linux_kaige

http://log-cd.iteye.com/blog/562052
http://bbs.chinaunix.net/thread-808211-1-1.html
http://soft.chinabyte.com/database/499/12362999.shtml

sunshuang

好。。。支持。正好需要。