oracle什么时候使用存储过程,oracle存储过程的使用

1. 定义游标(游标就是一个小集合)

2. 定义游标变量

3. 使用for循环游标

declare

-- 定义游标c_job

cursor c_job is

select empno, ename, job, sal from emp where job = ‘MANAGER‘;

-- 定义游标变量c_row

c_row c_job%rowtype;

begin

-- 循环游标，用游标变量c_row存循环出的值

for c_row in c_job loop

dbms_output.put_line(c_row.empno || ‘-‘ || c_row.ename || ‘-‘ ||

c_row.job || ‘-‘ || c_row.sal);

end loop;

end;

二. fetch游标：遍历全部职位为经理的雇员

使用的时候必须明白的打开和关闭

declare

--定义游标c_job

cursor c_job is

select empno, ename, job, sal from emp where job = ‘MANAGER‘;

--定义游标变量c_row

c_row c_job%rowtype;

begin

open c_job;

loop

--提取一行数据到c_row

fetch c_job into c_row;

--判读是否提取到值。没取到值就退出

exit when c_job%notfound;

dbms_output.put_line(c_row.empno || ‘-‘ || c_row.ename || ‘-‘ ||

c_row.job || ‘-‘ || c_row.sal);

end loop;

--关闭游标

close c_job;

end;

三. 使用游标和while循环：遍历全部部门的地理位置

--3,使用游标和while循环来显示全部部门的的地理位置(用%found属性)

declare

--声明游标

cursor csr_TestWhile is select loc from dept;

--指定行指针

row_loc csr_TestWhile%rowtype;

begin

open csr_TestWhile;

--给第一行数据

fetch csr_TestWhile into row_loc;

--測试是否有数据，并运行循环

while csr_TestWhile%found loop

dbms_output.put_line(‘部门地点：‘ || row_loc.LOC);

--给下一行数据

fetch csr_TestWhile into row_loc;

end loop;

close csr_TestWhile;

end;

四. 带參的游标：接受用户输入的部门编号

declare

-- 带參的游标

cursor c_dept(p_deptNo number) is

select * from emp where emp.deptno = p_deptNo;

r_emp emp%rowtype;

begin

for r_emp in c_dept(20) loop

dbms_output.put_line(‘员工号：‘ || r_emp.EMPNO || ‘员工名：‘

|| r_emp.ENAME || ‘工资：‘ || r_emp.SAL);

end loop;

end;

五. 加锁的游标：对全部的salesman添加佣金500

declare

--查询数据，加锁(for update of)

cursor csr_addComm(p_job nvarchar2) is

select * from emp where job = p_job for update of comm;

r_addComm emp%rowtype;

commInfo emp.comm%type;

begin

for r_addComm in csr_addComm(‘SALESMAN‘) loop

commInfo := r_addComm.comm + 500;

--更新数据(where current of)

update emp set comm = commInfo where current of csr_addComm;

end loop;

end;六. 使用计数器：找出两个工作时间最长的员工declare

cursor crs_testComput is

select * from emp order by hiredate asc;

--计数器

top_two number := 2;

r_testComput crs_testComput%rowtype;

begin

open crs_testComput;

fetch crs_testComput into r_testComput;

while top_two > 0 loop

dbms_output.put_line(‘员工姓名：‘ || r_testComput.ename ||

‘ 工作时间：‘ || r_testComput.hiredate);

--计速器减1

top_two := top_two - 1;

fetch crs_testComput into r_testComput;

end loop;

close crs_testComput;

end;七. if/else推断：对全部员工按基本薪水的20%加薪。假设添加的薪水大于300就取消加薪declare

cursor crs_upadateSal is

select * from emp for update of sal;

r_updateSal crs_upadateSal%rowtype;

salAdd emp.sal%type;

salInfo emp.sal%type;

begin

for r_updateSal in crs_upadateSal loop

salAdd := r_updateSal.sal * 0.2;

if salAdd > 300 then

salInfo := r_updateSal.sal;

dbms_output.put_line(r_updateSal.ename || ‘: 加薪失败。‘ ||

‘薪水维持在：‘ || r_updateSal.sal);

else

salInfo := r_updateSal.sal + salAdd;

dbms_output.put_line(r_updateSal.ENAME || ‘: 加薪成功.‘ ||

‘薪水变为：‘ || salInfo);

end if;

update emp set sal = salInfo where current of crs_upadateSal;

end loop;

end;八. 使用case

when：按部门进行加薪declare

cursor crs_caseTest is

select * from emp for update of sal;

r_caseTest crs_caseTest%rowtype;

salInfo emp.sal%type;

begin

for r_caseTest in crs_caseTest loop

case

when r_caseTest.deptno = 10 THEN

salInfo := r_caseTest.sal * 1.05;

when r_caseTest.deptno = 20 THEN

salInfo := r_caseTest.sal * 1.1;

when r_caseTest.deptno = 30 THEN

salInfo := r_caseTest.sal * 1.15;

when r_caseTest.deptno = 40 THEN

salInfo := r_caseTest.sal * 1.2;

end case;

update emp set sal = salInfo where current of crs_caseTest;

end loop;

end;

九. 异常处理：数据回滚

set serveroutput on;

declare

d_name varchar2(20);

begin

d_name := ‘developer‘;

savepoint A;

insert into DEPT values (50, d_name, ‘beijing‘);

savepoint B;

insert into DEPT values (40, d_name, ‘shanghai‘);

savepoint C;

exception when others then

dbms_output.put_line(‘error happens‘);

rollback to A;

commit;

end;

/

十. 基本指令：

set serveroutput on size 1000000 format wrapped; --使DBMS_OUTPUT有效,并设置成最大buffer,防止"吃掉"最前面的空格

set linesize 256; --设置一行能够容纳的字符数

set pagesize 50; --设置一页有多少行数

set arraysize 5000; --设置来回数据显示量,这个值会影响autotrace时一致性读等数据

set newpage none; --页和页之间不设不论什么间隔

set long 5000; --LONG或CLOB显示的长度

set trimspool on; --将SPOOL输出中每行后面多余的空格去掉

set timing on; --设置查询耗时

col plan_plus_exp format a120; --autotrace后explain plan output的格式

set termout off; --在屏幕上暂不显示输出的内容,为以下的设置sql做准备

alter session set nls_date_format=‘yyyy-mm-dd hh24:mi:ss‘; --设置时间格式小知识：

以下的语句一定要在Command Window里面才干打印出内容

set serveroutput on;

begin

dbms_output.put_line(‘hello!‘);

end;

/

oracle存储过程的使用

标签：off   size   tco   crs   autot   long   lin   update   alt

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉 本文系统来源：http://www.cnblogs.com/slgkaifa/p/6977777.html