创建包第八章包

学时：4学时教学方法：讲授ppt2目标:本章旨在向学员介绍包的应用，通过本课的学习，学员应该掌握如下知识：1)描述包并且列出其可能的组成部分2创建包集中相关的变量、游标、常量、表达式、过程和函数3指定包结构为public或者为private调用包结构了解包的特性了解Oracle服务器提供的包包的概览打包了相关的PL/SQL类型、子程序到一个容器中由两个部分组成：––体包本身不能被调用、被参数化或被嵌套允许Oracle

服务器一次读多个对象到内存中用户只能

程序包的包头部分,包体被隐藏3包的构成4开发包5开发包(续)和包体到两个不同的SQL

文件中，方便以后保存包修改包包

可以没有包体单独存在，但包体不能没有包的声明而单独存在6创建包CREATE

[OR

REPLACE]

PACKAGEpackage_nameIS|AS公共变量、异常、游标、子程序等END

package_name;REPLACE选项删除并重新创建包在包

中的变量在默认情况下被初始化为NULL在包

中的所有被

的结构对于已

的用户都是可见的7CREATE

OR

REPLACE

PACKAGE

comm_packageISmPROCEDURENUMBER

:=

100;m( m

IN

NUMBER);END

comm_package;/创建包

(续)89创建包体CREATE

OR

REPLACE

PACKAGE

BODY

package_nameIS私有的变量、静态变量、游标、异常、类型、子程序、子程序体END

package_name;在包体中定义的标识符是私有结构，在包的外部不可见在包体中先定义私有变量和子程序，后定义公共子程序创建包体(续)1011创建包体(续)CREATE

OR

REPLACE

PACKAGEBODY

comm_packageISFUNCTIONreturnbooleanISm( m

IN

number)m

number;BEGINSELECT

max(comm)

INTOm

FROM

emp;IF m

> m

THEN

return(false);ELSEreturn

(true);END

IF;ENDprocedureISBEGINIFm;m(

mIN

number)m(m

:=m)

THENm;调用包结构从SQL*Plus

中调用包中的过程EXECUTE

comm

m(2000);从不同的方案中调用包中的过程EXECUTE

m

m(2000)调用包结构(续)通过

块调用包中的过程：SQL>BEGINcommm(2000);END;通过过程调用包中的过程：SQL>CREATE

OR

REPLACE

PROCEDURE

testISBEGINcomm

m(2000);END;SQL>exec

test13包的练习1、创建表test,表定义如下：empnoenamesalnumber(2),varchar2(10),number(7,2)empno是主健2、创建包的

