ORACLE 第16讲 开发PL SQL子程序
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
第16讲开发PL/SQL子程序
1、使用过程
过程定义：
CREATE[OR REPLACE]PROCEDURE过程名(参数1定义，参数2定义...)
IS|AS
变量声明部分
BEGIN
可执行部分
EXCEPTION
异常处理部分
END;
参数的定义形式：
参数名参数传递模式数据类型:=默认值
其中参数名和数据类型是必不可少的部分，其他两部分是可以省略的。参数传递模式包括IN、OUT以及IN OUT三种形式，其中IN是默认的传递模式，如果没有指定，则默认为IN，它指的是从调用者向过程中传递一个实际参数。
OUT是指从过程中向调用者传递参数，如果要使用这种传递模式，则需要明确指定。
在调用过程时，过程的执行情况会影响这个变量的值。
“IN OUT”是一种双向传递模式，一方面从调用者向过程传递参数，另一方面从过程向调用者传递结果，如果要使用这种形式，则需要明确指定。
“:=默认值”等价于“DEFAULT默认值”。
删除过程：
DROP PROCEDURE过程名;
例1：编写一个添加员工基本信息的过程。
解决步骤：
（1）不考虑异常的解决方法
最简单的过程：
create or replace procedure add_emp(
v_empno number,
v_ename varchar2,
v_sal number,
v_job varchar2default'CLERK',
v_deptno in emp.deptno%type
)
is
begin
insert into emp(empno,ename,sal,job,deptno)
values(v_empno,v_ename,v_sal,v_job,v_deptno); end;
/
执行过程：
SQL>exec add_emp(1000,'张三',1500,'CLERK',10);
（2）考虑异常的解决方法
带有异常处理的过程：
create or replace procedure add_emp(
v_empno number,
v_ename varchar2,
v_sal number,
v_job varchar2default'CLERK',
v_deptno in emp.deptno%type
)
is
e_deptno exception;
pragma exception_init(e_deptno,-02291);
begin
insert into emp(empno,ename,sal,job,deptno)
values(v_empno,v_ename,v_sal,v_job,v_deptno);
exception
when dup_val_on_index then
raise_application_error(-20000,'雇员号不能重复');
when e_deptno then
raise_application_error(-20001,'部门号不存在'); end;
/
执行过程：
SQL>exec add_emp(1002,'张三',1500,'CLERK',10);
例2：编写一个查询雇员工资的过程
解决步骤：
（1）创建带有输出参数过程：
create or replace procedure get_sal(
v_empno in emp.empno%type,
v_sal out emp.sal%type
)
is
begin
select sal into v_sal from emp where empno=v_empno;
exception
when no_data_found then
raise_application_error(-20000,'雇员编号没找到');
end;
/
（2）调用过程：
set serveroutput on;
declare
v_sal emp.sal%type;
begin
get_sal(&empno,v_sal);
dbms_output.put_line('雇员工资：'||v_sal);
end;
/
练习1：编写一个添加部门信息的过程。
练习2：编写一个依据部门号查询部门名称的过程。
2、使用函数
函数是另一种形式的子程序，它不仅可以像过程那样定义数据和类型，传递参数，还可以向调用者返回执行结果。
函数的定义：
CREATE[OR REPLACE]FUNCTION函数名(参数1，参数2...)RETURN数据类型
IS|AS
声明部分
BEGIN
可执行部分
EXCEPTION
异常处理部分
END;
其中参数的定义、传递模式都与在过程中的情况是相同的。在函数头部必须带RETURN 子句。
删除函数：
DROP FUNCTION函数名;
例：依据部门编号，求部门平均工资。