ORACLE存储过程，循环语法和游标（转） 博客分类： Oracle
原作者 https://www.cnblogs.com/lgx5/p/11297563.html
1、定义
所谓存储过程(Stored Procedure)，就是一组用于完成特定数据库功能的SQL语句集，该SQL语句集经过
编译后存储在数据库系统中。在使用时候，用户通过指定已经定义的存储过程名字并给出相应的存储过程参数
来调用并执行它，从而完成一个或一系列的数据库操作。
2、存储过程的创建
Oracle存储过程包含三部分：过程声明，执行过程部分，存储过程异常。
（1）无参存储过程语法
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
create or replace procedure NoParProas //声明
;
begin // 执行
;
exception//存储过程异常
when too_many_rows then
***
when no_data_found then
***
when others then
***
;
end; |
（2）带参存储过程实例
|
1
2
3
4
5
6
7
8
9
|
create or replace procedure queryempname(sfindno emp.empno%type) as sName emp.ename%type;
sjob emp.job%type;
begin ....
exception ....
end; |
（3）带参数存储过程含赋值方式
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
create or replace procedure runbyparmeters (isal in emp.sal%type,
sname out varchar,
sjob in out varchar)
as icount number;
begin
select count(*) into icount from emp where sal>isal and job=sjob;
if icount=1 then
....
else ....
end if;
exception when too_many_rows then
DBMS_OUTPUT.PUT_LINE('返回值多于1行');
when others then
DBMS_OUTPUT.PUT_LINE('在RUNBYPARMETERS过程中出错！');
end; |
其中参数IN表示输入参数，是参数的默认模式。
OUT表示返回值参数，类型可以使用任意Oracle中的合法类型。
OUT模式定义的参数只能在过程体内部赋值，表示该参数可以将某个值传递回调用他的过程
IN OUT表示该参数可以向该过程中传递值，也可以将某个值传出去。
（4）存储过程中游标定义使用
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
as //定义(游标一个可以遍历的结果集)
CURSOR cur_1 IS SELECT area_code,CMCODE,SUM(rmb_amt)/10000 rmb_amt_sn,
SUM(usd_amt)/10000 usd_amt_sn
FROM BGD_AREA_CM_M_BASE_T
WHERE ym >= vs_ym_sn_beg
AND ym <= vs_ym_sn_end
GROUP BY area_code,CMCODE;
begin //执行（常用For语句遍历游标）
FOR rec IN cur_1 LOOP UPDATE xxxxxxxxxxx_T
SET rmb_amt_sn = rec.rmb_amt_sn,usd_amt_sn = rec.usd_amt_sn
WHERE area_code = rec.area_code
AND CMCODE = rec.CMCODE
AND ym = is_ym;
END LOOP; |
（5）游标的定义
|
1
2
3
4
5
6
7
8
9
10
11
12
|
--显示cursor的处理declare ---声明cursor,创建和命名一个sql工作区cursor cursor_name is select real_name from account_hcz;
v_realname varchar2(20);
begin open cursor_name;---打开cursor,执行sql语句产生的结果集
fetch cursor_name into v_realname;--提取cursor,提取结果集中的记录
dbms_output.put_line(v_realname);
close cursor_name;--关闭cursor
end; |
3、在Oracle中对存储过程的调用
（1）过程调用方式一
|
1
2
3
4
5
6
7
8
9
10
11
|
declare realsal emp.sal%type;
realname varchar(40);
realjob varchar(40);
begin //过程调用开始
realsal:=1100;
realname:='';
realjob:='CLERK';
runbyparmeters(realsal,realname,realjob);－－必须按顺序
DBMS_OUTPUT.PUT_LINE(REALNAME||' '||REALJOB);
END; //过程调用结束
|
（2）过程调用方式二
|
1
2
3
4
5
6
7
8
9
10
11
12
|
declare realsal emp.sal%type;
realname varchar(40);
realjob varchar(40);
begin //过程调用开始
realsal:=1100;
realname:='';
realjob:='CLERK';
－－指定值对应变量顺序可变
runbyparmeters(sname=>realname,isal=>realsal,sjob=>realjob);
DBMS_OUTPUT.PUT_LINE(REALNAME||' '||REALJOB);
END; //过程调用结束
|
（3）过程调用方式三（SQL命令行方式下）
|
1
2
3
4
|
1、SQL>exec proc_emp('参数1','参数2');//无返回值过程调用
2、SQL>var vsal number
SQL> exec proc_emp ('参数1',:vsal);// 有返回值过程调用
//或者：call proc_emp ('参数1',:vsal);// 有返回值过程调用 |
相当与do while ........先执行一次再循环
循环基本语法
1. while total <= 100
loop
.......
total := total + 1;
end loop;
例
|
1
2
3
4
5
6
7
8
9
|
declarevar number:=1;
begin while var <= 100
loop
dbms_output.put_line(var);
var := var + 1;
end loop;
end; |
这个就相当于 JAVA里的while循环。
2. for i in 1..100
loop
语句序列;
end loop;
例
|
1
2
3
4
5
6
7
|
declarebegin for i in 1..100
loop
dbms_output.put_line(i);
end loop;
end; |
进阶小例子
|
1
2
3
4
5
6
7
8
9
10
11
12
|
declare var number;
begin select count(1)
into var
from test_procedure;
for i in 1..var
loop
dbms_output.put_line(i);
end loop;
end; |
就相当于for 循环。
3. loop
语句；
exit when 条件;
语句;
end loop;
例
|
1
2
3
4
5
6
7
8
9
|
declarevar number :=1;
begin loop
dbms_output.put_line(var);
exit when var > 1;
var := var + 1;
end loop;
end; |
语法：
cursor 游标名 [ (参数名 数据类型,参数名 数据类型,...)] is select 语句;
例：
cursor c1 is select ename from emp;
游标的属性：
%isopen 是否打开 boolean类型
%rowcount 影响的行数 不是总行数，例如总数100，已经取了10条，那么这个数为10
%found 是否找到 boolean类型
%notfound 是否没找到 boolean类型
简单实例1：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
declare cursor p is select t.name,t.money from test_procedure t;
pname test_procedure.name%type;
pmoney test_procedure.money%type;
begin --使用前要开启光标
open p;
loop
--取当前记录
fetch p into pname,pmoney;
exit when p%notfound;
dbms_output.put_line(pname||'薪水是'||pmoney);
end loop;
--结束要关闭光标
close p;
end; |
简单实例2：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
declare cursor p is select * from test_procedure t;
var test_procedure%rowtype;
begin --使用前要开启光标
open p;
loop
--取当前记录
fetch p into var;
exit when p%notfound;
dbms_output.put_line(var.name||'薪水是'||var.money);
end loop;
--结束要关闭光标
close p;
end; |
简单实例3：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
declare cursor p is select * from test_procedure t;
var test_procedure%rowtype;
begin --使用前要开启光标
open p;
loop
--取当前记录
fetch p into var;
if var.job = '后端工程师' then update test_procedure set money = money+10000 where name = var.name;
elsif var.job = '前端工程师'
then update test_procedure set money = money+5000 where name = var.name;
else
update test_procedure set money = money+50000 where name = var.name;
end if;
exit when p%notfound;
dbms_output.put_line(var.name||'薪水是'||var.money);
end loop;
--结束要关闭光标
close p;
--提交事务
commit;
dbms_output.put_line('完成');
end; |
带参数的游标用法
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
--查询某个部门员工的姓名declare --形参
--在游标定义一个参数
cursor temp(pjob varchar2) is select name from test_procedure where job = pjob;
pname test_procedure.name%type;
begin --传参
--开启游标传递参数
open temp('后端工程师');
loop
fetch temp into pname;
exit when temp%notfound;
dbms_output.put_line(pname);
end loop;
close temp;
end; |
下一篇： WaterView 自定义圆形水波进度