本文编写目的： 本文对ORACLE存储过程，存储函数，包作了一个概括性的介绍，以实例为驱动介绍了存储过程，存储函数，包的语法，数据类型以及程序开发编写的方法。通过对本文的学习，达到使用ORACLE存储过程进行基本编程的目的。 本文主要参考： 《新编ＯRACLE7入门教程》 电子工业出版社 《ORACLE8I数据库高级应用开发技术》人民邮电出版社 《ORACLE8PL/SQL程序设计》机械工业出版社 本文面向对象： 对ORACLE有一定认识和经验的开发者和系统管理者。 本文中各例均使用Oracle数据库demo用户. 用户名:scott 用户口令:tiger 数据结构建立: /*使用system用户及口令登录oracle数据库*/ $SQLPLUS system/passwd /*建立scott用户口令为tiger*/ $SQL>create user scott identified bytiger; /*给scott用户授权*/ $SQL>grant create session to scott; $SQL>exit; $SQLPLUS scott/tiger $SQL>start $ORACLE_HOME/sqlplus/demo/demobld.sql主要数据结构：

[td=11]
[img=510,297]file:///C:/Users/ADMINI~1/AppData/Local/Temp/msohtmlclip1/07/clip_image001.gif[/img]

第一章 oracle存储过程概述Oracle存储过程(store procedure)作为PL/SQL语言的子程序,使用PL/SQL语言对数据处理逻辑,数据存储,数据操纵进行描述和封装,通过oracle其他工具(Pro*c&sqlplus等)对存储过程调用,实现相应功能.Oracle存储过程在创建时经过数据库编译,作为数据库对象存储在数据库中,使用存储过程名称和输入输出参数实现存储过程描述的功能. 存储过程是由流控制和SQL语句书写的过程，这个过程经编译和优化后存储在数据库服务器中，使用时只要调用即可。在ORACLE中，若干个有联系的过程可以组合在一起构成程序包。使用存储过程有以下的优点：1. 存储过程的能力大大增强了SQL语言的功能和灵活性。存储过程可以用流控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算.2. 可保证数据的安全性和完整性。3. 通过存储过程可以使没有权限的用户在控制之下间接地存取数据库，从而保证数据的安全。4. 通过存储过程可以使相关的动作在一起发生，从而可以维护数据库的完整性。5. 再运行存储过程前，数据库已对其进行了语法和句法分析，并给出了优化执行方案。这种已经编译好的过程可极大地改善SQL语句的性能。由于执行SQL语句的大部分工作已经完成，所以存储过程能以极快的速度执行。6. 可以降低网络的通信量。7. 使体现企业规则的运算程序放入数据库服务器中，以便集中控制。 当企业规则发生变化时在服务器中改变存储过程即可，无须修改任何应用程序。企业规则的特点是要经常变化，如果把体现企业规则的运算程序放入应用程序中，则当企业规则发生变化时，就需要修改应用程序工作量非常之大（修改、发行和安装应用程序）。如果把体现企业规则的运算放入存储过程中，则当企业规则发生变化时，只要修改存储过程就可以了，应用程序无须任何变化. Oracle存储函数(FUNCTION)作为特殊的存储过程,与C/C++语言函数相似,具备函数名,输入输出参数以及返回值.存储过程和存储函数都是相对独立的实体.Oracle包(Package)为了管理上的方便,把一些相关的程序结构如存储过程,存储函数,变量,游标等组织在一起,构成一个包.Oracle包具有面向对象程序设计语言的特点,是对PL/SQL程序设计元素的封装.包类似于C++和JAVA语言中的类,其中变量相当于类中的成员变量,存储过程和存储函数相当于类方法.包中的元素分为共有元素和私有元素,两种元素允许访问的程序范围不同.
1.1 存储过程基本结构(PROCEDURE)1.1.1创建存储过程CREATE　[ORREPLACE] PROCEDURE　存储过程名　（参数定义标）　IS/AS变量定义　　BEGIN PL/SQL语句块 EXCEPTION 例外处理 END 存储过程名 定义说明:1. 参数定义表:存储过程可以有三类参数IN 数据从调用环境传入存储过程OUT 数据从存储过程传入调用环境INOUT 数据可以传入或传出存储过程参数使用原则: 参数类型可以为ORACLE允许的任意类型,也可为%TYPE(与其他某一变量类型一致)或%ROWTYPE(与数据库中某一对象表,游标等数据类型一致) 类型 指定参数时,不能指定长度 所有输出参数(OUT)只能出现在SELECT INTO语句或赋值语句中. 尽量减少IN参数个数.2. 变量定义 变量类型可以为ORACLE允许的任意类型,也可为%TYPE(与其他某一变量类型一致)或%ROWTYPE(与数据库中某一对象表,游标等数据类型一致) 类型.3. 例外处理存储过程例外处理与PL/SQL错误处理一致,可按条件执行相应的操作. 例1.1 本例实现为指定雇员号(emp)的雇员加工资(sal),数据源为表emp
[td=570] [table=98%]
/*CREATE 创建存储过程*/ /*REPLACE 替换存储过程*/ /*CREATE OR REPLACE 如存储过程不存在则创建,否则替换*/ CREATE OR REPLACE PROCEDURE /*存储过程名为raise_sal*/ /*参数为输入NUMBER型 emp_id和输入NUMBER型 add_sal*/ raise_sal(emp_id IN NUMBER,add_sal IN NUMBER) AS /*无局部变量声明*/ BEGIN /*PLSQL语句块*/ UPDATE emp SET sal = sal+ add_sal WHERE empno = emp_id; EXCEPTION /*例外处理 NO_DATA_FOUND 数据未找到时执行*/ WHEN NO_DATA_FOUND THEN /*raise_application_error(错误代码,’错误信息’) 向调用环境返回错误信息*/ raise_application_error(-20011,'InvalidEmployee'||TO_CHAR(emp_id)); END raise_sal;
存储过程简例

1.1.2 存储过程删除 $SQLPLUS> DROP PROCEDURE 过程名 1.1.3 调用存储过程1. SQLPLUS环境语法 $SQLPLUS> EXECUTE 存储过程名 参数 SQLPLUS中的变量或常量例: $SQL>EXECUTE raise_sal(10,1000);2. SQLDBA环境语法 $SQLPLUS> EXECUTE 存储过程名 参数 SQLPLUS中的变量或常量3. SQLFORMS 语法:过程名 参数:SQLFORMS中的域或全局变量4. PLSQL或其他存储过程语法:过程名 参数:PLSQL局部变量5. Pro*C 语法:EXEC　SQL 过程名参数:主变量例1.2

[td=11]
[td=558] [table=98%]
#include EXEC SQL INCLUDE SQLCA; main(){ /*声明宿主变量*/ EXEC SQL BEGIN DECLARE SECTION; char *oid="scott/tiger"; int tt; EXEC SQL END DECLARE SECTION; /*连接数据库*/ EXEC SQL CONNECT :oid; if(sqlca.sqlcode != 0) {printf("connect database error \n");exit(0);} /*调用存储过程raise_sal*/ EXEC SQL EXECUTE BEGIN Raise_sal(100,1000); END; END-EXEC; /*调用完毕*/ if(sqlca.sqlcode != 0) {printf("Execute error \n");exit(0);} /*断开数据库连接*/ EXEC SQL COMMIT WORK RELEASE; }

1.2存储函数(FUNCTIONE)存储函数是一类特殊的存储过程,与一般存储过程不同的是存储函数必须返回一个值.1.2.1 创建存储函数CREATE [OR REPLACE] FUNCTION 存储函数名RETUNR 返回值类型IS/AS 变量声明BEGIN PLSQL语句块EXCEPTION 例外处理END 存储函数名备注: 返回值类型不带长度1.2.2 删除存储函数 $SQL>dropfunction 存储函数名例1.3 从员工信息表(emp)中选择部门代号为v_empno员工的工资

