包

范例：定义包规范

CREATE OR REPLACE PACKAGE mldn_pkg

FUNCTION get_emp_fun(p_dno dept.deptno%TYPE) RETURN SYS_REFCURSOR ; -- 返回弱游标类型

END ;

范例：定义包规范

CREATE OR REPLACE PACKAGE mldn_pkg

TYPE cursor_ref IS REF CURSOR ;

FUNCTION get_emp_fun(p_dno dept.deptno%TYPE) RETURN cursor_ref ;

END ;

范例：定义包体实现get_emp_fun()函数

CREATE OR REPLACE PACKAGE BODY mldn_pkg

FUNCTION get_emp_fun(p_dno dept.deptno%TYPE) RETURN SYS_REFCURSOR

cur_var SYS_REFCURSOR ;

BEGIN

OPEN cur_var FOR SELECT * FROM emp WHERE deptno=p_dno ; -- 打开参数游标

RETURN cur_var ;

END ;

范例：自定义游标变量

CREATE OR REPLACE PACKAGE BODY mldn_pkg

FUNCTION get_emp_fun(p_dno dept.deptno%TYPE) RETURN cursor_ref

cur_var cursor_ref ;

BEGIN

OPEN cur_var FOR SELECT * FROM emp WHERE deptno=p_dno ; -- 打开参数游标

RETURN cur_var ;

END ;

范例：查询user_objects数据字典确认包规范及包体信息

SELECT object_type , object_name , status FROM user_objects

WHERE object_type IN ('PACKAGE','PACKAGE BODY') ;

范例：查询user_source数据字典，查看包规范

SELECT *

FROM user_source

WHERE type='PACKAGE' AND name='MLDN_PKG' ;

范例：定义PL/SQL程序块调用包中的函数

DECLARE

v_receive SYS_REFCURSOR ;

v_empRow emp%ROWTYPE ; -- 定义行类型

BEGIN

v_receive := mldn_pkg.get_emp_fun(10) ;

LOOP

FETCH v_receive INTO v_empRow ; -- 取得游标数据

EXIT WHEN v_receive%NOTFOUND ; -- 如果没有数据则退出

DBMS_OUTPUT.put_line('雇员姓名：' || v_empRow.ename || '，雇员职位：' || v_empRow.job) ;

END LOOP ;

END ;

范例：自定义游标变量

DECLARE

v_receive mldn_pkg.cursor_ref ;

v_empRow emp%ROWTYPE ; -- 定义行类型

BEGIN

v_receive := mldn_pkg.get_emp_fun(10) ;

LOOP

FETCH v_receive INTO v_empRow ; -- 取得游标数据

EXIT WHEN v_receive%NOTFOUND ; -- 如果没有数据则退出

DBMS_OUTPUT.put_line('雇员姓名：' || v_empRow.ename || '，雇员职位：' || v_empRow.job) ;

END LOOP ;

END ;

范例：删除mldn_pkg包

DROP PACKAGE mldn_pkg ;

范例：重新编译包规范

ALTER PACKAGE mldn_pkg COMPILE SPECIFICATION ;

范例：重新编译包体

ALTER PACKAGE mldn_pkg COMPILE BODY ;

范例：在包规范中定义全局变量

CREATE OR REPLACE PACKAGE mldn_pkg

v_deptnodept.deptno%TYPE := 10 ;

FUNCTION get_emp_fun(p_eno emp.empno%TYPE) RETURN emp%ROWTYPE ;

END ;

范例：定义包体实现

CREATE OR REPLACE PACKAGE BODY mldn_pkg

FUNCTION get_emp_fun(p_eno emp.empno%TYPE) RETURN emp%ROWTYPE

v_empRow emp%ROWTYPE ;

BEGIN

SELECT * INTO v_empRow FROM emp WHERE empno=p_eno AND deptno=v_deptno ;

RETURN v_empRow ;

END ;

范例：编写多个PL/SQL程序块，调用包中定义的程序结构

BEGIN

mldn_pkg.v_deptno := 20 ;

END ;

DECLARE

v_empResult emp%ROWTYPE ;

BEGIN

v_empResult := mldn_pkg.get_emp_fun(7369) ;

DBMS_OUTPUT.put_line('雇员姓名：' || v_empResult.ename || '，职位：' || v_empResult.job || '，部门编号：' || mldn_pkg.v_deptno) ;

END ;

范例：修改包规范定义

