Oracle专题15之包

Oracle 前端之家
前端之家收集整理的这篇文章主要介绍了Oracle专题15之包，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。

1、包的概述

a、什么是包？

  • 包是一组相关过程、函数、变量、常量和游标等PL/sql程序设计元素的组合。

b、包的特点？

  • 它具有面向对象程序设计语言的特点，是对PL/sql程序设计元素（过程、函数、变量等）的封装。
  • 它使程序设计模块化。

c、包中的程序元素

  • 包中的程序元素分为两种：公用元素（公用组件）、私用元素（私用组件）。

e、包的组成

  • 一个包由两个分开的部分组成：
  • 包规范（包定义）：用于定义包的公用组件，包括常量、变量、游标、过程和函数等。
  • 包体（包主体）：用于实现包规范所定义的公用过程和函数。包体不仅可用于实现公用过程和函数，而且还可以定义包的私有组件（变量、游标、过程、函数等）。

f、简单代码示例

  • 创建包规范和包体的示例代码
--创建包规范
	CREATE OR REPLACE PACKAGE first_package
	IS
		v_no emp.deptno%TYPE := 10;
		--过期
		PROCEDURE query_emp(v_deptno IN NUMBER DEFAULT v_no,v_avgsal OUT NUMBER,v_cnt OUT NUMBER);
	END first_package;
	--创建包体
	CREATE OR REPLACE PACKAGE BODY first_package
	IS
				 PROCEDURE query_emp(v_deptno IN NUMBER DEFAULT v_no,v_avgsal) OUT NUMBER,v_cnt OUT NUMBER)
				 IS
				 BEGIN
					 SELECT avg(sal),count(*) INTO v_avgsal,v_cnt FROM emp WHERE deptno = v_deptno;
				 EXCEPTION
					 WHEN NO_DATA_FOUND THEN
						 dbms_output.put_line('没有此部门');   
					 WHEN OTHERS THEN
						 dbms_output.put_line(sqlERRM);
					 END;
	END first_package;
	```
-  调用包中的存储过程：
DECLARE
	v_avgsal NUMBER;
	v_cnt NUMBER;
BEGIN
	first_package.query_emp(20,v_avgsal,v_cnt);
	DBMS_OUTPUT.put_line('平均工资：' || v_avgsal);
	DBMS_OUTPUT.put_line('总人数：' || v_cnt);
END;
# 2、包的创建
## a、创建包规范
-  语法格式如下：

CREATE [OR REPLACE] PACKAGE package_name IS | AS -- 定义公用常量、变量、游标、过程、函数等 END [package_name];

-  示例代码：
	```
	CREATE OR REPLACE PACKAGE emp_package
	IS
				 --添加员工信息的存储过程
				 PROCEDURE add_emp_proc
				 (v_empno IN emp.empno%TYPE,v_ename IN emp.ename%TYPE,v_sal IN emp.sal%TYPE,v_deptno IN emp.deptno%TYPE);

				 --删除员工信息的存储过程
				 PROCEDURE del_emp_proc
				 (v_empno IN emp.empno%TYPE);
	END emp_package;

b、创建包体

  • 语法格式如下：
CREATE [OR REPLACE] PACKAGE BODY package_name 
	IS | AS
	--定义私有常量、变量、游标、过程和函数等
	--实现公用过程和函数
	END [package_name];
CREATE OR REPLACE PACKAGE BODY emp_package
	IS    
				--添加员工信息的存储过程
				 PROCEDURE add_emp_proc
				 (v_empno IN emp.empno%TYPE,v_deptno IN emp.deptno%TYPE)
				 IS
									e_2291 EXCEPTION;
									PRAGMA EXCEPTION_INIT(e_2291,-2291);
				 BEGIN
					 INSERT INTO emp(empno,ename,sal,deptno) VALUES(v_empno,v_ename,v_sal,v_deptno);
				 EXCEPTION
					 WHEN DUP_VAL_ON_INDEX THEN
						 RAISE_APPLICATION_ERROR(-20001,'员工号不能重复');
					 WHEN e_2291 THEN
						 RAISE_APPLICATION_ERROR(-20002,'部门号不存在');
				 END;

				 --删除员工信息的存储过程
				 PROCEDURE del_emp_proc
				 (v_empno IN emp.empno%TYPE)
				 IS
				 BEGIN
					 --根据员工号删除指定的员工信息
					 DELETE FROM emp WHERE empno = v_empno;
					 --判断是否删除成功
					 IF sql%NOTFOUND THEN
						 RAISE_APPLICATION_ERROR(-20009,'指定删除的员工不存在');
					 ELSE
						 DBMS_OUTPUT.PUT_line('删除成功');
					 END IF;
				 END;
	END emp_package;