[td=11]
[td=582] [table=98%]
CREATE OR REPLACE FUNCTION get_sal(v_empno IN emp.empno%TYPE) RETURN NUMBER IS v_emp_sal emp.sal%TYPE:=0; BEGIN SELECT sal INTO v_emp_sal FROM emp WHERE empno=v_empno; RETURN(v_emp_sal); END get_sal;

创建存储函数

1.3 包(package)1.3.1 包的基本结构包中可以包含过程(procedure),函数(function),变量(variable)游标(cursor),常量(constant),例外处理(exception). 包由两部分组成:包定义和包体 包定义:对包的公共元素如过程,函数,变量,常量,游标,例外情况等进行说明,可在包外独立使用这些公共元素. 包体部分包括包中使用的私有元素和包的公共元素的定义.1.3.2 包的创建1. 创建包定义CREATE〔OR REPLACE〕PACKAGE package_nameIS/AS公共元素声明END package_name;2. 创建包体CREATE〔OR REPLACE〕 PACKAGE BODY package_nameIS/AS私用元素定义公共元素定义BEGIN PLSQL语句END package_name;例 1.4
[td=12][td=546][td=12]
[td=2,1,558] [table=98%]
CREATE OR REPLACE PACKAGE emp_package AS /*声明函数*/ FUNCTION hire_emp(name VARCHAR2,job VARCHAR2,mgr NUMBER,hiredate DATE,sal NUMBER ,comm NUMBER,deptno NUMBER) RETURN NUMBER; /*声明过程*/ PROCEDURE fire_emp(emp_id NUMBER); PROCEDURE sal_raise(emp_id NUMBER,sal_id NUMBER); END emp_package; 创建包定义

[td=2,1,558] [table=98%]
CREATE OR REPLACE PACKAGE BODY emp_package AS /*定义函数*/ FUNCTION hire_emp(name VARCHAR2,job VARCHAR2,mgr NUMBER,hiredate DATE,sal NUMBER,comm NUMBER,deptno NUMBER) RETURN NUMBER IS new_empno NUMBER(10); BEGIN SELECT emp_sequence.NEXTVAL INTO new_empno FROM emp; INSERT INTO emp VALUES(new_empno,name,job,mgr,hiredate,sal,comm,deptno); RETURN (new_empno); END hire_emp; /*定义过程*/ PROCEDURE fire_emp(emp_id NUMBER) IS BEGIN DELETE FROM emp WHERE empno=emp_id; IF SQL%NOTFOUND THEN raise_Application_error(-20011,'Ivalid EmployeeNUMBER'||TO_CHAR(emp_id)); END IF; END fire_emp; PROCEDURE sal_raise(emp_id NUMBER,sal_id NUMBER) AS BEGIN UPDATE emp SET sal=sal+sal_id WHERE empno=emp_id; IF SQL%NOTFOUND THEN raise_Application_error(-21011,'Ivalid EmployeeNUMBER'||TO_CHAR(emp_id)); END IF; END sal_raise; /*结束包定义*/ END emp_package; 创建包体

1.3.3 调用包中元素$SQL>EXECUTE 包名.元素名(参数列表);例:$SQL>EXECUT emp_package.raise_sal(7654,100);1.3.4 包的修改和删除删除包:$SQL>dropPACKAGE 包名 $SQL>drop PACKAGE BODY 包名备注:包定义和包体应该同时修改,并保持一致.
第二章 oracle存储过程基础――PL/SQLOracle存储过程以PL/SQL作为其流程控制语言,可以理解Oracle存储过程为具有名称和输入输出参数的PL/SQL语句块,因此,本章介绍PL/SQL的语法和使用. 2.1 pl/sql基础2.1.1 PL/SQL简介一 PL/SQL优点1.过程化能力 PL/SQL称为SQL过程语言,他将高级程序设计语言中所具备的过程能力与非过程化的SQL语言有机的结合在一起,形成了一个集成式的Oracle数据库事务处理应用开发工具,为应用开发者提供了增强生产力的机制. PL/SQL以块(blocks)为单位,较大的块中可以镶嵌子块,可以将复杂的问题分解成一组易于控制的,很好定义的逻辑模块. 在PL/SQL块中可以进行变量定义,例外处理,然后在SQL语句中调用.PL/SQL块中可以使用过程化语言控制结构进行程序设计,包括条件转移,循环控制,游标. 2.改进处理性能 使用PL/SQL,Oracle数据库将PL/SQL语句块作为一组,一次提交给Oracle服务进程,减少Oracle客户服务进程间的交互. 3.良好的应用移植性 由于PL/SQL是模块化结构,在进行应用移植时可以将模块内部的复杂处理忽略,二只考虑模块间的数据交换. 4.与关系数据库管理系统(RDBMS)集成 使用PL/SQL,可以将许多用户都可能用到的处理编程封装过程或包,与关系数据库管理系统有效集成.这样,用户可使用Oracle工具直接调用,提高了开发效率,减少了再编译时间,提高系统性能.二 PL/SQL应用环境 SQL*PLUS;SQL*FORMS;Oracle CDE 工具;Pro*C 三 PL/SQL块的基本结构基本的PL/SQL块由定义部分,执行部分,例外处理部分组成
[td=558] [table=98%]
DECLARE 定义部分 BEGIN 执行部分 EXCEPTION 例外处理部分 END PL/SQL块基本结构

1. 定义部分: 定义在程序执行部分使用的常量,变量,游标和例外处理名称2. 可执行部分 包括数据库操作语句和PL/SQL块控制语句3. 例外处理部分 对执行部分的所有PL/SQL语句的执行进行监控,如执行发生例外,则程序跳到该部分执行2.1.2 一个简单的PL/SQL块 例2.1 向EMP表插入一条雇员记录,雇员代号为8000,雇员名是WUCHEN,其他字段与SMITH雇员相同,然后将所有雇员工资增加$500.
[td=558] [table=98%]
/*一个简单PLSQL块开始*/ /*定义变量*/ DECLARE /*ROWTYPE类型 定义变量myrecord为一结构,与表emp各字段数据类型一致*/ myrecord emp%ROWTYPE; /*定义变量myempno 类型为number(4) 变量非空,初值为8000*/ myempno number(4) NOT NULL:=8000; /*TYPE类型 定义变量myname 类型与表emp中ename字段一致*/ myname emp.ENAME%TYPE; /*CONSTANT关键字 定义常量 addsal 值为500*/ addsal CONSTANT number(4):=500; BEGIN SELECT * INTO myrecord FROM EMP WHERE ENAME='SMITH'; myname:='WUCHEN'; INSERT INTO EMP(EMPNO,ENAME,SAL,COMM,JOB,HIREDATE,DEPTNO) VALUES(myempno,myname,myrecord.sal,myrecord.comm,myrecord.job,myrecord.h iredate,myrecord.deptno); UPDATE EMP SET sal = sal + addsal;

一个简单的PL/SQL块程序

END;

