oracle循环语句游标,ORACLE存储过程，循环语法和游标

1、定义所谓存储过程(Stored Procedure)，就是一组用于完成特定数据库功能的sql语句集，该sql语句集经过

编译后存储在数据库系统中。在使用时候，用户通过指定已经定义的存储过程名字并给出相应的存储过程参数

来调用并执行它，从而完成一个或一系列的数据库操作。

2、存储过程的创建Oracle存储过程包含三部分：过程声明，执行过程部分，存储过程异常。

(1)无参存储过程语法

create or replace procedure NoParPro

as //声明

;

begin // 执行

;

exception//存储过程异常

when too_many_rows then

***

when no_data_found then

***

when others then

***

;

end;

(2)带参存储过程实例

create or replace procedure queryempname(sfindno emp.empno%type)

as

sName emp.ename%type;

sjob emp.job%type;

begin

....

exception

....

end;

(3)带参数存储过程含赋值方式

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)存储过程中游标定义使用

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)游标的定义

--显示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)过程调用方式一

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)过程调用方式二

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、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;

例

declare

var 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;

例

declare

begin

for i in 1..100

loop

dbms_output.put_line(i);

end loop;

end;

进阶小例子

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;

例

declare

var 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：

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：

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：

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;

带参数的游标用法

--查询某个部门员工的姓名

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;