PLSQL授课笔记

PL/SQL学习笔记Java相关课程系列笔记之三笔记内容说明PL/SQL（薛海璐老师主讲，占笔记内容100%）；目录 一、 PL/SQL 简介........................................................................................................................... 1 1.1什么是 PL/SQL.....................................................................................................................1 1.2 PL/SQL 程序结构................................................................................................................ 1 1.3 PL/SQL 运行过程................................................................................................................ 1 1.4注释 (2)二、变量与数据类型 (3)2.1数据类型...............................................................................................................................3 2.2标量类型...............................................................................................................................3 2.3变量声明. (3)三、流程控制语句 ....................................................................................................................... 5 3.1条件语句...............................................................................................................................5 3.2循环语句. (6)四、 PL/SQL 中的 SQL (9)4.1 PL/SQL 中的 SQL 分类.......................................................................................................9 4.2 DML （insert ，update ，delete ）和 TCL （commit ，rollback ）...................................... 9 4.3 DDL (9)五、 PL/SQL 中的select............................................................................................................11 5.1 select 语句的实现...............................................................................................................11 5.2 record 类型..........................................................................................................................11 5.3 %rowtype............................................................................................................................ 12 5.4 record 变量的引用..............................................................................................................12 5.5 cursor 的概念......................................................................................................................13 5.6 cursor 的分类......................................................................................................................13 5.7显式 cursor 的处理.............................................................................................................13 5.8显式 cursor 的属性.............................................................................................................14 5.9隐式 cursor 的属性.............................................................................................................14 5.10 cursor 的声明....................................................................................................................14 5.11 open cursor........................................................................................................................ 14 5.12 fetch cursor........................................................................................................................15 5.13结果集提取的控制...........................................................................................................15 5.14 close cursor (15)六、集合 (19)6.1什么是 collection................................................................................................................19 6.2什么是关联数组.................................................................................................................19 6.3 Associative arrays 的定义.................................................................................................. 19 6.4声明 Associative arrays 类型和变量.................................................................................19 6.5关联数组的操作.................................................................................................................20 6.6 Associative arrays 的方法.................................................................................................. 20 6.7关联数组的遍历.................................................................................................................21 6.8 Associative arrays 的异常.................................................................................................. 22 6.9批量绑定.. (22)七、异常 (25)17.1 Oralce错误 (25)7.2 Oralce错误处理机制 (25)7.3异常的类型 (25)7.4 PL/SQL中的异常 (25)7.5异常捕获 (25)7.6异常的捕获规则 (26)7.7 Oralce预定义错误的捕获 (26)7.8非Oracle预定义异常 (27)7.9用户自定义异常 (27)7.10异常处理总结 (28)7.11 sqlcode和sqlerrm............................................................................................................287.12异常的传播 (29)八、子程序 (30)8.1子程序 (30)8.2有名子程序 (30)8.3有名子程序的分类 (30)8.4有名子程序的优点 (30)九、过程procedure (31)9.1语法 (31)9.2创建存储过程 (31)9.3形参和实参 (31)9.4形参的种类 (32)9.5调用存储过程 (32)9.6存储过程中的参数 (32)9.7对实际参数的要求 (33)9.8形式参数的限制 (33)9.9带参数的过程调用 (33)9.10使用缺省参数 (33)9.11存储过程中的DDL语句 (34)9.12变量 (35)9.13 PL/SQL中的SQL分类 (35)9.14再一次来看SQL语句的处理过程 (37)9.15软分析和硬分析 (37)9.16对过程procedure的基本操作 (37)9.17案例 (37)十、函数function.........................................................................................................................3910.1语法 (39)10.2创建函数 (39)10.3调用函数 (39)10.4对函数function的基本操作 (40)10.5过程和函数的比较 (40)10.6匿名块中的过程和函数声明 (40)10.7案例 (40)2十一、包package.........................................................................................................................4211.1什么是package (42)11.2包的组成 (42)11.3包的优点 (42)11.4 package声明的语法 (42)11.5 package body声明的语法 (43)11.6编译包和包体 (43)11.7案例 (43)十二、触发器trigger (45)12.1面临问题 (45)12.2 DML触发器的组成 (45)12.3 DML触发器的类型 (45)12.4 DML触发器的触发顺序 (45)12.5 DML行级触发器 (45)12.6 :OLD和:NEW (45)12.7触发器的重新编译 (47)12.8触发器的状态 (47)十三、其他注意事项 (48)13.1 PL/SQL的特点 (48)13.2写PL/SQL的好处 (48)13.3命名建议 (48)13.4搞清楚如下内容 (48)13.5保证所有对象的状态都是 valid (48)13.6 declare中都可声明什么 (48)13.7数据库对象 (48)3一、PL/SQL 简介1.1什么是 PL/SQLPL/SQL （Procedural Language/SQL ）是 Oracle 在标准 SQL 的基础上增加了过程化处理， 把 DML 和 select 语句组织在 PL/SQL 代码的过程性单元中，通过逻辑判断、循环等操作，实 现复杂的功能或者计算的程序语言。

