Oracle数据库存储过程技术

教程发布:风哥 教程分类:ITPUX技术网 更新日期:2022-02-12 浏览学习:1357

本文编写目的: 本文对ORACLE存储过程,存储函数,包作了一个概括性的介绍,以实例为驱动介绍了存储过程,存储函数,包的语法,数据类型以及程序开发编写的方法。通过对本文的学习,达到使用ORACLE存储过程进行基本编程的目的。 本文主要参考: 《新编ORACLE7入门教程》 电子工业出版社 《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 [

本文标签:
网站声明:本文由风哥整理发布,转载请保留此段声明,本站所有内容将不对其使用后果做任何承诺,请读者谨慎使用!
【上一篇】
【下一篇】