易网时代-编程资源站

首页 / 数据库 / MySQL / Oracle游标存储过程语句

可以用Oracle已经存在的账号scott密码triger登陆进去用里面已存在的表来做试验。

create or replace procedure lpmtest2
as
para1 varchar2（10）;
cursor youbiao is select ename from test where sal>1300;
begin
open youbiao;
loop
fetch youbiao into para1;
exit when youbiao%notfound;
dbms_output.put_line（"++:"||para1）;
end loop;
close youbiao;
end;

代码

create or replace procedure lpmtest2
as
cursor youbiao is select ename,sal,job from test where sal>1300;
c_row youbiao%rowtype; --定义一个游标变量c_row ,该类型为游标youbiao中的一行数据类型
begin
open youbiao;
loop
fetch youbiao into c_row;
exit when youbiao%notfound;
dbms_output.put_line（"++:"||c_row.ename||":"||c_row.sal||":"||c_row.job）;
end loop;
close youbiao;
end;

代码

create or replace procedure lpmtest3
as
cursor c_dept is select * from dept order by deptno;
cursor c_emp（p_dept varchar2） is select ename,sal from emp where deptno=p_dept order by ename;
r_dept c_dept%rowtype;
v_ename emp.ename%type;
v_sal emp.sal%type;
v_totalsal emp.sal%type; --用来存每个部门所有员工的总工资
begin
open c_dept;
loop
fetch c_dept into r_dept;
exit when c_dept%notfound;
dbms_output.put_line（r_dept.deptno||":"||r_dept.dname||"+++++++++++"）;
v_totalsal:=0;
open c_emp（r_dept.deptno）;
loop
fetch c_emp into v_ename,v_sal;
exit when c_emp%notfound;
dbms_output.put_line（"v_ename:"||v_ename||";"||"v_sal:"||v_sal）;
v_totalsal:=v_totalsal+v_sal;
end loop;
close c_emp;
dbms_output.put_line（"deptsaltotal:"||v_totalsal）;
end loop;
close c_dept;
end;

打印出来效果：

10:ACCOUNTING+++++++++++
v_ename:CLARK;v_sal:2450
v_ename:KING;v_sal:5000
v_ename:MILLER;v_sal:1300
deptsaltotal:8750
20:RESEARCH+++++++++++
v_ename:ADAMS;v_sal:1100
v_ename:FORD;v_sal:3000
v_ename:JONES;v_sal:2975
v_ename:SCOTT;v_sal:3000
v_ename:SMITH;v_sal:800
deptsaltotal:10875
30:SALES+++++++++++
v_ename:ALLEN;v_sal:1600
v_ename:BLAKE;v_sal:2850
v_ename:JAMES;v_sal:950
v_ename:MARTIN;v_sal:1250
v_ename:TURNER;v_sal:1500
v_ename:WARD;v_sal:1250
deptsaltotal:9400
40:OPERATIONS+++++++++++
deptsaltotal:0

更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12参数cursor_sharing的设置导致含占位符的SQL执行变慢问题Oracle数据库短时间磁盘空间占用超过88%问题分析相关资讯 Oracle高级培训

delete表的数据后恢复（08/30/2012 08:59:58）
使用ASH信息，发现高CPUsession （08/14/2012 07:21:32）
如何阅读Oracle Errorstack Output （08/14/2012 07:15:47）

Oracle Apps Patching:adpatch（（08/16/2012 15:41:37）
话说V$SQL_MONITOR （08/14/2012 07:19:54）
Oracle Apps DBA工具:ADADMIN使用（08/14/2012 07:00:09）

本文评论查看全部评论（0）