1.PL/SQL块
PL/SQL块的结构如下:
DECLARE
--声明部分: 在此声明PL/SQL用到的变量,类型及游标,以及局部的存储过程和函数
BEGIN
-- 执行部分: 过程及SQL 语句 , 即程序的主要部分
EXCEPTION
-- 执行异常部分: 错误处理
END;
2.流程控制语句
2.1 条件语句IF
IF <布尔表达式> THEN
PL/SQL 和 SQL语句
END IF;
###############################
IF <布尔表达式> THEN
PL/SQL 和 SQL语句
ELSE
其它语句
END IF;
###############################
IF <布尔表达式> THEN
PL/SQL 和 SQL语句
ELSIF < 其它布尔表达式> THEN
其它语句
ELSIF < 其它布尔表达式> THEN
其它语句
ELSE
其它语句
END IF;
2.2 CASE表达式
CASE 条件表达式
WHEN 条件表达式结果1 THEN
语句段1
WHEN 条件表达式结果2 THEN
语句段2
......
WHEN 条件表达式结果n THEN
语句段n
[ELSE 条件表达式结果]
END;
##############################
CASE
WHEN 条件表达式1 THEN
语句段1
WHEN 条件表达式2 THEN
语句段2
......
WHEN 条件表达式n THEN
语句段n
[ELSE 语句段]
END;
2.3 循环语句
----1> 简单循环
LOOP
要执行的语句;
EXIT WHEN <条件语句> --条件满足,退出循环语句
END LOOP;
----2> while循环
WHILE <布尔表达式> LOOP
要执行的语句;
END LOOP;
----3> for循环
[<<循环标签>>]
FOR 循环计数器 IN [ REVERSE ] 下限 .. 上限 LOOP
要执行的语句;
END LOOP [循环标签];
注意:
REVERSE 表示倒序; 另外在循环中可以用exit退出循环
----4> 标号和GOTO
GOTO label;
......
<<label>> /*标号是用<< >>括起来的标识符 */
----goto语句 表示无条件跳转到指定的标号
注意:在以下地方使用是不合法的,编译时会出错误。
(1) 跳转到非执行语句前面。
(2) 跳转到子块中。
(3) 跳转到循环语句中。
(4) 跳转到条件语句中。
(5) 从异常处理部分跳转到执行。
(6) 从条件语句的一部分跳转到另一部分。
3.游标的使用
在 PL/SQL 程序中,对于处理多行记录的事务经常使用游标来实现。
在每个用户会话中,可以同时打开多个游标,其数量由数据库初始化参数文件中的OPEN_CURSORS参数定义。
对于不同的SQL语句,游标的使用情况不同:
SQL语句 | 游标 |
非查询语句 | 隐式的 |
结果是单行的查询语句 | 隐式的或显示的 |
结果是多行的查询语句 | 显示的 |
3.1 显式游标
定义/声明游标 -----> 打开游标 ----> 提取游标数据 ----> 关闭游标
cursor cursor_name is select .....;
------>
open cursor_name;
--------->
fetch cursor_name into v_.. ,v_.. ;
-------------->
close cursor_name;
执行FETCH语句时,每次返回一个数据行,然后自动将游标移动指向下一个数据行。当检索到最后一行数据时,如果再次执行FETCH语句,将操作失败,并将游标属性%NOTFOUND置为TRUE。所以每次执行完FETCH语句后,检查游标属性%NOTFOUND就可以判断FETCH语句是否执行成功并返回一个数据行,以便确定是否给对应的变量赋了值。
如:
OPEN c_cursor;
FETCH c_cursor INTO v_ename, v_sal;
WHILE c_cursor%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(v_ename||'---'||to_char(v_sal) );
FETCH c_cursor INTO v_ename, v_sal;
END LOOP;
CLOSE c_cursor;
3.2 游标属性
Cursor_name%FOUND 布尔型属性,当最近一次提取游标操作FETCH成功则为 TRUE,否则为FALSE;
Cursor_name%NOTFOUND 布尔型属性,与%FOUND相反;
Cursor_name%ISOPEN 布尔型属性,当游标已打开时返回 TRUE;
Cursor_name%ROWCOUNT 数字型属性,返回已从游标中读取的记录数。
3.3 游标的FOR循环
PL/SQL语言提供了游标FOR循环语句,自动执行游标的OPEN、FETCH、CLOSE语句和循环语句的功能;当进入循环时,游标FOR循环语句自动打开游标,并提取第一行游标数据,当程序处理完当前所提取的数据而进入下一次循环时,游标FOR循环语句自动提取下一行数据供程序处理,当提取完结果集合中的所有数据行后结束循环,并自动关闭游标。
格式:
FOR index_variable IN cursor_name[(value[, value]…)] LOOP
-- 游标数据处理代码
END LOOP;
示列:
DECLARE
CURSOR c_sal IS SELECT employee_id, first_name || last_name ename, salary
FROM employees ;
BEGIN
--隐含打开游标
FOR v_sal IN c_sal LOOP
--隐含执行一个FETCH语句
DBMS_OUTPUT.PUT_LINE(to_char(v_sal.employee_id)||'---'|| v_sal.ename||'---'||to_char(v_sal.salary)) ;
--隐含监测c_sal%NOTFOUND
END LOOP;
--隐含关闭游标
END;
3.4 隐式游标
显式游标主要是用于对查询语句的处理,尤其是在查询结果为多条记录的情况下;而对于非查询语句,如修改、删除操作,则由ORACLE 系统自动地为这些操作设置游标并创建其工作区,这些由系统隐含创建的游标称为隐式游标,隐式游标的名字为SQL,这是由ORACLE 系统定义的。对于隐式游标的操作,如定义、打开、取值及关闭操作,都由ORACLE 系统自动地完成,无需用户进行处理。用户只能通过隐式游标的相关属性,来完成相应的操作。在隐式游标的工作区中,所存放的数据是与用户自定义的显示游标无关的、最新处理的一条SQL 语句所包含的数据。
格式调用为: SQL%
注:INSERT, UPDATE, DELETE, SELECT 语句中不必明确定义游标。
隐式游标属性
属性 | 值 | SELECT | INSERT | UPDATE | DELETE |
SQL%ISOPEN |
| FALSE | FALSE | FALSE | FALSE |
SQL%FOUND | TRUE | 有结果 |
| 成功 | 成功 |
SQL%FOUND | FALSE | 没结果 |
| 失败 | 失败 |
SQL%NOTFUOND | TRUE | 没结果 |
| 失败 | 失败 |
SQL%NOTFOUND | FALSE | 有结果 |
| 成功 | 失败 |
SQL%ROWCOUNT |
| 返回行数,只为1 | 插入的行数 | 修改的行数 | 删除的行数 |
----通过隐式游标SQL的%ROWCOUNT属性来了解修改了多少行。
DECLARE
v_rows NUMBER;
BEGIN
--更新数据
UPDATE employees SET salary = 30000
WHERE department_id = 90 AND job_id = 'AD_VP';
--获取默认游标的属性值
v_rows := SQL%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE('更新了'||v_rows||'个雇员的工资');
--回退更新,以便使数据库的数据保持原样
ROLLBACK;
END;
3.5 关于no_data_found和%notfound的区别
SELECT … INTO 语句触发 NO_DATA_FOUND;
当一个显式游标的WHERE子句未找到时触发%NOTFOUND;
当UPDATE或DELETE 语句的WHERE 子句未找到时触发 SQL%NOTFOUND;
在提取循环中要用%NOTFOUND或%FOUND来确定循环的退出条件,不要用 NO_DATA_FOUND
4.函数与存储过程
4.1 函数的调用
----首先需要确定该函数有几个传入参数:
----法一:位置表示法调用函数,传入参数必须和函数定义传入参数相对应
begin
dbms_output.put_line(F_TEST( , , ));
end;
----法二:命名表示法调用函数
begin
dbms_output.put_line(F_test( a=> ,b=> ,c=> ));
end;
----法三:混合使用位置表示法和命名表示法调用函数
begin
dbms_output.put_line(F_test( ,b=> ,c=> ));
end;
----法四:排除表示法
begin
dbms_output.put_line(F_test( ,c=> ));
end;
----sql调用表示法之混合表示法
select f_test( ,b=> ,c=> ) from dual;
4.2 存储过程的调用
execute p_name;
begin
p_name;
end;
4.3 过程与函数的比较
----使用过程与函数具有如下优点:
1> 共同使用的代码可以只需要被编写和测试一次,而被需要该代码的任何应用程序(如:.NET、C++、JAVA、VB程序,也可以是DLL库)调用。
2> 这种集中编写、集中维护更新、大家共享(或重用)的方法,简化了应用程序的开发和维护,提高了效率与性能。
3> 这种模块化的方法,使得可以将一个复杂的问题、大的程序逐步简化成几个简单的、小的程序部分,进行分别编写、调试。因此使程序的结构清晰、简单,也容易实现。
4> 可以在各个开发者之间提供处理数据、控制流程、提示信息等方面的一致性。
5> 节省内存空间。它们以一种压缩的形式被存储在外存中,当被调用时才被放入内存进行处理。并且,如果多个用户要执行相同的过程或函数时,就只需要在内存中加载一个该过程或函数。
6> 提高数据的安全性与完整性。通过把一些对数据的操作放到过程或函数中,就可以通过是否授予用户有执行该过程或的权限,来限制某些用户对数据进行这些操作。
----过程与函数的相同功能有:
1> 都使用IN模式的参数传入数据、OUT模式的参数返回数据。
2> 输入参数都可以接受默认值,都可以传值或传引导。
3> 调用时的实际参数都可以使用位置表示法、名称表示法或组合方法。
4> 都有声明部分、执行部分和异常处理部分。
5> 其管理过程都有创建、编译、授权、删除、显示依赖关系等。
----使用过程与函数的原则:
1> 如果需要返回多个值和不返回值,就使用过程;如果只需要返回一个值,就使用函数。
2> 过程一般用于执行一个指定的动作,函数一般用于计算和返回一个值。
3> 可以通过SQL语句内部(如表达式)调用函数来完成复杂的计算问题,但不能调用过程。所以这是函数的特色。
5.程序包
5.1 程序包的优点
1> 简化应用程序设计:程序包的说明部分和包体部分可以分别创建各编译。主要体现 在以下三个方面:
(1) 可以在设计一个应用程序时,只创建各编译程序包的说明部分,然后再编写引用该 程序包的PL/SQL块。
(2) 当完成整个应用程序的整体框架后,再回头来定义包体部分。只要不改变包的说明部分,就可以单独调试、增加或替换包体的内容,这不会影响其他的应用程序。
(3) 更新包的说明后必须重新编译引用包的应用程序,但更新包体,则不需重新编译引用包的应用程序,以快速进行进行应用程序的原形开发。
2> 模块化:可将逻辑相关的PL/SQL块或元素等组织在一起,用名称来唯一标识程序包。把一个大的功能模块划分人适当个数小的功能模块,分别完成各自的功能。这样组织的程序包都易于编写,易于理解更易于管理。
3> 信息隐藏:因为包中的元素可以分为公有元素和私有元素。公有元素可被程序包内的过程、函数等的访问,还可以被包外的PL/SQL访问。但对于私有元素只能被包内的过程、函数等访问。对于用户,只需知道包的说明,不用了解包休的具体细节。
4> 效率高:程序包在应用程序第一次调用程序包中的某个元素时,ORACLE将把整个程序包加载到内存中,当第二次访问程序包中的元素时,ORACLE将直接从内在中读取,而不需要进行磁盘I/O操作而影响速度,同时位于内在中的程序包可被同一会话期间的其它应用程序共享。因此,程序包增加了重用性并改善了多用户、多应用程序环境的效率。
一个包由两个分开的部分组成:
包说明(PACKAGE):包说明部分声明包内数据类型、变量、常量、游标、子程序和异常错误处理等元素,这些元素为包的公有元素。
包主体(PACKAGE BODY):包主体则是包定义部分的具体实现,它定义了包定义部分所声明的游标和子程序,在包主体中还可以声明包的私有元素。
包说明和包主体分开编译,并作为两部分分开的对象存放在数据库字典中,可查看数据字典user_source, all_source, dba_source,分别了解包说明与包主体的详细信息。
5.2 程序包的定义
程序包的定义分为程序包说明定义和程序包主体定义两部分组成。
程序包说明用于声明包的公用组件,如变量、常量、自定义数据类型、异常、过程、函数、游标等。包说明中定义的公有组件不仅可以在包内使用,还可以由包外其他过程、函数。但需要说明与注意的是,我们为了实现信息的隐藏,建议不要将所有组件都放在包说明处声明,只应把公共组件放在包声明部分。包的名称是唯一的,但对于两个包中的公有组件的名称可以相同,这种用“包名.公有组件名”加以区分。
包体是包的具体实现细节,其实现在包说明中声明的所有公有过程、函数、游标等。当然也可以在包体中声明仅属于自己的私有过程、函数、游标等。创建包体时,有以下几点需要注意:
1> 包体只能在包说明被创建或编译后才能进行创建或编译。
2> 在包体中实现的过程、函数、游标的名称必须与包说明中的过程、函数、游标一致,包括名称、参数的名称以及参数的模式(IN、OUT、IN OUT)。并建设按包说明中的次序定义包体中具体的实现。
3> 在包体中声明的数据类型、变量、常量都是私有的,只能在包体中使用而不能被印刷体外的应用程序访问与使用。
4> 在包体执行部分,可对包说明,包体中声明的公有或私有变量进行初始化或其它设置。
创建程序包说明语法格式:
CREATE [OR REPLACE] PACKAGE package_name
[AUTHID {CURRENT_USER | DEFINER}]
{IS | AS}
[公有数据类型定义[公有数据类型定义]…]
[公有游标声明[公有游标声明]…]
[公有变量、常量声明[公有变量、常量声明]…]
[公有函数声明[公有函数声明]…]
[公有过程声明[公有过程声明]…]
END [package_name];
其中:AUTHID CURRENT_USER和AUTHID DEFINER选项说明应用程序在调用函数时所使用的权限模式,它们与CREATE FUNCTION语句中invoker_right_clause子句的作用相同。
创建程序包主体语法格式:
CREATE [OR REPLACE] PACKAGE BODY package_name
{IS | AS}
[私有数据类型定义[私有数据类型定义]…]
[私有变量、常量声明[私有变量、常量声明]…]
[私有异常错误声明[私有异常错误声明]…]
[私有函数声明和定义[私有函数声明和定义]…]
[私有函过程声明和定义[私有函过程声明和定义]…]
[公有游标定义[公有游标定义]…]
[公有函数定义[公有函数定义]…]
[公有过程定义[公有过程定义]…]
BEGIN
执行部分(初始化部分)
END package_name;
其中:在包主体定义公有程序时,它们必须与包定义中所声明子程序的格式完全一致。
5.3 包的开发步骤
与开发存储过程类似,包的开发需要几个步骤:
1> 将每个存储过程调式正确;
2> 用文本编辑软件将各个存储过程和函数集成在一起;
3> 按照包的定义要求将集成的文本的前面加上包定义;
4> 按照包的定义要求将集成的文本的前面加上包主体;
5> 使用SQLPLUS或开发工具进行调式。
5.4 包定义的说明
例1:创建的包为DEMO_PKG,该包中包含一个记录变量DEPTREC、两个函数和一个过程。实现对dept表的增加、删除与查询。
CREATE OR REPLACE PACKAGE DEMO_PKG
IS
DEPTREC DEPT%ROWTYPE;
--Add dept...
FUNCTION add_dept(
dept_no NUMBER,
dept_name VARCHAR2,
location VARCHAR2)
RETURN NUMBER;
--delete dept...
FUNCTION delete_dept(dept_no NUMBER)
RETURN NUMBER;
--query dept...
PROCEDURE query_dept(dept_no IN NUMBER);
END DEMO_PKG;
包主体的创建方法,它实现上面所声明的包定义,并在包主体中声明一个私有变量flag和一个私有函数check_dept,由于在add_dept和remove_dept等函数中需要调用check_dpet函数,所以,在定义check_dept 函数之前首先对该函数进行声明,这种声明方法称作前向声明。
CREATE OR REPLACE PACKAGE BODY DEMO_PKG
IS
FUNCTION add_dept
(
dept_no NUMBER,
dept_name VARCHAR2,
location VARCHAR2
)
RETURN NUMBER
IS
empno_remaining EXCEPTION; --自定义异常
PRAGMA EXCEPTION_INIT(empno_remaining, -1);
/* -1 是违反唯一约束条件的错误代码 */
BEGIN
INSERT INTO dept VALUES(dept_no, dept_name, location);
IF SQL%FOUND THEN
RETURN 1;
END IF;
EXCEPTION
WHEN empno_remaining THEN
RETURN 0;
WHEN OTHERS THEN
RETURN -1;
END add_dept;
FUNCTION delete_dept(dept_no NUMBER)
RETURN NUMBER
IS
BEGIN
DELETE FROM dept WHERE deptno = dept_no;
IF SQL%FOUND THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
EXCEPTION
WHEN OTHERS THEN
RETURN -1;
END delete_dept;
PROCEDURE query_dept
(dept_no IN NUMBER)
IS
BEGIN
SELECT * INTO DeptRec FROM dept WHERE deptno=dept_no;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('温馨提示:数据库中没有编码为'||dept_no||'的部门');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('程序运行错误,请使用游标进行操作!');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||'----'||SQLERRM);
END query_dept;
BEGIN
Null;
END DEMO_PKG;
对包内共有元素的调用格式为:包名.元素名称
调用DEMO_PKG包内函数对dept表进行插入、查询和删除操作,并通过DEMO_PKG包中的记录变量DEPTREC显示所查询到的数据库信息:
DECLARE
Var NUMBER;
BEGIN
Var := DEMO_PKG.add_dept(90,'HKLORB', 'HAIKOU');
IF var =-1 THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||'----'||SQLERRM);
ELSIF var =0 THEN
DBMS_OUTPUT.PUT_LINE('温馨提示:该部门记录已经存在!');
ELSE
DBMS_OUTPUT.PUT_LINE('温馨提示:添加记录成功!');
DEMO_PKG.query_dept(90);
DBMS_OUTPUT.PUT_LINE(DEMO_PKG.DeptRec.deptno||'---'||
DEMO_PKG.DeptRec.dname||'---'||DEMO_PKG.DeptRec.loc);
var := DEMO_PKG.delete_dept(90);
IF var =-1 THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||'----'||SQLERRM);
ELSIF var=0 THEN
DBMS_OUTPUT.PUT_LINE('温馨提示:该部门记录不存在!');
ELSE
DBMS_OUTPUT.PUT_LINE('温馨提示:删除记录成功!');
END IF;
END IF;
END;
例2: 创建包EMP_PKG,读取emp表中的数据
--创建包说明
CREATE OR REPLACE PACKAGE EMP_PKG
IS
TYPE emp_table_type IS TABLE OF emp%ROWTYPE
INDEX BY BINARY_INTEGER;
PROCEDURE read_emp_table (p_emp_table OUT emp_table_type);
END EMP_PKG;
--创建包体
CREATE OR REPLACE PACKAGE BODY EMP_PKG
IS
PROCEDURE read_emp_table (p_emp_table OUT emp_table_type)
IS
I BINARY_INTEGER := 0;
BEGIN
FOR emp_record IN ( SELECT * FROM emp ) LOOP
P_emp_table(i) := emp_record;
I := I + 1;
END LOOP;
END read_emp_table;
END EMP_PKG;
--执行
DECLARE
E_table EMP_PKG.emp_table_type;
BEGIN
EMP_PKG.read_emp_table(e_table);
FOR I IN e_table.FIRST ..e_table.LAST LOOP
DBMS_OUTPUT.PUT_LINE(e_table(i).empno||' '||e_table(i).ename);
END LOOP;
END;
例3: 创建包MANAGE_EMP_PKG,对员工进行管理(新增员工、新增部门、删除指定员工、删除指定部门、增加指定员工的工资与奖金):
--创建序列从100开始,依次增加1
CREATE SEQUENCE empseq
START WITH 100
INCREMENT BY 1
ORDER NOCYCLE;
--创建序列从100开始,依次增加10
CREATE SEQUENCE deptseq
START WITH 100
INCREMENT BY 10
ORDER NOCYCLE;
-- *******************************************
-- 创建包说明
-- 包 名:MANAGE_EMP_PKG
-- 功能描述:对员工进行管理(新增员工,新增部门
-- ,删除员工,删除部门,增加工资与奖金等)
-- 创建人员:胡勇
-- 创建日期:2010-05-19
-- Q Q: 80368704
-- E-mail : 80368704@yahoo.com.cn
-- WebSite: http://www.cnblogs.com/huyong
-- ******************************************
CREATE OR REPLACE PACKAGE MANAGE_EMP_PKG
AS
--增加一名员工
FUNCTION hire_emp
(ename VARCHAR2, job VARCHAR2
, mgr NUMBER, sal NUMBER
, comm NUMBER, deptno NUMBER)
RETURN NUMBER;
--新增一个部门
FUNCTION add_dept(dname VARCHAR2, loc VARCHAR2)
RETURN NUMBER;
--删除指定员工
PROCEDURE remove_emp(empno NUMBER);
--删除指定部门
PROCEDURE remove_dept(deptno NUMBER);
--增加指定员工的工资
PROCEDURE increase_sal(empno NUMBER, sal_incr NUMBER);
--增加指定员工的奖金
PROCEDURE increase_comm(empno NUMBER, comm_incr NUMBER);
END MANAGE_EMP_PKG;--创建包说明结束
-- *******************************************
-- 创建包体
-- 包 名:MANAGE_EMP_PKG
-- 功能描述:对员工进行管理(新增员工,新增部门
-- ,删除员工,删除部门,增加工资与奖金等)
-- 创建人员:胡勇
-- 创建日期:2010-05-19
-- Q Q: 80368704
-- E-mail : 80368704@yahoo.com.cn
-- WebSite: http://www.cnblogs.com/huyong
-- ******************************************
CREATE OR REPLACE PACKAGE BODY MANAGE_EMP_PKG
AS
total_emps NUMBER; --员工数
total_depts NUMBER; --部门数
no_sal EXCEPTION;
no_comm EXCEPTION;
--增加一名员工
FUNCTION hire_emp(ename VARCHAR2, job VARCHAR2, mgr NUMBER,
sal NUMBER, comm NUMBER, deptno NUMBER)
RETURN NUMBER --返回新增加的员工编号
IS
new_empno NUMBER(4);
BEGIN
SELECT empseq.NEXTVAL INTO new_empno FROM dual;
SELECT COUNT(*) INTO total_emps FROM emp;--当前记录总数
INSERT INTO emp
VALUES (new_empno, ename, job, mgr, sysdate, sal, comm, deptno);
total_emps:=total_emps+1;
RETURN(new_empno);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('温馨提示:发生系统错误!');
END hire_emp;
--新增一个部门
FUNCTION add_dept(dname VARCHAR2, loc VARCHAR2)
RETURN NUMBER
IS
new_deptno NUMBER(4); --部门编号
BEGIN
--得到一个新的自增的员工编号
SELECT deptseq.NEXTVAL INTO new_deptno FROM dual;
SELECT COUNT(*) INTO total_depts FROM dept;--当前部门总数
INSERT INTO dept VALUES (new_deptno, dname, loc);
total_depts:=total_depts;
RETURN(new_deptno);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('温馨提示:发生系统错误!');
END add_dept;
--删除指定员工
PROCEDURE remove_emp(empno NUMBER)
IS
no_result EXCEPTION; --自定义异常
BEGIN
DELETE FROM emp WHERE emp.empno=remove_emp.empno;
IF SQL%NOTFOUND THEN
RAISE no_result;
END IF;
total_emps:=total_emps - 1; --总的员工数减1
EXCEPTION
WHEN no_result THEN
DBMS_OUTPUT.PUT_LINE('温馨提示:你需要的数据不存在!');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('温馨提示:发生系统错误!');
END remove_emp;
--删除指定部门
PROCEDURE remove_dept(deptno NUMBER)
IS
no_result EXCEPTION; --自定义异常
exception_deptno_remaining EXCEPTION; --自定义异常
/*-2292 是违反一致性约束的错误代码*/
PRAGMA EXCEPTION_INIT(exception_deptno_remaining, -2292);
BEGIN
DELETE FROM dept WHERE dept.deptno=remove_dept.deptno;
IF SQL%NOTFOUND THEN
RAISE no_result;
END IF;
total_depts:=total_depts-1; --总的部门数减1
EXCEPTION
WHEN no_result THEN
DBMS_OUTPUT.PUT_LINE('温馨提示:你需要的数据不存在!');
WHEN exception_deptno_remaining THEN
DBMS_OUTPUT.PUT_LINE('温馨提示:违反数据完整性约束!');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('温馨提示:发生系统错误!');
END remove_dept;
--给指定员工增加指定数量的工资
PROCEDURE increase_sal(empno NUMBER, sal_incr NUMBER)
IS
curr_sal NUMBER(7, 2); --当前工资
BEGIN
--得到当前工资
SELECT sal INTO curr_sal FROM emp WHERE emp.empno=increase_sal.empno;
IF curr_sal IS NULL THEN
RAISE no_sal;
ELSE
UPDATE emp SET sal = sal + increase_sal.sal_incr --当前工资加新增的工资
WHERE emp.empno = increase_sal.empno;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('温馨提示:你需要的数据不存在!');
WHEN no_sal THEN
DBMS_OUTPUT.PUT_LINE('温馨提示:此员工的工资不存在!');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('温馨提示:发生系统错误!');
END increase_sal;
--给指定员工增加指定数量的奖金
PROCEDURE increase_comm(empno NUMBER, comm_incr NUMBER)
IS
curr_comm NUMBER(7,2);
BEGIN
--得到指定员工的当前资金
SELECT comm INTO curr_comm
FROM emp WHERE emp.empno = increase_comm.empno;
IF curr_comm IS NULL THEN
RAISE no_comm;
ELSE
UPDATE emp SET comm = comm + increase_comm.comm_incr
WHERE emp.empno=increase_comm.empno;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('温馨提示:你需要的数据不存在!');
WHEN no_comm THEN
DBMS_OUTPUT.PUT_LINE('温馨提示:此员工的奖金不存在!');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('温馨提示:发生系统错误!');
END increase_comm;
END MANAGE_EMP_PKG;--创建包体结束
--调用
SQL> variable empno number
SQL>execute :empno:= manage_emp_pkg.hire_emp('HUYONG',PM,1455,5500,14,10)
PL/SQL procedure successfully completed
empno
---------
例4:利用游标变量创建包 CURROR_VARIBAL_PKG。由于游标变量指是一个指针,其状态是不确定的,因此它不能随同包存储在数据库中,既不能在PL/SQL包中声明游标变量。但在包中可以创建游标变量参照类型,并可向包中的子程序传递游标变量参数。
-- *******************************************
-- 创建包体
-- 包 名:CURROR_VARIBAL_PKG
-- 功能描述:在包中引用游标变量
-- 创建人员:胡勇
-- 创建日期:2010-05-19
-- Q Q: 80368704
-- E-mail : 80368704@yahoo.com.cn
-- WebSite: http://www.cnblogs.com/huyong
-- ******************************************
CREATE OR REPLACE PACKAGE CURROR_VARIBAL_PKG AS
TYPE DeptCurType IS REF CURSOR
RETURN dept%ROWTYPE; --强类型定义
TYPE CurType IS REF CURSOR;-- 弱类型定义
PROCEDURE OpenDeptVar(
Cv IN OUT DeptCurType,
Choice INTEGER DEFAULT 0,
Dept_no NUMBER DEFAULT 50,
Dept_name VARCHAR DEFAULT '%');
END;
-- *******************************************
-- 创建包体
-- 包 名:CURROR_VARIBAL_PKG
-- 功能描述:在包中引用游标变量
-- 创建人员:胡勇
-- 创建日期:2010-05-19
-- Q Q: 80368704
-- E-mail : 80368704@yahoo.com.cn
-- WebSite: http://www.cnblogs.com/huyong
-- ******************************************
CREATE OR REPLACE PACKAGE BODY CURROR_VARIBAL_PKG
AS
PROCEDURE OpenDeptvar(
Cv IN OUT DeptCurType,
Choice INTEGER DEFAULT 0,
Dept_no NUMBER DEFAULT 50,
Dept_name VARCHAR DEFAULT ‘%’)
IS
BEGIN
IF choice =1 THEN
OPEN cv FOR SELECT * FROM dept WHERE deptno <= dept_no;
ELSIF choice = 2 THEN
OPEN cv FOR SELECT * FROM dept WHERE dname LIKE dept_name;
ELSE
OPEN cv FOR SELECT * FROM dept;
END IF;
END OpenDeptvar;
END CURROR_VARIBAL_PKG;
--定义一个过程
CREATE OR REPLACE PROCEDURE UP_OpenCurType(
Cv IN OUT CURROR_VARIBAL_PKG.CurType,
FirstCapInTableName CHAR)
AS
BEGIN
--CURROR_VARIBAL_PKG.CurType采用弱类型定义
--所以可以使用它定义的游标变量打开不同类型的查询语句
IF FirstCapInTableName = 'D' THEN
OPEN cv FOR SELECT * FROM dept;
ELSE
OPEN cv FOR SELECT * FROM emp;
END IF;
END UP_OpenCurType;
--定义一个应用
DECLARE
DeptRec Dept%ROWTYPE;
EmpRec Emp%ROWTYPE;
Cv1 CURROR_VARIBAL_PKG.deptcurtype;
Cv2 CURROR_VARIBAL_PKG.curtype;
BEGIN
DBMS_OUTPUT.PUT_LINE('游标变量强类型定义应用');
CURROR_VARIBAL_PKG.OpenDeptVar(cv1, 1, 30);
FETCH cv1 INTO DeptRec;
WHILE cv1%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(DeptRec.deptno||':'||DeptRec.dname);
FETCH cv1 INTO DeptRec;
END LOOP;
CLOSE cv1;
DBMS_OUTPUT.PUT_LINE('游标变量弱类型定义应用');
CURROR_VARIBAL_PKG.OpenDeptvar(cv2, 2, dept_name => 'A%');
FETCH cv2 INTO DeptRec;
WHILE cv2%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(DeptRec.deptno||':'||DeptRec.dname);
FETCH cv2 INTO DeptRec;
END LOOP;
DBMS_OUTPUT.PUT_LINE('游标变量弱类型定义应用—dept表');
UP_OpenCurType(cv2, 'D');
FETCH cv2 INTO DeptRec;
WHILE cv2%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(deptrec.deptno||':'||deptrec.dname);
FETCH cv2 INTO deptrec;
END LOOP;
DBMS_OUTPUT.PUT_LINE('游标变量弱类型定义应用—emp表');
UP_OpenCurType(cv2, 'E');
FETCH cv2 INTO EmpRec;
WHILE cv2%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(emprec.empno||':'||emprec.ename);
FETCH cv2 INTO emprec;
END LOOP;
CLOSE cv2;
END;
----------运行结果-------------------
游标变量强类型定义应用
10:ACCOUNTING
20:RESEARCH
30:SALES
游标变量弱类型定义应用
10:ACCOUNTING
游标变量弱类型定义应用—dept表
10:ACCOUNTING
20:RESEARCH
30:SALES
40:OPERATIONS
50:50abc
60:Developer
游标变量弱类型定义应用—emp表
7369:SMITH
7499:ALLEN
7521:WARD
7566:JONES
7654:MARTIN
7698:BLAKE
7782:CLARK
7788:SCOTT
7839:KING
7844:TURNER
7876:ADAMS
7900:JAMES
7902:FORD
7934:MILLER
PL/SQL procedure successfully completed
5.5 子程序重载
PL/SQL 允许对包内子程序和本地子程序进行重载。所谓重载时指两个或多个子程序有相同的名称,但拥有不同的参数变量、参数顺序或参数数据类型。
-- *******************************************
-- 创建包说明
-- 包 名:DEMO_PKG1
-- 功能描述:创建包对子程序重载进行测试
-- 创建人员:胡勇
-- 创建日期:2010-05-22
-- Q Q: 80368704
-- E-mail : 80368704@yahoo.com.cn
-- WebSite: http://www.cnblogs.com/huyong
-- ******************************************
CREATE OR REPLACE PACKAGE DEMO_PKG1
IS
DeptRec dept%ROWTYPE;
V_sqlcode NUMBER;
V_sqlerr VARCHAR2(2048);
--两个子程序名字相同,但参数类型不同
FUNCTION query_dept(dept_no IN NUMBER)
RETURN INTEGER;
FUNCTION query_dept(dept_no IN VARCHAR2)
RETURN INTEGER;
END DEMO_PKG1;
-- *******************************************
-- 创建包体
-- 包 名:DEMO_PKG1
-- 功能描述:创建包对子程序重载进行测试
-- 创建人员:胡勇
-- 创建日期:2010-05-22
-- Q Q: 80368704
-- E-mail : 80368704@yahoo.com.cn
-- WebSite: http://www.cnblogs.com/huyong
-- ******************************************
CREATE OR REPLACE PACKAGE BODY DEMO_PKG1
IS
FUNCTION check_dept(dept_no NUMBER)
RETURN INTEGER
IS
deptCnt INTEGER; --指定部门号的部门数量
BEGIN
SELECT COUNT(*) INTO deptCnt FROM dept WHERE deptno = dept_no;
IF deptCnt > 0 THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END check_dept;
FUNCTION check_dept(dept_no VARCHAR2)
RETURN INTEGER
IS
deptCnt INTEGER;
BEGIN
SELECT COUNT(*) INTO deptCnt FROM dept WHERE deptno=dept_no;
IF deptCnt > 0 THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END check_dept;
FUNCTION query_dept(dept_no IN NUMBER)
RETURN INTEGER
IS
BEGIN
IF check_dept(dept_no) =1 THEN
SELECT * INTO DeptRec FROM dept WHERE deptno=dept_no;
RETURN 1;
ELSE
RETURN 0;
END IF;
END query_dept;
FUNCTION query_dept(dept_no IN VARCHAR2)
RETURN INTEGER
IS
BEGIN
IF check_dept(dept_no) =1 THEN
SELECT * INTO DeptRec FROM dept WHERE deptno = dept_no;
RETURN 1;
ELSE
RETURN 0;
END IF;
END query_dept;
END DEMO_PKG1;
5.6 加密实用程序
ORACLE 提供了一个实用工具来加密或者包装用户的PL/SQL,它会将用户的PL/SQL改变为只有ORACLE能够解释的代码版本.
WRAP 实用工具位于$ORACLE_HOME/BIN.
格式为:
WRAP INAME=<input_file_name> [ONAME=<output_file_name>]
wrap iname=e:\sample.txt
注意:在加密前,请将PL/SQL程序先保存一份,以备后用。
5.7 删除包
可以使用 DROP PACKAGE 命令对不需要的包进行删除,语法如下:
DROP PACKAGE [BODY] [user.]package_name;
DROP PROCEDURE OpenCurType; --删除存储过程
--删除我们实例中创建的各个包
DROP PACKAGE demo_pack;
DROP PACKAGE demo_pack1;
DROP PACKAGE emp_mgmt;
DROP PACKAGE emp_package;
5.8 包的管理
为了避免调用的失败,在更新表的结构后,一定要记得重新编译依赖于它的程序包。在更新了包说明或包体后,也应该重新编译包说明与包体。语法如下:
ALTER PACKAGE package_name COMPILE [PACKAGE|BODY|SPECIFICATION];
也可以通过以下数据字典视图查看包的相关。
DBA_SOURCE, USER_SOURCE, USER_ERRORS,DBA-OBJECTS
如,我们可以用:select text from user_source wherename='DEMO_PKG1';来查看我们创建的包的源码。
reference http://www.cnblogs.com/huyong/archive/2011/05/10/2041951.html
http://www.cnblogs.com/huyong/archive/2011/05/13/2045407.html
http://www.cnblogs.com/huyong/archive/2011/05/04/2036377.html
http://www.cnblogs.com/huyong/archive/2011/04/28/2031541.html
http://www.cnblogs.com/huyong/archive/2011/05/26/2057973.html
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30130773/viewspace-2122883/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30130773/viewspace-2122883/