范例：定义包规范
|
CREATE OR REPLACE PACKAGE mldn_pkg AS FUNCTION get_emp_fun(p_dno dept.deptno%TYPE) RETURN SYS_REFCURSOR ; -- 返回弱游标类型 END ; / |
范例：定义包规范
|
CREATE OR REPLACE PACKAGE mldn_pkg AS 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 AS FUNCTION get_emp_fun(p_dno dept.deptno%TYPE) RETURN SYS_REFCURSOR AS cur_var SYS_REFCURSOR ; BEGIN OPEN cur_var FOR SELECT * FROM emp WHERE deptno=p_dno ; -- 打开参数游标 RETURN cur_var ; END ; END ; / |
范例：自定义游标变量
|
CREATE OR REPLACE PACKAGE BODY mldn_pkg AS FUNCTION get_emp_fun(p_dno dept.deptno%TYPE) RETURN cursor_ref AS cur_var cursor_ref ; BEGIN OPEN cur_var FOR SELECT * FROM emp WHERE deptno=p_dno ; -- 打开参数游标 RETURN cur_var ; END ; 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 AS 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 AS FUNCTION get_emp_fun(p_eno emp.empno%TYPE) RETURN emp%ROWTYPE AS v_empRow emp%ROWTYPE ; BEGIN SELECT * INTO v_empRow FROM emp WHERE empno=p_eno AND deptno=v_deptno ; RETURN v_empRow ; END ; 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 AS 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 AS PRAGMA SERIALLY_REUSABLE ; FUNCTION get_emp_fun(p_eno emp.empno%TYPE) RETURN emp%ROWTYPE AS v_empRow emp%ROWTYPE ; BEGIN SELECT * INTO v_empRow FROM emp WHERE empno=p_eno AND deptno=v_deptno ; RETURN v_empRow ; END ; END ; / |
范例：编写包规范，同时进行子程序重载
|
CREATE OR REPLACE PACKAGE emp_delete_pkg AS -- 删除雇员时所发生的异常 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 AS -- 根据雇员编号删除雇员信息，如果没有数据被删除则抛出异常 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 ; 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 ; / |