点击“蓝字”关注我们
晟数学院DBA成长日记--PL/SQL篇
子程序
目标：
掌握子程序的分类；
掌握过程与函数的定义及区别；
掌握子程序的参数模式；
子程序
子程序定义
在开发之中经常会出现一些重复的代码块，Oracle为了方便管理这些代码块，往往会将其封装到一个特定的结构体之中，这样的结构体在Oracle之中就被称为子程序，定义为子程序的代码块也将成为Oracle数据库的对象，会将其对象信息保存在相应的数据字典之中。在Oracle中子程序分为两种：过程、函数。
定义过程
过程指的是在大型数据库系统之中，专门定义的一组SQL语句集，它可以定义用户操作参数，并且存在于数据库之中，当使用时直接调用即可，在Oracle之中，可以使用以下的语法来定义存储过程。
CREATE [OR REPLACE] PROCEDURE 过程名称([参数名称 [参数模式]
NOCOPY 数据类型 [,参数名称 [参数模式] NOCOPY 数据类型 , ….]])
[AUTHID [DEFINER | CURRENT_USER]]
AS | IS
[PRAGMA AUTONOMOUS_TRANSACTION ;]
声明部分 ;
BEGIN
程序部分 ;
EXCEPTION
异常处理 ;
END;
/
本语法中的部分解释如下:
参数中定义的参数模式表示过程的数据接收操作，一般分为IN、 OUT、IN OUT3类。
CREATE [OR REPLACE]: 表示创建或替换过程，如果此过程存在则替换，如果不存在则创建一个新的。
AUTHID 子句定义了一个过程的所有者权限，DEFINER (默认)表示为定义者权限执行，或者使用CURRENT_ USER覆盖程序的默认行为，变为使用者权限执行。
PRAGMA AUTONOMOUS _TRANSACTION:表示由过程启动一个自治事务，自治事务可以让主事务挂起，在过程中执行完SQL后，由用户处理提交或回滚自治事务，然.后再恢复主事务。
（1）定义一个简单的过程
SCOTT@SDEDU> ed11702.sql
create or replace procedure sandata_proc
as
begin
dbms_output.put_line('www.sandata.com');
end;
/
SCOTT@SDEDU> @11702
Procedure created.
SCOTT@SDEDU> exec sandata_proc;
www.sandata.com
PL/SQL procedure successfully completed.
SCOTT@SDEDU> select object_name from user_objects;
OBJECT_NAME
---------------------------
GET_EMP_INFO_PROC
SANDATA_PROC
MYSEQ
EMP_DEPTNO_IND
…………
…………
PK_DEPT
DEPT
60 rows selected.
（2）定义过程，根据雇员编号找到雇员姓名及工资
SCOTT@SDEDU> ed11703.sql
create or replace procedure get_emp_info_proc(p_eno emp.empno%type)
as
v_ename emp.ename%type;
v_sal emp.sal%type;
v_count number;
begin
select count(empno) into v_count from emp where empno=p_eno;
if v_count=0 then --没有发现数据
return; --结束过程调用
end if;
select ename,sal into v_ename,v_sal from emp where empno=p_eno;
dbms_output.put_line('number is: '||p_eno||' ,name is: '||v_ename||' ,sal is: '||v_sal);
end;
/
SCOTT@SDEDU> @11703
Procedure created.
SCOTT@SDEDU> exec get_emp_info_proc(7369);
number is: 7369 ,name is: SMITH ,sal is: 800
PL/SQL procedure successfully completed.
函数
函数（又称存储函数）也是一种较为方便的存储结构，用户定义的函数可以被SQL语句或者是PL/SQL程序直接进行调用，实际上函数与过程最大的区别就在于，函数是可以有返回值的，而过程 只能依靠OUT或IN OUT来返回数据。
函数定义语法：
CREATE [OR REPLACE] FUNCTION 函数名([参数 , [参数 , ...]])
RETURN 返回值类型
[AUTHID {DEFINER | CURRENT_USER}]
AS | IS
[PRAGMA AUTONOMOUS_TRANSACTION ;]
声明部分 ;
BEGIN
程序部分 ;
[RETURN 返回值 ;]
[EXCEPTION
异常处理]
END [函数名] ;
/
（3）定义函数 —— 通过雇员编号查找此雇员的月薪
SCOTT@SDEDU> ed11705.sql
--通过编号查询员工工资
create or replace function get_salary_fun(p_eno emp.empno%type)
return number
as
v_salary emp.sal%type;
begin
select sal+nvl(comm,0) into v_salary from emp where empno=p_eno;
return v_salary;
end;
/
SCOTT@SDEDU> @11705
Function created.
函数的执行方式：
SCOTT@SDEDU> select get_salary_fun(7369) from dual;
GET_SALARY_FUN(7369)
------------------------------------------
800
（4）函数的另一种执行方式：通过PL/SQL块验证函数
SCOTT@SDEDU> declare
2 v_eno number;
3 v_sal number;
4 begin
5 v_eno:=&input;
6 v_sal:=get_salary_fun(v_eno);
7 dbms_output.put_line(v_sal);
8 end;
9 /
Enter value for input: 7369
old 5: v_eno:=&input;
new 5: v_eno:=7369;
800
PL/SQL procedure successfully completed.
SCOTT@SDEDU> ed11706
--通过PL/SQL块验证函数
declare
v_salary number;
begin
v_salary:=get_salary_fun(7369);
dbms_output.put_line('7369`s sal is: '||v_salary);
end;
/
SCOTT@SDEDU> @11706
7369`s sal is: 800
PL/SQL procedure successfully completed.
推荐阅读
晟数学院DBA成长日记
晟数学院DBA成长日记
晟数学院DBA成长日记