CREATE OR REPLACE PACKAGE mldn_pkg

PRAGMA SERIALLY_REUSABLE ;

v_deptno dept.deptno%TYPE ;

FUNCTION get_emp_fun(p_eno emp.empno%TYPE) RETURN emp%ROWTYPE ;

END ;

范例：修改包体定义

CREATE OR REPLACE PACKAGE BODY mldn_pkg

PRAGMA SERIALLY_REUSABLE ;

FUNCTION get_emp_fun(p_eno emp.empno%TYPE) RETURN emp%ROWTYPE

v_empRow emp%ROWTYPE ;

BEGIN

SELECT * INTO v_empRow FROM emp WHERE empno=p_eno AND deptno=v_deptno ;

RETURN v_empRow ;

END ;

范例：编写包规范，同时进行子程序重载

CREATE OR REPLACE PACKAGE emp_delete_pkg

-- 删除雇员时所发生的异常

emp_delete_exception EXCEPTION ;

-- 根据雇员编号删除雇员信息

PROCEDURE delete_emp_proc(p_empno emp.empno%TYPE) ;

-- 根据雇员姓名删除雇员信息

PROCEDURE delete_emp_proc(p_ename emp.ename%TYPE) ;

-- 根据雇员所在部门及职位删除雇员信息

PROCEDURE delete_emp_proc(p_deptno emp.deptno%TYPE , p_job emp.job%TYPE) ;

END ;

范例：定义包体实现具体的包规范

CREATE OR REPLACE PACKAGE BODY emp_delete_pkg

-- 根据雇员编号删除雇员信息，如果没有数据被删除则抛出异常

PROCEDURE delete_emp_proc(p_empno emp.empno%TYPE) AS

BEGIN

DELETE FROM emp WHERE empno=p_empno ;

IF SQL%NOTFOUND THEN

RAISE emp_delete_exception ;

END IF ;

END delete_emp_proc ;

-- 根据雇员姓名删除雇员信息，如果没有数据被删除则抛出异常

PROCEDURE delete_emp_proc(p_ename emp.ename%TYPE) AS

BEGIN

DELETE FROM emp WHERE ename=UPPER(p_ename) ;

IF SQL%NOTFOUND THEN

RAISE emp_delete_exception ;

END IF ;

END delete_emp_proc ;

-- 根据部门编号和雇员职位删除雇员信息，如果没有数据被删除则抛出异常

PROCEDURE delete_emp_proc(p_deptno emp.deptno%TYPE , p_job emp.job%TYPE) AS

BEGIN

DELETE FROM emp WHERE deptno=p_deptno AND job=p_job ;

IF SQL%NOTFOUND THEN

RAISE emp_delete_exception ;

END IF ;

END delete_emp_proc ;

END ;

范例：定义包规范

CREATE OR REPLACE PACKAGE init_pkg AS

-- 定义索引表类型，里面将保存多个dept行记录，使用数字作为索引类型

TYPE dept_index IS TABLE OF dept%ROWTYPE INDEX BY PLS_INTEGER ;

-- 定义要操作的游标

CURSOR dept_cur RETURN dept%ROWTYPE ;

-- 定义索引表变量

v_dept dept_index ;

-- 定义部门增加操作函数，如果增加成功返回true，否则返回false

FUNCTION dept_insert_fun(p_deptno dept.deptno%TYPE , p_dname dept.dname%TYPE , p_loc dept.loc%TYPE) RETURN BOOLEAN ;

END ;

范例：定义带查询语句的游标

CURSOR emp_cur(p_sal emp.sal%TYPE) RETURN emp%ROWTYPE IS

SELECT * FROM emp WHERE sal>p_sal ;

范例：定义包体

CREATE OR REPLACE PACKAGE BODY init_pkg AS

CURSOR dept_cur RETURN dept%ROWTYPE IS

SELECT * FROM dept ;

FUNCTION dept_insert_fun(p_deptno dept.deptno%TYPE , p_dname dept.dname%TYPE , p_loc dept.loc%TYPE) RETURN BOOLEAN AS

BEGIN

IF NOT v_dept.EXISTS(p_deptno) THEN -- 数据不存在

INSERT INTO dept(deptno,dname,loc) VALUES (p_deptno,p_dname,p_loc) ;

v_dept(p_deptno).deptno := p_deptno ;

v_dept(p_deptno).dname := p_dname ;

v_dept(p_deptno).loc := p_loc ;

RETURN true ;

