PL/SQL块最大优点:降低网络开销，提高应用程序性能。

文章需要用到的表结构及信息

select * from emp;
     EMPNO ENAME      JOB          MGR HIREDATE           SAL   COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK       7902 1980-12-17 12:00:00       5000            20
      7499 SMITH      SALESMAN        7698 1981-02-20 12:00:00       5000    300         30
      7521 WARD       SALESMAN        7698 1981-02-22 12:00:00     1437.5    500         30
      7566 JONES      MANAGER         7839 1981-04-02 12:00:00    8013.97            20
      7654 MARTIN     SALESMAN        7698 1981-09-28 12:00:00     1437.5   1400         30
      7698 BLAKE      MANAGER         7839 1981-05-01 12:00:00     3277.5            30
      7782 CLARK      MANAGER         7839 1981-06-09 12:00:00    3084.27            10
      7788 SCOTT      ANALYST         7566 1987-04-19 12:00:00     4163.1            20
      7839 KING       PRESIDENT        1981-11-17 12:00:00    6797.97            10
      7844 TURNER     SALESMAN        7698 1981-09-08 12:00:00       1725      0         30


desc emp;
 Name            Null?    Type
 ----------------------- -------- ----------------
 EMPNO           NOT NULL NUMBER(4)
 ENAME                VARCHAR2(10)
 JOB                  VARCHAR2(9)
 MGR                  NUMBER(4)
 HIREDATE             DATE
 SAL                  NUMBER(7,2)
 COMM                 NUMBER(7,2)
 DEPTNO               NUMBER(2)

1.procedure语法

create or replace procedure procedure_name

(argu1 datatype,argu2 datatype...)

is [as]

PL/SQL Block;

procedure_name:存储过程名称

argu1,argu2...:参数名称，可以指定输入参数(IN),也可以指定输出参数(OUT),也可以指定输入输出参数(IN OUT)

datatype：参数数据类型，不能指定长度。

is [as]:开始一个PL/SQL块

---输出当前系统日期和时间的过程

create or replace procedure pro_output_time
is
begin
dbms_output.put_line(systimestamp);
end;
SQL> set serveroutput on;

2.procedure调试

//调试方法1

SQL> exec pro_output_time;

16-APR-14 08.42.33.678767000 AM +08:00

PL/SQL procedure successfully completed.

//调试方法2

SQL> call pro_output_time();

16-APR-14 08.42.47.881928000 AM +08:00

Call completed.

//调试方法3

SQL> begin

 2  pro_output_time();

 3  end;

 4  /

3.例子1：根据输入的信息，增加一条雇员记录

create or replace procedure pro_add_emp(v_empno number,v_ename varchar2,
v_sal number,v_deptno number,v_job varchar2 default 'CLERK')
is
begin
insert into emp(empno,ename,sal,deptno,job)
values (v_empno,v_ename,v_sal,v_deptno,v_job);
commit;
end;

4.例子2：雇员编号重复 &&  deptno不存在

---code

create or replace procedure pro_add_emp(v_empno number,v_ename varchar2,
v_sal number,v_deptno number,v_job varchar2 default 'CLERK')
is
v_no_deptno exception;
pragma exception_init(v_no_deptno,-2291);
begin
insert into emp(empno,ename,sal,deptno,job)
values (v_empno,v_ename,v_sal,v_deptno,v_job);
commit;
exception
when v_no_deptno then
raise_application_error(-20001,'deptno is not exist.');
when dup_val_on_index then
raise_application_error(-20002,'empno is duplicat.');
end;