Oracle数据库存储过程技术文档
前言
本文编写目的:
本文对ORACLE存储过程，存储函数，包作了一个概括性的介绍，以实例为驱动介绍了存储过程，存储函数，包的语法，数据类型以及程序开发编写的方法。通过对本文的学习，达到使用ORACLE存储过程进行基本编程的目的。
本文主要参考:
《新编,RACLE7入门教程》 电子工业出版社
《ORACLE8I数据库高级应用开发技术》人民邮电出版社
《ORACLE8 PL/SQL程序设计》机械工业出版社
本文面向对象:
对ORACLE有一定认识和经验的开发者和系统管理者。
本文中各例均使用Oracle数据库demo用户.
用户名:scott 用户口令:tiger
数据结构建立:
/*使用system用户及口令登录oracle数据库*/
$SQLPLUS system/passwd
/*建立scott用户口令为tiger*/
$SQL>create user scott identified by tiger;
/*给scott用户授权*/
$SQL>grant create session to scott;
$SQL>exit;
$SQLPLUS scott/tiger
$SQL>start $ORACLE_HOME/sqlplus/demo/demobld.sql
主要数据结构:
CREATE TABLE EMP CREATE TABLE BONUS
(EMPNO NUMBER(4) NOT NULL, (ENAME VARCHAR2(10),
ENAME VARCHAR2(10), JOB VARCHAR2(9),
JOB VARCHAR2(9), SAL NUMBER,
COMM NUMBER); MGR NUMBER(4),
HIREDATE DATE,
CREATE TABLE SALGRADE SAL NUMBER(7,2),
( GRADE NUMBER, COMM NUMBER(7,2),
LOSAL NUMBER, DEPTNO NUMBER(2)); CREATE TABLE DEPT HISAL NUMBER ); (DEPTNO NUMBER(2), CREATE TABLE DUMMY DNAME VARCHAR2(14),
( DUMMY NUMBER ); LOC VARCHAR2(13) );
第一章 oracle存储过程概述
Oracle存储过程(store procedure)作为PL/SQL语言的子程序,使用PL/SQL语言对数据处理逻辑,数据存储,数据操纵进行描述和封装,通过oracle其他工具(Pro*c&sqlplus等)对存储过程调用,实现相应功能.
Oracle存储过程在创建时经过数据库编译,作为数据库对象存储在数据库中,使用存储过程名称和输入输出参数实现存储过程描述的功能.
存储过程是由流控制和SQL语句书写的过程，这个过程经编译和优化后存储在数据库服务器中，使用时只要调用即可。在ORACLE中，若干个有联系的过程可以组合在一起构成程序包。
使用存储过程有以下的优点:
1. 存储过程的能力大大增强了SQL语言的功能和灵活
性。存储过程可以用流控制语句编写，有很强的灵活性，
可以完成复杂的判断和较复杂的运算.
2. 可保证数据的安全性和完整性。
3. 通过存储过程可以使没有权限的用户在控制之下间接
地存取数据库，从而保证数据的安全。
4. 通过存储过程可以使相关的动作在一起发生，从而可
以维护数据库的完整性。
5. 再运行存储过程前，数据库已对其进行了语法和句法
分析，并给出了优化执行
方案
气瓶 现场处置方案 .pdf气瓶 现场处置方案 .doc见习基地管理方案.doc关于群访事件的化解方案建筑工地扬尘治理专项方案下载
。这种已经编译好的过程
可极大地改善SQL语句的性能。由于执行SQL语句的
大部分工作已经完成，所以存储过程能以极快的速度执
行。
6. 可以降低网络的通信量。
7. 使体现企业规则的运算程序放入数据库服务器中，以
便集中控制。
当企业规则发生变化时在服务器中改变存储过程即可，无须修改任何应用程序。企业规则的特点是要经常变化，如果把体现企业规则的运算程序放入应用程序中，则当企业规则发生变化时，就需要修改应用程序工作量非常之大(修改、发行和安装应用程序)。如果把体现企业规则的运算放
入存储过程中，则当企业规则发生变化时，只要修改存储过程就可以了，应用程序无须任何变化.
Oracle存储函数(FUNCTION)作为特殊的存储过程,与C/C++语言函数相似,具备函数名,输入输出参数以及返回值. 存储过程和存储函数都是相对独立的实体.
Oracle包(Package)为了管理上的方便,把一些相关的程序结构如存储过程,存储函数,变量,游标等组织在一起,构成一个包.Oracle包具有面向对象程序设计语言的特点,是对PL/SQL程序设计元素的封装.包类似于C++和JAVA语言中的类,其中变量相当于类中的成员变量,存储过程和存储函数相当于类方法.包中的元素分为共有元素和私有元素,两种元素允许访问的程序范围不同.
1.1 存储过程基本结构(PROCEDURE) 1.1.1创建存储过程
CREATE [OR REPLACE] PROCEDURE 存储过程
名
(参数定义标)
IS/AS
变量定义
BEGIN
PL/SQL语句块
EXCEPTION
例外处理
END 存储过程名
定义说明:
1. 参数定义
表
关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf
:
存储过程可以有三类参数
IN 数据从调用环境传入存储过程
OUT 数据从存储过程传入调用环境
INOUT 数据可以传入或传出存储过程
参数使用原则:
参数类型可以为ORACLE允许的任意类型,也可为%TYPE(与其他某一变量类型一致)或%ROWTYPE(与数据库中某一对象表,游标等数据类型一致) 类型
指定参数时,不能指定长度
所有输出参数(OUT)只能出现在SELECT INTO语句或赋值语句中.
尽量减少IN参数个数.
2. 变量定义
变量类型可以为ORACLE允许的任意类型,也可
为%TYPE(与其他某一变量类型一致)或%ROWTYPE(与
数据库中某一对象表,游标等数据类型一致) 类型.
3. 例外处理
存储过程例外处理与PL/SQL错误处理一致,可按条件执
行相应的操作.
例1.1
本例实现为指定雇员号(emp)的雇员加工资(sal),数据源
为表emp
/*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
#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
1.2存储函数(FUNCTIONE) 存储函数是一类特殊的存储过程,与一般存储过程不同的是
存储函数必须返回一个值.
1.2.1 创建存储函数
CREATE [OR REPLACE] FUNCTION 存储函数名
RETUNR 返回值类型
IS/AS
变量声明
BEGIN
PLSQL语句块
EXCEPTION
例外处理
END 存储函数名
备注: 返回值类型不带长度
1.2.2 删除存储函数
$SQL>drop function 存储函数名 例1.3
从员工信息表(emp)中选择部门代号为v_empno员工的工
资
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
1.3 包(package)
1.3.1 包的基本结构
包中可以包含过程(procedure),函数(function),变量(variable) 游标(cursor),常量(constant),例外处理(exception). 包由两部分组成:包定义和包体
包定义:对包的公共元素如过程,函数,变量,常量,游标,例外情况等进行说明,可在包外独立使用这些公共元素.
包体部分包括包中使用的私有元素和包的公共元素的定义. 1.3.2 包的创建
1. 创建包定义
CREATE〔OR REPLACE〕PACKAGE package_name IS/AS
公共元素声明
END package_name;
2. 创建包体
CREATE〔OR REPLACE〕 PACKAGE BODY package_name
IS/AS
私用元素定义
公共元素定义
BEGIN
PLSQL语句
END package_name; 例 1.4
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;
创建包定义
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
1.3.3 调用包中元素
$SQL>EXECUTE 包名.元素名(参数列表);
例: $SQL>EXECUT emp_package.raise_sal(7654,100);
1.3.4 包的修改和删除
删除包:$SQL>drop PACKAGE 包名
$SQL>drop PACKAGE BODY 包名
备注:包定义和包体应该同时修改,并保持一致.
第二章 oracle存储过程基础――PL/SQL Oracle存储过程以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块由定义部分,执行部分,例外处理部分组成 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.
/*一个简单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;
END;
一个简单的PL/SQL块程序
执行步骤:
$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 没找到记录 在SQL语句中使用的变量名应与数据库字段名区分开.
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语句完
全一样,只是可以使用定义的变量和表达式
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 标记
2.1.3 PL/SQL流程控制
PL/SQL具有与高级语言类似的流程控制语句.PL/SQL主要控制语句有:
条件控制语句
循环控制语句
跳转控制语句
1. 条件控制语句:
IF_THEN_ELSE语句
语法: IF 条件 THEN
语句;
ELSE
语句;
END IF;
条件可为IS NULL或NOT IS NULL以及AND, OR, NOT,
逻辑运算符
例:将emp表中的雇员名为SMITH雇员的工资修改,如果工资大于$2000,则加$500,否则
加$1000.
DECLARE
v_ename emp.ename%TYPE:='SMITH';
v_addsal emp.sal%TYPE;
v_sal emp.sal%TYPE;
BEGIN
IF_THAN_ELSIF语句:
:IF 条件 THEN 语法
语句;
ELSIF 条件 THEN
语句;
[ELSIF 条件 THEN 语句;]
[ELSE 语句;]
END IF
根据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';
ELSIF v_job=’SALESMAN’ THEN v_addsal=500;
ELSIF v_job=’ANALYST’ THEN V_addsal=200;
ELSE
v_addsal=200;
END IF
UPDATE emp SET sal=sal+v_addsal
WHERE ename=’SMITH’;
END;
2. 循环控制语句: LOOP循环:
语法: LOOP
语句;
[EXIT [WHEN 条件]];
END LOOP;
例 给10号部门增加新雇员,只确定雇员代号,其他信息忽略.
DECLARE
v_empno emp.empno%TYPE:=8000; BEGIN
LOOP
INSERT INTO emp(deptno,empno)
VALUES(10,v_empno);
v_empno:=v_empno+100;
FOR 循环:
: FOR 计数器 IN [REVERSE] 下界…上界 LOOP 语法
语句;
END LOOP;
计数器用于控制循环次数的变量,无需在定义部分做出定义,系统隐含定义为整数,REVERSE表示计数器从上界到下界递减计数,下界定义初值,上界定义终值,下界应小于上界.对计数器不可作赋值操作.
例 同上例
DECLARE
v_deptno emp.deptno%TYPE:=10;
BEGIN
FOR i IN 1...10 LOOP
INSERT INTO emp(deptno,empno)
WHILE 循环
语法: WHILE 条件 LOOP
语句;
END LOOP;
例 同上例
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体内
跳转语句不能从循环体外跳到循环体内
例 同上例
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语句则会出错.因此,在查询语句返回多条记录或不知返回结果数目时,必须使用游标.
2.2.1 游标的概念
PL/SQL中游标的使用与Pro*C中游标使用相似,包括定义,打开,提取数据,关闭四个步骤.一般游标在定义，打开后使用循环语句逐条处理提取的数据。
一 定义游标
语法: CURSOR 游标名称 IS
SELECT 语句;
定义游标应写在PL/SQL语句的DECLARE变量定义部分
定义游标时SELECT语句中不可有INTO子语句
在SELECT语句中使用的变量必须在定义游标前定义
二 打开游标
语法:OPEN 游标名;
在BEGIN语句之后,可以打开游标,在打开游标之前,必须对游标所涉及到的变量赋值.
三 利用游标提取数据
语法: FETCH 游标名 INTO 变量1,变量2,…..
游标每次只能取到一条数据,同时游标指针下移,等待取下一条数据.该条语句变量列表应与定义游标时的参数列表一致
四 关闭游标
语法: CLOSE 游标名
关闭游标,释放资源,游标关闭后不能再提取数据. 2.2.2 游标的属性
游标的属性标示游标的运行状况.
%ISOPEN 布尔型 表示游标是否打开
%NOTFOUND 布尔型 描述最后一次FETCH的结果
%FOUND 布尔型 描述最后一次FETCH的结果,与NOTFOUND相反
%ROWCOUNT 数字型 描述当前取值的条数
例 查询10号部门所有雇员的姓名工资，并插入到临时表tmp中
tmp表结构为 t1 char(20),t2 number(10)
以下使用三种不同的循环方法实现该例:
例 查询10号部门所有雇员的姓名工资，并插入到临时表tmp中
DECLARE
v_deptno emp.deptno%TYPE:=10;
/*定义游标*/
CURSOR C1 IS
SELECT ename,sal FROM EMP
WHERE DEPTNO=v_deptno;
/*定义ROWTYPE型变量emp_rec 变量为与游标C1结构一致的结构型，即
ename,sal结构*/
emp_rec C1%ROWTYPE;
BEGIN
/*打开游标*/
OPEN C1;
/*循环开始*/
LOOP
/*提取数据到变量emp_rec结构中*/
FETCH C1 INTO emp_rec;
/*如果数据提取完毕，退出*/
EXIT WHEN C1%NOTFOUND;
INSERT INTO tmp
VALUES(emp_rec.ename,emp_rec.sal);
/*结束循环*/
END LOOP;
/*关闭游标*/
CLOSE C1;
COMMIT WORK;
END;
游标使用NOTFOUND属性
/*如果游标已经打开*/
IF C1%ISOPEN THEN
FETCH C1 INTO emp_rec;
/*如果游标未打开，先打开游标再提取数据*/ ELSE
OPEN C1;
FETCH C1 INTO emp_rec;
END IF;
游标使用ISOPEN属性
DECLARE
v_deptno emp.deptno%TYPE:=10;
CURSOR C1 IS
SELECT ename,sal FROM EMP
WHERE DEPTNO=v_deptno;
emp_rec C1%ROWTYPE;
BEGIN
OPEN C1;
FETCH C1 INTO emp_rec;
WHILE C1%FOUND LOOP
INSERT INTO tmp
VALUES(emp_rec.ename,emp_rec.sal);
FETCH C1 INTO emp_rec;
END LOOP;
CLOSE C1;
COMMIT WORK;
END;
游标使用FOUND属性
2.2.3 游标中FOR循环的使用
游标使用FOR循环可以简化游标的操作，使用FOR循环
时，系统隐含定义了一个数据类型为%ROWTYPE的变量为
循环计数器，此时在PL/SQL块中不用显式的打开，关闭游
标。
语法: FOR 组合变量名 IN 游标名 LOOP
语句;
END LOOP;
DECLARE
v_deptno emp.deptno%TYPE:=10;
CURSOR C1 IS
SELECT ename,sal FROM EMP
WHERE DEPTNO=v_deptno;
emp_rec C1%ROWTYPE;
BEGIN
FOR emp_rec IN C1 LOOP
INSERT INTO tmp
VALUES(emp_rec.ename,emp_rec.sal);
FETCH C1 INTO emp_rec;
END LOOP;
COMMIT WORK;
END;
游标使用FOR循环
2.2.4 带参数游标的使用方法 在定义游标时，可以加入参数，参数再游标中使用。
语法: DECLARE
CURSOR 游标名(参数列表) IS
SELECT 语句;
DECLARE
/*游标C1 带参数v_deptno*/
CURSOR C1(v_deptno emp.deptno%TYPE) IS
SELECT ename,sal FROM EMP
WHERE DEPTNO=v_deptno;
emp_rec C1%ROWTYPE;
BEGIN
/*参数值为10*/
FOR emp_rec IN C1(10) LOOP
INSERT INTO tmp
VALUES(emp_rec.ename,emp_rec.sal);
FETCH C1 INTO emp_rec;
END LOOP;
/*参数值为20*/
FOR emp_rec IN C1(20) LOOP
INSERT INTO tmp
VALUES(emp_rec.ename,emp_rec.sal);
FETCH C1 INTO emp_rec;
END LOOP;
COMMIT WORK;
END;
使用带参数游标
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|IN OUT] bind_argument]…];
其中动态语句串表示SQL语句或PL/SQL块文本，对于SQL语句不能使用语句结束符(;)，对于PL/SQL块文本，必须加结束符。INTO子句只能用于单行查询语句，将查询结果存储到指定变量variable或记录record变量中。USING子句使用bind_argument值替换动态语句串中的占位符。USING语句不能传递布尔型变量(TRUE FALSE NULL)。由于Oracle自动将所有未赋值的变量设置为NULL,因此在需要传递NULL变量时，使用未赋值变量即可。
例:DECLARE
no_initialization NUMBER;
BEGIN
EXECUTE IMMEDIATE ‘DELETE FROM emp WHERE comm=:x’
USING no_initialization;
END;
使用未赋值变量传递NULL
USING子句中，不能使用结合参数传递对象名称。
例:
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语句中结合变量默认参数模式为输入参数，当结合变量为输出参数或输入输出参数时应加OUT或IN OUT选项说明。
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 例外处理
例外处理是指程序在执行过程中的警告或错误的处理。
语法: DECLARE
声明语句块
BEGIN 例外发生 执行语句
EXCEPTION
例外处理 例外语句
END;
例外处理
EXCEPTION
WHEN 例外情况1[OR 例外情况2…] THEN
语句;
WHEN 例外情况 3[OR 例外情况4…] THEN
语句;
[WHEN OTHERS 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块内部错误
例 删除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 例外处理名称
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表中统计各部门人数以及工资总和。
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
第三章 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 [,….]])
RETURN datatype
[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
权限。
例:创建函数，查询指定部门的工资总和。
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时必须拥有ALTER ANY PROCEDURE权限
删除函数:
语法:DROP FUNCTION [schema.]function_name;
用户可以删除自己用户下的所用函数，删除其他用户的函数时必须具有DROP ANY PROCEDURE 系统权限。 3.1.2 参数传递
应用程序在调用函数时，可使用以下三种方法向函数传递参数:
1( 位置表示法
.] 语法:Argument_value1[,argument_value2…
所传递的参数数量，数据类型和参数模式必须与定义时一致。
例:统计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为实际参数。在这种格式下，形式参数与实际参数一一对应，因此参数的顺序可以任意排列。
例:
DECLARE
V_num INTEGER;
V_sum NUMBER(8，2);
BEGIN
/*改变参数传入的顺序*/
V_sum:=get_salary(emp_count=>v_num,dept_nu=>30);
END;
3( 混合表示法:
同时使用位置表示法和名称表示法，位置表示法参数必须
放在名称表示法参数的前面，即使用名称表示法后不能再
使用位置表示法。
例:
CREATE OR REPLACE FUNCTION demo_fun
(name VARCHAR2,age INTEGER,sex VARCHAR2)
RETURN VARCHAR2 AS
V_var VARCHAR2(32);
BEGIN
V_var:=name||’:’||TO_CHAR(age)||’岁‘||sex;
RETURN v_var;
END;
例:函数调用
DECLARE
Var VARCHAR(32);
BEGIN
Var:=demo_fun(‘user1’,30,sex=>’男’);
Var:=demo_fun(‘user2’,age=>40,sex=>’男’);
Var:=demo_fun(‘user3’,sex=>’女’,age=>20);
/*以上均正确*/
var:=demo_fun(‘user1’,name=>30,’男’);
/*以上调用错误*/
END;
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]关键字。
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直接从内存中读取。
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]; 3.3.2 删除包
删除包:$SQL>drop PACKAGE 包名
$SQL>drop PACKAGE BODY 包名
备注:包定义和包体应该同时修改,并保持一致.
3.3.3 应用举例
例:定义包定义部分
CREATE OR REPLACE PACKAGE demo_pack
AUTHID CURRENT_USER
IS
Deptrec dept%ROWTYPE;
V_sqlcode NUMBER;
V_sqlerr VARCHAR2(2048);
FUNCTION add_dept(dept_no NUMBER,dept_name VARCHAR2,location VARCHAR2)
RETURN NUMBER;
FUNCTION remove_dept(dept_no NUMBER)
RETURN NUMBER;
PROCEDURE query_dept(dept_no IN NUMBER); END demo_pack;
例 :定义包体
CREATE OR REPLACE PACKAGE BODY demo_pack IS
--声明包私有变量 flag INTEGER;
--声明包私有函数 FUNCTION check_dept(dept_no NUMBER)
RETURN INTEGER; --以下为公有函数
FUNCTION add_dept(dept_no NUMBER,dept_name VARCHAR2,location VARCHAR2)
RETURN NUMBER
IS
BEGIN
IF check_dept(dept_no) = 0 THEN
INSERT INTO dept
--接上页
EXCEPTION
WHEN OTHERS THEN
V_sqlcode:=SQLCODE; --捕获异常信息
V_sqlerr:=SQLERRM;
RETURN –1;
END add_dept;
FUNCTION remove_dept(dept_no NUMBER)
RETURN NUMBER
IS
BEGIN
V_sqlcode := 0;
V_sqlerr :=NULL;
IF check_dept(dept_no) = 1 THEN
例:接上页
END check_dept; BEGIN --包体初始化，对公有变量初始化
V_sqlcode : =NULL;
V_sqlerr : =NULL;
END demo_pack;
例:调用demo_pack包内函数对dept表进行插入，查询和修改操作，通过demo_pack包
中的记录变量deptrec显示查询到的信息。
DECLARE
Var NUMBER;
BEGIN
Var : = demo_pack.add_dept(90,’abc’,’def’);
--调用包demo_pack中add_dept函数向数据库表dept插入数据
IF var = -1 THEN
Dbms_output.put_line(demo_pack.v_sqlerr);
--使用函数dbms_output.put_line输出错误信息
ELSE
Demo_pack.query_dept(90);
--查询部门号为90信息
Dbms_output.put_line(demo_pack.deptrec.deptno||demo_pack.deptrec.dname||demo_pack.
deptrec.loc);
Var:=demo_pack.remove_dept(90);
--删除部门号为90的信息
IF var = -1 THEN
Dbms_output.put_line(demo_pack.v_sqlerr);
END If;
END IF;
END;
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,filename IN 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_ERROR
2( FCLOSE文件操作完毕后，使用FCLOSE关闭文件。
PROCEDURE FCLOSE(file_handle IN OUT
FILE_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 IN FILE_TYPE,buffer IN
VARCHAR2);
2( NEW_LINE 向文件中写入行终结束符。相当于换
行。
PROCEDURE NEW_LINE(file_handle IN
FILE_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强令缓冲区中的数据立即写入文件中。
PROCEDURE FFLUSH(file_handle IN FILE_TYPE);
3.4.3 文件输入:
GET_LINE 从文件中读入一行数据。不包括新行字符
PROCEDURE GET_LINE (file_handle IN FILE_TYPE,
buffer OUT VARCHAR2)
参数说明:file_handle 文件句柄
buffer 缓冲区
3.4.4 应用举例
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;
文件操作
4.1 Wrapper应用
对于Oracle存储过程，包，函数或其他PL/SQL块，是以明码的形式存储在数据库中，视图名为user_source,对于一些加密算法，业务逻辑等不希望对客户公开的源代码，我们可以使用wrapper工具，将明码转换为16进制码，然后再使用数据库进行编译。
wrapper是一个操作系统可执行文件。在大多数系统中名字为wrap.
语法:
wrap iname=input_file [oname=output_file]
iname是包含一条CREATE OR REPLACE语句的文件名字，该文件可为任意扩展名，缺省为.sql。如果指定了output_file,则output_file为输出文件名称。否则输入文件与输出文件名称一致，后缀为.plb.
.plb文件与.SQL文件一样均可以在SQL*PLUS等Oracle工具中使用。
第四章 存储过程运行环境
4.1 存储过程以及PL/SQL执行环境
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语句块内的任何地方使用，包括替换数据库对
象。
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语句内部使用。该内存单元是在块的
外面分配的，因此可以连续被多个块使用，并且一个块执
行完毕后可以打印该内存单元的内容，该内存单元被称为
联编变量。
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>/
4.1.2 Pro*c预编译环境
1. 在Pro*C程序中静态调用存储过程
语法:
EXEC SQL EXECUTE
BEGIN
存储过程名称(参数列表)/PLSQL块
END;
END-EXEC;
参数列表中各参数应是在Pro*C程序中预先声明的宿主变量。
2. 关于指示器变量的使用
在C语言中没有NULL的概念，在需要使用NULL时使用空字符串模拟字符串的NULL ,但无法模拟NULL整数。因此在预编译器中提供指示变量弥补该缺陷。
指示器编量为一二位短整型变量。
语法:
EXEC SQL BEGIN DECLARE SECTION;
Short 指示器变量名称;
声明其他宿主变量;
EXEC SQL END DECLARE SECTION;
:宿主变量[INDICATOR]:指示器变量 SQL语句
指示器变量紧随宿主变量之后，INDICATOR参数指明其后
为指示器变量，该参数可以省略，指示器变量前应加冒号(:)前缀。
指示器变量为0 :宿主变量检索成功
指示器变量为-1 :返回值为NULL
指示器变量为-2 :宿主变量过短，无法完全存放返回值。
3. 编译器选项
为了编译嵌套PL/SQL语句块的程序，应设置一些必须预编译参数。
SQLCHECK = SEMNTICS
USERID = 用户名/口令
4.2 存储过程调试方法
4.2.1 SQL*PLUS环境中显示错误
$SQL>show errors
show errors显示存储过程编译时产生的错误
$SQL>select * from user_errors;
user_errors视图存储编译存储过程是的出错信息
$SQL>select * from user_source;
user_source视图存储存储过程的源代码。
4.2.2 插入测试表调试存储过程
方法:编写调试包，将存储过程执行时的局部变量取值
插入到临时表中。通过判断局部变量的取值查找错误
简单的debug包:
CREATE OR REPLACE PACKAGE Debug AS
/*调试过程，向临时表中插入值*/
PROCEDURE Debug(p_description IN VARCHAR2,p_value IN VARCHAR2);
/*过程初始化*/
PROCEDURE Reset;
END Debug
创建包体:
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(a VARCHAR2)
PROCEDURE PUT_LINE(a NUMBER)
PROCEDURE PUT_LINE(a DATE)
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
例: 输出测试
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 [
浙公网安备 33021202002483