一、PL/SQL基本语法要素1.相对其他语言特殊运算符=号是是否相等的比较运算符:=才是赋值运算符' ' 两个单引号之间表示字符类型的变量" " 双引号之间标识引用，如果字段名，数据库名等|| 表示字符之间的连接符--表示单行注释%属性指示器2.PL/SQL变量及数据类型1.变量类型标量类型：数值，字符，日期，布尔复合类型：索引表，嵌套表，数组引用类型：游标类型（CURSOR）,对象类型（REF）LOB类型：大文本等2.使用%定义变量的类型跟某个表的列属性一致的变量的声明v_name %type 表示v_name 这个变量与表student中的列name 属性一样v_student student%rowtype 表示v_student这个变量客户存student中的一行数据3.用select into个变量赋值的时候select结果必须只有一行数据，如果多行会包too many row错误，空的话则会报空错误。

3.PL/SQL中最基本的语句块结构DECLARE声明变量，要使用的变量必须在这里声明BEGIN需要执行的程序语句EXCEPTION异常处理语句END二、PL/SQL 程序顺序结构（分支和循环）1.正常都是从上到下的顺序结构2.使用if进行分支处理IF 语句的格式：if condition_1 then statement_1; elsif condition_n then statement_n;else else_statements; endif;3.使用case进行分支处理CASE语句格式：1.单值比较: case expression when result_1 then statements_1;when result_2 then statements_2;elseelse_statement;endcase;2.多值比较：casewhen expression_1 then statement_1;when expression_2 then statement_2;elseelse_statements;endcase;4.使用循环结构1.使用loop循环结构：loop statements；exit when expression；end loop；满足expression条件的时候跳出循环；2.使用while循环结构：while expression loop statements；end loop；expression 的格式：vi<=10 不加分号3.使用for循环结构：for expression loop statements；end loop;expression 的格式：vi in 1..10 不叫分号三、游标基础及使用1.游标的定义：游标用于表示一查询结果的结果集2.游标使用四个步骤：---1.声明declare --- 2.打开open ----3.读取fetch ----4.关闭close1.声明游标：CURSOR cursor_name IS select_statements;例如：CURSOR cursor_student IS SELECT id,name,age,sex FROM students WHERE name LIKE '林%'；2.使用游标是打开：Open cursor_name;例如：OPEN cursor_student;打开游标的时候系统才会去查询游标声明的数据集；3.读取数据：FETCH cursor_name INTO variable_name1,…variable_name2;例如：FETCH cursor_studnet INTO v_id,v_sname,v_age,v_sex;第一次读取的是第一行数据，再次使用FETCH读取数据的时候是下一行所以处理游标时一般都是用循环；4.关闭游标：CLOSE cursor_name;例如:CLOSE cursor_student;3.游标中的属性：四个常用属性：%ISOPEN 、%FOUND 、%NOTFOUND 、%ROWCOUNT1.%ISOPEN 返回游标是否打开使用方法cursor_name%ISOPEN例如：cursor_student%ISOPEN;2.%NOTFUND 返回最近一次FETCH取数是否有取到数，没取到返回true，有取到返回false；一般用于判断循环终止使用方法：cursor_name%NOTFUND例如：cursor_student%NOTFOUND3.%FOUND 与%NOTFOUND相反4.%ROWCOUNT 返回现在已经读取的行数使用方法：cursor_name%ROWCOUNT例如：cursor_student%ROWCOUNT4.简单使用游标循环读取数据的例子读取幵打印学生表中“计算机系学生的信息”DECLAREv_specialty students.specialty%type; --声明变量v_name %type;v_dob students.dob%type;CURSOR student_cur --声明游标ISSELECT name,dobFROM students WHERE specialty=v_specialty;BEGINv_specialty:='计算机'; --给变量赋值OPEN student_cur; --打开游标DBMES_OUTPUT.PUT_LINE('学生姓名出生日期'); --输出LOOPFETCH student_cur INTO v_name,v_dob; --读取游标中的数据EXIT WHEN student_cur%NOTFUND; --设定退出条件DBMS_OUTPUT.PUT_LINE(v_name||' '||v_dob); --输出结果END LOOP; --循环体结束标志CLOSE sutent_cur; --关闭游标END; --块结构结束标志5.游标的应用：1.对游标指定的数据进行修改：需要对游标指定的数据修改必须在声明游标的时候带有FOR UPDATE 关键字语法：CURSOR cursor_name IS select_statement FOR UPDATE OF table_name NOTWAIT;OF table_name 可以不写，用于自动选择多表的时候确定那些表需要锁定；NOTWAIT可以不写，用于确认是否等待锁。

