PL/SQL程序设计
第一章:PL/SQL概述:
什么是PL/SQL:
A。PL/SQL是 Procedure Language & Structured Query Language 的缩写。PL/SQL是对SQL语言存储过程语言的扩展。从ORACLE6以后,ORACLE的RDBMS附带了PL/SQL。它现在已经成为一种过程处理语言,简称PL/SQL。目前的PL/SQL包括两部分,一部分是数据库引擎部分;另一部分是可嵌入到许多产品(如C语言,JAVA语言等)工具中的独立引擎。可以将这两部分称为:数据库PL/SQL和工具PL/SQL。两者的编程非常相似。都具有编程结构、语法和逻辑机制。
B。PL /SQL是一种高级数据库程序设计语言,该语言专门用于在各种环境下对ORACLE数据库进行访问。由于该语言集成于数据库服务器中 ,所以PL/SQL代码可以对数据进行快速高效的处理。
PL/SQL的好处:
A。有利于客户/服务器环境应用的运行
对于客户/服务器环境来说,真正的瓶颈是网络上。无论网络多快,只要客户端与服务器进行大量的数据交换。应用运行的效率自然就回受到影响。如果使用PL/SQL进行编程,将这种具有大量数据处理的应用放在服务器端来执行。自然就省去了数据在网上的传输时间。
B。适合于客户环境
PL/SQL由于分为数据库PL/SQL部分和工具PL/SQL。对于客户端来说,PL/SQL可以嵌套到相应的工具中,客户端程序可以执行本地包含PL/SQL部分,也可以向服务发SQL命令或激活服务器端的PL/SQL程序运行。
第二章:PL/SQL块结构与组成元素:
A。PL/SQL块:
PL/SQL程序由三个块组成,即声明部分、执行部分、异常处理部分。结构如下:
DECLARE
/* 声明部分: 在此声明PL/SQL用到的变量,类型及游标,以及局部的存储过程和函数 */
BEGIN
/* 执行部分: 过程及SQL 语句 , 即程序的主要部分 */
EXCEPTION
/* 执行异常部分: 错误处理 */
END;
其中执行部分是必须的。
PL/SQL块可以分为三类:
1. 无名块:动态构造,只能执行一次。
2. 子程序:存储在数据库中的存储过程、函数及包等。当在数据库上建立好后可以在其它程序中调用它们。
3. 触发器:当数据库发生操作时,会触发一些事件,从而自动执行相应的程序。
B。标识符:
PL/SQL程序设计中的标识符定义与SQL 的标识符定义的要求相同。要求和限制有:
不能超过30字符(最好8个左右);
字母开头,不分大小写;
不能用’-‘(减号);
不能是SQL保留字。
提示:一般不要把变量名声明与表中字段名完全一样,这样可能得到不正确的结果.例如下面的例子将会删除所有的纪录。
DECLARE
ename varchar2(20) :=’KING’;
BEGIN
DELETE FROM emp WHERE ename=ename;
END;
C。声明变量
语法:Var_name type [CONSTANT][NOT NULL][:=value];
说明:赋值语句为“:=”;如有[CONSTANT][NOT NULL],变量一定要有一个初始值;规定没有初始化的变量为NULL。
D。记录类型RECORD
记录类型是把逻辑相关的数据作为一个单元存储起来,它必须包括至少一个标量型或RECORD 数据类型的成员,称作PL/SQL RECORD 的域(FIELD),其作用是存放互不相同但逻辑相关的信息。
定义记录类型语法如下:
TYPE record_name IS RECORD( /*其中TYPE,IS,RECORD为关键字,record_name为变量名称*/
field1 type [NOT NULL][:=expr1], /*每个等价的成员间用逗号分隔*/
field2 type [NOT NULL][:=expr2], /*如果一个字段限定NOT NULL,那么它必须拥有一个初始值*/
... /*所有没有初始化的字段都会初始为NULL
fieldn type [NOT NULL][:=exprn]);
例如:
DECLARE
TYPE test_record IS RECORD(
Code VARCHAR2(10),
Name VARCHAR2(30) NOT NULL :=’a book’);
V_book test_record; /*类似实例化对象*/
BEGIN
V_book.code :=’123’;
V_book.name :=’C++ Programming’;
DBMS_OUTPUT.PUT_LINE(v_book.code||v_book.name);
END;
E。使用%TYPE
定义一个变量,其数据类型与已经定义的某个数据变量的类型相同,或者与数据库表的某个列的数据类型相同,这时可以使用%TYPE。
使用%TYPE特性的优点在于:
1。所引用的数据库列的数据类型可以不必知道;
2。所引用的数据库列的数据类型可以实时改变。
例如:
F。使用%ROWTYPE
PL/SQL 提供%ROWTYPE操作符, 返回一个记录类型, 其数据类型和数据库表的数据结构相一致。
使用%ROWTYPE特性的优点在于:
1。所引用的数据库中列的个数和数据类型可以不必知道;
2。所引用的数据库中列的个数和数据类型可以实时改变。
例如:
DECLARE
v_empno emp.empno%TYPE :=&empno;
rec emp%ROWTYPE; /*rec的结构和表emp相同*/
BEGIN
SELECT * INTO rec FROM emp WHERE empno=v_empno;
DBMS_OUTPUT.PUT_LINE('姓名:'||rec.ename||'工资:'||rec.sal||'工作时间:'||rec.hiredate);
END;
G。数据库赋值
数据库赋值是通过 SELECT语句来完成的,每次执行 SELECT语句就赋值一次,一般要求被赋值的变量与SELECT中的列名要一一对应。
例如:
DECLARE
emp_id emp.empno%TYPE :=7788;
emp_name emp.ename%TYPE;
wages emp.sal%TYPE;
BEGIN
SELECT ename, NVL(sal,0) + NVL(comm,0) INTO emp_name, wages
/*NVL(a,b)函数说明:a!=null,return a; a==null,return b; a==null&&b==null,return null*/
FROM emp WHERE empno = emp_id;
Dbms_output.put_line(emp_name||’----‘||to_char(wages));
END;
提示:不能将SELECT语句中的列赋值给布尔变量。
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
H。可转换的类型赋值
1。CHAR 转换为 NUMBER:v_total := TO_NUMBER(‘100.0’) + sal;
2。NUMBER 转换为CHAR:v_comm := TO_CHAR(‘123.45’) || ’元’ ;
3。字符转换为日期:v_date := TO_DATE('2001.07.03','yyyy.mm.dd');
4。日期转换为字符:v_to_day := TO_CHAR(SYSDATE, 'yyyy.mm.dd hh24:mi:ss');
第三章:PL/SQL流程控制语句
A。IF语句
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
B。CASE语句:
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
C。WHILE语句
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
D。数字循环
格式:
FOR 循环计数器 IN [ REVERSE ] 下限 .. 上限 LOOP
要执行的语句;
END LOOP;
说明:
每循环一次,循环变量自动加1;使用关键字REVERSE,循环变量自动减1。数字必须是从小到大的顺序,而且必须是整数,不能是变量或表达式。可以使用EXIT 退出循环。
例子:
BEGIN
FOR int in 1..10 LOOP
DBMS_OUTPUT.PUT_LINE('int 的当前值为: '||int);
END LOOP;
END;
E。NULL语句
在语句块中加空语句,用于补充语句的完整性,提高程序的可读性。
例如:
IF boolean_expr THEN
...
ELSE
NULL;
END IF;
第四章:游标的使用
游标是一个指向上下文的句柄( handle)或指针。通过游标,PL/SQL可以控制上下文区和处理语句时上下文区会发生些什么事情。用于提取多行数据集。
A。声明:
格式:CURSOR cursor_name[(parameter[, parameter]…)] IS select_statement;,如:
1。普通申明:DELCARE CURSOR CURSOR_NAME IS select_statement /* CURSOR的内容必须是一条查询语句*/
2。带参数申明:DELCARE CURSOR c_stu(p_id student.ID%TYPE) IS SELECT * FROM student WHERE ID = p_id;
提示:在指定数据类型时,可以使用%TYPE;不能使用长度约束。如NUMBER(4)、CHAR(10) 等都是错误的。
B。打开游标:
就是执行游标所对应的SELECT 语句,将其查询结果放入工作区,并且指针指向工作区的首部,标识游标结果集合。相当于执行select语句,且把执行结果存入CURSOR。如果游标查询语句中带有FOR UPDATE选项,OPEN 语句还将锁定数据库表中游标结果集合对应的数据行。
格式:OPEN cursor_name[value[,value]....];PL/SQL 程序不能用OPEN 语句重复打开一个游标。
C。提取游标数据:
检索结果集合中的数据行,放入指定的输出变量中,每FETCH一次取一条记录。
格式:FETCH cursor_name INTO {var_list | record_var }; //var_list变量的数量、类型、顺序要和Table中字段一致。
D。关闭游标:
格式:CLOSE Cursor_name;
说明:
1. 游标使用后应该关闭;关闭后的游标可以使用OPEN 语句重新打开。
2. 关闭后的游标不能FETCH和再次CLOSE。
3. 关闭游标相当于将内存中CURSOR的内容清空。
E。FETCH的几种循环
1。
LOOP
FETCH cursor INTO ...
EXIT WHEN cursor%NOTFOUND;
END LOOP;
2。
WHILE cursor%FOUND LOOP
FETCH cursor INTO ...
END LOOP;
3。
FOR var IN cursor LOOP
FETCH cursor INTO...
END LOOP;
F。隐式游标属性
%FOUND 布尔型属性,当最近一次读记录时成功返回,则值为true;
%NOTFOUND 布尔型属性,与%found相反;
%ROWCOUNT 数字型属性, 返回已从游标中读取得记录数;
%ISOPEN 布尔型属性, 判断游标是否是打开状态。
G:游标修改和删除操作
为了对正在处理(查询)的行不被另外的用户改动,ORACLE 提供一个 FOR UPDATE 子句来对所选择的行进行锁住。该需求迫使ORACLE锁定游标结果集合的行,可以防止其他事务处理更新或删除相同的行,直到您的事务处理提交或回退为止。
SELECT...FROM...FOR UPDATE [OF column[, column]...][NOWAIT]
如果另一个会话已对活动集中的行加了锁,那么SELECT FOR UPDATE操作一直等待到其它的会话释放这些锁后才继续自己的操作,对于这种情况,当加上NOWAIT子句时,如果这些行真的被另一个会话锁定,则OPEN立即返回并给出提示:
ORA-0054 :resource busy and acquire with nowait specified.
例子1:
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
例子2:给工资低于1200 的员工增加工资50。
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
例子3:从EMP表中查询某部门的员工情况,将其工资最低定为 1500。
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
第五章:异常错误处理
异常处理部分一般放在 PL/SQL 程序体的后半部,结构为:
EXCEPTION
WHEN first_exception THEN <code to handle first exception >
WHEN second_exception THEN <code to handle second exception >
WHEN OTHERS THEN <code to handle others exception >
END;
异常处理可以按任意次序排列,但 OTHERS 必须放在最后。
A。预定义的异常处理
ORACLE预定义的异常情况大约有24个。对这种异常情况的处理,无需在程序中定义,由ORACLE自动将其引发。
具体异常的名称:No_data_found,Timeout-on-resource,Program-error等等。
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('数据库中没有满足条件的数据');
WHEN Invalid-CURSOR THEN
DBMS_OUTPUT.PUT_LINE('试图使用一个无效的游标');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('发生其它错误!');
END;
B。非预定义的异常处理
即其他标准的ORACLE错误。对这种异常情况的处理,需要用户在程序中定义,然后将其定义好的异常情况用EXCEPTION_INIT语句与标准的ORACLE错误联系起来,由ORACLE自动将其引发(变相的预定义异常)。
C。用户自定义的异常处理
DECLARE
v_empno emp.empno%TYPE :=&empno;
no_result EXCEPTION; /*自定义异常*/
BEGIN
UPDATE emp SET sal=sal+100 WHERE empno=v_empno;
IF SQL%NOTFOUND THEN
RAISE no_result; /*确定异常*/
END IF;
EXCEPTION
WHEN no_result THEN
DBMS_OUTPUT.PUT_LINE('更新语句失败!');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('发生其它错误!');
END;
第六章:存储函数和过程
过程和函数的唯一区别是函数总向调用者返回数据,而过程则不返回数据。没有返回语句的函数将是一个错误。
在PL/SQL 程序中还可以在块内建立本地函数和过程,这些函数和过程不存储在数据库中,但可以在创建它们的PL/SQL 程序中被重复调用。本地函数和过程在PL/SQL 块的声明部分定义,它们的语法格式与存储函数和过程相同,但不能使用CREATE OR REPLACE 关键字。
A。建立内嵌函数
获取某部门的工资总和:
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
B。内嵌函数的调用
计算某部门的工资总和:
DECLARE
v_num NUMBER;
v_sum NUMBER;
BEGIN
v_sum :=get_salary(30, v_num); /*函数out v_num,返回 v_sum*/
DBMS_OUTPUT.PUT_LINE(’30号部门工资总和:’||v_sum||’,人数:’||v_num);
END;
1。位置表示法:以上的参数传递称方式为位置表示法,要求参数顺序一定。
2。名称表示法:没有要求参数传递的顺序,如:
v_sum :=get_salary(emp_count => v_num, dept_no => 30);
3。混合表示法:位置表示法所传递的参数必须放在名称表示法所传递的参数前面,即只要其中有一个参数使用名称表示法,其后所有的参数 都必须使用名称表示法。(这里用名称表示法传递的参数当然也不要求顺序)
C。函数参数默认值
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
D。创建存储过程:
1。无参:
CREATE table logtable (userid VARCHAR2(10), logdate date);
CREATE OR REPLACE PROCEDURE logexecution IS
BEGIN
INSERT INTO logtable (userid, logdate) VALUES (USER, SYSDATE);
END;
2。带参。计算指定部门的工资总和,并统计其中的职工数量:
CREATE OR REPLACE PROCEDURE proc_demo(
Dept_no NUMBER DEFAULT 10,
sal_sum OUT NUMBER,
emp_count OUT NUMBER)
IS
BEGIN
SELECT SUM(sal), COUNT(*) INTO sal_sum, emp_count /*把值存进两个输出参数*/
FROM emp WHERE deptno=dept_no;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('你需要的数据不存在!');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('发生其它错误!');
END proc_demo;
E。调用存储过程:
1。无参:
EXEC[UTE] Procedure_name( parameter1, parameter2...);
2。带参:
DECLARE
V_num NUMBER;
V_sum NUMBER(8, 2);
BEGIN
Proc_demo(30, v_sum, v_num);
DBMS_OUTPUT.PUT_LINE('30号部门工资总和:'||v_sum||’,人数:’||v_num);
Proc_demo(sal_sum => v_sum, emp_count => v_num);
DBMS_OUTPUT.PUT_LINE('10号部门工资总和:'||v_sum||’,人数:’||v_num);
END;
F。删除过程、函数
1.删除过程:DROP PROCEDURE [user.]Procudure_name;
2.删除函数:DROP FUNCTION [user.]Function_name;
第七章:包
A。包的概述:
1。包是一组相关过程、函数、变量、常量和游标等PL/SQL程序设计元素的组合,它具有面向对象程序设计语言的特点,是对这些PL/SQL 程序设计元素的封装。包类似于C++和JAVA语言中的类,其中变量相当于类中的成员变量,过程和函数相当于类方法。把相关的模块归类成为包,可使开发人员利用面向对象的方法进行存储过程的开发,从而提高系统性能。
2。与类相同,包中的程序元素也分为公用元素和私用元素两种,这两种元素的区别是他们允许访问的程序范围不同,即它们的作用域不同。公用元素不仅可以被包中的函数、过程所调用,也可以被包外的PL/SQL程序访问,而私有元素只能被包内的函数和过程序所访问。
3。在PL/SQL程序设计中,使用包不仅可以使程序设计模块化,对外隐藏包内所使用的信息(通过使用私用变量),而写可以提高程序的执行效率。因为,当程序首次调用包内函数或过程时,ORACLE将整个包调入内存,当再次访问包内元素时,ORACLE直接从内存中读取,而不需要进行磁盘I/O操作,从而使程序执行效率得到提高。
B。包的组成:
一个包由两个分开的部分组成:
包定义(PACKAGE):声明包内数据类型、变量、常量、游标、子程序和异常错误处理等元素,这些元素为包的公有元素。
包主体(PACKAGE BODY):是包定义部分的具体实现。它定义了包定义部分所声明的游标和子程序,在包主体中还可以声明包的私有元素。
包定义和包主体分开编译,并作为两部分分开的对象存放在数据库字典中,详见数据字典user_source, all_source, dba_source.
C。创建包定义:
CREATE [OR REPLACE] PACKAGE package_name
[AUTHID {CURRENT_USER | DEFINER}]
{IS | AS}
[公有数据类型定义[公有数据类型定义]…]
[公有游标声明[公有游标声明]…]
[公有变量、常量声明[公有变量、常量声明]…]
[公有子程序声明[公有子程序声明]…]
END [package_name];
其中:AUTHID CURRENT_USER和AUTHID DEFINER选项说明应用程序在调用函数时所使用的权限模式。
D。创建包主体:
CREATE [OR REPLACE] PACKAGE BODY package_name
{IS | AS}
[私有数据类型定义[私有数据类型定义]…]
[私有变量、常量声明[私有变量、常量声明]…]
[私有子程序声明和定义[私有子程序声明和定义]…]
[公有游标定义[公有游标定义]…]
[公有子程序定义[公有子程序定义]…]
BEGIN
PL/SQL 语句
END [package_name];
其中:在包主体定义公有程序时,它们必须与包定义中所声明子程序的格式完全一致。
E。包的调用、删除和重载:
1。对包内共有元素的调用格式为:包名.元素名称。
2。删除包:DROP PACKAGE [BODY] [user.]package_name;
3。PL/SQL 允许对包内子程序和本地子程序进行重载。所谓重载时指两个或多个子程序有相同的名称,但拥有不同的参数变量、参数顺序或参数数据类型。
F。例子:
1。创建的包为demo_pack, 该包中包含一个记录变量DeptRec、两个函数和一个过程。
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
2。包主体的创建方法,它实现上面所声明的包定义,并在包主体中声明一个私有变量flag和一个私有函数check_dept,由于在add_dept和re move_dept等函数中需要调用check_dpet函数,所以,在定义check_dept 函数之前首先对该函数进行声明,这种声明方法称作前向声明。
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
3。调用demo_pack包内函数对dept表进行插入、查询和修改操作,并通过demo_pack包中的记录变量DeptRec 显示所查询到的数据库信息:
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
第八章:触发器
A。概述:
1。触发器在数据库里以独立的对象存储,它与存储过程不同的是,存储过程通过其它程序来启动运行或直接启动运行,而触发器是由一个事件来启动运行。即触发器是当某个事件发生时自动地隐式运行。并且,触发器不能接收参数。
2。ORACLE事件指的是对数据库的表进行的INSERT、UPDATE及DELETE操作或对视图进行类似的操作。ORACLE将触发器的功能扩展到了触发ORACLE,如数据库的启动与关闭等。
B。触发器组成:
触发事件:即在何种情况下触发TRIGGER; 例如:INSERT, UPDATE, DELETE。
触发时间:即该TRIGGER 是在触发事件发生之前(BEFORE)还是之后(AFTER)触发,也就是触发事件和该TRIGGER 的操作顺序。
触发器本身:即该TRIGGER 被触发之后的目的和意图,正是触发器本身要做的事情。 例如:PL/SQL 块。
触发频率:说明触发器内定义的动作被执行的次数。即语句级(STATEMENT)触发器和行级(ROW)触发器。
1。语句级(STATEMENT)触发器:是指当某触发事件发生时,该触发器只执行一次;
2。行级(ROW)触发器:是指当某触发事件发生时,对受到该操作影响的每一行数据,触发器都单独执行一次。
C。创建触发器:
格式:
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF}
{INSERT | DELETE | UPDATE [OF column [, column …]]}
ON {[schema.] table_name | [schema.] view_name}
[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]
[FOR EACH ROW ]
[WHEN condition]
trigger_body;
说明:
1。BEFORE 和AFTER指出触发器的触发时序分别为前触发和后触发方式,前触发是在执行触发事件之前触发当前所创建的触发器,后触发是在执行触发事件之后触发当前所创建的触发器。
2。INSTEAD OF 选项使ORACLE激活触发器,而不执行触发事件。只能对视图和对象视图建立INSTEAD OF触发器,而不能对表、模式和数据库建立INSTEAD OF 触发器。
3。FOR EACH ROW选项说明触发器为行触发器。行触发器和语句触发器的区别表现在:行触发器要求当一个DML语句操走影响数据库中的多行数据时,对于其中的每个数据行,只要它们符合触发约束条件,均激活一次触发器;而语句触发器将整个语句操作作为触发事件,当它符合约束条件时,激活一次触发器。当省略FOR EACH ROW 选项时,BEFORE 和AFTER 触发器为语句触发器,而INSTEAD OF 触发器则为行触发器。
4。WHEN 子句指定的触发约束条件只能用在BEFORE 和AFTER 行触发器中,不能用在INSTEAD OF 行触发器和其它类型的触发器中。
D。触发器的限制:
1。触发器中不能使用数据库事务控制语句 COMMIT; ROLLBACK, SVAEPOINT 语句;
2。由触发器所调用的过程或函数也不能使用数据库事务控制语句;
3。触发器中不能使用LONG, LONG RAW 类型;
4。触发器的主体不可以读取或修改任何变化表(被DML语句正在修改的表);
5。触发器的主体不可以读取或修改限制表(带有约束的表)的主键、唯一值、外键列。
问题:当触发器被触发时,有时要使用被插入、更新或删除的记录中的列值,有时要使用操作前、 后列的值,如何区分。
实现:
:new 修饰符访问操作完成后列的值
:old 修饰符访问操作完成前列的值
E。例子:
1。建立一个触发器, 当职工表 emp 表被删除一条记录时,把被删除记录写到职工表删除日志表中去
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
2。创建Instead_of触发器
问题:
CREATE OR REPLACE VIEW emp_view AS
SELECT deptno, count(*) total_employeer, sum(sal) total_salary
FROM emp GROUP BY deptno;
/*直接操作会引起异常*/
DELETE FROM emp_view WHERE deptno=10; /*ORA-01732: 此视图的数据操纵操作非法*/
实现:
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)