使用in out引數模式的過程

in out模式就是兼備了in與out兩種的模式，則既可以作為輸入值，被過程呼叫，
也可以作為輸出，從過程中呼叫出來作為結果，透過形參傳遞給實參。這裡的過程
例子主要實現兩個事情：一是透過三個引數的輸入值作為記錄插入表dept中，另一個
透過引數傳遞確定部門號的下一個部門號，查詢表中的記錄。當然當中也扦插一些程式
編寫錯誤的解決方法。

---建立一個過程：使用in out引數：

---將使用Scott使用者下的dept表：

--檢視錶dept的表結構：

scott@PROD>desc dept

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 DEPTNO                                    NOT NULL NUMBER(2)

 DNAME                                              VARCHAR2(14)

 LOC                                                VARCHAR2(13)

--檢視錶dept的記錄：

scott@PROD>select * from dept;

    DEPTNO DNAME          LOC

---------- -------------- -------------

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

 

--建立一個過程：

scott@PROD>create or replace procedure app_dept(

  2  v_no in out dept.deptno%type,

  3  v_name in out dept.dname%type,

  4  v_addr in out dept.loc%type)

  5  as

  6  begin

  7  insert into dept

  8  values(v_no,v_name,v_addr);

  9  v_no = v_no - 1;

 10  select deptno,dname,loc into v_no,v_name,v_addr

 11  from dept where deptno = v_no;

 12  exception

 13  when dup_val_on_index then

 14  dbms_output.put_line('There is a deptno,can't appear again!');

 15  when no_data_found then

 16  dbms_output.put_line('There is no this deptno in this table!');

 17  end app_dept;

 18  /

Warning: Procedure created with compilation errors.

scott@PROD>

#當編寫程式過程中遇到錯誤時，我們可以呼叫資料庫中的程式檢視出錯的地方，進而修改過來，重新執行過程。
 

--檢視錯誤

scott@PROD>show error

Errors for PROCEDURE APP_DEPT:

LINE/COL ERROR

-------- -----------------------------------------------------------------

9/6      PLS-00103: Encountered the symbol "=" when expecting one of the

         following:

         := . ( @ % ;

         The symbol ":= was inserted before "=" to continue.

 

14/45    PLS-00103: Encountered the symbol "T" when expecting one of the

         following:

         ) , * & = - + < / > at in is mod remainder not rem =>

         <an exponent (**)> <> or != or ~= >= <= <> and or like like2

         like4 likec as between from using || multiset member

         submultiset

LINE/COL ERROR

-------- -----------------------------------------------------------------

 

--edit重新修改程式的句子：

#修改（略）

scott@PROD>edit

Wrote file afiedt.buf

482   

  1  create or replace procedure app_dept(

  2  v_no in out dept.deptno%type,

  3  v_name in out dept.dname%type,

  4  v_addr in out dept.loc%type)

  5  as

  6  begin

  7  insert into dept

  8  values(v_no,v_name,v_addr);

  9  v_no = v_no - 1;

 10  select deptno,dname,loc into v_no,v_name,v_addr

 11  from dept where deptno = v_no;

 12  exception

 13  when dup_val_on_index then

 14  dbms_output.put_line('There is a deptno,can't appear again!');

 15  when no_data_found then

 16  dbms_output.put_line('There is no this deptno in this table!');

 17* end app_dept;

scott@PROD>
 

--修改後重新執行：

scott@PROD>create or replace procedure app_dept(

  2  v_no in out dept.deptno%type,

  3  v_name in out dept.dname%type,

  4  v_addr in out dept.loc%type)

  5  as

  6  begin

  7  insert into dept

  8  values(v_no,v_name,v_addr);

  9  v_no := v_no - 1;

 10  select deptno,dname,loc into v_no,v_name,v_addr

 11  from dept where deptno = v_no;

 12  exception

 13  when dup_val_on_index then

 14  dbms_output.put_line('There is a deptno,can not appear again!');

 15  when no_data_found then

 16  dbms_output.put_line('There is no this deptno in this table!');

 17  end app_dept;

 18  /

Procedure created.

#過程成功執行。

 

--呼叫程式：

scott@PROD>variable v_no number

scott@PROD>variable v_name varchar2(14)

scott@PROD>variable v_addr varchar2(13)

scott@PROD>execute :v_no :=21

PL/SQL procedure successfully completed.

scott@PROD>execute :v_name :='manager'

PL/SQL procedure successfully completed.

 

scott@PROD>execute :v_addr :='china'

PL/SQL procedure successfully completed.

 

scott@PROD>call app_dept(:v_no,:v_name,:v_addr);

Call completed.

scott@PROD>

#過程執行完成。

 

--檢視插入的資料：

scott@PROD>print :v_no :v_name :v_addr

      V_NO

----------

        20

 

V_NAME

--------------------------------------------------------------------------------

RESEARCH

 

V_ADDR

--------------------------------------------------------------------------------

DALLAS

scott@PROD>

#已經顯示出來：

 

--使用SQL語句檢視修改後表dept的記錄：

scott@PROD>execute :v_no :=32

 

PL/SQL procedure successfully completed.

 

scott@PROD>execute :v_name :='clerk'

 

PL/SQL procedure successfully completed.

 

scott@PROD>execute :v_addr :='shanghai'

 

PL/SQL procedure successfully completed.

 

scott@PROD>call app_dept(:v_no,:v_name,:v_addr);

There is no this deptno in this table!

Call completed.

scott@PROD>

 

--列印插入的資料：

scott@PROD>call app_dept(:v_no,:v_name,:v_addr);

There is no this deptno in this table!

Call completed.

 

scott@PROD>print :v_no :v_name :v_addr

      V_NO

----------

        31

V_NAME

--------------------------------------------------------------------------------

clerk

V_ADDR

--------------------------------------------------------------------------------

shanghai

scott@PROD>

 

--使用SQL語言檢視執行過程後的表dept的記錄：

scott@PROD>select * from dept;

    DEPTNO DNAME          LOC

---------- -------------- -------------

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

        21 manager        china

        32 clerk          shanghai

6 rows selected.

#這裡可以看到，以上插入過程是成功的，就只有檢視的時候，返回查不到記錄的異常。