使用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.

#这里可以看到，以上插入过程是成功的，就只有查看的时候，返回查不到记录的异常。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31392094/viewspace-2128472/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31392094/viewspace-2128472/