oracle存储过程的使用

一. 使用for循环游标：遍历所有职位为经理的雇员

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