2016年7月28日 星期四

學習初級Oracle PL/SQL (五)

Single Row Processing
現在,將開始使用PL/SQL的insert,update,delete旅程.一次insert一行,然後每次update一行,接著一次delete一行,最後一次select一行.

Inserts
用PL/SQL將數據insert到數據庫中,您只需編寫一個SQL INSERT語句,其中PL/SQL的value是文字,PL/SQL變量或SQL column. 這裡將會舉例常見的做法,然後通過進展持續改進你的設計. 你會明白PL/SQL程序員最常做的, 以及如何改善代碼. 請花多點時間了解這些方案.
正常的時候你很少使用PL/SQL執行一個簡單的INSERT語句, 因為你可以使用SQLPLUS scripts而不使用PL/SQL.但更多的時候,當你要insert或update記錄時發現記錄已經存在於數據庫中,該記錄要insert還是update呢? Sqlplus scripts就不能有更好的做法.

以下是用PL/SQL insert的例子, 為empno建立一個sequence, 在scott.emp 增加一個記錄,並為ename,job,mgr,sal,comm 加入unique key防止重覆值:
create sequence emp_seq start with 8001 increment by 10;
alter table emp add constraint UK1_emp unique(ename,job,mgr,sal,comm) using index;
set serveroutput on size 1000000;

declare
n_empno          emp.empno%type;
n_mgr               emp.mgr%type;
n_deptno  emp.deptno%type;

-- 用n_count來保全insert結果.
n_count    number;

begin
       
        -- 假設,我們獲取BLAKE 的mgr number.
        begin
         select empno into n_mgr from emp where ename='BLAKE';
        exception
         when others then raise_application_error(-20002, SQLERRM||' get mgr number');
        end;

        -- 然後獲取sales deptno.
        begin
         select deptno into n_deptno from dept where dname='SALES';
        exception
         when others then raise_application_error(-20003, SQLERRM||' get deptno');
        end;

        -- 然後獲取 empno sequence.
        begin
         select emp_seq.nextval into n_empno from dual;
        exception
         when others then raise_application_error(-20004, SQLERRM||' get empno seq');
        end;

        -- 需要獲取的ID已齊全, 終於可以開始insert.
        begin
         insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(
         n_empno,'SALLY','SALESMAN',n_mgr,SYSDATE,1500,300,n_deptno);
         
         n_count := sql%rowcount;
        exception
         when others then raise_application_error(-20005, SQLERRM||' insert record');
        end;
        sys.dbms_output.put_line(to_char(n_count)||' record(s) inserted.');
end;
/

commit;
執行效果:
1 record(s) inserted.
PL/SQL procedure successfully completed.

假若再次執行則出現下方錯誤:
declare
*
ERROR at line 1:
ORA-20005: ORA-00001: unique constraint (SCOTT.UK1_EMP) violated insert record
ORA-06512: at line 39

Unique constraint exception -20005 出現.
現在, 如果你要處理這種特殊的exception, 可以使用以下三種策略:

- 使用"DUP_VAL_ON_INDEX"捕捉exception (good).
- 使用額外的PL/SQL代碼來檢測預重複值的存在 (better).
- 使用額外的SQL代碼來檢測預重複值的存在 (best)

讓我們來看看這些解決方案,從good到best.



使用"DUP_VAL_ON_INDEX"捕捉exception
當涉及到處理的代碼, 估計可能會引發一個DUP_VAL_ON_INDEX exception, 捕捉exception是最懶的解決方案.
Oracle 會在任何現有的unique index或unique key檢查table有沒有重覆值. 如果發現重覆,PL/SQL會使用 DUP_VAL_ON_INDEX exception. 那麼,你如何處理這個問題?

-- 承接上一段代碼更改如下
..
…
        begin
         insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(
         n_empno,'SALLY','SALESMAN',n_mgr,SYSDATE,1500,300,n_deptno);
         
         n_count := sql%rowcount;
        exception
         when DUP_VAL_ON_INDEX then
                n_count := 0;
                sys.dbms_output.put_line('Found duplicate record in table');
         when others then raise_application_error(-20005, SQLERRM||' insert record');
        end;
        sys.dbms_output.put_line(to_char(n_count)||' record(s) inserted.');
end;
/
執行效果:
Found duplicate record in table
0 record(s) inserted.

PL/SQL procedure successfully completed.

以上並沒有出現任何error,因為增加了WHEN DUP_VAL_ON_INDEX 的條件.可以更加好地處理重覆值的錯誤.



使用額外的PL/SQL代碼來檢測預重複值的存在
使用額外的PL/SQL代碼來檢測預重複值的存在是比使用"DUP_VAL_ON_INDEX"捕捉exception來的好,理由如下:

