下载文档原格式
第一章:PL/SQL概述一、什么就是PL/SQLORACLE公司发明PL/SQL为了克服SQL语言自身得一些缺陷并为那些要在ORACLE中运行核心业务提供一种完整得解决方案。
PL/SQL具有以下特点●就是一种高度结构化、易读易理解得语言●就是一种标准得、可移植得ORACLE语言,实现了编写一次到处运行●就是一种嵌入式语言,必须在特定得宿主环境内执行,不能编写一个PL/SQL可执行程序●就是一种高性能得、高集成度得数据库语言,如果您使用VB、DELPHI等进行数据库开发,最终您会发现使用PL/SQL将比其她任何语言都跟轻松高效二、改进得执行授权与事物一致性数据库与SQL语言让我们可以紧密地控制对任何个别数据库表得访问与修改,例如可以通过GRANT命令确保只有特定得角色与用户可以对指定得表执行更新操作,另一方面GRANT命令不能确保用户或者应用执行得更新操作能够正确执行,换句话说,数据库不能确保跨越多个表得事物一致性,在业务事务中涉及多个表就是很常见得。
PL/SQL实现紧密地控制与管理能力就是通过执行授权。
我们只就是授权执行某个过程,后者控制对底层数据结构得访问。
从ORACLE8I开始,PL/SQL提供了AUTHID子句,我们可以使用之前描述得定义者权限模型,也可以使用调用者权限模型。
三、PL/SQL结构:SQL整合DeclareI_book_count int;BeginSelect count(*) into i_book_count from books where author like ‘12’;Dbms_output、put_line(‘123’);--改名Update books set author = ‘31’;End;Declare:声明单元Begin:执行单元--:注释出现错误PL/SQL提供了强大得错误处理机制Procedure check_account(account_id_in in accounts、id%type)IsI_balance_remaining number;I_balance_below_minimum exception;I_account_name account、name%type;BeginSelect name into i_account_name from accounts where id = account_id_in;I_balance_remaining := account_balance(account_id_in); If i_balance_remaining < 1000ThenRaise i_balance_below_minimum;End if;ExceptionWhen no_data_foundThen log_error(…、);When i_balance_below_minumumThen log_error(…、);Raise;End;第二章:语言基础2、1、PL/SQL块结构块:最小得有意义得代码单元,一个块就就是一组代码,这个块给出了执行边界,也为变量声明与异常处理提供了作用范围,PL/SQL允许我们创建匿名块与命名块,命名块可以使包、过程、函数、触发器或者对象。
PL/SQL系列培训之一：基本语法第一讲分四个部分：SQL基本语法、基本逻辑控制、事务和异常SQL基本语法SQL分类数据定义语言DDL：CREATE/DROP/ALTER/COMMENT/TRUNCATE等数据操作语言DML：UPDA TE/DELETE/INSERT/CALL/SELECT FOR UPDA TE等数据控制语言DCL：GRANT/REVOKE/COMMIT/ROLLBACK等SELECT FOR UPDATESELECT FOR UPDA TE锁住某个表的相关行。
如果是多表查询：SELECT FOR UPDA TE of table.column----用来表连接时锁住其中一个表的相关行；----否则将锁住所有表（包括from子查询，不包括条件子查询）的相关行举例：SELECT t1.id, FROM test_table1 t1, test_table2 t2WHERE t1.id = t2.idFOR UPDA TE of t1.idLOCK TABLELOCK TABLE table_name IN lockmode MODE (NOWAIT)LOCK TABLE tanjie21 IN SHARE UPDATE MODE; --同下LOCK TABLE tanjie21 IN ROW SHARE MODE; --行共享锁，其他会话无法获得排他锁LOCK TABLE tanjie22 IN ROW EXCLUSIVE MODE; --行排他锁，禁止SHARE锁定--UPDATE/DELETE/INSERT自动获得LOCK TABLE tanjie23 IN SHARE MODE; --表共享锁LOCK TABLE tanjie24 IN EXCLUSIVE MODE; --表排他锁LOCK TABLE tanjie25 IN SHARE ROW EXCLUSIVE MODE; --表级共享行级排他W AIT/NOW AITNOWAIT --报ora-00054WAIT integer(秒) --报ora-30006EXECUTE IMMEDIATE处理动态SQL的两种方式：（1）EXECUTE IMMEDIA TE（2）OPEN-FOR, FETCH, and CLOSE性能优化（以后再讲）：BULK EXECUTE IMMEDIA TEEXECUTE IMMEDIA TE的完整语法：EXECUTE IMMEDIATE dynamic_SQL_string[INTO defined_variable1, defined_variable2, ...][USING [IN | OUT | IN OUT] bind_argument1, bind_argument2, ...] [RETURNING INTO | RETURN bind_argument1, bind_argument2, ...]使用INTO子句确定返回的是单行查询结果（与SELECT INTO类似）SUB QUERY标准子查询：和主查询没有直接联系，在ORACLE中首先执行且只执行一次关联子查询：在执行过程中需要与主查询发生联系，如子查询的条件依赖于主查询传递的条件。
PLSQL培训一、PLSQL简介PLSQL是ORACLE公司对SQL的一个扩展，也就相当于微软所扩展的TSQL一样。
它是一种过程化的语言，包括一整套的数据类型、条件结构、循环结构和异常处理结构，PLSQL可以执行SQL语句，SQL语句中也可以使用PLSQL函数。
二、PLSQL的数据类型只列出我们使用的VARCHAR2:可变长度字符串；注意：VARCHAR2 定义的长度为字节长度INT(INTEGER):整数；NUMERIC(M,N):小数;DATE:日期；BOOLEAN:布尔数。
Type:记录类型。
三、PLSQL DEV的使用。
四、PLSQL重要的一个组成：块DECLARE …;BEGIN…END;DECLARE :为声明部分，主要是声明这个块中用的变量，例：DECLARE V_CNT INT;该示例声明(或者说定义)了一个V_CNT的整形变量，与TSQL变量区别是变量名无需以@开头。
给变量赋值：V_CNT := 1;注1：赋值时候可以像程序语言一样直接赋值，赋值符号为:=;注2：在声明时候可以赋默认值，有如下两种方式：DECLARE V_CNT1 INT:=1; V_CNT2 INT DEFAULT2;注 3：另外一种赋值方式(如果是对一个变量直接赋一个静态值不要采取这种方式) DECLARE V_CNT INT;BEGINSELECT COUNT(*)INTO V_CNT FROM USER_TABLES;DBMS_OUTPUT.PUT_LINE(V_CNT);END;块中可以包含子块，类似于在程序语言中的一个函数体内部嵌入一个子函数体，而在子块中可以定义和父块中相同的变量名，在此，变量作用域与其他编程语言类似，子域中的变量覆盖父域中的变量。
块也可以加上名称，变为有名块。
BEGIN<<BLOCK1>>DECLARE V_CNT INT;BEGINV_CNT:=1;BEGIN <<BLOCK2>>DECLARE V_CNT INT;BEGINDBMS_OUTPUT.PUT_LINE(V_CNT);V_CNT:= 2;END;END;DBMS_OUTPUT.PUT_LINE(V_CNT);END;END;注：红色部分为子块，若需要引用父块中的变量，可以按如下方式引用父块名.变量名存储过程，函数都可以理解为一个指定了名字的块。
上机4PL/SQL语言基础上机内容1. 程序结构2. 流程控制3. 异常处理4. 开发动态SQL上机目标1. 掌握PL/SQL程序结构2. 掌握PL/SQL流程控制3. 掌握PL/SQL异常处理4. 掌握PL/SQL中开发动态SQL的方法阶段1指导部分训练内容PL/SQL块程序结构中的数据类型需求说明在VALUES子句中使用记录变量插入数据是Oracle 9i新增的特征。
在VALUES子句中使用记录变量插入数据时，列的顺序、个数、类型必须与记录成员的顺序、个数、类型完全匹配，如图2.4.1所示。
在VALUES子句中使用记录变量图2.4.1 在VALUES子句中使用记录变量另外，从Oracle 9i开始，在SET子句中不仅可以使用记录成员，而且还可以直接使用记录变量。
当在SET子句中使用记录变量更新数据时，列的顺序、个数、类型必须与记录成员的顺序、个数、类型完全匹配。
现要求实现：由用户输入部门编号，通过在SET子句中使用记录变量的方式来修改指定部门信息。
实现思路定义一个关于DEPT部门表的%ROWTYPE变量，让用户通过替换变量给其中的deptno部门编号赋值，通过UPDATE语句进行更新，如图2.4.2所示。
注意使用ROW关键字图2.4.2 在SET子句中使用记录变量练习部分（一）需求说明索引表（TABLE）类型不仅可以在PL/SQL中直接使用，也可以作为表列的数据类型使用。
在表列中使用索引表类型时，必须首先使用CREATE TYPE命令创建索引表类型。
需要注意的是，当使用索引表类型作为列的数据类型时，必须为索引表列指定专用的存储表。
例如，需要存储一个会员（TB_VIPInfo）信息时，需要记录该会员的联系方式，但该会员的联系方式可能有多个，因此可以考虑在记录会员联系方式的列上使用索引表。
创建一个关于会员信息表，并使用索引表类型作为该表中的某列，如图2.4.3所示。
图2.4.3 在表列中使用索引表要求：（1）为索引表列插入数据。
PL/SQL程序设计目录第一章PL/SQL 程序设计简介 (4)§1.2SQL与PL/SQL (4)§1.2.1 什么是PL/SQL? (4)§1.2.1 PL/SQL的好处 (4)§1.2.2 PL/SQL 可用的SQL语句 (5)§1.3运行PL/SQL程序 (5)第二章PL/SQL块结构和组成元素 (6)§2.1PL/SQL块 (6)§2.2PL/SQL结构 (6)§2.3标识符 (6)§2.4PL/SQL变量类型 (7)§2.4.1 变量类型 (7)§2.4.2 复合类型 (9)§2.4.3 使用%ROWTYPE (11)§2.4.4 LOB类型* (11)§2.4.5 Bind 变量 (12)§2.4.6 INDEX BY TABLES (12)§2.4.7 数据类型的转换* (14)§2.5运算符和表达式(数据定义) (14)§2.5.1 关系运算符 (14)§2.5.2 一般运算符 (14)§2.5.3 逻辑运算符 (14)§2.6变量赋值 (15)§2.6.1 字符及数字运算特点 (15)§2.6.2 BOOLEAN 赋值 (15)§2.6.3 数据库赋值 (15)§2.6.4 可转换的类型赋值 (16)§2.7变量作用范围及可见性 (16)§2.8注释 (17)§2.9简单例子 (18)§2.9.1 简单数据插入例子 (18)§2.9.2 简单数据删除例子 (18)第三章PL/SQL流程控制语句 (19)§3.1条件语句 (19)§3.2CASE表达式 (20)§3.3循环 (20)§3.3标号和GOTO (22)§3.4NULL语句 (23)第四章游标的使用 (24)§4.1游标概念 (24)§4.1.1 处理显式游标 (24)§4.1.2 处理隐式游标 (27)§4.1.3 游标修改和删除操作 (28)第五章异常错误处理 (30)§5.1异常处理概念 (30)§5.1.1 预定义的异常处理 (30)§5.1.2 非预定义的异常处理 (31)§5.1.3 用户自定义的异常处理 (32)§5.1.4 用户定义的异常处理 (33)§5.2异常错误传播 (35)§5.2.1 在执行部分引发异常错误 (35)§5.2.2 在声明部分引发异常错误 (35)§5.3异常错误处理编程 (36)§5.4在PL/SQL中使用SQLCODE,SQLERRM (36)第六章存储函数和过程 (38)§6.1引言 (38)§6.2创建函数 (38)§6.3存储过程 (41)§6.3.1 创建过程 (41)§6.3.2 调用存储过程 (42)§6.3.3 开发存储过程步骤 (44)§6.3.4 与过程相关数据字典 (45)第七章包的创建和应用 (46)§7.1引言 (46)§7.2包的定义 (46)§7.3包的开发步骤 (47)§7.4包定义的说明 (47)§7.5子程序重载 (50)§7.6删除过程、函数和包 (52)§7.7包的管理 (52)第八章触发器 (53)§8.1触发器类型 (53)§8.1.1 DML触发器 (53)§8.1.2 替代触发器 (53)§8.1.3 系统触发器 (53)§8.2创建触发器 (53)§8.2.1 触发器触发次序 (55)§8.2.2 创建DML触发器 (55)§8.2.3 创建替代(Instead_of)触发器 (56)§8.2.3 创建系统事件触发器 (57)§8.2.4 系统触发器事件属性 (57)§8.2.5 使用触发器谓词 (58)§8.2.6 重新编译触发器 (59)§8.3删除和使能触发器 (59)§8.4触发器和数据字典 (59)§8.5数据库触发器的应用举例 (60)第一章PL/SQL 程序设计简介PL /SQL是一种高级数据库程序设计语言，该语言专门用于在各种环境下对ORACLE数据库进行访问。
PLSQL学习(一) 基础知识
很久以前自己整理的PLSQL一些基础知识，翻出来看看都是比较基础的内容，还是放上来好了。
参考的资料是《PL/SQL用户指南与参考》中译版，觉得这是一部很不错的入门书，感谢把这本书翻译出来的网友。
1、乘方的不同应用：
在SQL中乘方只能使用函数Power(a,b)来计算
但在PLSQL中可直接使用a**b表示
2、赋值语句Returning：
insert into t1 values(90,'SERVICE','BEIJING')
Returning rowid,name into row_id,info;
注：只限insert、update、delete一条记录时进行赋值
3、自定义参数——&：
可SQL和PLSQL中均可使用自定义参数，即需要自己输入值的参数
select * from ldcom where comcode=&a andname=&b;
PLSQL：
declare i int := &a; n int := &b;
4、绑定变量的使用：
可使用绑定变量的办法提高SQL效率
SQL> variable x number ;
SQL> exec :x := 8600 ;
SQL> select * from ldcom where comcode= :x ;
注：PLSQL中的declare即隐士绑定，无需再申明
可用Print查看绑定变量 SQL> print x;
也可使用查询SQL> select :x from dual;
实际的简单应用：
variable x number;
declare
v_date date;
begin
for i in1.. 10loop
:x := i;
select sysdate+:x into v_date from dual;
dbms_output.put_line(v_date);
end loop;
end;
/
在execute immediate中的应用：
declare
v_x t1.num%type;
begin
execute immediate'update t1 set num=8888 where id=:a returning num into :b'
using2returning into v_x;
dbms_output.put_line(v_x);
end;
注意returning的返回值在动态SQL中的操作格式
5、%TYPE和%ROWTYPE的区别：
%TYPE针对某一字段类型(数组也是单一的类型)
%ROWTYPE针对某一整表的类型(游标也是整表)
6、计数循环的逆序法：
for i in reverse10.. 100loop
注意reverse的位置不要记错
7、一般的游标使用法：
1、游标的正常使用需要四步
①定义游标
cursor c2(dept_no number default10) is----注意定义dept_no的方法
select name,agentcode from laagent where rownum <= dept_no;
②打开游标
open c3(dept_no =>20); ----可以重新定义dept_no
③提取游标数据
fetch c2 into dept_name,dept_loc;----字符类型、个数相等
fetch c3 into deptrec;----deptrec为rowtype
exit when c3%notfound;
④关闭游标
CLOSE c3;
⑤游标属性
%FOUND--布尔型属性，当最近一次读记录时成功返回,则值为TRUE；
%NOTFOUND--布尔型属性，与%FOUND相反；
%ISOPEN--布尔型属性，当游标已打开时返回TRUE；
%ROWCOUNT--数字型属性，返回已从游标中读取的记录数。
2、使用for自动打开、提取、关闭游标
FOR c1_rec IN c1 LOOP
FOR c1_rec IN(SELECT dname, loc FROM dept) LOOP
3、注：单一的SQL语句都是一个隐式的游标，属性为
SQL%FOUND--布尔型属性，当最近一次读记录时成功返回,则值为TRUE；
SQL%NOTFOUND--布尔型属性，与%FOUND相反；
SQL%ISOPEN--布尔型属性，当游标已打开时返回TRUE；
SQL%ROWCOUNT--数字型属性，返回已从游标中读取的记录数。
例如：DELETEFROM emp WHERE deptno=v_deptno;
IF SQL%NOTFOUND THEN ...
8、For Update锁定数据：
CURSOR emp_cursor is select empno,sal
from emp where deptno=v_deptno for update of sal nowait;
注意：其中的of sal，for update可以精确到某一个或几个字段
在使用了For Update之后可以在delete和update语句中使用current of cursor_name子句
for emp_record in emp_cursor loop
if emp_record.sal < 1500then
update emp set sal=1500where current of emp_cursor;
end if;
end loop;
但需注意：只能针对for update的表进行修改。
9、Package中的子程序可以重载
即函数名相同，参数个数不同
10、PL/SQL的执行顺序
在同一Package中，若要使用自身定义的Function或Procedure，则必须是之前定义过的。
同理，即便使用参数，也必须在前面定义过。
也就是说：一般最终的执行程序，会放在Package的最后，除非将所有部分都申明成Public。