执行步骤: $SQLPLUS scott/tiger $SQL>start./PLSQL块名称 $SQL>. (输入符号点) $SQL>r (字母r 或符号/执行程序)
一定义变量 在PLSQL中所使用的变量必须在变量定义部分明确定义.变量定义部分是包括在关键字DECLARE和BEGIN之间的部分,每条语句后用(;)结束. 定义格式: 变量标示符[CONSTANT] 数据类型 [NOT NULL] [:=缺省值或PLSQL表达式]; 变量标示符命名规则应遵循SQL实体命名规则 定义常量时必须加关键字 CONSTANT 必须为其赋值 如该变量不允许为空值,必须加参数NOT NULL 变量赋值时,可使用:=或使用关键字DEFAULT. 每行只能定义一个变量. 数据类型 简单数据类型(标量数据类型): NUMBER(m,n) 数字类型 m为总长度,n为小数长度 CHAR(m) 字符型 m为变量长度 VARCHAR2(m) 可变长字符型 m为最大长度 DATE 日期型 LONG 长型 BOONEAN 布尔型值为TRUE FALSE NULL 已定义变量%TYPE 定义成与已定义变量一致类型 复合数据类型 变量标示符 对象标示符%ROWTYPE; 对象标示符可为表,游标等,变量被定义成与数据库对象一致的类型结构,当数据库结构改变时,不必改变改变量的定义. 使用%ROWTYPE分为两种不同情况:1. 作为查询结果存放空间时:select字段列表 INTO　%ROWTYPE型变量2. 作为单个成员使用:%ROWTYPE变量名.字段名
二变量赋值变量赋值时需使用PLSQL变量赋值操作符(:=)1. 常量赋值: 变量名 := 常量2. 变量赋值: 变量名 := 同类型变量3. 为%ROWTYPE型变量赋值a . select 列表 into%ROWTYPE型变量 from表b. 为%ROWTYPE变量每个成员单独赋值 如 %ROWTYPE变量.变量成员 = 值4. 表达式赋值: 变量名:=表达式或函数
三 PLSQL中使用的SQL语句在PL/SQL块中,所有对数据库的访问和操作还是要经由SQL语言进行,在PL/SQL块中可以使用数据查询语言,数据操纵语言和数据控制语言,但不能使用数据定义语言具体地说可以使用select,insert,update,delete,commit,rollback,但不能使用create,alter,drop,grant,revoke.1. PL/SQL块中使用查询语句在PL/SQL中使用select时必须加INTO语句.INTO子句后的变量个数和位置必须与SELECT后的字段列表相同SELECT语句中的WHERE条件可以包含PL/SQL块中定义的变量及表达式.SELECT语句必须保证有且仅有一条记录返回,否则出错: TOO_MANY_ROWS -1422 记录多于一条 NO_DATA_FOUND -1403 没找到记录

DUP_VAL_ON_INDEX 唯一索引上有重复值

在SQL语句中使用的变量名应与数据库字段名区分开.

[td=11]
[td=558] [table=98%]
DECLARE /* 变量emp_rec为ROWTYPE型结构变量,结构类型与表EMP结构类型一致*/ emp_rec EMP%ROWTYPE; /*变量v_ename与表emp中ename字段类型一致*/ v_ename EMP.ename%TYPE:='SMITH'; BEGIN SELECT * INTO emp_rec FROM emp /*ENAME字段 = 变量v_ename值*/ WHERE ENAME=v_ename; END;
PL/SQL使用查询语句

2. PL/SQL块中使用操纵语句 PL/SQL中使用INSERT,DELETE,UPDATE与SQL语句完全一样,只是可以使用定义的变量和表达式

[td=11]
[td=558] [table=98%]
DECLARE v_empno emp.empno%TYPE NOT NULL:=8000; v_ename emp.ename%TYPE:='Bill'; v_job emp.job%TYPE:='MANAGER'; v_sal emp.sal%TYPE:=2000; v_comm emp.comm%TYPE:=1000; v_hiredate emp.hiredate%TYPE:=SYSDATE; v_deptno emp.deptno%TYPE:=10; v_addsal emp.sal%TYPE; BEGIN INSERT INTO emp(empno,ename,job,sal,comm,hiredate,deptno) VALUES (v_empno,v_ename,v_job,v_sal,v_comm,v_hiredate,v_deptno); v_addsal:=1000; UPDATE emp SET sal=sal+v_addsal WHERE empno=v_empno; DELETE FROM emp WHERE empno > 8000; COMMIT WORK; END; PL/SQL使用数据操纵语句

3. PL/SQL块中使用事物控制语句提交命令(COMMIT):结束当前事物,对数据库作永久性改变 语法 COMMIT [WORK]回退命令(ROLLBACK): 结束当前事物,并放弃对数据库所作修改 语法 ROLLBACK [WORK]保存点(SAVEPOINT):为了避免一处失败导致全部事物回滚,可以使用SAVEPOINT和ROLLBACKTO语句 语法 SAVEPOINT 标记 ROLLBACK TO 标记