1.声明部分:主要用于声明变量、常量、数据类型、游标、异常处理名称以及本地(局部)子程序定义等。

2.可执行部分执行部分:是PL/SQL块的功能实现部分。

该部分通过变量赋值、流程控制、数据查询、数据操纵、数据定义、事务控制、游标处理等实现块的功能。

3.异常处理部分:异常处理部分用于处理该块执行过程中产生的异常。

执行部分是必须的,而声明部分和异常部分是可选的；PL/SQL的字符集包括：大小写字母：A~Z，a~z数字：0~9空白：制表符、空格和回车数字符号：+ - * / 〈〉 =标点符号：~ ! @ # $ % ^&* （）_ | { } [ ] ？；：, . “ ‘1.1标识符用于定义PL/SQL变量、常量、异常、游标名称、游标变量、参数、子程序名称和其他的程序单元名称等。

1.2在PL/SQL程序中，标识符是以字母开头的，后边可以跟字母、数字、美元符号($)、井号(#)或下划线(_)，其最大长度为30个字符，并且所有字符都是有效的。

数字类型字符类型日期/区间类型行标识类型布尔类型原始类型LOB类型引用类型记录类型集合类型%TYPE与%ROWTYPE分类数据类型数字类型 NUMBER、BINARY_NUMBER PLS_NUMBER字符类型 VARCHAR2、CHAR、LONG、NCHAR、NVARCHAR日期/区间类型 DATE、TIMESTAMP、INTERVAL行标识类型 ROWID、UROWID布尔类型 BOOLEAN（TRUE、FALSE、NULL）原始类型 RAW、LONG RAWLOB类型 CLOB、BLOB、NCLOB、BFILE引用类型 REF CURSOR，REF object_type。

记录类型 RECORD集合类型 TABLE、VARRAY变量定义的一般格式variable_name [CONSTANT] datatype [NOT NULL] [DEFAULT|:=expression];说明1.1变量或常量名称是一个PL/SQL标识符，应符合标识符命名规范；1.2每行只能定义一个变量；1.3如果加上关键字CONSTANT，则表示所定义的是一个常量，必须为它赋初值；1.4如果定义变量时使用了NOT NULL关键字，则必须为变量赋初值；1.5如果变量没有赋初值，则默认为NULL；1.6使用DEFAULT或“:=”运算符为变量初始化。

PL/SQL学习笔记PLSQL特有的%TYPE属性来声明与XX类型一致的变量类型：举例：...v_st_name%TYPE;v_min_balancev_balance%TYPE:= 10;...可绑定变量（Bind Variable 也称为Host Variable , 非PLSQL 变量）：可绑定变量是一种在缩主环境中定义的变量，所谓缩主环境一般指示SQLPLUS执行环境或者是PLSQL Developer 的Command Window执行环境；可绑定变量可用于在运行时把值传递给PLSQL, 创建语法:V ARIABLE return_codeNUMBERV ARIABLE return_msgV ARCHAR2(30)大家注意，在标准的PLSQL中定义变量是不能用V ARIABLE关键字的，此关键字只在SQLPLUS执行环境中有效，可使用PRINT语句输出变量内容。

在PLSQL中使用这种变量时，前面加”:”, 以示区分。

SELECT INTO 语句：用于把从数据库查询出内容存入变量BEGININSERT INTO employees(employee_id, first_name, last_name, email,hire_date, job_id, salary)V ALUES(employees_seq.NEXTV AL, 'Ruth', 'Cores', 'RCORES',sysdate, 'AD_ASST', 4000);END;循环语句的语法与其他语言类似：有基本循环、For循环、Wihle循环三种LOOPstatement1;. . .EXIT [WHEN condition];END LOOP;WHILE condition LOOPstatement1;statement2;. . .END LOOP;FOR counter IN [REVERSE]lower_bound..upper_bound LOOPstatement1;statement2;. . .END LOOP;概述：PLSQL中常用的自定义类型就两种：记录类型、PLSQL内存表类型（根据表中的数据字段的简单和复杂程度又可分别实现类似于简单数组和记录数组的功能）记录类型的定义语法：TYPE type_name IS RECORD(field_declaration[, field_declaration]…);identifiertype_name;这里的field_declaration 的具体格式可以是：field_name {field_type | variable%TYPE| table.column%TYPE | table%ROWTYPE}[[NOT NULL] {:= | DEFAULT} expr]%ROWTYPE属性：在PLSQL中%ROWTYPE 表示某张表的记录类型或者是用户指定以的记录类型，使用此属性可以很方便的定义一个变量，其类型与某张表的记录或者自定义的记录类型保持一致。

1.PL/SQL综述本章学习目标，了解如下内容：PL/SQL的功能和作用PL/SQL 的优点和特征；Oracle 10g、Oracle9i 的PL/SQL新特征1.1.SQL简介1.1.1.SQL语言特点SQL语言采用集合操作方式1.1.2.SQL语言分类●数据查询语言（SELECT语句）：检索数据库数据。

●数据操纵语言（DML）：用于改变数据库数据。

包括insert，update和delete三条语句。

●事务控制语言（TCL）：用于维护数据库的一致性，包括commit，rollback和savepoint 三条语句●数据定义语言（DDL）：用户建立、修改和删除数据库对象。

●数据控制语言（DDL）：用于执行权限授予和收回操作。

包括grant 和revoke两条命令。

1.1.3.SQL 语句编写规则●SQL关键字不区分大小写●对象名和列名不区分大小写●字符值和日期值区分大小写●书写格式随意1.2.PL/SQL简介1.3.Oracle 10G PL/SQL 新特征2.PL/SQL开发工具本章学习目标：学会使用SQL*PLUS学会使用PL/SQL developer；学会使用Procedure Builder。

2.1.SQL*PLUS在命令行运行SQL*PlusSqlplus [username]/[password] [@server]3.PL/SQL 基础学习目标：●了解PL/SQL块的基本结构以及PL/SQL块的分类；●学会在PL/SQL块中定义和使用变量●学会在PL/SQL块中编写可执行语句；●了解编写PL/SQL代码的指导方针；●了解Oracle 10g的新特征——新数据类型BINARY_FLOAT 和BINARY_DOUBLE，以及指定字符串文本的新方法。

3.1.PL/SQL 块简介3.1.1.PL/SQL块结构3.1.2.PL/SQL 块分类匿名块命名块子程序触发器3.2. 定义并使用变量3.2.1.标量变量3.2.2.复合变量3.2.3.参照变量3.2.4.LOB 变量3.2.5.非PL/SQL 变量3.3.编写 PL/SQL 代码3.3.1.PL/SQL 词汇单元分隔符标识符文本（数字文本，字符文本，字符串文本，布尔文本，日期时间文本）注释3.3.2.PL/SQL 代码编码规则标识符命名规则大小写规则代码缩进嵌套块和变量范围PL/SQL中可以使用的SQL函数4.使用SQL语句学习目标：学会使用SELECT语句去完成基本查询功能学会使用INSERT，UPDA TE和DELETE语句去操作数据库数据学会使用COMMIT，ROLLBACK和SA VEPOINT语句去控制事务学会使用SELECT语句去实现各种复杂查询功能（数据分组、连接查询、子查询、层次查询、合并查询等）4.1.使用基本查询处理NULL：函数nvl(expr1,expr2)，nvl2(expr1,expr2,expr3)4.2.使用DML语句使用多表插入数据语法：INSERT ALL insert_into_clause [value_clause] subquery;INSERT conditional_insert_clause subquery;示例1：使用ALL 操作符执行多表插入INSERT ALLWHEN deptno=10 THEN INTO dept10WHEN deptno=20 THEN INTO dept20WHEN deptno=30 THEN INTO dept30WHEN job=’CLERK’ THEN INTO clerkELSE INTO otherSelect * from emp;示例2：使用FIRST 操作符执行多表插入INSERT FIRSTWHEN deptno=10 THEN INTO dept10WHEN deptno=20 THEN INTO dept20WHEN deptno=30 THEN INTO dept30WHEN job=’CLERK’ THEN INTO clerkELSE INTO otherSELECT * FROM emp;4.3.使用事务控制语句4.3.1.事务和锁4.3.2.提交事务4.3.3.回退事务设置保存点：savepoint a;或者exec dbms_transaction.savepoint(‘a’)取消部分事务Rollback to a;或者Exec dbms_transaction.rollback_savepoint(‘a’)取消全部事务：Rollback; 或者exec dbms_transaction.rollback() 4.3.4.只读事务4.3.5.顺序事务4.4.数据分组4.4.1.分组函数MaxMinAvgSumCountVarianceStddev使用分组函数注意事项：●当使用分组函数时，除了函数Count(*) 之外，其他分组函数都会忽略NULL行。

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 变量 (11)§2.4.6 INDEX BY TABLES (12)§2.4.7 数据类型的转换* (13)§2.5运算符和表达式(数据定义) (13)§2.5.1 关系运算符 (13)§2.5.2 一般运算符 (13)§2.5.3 逻辑运算符 (14)§2.6变量赋值 (14)§2.6.1 字符及数字运算特点 (14)§2.6.2 BOOLEAN 赋值 (14)§2.6.3 数据库赋值 (14)§2.6.4 可转换的类型赋值 (15)§2.7变量作用范围及可见性 (15)§2.8注释 (16)§2.9简单例子 (17)§2.9.1 简单数据插入例子 (17)§2.9.2 简单数据删除例子 (17)第三章PL/SQL流程控制语句 (18)§3.1条件语句 (18)§3.2CASE表达式 (19)§3.3循环 (19)§3.3标号和GOTO (21)§3.4NULL语句 (21)第四章游标的使用 (23)§4.1游标概念 (23)§4.1.1 处理显式游标 (23)§4.1.2 处理隐式游标 (26)§4.1.3 游标修改和删除操作 (27)第五章异常错误处理 (29)§5.1异常处理概念 (29)§5.1.1 预定义的异常处理 (29)§5.1.2 非预定义的异常处理 (30)§5.1.3 用户自定义的异常处理 (31)§5.1.4 用户定义的异常处理 (32)§5.2异常错误传播 (33)§5.2.1 在执行部分引发异常错误 (33)§5.2.2 在声明部分引发异常错误 (34)§5.3异常错误处理编程 (34)§5.4在PL/SQL中使用SQLCODE,SQLERRM (35)第六章存储函数和过程 (36)§6.1引言 (36)§6.2创建函数 (36)§6.3存储过程 (39)§6.3.1 创建过程 (39)§6.3.2 调用存储过程 (40)§6.3.3 开发存储过程步骤 (42)§6.3.4 与过程相关数据字典 (43)第七章包的创建和应用 (44)§7.1引言 (44)§7.2包的定义 (44)§7.3包的开发步骤 (45)§7.4包定义的说明 (45)§7.5子程序重载 (48)§7.6删除过程、函数和包 (50)§7.7包的管理 (50)第八章触发器 (51)§8.1触发器类型 (51)§8.1.1 DML触发器 (51)§8.1.2 替代触发器 (51)§8.1.3 系统触发器 (51)§8.2创建触发器 (51)§8.2.1 触发器触发次序 (53)§8.2.2 创建DML触发器 (53)§8.2.3 创建替代(Instead_of)触发器 (54)§8.2.3 创建系统事件触发器 (54)§8.2.4 系统触发器事件属性 (55)§8.2.5 使用触发器谓词 (56)§8.2.6 重新编译触发器 (56)§8.3删除和使能触发器 (56)§8.4触发器和数据字典 (57)§8.5数据库触发器的应用举例 (57)第一章PL/SQL 程序设计简介PL /SQL是一种高级数据库程序设计语言，该语言专门用于在各种环境下对ORACLE数据库进行访问。

PLSQL笔记PL/SQL编辑器，具有语法加强、SQL和PL/SQL帮助、对象描述、代码助手、编译器提示、PL/SQL完善、代码内容、代码分级、浏览器按钮、超链接导航、宏库等许多智能特性，能够满足要求性最高的用户需求。

当您需要某个信息时，它将自动出现，至多单击即可将信息调出。

一、简介1、PLSQL Developer是一个为Oracle数据库开发存储程序单元的集成开发环境，你能方便的创建你的客户/服务器应用程序的服务器部分。

2、PL/SQL可以做的事：1）使用文本编辑器写程序单元（过程、触发器等）。

2）使用Oracle SQL*PLUS编译源文件3）如果有编译错误，你必须找出它位于源文件何处，纠正它，转回到SQL*PLUS重新编译它，然后再找下一处错误。

4）使用SQL*PLUS或你的客户端应用程序测试程序单元。

5）如果发生运行时间错误，你又要很辛苦地定位问题的原因并纠正错误。

6）使用解释计划工具或tkprof工具优化你的SQL语句。

7）使用SQL*plus或另外的工具在你的数据库里查看或修改其它对象和数据。

3、工具PL/SQL Developer提供了几个能对开发有帮助的工具，这些工具包括允许你在数据库对象源搜索文本的查找数据库对象工具、快速编译在开发期间已经变得无效的对象的编译无效对象工具、导出和导入工具、创建测试数据的数据生成器工具、导出用户对象DDL语句的导出用户对象工具、比较两个用户对象定义的比较用户对象工具、会话信息工具、事件监视器和一个比较并使表内容相同的比较表数据工具。

除了这些标准工具外，你还可以定义你自己的工具，并把他们包括在PL/SQL Developer的工具菜单里面。

4、工程要组织你的工作，你可以使用PL/SQL Developer的工程概念，工程由很多文件和数据库对象组成，这些对象通过工程项目窗口很容易被建成，并且通过单击鼠标就能被编译。

5、图形图形窗口可以运行于SQL窗口或报告窗口的内部，以获得查询数据的图示。