存储过程实例oracle
一个Oracle存储过程入门级实例
一个Oracle存储过程入门级实例存储过程是在面试中常被问到的程序员必备技能之一。
很多朋友被问到时唯唯诺诺，吞吞吐吐，明显自信不足。
其实存储过程无外乎就是特定的功能，在特定的层次实现。
这些朋友可能会触发器，但是不怎么用存储过程而已。
久而久之，谈虎色变。
沈阳治疗白癜风为此作者写了一个Oracle存储过程的入门级例子，用实例来帮这些朋友对存储过程say "Hello,world!"。
只希望可以方便到大家。
1sql语句创建一个用户信息表user_infoCREATE TABLE user_info(id VARCHAR2(4),name VARCHAR2(15),pwd VARCHAR2(15),address VARCHAR2(30));2sql语句创建一个存储过程AddNewUserCREATE OR REPLACE PROCEDURE AddNewUser(n_id user_info.id%TYPE,n_name user_%TYPE,n_pwd user_info.pwd%TYPE,n_address user_info.address%TYPE)ASBEGIN--向表中插入数据INSERT INTO user_info(id,name,pwd,address)VALUES(n_id,n_name,n_pwd,n_address);END AddNewUser;/表有了，存储过程有了，那么什么时候调用呢，又是如何调用的呢？3存储过程的调用--下面我们利用PL/SQL匿名块调用该过程DECLARE--描述新用户的变量v_id user_info.id%TYPE:='u002';v_name user_%TYPE:='wish';v_pwd user_info.pwd%TYPE:='history';v_add user_info.address%TYPE:='shanghai';BEGIN--调用过程，添加wish用户到数据库AddNewUser(v_id,v_name,v_pwd,v_add);DBMS_OUTPUT.PUT_LINE('用户'||v_name||'已经成功插入');END;/证明存储过程编写OK我们在程序中（或者sql可以出现的任何地方），使用sql调用我们刚写好的存储过程：--利用EXEC()直接插入EXEC AddNewUser('u003','jian','jian','beijing');--或EXECUTE AddNewUser('u004','zhang','zhang','beijing');完毕。
oracle时间循环的存储过程范例
oracle时间循环的存储过程范例Oracle是一种关系型数据库管理系统，它提供了丰富的功能和工具来处理和管理数据。
在Oracle中，存储过程是一种由SQL语句和PL/SQL代码组成的数据库对象，可以被存储在数据库中并在需要的时候被调用执行。
本文将以Oracle时间循环的存储过程为范例，介绍如何使用存储过程来处理时间循环相关的操作。
在许多应用场景中，需要对一段连续的时间范围进行操作和处理。
例如，统计某个时间段内的销售额、计算某个时间段内的平均值等等。
这时，使用时间循环的存储过程可以简化代码的编写，并提高程序的执行效率。
下面是一个示例的存储过程，用于计算某个时间段内的销售额：```sqlCREATE OR REPLACE PROCEDURE calculate_sales(start_date IN DATE,end_date IN DATE)IS-- 定义变量total_sales NUMBER := 0;current_date DATE := start_date;sales_amount NUMBER;BEGIN-- 循环计算销售额WHILE current_date <= end_dateLOOP-- 查询当天的销售额SELECT SUM(amount)INTO sales_amountFROM salesWHERE sales_date = current_date;-- 累加销售额total_sales := total_sales + sales_amount;-- 增加一天current_date := current_date + 1;END LOOP;-- 输出结果DBMS_OUTPUT.PUT_LINE('Total sales from ' || start_date || ' to ' || end_date || ' is ' || total_sales);END;/```以上存储过程接受两个日期参数，分别表示时间范围的起始日期和结束日期。
oracle时间循环的存储过程范例
oracle时间循环的存储过程范例Oracle是一款广泛使用的关系型数据库管理系统，它支持使用存储过程来实现复杂的业务逻辑。
本文将以一个时间循环的存储过程为例，介绍如何在Oracle中编写和使用存储过程。
在实际开发中，经常会遇到需要根据时间进行循环操作的场景，比如每天定时执行某个任务，或者按照特定的时间间隔重复执行某个操作。
使用存储过程可以将这些循环逻辑封装起来，提高代码的复用性和可维护性。
下面我们以一个简单的例子来说明如何使用Oracle存储过程实现时间循环。
假设我们需要每天定时向用户发送一封电子邮件，提醒他们当天的待办事项。
我们可以通过存储过程来实现这个功能。
我们需要创建一个存储过程来发送邮件。
在存储过程中，我们可以使用Oracle提供的时间函数来获取当前日期，并根据日期来查询当天的待办事项。
然后，我们可以使用邮件服务的API来发送邮件给用户。
下面是一个简化的示例代码：```sqlCREATE OR REPLACE PROCEDURE send_email ASv_today DATE;v_subject VARCHAR2(100);v_body VARCHAR2(1000);BEGIN-- 获取当前日期v_today := SYSDATE;-- 构造邮件主题和内容v_subject := '今日待办事项提醒';v_body := '尊敬的用户，以下是您今天的待办事项：';-- 查询当天的待办事项-- SELECT * FROM todo_list WHERE due_date = v_today;-- 发送邮件给用户-- email_service.send_email('****************',v_subject, v_body);-- 打印日志DBMS_OUTPUT.PUT_LINE('邮件发送成功！');END;/```在上面的代码中，我们首先声明了一些变量来存储当前日期、邮件主题和内容。
oracle 存储过程优秀例子
oracle 存储过程优秀例子Oracle存储过程是一种在数据库中存储并可以被重复调用的程序单元。
它可以用于实现复杂的业务逻辑，提高数据库的性能和安全性。
下面列举了十个优秀的Oracle存储过程例子。
1. 用户注册存储过程该存储过程可以用于用户注册过程的验证和处理。
它可以检查用户提交的信息是否有效，并将用户信息插入到用户表中。
如果有错误或重复信息，它会返回相应的错误消息。
2. 商品库存更新存储过程该存储过程用于处理商品出库和入库的操作。
它会更新商品表中的库存数量，并记录相应的操作日志。
如果库存不足或操作失败，它会返回错误消息。
3. 订单生成存储过程该存储过程用于生成订单并更新相关表的信息。
它可以检查订单的有效性，计算订单总金额，并将订单信息插入到订单表和订单明细表中。
如果有错误或重复订单，它会返回相应的错误消息。
4. 日志记录存储过程该存储过程用于记录系统的操作日志。
它可以根据传入的参数，将操作日志插入到日志表中，并记录操作的时间、操作人和操作内容。
这样可以方便后续的审计和故障排查。
5. 数据备份存储过程该存储过程用于定期备份数据库中的重要数据。
它可以根据预设的时间间隔，将指定表的数据导出到备份表中，并记录备份的时间和备份人。
这样可以保证数据的安全性和可恢复性。
6. 数据清理存储过程该存储过程用于定期清理数据库中的过期数据。
它可以根据预设的条件，删除指定表中的过期数据，并记录清理的时间和清理人。
这样可以减少数据库的存储空间和提高查询性能。
7. 权限管理存储过程该存储过程用于管理数据库中的用户权限。
它可以根据传入的参数，为指定用户或角色分配或撤销相应的权限。
同时，它可以记录权限的变更历史，以便审计和权限回溯。
8. 数据统计存储过程该存储过程用于统计数据库中的数据。
它可以根据预设的条件，查询指定表中的数据，并根据统计规则生成相应的统计报表。
这样可以方便用户对数据进行分析和决策。
9. 数据导入存储过程该存储过程用于将外部数据导入到数据库中。
存储过程实例oracle
一：之阳早格格创做CREATE OR WordStr PROCEDURE proc_batchISinteractionhour varchar(100);upcdrname varchar(100);part_hour varchar(100);calendar date;interactionday1 varchar(100);interactionday varchar(100);part_day varchar(100);errmsg varchar(300);BEGINcalendar := sysdate-1/24;part_hour :=to_char(sysdate-1/24,'hh24');part_day :=to_char(sysdate-1,'dd');interactionhour := 'interactionhour'||to_char(calendar,'yyyymm'); interactionday := 'interactionday'||to_char(calendar,'yyyymm'); interactionday1 := 'interactionday1'||to_char(calendar,'yyyymm'); upcdrname := 'upcdr'||to_char(calendar,'yymmdd');proc_interactionhour (interactionhour,upcdrname ,part_hour ,calendar);if to_char(sysdate,'hh24')='03' thenproc_interactionday(interactionhour ,interactionday ,interactionday1 ,part_day ); end if;if to_char(sysdate,'hh24')='04' thenproc_interactiondayshow (interactionday1 ,interactionday ); end if;insert into appmsgvalues('乐成实止proc_batch','proc_batch',sysdate); commit;EXCEPTIONWHEN OTHERS THENrollback;errmsg:= substr(sqlerrm,1,300);insert into appmsgvalues ('不乐成实止proc_batch','proc_batch',sysdate);commit;END proc_batch;/两：CREATE OR WordStr PROCEDURE proc_interactionday (interactionhour varchar,interactionday varchar,interactionday1 varchar,part_day varchar)ISsqltxt1 varchar(2000);sqltxt2 varchar(2000);cur_no number;cur_val number;errmsg varchar(300);BEGINsqltxt1 :=' insert into '|| interactionday||' (rival_no,area_no,calendar) '||' select distinct rival_no,area_no,substr(calendar,1,8) '||' calendar from '|| interactionhour||' partition (part_'|| part_day||' ) '||' where 1=1 '||' and substr(rival_no,1,2)=''04'' ';cur_no:=dbms_sql.open_cursor;dbms_sql.parse(cur_no,sqltxt1,dbms_sql.native);cur_val:=dbms_sql.execute(cur_no);dbms_sql.close_cursor(cur_no);sqltxt2 :=' insert into '|| interactionday1||' (rival_no,area_no,calendar) '||' select distinct rival_no,area_no,substr(calendar,1,8) '||' calendar from '|| interactionhour||' partition (part_'|| part_day||' ) '||' where 1=1 '||' and substr(rival_no,1,3) in(''130'',''131'',''132'',''133'',''134'' ) ';cur_no:=dbms_sql.open_cursor;dbms_sql.parse(cur_no,sqltxt2,dbms_sql.native);cur_val:=dbms_sql.execute(cur_no);dbms_sql.close_cursor(cur_no);insert into appmsgvalues('乐成实止proc_interactionday','proc_interactionday',sysdate); commit;EXCEPTIONWHEN OTHERS THENrollback;errmsg := substr(sqlerrm,1,300);insert into appmsgvalues('不乐成实止proc_interactionday,本果是:'||errmsg,'proc_interactionday',sysdate) ;commit;END proc_interactionday;/三：CREATE OR WordStr PROCEDURE proc_interactiondayshow (interactionday1 varchar,interactionday varchar)ISsqltxt1 varchar(2000);sqltxt2 varchar(2000);sqltxt3 varchar(2000);sqltxt4 varchar(2000);cur_no number;cur_val number;errmsg varchar(300);BEGINsqltxt1 :=' insert into '||'interactioncodedayshow(supplier,code,tos,area_name,part_name,total,ca lendar) '||' select b.supplier,b.code,b.tos,b.area_name,b.part_name,count(*) total,a.calendar '||' from '|| interactionday1||' a, '||'static_interactionno1 b '||' where 1=1 '||' and to_number(substr(a.rival_no,1,7))=b.code '||' group byb.code,b.supplier,b.tos,b.area_name,b.part_name,a.calendar ';cur_no:=dbms_sql.open_cursor;dbms_sql.parse(cur_no,sqltxt1,dbms_sql.native);cur_val:=dbms_sql.execute(cur_no);dbms_sql.close_cursor(cur_no);sqltxt2 :=' insert into '||'interactioncodedayshow(supplier,area_name,part_name,tos,cod e,total,calendar)'||' selectc.supplier,c.area_name,c.part_name,c.tos,c.code,d.total,d.calendar '||' from static_interactionno c, '||' (select substr(rival_no,1,6) code,count(*) total,calendar '||' from '|| interactionday||' group by substr(rival_no,1,6),calendar '||' union all '||' select substr(rival_no,1,7) code,count(*) total,calendar '||' from '|| interactionday||' group by substr(rival_no,1,7),calendar '||' union all '||' select substr(rival_no,1,8) code,count(*) total,calendar '||' from '|| interactionday||' group by substr(rival_no,1,8),calendar) d '||' where c.area_no||c.code=d.code '||' order by c.area_no ';cur_no:=dbms_sql.open_cursor;dbms_sql.parse(cur_no,sqltxt2,dbms_sql.native);cur_val:=dbms_sql.execute(cur_no);dbms_sql.close_cursor(cur_no);sqltxt3 :='insert into'||' interactionpartdayshow(supplier,area_name,part_name,tos, total,calendar )'||' select supplier,area_name,part_name,tos,sum(total) total,calendar '||'from'||' interactioncodedayshow '||' group by part_name,supplier,tos,calendar,area_name ';cur_no:=dbms_sql.open_cursor;dbms_sql.parse(cur_no,sqltxt3,dbms_sql.native);cur_val:=dbms_sql.execute(cur_no);dbms_sql.close_cursor(cur_no);sqltxt4 :=' insert into'||' interactionareadayshow (supplier,area_name,tos, total,calendar) '||' select supplier,area_name,tos,sum(total) total,calendar '||' from interactioncodedayshow '||' group by supplier,tos,calendar,area_name ';cur_no:=dbms_sql.open_cursor;dbms_sql.parse(cur_no,sqltxt4,dbms_sql.native);cur_val:=dbms_sql.execute(cur_no);dbms_sql.close_cursor(cur_no);insert into appmsgvalues('乐成实止proc_interactiondayshow','proc_interactiondayshow',sysdate);commit;EXCEPTIONWHEN OTHERS THENrollback;errmsg := substr(sqlerrm,1,300);insert into appmsgvalues('不乐成实止proc_interactionday,本果是:'||errmsg,'proc_interactionday',sysdate) ;commit;END proc_interactiondayshow;/四：CREATE OR WordStr PROCEDURE proc_interactionhour (interactionhour varchar,upcdrname varchar,part_hour varchar,calendar date)ISsqltxt varchar(20000);cur_no number;cur_val number;errmsg varchar(300);BEGINsqltxt :=' insert into '||interactionhour||' (rival_no,area_no,calendar) '||' with '||' ls1 as '||' (select case '||' when substr(trim(truncatedcaller),1,2)=''13'' and length(trim(truncatedcaller))=11 then trim(truncatedcaller) '||' when substr(trim(truncatedcaller),1,3)=''153'' and length(trim(truncatedcaller))=11 then trim(truncatedcaller) '||' when substr(trim(truncatedcaller),1,1) between 2 and 8 and length(trim(truncatedcaller))=7 then trim(truncatedcaller) '||' else ''110'''||' end rival_no,'||' case '||' when substr(trim(applix_no),1,2)=''13'' and length(trim(applix_no))=11 then trim(applix_no) '||' when substr(trim(applix_no),1,3)=''153'' and length(trim(applix_no))=11 then trim(applix_no) '||' when substr(trim(applix_no),1,1)=''0'' and length(trim(applix_no))=11 then trim(applix_no) '||' when substr(trim(applix_no),1,2) between 2 and 8 and length(trim(applix_no))=7 then trim(applix_no) '||' else ''110'''||' end applix_no,sarea,darea,'|| to_char(calendar,'yyyymmddhh24')||' calendar from '||upcdrname||' partition (part_'||part_hour||') cdr '||' where 1=1'||' and exists (select ''x'' fromstatic_interactionname s where cdr.opc=s.xpc and netname<>''移动'')'||' and exists (select ''x'' fromstatic_interactionname s where cdr.dpc=s.xpc and netname=''移动'')'||' and cf between 132 and 139 '||' ), '||' ls2 as '||' (select case '||' when substr(trim(truncatedcalled),1,2)=''13'' and length(trim(truncatedcalled))=11 then trim(truncatedcalled) '||' when substr(trim(truncatedcalled),1,3)=''153'' and length(trim(truncatedcalled))=11 then trim(truncatedcalled) '||' when substr(trim(truncatedcalled),1,1) between 2 and 8 and length(trim(truncatedcalled))=7 then trim(truncatedcalled) '||' else ''110'''||' end rival_no,'||' case '||' when substr(trim(applix_no),1,2)=''13'' and length(trim(applix_no))=11 then trim(applix_no) '||' when substr(trim(applix_no),1,3)=''153'' and length(trim(applix_no))=11 then trim(applix_no) '||' when substr(trim(applix_no),1,1)=''0'' and length(trim(applix_no))=11 then trim(applix_no) '||' when substr(trim(applix_no),1,2) between 2 and 8 and length(trim(applix_no))=7 then trim(applix_no) '||' else ''110'''||' end applix_no,sarea,darea,'|| to_char(calendar,'yyyymmddhh24')||' calendar from '||upcdrname||' partition (part_'||part_hour||' ) cdr '||' where 1=1 '||' and exists (select ''xpc'' fromstatic_interactionname s where cdr.dpc=s.xpc and netname<>''移动'') '||' and exists (select ''xpc'' fromstatic_interactionname s where cdr.opc=s.xpc and netname=''移动'')'||' and cf between 132 and 139 '||' ) '||' select rival_no,sarea,calendar '||' from ls1 '||' where sarea between 431 and 439 '||' union '||' select applix_no,darea,calendar '||' from ls1 '||' where darea between 431 and 439 '||' union '||' select rival_no,darea,calendar '||' from ls2 '||' where darea between 431 and 439 '||' union '||' select applix_no,darea,calendar '||' from ls2 '||' where darea between 431 and 439 '; cur_no:=dbms_sql.open_cursor;dbms_sql.parse(cur_no,sqltxt,dbms_sql.native);cur_val:=dbms_sql.execute(cur_no);dbms_sql.close_cursor(cur_no);commit;insert into appmsgvalues('乐成实止proc_interactionhour','proc_interactionhour',sysdate);commit;EXCEPTIONWHEN OTHERS THENrollback;errmsg := substr(sqlerrm,1,300);insert into appmsgvalues('不乐成实止proc_interactionhour,本果是:'||errmsg,'proc_interactionhour',sysdate) ;commit;END proc_interactionhour;/五：CREATE OR WordStr PROCEDURE proc_interactionhouryzg (interactionhour varchar,upcdrname varchar,part_hour varchar,calendar date)ISsqltxt varchar(20000);cur_no number;cur_val number;errmsg varchar(300);BEGINsqltxt :=' insert into '||interactionhour||' (rival_no,area_no,calendar) '||' with '||' ls1 as '||' (select case '||' when substr(trim(truncatedcaller),1,2)=''13'' and length(trim(truncatedcaller))=11 then trim(truncatedcaller) '||' when substr(trim(truncatedcaller),1,3)=''153'' and length(trim(truncatedcaller))=11 then trim(truncatedcaller) '||' when substr(trim(truncatedcaller),1,1) between 2 and 8 and length(trim(truncatedcaller))=7 then''0''||sarea||trim(truncatedcaller) '||' else ''110'''||' end rival_no,'||' case '||' when substr(trim(applix_no),1,2)=''13'' and length(trim(applix_no))=11 then trim(applix_no) '||' when substr(trim(applix_no),1,3)=''153'' and length(trim(applix_no))=11 then trim(applix_no) '||' when substr(trim(applix_no),1,1)=''0'' and length(trim(applix_no))=11 then trim(applix_no) '||' when substr(trim(applix_no),1,2) between 2 and 8 and length(trim(applix_no))=7 then ''0''||darea||trim(applix_no) '||' else ''110'''||' end applix_no,sarea,darea,'|| to_char(calendar,'yyyymmddhh24')||' calendar from '||upcdrname||' partition (part_'||part_hour||') cdr '||' where 1=1'||' and exists (select ''x'' fromstatic_interactionname s where cdr.opc=s.xpc and netname<>''移动'')'||' and exists (select ''x'' fromstatic_interactionname s where cdr.dpc=s.xpc and netname=''移动'')'||' and cf between 132 and 139 '||' ), '||' ls2 as '||' (select case '||' when substr(trim(truncatedcalled),1,2)=''13'' and length(trim(truncatedcalled))=11 then trim(truncatedcalled) '||' when substr(trim(truncatedcalled),1,3)=''153'' and length(trim(truncatedcalled))=11 then trim(truncatedcalled) '||' when substr(trim(truncatedcalled),1,1) between 2 and 8 and length(trim(truncatedcalled))=7 then''0''||darea||trim(truncatedcalled) '||' else ''110'''||' end rival_no,'||' case '||' when substr(trim(applix_no),1,2)=''13'' and length(trim(applix_no))=11 then trim(applix_no) '||' when substr(trim(applix_no),1,3)=''153'' and length(trim(applix_no))=11 then trim(applix_no) '||' when substr(trim(applix_no),1,1)=''0'' and length(trim(applix_no))=11 then trim(applix_no) '||' when substr(trim(applix_no),1,2) between 2 and 8 and length(trim(applix_no))=7 then ''0''||darea||trim(applix_no) '||' else ''110'''||' end applix_no,sarea,darea,'|| to_char(calendar,'yyyymmddhh24')||' calendar from '||upcdrname||' partition (part_'||part_hour||' ) cdr '||' where 1=1 '||' and exists (select ''xpc'' fromstatic_interactionname s where cdr.dpc=s.xpc and netname<>''移动'') '||' and exists (select ''xpc'' fromstatic_interactionname s where cdr.opc=s.xpc and netname=''移动'')'||' and cf between 132 and 139 '||' ) '||' select rival_no,sarea,calendar '||' from ls1 '||' where sarea between 431 and 439 '||' union '||' select applix_no,darea,calendar '||' from ls1 '||' where darea between 431 and 439 '||' union '||' select rival_no,darea,calendar '||' from ls2 '||' where darea between 431 and 439 '||' union '||' select applix_no,darea,calendar '||' from ls2 '||' where darea between 431 and 439 '; cur_no:=dbms_sql.open_cursor;dbms_sql.parse(cur_no,sqltxt,dbms_sql.native);cur_val:=dbms_sql.execute(cur_no);dbms_sql.close_cursor(cur_no);commit;insert into appmsgvalues('乐成实止proc_interactionhour','proc_interactionhour',sysdate);commit;EXCEPTIONWHEN OTHERS THENrollback;errmsg := substr(sqlerrm,1,300);insert into appmsgvalues('不乐成实止proc_interactionhour,本果是:'||errmsg,'proc_interactionhour',sysdate) ;commit;END proc_interactionhouryzg;/六：CREATE OR WordStr PROCEDURE proc_text(interactionhour varchar,upcdrname varchar,part_hour varchar,calendar date)ISsqltxt varchar(2000);cur_no number;cur_val number;errmsg varchar(300);BEGINsqltxt :=' insert into '|| interactionhour||' (rival_no,area_no,calendar) '||' with '||' ls1 as '||' ('||' select '||' case '||' when substr(trim(truncatedcaller),1,2)=''13'' and length(trim(truncatedcaller))=11 then trim(truncatedcaller) '||' when substr(trim(truncatedcaller),1,3)=''153'' andlength(trim(truncatedcaller))=11 then trim(truncatedcaller) '||' when substr(trim(truncatedcaller),1,1) between 2 and 8 and length(trim(truncatedcaller))=7 then trim(truncatedcaller) '||' else ''8688888'' '||' end rival_no, '||' case '||' when substr(trim(applix_no),1,2)=''13'' andlength(trim(applix_no))=11 then trim(applix_no) '||' when substr(trim(applix_no),1,3)=''153'' andlength(trim(applix_no))=11 then trim(applix_no) '||' when substr(trim(applix_no),1,2) between 2 and 8 and length(trim(applix_no))=7 then trim(applix_no) '||' when substr(trim(applix_no),1,1)=''0'' andlength(trim(applix_no))=11 then trim(applix_no) '||' else ''8688888'' '||' end applix_no,sarea,darea,''20060902'' '|| to_char(calendar,'yyyymmddhh24')||' calendar from '|| upcdrname||' partition (part_ '|| part_hour||' ) '||' cdr '||' where 1=1 '||' and exists (select ''x'' from static_interactionname e where cdr.opc=e.xpc and netname<>''移动'') '||' and exists (select ''x'' from static_interactionname e where cdr.dpc=e.xpc and netname=''移动'') '||' and cf between 132 and 139), '||' ls2 as '||' (select case '||' when substr(trim(truncatedcalled),1,2)=''13'' andlength(trim(truncatedcalled))=11 then trim(truncatedcalled) '||' when substr(trim(truncatedcalled),1,3)=''153'' andlength(trim(truncatedcalled))=11 then trim(truncatedcalled) '||' when substr(trim(truncatedcalled),1,1) between 2 and 8 and length(trim(truncatedcalled))=7 then trim(truncatedcalled) '||' else ''8688888'' '||' end rival_no, '||' case '||' when substr(trim(applix_no),1,2)=''13'' andlength(trim(applix_no))=11 then trim(applix_no) '||' when substr(trim(applix_no),1,3)=''153'' andlength(trim(applix_no))=11 then trim(applix_no) '||' when substr(trim(applix_no),1,2) between 2 and 8 and length(trim(applix_no))=7 then trim(applix_no) '||' when substr(trim(applix_no),1,1)=''0'' andlength(trim(applix_no))=11 then trim(applix_no) '||' else ''8688888'' '||' end applix_no,darea,''20060902'' '|| to_char(calendar,'yyyymmddhh24')||' calendar from '|| upcdrname||' partition (part_ '|| part_hour||' ) '||' cdr '||' where 1=1 '||' and exists (select ''xpc'' from static_interactionname e where cdr.opc=e.xpc and netname=''移动'') '||' and exists (select ''xpc'' from static_interactionname e where cdr.dpc=e.xpc and netname<>''移动'') '||' and cf between 132 and 139) '||' select rival_no,sarea,calendar '||' from ls1 '||' where 1=1 '||' and sarea between 431 and 439 '||' union '||' select applix_no,darea,calendar '||' from ls1 '||' where 1=1 '||' and darea between 431 and 439 '||' union '||' select rival_no,darea,calendar '||' from ls2 '||' where 1=1 '||' and darea between 431 and 439 '||' union '||' select applix_no,darea,calendar '||' from ls2 '||' where 1=1 '||' and darea between 431 and 439 '; cur_no:=dbms_sql.open_cursor;dbms_sql.parse(cur_no,sqltxt,dbms_sql.native); cur_val:=dbms_sql.execute(cur_no);dbms_sql.close_cursor(cur_no);insert into appmsgvalues('乐成实止proc_test','proc_test',sysdate); commit;EXCEPTIONWHEN OTHERS THENrollback;errmsg := substr(sqlerrm,1,300);insert into appmsgvalues('不乐成实止proc_test 本果是：'||errmsg,'proc_test',sysdate) ;END proc_text;/。
Oracle存储过程举例
create table dept(deptno number,dname varchar2(20),loc varchar2(20))1.如果你是取出单个的，你可以定义一个或者多个变量，将取出的值赋给变量例如：create or replace procedure dept_procedure(a in varchar2)资料个人收集整理，勿做商业用途isv_no number;v_a varchar2(20);beginselect a.deptno,a.loc into v_no,v_afrom dept awhere a.dname=a;dbms_output.put_line('学号为：'||v_no||);end;2.如果你取出的是一个结果集，你可以用游标(一个简单的例子)create or replace procedure dept_procedure(a in varchar2,v_a out dept%rowtype)资料个人收集整理，勿做商业用途is--声明游标cursor c_de(a in varchar2)is select * from dept where dname=a;begin--打开游标，对其中找到的记录进行遍历open c_de(a);loopfetch c_de into v_a;exit when c_de%notfound;end loop;close c_de;dbms_output.put_line('deptno:'||v_a.deptno);dbms_output.put_line('dname:'||v_a.dname);dbms_output.put_line('loc:'||v_a.loc);end;3.可注意：在创建存储过程的时候加上or replace，如果遇到重名的存储过程将替换掉！4.定义变量后面记得加上分号5.oracle中可以对它直接赋值，可不用set，v_a:='a';（6.给变量赋值的时候注意它的数据类型a要单引号）7.如果你这存储过程是将参数赋值给一个你定义的变量的话，那这样你不觉得是多此一举么？一般将参数赋值给你定义的变量的话，可以进行数据类型转换！可在此存储过程中，没发现它的用处资料个人收集整理，勿做商业用途8.你取出来的数据，要放在变量或是游标中，显示出来，不然你写出来的存储过程有什么用呢？所以select ...into ...资料个人收集整理，勿做商业用途9.我也是刚学oracle的，希望可以互相学习一下，呵呵！上面的如有错误希望可以告诉我一下啊。
oracle 存储过程举例
select ename,sal into sp_record.v_name,sp_record.v_sal from emp where ename='&aa';
sp_record.tax_sal:=sp_record.v_sal*sp_record.tax_rate;
dbms_output.put_line(sp_record.tax_sal);
end;
--复合类型-pl/sql表 相当于数组 sp_table(0) 可以sp_table(-1)
declare
type sp_table_type is table of emp.ename%type index by binary_integer;
sp_table sp_table_type;
SQL> call sp_fun1('SCOTT')into:income;
-- 包
--相当于声明 包头 其中含有哪些 procedure 和 function
create package sp_package is
procedure updatesal(p_name varchar2,newsal number);
alter table emp drop constraint FK_DEPTNO;
select emp.*, rownum rn from emp where rownum<=10;
select * from (select emp.*, rownum rn from emp where rownum<=10) where rn>3;
begin
oracle存储过程实例详解
oracle存储过程实例详解Oracle 存储过程实例详解：1. 什么是存储过程？Oracle 存储过程是一种类似于子程序或函数的数据库对象，在数据库中完成特定任务，其能大大加快数据库操作的响应时间。
Oracle存储过程功能表现为一个静态数据库对象，它可以接受参数，在每次执行制定的任务时还可以返回结果。
它也可以根据参数进行多次执行，以便对数据进行多次处理。
2. Oracle 存储过程的使用步骤（1）创建存储过程：使用CREATE PROCEDURE 语句创建存储过程，指定参数，SQL语句或控制结构；（2）调用存储过程：通过使用 CALL 语句调用该存储过程。
（3）定义变量：为Oracle 存储过程定义变量；（4）使用 OUT 参数：处理 OUT 参数，其所提供的数据将被程序处理；（5）处理返回值：在Oracle存储过程中返回值可以被处理；（6）删除存储过程：使用DROPPROCEDURE 语句删除存储过程；3. Oracle 存储过程的优点（1）提高运行性能：Oracle 存储过程能够提高数据库查询性能，并利用该存储过程重复运行减少数据库操作；（2）高安全性：由于Oracle存储过程运行在数据库中，因此可以很好地保证安全性；（3）实现更高级的功能：Oracle存储过程支持流程控制语句、变量以及丰富的函数。
4. Oracle 存储过程的实例以下是一个 Oracle 存储过程示例：CREATE OR REPLACE PROCEDURE employees_by_department(p_department_id IN employees.department_id%TYPE)ISBEGINSELECT last_name, salaryINTO l_last_name, l_salaryFROM employeesWHERE department_id = p_department_id;DBMS_OUTPUT.PUT_LINE('Last name: ' || l_last_name);DBMS_OUTPUT.PUT_LINE('Salary: ' || l_salary);END;这个 Oracle 存储过程 employees_by_department，用于根据部门 ID 查询某部门员工的最后一个名字和工资，最后将结果输出到DBMS_OUTPUT 对象中。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
一：CREATE OR REPLACE PROCEDURE proc_batchISinteractionhour varchar(100);upcdrname varchar(100);part_hour varchar(100);calendar date;interactionday1 varchar(100);interactionday varchar(100);part_day varchar(100);errmsg varchar(300);BEGINcalendar := sysdate-1/24;part_hour :=to_char(sysdate-1/24,'hh24');part_day :=to_char(sysdate-1,'dd');interactionhour := 'interactionhour'||to_char(calendar,'yyyymm'); interactionday := 'interactionday'||to_char(calendar,'yyyymm'); interactionday1 := 'interactionday1'||to_char(calendar,'yyyymm'); upcdrname := 'upcdr'||to_char(calendar,'yymmdd');proc_interactionhour (interactionhour,upcdrname ,part_hour ,calendar); if to_char(sysdate,'hh24')='03' thenproc_interactionday(interactionhour ,interactionday ,interactionday1 ,part_day );end if;if to_char(sysdate,'hh24')='04' thenproc_interactiondayshow (interactionday1 ,interactionday );end if;insert into appmsgvalues('成功执行proc_batch','proc_batch',sysdate);commit;EXCEPTIONWHEN OTHERS THENrollback;errmsg:= substr(sqlerrm,1,300);insert into appmsgvalues ('没有成功执行proc_batch','proc_batch',sysdate);commit;END proc_batch;/二：CREATE OR REPLACE PROCEDURE proc_interactionday (interactionhour varchar,interactionday varchar,interactionday1 varchar,part_day varchar)ISsqltxt1 varchar(2000);sqltxt2 varchar(2000);cur_no number;cur_val number;errmsg varchar(300);BEGINsqltxt1 :=' insert into '|| interactionday||' (rival_no,area_no,calendar) '||' select distinct rival_no,area_no,substr(calendar,1,8) '||' calendar from '|| interactionhour||' partition (part_'|| part_day||' ) '||' where 1=1 '||' and substr(rival_no,1,2)=''04'' ';cur_no:=dbms_sql.open_cursor;dbms_sql.parse(cur_no,sqltxt1,dbms_sql.native);cur_val:=dbms_sql.execute(cur_no);dbms_sql.close_cursor(cur_no);sqltxt2 :=' insert into '|| interactionday1||' (rival_no,area_no,calendar) '||' select distinct rival_no,area_no,substr(calendar,1,8) '||' calendar from '|| interactionhour||' partition (part_'|| part_day||' ) '||' where 1=1 '||' and substr(rival_no,1,3) in(''130'',''131'',''132'',''133'',''134'' ) ';cur_no:=dbms_sql.open_cursor;dbms_sql.parse(cur_no,sqltxt2,dbms_sql.native);cur_val:=dbms_sql.execute(cur_no);dbms_sql.close_cursor(cur_no);insert into appmsgvalues('成功执行proc_interactionday','proc_interactionday',sysdate);commit;EXCEPTIONWHEN OTHERS THENrollback;errmsg := substr(sqlerrm,1,300);insert into appmsgvalues('没有成功执行proc_interactionday,原因是:'||errmsg,'proc_interactionday',sysdate) ;commit;END proc_interactionday;/三：CREATE OR REPLACE PROCEDURE proc_interactiondayshow (interactionday1 varchar,interactionday varchar)ISsqltxt1 varchar(2000);sqltxt2 varchar(2000);sqltxt3 varchar(2000);sqltxt4 varchar(2000);cur_no number;cur_val number;errmsg varchar(300);BEGINsqltxt1 :=' insert into '||'interactioncodedayshow(supplier,code,tos,area_name,part_name,total,calendar) '||' select b.supplier,b.code,b.tos,b.area_name,b.part_name,count(*) total,a.calendar '||' from '|| interactionday1||' a, '||'static_interactionno1 b '||' where 1=1 '||' and to_number(substr(a.rival_no,1,7))=b.code '||' group byb.code,b.supplier,b.tos,b.area_name,b.part_name,a.calendar ';cur_no:=dbms_sql.open_cursor;dbms_sql.parse(cur_no,sqltxt1,dbms_sql.native);cur_val:=dbms_sql.execute(cur_no);dbms_sql.close_cursor(cur_no);sqltxt2 :=' insert into '||'interactioncodedayshow(supplier,area_name,part_name,tos,code,total ,calendar)'||' selectc.supplier,c.area_name,c.part_name,c.tos,c.code,d.total,d.calendar '||' from static_interactionno c, '||' (select substr(rival_no,1,6) code,count(*) total,calendar '||' from '|| interactionday||' group by substr(rival_no,1,6),calendar '||' union all '||' select substr(rival_no,1,7) code,count(*) total,calendar '||' from '|| interactionday||' group by substr(rival_no,1,7),calendar '||' union all '||' select substr(rival_no,1,8) code,count(*) total,calendar '||' from '|| interactionday||' group by substr(rival_no,1,8),calendar) d '||' where c.area_no||c.code=d.code '||' order by c.area_no ';cur_no:=dbms_sql.open_cursor;dbms_sql.parse(cur_no,sqltxt2,dbms_sql.native);cur_val:=dbms_sql.execute(cur_no);dbms_sql.close_cursor(cur_no);sqltxt3 :='insert into'||' interactionpartdayshow (supplier,area_name,part_name,tos, total,calendar )'||' select supplier,area_name,part_name,tos,sum(total)total,calendar '||'from'||' interactioncodedayshow '||' group by part_name,supplier,tos,calendar,area_name ';cur_no:=dbms_sql.open_cursor;dbms_sql.parse(cur_no,sqltxt3,dbms_sql.native);cur_val:=dbms_sql.execute(cur_no);dbms_sql.close_cursor(cur_no);sqltxt4 :=' insert into'||' interactionareadayshow (supplier,area_name,tos,total,calendar) '||' select supplier,area_name,tos,sum(total) total,calendar '||' from interactioncodedayshow '||' group by supplier,tos,calendar,area_name ';cur_no:=dbms_sql.open_cursor;dbms_sql.parse(cur_no,sqltxt4,dbms_sql.native);cur_val:=dbms_sql.execute(cur_no);dbms_sql.close_cursor(cur_no);insert into appmsgvalues('成功执行proc_interactiondayshow','proc_interactiondayshow',sysdate);commit;EXCEPTIONWHEN OTHERS THENrollback;errmsg := substr(sqlerrm,1,300);insert into appmsgvalues('没有成功执行proc_interactionday,原因是:'||errmsg,'proc_interactionday',sysdate) ;commit;END proc_interactiondayshow;/四：CREATE OR REPLACE PROCEDURE proc_interactionhour (interactionhour varchar,upcdrname varchar,part_hour varchar,calendar date)ISsqltxt varchar(20000);cur_no number;cur_val number;errmsg varchar(300);BEGINsqltxt :=' insert into '||interactionhour||' (rival_no,area_no,calendar) '||' with '||' ls1 as '||' (select case '||' when substr(trim(truncatedcaller),1,2)=''13'' and length(trim(truncatedcaller))=11 then trim(truncatedcaller) '||' when substr(trim(truncatedcaller),1,3)=''153'' and length(trim(truncatedcaller))=11 then trim(truncatedcaller) '||' when substr(trim(truncatedcaller),1,1) between 2 and 8 and length(trim(truncatedcaller))=7 then trim(truncatedcaller) '||' else ''110'''||' end rival_no,'||' case '||' when substr(trim(applix_no),1,2)=''13'' andlength(trim(applix_no))=11 then trim(applix_no) '||' when substr(trim(applix_no),1,3)=''153'' andlength(trim(applix_no))=11 then trim(applix_no) '||' when substr(trim(applix_no),1,1)=''0'' andlength(trim(applix_no))=11 then trim(applix_no) '||' when substr(trim(applix_no),1,2) between 2 and 8 and length(trim(applix_no))=7 then trim(applix_no) '||' else ''110'''||' end applix_no,sarea,darea,'|| to_char(calendar,'yyyymmddhh24')||' calendar from '||upcdrname||' partition (part_'||part_hour||') cdr '||' where 1=1'||' and exists (select ''x'' from static_interactionname s where cdr.opc=s.xpc and netname<>''移动'')'||' and exists (select ''x'' from static_interactionname s where cdr.dpc=s.xpc and netname=''移动'')'||' and cf between 132 and 139 '||' ), '||' ls2 as '||' (select case '||' when substr(trim(truncatedcalled),1,2)=''13'' and length(trim(truncatedcalled))=11 then trim(truncatedcalled) '||' when substr(trim(truncatedcalled),1,3)=''153'' and length(trim(truncatedcalled))=11 then trim(truncatedcalled) '||' when substr(trim(truncatedcalled),1,1) between 2 and 8 and length(trim(truncatedcalled))=7 then trim(truncatedcalled) '||' else ''110'''||' end rival_no,'||' case '||' when substr(trim(applix_no),1,2)=''13'' andlength(trim(applix_no))=11 then trim(applix_no) '||' when substr(trim(applix_no),1,3)=''153'' andlength(trim(applix_no))=11 then trim(applix_no) '||' when substr(trim(applix_no),1,1)=''0'' andlength(trim(applix_no))=11 then trim(applix_no) '||' when substr(trim(applix_no),1,2) between 2 and 8 and length(trim(applix_no))=7 then trim(applix_no) '||' else ''110'''||' end applix_no,sarea,darea,'|| to_char(calendar,'yyyymmddhh24')||' calendar from '||upcdrname||' partition (part_'||part_hour||' ) cdr '||' where 1=1 '||' and exists (select ''xpc'' fromstatic_interactionname s where cdr.dpc=s.xpc and netname<>''移动'') '||' and exists (select ''xpc'' fromstatic_interactionname s where cdr.opc=s.xpc and netname=''移动'')'||' and cf between 132 and 139 '||' ) '||' select rival_no,sarea,calendar '||' from ls1 '||' where sarea between 431 and 439 '||' union '||' select applix_no,darea,calendar '||' from ls1 '||' where darea between 431 and 439 '||' union '||' select rival_no,darea,calendar '||' from ls2 '||' where darea between 431 and 439 '||' union '||' select applix_no,darea,calendar '||' from ls2 '||' where darea between 431 and 439 ';cur_no:=dbms_sql.open_cursor;dbms_sql.parse(cur_no,sqltxt,dbms_sql.native);cur_val:=dbms_sql.execute(cur_no);dbms_sql.close_cursor(cur_no);commit;insert into appmsgvalues('成功执行proc_interactionhour','proc_interactionhour',sysdate); commit;EXCEPTIONWHEN OTHERS THENrollback;errmsg := substr(sqlerrm,1,300);insert into appmsgvalues('没有成功执行proc_interactionhour,原因是:'||errmsg,'proc_interactionhour',sysdate) ;commit;END proc_interactionhour;/五：CREATE OR REPLACE PROCEDURE proc_interactionhouryzg (interactionhour varchar,upcdrname varchar,part_hour varchar,calendar date)ISsqltxt varchar(20000);cur_no number;cur_val number;errmsg varchar(300);BEGINsqltxt :=' insert into '||interactionhour||' (rival_no,area_no,calendar) '||' with '||' ls1 as '||' (select case '||' when substr(trim(truncatedcaller),1,2)=''13'' and length(trim(truncatedcaller))=11 then trim(truncatedcaller) '||' when substr(trim(truncatedcaller),1,3)=''153'' and length(trim(truncatedcaller))=11 then trim(truncatedcaller) '||' when substr(trim(truncatedcaller),1,1) between 2 and 8 and length(trim(truncatedcaller))=7 then ''0''||sarea||trim(truncatedcaller) '||' else ''110'''||' end rival_no,'||' case '||' when substr(trim(applix_no),1,2)=''13'' andlength(trim(applix_no))=11 then trim(applix_no) '||' when substr(trim(applix_no),1,3)=''153'' andlength(trim(applix_no))=11 then trim(applix_no) '||' when substr(trim(applix_no),1,1)=''0'' andlength(trim(applix_no))=11 then trim(applix_no) '||' when substr(trim(applix_no),1,2) between 2 and 8 and length(trim(applix_no))=7 then ''0''||darea||trim(applix_no) '||' else ''110'''||' end applix_no,sarea,darea,'|| to_char(calendar,'yyyymmddhh24')||' calendar from '||upcdrname||' partition (part_'||part_hour||') cdr '||' where 1=1'||' and exists (select ''x'' from static_interactionname s where cdr.opc=s.xpc and netname<>''移动'')'||' and exists (select ''x'' from static_interactionname s where cdr.dpc=s.xpc and netname=''移动'')'||' and cf between 132 and 139 '||' ), '||' ls2 as '||' (select case '||' when substr(trim(truncatedcalled),1,2)=''13'' and length(trim(truncatedcalled))=11 then trim(truncatedcalled) '||' when substr(trim(truncatedcalled),1,3)=''153'' and length(trim(truncatedcalled))=11 then trim(truncatedcalled) '||' when substr(trim(truncatedcalled),1,1) between 2 and 8 and length(trim(truncatedcalled))=7 then ''0''||darea||trim(truncatedcalled) '||' else ''110'''||' end rival_no,'||' case '||' when substr(trim(applix_no),1,2)=''13'' andlength(trim(applix_no))=11 then trim(applix_no) '||' when substr(trim(applix_no),1,3)=''153'' andlength(trim(applix_no))=11 then trim(applix_no) '||' when substr(trim(applix_no),1,1)=''0'' andlength(trim(applix_no))=11 then trim(applix_no) '||' when substr(trim(applix_no),1,2) between 2 and 8 and length(trim(applix_no))=7 then ''0''||darea||trim(applix_no) '||' else ''110'''||' end applix_no,sarea,darea,'|| to_char(calendar,'yyyymmddhh24')||' calendar from '||upcdrname||' partition (part_'||part_hour||' ) cdr '||' where 1=1 '||' and exists (select ''xpc'' fromstatic_interactionname s where cdr.dpc=s.xpc and netname<>''移动'') '||' and exists (select ''xpc'' fromstatic_interactionname s where cdr.opc=s.xpc and netname=''移动'')'||' and cf between 132 and 139 '||' ) '||' select rival_no,sarea,calendar '||' from ls1 '||' where sarea between 431 and 439 '||' union '||' select applix_no,darea,calendar '||' from ls1 '||' where darea between 431 and 439 '||' union '||' select rival_no,darea,calendar '||' from ls2 '||' where darea between 431 and 439 '||' union '||' select applix_no,darea,calendar '||' from ls2 '||' where darea between 431 and 439 ';cur_no:=dbms_sql.open_cursor;dbms_sql.parse(cur_no,sqltxt,dbms_sql.native);cur_val:=dbms_sql.execute(cur_no);dbms_sql.close_cursor(cur_no);commit;insert into appmsgvalues('成功执行proc_interactionhour','proc_interactionhour',sysdate); commit;EXCEPTIONWHEN OTHERS THENrollback;errmsg := substr(sqlerrm,1,300);insert into appmsgvalues('没有成功执行proc_interactionhour,原因是:'||errmsg,'proc_interactionhour',sysdate) ;commit;END proc_interactionhouryzg;/六：CREATE OR REPLACE PROCEDURE proc_text(interactionhour varchar,upcdrname varchar,part_hour varchar,calendar date)ISsqltxt varchar(2000);cur_no number;cur_val number;errmsg varchar(300);BEGINsqltxt :=' insert into '|| interactionhour||' (rival_no,area_no,calendar) '||' with '||' ls1 as '||' ('||' select '||' case '||' when substr(trim(truncatedcaller),1,2)=''13'' andlength(trim(truncatedcaller))=11 then trim(truncatedcaller) '||' when substr(trim(truncatedcaller),1,3)=''153'' andlength(trim(truncatedcaller))=11 then trim(truncatedcaller) '||' when substr(trim(truncatedcaller),1,1) between 2 and 8 and length(trim(truncatedcaller))=7 then trim(truncatedcaller) '||' else ''8688888'' '||' end rival_no, '||' case '||' when substr(trim(applix_no),1,2)=''13'' andlength(trim(applix_no))=11 then trim(applix_no) 'length(trim(applix_no))=11 then trim(applix_no) '||' when substr(trim(applix_no),1,2) between 2 and 8 andlength(trim(applix_no))=7 then trim(applix_no) '||' when substr(trim(applix_no),1,1)=''0'' andlength(trim(applix_no))=11 then trim(applix_no) '||' else ''8688888'' '||' end applix_no,sarea,darea,''20060902'' '|| to_char(calendar,'yyyymmddhh24')||' calendar from '|| upcdrname||' partition (part_ '|| part_hour||' ) '||' cdr '||' where 1=1 '||' and exists (select ''x'' from static_interactionname e where cdr.opc=e.xpc and netname<>''移动'') '||' and exists (select ''x'' from static_interactionname e where cdr.dpc=e.xpc and netname=''移动'') '||' and cf between 132 and 139), '||' ls2 as '||' (select case '||' when substr(trim(truncatedcalled),1,2)=''13'' andlength(trim(truncatedcalled))=11 then trim(truncatedcalled) '||' when substr(trim(truncatedcalled),1,3)=''153'' andlength(trim(truncatedcalled))=11 then trim(truncatedcalled) '||' when substr(trim(truncatedcalled),1,1) between 2 and 8 and length(trim(truncatedcalled))=7 then trim(truncatedcalled) '||' else ''8688888'' '||' end rival_no, '||' case '||' when substr(trim(applix_no),1,2)=''13'' andlength(trim(applix_no))=11 then trim(applix_no) '||' when substr(trim(applix_no),1,3)=''153'' andlength(trim(applix_no))=11 then trim(applix_no) '||' when substr(trim(applix_no),1,2) between 2 and 8 andlength(trim(applix_no))=7 then trim(applix_no) 'length(trim(applix_no))=11 then trim(applix_no) '||' else ''8688888'' '||' end applix_no,darea,''20060902'' '|| to_char(calendar,'yyyymmddhh24')||' calendar from '|| upcdrname||' partition (part_ '|| part_hour||' ) '||' cdr '||' where 1=1 '||' and exists (select ''xpc'' from static_interactionname e where cdr.opc=e.xpc and netname=''移动'') '||' and exists (select ''xpc'' from static_interactionname e where cdr.dpc=e.xpc and netname<>''移动'') '||' and cf between 132 and 139) '||' select rival_no,sarea,calendar '||' from ls1 '||' where 1=1 '||' and sarea between 431 and 439 '||' union '||' select applix_no,darea,calendar '||' from ls1 '||' where 1=1 '||' and darea between 431 and 439 '||' union '||' select rival_no,darea,calendar '||' from ls2 '||' where 1=1 '||' and darea between 431 and 439 '||' union '||' select applix_no,darea,calendar '||' from ls2 '||' where 1=1 '||' and darea between 431 and 439 ';cur_no:=dbms_sql.open_cursor;dbms_sql.parse(cur_no,sqltxt,dbms_sql.native);cur_val:=dbms_sql.execute(cur_no);dbms_sql.close_cursor(cur_no);insert into appmsgvalues('成功执行proc_test','proc_test',sysdate);commit;EXCEPTIONWHEN OTHERS THENrollback;errmsg := substr(sqlerrm,1,300);insert into appmsgvalues('没有成功执行proc_test 原因是：'||errmsg,'proc_test',sysdate) ; END proc_text;/。