test_pak,内容包含add_user过程、del_user过程(根据empno值删除用户）、add_sal函数（根据empno值确定用户，增加员工工资，并返回该用户的工资）3、创建包体，实现上述定义4、调用上述的每部分,验证正确性14CREATE

OR

REPLACE

PACKAGE

global_constsIS_2_kilo

CONSTANT

NUMBER

:=

1.6093;kilo_2_CONSTANT

NUMBER

:=

0.6214;yard_2_meter

CONSTANT

NUMBER

:=

0.9144;meter_2_yard

CONSTANT

NUMBER

:=

1.0936;END

global_consts;/EXECUTE

DBMS_OUTPUT.PUT_LINE(’20=’||20*global_consts._2_kilo||’km’)无体包15无体包(续)CREATE

OR

REPLACE

PROCEDURE

meter_to_yard(p_meter

IN

NUMBER,

p_yard

OUT

NUMBER)ISBEGINp_yard

:=

p_meter

*

global_consts.meter_2_yard;END

meter_to_yard;/VARIABLE

yard

NUMBEREXECUTE

meter_to_yard

(1,

:yard)PRINT

yard16删除包用下面的语法删除包

和包体：DROP

PACKAGE

package_name;下面的语法只删除包体：DROP

PACKAGE

BODY

package_name;17开发包的原则先定义包

，再定义包体包

中应该只包含公共结构改变包的

需要重新编译每个涉及到的子程序包体的变化不要求重新编译包的包

应该尽可能包含少的内容18包的好处模块性：封装相关的结构使应用程序设计容易：可以分别编码和编译

和体隐藏信息：–只有在包 中的定义是可见和可以被应用程序–在包体中的私有结构是隐藏的和不可

的–所有在包体中的代码是隐藏的更好的性能：–在包第一次被

时，整个包被装载到内存中–对所有用户来说只有一个拷贝在内存中1920重载(Overloading)程序包中的多个子程序可以具有相同的名字只能针对打包的子程序要求子程序形式参数的个数、顺序或数据类型不同如果子程序的参数仅名称或模式不同，则不能重载不能基于其返回类型重载函数21重载(续)CREATE

OR

REPLACE

PACKAGE

comm_packISm(m(m

in

number);m

in

varchar2);PROCEDUREPROCEDUREEND

comm_pack

;22重载(续)CREATE

OR

REPLACE

PACKAGE

BODY

comm_packISm(

m in

number)PROCEDUREISBEGINENDdbms_output.put_line('参数是数字');m;m( m

in

varchar2)PROCEDUREISBEGINENDdbms_output.put_line('参数是字符');m;END

comm_pack;FUNCTIONTO_CHAR(p1DATE)

RETURN

VARCHAR2;FUNCTIONTO_CHAR(p2NUMBER)

RETURN

VARCHAR2;FUNCTIONTO_CHAR(p1DATE,

P2

VARCHAR2)

RETURN

VARCHAR2;FUNCTIONTO_CHAR(p1NUMBER,

P2

VARCHAR2)

RETURN

VARCHAR2;重载(续)大部分的内建函数是重载的例如，STANDARD包中的TO_CHAR函数了一个内建子程序，局部声如果在一个PL/SQL程序中重新明将覆盖(override)全局2324重载(续)创建包test_pak，实现reset_sal的重载:reset_sal(p_sal

in

number,empno

in

number)reset_sal(ename

in

varchar2,

p_sal in

number)当执行test_pak.reset_sal(1,1)时，显示call

1.当执行test_pak.reset_sal(‘1’,1)时，显示call

2.前向CREATE

OR

REPLACE

PACKAGE

comm_packISMPROCEDUREnumber(7,2);m( m

IN

NUMBER);END

comm_pack;25前向(续)CREATE

OR

REPLACE

PACKAGE

BODY

comm_packISm(

m

IN

number)m(m:=m)

THENm;dbms_output.put_line('数据');PROCEDUREISBEGINIFELSEEND

IF;ENDm;FUNCTIONm( mIN

number)returnbooleanISv_max

number;BEGINSELECT

max(sal)

INTO

v_max

FROM

emp;IF m

>v_max THEN

return(false);ELSE

return

(true);END

IF;ENDm;END

comm_pack;2627前向(续)CREATE

OR

REPLACE

PACKAGE

BODY

comm_packISFUNCTION

m( m

INreturn

boolean;PROCEDUREm(m

IN

number)ISBEGINIFm(m)

THENm

:=m;ELSEnumber)dbms_output.put_line('数据');END

IF;END

m;FUNCTIONm(m

IN

number)return

booleanISv_max

number;BEGIN28在SQL中使用包函数的限制从查询语句或DML

语句中调用函数时,函数不能执行DML语句或修改数据库从查询或DML

语句中调用函数时,函数不能结束当前事务，不能创建或回退保存点，或者改变系统或会话从DML

语句中调用函数,函数不能读或修改正在被该DML语句修改的表CREATE

OR

REPLACE

PACKAGE

pack_curISCURSOR

c1

ISSELECT

empnoFROM

empORDER

BY

empno

DESC;PROCEDURE

proc1_rows;PROCEDURE

proc2_rows;END

pack_cur;/29包游标的持久状态包游标的持久状态(续)30CREATE

OR

REPLACE

PACKAGE

BODY

pack_cur

ISv_empno

NUMBER;PROCEDURE

proc1_rows

ISBEGINOPEN

c1;LOOPFETCH

c1

INTO

v_empno;DBMS_OUTPUT.PUT_LINE('Id

:'||(v_empno));EXIT

WHEN

c1%ROWCOUNT

>=

3;END

LOOP;END

proc1_rows;PROCEDURE

proc2_rows

ISBEGINLOOPFETCH

c1

INTO

v_empno;DBMS_OUTPUT.PUT_LINE('Id

:'||(v_empno));EXIT

WHEN

c1%ROWCOUNT

>=

5;END

LOOP;CLOSE

c1;END

proc2_rows;END

pack_cur;/SET

SERVEROUTPUT

ONEXECUTE

pack_c1_rowsEXECUTE

pack_c2_rows31包游标的持久状态(续)使用Oracle提供的包Oracle

提供的包：是Oracle

服务器提供的扩展了数据库的功能能够

一些通常对于PL/SQL受限的SQL

特性3233动态SQLCREATE

OR

REPLACE

procedure

delete_all_rows(p_tab_name

IN

VARCHAR2,p_rows_del

OUT

NUMBER)ISBEGINdelete

from

p_tab_name;p_rows_del

:=

SQL%ROWCOUNT;END

delete_all_rows;34动态SQL在PL/SQL中对于SQL语言的DDL和DCL语句不能够进行直接使用，而是需要使用动态SQL来实现相关语句的执行。实现动态SQL有两种方式：调用DBMS_SQL包和本地动态SQL。本地动态SQL是通过执行EXECUTE

IMMEIDATE命令来实现的。35SQL语句的执行步骤SQL

语句经历的各个阶段：解析(Parse)绑定(Bind)执行(Execute)取回(Fetch)注：某些阶段可能被跳过。使用DBMS_SQL包DBMS_SQL包用于在

过程中写动态SQL，并解析DDL

语句。包中的一些过程和函数：OPEN_CURSORPARSEBIND_VARIABLEEXECUTEFETCH_ROWSCLOSE_CURSOR3637使用DBMS_SQL包(续)CREATE

OR

REPLACEPROCEDURE

delete_all_rows(p_tab_name

IN

VARCHAR2,

p_rows_del

OUT

NUMBER)IScursor_nameINTEGER;BEGINcursor_name

:=

DBMS_SQL.OPEN_CURSOR;DBMS_SQL.PARSE(cursor_name,'DELETE

FROM

'||p_tab_name||'

where

sal

>

:x'

,DBMS_SQL.NATIVE

);DBMS_SQL.bind_variable(cursor_name,'x',3000);p_rows_del

:=

DBMS_SQL.EXECUTE

(cursor_name);DBMS_SQL.CLOSE_CURSOR(cursor_name);END;/用动态SQL删除行VARIABLE

deleted

NUMBEREXECUTE

delete_all_rows('emp',

:deleted)本地动态SQL本地动态SQL语法：语法说明：动态SQL是指一个字符串表达式,代表一个动态

SQL语句(不带结束符)或一个

PL/SQL块(带结束符)变量列表用于存放取回的列值，仅用于单行查询。绑定参数列表为传入参数值列表，即其类型为IN类型，在执行时与动态SQL中的参数（即占位符，可以理解为形参）进行绑定。在单行查询中使用INTO子句，但对于多行查询，必须用OPEN、FETCH

和CLOSE。EXECUTE

IMMEDIATE

‘动态SQL’[INTO

变量列表][USING

绑定参数列表];3839本地动态SQL(续)CREATE

OR

REPLACEPROCEDUREdel_rows(p_table_name

IN

VARCHAR2,p_rows_deld

OUT

NUMBER)ISv_str

varchar2(50);BEGINv_str:=

'delete

from

'||p_table_name||'where

sal>3000';EXECUTE

IMMEDIATE

v_str;p_rows_deld

:=

SQL%ROWCOUNT;END;/VARIABLEdeleted

NUMBEREXECUTE

del_rows('emp',:deleted)本地动态SQL(续)40CREATE

OR

REPLACE

PROCEDURE

test_sql(p_no

number)ISv_name

varchar2(10);v_loc

varchar2(10);BEGINEXECUTE

IMMEDIATE

'

select

dname,loc

from

deptwhere

deptno=:1'INTO

v_name,v_locUSING

p_no;DBMS_OUTPUT.PUT_LINE(v_name

||’所在地为'||v_loc);EXCEPTIONWHEN

OTHERS

THENDBMS_OUTPUT.PUT_LINE(‘没有符合条件记录!’);END

test_sql;使用EXECUTE

IMMEDIATE练习1、查询当前用户下的表信息2、创建

过程drop_table，根据输入的参数信息来删除当前用户下的表（输入的参数为要删除的表名）3、创建

过程proc_test，根据用户输入的表名、字段名、字段类型参数动态创建包含两个字段的表。4、执行该

过程,验证正确性4142用DBMS_JOB做时间安排DBMS_JOB能够做PL/SQL

程序的执行时间安排：提交jobs执行jobs改变jobs的执行参数删除jobs悬挂JobsDBMS_JOB子程序可用的子程序包括：SUBMIT--提交一个工作到工作队列REMOVE--从工作队列中删除一个指定的工作CHANGE--改变一个已经被提交到工作队列中的指定的工作WHAT--改变一个指定工作的说明NEXT_DATE--改变一个指定工作的下一次执行时间INTERVAL--改变一个指定工作在两次执行期间的时间间隔BROKEN--工作执行RUN--强制一个指定的工作运行4344提交工作DBMS_JOB.SUBMIT

过程添加一个新的工作到工作队列中可用的参数包括：–

JOBOUT工作的唯一标识–

WHATIN作为一个工作执行的PL/SQL

代码–

NEXT_DATEIN工作的下一个执行日期–

INTERVALIN计算一个工作下一个执行日期的日期函数–

NO_PARSE

IN时解析该工作布尔标志，用于指示是否在工作提交VARIABLE

jobno

NUMBERBEGINDBMS_JOB.SUBMIT

(job

=>

:jobno,what

=>

'raise_salary(1000);',next_date

=>

TRUNC(SYSDATE

+

1),interval

=>

'TRUNC(SYSDATE

+

1)');END;/

…PRINT

jobno45提交工作(续)用DBMS_JOB.SUBMIT在工作队列中放置一个要执行的工作46改变工作特征DBMS_JOB.CHANGE：改变WHAT,NEXT_DATE,和INTERVAL参数DBMS_JOB.INTERVAL：改变INTERVAL参数DBMS_JOB.NEXT_DATE：改变下一次执行日期DBMS_JOB.WHAT：改变WHAT参数DBMS_JOB.CHANGE(1,

NULL,

TRUNC(SYSDATE+1)+6/24,’SYSDATE+4/24');47运行、删除和中断工作DBMS_JOB.RUN：立即运行一个已提交的工作DBMS_JOB.REMOVE：从工作队列中删除一个已提交的工作DBMS_JOB.BROKEN：标记一个已提交的工作为中断，已中断的工作不再运行EXEC

DBMS_JOB.BROKEN(1,

TRUE)48查看已提交工作的信息用DBA_JOBS数据字典视图查看已提交的工作状态SELECT

job,log_user,next_date,next_sec,broken,whatFROM

dba_jobs;用DBA_JOBS_RUNNING数据字典视图显示当前运行的工作UTL_FILE包UTL_FILE为PL/SQL提供对文本文件I/O,允许PL/SQL在服务器上读和写文本文件通过设置初始化参数

UTL_FILE_DIR

来限制可

的

,以实现服务端的安全类似标准操作系统I/O–Open

文件–Get

文本–Put

文本–Close

文件–对UTL_FILE包使用异常指定49用UTL_FILE包处理文件50UTL_FILE过程和函数函数FOPEN函数IS_OPEN过程GET_LINE过程PUT、PUT_LINE、PUTF过程NEW_LINE过程FFLUSH过程FCLOSE,FCLOSE_ALL51对UTL_FILE包的异常52INVALID_PATHINVALID_MODEINVALID_FILEHANDLEINVALID_OPERATIONREAD_ERRORWRITE_ERRORINTERNAL_ERROR--文件位置或文件名无效--OPEN_MODE参数在FOPEN无效--文件句柄无效--文件可能没有打开就作为查询操作--在读操作期间一个操作系统错误发生--在写操作期间一个操作系统错误发生--在PL/SQL中一个未指定的错误发生FOPEN和IS_OPEN函数FUNCTION

FOPEN(location

IN

VARCHAR2,filename

IN

VARCHAR2,open_mode

IN

VARCHAR2)RETURN

UTL_FILE.FILE_TYPE;FUNCTION

IS_OPEN(file_handle

IN

FILE_TYPE)RETURN

BOOLEAN;5354使用UTL_FILECREATE

OR

REPLACE

PROCEDURE

sal_status(p_filedir

IN

VARCHAR2,

p_filename

IN

VARCHAR2)ISv_filehandle

UTL_FILE.FILE_TYPE;CURSOR

emp_info

ISSELECT

ename,

sal,

deptnoFROM

emp

ORDER

BY

deptno;v_newdeptnov_olddeptnoemp.deptno%TYPE;emp.deptno%TYPE

:=

0;BEGINv_filehandle

:=

UTL_FILE.FOPEN

(p_filedir,p_filename,'w');UTL_FILE.PUTF

(v_filehandle,'SALARY

REPORT:

GENERATED

ON

%s\n',

SYSDATE);UTL_FILE.NEW_LINE

(v_fileha