c、包创建的过程案例代码

  • 根据员工号查询工资，如果工资小于等于3000，工资涨500。
--创建包规范
	CREATE OR REPLACE PACKAGE emp_sal_pkg
	IS
				 FUNCTION get_sal(eno NUMBER) RETURN NUMBER;

				 PROCEDURE upd_sal(eno NUMBER,salary NUMBER);
	END emp_sal_pkg;

	--包体
	CREATE OR REPLACE PACKAGE BODY emp_sal_pkg
	IS
				 FUNCTION get_sal(eno NUMBER) RETURN NUMBER
				 IS
									v_sal emp.sal%TYPE := 0;
				 BEGIN
									SELECT sal INTO v_sal FROM emp WHERE empno = eno;
									RETURN v_sal;
				 EXCEPTION
									WHEN NO_DATA_FOUND THEN
										RAISE_APPLICATION_ERROR(-20010,'此员工号不存在');
				 END;

				 PROCEDURE upd_sal(eno NUMBER,salary NUMBER)
				 IS
				 BEGIN
					 IF salary <=3000 THEN
						 UPDATE emp SET sal = sal + 500 WHERE empno = eno;
					 END IF;
				 END;
	END emp_sal_pkg;

3、包的调用和删除

a、包的调用

  • 对包内共有元素（公用组件）的调用格式为：包名.元素名称（组件名称）
  • 例如：emp_package.del_emp_proc();

b、 包的调用示例1

  • 调用emp_package包下添加员工信息的存储过程。（在本专题的第2节包的创建，已经在Oracle数据库中创建emp_package包规范和包体） 
DECLARE 
		v_empno emp.empno%TYPE := &empno;
		v_ename emp.ename%TYPE := '&name';
		v_sal emp.sal%TYPE := &salary;
		v_deptno emp.deptno%TYPE := &deptno;
		e_dup_val EXCEPTION;
		e_no_dept EXCEPTION;

		PRAGMA EXCEPTION_INIT(e_dup_val,-20001);
		PRAGMA EXCEPTION_INIT(e_no_dept,-20002);
	BEGIN
		emp_package.add_emp_proc(v_empno,v_deptno);
		COMMIT;
	EXCEPTION
		WHEN e_dup_val THEN
			DBMS_OUTPUT.put_line(sqlERRM);
		WHEN e_no_dept THEN
			DBMS_OUTPUT.put_line(sqlERRM);
			ROLLBACK;
	END;

b、包的调用示例2

  • 调用emp_package包下删除员工信息的存储过程。（在本专题的第2节包的创建，已经在Oracle数据库中创建emp_package包规范和包体） 
DECLARE 
		v_empno emp.empno%TYPE := &empno;
		e_no_emp EXCEPTION;
		PRAGMA EXCEPTION_INIT(e_no_emp,-20009);
	BEGIN
		emp_package.del_emp_proc(v_empno);
		COMMIT;
	EXCEPTION
		WHEN e_no_emp THEN
			DBMS_OUTPUT.put_line(sqlERRM);
			ROLLBACK;
	END;

c、包的调用示例3

  • 调用emp_sal_pkg包下的过程和函数：（在本专题的第2节包的创建，已经在Oracle数据库中创建emp_sal_pkg包规范和包体） 1、调用根据员工号返回员工工资的函数 2、调用更新满足条件的员工工资的过程
DECLARE
		v_empno emp.empno%TYPE := &empno;
		v_salary emp.sal%TYPE;
		e_no_emp EXCEPTION;
		PRAGMA EXCEPTION_INIT(e_no_emp,-20010);
	BEGIN
		v_salary := emp_sal_pkg.get_sal(v_empno);
		emp_sal_pkg.upd_sal(v_empno,v_salary);
		COMMIT;
	EXCEPTION
		WHEN e_no_emp THEN
			DBMS_OUTPUT.put_line(sqlERRM);
	END;

