创建存储过程oracle实例,Oracle存储过程的创建实例和调用实例

--编写一个存储过程，给emp表中添加数据。

--方法一：

create or replace procedure add_employee(

eno number,

name varchar2,

salary number,

job varchar2 default 'CLERK',

dno number

)

is

begin

insert into emp (empno,ename,sal,job,deptno) values (eno,name,salary,job,dno);

end;

--方法二：

create or replace procedure add_employee_1(

eno number,

name varchar2,

salary number,

job varchar2 default 'CLERK',

dno number

)

is

emp_null_error exception; --声明异常变量

pragma exception_init( emp_null_error, -1400 ); --把异常变量和异常编号-1400绑定.-非空约束

emp_no_deptno exception ; --声明异常变量

pragma exception_init ( emp_no_deptno, -2291 );--外键约束

begin

insert into emp ( empno,ename,sal,job,deptno )values( eno,name,salary,job,dno);

exception

when DUP_VAL_oN_INDEX then

RAISE_APPLICATION_ERROR( -20000, '该雇员以存在' );

when emp_null_error then

RAISE_APPLICATION_ERROR( -20001, '部门编号不能为空' );

when emp_no_deptno then

RAISE_APPLICATION_ERROR( -20002, '不存在该部门编号' );

end;

--调用存储过程

--在sql提示符下调用

--外键约束

SQL> exec add_employee( 1111,'MARY', 2000, 'MANAGER', 66 );

begin add_employee( 1111,'MARY', 2000, 'MANAGER', 66 ); end;

ORA-02291: 违反完整约束条件 (SCOTT.FK_DEPTNO) - 未找到父项关键字

ORA-06512: 在 "SCOTT.ADD_EMPLOYEE", line 10

ORA-06512: 在 line 1

--异常部门为空

SQL>  exec add_employee(1111,'MARY', 2000, 'MANAGER', null);

begin add_employee(1111,'MARY', 2000, 'MANAGER', null); end;

ORA-01400: 无法将 NULL 插入 ("SCOTT"."EMP"."DEPTNO")

ORA-06512: 在 "SCOTT.ADD_EMPLOYEE", line 10

ORA-06512: 在 line 1

--正确，按位置传参

SQL>  exec add_employee(1111,'MARY', 2000, 'MANAGER', 10);

PL/SQL procedure successfully completed

--雇员编号重复

SQL>  exec add_employee(1111,'MARYE', 3000, 'MANAGER', 20);

begin add_employee(1111,'MARYE', 3000, 'MANAGER', 20); end;

ORA-00001: 违反唯一约束条件 (SCOTT.PK_EMP)

ORA-06512: 在 "SCOTT.ADD_EMPLOYEE", line 10

ORA-06512: 在 line 1

--正确，按名字传参

SQL>  exec add_employee(eno=>1112,name=>'MARYE', salary=>3000, job=>'MANAGER',dno=> 20);

PL/SQL procedure successfully completed

--混合传参

SQL>  exec add_employee(1113,name=>'MACLE', salary=>3000, job=>'CLERK',dno=> 20);

PL/SQL procedure successfully completed

--错误的混合传参

SQL>  exec add_employee(1113,name=>'MACLE', 3000, job=>'CLERK',dno=> 20);

begin add_employee(1113,name=>'MACLE', 3000, job=>'CLERK',dno=> 20); end;

ORA-06550: 第 1 行, 第 40 列:

PLS-00312: 一个定位相关参数没有说明其相关性

ORA-06550: 第 1 行, 第 7 列:

PL/SQL: Statement ignored

--pl/sql 调用存储过程

declare

emp_20000 exception;

pragma exception_init(emp_20000, -20000);

emp_20001 exception;

pragma exception_init(emp_20001, -20001);

emp_20002 exception;

pragma exception_init(emp_20002, -20002);

begin

--异常。部门不存在

add_employee_1(7369,'MARAY',2010,'SALESMAN',66);

EXCEPTION

when emp_20000 then

dbms_output.put_line( 'emp_20000 雇员编码不能重复');

when emp_20001 then

dbms_output.put_line( 'emp_20001 部门编号不能为空');

when emp_20002 then

dbms_output.put_line( 'emp_20002 不存在该部门编号');

when others then

dbms_output.put_line( '出现了其他异常错误');

end;