[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[SavePoint[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[
保存点(SAVEPOINT)是事务处理过程中的一个标志，与回滚命令(ROLLBACK)结合使用，主要的用途是允许用户将某一段处理回滚而不必回滚整个事务，这在PL/SQL开发中还是很有用处的。下面的例子中，把SAVEPOINT标记在INSERT语句之前，如果这条INSERT语句试图将重复的数据保存到EMP表中的话，将触发执行预先定义的DUP_VAL_ON_INDEX例外处理，在这里面的ROLLBACK TO do_insert命令将回滚上面的那条INSERT操作，而不会影响前面的任何操作。
DECLARE emp_id emp.empno%TYPE;BEGIN UPDATE emp SET... WHERE empno = emp_id; DELETE FROMemp WHERE ... ... SAVEPOINTdo_insert; INSERT INTOemp VALUES (emp_id, ...);EXCEPTION WHENDUP_VAL_ON_INDEX THEN ROLLBACK TOdo_insert;END;
如果你定义了多个savepoint，当你指定回滚到某个savepoint时，那么回滚操作将回滚这个savepoint后面的所有操作（即使后面可能标记了N个savepoint）。例如，在一段处理中你定义了五个savepoint，从第三个savepoint回滚，后面的第四、第五个标记的操作都将被回滚，如果不使用ROLLBACK TO savepoint_name而使用ROLLBACK，将会滚整个事务处理。
如果你在递归子程序里面定义了一个savepoint, 如果每一个递归层都设置了SAVEPOINT. 此时, 你只能回滚到最近的一个savepoint.
Savepoint的声明可以在同一个事务处理里面重复定义. 它的作用就是把savepoint从上一个位置转移到目前的位置.因而，执行回滚也只回滚到最近的savepoint.下面是一个例子:
BEGIN ... SAVEPOINTmy_point; UPDATE emp SET... WHERE empno = emp_id; ... SAVEPOINTmy_point; -- move my_point to currentpoint INSERT INTOemp VALUES (emp_id, ...);EXCEPTION WHEN OTHERSTHEN ROLLBACK TOmy_point;END;
另外，Oracle没有对每个session里面可以使用的savepoint个数做限制.

2.1.3 PL/SQL流程控制PL/SQL具有与高级语言类似的流程控制语句.PL/SQL主要控制语句有: 条件控制语句 循环控制语句 跳转控制语句1. 条件控制语句:IF_THEN_ELSE语句语法: IF 条件 THEN 语句; ELSE 语句; ENDIF;条件可为IS NULL或NOT IS NULL以及AND, OR,NOT,逻辑运算符

[td=11]
[td=546] [table=98%]
例:将emp表中的雇员名为SMITH雇员的工资修改,如果工资大于$2000,则加$500,否则加$1000. DECLARE v_ename emp.ename%TYPE:='SMITH'; v_addsal emp.sal%TYPE; v_sal emp.sal%TYPE; BEGIN SELECT sal INTO v_sal FROM emp WHERE ename=v_ename; IF v_sal > 2000 THEN v_addsal:=500; ELSE v_addsal:=1000; END IF; UPDATE emp SET sal=sal+v_addsal WHERE ename=v_ename; END; PL/SQL控制语句IF_THEN_ELSE例程

IF_THAN_ELSIF语句: 语法:IF 条件 THEN 语句; ELSIF条件 THEN 语句; [ELSIF条件 THEN 语句;] [ELSE语句;] ENDIF

[td=11]
[td=546] [table=98%]
根据emp表中的工种为SMITH修改工资,若工种为MANAGER,工资加$1000,工种为SALESMAN,工资加$500,工种为ANALYST,工资加$200,否则加$100.
DECLARE v_job emp.job%TYPE; v_addsal emp.sal%TYPE; BEGIN SELECT job INTO v_job FROM emp WHERE ename='SMITH';
IF v_job='MANAGER' THEN v_addsal :=1000; ELSIF v_job='SAIESMAN' THEN v_addsal :=500; ELSIF v_job='ANALYST' THEN v_addsal :=200; ELSE v_addsal :=100; END IF;
UPDATE emp SET sal=sal+v_addsal WHERE ename='SMITH'; COMMIT WORK; END;
条件控制语句例程

ELSIF v_job=’SALESMAN’ THENv_addsal=500;ELSIF v_job=’ANALYST’ THENV_addsal=200;ELSE v_addsal=200;END IF UPDATE emp SET sal=sal+v_addsalWHERE ename=’SMITH’;END;

2. 循环控制语句:LOOP循环: 语法: LOOP 语句; [EXIT [WHEN 条件]]; ENDLOOP;

[td=558] [table=98%]
例 给10号部门增加新雇员,只确定雇员代号,其他信息忽略. DECLARE v_empno emp.empno%TYPE:=8000; BEGIN LOOP INSERT INTO emp(deptno,empno) VALUES(10,v_empno); v_empno:=v_empno+100; /*如果雇员代号>=9000则退出循环*/ EXIT WHEN v_empno >= 9000; END LOOP; END;

FOR 循环: 语法: FOR 计数器 IN [REVERSE] 下界…上界 LOOP 语句; END LOOP; 计数器用于控制循环次数的变量,无需在定义部分做出定义,系统隐含定义为整数,REVERSE表示计数器从上界到下界递减计数,下界定义初值,上界定义终值,下界应小于上界.对计数器不可作赋值操作.
[td=570] [table=98%]
例 同上例
DECLARE v_deptno emp.deptno%TYPE:=10; BEGIN FOR i IN 1...10 LOOP INSERT INTO emp(deptno,empno) VALUES(v_deptno,8000+i*100); END LOOP; COMMIT WORK; END;
循环控制例程

WHILE 循环 语法: WHILE 条件 LOOP 语句; ENDLOOP;

[td=558] [table=98%]
例 同上例 DECLARE i number(2):=1; BEGIN WHILE i<=10 LOOP INSERT INTO emp(deptno,empno) VALUES(10,8000+i*100); i:=i+1; END LOOP; COMMIT WORK; END; 循环控制例程 3. 跳转控制语句:语法: 《标号》 其他语句； GOTO 标号；跳转语句可在统一块语句间跳转 跳转语句可从子块跳转倒父块中，但不能从父块跳转到子块中 跳转语句不能在IF语句体外跳到IF体内 跳转语句不能从循环体外跳到循环体内 [td=11] [td=570] [table=98%] 例 同上例 DECLARE v_empno emp.empno%TYPE := 8000; BEGIN <> INSERT INTO emp(deptno,empno) VALUES(10,v_empno); v_empno:=v_empno+100; IF v_empno <= 9000 THEN GOTO repeat; END IF; END; 跳转控制例程 2.2 游标(CURSOR)在PL/SQL查询语句中,有时会返回多条记录,这时如使用SQL语句则会出错.因此,在查询语句返回多条记录或不知返回结果数目时,必须使用游标.[font="]2.2.1 游标的概念PL/SQL中游标的使用与Pro*C中游标使用相似,包括定义,打开,提取数据,关闭四个步骤.一般游标在定义，打开后使用循环语句逐条处理提取的数据。一 定义游标 语法: CURSOR 游标名称 IS SELECT语句; 定义游标应写在PL/SQL语句的DECLARE变量定义部分 定义游标时SELECT语句中不可有INTO子语句 在SELECT语句中使用的变量必须在定义游标前定义二 打开游标 语法:OPEN 游标名; 在BEGIN语句之后,可以打开游标,在打开游标之前,必须对游标所涉及到的变量赋值.三 利用游标提取数据 语法: FETCH 游标名 INTO 变量1,变量2,….. 游标每次只能取到一条数据,同时游标指针下移,等待取下一条数据.该条语句变量列表应与定义游标时的参数列表一致四 关闭游标 语法: CLOSE 游标名 关闭游标,释放资源,游标关闭后不能再提取数据.[font="]2.2.2 游标的属性游标的属性标示游标的运行状况.%ISOPEN 布尔型 表示游标是否打开%NOTFOUND 布尔型 描述最后一次FETCH的结果%FOUND 布尔型 描述最后一次FETCH的结果,与NOTFOUND相反%ROWCOUNT 数字型 描述当前取值的条数 例 查询10号部门所有雇员的姓名工资，并插入到临时表tmp中tmp表结构为 t1 char(20),t2 number(10)以下使用三种不同的循环方法实现该例：[img=575,1103]file:///C:/Users/ADMINI~1/AppData/Local/Temp/msohtmlclip1/07/clip_image002.gif[/img] [font="]2.2.3 游标中FOR循环的使用游标使用FOR循环可以简化游标的操作，使用FOR循环时，系统隐含定义了一个数据类型为%ROWTYPE的变量为循环计数器，此时在PL/SQL块中不用显式的打开，关闭游标。语法： FOR 组合变量名 IN 游标名 LOOP 语句； END LOOP;[img=558,918]file:///C:/Users/ADMINI~1/AppData/Local/Temp/msohtmlclip1/07/clip_image003.gif[/img] [font="]2.2.4 带参数游标的使用方法在定义游标时，可以加入参数，参数再游标中使用。语法： DECLARE CURSOR游标名（参数列表） IS SELECT语句； 2.3 动态SQL语句动态SQL语句是指语句文本在应用程序运行时才被建立的SQL语句或PL/SQL块，动态SQL语句文本中可以包含结合参数占为符。在使用占位符时，必须在其前面加冒号（：）前缀。使用动态SQL语句还能执行在PL/SQL块中不能静态执行的SQL语句，如DDL语句。语法： EXECUTE IMMDIATE 动态语句串 [INTO{variable[,variable]….|record}] [USING[IN|OUT|IN OUT] bind_argument [,[IN|OUT|INOUT] bind_argument]…];其中动态语句串表示SQL语句或PL/SQL块文本，对于SQL语句不能使用语句结束符（；），对于PL/SQL块文本，必须加结束符。INTO子句只能用于单行查询语句，将查询结果存储到指定变量variable或记录record变量中。USING子句使用bind_argument值替换动态语句串中的占位符。USING语句不能传递布尔型变量（TRUE FALSE NULL）。由于Oracle自动将所有未赋值的变量设置为NULL,因此在需要传递NULL变量时，使用未赋值变量即可。 [td=570] [table=98%] 例：DECLARE no_initialization NUMBER; BEGIN EXECUTE IMMEDIATE ‘DELETE FROM emp WHERE comm=:x’ USING no_initialization; END; 使用未赋值变量传递NULL USING子句中，不能使用结合参数传递对象名称。 [td=582] [table=98%] 例： DECLARE Tab_name VARCHAR2(30); BEGIN Tab_name :=’scott.mytab’; ----对象名称 /**错误执行*/ EXECUTE IMMDIATE ‘DROP TABLE :tab’ USING tab_name; /*正确执行*/ EXECUTE IMMDIATE ‘DROP TABLE ‘||tab_name; END; USING语句中不能传递对象 USING语句中结合变量默认参数模式为输入参数，当结合变量为输出参数或输入输出参数时应加OUT或IN OUT选项说明。 [td=582] [table=98%] DECALRE Dyna_sql VARCHAR2(128); Old_loc VARCHAR2(15); BEGIN /*para为占位符*/ Dyna_sql:=’update dept set loc=’’BEIJING’’ where deptno=90 returning loc into :para’; EXECUTE IMMDIATE dyna_sql USING OUT old_loc; END; Old_loc为输出变量 2.4 例外处理例外处理是指程序在执行过程中的警告或错误的处理。[img=306,245]file:///C:/Users/ADMINI~1/AppData/Local/Temp/msohtmlclip1/07/clip_image004.gif[/img]语法： EXCEPTION WHEN例外情况1[OR 例外情况2…] THEN 语句； WHEN例外情况 3[OR 例外情况4…] THEN 语句； [WHENOTHERS THEN 语句；] 常见系统预定义例外情况 NO_DATA_FOUND ORA_01403 执行SELECT时未找到数据TOO_MANY_ROWS ORA_01427 未使用游标的SELECT语句返回了多行数据INVALID_CURSOR ORA_1001 非法的游标操作VALUES_ERROR ORA_06502 出现数字，数据转换，截字符串错误INVALID_NUMBER ORA_01722 字符串向数字转换失败ZERO_DIVIDE ORA_01476 分母未零DUP_VAL_ON_INDEX ORA_0001 向具有唯一索引表中插入重复键值TIMEOUT_ON_RESOURCE ORA_00051 等待资源超时INVALID_CURSOR ORA_1001 试图关闭一个未打开的游标NOT_LOGGED_ON ORA_1012 数据库未联接LOGIN_DENIED ORA_1017 登录数据库失败SYS_INVALID_ROWID ORA_1410 无效字符串STORAGE_ERROR ORA_30625 PL/SQL用尽内存或内存被破坏ROWTYPE_MISMATCH ORA_6504 赋值时，类型不匹配CURSOR_ALREADY_OPEN ORA_6511 试图打开一个已达开的游标PROGRAM_ERROR ORA_6501 PL/SQL块内部错误 [td=558] [table=98%] 例 删除EMP表中的SMITH信息。 DECLARE v_ename emp.ename%TYPE:='SMITH'; BEGIN DELETE FROM emp WHERE ename=v_ename; COMMIT WORK; EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO tmp(t1) VALUES('SMITH is not found'); WHEN TOO_MANY_ROWS THEN ROLLBACK WORK; INSERT INTO tmp(t1) VALUES('SMITH is too many rows'); WHEN OTHERS THEN ROLLBACK WORK; INSERT INTO tmp(t1) VALUES('other error occurred'); END; 系统预定义例外处理 用户可以自定义例外处理，例外处理的名称需在定义部分声明，在引发例外处理时，需使用RAISE子句：语法： RAISE 例外处理名称 [td=558] [table=98%] DECLARE v_sal_err emp.sal%TYPE:=5000; CURSOR C1 IS SELECT sal FROM EMP; v_sal emp.sal%TYPE; sal_error EXCEPTION; --定义例外名称 BEGIN OPEN C1; FETCH C1 INTO v_sal; LOOP EXIT WHEN C1%NOTFOUND; IF v_sal > 5000 THEN RAISE sal_error; ---调用例外处理 ELSE FETCH C1 INTO v_sal; END IF; END LOOP; CLOSE C1; EXCEPTION WHEN sal_error THEN ---例外处理 INSERT INTO tmp(t1) values ('found error sal>5000'); CLOSE C1; END;

2.5 一个完整的PL/SQL实例问题：通过DEPT表查询储所有部门号，并将每个部门工资高于$2000的雇员姓名，工资插入到highsal表中；工资低于$2000的雇员插入到lowsal表中，如果工资大于$5000，则出错，并向tmp表中插入出错信息，退出程序，最后在summary表中统计各部门人数以及工资总和。

[td=558] [table=98%]
DECLARE CURSOR dept_cursor IS SELECT distinct deptno FROM DEPT; deptrec dept_cursor%ROWTYPE; CURSOR emp_cursor(v_deptno emp.deptno%TYPE) IS SELECT ename,sal FROM EMP WHERE deptno=v_deptno order by sal; emprec emp_cursor%ROWTYPE; allsal NUMBER(5):=0; allemp NUMBER(3):=0; ERR EXCEPTION; BEGIN FOR deptrec IN dept_cursor LOOP FOR emprec IN emp_cursor(deptrec.deptno) LOOP IF emprec.sal > 5000 THEN RAISE ERR; ELSIF emprec.sal > 2000 THEN INSERT INTO highsal(ename,sal)VALUES(emprec.ename,emprec.sal); ELSE INSERT INTO lowsal(ename,sal) VALUES(emprec.ename,emprec.sal); END IF; allsal:=allsal+emprec.sal; END LOOP; INSERT INTO summary(deptno,sum_emp,sum_sal) VALUES (deptrec.deptno,allemp,allsal); allsal:=0; END LOOP; COMMIT WORK; EXCEPTION WHEN ERR THEN ROLLBACK WORK; INSERT INTO tmp(t1) VALUES('found sal > 5000'); COMMIT WORK; WHEN OTHERS THEN ROLLBACK WORK; INSERT INTO tmp(t1) VALUES('found other error'); COMMIT WORK; END;

第三章 oracle存储过程讨论Oracle存储过程是一组有名字并且可以传递参数的PL/SQL语句集合，Oracle函数是具有返回值的存储过程，包是一组具有类似属性的存储过程的集合。3.1 函数（FUNCTION）PL/SQL用户定义函数是一组PL/SQL语句的集合，它具有两个特点：1． 每个函数具有固定的名称。2． 函数向其调用者返回数据。3.1.1 用户函数创建，编译，删除 用户函数建立： 语法 CREATE[OR　REPLACE] FUNCTIONE [schema.]function_name （[argument [IN|OUT[NOCOPY] | IN OUT [NOCOPY]] datatype[,….]]） RETURNdatatype [invoker_rights_clause][DETERMINISTIC] [PARALLEL_ENABLE] {IS|AS} PL/SQL block; 从功能上分可将该语句分为两部分：{IS|AS}前为函数定义部分，说明函数的参数数量，类型；第二部分为{IS|AS}以后部分，这是函数体，实现函数功能 OR REPLACE 语句说明当创建用户函数时，该函数已经存在，则用当前函数定义替换原函数定义。 Schema 说明所创建的函数的模式名称，默认为当前用户。 Function_name 用户函数的名称。 Argument 说明参数名称，参数类型，参数为输入，输出，输入输出。 Datatype 说明参数类型，它可以是PL/SQL支持的所有数据类型。在制定数据类型时，不能指定长度，精度和小数位数以及NOT NULL等约束。 NOCOPY 说明参数数据传递方法。地址传递或值传递。当作值传递时，大量数据需在内存中拷贝，影响效率，因次考虑使用NOCOPY参数，进行地址传递。 RETURN 说明函数返回值的数据类型。 Invoker_rights_clause 说明应用程序在调用函数时所使用的权限模式: AUTHID DEFINER 定义者权限 AUTHID CURRENT_USER 当前用户权限 DETERMINISTIC Oracle优化提示选项，他提示系统可以使用函数返回值的存储备份 PARALLEL_ENABLE 说明函数能够被并行查询操作所执行。 在Oracle服务器执行CREATE FUNCTION 语句时，用户应具备一定的权限，用户在其自己的模式下创建函数时，应拥有CREATE PROCEDURE权限，用户在其他用户模式下创建函数时，需要拥有CREATE ANY PROCEDURE权限。

[td=558] [table=98%]
例：创建函数，查询指定部门的工资总和。 CREATE OR REPLACE FUNCTION get_salary( dept_no NUMBER, --部门编号 emp_count OUT INTEGER) --输出参数，部门人数 RETURN NUMBER IS v_sum NUMBER(10,2);--返回指定部门的工资总和 BEGIN SELECT sum(sal),count(*) INTO v_sum,emp_count FROM EMP WHERE deptno=dept_no; RETURN v_sum; END get_salary;
创建函数

修改函数： 语法： ALTER FUNCTION [schema.]function_name COMPILE [DEBUG] DEBUG选项指示PL/SQL编译起在编译时生成PL/SQL调试程序所使用的符号代码。 用户执行ALTER FUNCTION时必须拥有ALTERANY PROCEDURE权限删除函数： 语法：DROP FUNCTION [schema.]function_name; 用户可以删除自己用户下的所用函数，删除其他用户的函数时必须具有DROP ANY PROCEDURE 系统权限。3.1.2 参数传递应用程序在调用函数时，可使用以下三种方法向函数传递参数：1． 位置表示法 语法：Argument_value1[,argument_value2….] 所传递的参数数量，数据类型和参数模式必须与定义时一致。
[td=558] [table=98%]
例：统计30号部门的工资和人数 DECLARE V_num INTEGER; V_sum NUMBER(8,2); BEGIN --用v_sum接收函数返回值 V_sum:=get_salary(30,v_num);--用v_num变量做函数输出参数 END;

2． 名称表示法：语法：argument=>parameter [,….] argument为形式参数名称，必须与函数定义时的形式参数名称相同，parameter为实际参数。在这种格式下，形式参数与实际参数一一对应，因此参数的顺序可以任意排列。
[td=594] [table=98%]
例： DECLARE V_num INTEGER; V_sum NUMBER（8，2）； BEGIN /*改变参数传入的顺序*/ V_sum:=get_salary(emp_count=>v_num,dept_nu=>30); END;

3． 混合表示法：同时使用位置表示法和名称表示法，位置表示法参数必须放在名称表示法参数的前面，即使用名称表示法后不能再使用位置表示法。

[td=23]
[img=546,436]file:///C:/Users/ADMINI~1/AppData/Local/Temp/msohtmlclip1/07/clip_image005.gif[/img]

3.2 存储过程过程与函数统称为PL/SQL子程序，他们是命名的PL/SQL块，存储在数据库中，并通过输入，输出参数或输入输出参数与其调用者交换信息。函数与过程唯一区别是函数总是返回参数，而过程不返回参数。语法： 创建过程： CREATE [OR REPLACE]PROCEDURE [schema.] proc_name ([argument[IN|OUT [NOCOPY]|INOUT[NOCOPY]] datatype[,…]]) [invoker_rights_clause] {IS|AS} PL/SQL block; 删除过程：DROP PROCEDURE[schema.]proc_name;关于本地过程和本地函数：本地过程和本地函数在PL/SQL块的声明部分定义，他的语法格式与存储函数和过程相同，但不使用CREATE [OR REPLACE]关键字。
[td=606] [table=98%]
DECLARE V_num INTEGER; V_sum NUMBER(8,2); --建立本地过程； PROCEDURE proc_demo1（ Dept_no NUMBER DEFAULT 10 , --输入参数，部门编号 Sal_sum OUT NUMBER,--输出参数，说明部门工资总和 Emp_count OUT INTEGER –输出函数，说明部门人数 ） IS BEGIN SELECT sum(sal),count(*) INTO sal_sum,emp_count FROM emp WHERE deptno = dept_no; END proc_demo1; BEGIN Proc_demo1(30,v_sum,v_num); Proc_demo1（sal_sum=>v_sum,emp_count=>v_num）; /*部门代号使用默认值10*/ END;
本地过程及其调用

3.3 包包是一组相关的过程，函数，变量，常量和游标等PL/SQL程序设计元素的组合，它具有面向对象程序设计语言的特点，是对这些PL/SQL程序设计元素的封装。包类似于C++中的类，其变量相当于类中的成员变量，过程和函数相当于方法。与类相同，包中的程序元素也分为共有元素和私有元素两种，这两种元素区别时允许的访问程序范围不同，即作用域不同。共有元素可以被包中的函数，过程访问，也可被包外的PL/SQL程序访问。私有元素只能被包内的函数合过程访问。在PL/SQL程序设计中，使用包不仅可以使程序设计模块化，对外隐藏包内所使用的信息，而且可以提高程序的执行效率。以为当程序首次调用包内函数或过程时，Oracle把整个包调进内存，当再次访问包内元素时，Oracle直接从内存中读取。[font="]3.3.1 创建包创建包分为创建包定义和创建包体两个过程,包定义部分声明包内数据类型,变量,常量,游标,子程序,异常处理等元素,这些元素为公有元素,而包体则是包定义部分的实现,他定义了包定义部分所声明的游标和子程序,包体内还可以声明私有元素.包定义: 语法:CREATE[OR REPLACE] PACKAGE package_name [AUTHID{CURRENT_USER|DEFINER}] {IS|AS} [公有数据类型定义] [公有游标定义] [公有变量，常量声明] [公有子程序声明] END [package_name]; 说明：AUTHID参数说明应用程序在调用函数时说使用的权限模式 CURRENT_USER：当前使用者权限 DEFINER: 定义者权限包体定义： 语法： CREATE [OR REPLACE]PACKAGE BODY package_name{IS|AS} [私有数据类型定义] [私有变量，常量声明] [公有游标定义] [公有子程序定义] [BEGIN 语句] END [package_name];[font="]3.3.2 删除包删除包:$SQL>dropPACKAGE 包名 $SQL>drop PACKAGE BODY 包名备注:包定义和包体应该同时修改,并保持一致. [font="]3.3.3 应用举例[img=570,1944]file:///C:/Users/ADMINI~1/AppData/Local/Temp/msohtmlclip1/07/clip_image006.gif[/img]

[font="]3.4 UTL_FILE包的使用Oracle通过包UTL_FILE操作文件，常用过程为文件控制：FOPEN FCLOSE IS_OPEN FCLOSE_ALL文件输出：PUT PUT_LINE NEW_LINE PUTF FFLUSH文件输入：GET_LINE相关参数： init.ora文件 UTL_FILE_DIR=目录

3.4.1 文件控制： 1． FUNCTION FOPEN(location IN VARCHAR2,filenameIN VARCHAR2,open_mode IN VARCHAR2) RETURN FILE_TYPE; FOPEN函数提供文件打开功能。 Location 为文件存放目录，制定的目录必须存在，FOPEN不会自己创建不存在的目录。如果模式是’w’模式，将覆盖已存在的文件。 Filename 为文件名称Open_mode 文件打开模式 ‘w’（写模式）,’r’（读模式）,’a’（附加模式）;返回值为打开文件的句柄。FOPEN引发的异常现象： UTL_FILE.INVALID_PATH 目录不存在或不可访问 UTL_FILE.INVALID_MODE UTL_FILE.INVALID_OPERATION UTL_FILE.INVALID_ERROR2． FCLOSE文件操作完毕后，使用FCLOSE关闭文件。PROCEDURE FCLOSE(file_handle IN OUTFILE_TYPE);参数说明：file_handle关闭文件的句柄FCLOSE引发的异常现象： UTL_FILE.WRITE_ERROR写操作时发生错误 UTL_FILE.INVALID_FILEHANDLE句柄非法3． IS_OPEN判断文件是否已经打开FUNCTION IS_OPEN(file_handle IN FILE_TYPE)RETURN BOOLEAN参数说明：输入句柄。返回值说明：文件打开时返回TRUE ,否则返回FALSE。4． FCLOSE_ALL关闭所有打开的文件PROCEDURE FCLOSE_ALL
3.4.2 文件输出：1． PUT 将指定的字符串输入到文件中，输入后不换行。PROCEDURE PUT(file_handle INFILE_TYPE,buffer IN VARCHAR2);2． NEW_LINE 向文件中写入行终结束符。相当于换行。PROCEDURE NEW_LINE(file_handle INFILE_TYPE,lines IN NATURAL:=1);参数说明：file_handle 文件句柄 lines 输入换行个数，默认为1。3． PUT_LINE 向文件写入一行。PROCEDURE PUT_LINE(file_handle IN FILE_TYPE,buffer IN VARCHAR2);4． PUTF 格式输出字符串，与C语言printf()相似。PROCEDURE PUTF(file_handle IN FILE_TYPE, Format IN VARCHAR2, Arg1 IN VARCHAR2 DEFAULT NULL, ……);参 参数说明 format 与C语言printf一致的输出格式。比如’%s’等5．FFLUSH :使用PUT PUT_LINE PUTF NEW_LINE输出的数据通常被写在缓冲区中，缓冲区满了才向文件些数据，FFLUSH强令缓冲区中的数据立即写入文件中。 PROCEDUREFFLUSH(file_handle IN FILE_TYPE);3.4.3 文件输入： GET_LINE 从文件中读入一行数据。不包括新行字符 PROCEDUREGET_LINE (file_handle IN FILE_TYPE, buffer OUT VARCHAR2) 参数说明：file_handle 文件句柄 buffer 缓冲区
[td=582] [table=98%]
DECLARE v_OutputFile UTL_FILE.FILE_TYPE; v_Name VARCHAR2(10):='scott'; BEGIN v_OutputFile:=UTL_FILE.FOPEN('/tmp','aaaa','w'); UTL_FILE.PUTF(v_OutputFile,'hi there !\n my name is %s,and i am a %s maj or.\n',v_Name,'Computer Sclence'); UTL_FILE.FCLOSE(v_OutputFile); END;
文件操作

3.4.4 应用举例

[font="]4.1 Wrapper应用对于Oracle存储过程，包，函数或其他PL/SQL块，是以明码的形式存储在数据库中，视图名为user_source,对于一些加密算法，业务逻辑等不希望对客户公开的源代码，我们可以使用wrapper工具，将明码转换为16进制码，然后再使用数据库进行编译。wrapper是一个操作系统可执行文件。在大多数系统中名字为wrap.语法： wrapiname=input_file [oname=output_file]iname是包含一条CREATE OR REPLACE语句的文件名字，该文件可为任意扩展名，缺省为.sql。如果指定了output_file,则output_file为输出文件名称。否则输入文件与输出文件名称一致，后缀为.plb. .plb文件与.SQL文件一样均可以在SQL*PLUS等Oracle工具中使用。
第四章 存储过程运行环境4.1 存储过程以及PL/SQL执行环境[font="]4.1.1 SQL*PLUS环境 1.在PL*SQL中操纵块 在SQL*PLUS中执行一条SQL语句时，应该使用分号结束该语句。分号不是该语句的一部分，他是语句终结符。当SQL*PLUS读到这个分号时，他便知道该语句已经结束，并把语句发送到数据库。另一方面，在PL/SQL块中，该分号是PL/SQL块的一个语法成分，而不再是结束符。当输入DECLARE或BEGIN关键字时，SQL*PLUS会检测正在运行的是PL/SQL块而不是SQL语句，这时需要使用斜线（/）来指明PL/SQL块结束。2． 替换变量替换变量为SQL语句和PL/SQL语句块提供了与SQL*PLUS交互的媒介，通过替换变量可以将参数值传入到PL/SQL块中。替换变量可以在SQL语句或PL/SQL语句块内的任何地方使用，包括替换数据库对象。

[td=47]
[td=522] [table=98%]
SQL> select ename,sal 2 from &table_name --替换变量 替换表名 3 where deptno=&dept_no --替换变量 替换部门代号 4 ； Enter value for table_name: emp --数据库提示 输入表名 emp old 2: from &table_name --原语句 new 2: from emp --新语句 Enter value for dept_no: 20 --提示输入部门代号 20 old 3: where deptno=&dept_no --原语句 new 3: where deptno=20 --新语句
ENAME SAL ---------- ---------- JONES 3475 SCOTT 3500 ADAMS 1600 FORD 3500 WUCHEN 3300
使用替换变量

3． 使用联编变量：SQL*PLUS以可以分配内存单元，分配的内存块可以在PL/SQL块或SQL语句内部使用。该内存单元是在块的外面分配的，因此可以连续被多个块使用，并且一个块执行完毕后可以打印该内存单元的内容，该内存单元被称为联编变量。
[td=594] [table=98%]
SQL> VARIABLE v_count NUMBER --定义联编变量 SQL> BEGIN 2 SELECT count(*) 3 INTO :v_count --使用联编变量时变量前应加冒号（：） 4 FROM emp; 5 END; 6 / PL/SQL procedure successfully completed. SQL> print v_count --打印联编变量 V_COUNT ---------- 48 SQL>
使用联编变量

4 使用EXECUTE调用内置存储过程语法：$>SQLPLUS scott/tiger; $SQL>EXECUTE 存储过程名称（参数列表）；5。使用文件PL/SQL块或存储过程编写完成后，通常是存放在文本文件中，我们在SQL*PLUS中使用以下语法在SQL*PLUS中执行文件。语法一：$SQL>@文件名语法二：$SQL>start 文件名 $SQL>/ [font="]4.1.2 Pro*c预编译环境1. 在Pro*C程序中静态调用存储过程语法： EXECSQL EXECUTE BEGIN 存储过程名称（参数列表）/PLSQL块 END; END-EXEC;参数列表中各参数应是在Pro*C程序中预先声明的宿主变量。

2. 关于指示器变量的使用在C语言中没有NULL的概念，在需要使用NULL时使用空字符串模拟字符串的NULL ,但无法模拟NULL整数。因此在预编译器中提供指示变量弥补该缺陷。指示器编量为一二位短整型变量。语法： EXEC　SQL BEGIN DECLARE SECTION; Short 指示器变量名称； 声明其他宿主变量； EXECSQL END DECLARE SECTION;
SQL语句 ：宿主变量[INDICATOR]：指示器变量指示器变量紧随宿主变量之后，INDICATOR参数指明其后为指示器变量，该参数可以省略，指示器变量前应加冒号（：）前缀。 指示器变量为0 ：宿主变量检索成功 指示器变量为-1 ：返回值为NULL 指示器变量为-2 ：宿主变量过短，无法完全存放返回值。
3. 编译器选项为了编译嵌套PL/SQL语句块的程序，应设置一些必须预编译参数。 SQLCHECK= SEMNTICS USERID= 用户名/口令
4.2 存储过程调试方法4.2.1 SQL*PLUS环境中显示错误$SQL>show errors showerrors显示存储过程编译时产生的错误$SQL>select * from user_errors; user_errors视图存储编译存储过程是的出错信息$SQL>select * from user_source; user_source视图存储存储过程的源代码。
4.2.2 插入测试表调试存储过程方法：编写调试包，将存储过程执行时的局部变量取值插入到临时表中。通过判断局部变量的取值查找错误

[td=558] [table=98%]
简单的debug包： CREATE OR REPLACE PACKAGE Debug AS /*调试过程，向临时表中插入值*/ PROCEDURE Debug(p_description IN VARCHAR2,p_value IN VARCHAR2); /*过程初始化*/ PROCEDURE Reset; END Debug

[td=558] [table=98%]
创建包体： CREATE OR REPLACE PACKAGE BODY Debug AS V_linecount NUMBER; PEOCEDURE Debug (p_Description IN VARCHAR2,p_Value IN VARCHAR2) IS BEGIN INSERT INTO debug_table(linecount,debug_str) VALUES(v_linecount,p_Descrition||’:’||p_Value); COMMIT WORK; V_linecount：=v_lincount +1; END Debug;
PROCEDURE Reset IS BEGIN V_linecount:=1; DELETE FROM debug_table; COMMIT WORK; END Reset; BEGIN /*包初始化*/ Reset; END Debug;

使用Debug包时，需先调用过程Debug.Reset对临时表初始化，然后调用过程Debug.Debug相临时表记录局部变量值。
4.2.3 DBMS_OUTPUT系统内置包 PL/SQL通过内置包DBMS_OUTPUT加入了输出功能。 DBMS_OUTPUT内置包中主要包括以下几类过程： 1. PUT类过程：输出变量值，不换行。 PROCEDURE PUT（a VARCHAR2） PROCEDURE PUT (a NUMBER) PROCEDURE PUT (a DATE) 2． PUT_LINE类过程：输出变量值，输出后换行。 PROCEDURE PUT_LINE(aVARCHAR2) PROCEDURE PUT_LINE(aNUMBER) PROCEDURE PUT_LINE(aDATE) PUT_LINE相当于先调用PUT然后调用 NEW_LINE过程3． NEW_LINE过程PROCEDURE NEW_LINE 在缓冲区中放入新行字符，表示一行的结束。4． GET_LINE:PROCEDURE GET_LINE(line OUT VARCHAR2,status OUT INTEGER);Line参数为包含缓冲区一行的字符串，status指明是否成功检索，一行最大长度为255字节。如检索成功status值为0，否则为1。
DBMS_OUTPUT包的使用： 在SQLPLUS中应设置serveroutput参数 语法：$SQL>SET SERVEROUTPUT ON SIZE buffer_size
[td=570] [table=98%]
例： 输出测试 SQL> SET SERVEROUTPUT ON SIZE 2000 SQL> BEGIN 2 DBMS_OUTPUT.PUT_LINE('打印测试'); 3 END; 4 / 打印测试 ---输出打印结果
PL/SQL procedure successfully completed. SQL>

附录一 sql*plus工具sql*plus是oracle数据库管理员和普通用户最常用的实用程序,他提供一个交互式SQL语句,PL/SQL语句块和sql*plus命令的编辑,编译和执行环境.sql*plus作为交互式管理,操作工具,使用命令行方式实现数据的操作,数据库的管理等工作.附录1.1 sql*plus启动和关闭语法一: sql*plus启动语法格式:$> sqlplus [][logon] [@[.ext] [..]] 选项 {- | -? | -s[ilent]} $>sqlplus - 显示sqlplus使用帮助 $>sqlplus -? 显示sqlplus 版本号和运行时间 $>sqlplus –s[ilent]启动过程中不显示任何信息,启动后不显示提示信息,经常在其他程序中调用sqlplus时使用. 选项{[/][@]|/|/nolog $sqlplus username/passwd 连接数据库用户username 使用passwd,如果不输入passwd,sqlplus会使用交互方式提示输入密码. $sqlplus mailto:username/passwd@connect_string]username/passwd@connect_string 连接远程数据库用户,connect_string为数据库网络服务名称. $sqlplus / 使用系统认证方式自动连接数据库,采用自动登陆方式.数据库用户名称应与系统用户名称对应.sco unix系统oracle7.3.2外部用户为ops$user_name. $sqlplus /nolog sqlplus登录时不与用户建立连接,使用用户时 $sql>connect user_name建立连接, 使用$sql > disconnect 断开连接 [@[.ext][…]]选项 @[.ext]为sqlplus登录成功后立即执行该文件,文件扩展名为.ext,默认扩展名为.sql. 为文件中使用的参数. 例1 查询文件selectdept.sql内容为 SELECT * FROM　dept WHERE deptno=&1; 执行$>sqlplus–s scott/tiger @selectdept 30 其中$>为系统提示符–s为sqlplus不显示提示信息 scott/tiger为用户名和密码 @为文件执行命令 selectdept为文件名称 30为&1使用的参数值.语法二:关闭sqlplus{exit|ouit} [success|failure|warning|n|variabe|:bindvariable] [commit|rollback] exit命令或quit命令正常退出sql*plus $sql>exit或$sql>quit $sql>exit commit 提交sqlplus所作操作 $sql>exit rollback 回退sqlplus所作操作附录1.2 sql*plus 环境设置 语法一 修改环境变量 def[ine] [variable]|[variable=text] $sql>def 显示已定义的环境变量 $sql>def _editor = vi 设置编辑器为vi $sql>def new_name = old_name 定义新环境变量,在sql语句中使用 例:$sql>def col(新变量名) = job(数据库表列名) $sql>select &col from emp与 $sql>select job from emp等价 语法二 释放环境变量 undef[ine] 变量名 例:$sql>undef col附录1.3 设置环境参数基本环境参数存储在数据库站点概要文件中,oracle8.1.5站点概要文件为$oracle_home/sqlplus/admin/glogin.sql用户设置参数存储在用户概要文件中,默认文件名为login.sql语法一:set 系统变量 变量值 $sql>set feed[back] n/off/on 执行查询语句时是否显示记录统计信息(如行数),n为查询记录数大于n行时统计信息,off不显示统计信息,on显示统计信息. $sql>set heading on/off 查询时是(on)否(off)打印列名 $sql>set lin[esize] n 每行显示的最多字符数,默认为80 $sql>set newp[age] n 设置每页首行前的空行数 $sql>set null 查询时返回的空值用name替换 $sql>set numf[ormat] format 设置数字显示格式: $99999 数字前加$符号,长度与9的个数相同 999999 数字前导0换成空格 000000 数字前导零保留 XXXX 十六进制,字母大写xxxx 十六进制,字母小写 99G999D99 G位显示逗号分隔符,D位显示小数点 L999D99 L显示本地货币符号 $sql>set num[width] 设置数字显示宽度 $sql>set pages[ize] 设置每页显示行数 $sql>set pau[se] 设置换页时是(on)否(off)暂停附录1.4 sqlplus命令的执行 $sql>SQL语句 ; 分号执行命令(;) sqlplus执行分号前的SQL语句 $sql>SQL语句 $sql> / /执行命令 $sql>SQL语句 $sql> r(un) run执行命令 $sql>;PL/SQL块 $sql> . .执行命令,PL/SQL块遇到.命令开始执行 $sql>start file_name 使用start命令执行外部SQL语句文件 $sql>@ file_name 使用@命令执行外部SQL语句文件附录1.5 sql*plus编辑命令 显示命令 $sql>L[ist]/L[ist] * 显示当前行内容 $sql>L n 显示第n行内容,并将第n行设为当前行 $sql>L n * 显示第n行到当前行 $sql>L m n 显示第m行到第n行 $sql>L last 显示最后一行添加命令 $sql>A(ppend) text 在当前行尾添加text修改命令 $sql>C(hange) /old/new 将当前行中old替换为new $sql>C(hange) /old/ 删除当前行中的old $sql>CL(ear) buff 清除缓冲区 $sql>del m(*) n(*) 删除行 $sql>I(nput) 在当前行后添加新行 $sql>I(nput) text 在当前行后添加文本text