d、如何在sqlPlus窗口中进行包的调用

  • 示例1：在sqlPlus窗口中进行emp_sal_pkg包的调用，查询员工的工资信息：
sql> VAR v_empno NUMBER
	sql> EXEC :v_empno := &no

	PL/sql procedure successfully completed

	v_empno
	---------
	1234

	sql> VAR v_salary NUMBER
	sql> EXEC :v_salary := emp_sal_pkg.get_sal(:v_empno)
	begin :v_salary := emp_sal_pkg.get_sal(:v_empno); end;

	ORA-20010: 此员工号不存在
	ORA-06512: 在 "SCOTT.EMP_SAL_PKG",line 11
	ORA-06512: 在 line 1
	v_salary
	---------
	v_empno
	---------
	1234
  • 示例2：在sqlPlus窗口中进行emp_sal_pkg包的调用，更新员工的工资信息：（变量v_empno已经在示例1中定义）
sql> EXEC :v_empno := &no

	PL/sql procedure successfully completed

	v_empno
	---------
	7369

	sql> EXEC :v_salary := emp_sal_pkg.get_sal(:v_empno)

	PL/sql procedure successfully completed

	v_salary
	---------
	800
	v_empno
	---------
	7369

	sql> EXEC emp_sal_pkg.upd_sal(:v_empno,:v_salary)

	PL/sql procedure successfully completed

	v_empno
	---------
	7369
	v_salary
	---------
	800

	sql> SELECT ename,sal from emp WHERE empno = 7369;

	ENAME            SAL
	---------- ---------
	G_EASON      1300.00
  • 在sqlPlus环境中，可以使用 [VAR 变量名 变量类型]命令定义变量。

e、包的删除

  • 可以使用DROP PACKAGE命令对不需要的包进行删除，语法如下：
DROP PACKAGE [BODY] [user.] package_name;

4、子程序重载

a、什么是子程序重载？

  • 所谓重载是指两个或者多个子程序有相同的名称，但是拥有不同的参数变量、参数顺序或者参数数据类型。

b、示例代码

  • 示例：1、根据员工号或者员工姓名获取员工信息；2、根据员工号或者员工姓名删除员工的信息。
--创建包规范
	CREATE OR REPLACE PACKAGE overload_pkg
	IS
				 FUNCTION get_info(eno NUMBER) RETURN emp%ROWTYPE;
				 FUNCTION get_info(name VARCHAR) RETURN emp%ROWTYPE;

				 PROCEDURE del_emp(eno NUMBER);
				 PROCEDURE del_emp(name VARCHAR);
	END;
	--创建包体
		CREATE OR REPLACE PACKAGE BODY overload_pkg
	IS
				 FUNCTION get_info(eno NUMBER) RETURN emp%ROWTYPE
				 IS
									emp_record emp%ROWTYPE;
				 BEGIN
									SELECT * INTO emp_record FROM emp WHERE empno = eno;
									RETURN emp_record;
				 EXCEPTION
									WHEN NO_DATA_FOUND THEN
											 RAISE_APPLICATION_ERROR(-20020,'不存在此员工');
				 END;

				 FUNCTION get_info (name VARCHAR) RETURN emp%ROWTYPE
				 IS
									emp_record emp%ROWTYPE;
				 BEGIN
									SELECT * INTO emp_record FROM emp WHERE ename= name;
									RETURN emp_record;
				 EXCEPTION
									WHEN NO_DATA_FOUND THEN
										RAISE_APPLICATION_ERROR(-20020,'不存在此员工');
				 END;

				 PROCEDURE del_emp(eno NUMBER)
				 IS
				 BEGIN
					 DELETE FROM emp WHERE empno = eno;
					 IF sql%NOTFOUND THEN
						 RAISE_APPLICATION_ERROR(-20020,'不存在此员工');
					 END IF;
				 END;

				 PROCEDURE del_emp(name VARCHAR)
				 IS
				 BEGIN
					 DELETE FROM emp WHERE ename = name;
					 IF sql%NOTFOUND THEN
						 RAISE_APPLICATION_ERROR(-20020,'不存在此员工');
					 END IF;
				 END;
	END overload_pkg;
  • overload_pkg包的调用示例：
