Procedure笔记

Procedure(过程)
当经常需要完成某项特定操作时应考虑建立存储过程，以简化应用开发.
参数可以指定默认值：
    ...PROCEDURE add_deptno(v_deptno IN dept.deptno%TYPE,
                v_dname IN VARCHAR2,
                v_loc IN dept.loc%TYPE DEFAULT 'BEJING')...
给过程传递参数的方法：
    1，位置传递 exec add_dept(50,'SALES','BEIJING')
    2，名称传递 exec add_dept(v_dname=>'SALES',v_deptno=>50,v_loc=>'BEIJING')
    3，组合传递 exec add_dept(50,v_loc=>'BEIJING,v_dname=>'SALES')

可以在过程中使用异常处理以提高程序的健壮性
过程的输入、输出参数可以是记录变量和集合变量
    当使用集合变量作为参数时，需要使用自定义的嵌套表类型或者数组类型


＝＝＝＝＝＝＝＝＝＝＝不带参数的存储过程＝＝＝＝＝＝＝＝＝＝＝＝＝
SCOTT@uplookin>> ed
Wrote file afiedt.buf

  1  CREATE OR REPLACE PROCEDURE out_time IS
  2  BEGIN
  3  DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE,'YYYY-MON-DD HH24:MI:SS'));
  4* END;
SCOTT@uplookin>> /

Procedure created.

SCOTT@uplookin>> EXEC out_time;
2012-MAY-10 10:28:20

PL/SQL procedure successfully completed.

＝＝＝＝＝＝＝＝＝＝＝带有参数的存储过程＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
注：参数只能指定数据类型，不能指定数据长度
SCOTT@uplookin>> ed
Wrote file afiedt.buf

  1  CREATE OR REPLACE PROCEDURE update_sal(
  2     v_ename IN OUT VARCHAR2,
  3     v_deptno OUT emp.deptno%TYPE,
  4     v_sal IN OUT emp.sal%TYPE) IS
  5  BEGIN
  6     UPDATE t1 SET sal=v_sal WHERE ename=v_ename RETURNING ename,deptno,sal INTO v_ename,v_deptno,v_sal;
  7     DBMS_OUTPUT.PUT_LINE(v_ename||' from department '||v_deptno||' new_sal is '||v_sal);
  8* END;
SCOTT@uplookin>> /

Procedure created.

SCOTT@uplookin>> ed
Wrote file afiedt.buf

  1  declare
  2     v_ename emp.ename%type :='&ename';
  3     v_deptno emp.deptno%type;
  4     v_sal emp.sal%type :=&sal;
  5  begin
  6  update_sal(v_ename,v_deptno,v_sal);
  7* end;
SCOTT@uplookin>> /
Enter value for ename: KING
Enter value for sal: 9999
KING from department 10 new_sal is 9999

PL/SQL procedure successfully completed.

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝输入参数为集合，输出为记录＝＝＝＝＝＝＝＝＝＝＝＝＝
SCOTT@uplookin>> ed
Wrote file afiedt.buf

  1  CREATE OR REPLACE PROCEDURE get_dept_info(
  2     deptno_table IN deptno_table_type,
  3     dept_record OUT dept%rowtype) IS
  4  BEGIN
  5     FOR i IN 1..deptno_table.count LOOP
  6     SELECT * INTO dept_record FROM dept WHERE deptno=deptno_table(i);
  7     DBMS_OUTPUT.PUT_LINE(dept_record.deptno||' >> '||dept_record.dname||' >> '||dept_record.loc);
  8     END LOOP;
  9* END;
SCOTT@uplookin>> /

Procedure created.
SCOTT@uplookin>> declare
  2  deptno_table deptno_table_type :=deptno_table_type(&1,&2,&3);
  3  dept_record dept%rowtype;
  4  begin
  5  get_dept_info(deptno_table,dept_record);
  6  end;
  7  /
Enter value for 1: 10
Enter value for 2: 20
Enter value for 3: 30
10 >> ACCOUNTING >> NEW YORK
20 >> RESEARCH >> DALLAS
30 >> SALES >> CHICAGO

PL/SQL procedure successfully completed.