oracle預存程序的使用，oracle預存程序

oracle預存程序的使用，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;/