ELSE

RETURN false ;

END IF ;

END dept_insert_fun ;

BEGIN

-- 包初始化操作：将游标中的数据保存到索引表之中，同时以部门编号作为索引表操作索引

FOR dept_row IN dept_cur LOOP

v_dept(dept_row.deptno) := dept_row ;

END LOOP ;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.put_line('程序出现错误。') ;

END ;

范例：编写PL/SQL程序块调用包

BEGIN

DBMS_OUTPUT.put_line('部门编号：' || init_pkg.v_dept(10).deptno ||

'，名称：' || init_pkg.v_dept(10).dname ||

'，位置：' || init_pkg.v_dept(10).loc) ;

IF init_pkg.dept_insert_fun(50,'MLDNJAVA','北京') THEN

DBMS_OUTPUT.put_line('新部门增加成功！') ;

DBMS_OUTPUT.put_line('新增部门编号：' || init_pkg.v_dept(50).deptno ||

'，名称：' || init_pkg.v_dept(50).dname ||

'，位置：' || init_pkg.v_dept(50).loc) ;

ELSE

DBMS_OUTPUT.put_line('部门信息已存在，增加失败！') ;

END IF ;

END ;

范例：定义包中函数的纯度级别

CREATE OR REPLACE PACKAGE purity_pkg AS

-- 定义包中的变量

v_name VARCHAR2(10) := 'mldn' ;

-- 根据雇员编号删除雇员信息。但此函数不能执行更新操作

FUNCTION emp_delete_fun_wnds(p_empno emp.empno%TYPE) RETURN NUMBER ;

-- 根据雇员编号查找雇员信息。但此函数不能执行SELECT操作

FUNCTION emp_find_fun_rnds(p_empno emp.empno%TYPE) RETURN NUMBER ;

-- 使用新的内容修改v_name变量内容。但此函数不能修改包中的变量

FUNCTION change_name_fun_wnps(p_param VARCHAR2) RETURN VARCHAR2 ;

-- 读取v_name属性内容。但此函数不能读取包中变量

FUNCTION get_name_fun_rnps(p_param NUMBER) RETURN VARCHAR2 ;

PRAGMA RESTRICT_REFERENCES(emp_delete_fun_wnds, WNDS) ; -- 设置函数纯度级别

PRAGMA RESTRICT_REFERENCES(emp_find_fun_rnds, RNDS) ; -- 设置函数纯度级别

PRAGMA RESTRICT_REFERENCES(change_name_fun_wnps, WNPS) ; -- 设置函数纯度级别

PRAGMA RESTRICT_REFERENCES(get_name_fun_rnps, RNPS) ; -- 设置函数纯度级别

END ;

范例：定义违反纯度级别的包体

CREATE OR REPLACE PACKAGE BODY purity_pkg AS

-- 根据雇员编号删除雇员信息。但此函数不能执行更新操作

FUNCTION emp_delete_fun_wnds(p_empno emp.empno%TYPE) RETURN NUMBER AS

BEGIN -- 此函数由于定义了wnds纯度，所以无法执行数据表更新操作

DELETE FROM emp WHERE empno=p_empno ;

RETURN 0 ; -- 满足函数要求返回数据

END ;

-- 根据雇员编号查找雇员信息。但此函数不能执行SELECT操作

FUNCTION emp_find_fun_rnds(p_empno emp.empno%TYPE) RETURN NUMBER AS

v_emp emp%ROWTYPE ;

BEGIN -- 此函数由于定义了rnds纯度，所以无法执行数据表查询操作

SELECT * INTO v_emp FROM emp WHERE empno=p_empno ;

RETURN 0 ; -- 满足函数要求返回数据

END ;

-- 使用新的内容修改v_name变量内容。但此函数不能修改包中的变量

FUNCTION change_name_fun_wnps(p_param VARCHAR2) RETURN VARCHAR2 AS

BEGIN -- 此函数由于定义了wnps纯度，所以函数无法修改包中的v_name变量

v_name := p_param ;

RETURN '' ; -- 满足函数要求返回数据

END ;

-- 读取v_name属性内容。但此函数不能读取包中变量

FUNCTION get_name_fun_rnps(p_param NUMBER) RETURN VARCHAR2 AS

BEGIN -- 此函数由于定义了rnps，所以函数无法读取v_name变量

RETURN v_name ;

END ;

范例：定义包公用函数

CREATE OR REPLACE PACKAGE purity2_pkg AS