- 您維護您的PL/SQL程序執行的控制.
- 可以有條件地決定如何處理重複值.
- 您可以使用的檢測方式取得這個row的primary key,當發現是重複值,更改insert為update.

現在我們看看怎樣用PL/SQL來檢測重複值(留意綠字):
declare
n_empno          emp.empno%type;
n_mgr               emp.mgr%type;
n_deptno  emp.deptno%type;
        -- 設定變量
        v_ename           emp.ename%type;
        v_job                emp.job%type;
        v_mgr               emp.ename%type;
        n_sal                emp.sal%type;
        n_comm           emp.comm%type;

n_count    number;

    begin
         -- 自定檢查用的變量值
         v_ename := 'SALLY';
         v_job      := 'SALESMAN';
         v_mgr     := 'BLAKE';
         n_sal       := 1500;
         n_comm  := 300;

        --使用 uk1_emp 的條件檢查重複值"UK1_emp unique(ename,job,mgr,sal,comm)"
        begin
         select count(1) into n_count from emp c1 cross join emp c2 where
         c1.ename=v_ename and c1.job=v_job and c2.ename=v_mgr and c1.sal=n_sal and c1.comm=n_comm;
        exception
         when others then raise_application_error(-20005, SQLERRM||' on detect duplicate');
        end;

    -- 如果沒有發現重複值
    if n_count = 0 then
       
        -- 獲取BLAKE 的mgr number.
        begin
         select empno into n_mgr from emp where ename='BLAKE';
        exception
         when others then raise_application_error(-20002, SQLERRM||' get mgr number');
        end;

        -- 然後獲取sales deptno.
        begin
         select deptno into n_deptno from dept where dname='SALES';
        exception
         when others then raise_application_error(-20003, SQLERRM||' get deptno');
        end;

        -- 然後獲取 empno sequence.
        begin
         select emp_seq.nextval into n_empno from dual;
        exception
         when others then raise_application_error(-20004, SQLERRM||' get empno seq');
        end;

        -- 需要獲取的資已齊全, 可以開始insert.
        begin
         insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(
         n_empno,v_ename,v_job,n_mgr,SYSDATE,n_sal,n_comm,n_deptno);
         n_count := sql%rowcount;
        exception
         when others then raise_application_error(-20006, SQLERRM||' insert record');
        end;

    -- 否則
    else
     n_count := 0;
     sys.dbms_output.put_line('Duplicate record found');
    end if;

    sys.dbms_output.put_line(to_char(n_count)||' record(s) inserted.');
    end;
/
commit;
執行效果:
Duplicate record found
0 record(s) inserted.

PL/SQL procedure successfully completed.

執行結果雖然和DUP_VAL_ON_INDEX一樣, 但是已經能用if…else來控制重複後的處理, 以下例子處理發現重複值後更改為update (留意綠字).
declare
v_ename           emp.ename%type;
v_job                        emp.job%type;
v_mgr               emp.ename%type;
n_mgr               emp.mgr%type;
n_sal                        emp.sal%type;
n_comm           emp.comm%type;
n_empno          emp.empno%type;
n_deptno  emp.deptno%type;
-- n_count number;  -- 由於有2個動作insert和update,所以更改為以下變量儲存結果
n_inserted         number := 0;
n_updated                number := 0;

begin
  -- 自定檢查用的變量值
  v_ename       := 'SALLY';
  v_job             := 'SALESMAN';
  v_mgr            := 'BLAKE';
  n_sal             := 1500;
  n_comm        := 300;
       
        --使用 uk1_emp 的條件檢查重複值"UK1_emp unique(ename,job,mgr,sal,comm)"
        begin
         select c1.empno into n_empno from emp c1 cross join emp c2 where
         c1.ename=v_ename and c1.job=v_job and c2.ename=v_mgr and c1.sal=n_sal and c1.comm=n_comm;
        exception
         when NO_DATA_FOUND then n_empno := NULL;
         when others then raise_application_error(-20005, SQLERRM||' on detect duplicate');
        end;

        if n_empno is NULL then
         -- 獲取 empno sequence.
         begin
          select emp_seq.nextval into n_empno from dual;
         exception
          when others then raise_application_error(-20004, SQLERRM||' get empno seq');
         end;
         -- 獲取BLAKE 的mgr number.
         begin
          select empno into n_mgr from emp where ename='BLAKE';
         exception
          when others then raise_application_error(-20002, SQLERRM||' get mgr number');
         end;
         -- 然後獲取sales deptno.
         begin
          select deptno into n_deptno from dept where dname='SALES';
         exception
          when others then raise_application_error(-20003, SQLERRM||' get deptno');
         end;

         -- 需要獲取的資已齊全, 可以開始insert.
         begin
          insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(
          n_empno,v_ename,v_job,n_mgr,SYSDATE,n_sal,n_comm,n_deptno);
          n_inserted := sql%rowcount;
         exception
          when others then raise_application_error(-20006, SQLERRM||' insert record');
      end;
        else
         begin
          update emp set sal=n_sal where empno=n_empno;
          n_updated := sql%rowcount;
         exception
          when others then raise_application_error(-20007, SQLERRM||' update record');
         end;
        end if;
        sys.dbms_output.put_line(to_char(n_inserted)||' record(s) inserted.');
        sys.dbms_output.put_line(to_char(n_updated)||' record(s) updated.');
