oracle中存儲過程如何使用

今天就跟大家聊聊有關(guān)oracle中存儲過程如何使用，可能很多人都不太了解，為了讓大家更加了解，小編給大家總結(jié)了以下內(nèi)容，希望大家根據(jù)這篇文章可以有所收獲。

一. 使用for循環(huán)游標(biāo)：遍歷所有職位為經(jīng)理的雇員

1. 定義游標(biāo)(游標(biāo)就是一個(gè)小集合)

2. 定義游標(biāo)變量

3. 使用for循環(huán)游標(biāo)

declare
  -- 定義游標(biāo)c_job
  cursor c_job is
    select empno, ename, job, sal from emp where job = 'MANAGER';
    
  -- 定義游標(biāo)變量c_row
  c_row c_job%rowtype;
begin
  -- 循環(huán)游標(biāo)，用游標(biāo)變量c_row存循環(huán)出的值
  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游標(biāo)：遍歷所有職位為經(jīng)理的雇員

使用的時(shí)候必須明確的打開和關(guān)閉

declare
  --定義游標(biāo)c_job
  cursor c_job is
    select empno, ename, job, sal from emp where job = 'MANAGER';

  --定義游標(biāo)變量c_row
  c_row c_job%rowtype;
begin
  open c_job;
  loop
    --提取一行數(shù)據(jù)到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;
  
  --關(guān)閉游標(biāo)
  close c_job;
end;

三. 使用游標(biāo)和while循環(huán)：遍歷所有部門的地理位置

--3,使用游標(biāo)和while循環(huán)來顯示所有部門的的地理位置（用%found屬性）
declare
  --聲明游標(biāo)
  cursor csr_TestWhile is select loc from dept;

  --指定行指針
  row_loc csr_TestWhile%rowtype;
begin
  open csr_TestWhile;
  --給第一行數(shù)據(jù)
  fetch csr_TestWhile into row_loc;
  
  --測試是否有數(shù)據(jù)，并執(zhí)行循環(huán)
  while csr_TestWhile%found loop
    dbms_output.put_line('部門地點(diǎn)：' || row_loc.LOC);
    --給下一行數(shù)據(jù)
    fetch csr_TestWhile into row_loc;
  end loop;
  close csr_TestWhile;
end;

四. 帶參的游標(biāo)：接受用戶輸入的部門編號

declare
  -- 帶參的游標(biāo)
  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;

五. 加鎖的游標(biāo)：對所有的salesman增加傭金500

declare
  --查詢數(shù)據(jù)，加鎖(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;
    
    --更新數(shù)據(jù)(where current of)
    update emp set comm = commInfo where current of csr_addComm;
  end loop;
end;

六. 使用計(jì)數(shù)器：找出兩個(gè)工作時(shí)間最長的員工

declare
  cursor crs_testComput is
    select * from emp order by hiredate asc;
    
  --計(jì)數(shù)器
  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 ||
                         ' 工作時(shí)間：' || r_testComput.hiredate);
    --計(jì)速器減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 || ':  加薪成功.' ||
                           '薪水變?yōu)椋?#39; || salInfo);
    end if;
    update emp set sal = salInfo where current of crs_upadateSal;
  end loop;
end;

八. 使用case
when：按部門進(jìn)行加薪

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;

九. 異常處理：數(shù)據(jù)回滾

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有效,并設(shè)置成最大buffer,防止"吃掉"最前面的空格
set linesize 256; --設(shè)置一行可以容納的字符數(shù)
set pagesize 50; --設(shè)置一頁有多少行數(shù)
set arraysize 5000; --設(shè)置來回?cái)?shù)據(jù)顯示量,這個(gè)值會影響autotrace時(shí)一致性讀等數(shù)據(jù)
set newpage none; --頁和頁之間不設(shè)任何間隔
set long 5000; --LONG或CLOB顯示的長度
set trimspool on; --將SPOOL輸出中每行后面多余的空格去掉
set timing on; --設(shè)置查詢耗時(shí)
col plan_plus_exp format a120; --autotrace后explain plan output的格式
set termout off; --在屏幕上暫不顯示輸出的內(nèi)容,為下面的設(shè)置sql做準(zhǔn)備
alter session set nls_date_format='yyyy-mm-dd hh34:mi:ss'; --設(shè)置時(shí)間格式

小知識：

下面的語句一定要在Command Window里面才能打印出內(nèi)容

set serveroutput on;
begin 
dbms_output.put_line('hello!');
end;
/

看完上述內(nèi)容，你們對oracle中存儲過程如何使用有進(jìn)一步的了解嗎？如果還想了解更多知識或者相關(guān)內(nèi)容，請關(guān)注億速云行業(yè)資訊頻道，感謝大家的支持。