-- 定义取得雇员上缴个人所得税的函数

FUNCTION tax_fun(p_sal emp.sal%TYPE) RETURN NUMBER ;

-- 定义函数纯度：不能修改数据表、不能修改或读取包变量

PRAGMA RESTRICT_REFERENCES(tax_fun,WNDS,WNPS,RNPS) ;

END ;

范例：查询all_source数据字典

SELECT *

FROM all_source

WHERE type='PACKAGE' AND name='DBMS_OUTPUT' ;

范例：设置输出打开（enable）和关闭（disable）

BEGIN

DBMS_OUTPUT.enable ;-- 启用缓冲

DBMS_OUTPUT.put_line('此信息可以正常输出。') ;

END ;

BEGIN

DBMS_OUTPUT.disable ;-- 禁用缓冲

DBMS_OUTPUT.put_line('此信息输出无法显示。') ;

END ;

范例：设置缓冲区数据

BEGIN

DBMS_OUTPUT.enable ; -- 开启缓冲区

DBMS_OUTPUT.put('www.') ; -- 向缓冲增加内容

DBMS_OUTPUT.put('mldn.cn') ; -- 向缓冲增加内容

DBMS_OUTPUT.new_line ; -- 换行，输出之前缓冲区内容

DBMS_OUTPUT.put('www.mldnjava.cn') ; -- 向缓冲增加内容

DBMS_OUTPUT.new_line ; -- 换行，输出之前缓冲区内容

DBMS_OUTPUT.put('bbs.mldn.cn') ;-- 向缓冲增加内容，之后没有换行，此内容不输出

END ;

范例：使用get_line()和get_lines()取回缓冲数据

DECLARE

v_line1 VARCHAR2(200) ; -- 保存第1行数据

v_line2 VARCHAR2(200) ; -- 保存第2行数据

v_line3 VARCHAR2(200) ; -- 保存第3行数据

v_status NUMBER ; -- 保存状态

BEGIN

DBMS_OUTPUT.enable ; -- 开启缓冲区

DBMS_OUTPUT.put('www.mldn.cn') ; -- 向缓冲增加内容

DBMS_OUTPUT.new_line ; -- 换行

DBMS_OUTPUT.put('www.mldnjava.cn') ; -- 向缓冲增加内容

DBMS_OUTPUT.new_line ; -- 换行

DBMS_OUTPUT.put('bbs.mldn.cn') ; -- 向缓冲增加内容

DBMS_OUTPUT.new_line ; -- 换行

DBMS_OUTPUT.get_line(v_line1 , v_status) ; -- 读取缓冲区一行数据

DBMS_OUTPUT.get_line(v_line2 , v_status) ; -- 读取缓冲区一行数据

DBMS_OUTPUT.get_line(v_line3 , v_status) ; -- 读取缓冲区一行数据

DBMS_OUTPUT.put_line('取得数据：' || v_line1) ;

DBMS_OUTPUT.put_line('取得数据：' || v_line2) ;

DBMS_OUTPUT.put_line('取得数据：' || v_line3) ;

END ;

范例：利用get_lines()取得缓冲区中的数据

DECLARE

v_lines DBMS_OUTPUT.CHARARR ; -- 定义CHARRARR变量

v_status NUMBER := 3 ; -- 保存3个状态

BEGIN

DBMS_OUTPUT.enable ; -- 开启缓冲区

DBMS_OUTPUT.put('www.mldn.cn') ; -- 向缓冲增加内容

DBMS_OUTPUT.new_line ;

DBMS_OUTPUT.put('www.mldnjava.cn') ; -- 向缓冲增加内容

DBMS_OUTPUT.new_line ;

DBMS_OUTPUT.put('bbs.mldn.cn') ; -- 向缓冲增加内容

DBMS_OUTPUT.new_line ;

DBMS_OUTPUT.get_lines(v_lines , v_status) ; -- 读取缓冲区的3个数据

FOR x IN 1.. v_lines.COUNT LOOP

DBMS_OUTPUT.put_line('缓冲区' || x || '数据：' || v_lines(x)) ;

END LOOP ;

END ;

范例：定义创建脚本

DROP SEQUENCE job_seq ;

DROP TABLE job_data PURGE ;

CREATE SEQUENCE job_seq ;

CREATE TABLE job_data (

jid NUMBER ,

title VARCHAR2(20) ,

job_date DATE ,

CONSTRAINT pk_jid PRIMARY KEY(jid)

) ;