end;
/

執行效果:
0 record(s) inserted.
1 record(s) updated.

PL/SQL procedure successfully completed.

IF-THEN-ELSE語句的語法是:
[ ] 為非必要

單一條件
IF < condition> THEN
-- do this if it's TRUE
[ELSE
-- do this if it's not TRUE]
END IF;
多個條件
IF <condition> THEN
-- do this if it's TRUE
[ELSIF <condition> THEN
-- do this if it's TRUE
ELSIF ...
ELSE
-- do this if it's not TRUE]
END IF;




使用額外的SQL代碼來檢測預重複值的存在

這幾乎是最好的,讓SQL執行EXISTS子句做檢測的工作!EXISTS子句被執行時,如果重複值不存在SQL會立刻insert.

以下例子:
-- 先把記錄刪除
delete emp where ename=’SALLY’;
declare
v_ename           emp.ename%type;
v_job                        emp.job%type;
d_hiredate         emp.hiredate%type;
n_sal                        emp.sal%type;
n_comm           emp.comm%type;
n_count            number;

begin
 v_ename         := 'SALLY';
 v_job              := 'SALESMAN';
 d_hiredate       := sysdate;
 n_sal               := 1500;
 n_comm          := 300;

        -- 可以簡單使用SQL, 不用PL/SQL
        begin
         insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
         select emp_seq.nextval,v_ename,v_job,c1.mgr,sysdate,n_sal,n_comm,c2.deptno
         from emp c1 cross join dept c2 where c1.ename='BLAKE' and c2.dname='SALES'
         and not exists(select 1 from emp c3 where c3.ename=v_ename and c3.job=v_job
         and c3.mgr=c1.mgr);
         n_count := sql%rowcount;
        exception
         when others then raise_application_error(-20006, SQLERRM||' on insert');
        end;
 sys.dbms_output.put_line(to_char(n_count)||' record(s) inserted.');
end;
/
commit;

執行2次效果:
1 record(s) inserted.
PL/SQL procedure successfully completed.

0 record(s) inserted.

PL/SQL procedure successfully completed.


It’s Your Turn to Insert
你要編寫一個用PL/SQL insert 以下4個員工到 emp:
 - TOM,   ANALYST, JONES, sysdate-10, 2500, null, RESEARCH
 - BILLY, CLERK,   BLAKE, sysdate-5,  800,  null, SALES
 - KELLY, SALESMAN,BLAKE, sysdate-7,  1400, 200,  SALES
 - ALAN,  ANALYST, CLARK, sysdate-30, 3000, null, ACCOUNTING

提示: 你可以使用 Anonymous + Function 完成.
declare
n_count number := 0;

function add_emp(
v_ename           in emp.ename%type,
v_job                in emp.job%type,
v_mgr               in emp.ename%type,
d_hiredate in emp.hiredate%type,
n_sal                in emp.sal%type,
n_comm           in emp.comm%type,
v_dname           in dept.dname%type)
return number as
begin
 insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
 select emp_seq.nextval,v_ename,v_job,c1.mgr,d_hiredate,n_sal,n_comm,c2.deptno
 from emp c1 cross join dept c2 where c1.ename=v_mgr and c2.dname=v_dname and
 not exists(select 1 from emp c3 where c3.ename=v_ename and c3.job=v_job and
 c3.mgr=c1.mgr);
  return sql%rowcount;
exception
 when others then raise_application_error(-20006, SQLERRM||' on insert');
end add_emp;

begin
 n_count := n_count + add_emp('TOM','ANALYST','JONES',sysdate-10,2500,null,'RESEARCH');
 n_count := n_count + add_emp('BILLY','CLERK','BLAKE',sysdate-5,800,null,'SALES');
 n_count := n_count + add_emp('KELLY','SALESMAN','BLAKE',sysdate-7,1400,200,'SALES');
 n_count := n_count + add_emp('ALAN','ANALYST','CLARK',sysdate-30,3000,null,'ACCOUNTING');
 sys.dbms_output.put_line(to_char(n_count)||' add record(s).');
end;
/


張貼者： 於
標籤： , , , , , , ,

沒有留言:

張貼留言

訂閱： 張貼留言 (Atom)