--根据员工号查询员工信息
	DECLARE
		emp_record emp%rowtype;
		e_no_emp EXCEPTION;
		PRAGMA EXCEPTION_INIT(e_no_emp,-20020);
	BEGIN 
		emp_record := overload_pkg.get_info(&no);
		DBMS_OUTPUT.put_line('员工号：' || emp_record.empno || '，姓名：' || emp_record.ename || '，工资：' || 
		emp_record.sal);
	EXCEPTION
		WHEN e_no_emp THEN
			DBMS_OUTPUT.put_line(sqlERRM);
	END;
--根据员工姓名查询员工信息
	DECLARE
		emp_record emp%rowtype;
		e_no_emp EXCEPTION;
		PRAGMA EXCEPTION_INIT(e_no_emp,-20020);
	BEGIN 
		emp_record := overload_pkg.get_info('&ename');
		DBMS_OUTPUT.put_line('员工号：' || emp_record.empno || '，姓名：' || emp_record.ename || '，工资：' || 
		emp_record.sal);
	EXCEPTION
		WHEN e_no_emp THEN
			DBMS_OUTPUT.put_line(sqlERRM);
	END;
--根据员工号删除员工信息
	DECLARE 
		e_no_emp EXCEPTION;
		PRAGMA EXCEPTION_INIT(e_no_emp,-20020);
	BEGIN
		overload_pkg.del_emp(&no);
		COMMIT;
	EXCEPTION
		WHEN e_no_emp THEN
			DBMS_OUTPUT.put_line(sqlERRM);
		ROLLBACK;
	END;
--根据员工姓名删除员工信息
	DECLARE 
		e_no_emp EXCEPTION;
		PRAGMA EXCEPTION_INIT(e_no_emp,-20020);
	BEGIN
		overload_pkg.del_emp('&ename');
		COMMIT;
	EXCEPTION
		WHEN e_no_emp THEN
			DBMS_OUTPUT.put_line(sqlERRM);
		ROLLBACK;
	END;

猜你在找的Oracle相关文章

[Oracle]记一次由sequence引发的enq sv-contention等待事件
数据库版本：11.2.0.4 RAC(1)问题现象从EM里面可以看到，在23号早上8：45~8：55时，数据库...
oracle统计信息的锁定与解锁
（一）问题背景最近在对一个大约200万行数据的表查看执行计划时，发现存在异常，理论上应该...
【转】RMAN删除过期备份或非过期备份
（一）删除备份--DELETE命令用于删除RMAN备份记录及相应的物理文件。当使用RMAN执行备份操...
数据恢复顾问（DRA）
（1）DRA介绍 数据恢复顾问（Data Recovery Advise）是一个诊断和修复数据库的工具，DRA能...
RMAN备份与恢复（一）--认识RMAN
RMAN（Recovery Manager）是Oracle恢复管理器的简称，是集数据库备份（backup）、修复（re...
RMAN备份与恢复（二）--RMAN常用操作学习
（1）连接目标数据库 在RMAN中可以建立与目标数据库或恢复目录数据库的连接。与目标数据库...
ORA-00245问题总结
（1）问题描述 在进行数据库归档备份时（备份归档日志文件和控制文件），有时成功，有时失...
使用RMAN对数据文件进行恢复
（1）备份数据库 在使用RMAN进行数据库恢复之前，先用RMAN进行全库备份 （2）删除数据文件...
Oracle使用fy_recover_data恢复truncate删除的数据
(一)truncate操作概述在生产中，truncate是使用较多的命令，在使用不当的情况下，往往会造...
备份与恢复（四）--RMAN使用恢复目录catalog来保存档案资料库
（一）恢复目录概述RMAN档案资料库用于存储数据库备份、修复以及恢复所需的信息。这些信息...
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLHBaseJDBC
undo日志persistent-cmysql-error-postal-codesql-match-almysql-5.6mysql-8.0database-tri安装路径系统错误data_dir丢失文件主从同步sql_mode数据库目录匿名用户character_seID归零数据库位置查询表重复字段查询字段截断日志SUSPECT7391Remote ServeLinked Serve玄学问题登录不上开启远程访问