范例：定义过程，实现数据增加

CREATE OR REPLACE PROCEDURE insert_demo_proc(p_title job_data.title%TYPE) AS

BEGIN

INSERT INTO job_data(jid,title,job_date) VALUES (job_seq.nextval ,p_title, SYSDATE) ;

END ;

范例：定义作业，每秒执行一次，执行时调用insert_demo_proc过程

DECLARE

v_jobno NUMBER ;

BEGIN

DBMS_JOB.submit(v_jobno , -- 通过OUT模式返回创建的作业编号

'insert_demo_proc(''作业A'') ;' , -- 作业执行时需要调用的过程

SYSDATE , -- 作业开始时间

'SYSDATE + (1/(24*60*60))') ; -- 作业操作间隔

DBMS_OUTPUT.put_line('作业编号：' || v_jobno) ;

COMMIT ; -- 必须执行此操作

END ;

范例：修改JOB_QUEUE_PROCESSES参数

ALTER SYSTEM SET JOB_QUEUE_PROCESSES =10 ;

范例：查看user_jobs数据字典

select job,next_date,broken,interval,what from user_jobs ;

范例：查询job_data数据表内容

SELECT jid,title,TO_CHAR(job_date,'yyyy-mm-dd hh24:mi:ss')

FROM c##scott.job_data

ORDER BY jid ASC ;

范例：修改作业的运行间隔，每小时运行一次

EXECUTE DBMS_JOB.interval(1 , 'SYSDATE + (1/(24*60))') ;

范例：删除作业

EXECUTE DBMS_JOB.remove(1) ;

范例：为字符串前后添加单引号

SELECT DBMS_ASSERT.enquote_literal('www.mldnjava.cn') FROM dual ;

范例：为前后增加双引号

SELECT DBMS_ASSERT.enquote_name('www.mldnjava.cn') FROM dual ;

范例：验证字符串是否为有效模式对象名

SELECT DBMS_ASSERT.qualified_sql_name('mldn_oracle') FROM dual ;

范例：输入错误的模式对象名

SELECT DBMS_ASSERT.qualified_sql_name('123') FROM dual ;

范例：验证字符串是否为有效模式名

SELECT DBMS_ASSERT.schema_name('C##SCOTT') FROM dual ;

范例：错误的模式名称

SELECT DBMS_ASSERT.schema_name('LXH') FROM dual ;

范例：使用sys用户登陆，并创建目录

CONN sys/change_on_install AS SYSDBA ;

CREATE OR REPLACE DIRECTORY mldn_files AS 'd:mldn_dir' ;

范例：为c##scott用户授权

GRANT READ ON DIRECTORY mldn_files TO c##scott ;

GRANT WRITE ON DIRECTORY mldn_files TO c##scott ;

范例：创建数据表，包含BLOB及CLOB类型字段

DROP TABLE teacher PURGE ;

DROP SEQUENCE teacher_seq ;

CREATE SEQUENCE teacher_seq ;

CREATE TABLE teacher(

tid NUMBER ,

name VARCHAR2(50) NOT NULL ,

note CLOB ,

photo BLOB ,

CONSTRAINT pk_tid PRIMARY KEY (tid)

) ;

范例：编写一个PL/SQL块，操作BLOB

DECLARE

v_photo teacher.photo%TYPE ; -- BLOB数据类型

v_pos_write INTEGER ; -- 保存的数据长度

v_srcfile BFILE ; -- 通过BFILE设置文件

BEGIN

-- 增加一条新的数据，但是对于BLOB使用empty_blob()设置为空数据

INSERT INTO teacher(tid,name,note,photo)

VALUES (teacher_seq.nextval,'李兴华','是一位老师', empty_blob())

RETURN photo INTO v_photo ; -- 将photo的类型返回给v_photo变量

-- 定义BFILE，找到指定文件

v_srcfile := BFILENAME('MLDN_FILES','MLDN.JPG') ;

-- 取得要保存文件的长度

v_pos_write := DBMS_LOB.getlength(v_srcfile) ;

-- 以只读方式打开要操作的文件

DBMS_LOB.fileopen(v_srcfile , DBMS_LOB.file_readonly) ;

-- 实现文件数据的保存

DBMS_LOB.loadfromfile(v_photo,v_srcfile,v_pos_write) ;

-- 关闭文件

DBMS_LOB.fileclose(v_srcfile) ;

END ;