Oracle数据库学习笔记 （六 —— 开发子程序和包）

基本内容

1. 过程
2. 函数
3. 程序包

基本要求：

1. 掌握过程的创建于使用
2. 账务函数的创建于使用
3. 掌握包的创建与使用

一、什么是子程序？

答:

1. 子程序是指被命名的PL/SQL块，这种块可以带有参数，可以在不同应用中多次调用
2. PL/SQL有两种类型的子程序：过程和函数*
3. 过程用于执行特定的操作，而函数用于返回特定数据

二、过程

2.1 开发过程

开发过程

过程一般用于执行一个指定的操作，可以将常用的特定操作封装成过程

CREATE [OR REPLACE] PROCEDURE procedure_name
(argument1 [mode1] datatype1, 
 argument2 [mode2] datatype2, ...)
IS [AS]
声明部分
BEGIN
执行部分
EXCEPTION
异常处理部分
END;

2.2 创建过程：无参数

• 创建一个无参的过程
• 执行过程 （三种方法）

创建

-- 创建一个过程
create table dept1 as select * from dept -- 复制 dept 表

select * from dept1

create or replace procedure pro_dept_del -- 创建一个删除的过程
as 
begin
  delete from dept1 where DEPTNO=10;
end;  

执行

call pro_dept_del();

begin
	 pro_dept_del;
end;

exec pro_dept_del

2.4 创建过程：带有 IN 参数

• 当为过程定义参数时，如果不指定参数模式，则默认为输入参数

小测试：

根据输入的员工编号输出该员工的工资

create or replace procedure 
pro_query_emp(v_no in emp.empno%type)
as
v_sal emp.sal%type;
begin  
  	select sal into v_sal from emp where empno=v_no;
  	dbms_output.put_line('该员工薪水为：'||v_sal);
  	exception
   		 when no_data_found then
      			dbms_output.put_line('找不到该员工！');
end;

2.5 创建过程：带有 out 参数

• 过程不仅可以用于执行特定操作，还可以用于输出数据
• 在过程中输出数据时，需要使用OUT或IN OUT参数来完成
• 修改刚才的过程让员工工资作为输出参数

create or replace procedure 
pro_query_emp(v_no in emp.empno%type, out_sal out number)
as
begin  
  	select sal into out_sal from emp where empno=v_no;
  	exception
   		 when no_data_found then
      			dbms_output.put_line('找不到该员工！');
end;

调用带有 out 的参数

• 必须定义变量接收输出参数

declare
	v_no emp.empno%type;
	v_sal emp.sal%type;
begin
  	v_no:=&no;
  	pro_emp_sal(v_no,v_sal);
  	dbms_output.put_line('薪水是：'||v_sal);
end;

2.6 创建过程：带有IN OUT 参数

• IN OUT参数也称为输入输出参数，当使用这种参数时，在调用过程之前需要通过变量给该种参数传递数据，调用结束后，Oracle会通过该变量将过程结果传递给应用

alter table dept1 add(level1 number(2)); -- 给 dept1 添加一列数据
select * from dept1

-- 创建查询的过程
create or replace procedure pro_dept_query(v_no in dept.deptno%type,v_loc out dept.loc%type)
as 
begin
  select loc into v_loc  from dept  where deptno=v_no;
end;  

-- 调用过程
declare
   v_no  dept.deptno%type;--部门编号
   v_loc dept.loc%type;--部门的所在地
begin
   v_no:=&no;
   pro_dept_query(v_no,v_loc);
   dbms_output.put_line(v_loc);
end;   
      

2.7 过程多参传递

• 使用过程进行多餐传递
• 为形参传递变量和数据采用
  • 位置传递
  • 名称传递
  • 组合传递

-- 定义插入的过程
create or replace procedure 
pro_add_dept(v_deptno number,v_dname varchar2, v_loc varchar2)
as
begin
  insert into dept values(v_deptno,v_dname,v_loc);  
end;

2.7.1 按位置传递

• 按位置传递按位置传递是指在调用时按参数的排列顺序依次写出实参的名称，将形参与实参关联起来进行传递
• 在这种方法中，形参与实参的名称是相互独立、没有关系的，次序才重要

-- 按位置传参
exec pro_add_dept(70,'研发部','北京');




-- 指定参数的名称通过 => 来实现
call pro_dept1_insert(v_deptno=>50,v_loc=>'东京',v_name=>'cc');
-- 混合
call pro_dept1_insert(60,v_loc=>'广东',v_name=>'dd');

2.7.2 按名称传参

• 按名称传递是指在调用时按照形参与实参的名称写出实参所对应的形参，将形参与实参关联起来进行传递
• 在这种方法中，形参与实参的名称是相互独立、没有关系的，名称的对应关系很重要，但次序不重要
• 名称传递在调用子程序时指定参数名，并使用关联符号“=>”为其提供相应的数值或变量

call pro_add_emp(v_deptno=>90,v_loc=>'南京',v_dname=>'软件部');

2.7.3 组合传递

• 可以将按位置传递、按名称传递两种方法在同一调用中混合使用
• 但前面的实参必须使用按位置传递方法，而后面其余的实参则可以使用按名称传递的方法

call pro_add_emp(90,v_loc=>'南京',v_dname=>'软件部');

三、函数

• 函数用于返回特定数据，如果在应用程序中经常需要通过执行SQL语句来返回特定数据，则可以基于这些操作创建特定的函数

语法：

 CREATE [OR REPLACE] FUNCTION function_name
 (argument1 [model] datatype1,
  argument2 [mode2] datatype2,
 ...)
 RETURN datatype
 IS|AS
   声明部分
 BEGIN
     执行部分
 EXCEPTION 
  异常处理部分
 END;

3.1 创建函数

• 当创建函数时，通过使用输入参数，可以将应用的数据传递到函数中，最终通过执行函数可以将结果返回到应用程序中
• 当定义参数时，如果不指定参数模式，则默认为输入参数

-- 创建函数
create or replace function fun_getrandom return number
as
v_num number;--存储返回值
begin
  	v_num:=floor(dbms_random.value(1,10));--产生随机数
  	return v_num;--返回随机数
end;

-- 使用函数
declare
	num number;
begin
  	num:=fun_getrandom();
  	dbms_output.put_line(num);
end;

3.2 创建带输出的函数

• 输入员工编号，获得员工所在部门

create function getDept(eno number,deptName out VARCHAR2)
return VARCHAR2
as
v_address VARCHAR2(40);
begin
  select dName, loc into deptName, v_address from dept, emp
  where dept.deptno= emp.deptno and empno=eno;
  return v_address;  
end;

declare 
address VARCHAR2(30);
deptName VARCHAR2(20);
begin
  address:=getDept(7654,deptName);
  dbms_output.put_line('部门名称:'||deptName);
  dbms_output.put_line('部门地址:'||address);
end;

3.3.3 过程 与 函数 的比较

• 过程与函数的相同功能及特性

  • 都使用IN模式的参数传入数据、OUT模式的参数返回数据
  • 输入参数都可以接收默认值，都可以传值
  • 调用时的实参都可以使用位置表示法或名称表示法
  • 都有声明部分、执行部分和异常处理部分

• 一般而言，如果需要返回多个值或不返回值，就使用过程
  如果只需要返回一个值，就使用函数

• 虽然函数带OUT模式的参数也能返回多个值，但是一般都认为这种方法属于不好的编程习惯或风格

• 过程一般用于执行一个指定的动作，函数一般用于计算和返回一个值

四、包

4.1 创建包

• 包（Package）用于组合逻辑相关的PL/SQL类型、PL/SQL项和PL/SQL子程序
• 通过使用PL/SQL包，不仅可以简化应用设计，提高应用性能，还可以实现信息隐藏、子程序重载等功能
• 包由包规范和包体两部分组成
• 当创建包时，需要首先创建包规范，然后再创建包体

注意：

1. 包规范下你跟对接口
2. 包体相当于接口的实现

4.2 创建包规范

• 包规范是包与应用程序之间的接口，用于定义包的公用组件，包括常量、变量、游标、过程和函数
• 在包规范中所定义的公用组件不仅可以在包内引用，而且还可以由其他的子程序引用
• 创建包规范时需要注意的是：为了实现信息隐藏，不应该将所有组件全部放在包规范处定义，而应该只定义公用组件

语法：

create or replace package dbutil_package is
pi constant number(10,7):=3.1415926; -- 定义常量
function getarea(radius number) return  number; -- 定义函数及返回值
procedure print_area;
end dbutil_package;

4.3 创建包体

• 为了实现包规范中所定义的公用过程和函数，必须创建包体
• 包体用于实现包规范所定义的过程和函数
• 在创建包时，为了实现信息隐藏，应该在包体内定义私有组件

语法：

CREATE [OR REPLACE] PACKAGE  BODY package_name
IS | AS
private type and item declarations
subprogram bodies
END package_name;

使用包体

create or replace package  body dbutil_package as
	area number(10);
	function getarea(radius number) return number is
	begin
  		area:=pi*radius*radius;
  		return area;
	end;

	procedure print_area is
	begin
  		dbms_output.put_line('圆的面积是：'||area);
	end;
end dbutil_package;

4.4 调用包的组件

• 对于包的私有组件，只能在包内调用，并且可以直接调用
• 对于包的公用组件，既可以在包内调用，又可以在其他应用中调用
• 在调用同一包内其他组件，可直接调用，不需要加包名作为前缀

4.5 调用包的公用变量 、过程、函数

当在其他应用中调用包的公用变量时，必须在公用变量、过程、函数名前添加包名作为前缀

调用包

declare
	area number(10,7);
begin
  	area:=dbutil_package.getarea(3);
	dbms_output.put_line('由function返回的面积：'||area);
  	dbutil_package.print_area;
end;

五、总结

5.1 创建过程

CREATE [OR REPLACE] PROCEDURE procedure_name
(argument1 [mode1] datatype1, argument2 [mode2] datatype2, ...)
IS [AS]
声明部分
BEGIN
执行部分
EXCEPTION
异常处理部分
END;

5.2 创建函数

 CREATE [OR REPLACE] FUNCTION function_name
   (argument1 [model] datatype1,
    argument2 [mode2] datatype2,
...)
RETURN datatype
 IS|AS
   声明部分
  BEGIN
     执行部分
 EXCEPTION 
  异常处理部分
END;

5.3 创建包

CREATE [OR REPLACE] PACKAGE package_name -- 创建公有的
IS|AS
public type and item declarations
subprogram specifications
END package_name;

CREATE [OR REPLACE] PACKAGE  BODY package_name -- 创建私有的
IS | AS
private type and item declarations
subprogram bodies
END package_name;