- pl/sql是什么
pl/sql是oracle在标准sql语言上的扩展。pl/sql不仅允许嵌入sql语言，还可以定义变量和常量，允许使用条件语句和循环语句，允许使用另外处理各种错误，功能强大。
-
为什么学pl/sql
优点：
1.提高应用程序的运行性能
2.模块化得设计思想
3.减少网络传输量
4.提高数据库访问的安全性
缺点：
移植性不好
- 开发工具：
环境设置：
- 页大小和行大小： set pagesize 50 linesize 120;
- 更改日期格式：alter session set nls_date_format='YY-MM-DD HH24:MI:SS';
-
为使用程序包DBMS_OUTPUT中的PUT_LINE过程输出: set serveroutput on;
1.sqlplus
---oracle公司提供的一个工具。
2.pl/sql developer 开发工具
pl/sql developer 是用于开发pl/sql块的IDE，独立的产品。
举例：
创建过程：create [or replace ] procedure 过程名 is
begin
----执行过程
end；
/
replace:表示如果存在过程名，就替换。
--：注释
查看错误信息： show error
调用过程：
法一：exec 过程名(参数列)；
法二：call 过程名(参数列)；
- 块的类型
所有的PL/Sql程序都是以块为基本单位。块包含过程化语句和DML语句。块的分类：
- 匿名块：出现在应用程序中的、没有名字、不存储在数据库中的块。可以出现在SQL语句出现的地方。可以调用其他程序，不能被调用。
- 命名块：一个带有标签的匿名块，标签为块指明一个名称。
- 子程序：存储在数据库中的过程、函数，函数有返回值，过程无返回值。
- 程序包：存储在数据库中的一组子程序、变量的定义。
- 触发器：一种存储在数据库中的命名块，可多次调用。
2.PL/SQL基础
-
编写规范
注释：
单行注释： --
多行注释：/* ...*/
标识符号的命名规范：
1）当定义变量时，建议用v_作为前缀：
2）当定义常量时，建议用c_作为前缀：
3）当定义游标时，建议用_cursor作为后缀：
4）当定义例外时，建议用e_作为前缀：
- PL/SQL块
介绍：块是pl/sql的基本程序单元。复杂功能可能需在pl/sql块中嵌套其它的pl/sql块。
块的结构
三部分：定义部分、执行部分、异常处理部分。 如下所示：
declear
/*定义部分--定义常量、变量、函数、游标、异常、复杂数据类型 是可选的*/
begin
/*执行部分--要执行的pl/sql语句和sql语句，至少包含一条 是必须的*/
exception
/*例外处理部分--处理运行的各种错误 是可选的*/
end；
主块中可以包含子块，可以将子块放在执行部分或异常处理部分，但是不能放在声明部分。
块的实例：
实例1： --只包含执行部分的pl/sql块
set serveroutput on --打开输出选项
begin
dbms_output.putline('Hell World');
end;
相关说明：
dbms_output是oracle所提供的包，该包中包含一些过程，put_line就是dbms_output包的一个过程。
实例2： --包含定义部分和执行部分的pl/sql块
declare
v_ename varchar2(5); --定义字符串变量
begin
select ename,...into v_ename,其它变量 from emp where empno=&no; --&表示要接收从控制台输入的变量
dbms_output.putline('雇员名' || v_ename ||其它变量);
end;
实例3： ----包含定义部分和执行部分和例外处理部分
exception
--异常处理
when no_data_found then
dbms_output.putline('数据找不到');
相关说明：oracle事先预定义了一些异常，no_data_found就是找不到数据的异常。
实例4： ---声明命名块
<<命名块>>
declare
v_ename varchar2(5); --定义字符串变量
begin
select ename,...into v_ename,其它变量 from emp where empno=&no; --&表示要接收从控制台输入的变量
dbms_output.putline('雇员名' || v_ename ||其它变量);
end <<命名块>>;
- 定义并使用变量
介绍：在编写pl/sql程序时，可以使用变量和常量；包括：标量类型(scalar)、复合类型(composite)、参照类型(reference)、lob(large object)。
-
数据类型：
NUMBER(p,s)：p表示总位数，s表示小数点后的位数。p:1-38 s：-84-127
CHAR(n)：用于固定长度的字符串。n：1-32767，pl/sql中操纵char列时,应<2000字节.
VARCHAR2(n):用于可变长度的字符串。n:1-32767,pl/sql中操纵char列时,应<4000字节.
BOOLEAN:值-true、false、null，这些值赋给boolean变量时，不能用单引号。此数据类型为pl/sql特有，表中的列不能采用此类型(char)代替。
DATE：公元前4712年1月1日--9999年12月31日。内存中用7个字节保存。
TIMESTEMP(s)
INTERVAL YEAR(y) TO MONTH
INTERVAL DAY(d) TO SECOND(s)
CLOB:具体类型:BLOB CLOB BFIFE
%TYPE:用已经定义了的变量的数据类型来定义另一变量： ..=..%TYPE
%ROWTYPE:定义一个表示表中一行记录的变量。 一行记录可以保存从一个表或游标中查询得到的整个数据行的各个列的数据。注意：使用%ROWTYPE定义的变量时，要使用"."运算符来指定记录变量名限定词。
RECORD：定义记录数据类型。声明部分定义记录的组成、记录的变量。
定义记录数据类型： TYPE record_name IS RECORD(v1 datatype [not null] [:= ],...);
TABLE:定义记录表的数据类型，可处理多行记录。
定义记录表数据类型：TYPE table_name IS TABLE OF element_type [NOT NULL] INDEX BY [BINARY_INTEGER|PLS_INTERGER|VARRAY2];
-
标量类型
在编写pl/sql块时，如果要使用变量，需要在定义部分部分定义变量，语法如下：
indentifier [constant] datatype [not null] [:= | default expr];
indentifier： 变量名称
constant 指定常量，需要指定初始值，且不可改变。
datatype 数据类型
not null 指定变量值，不能为null
:= 给变量或常量指定初始值
default 用于指定初始值
expr 指定出事值的pl/sql表达式，可能是文本值、其它变量、函数等。
作用域： 和其他高级语言类型。
标量定义案例
1.定义一个变长字符串： v_ename varchar2(10);
2.定义一个小数： v_sal number(6,2);
3.定义一个小数并给一个初始值： v_sal number :=5.4;
4.定义一个日期类型的数据： v_hiredate date；
5.定义以个布尔变量，不能为空，初始值为false：v_valid boolean not null default false;
使用标量
1.定义完变量后就可以使用，pl/sql块中为变量赋值，需要在等号前加冒号(:=);
2.用select into语句给变量赋值： select 列名 into 变量 from ...
3.为降低pl/sql程序的维护量，可以使用%types属性定义变量，这样它会按照数据库列来确定你定义的变来那个的类型和长度：标识符名 表名.列名%types;
- 复合变量
介绍：用于存放多个值的变量，主要包括：pl/sql记录、pl/sql表、嵌套表、varray。
复合类型 --pl/sql记录
类似结构体。当引用pl/sql记录成员时，必须要加记录变量作为前缀(记录变量.记录成员)。如下：
declare
--定义一个pl/sql记录类型
type 自定义的pl/sql记录类型名 is record(
记录成员 记录成员类型，
....);
--用自定义的pl/sql记录类型来定义变量
变量名 自定义的pl/sql记录类型名；
begin
--执行语句
...一条记录的多个列值 into 记录变量名(pl/sql记录类型) ...
--取出记录成员值
记录变量名.记录成员
end;
复合类型 --pl/sql表
相当于数组。但是不同的是：在pl/sql中，下标可以为负数，并且表元素的下标没有限制。实例如下：
declare
--定义一个存储 (存储数据类型) 的pl/sql表类型，并且下标是整数
type 自定义的pl/sql表类型名 is table of 存储数据类型名 index by binary_integer;
--用自定义的pl/sql表类型来定义表变量
表变量名 自定义的pl/sql表类型名；
begin
--执行语句
...一个存储数据类型的值 into 表变量名(下标)(pl/sql表类型) ...
--取出记录成员值
表变量名(下标)
end;
复合变量 --嵌套表(nested table)
复合变量 --变长数组(varray)
- 参照变量
介绍：参照变量是指用于存放数值指针的变量。通过使用参照变量，可以使得应用程序共享相同对象，从而降低占用的空间。在编写pl/sql程序时，可以使用游标变量(ref cursor)和对象类型变量(ref obj_type)两种参照变量类型。
游标变量
declare
--定义一个游标类型
type 游标类型 is ref cursor;
--定义该游标类型的游标变量
游标变量 游标类型;
--定义一组变量，为游标取值用
......
begin
--执行
--把游标变量和一个select结合
open 游标变量 for 一个select语句;
--循环取出
loop
--把游标指向的一条记录赋值给一组变量
fetch test_cursor into 一组变量；
--判断游标变量是否为空，为空时退出
exit when 游标变量%notfound;
--输出一组变量
一组变量；
end loop;
--关闭游标
close 游标变量;
end;
/
- 游标
介绍：在pl/sql块中执行DML语句时，Oracle为在内存中为其分配上下文区，游标就是指向这一区的指针，为应用程序提供了对多行数据结果集操作每一条记录的方法。分为显式游标和隐式游标。每次会话中打开游标的数目是由数据库初始化文件参数的open_cursor参数定义的。
-
显式游标
游标操作
- 声明游标：
声明游标就是声明游标的名称和该游标所对应的SELECT语句。游标可以带参数，也可以有返回值。
语法格式：Cursor cursor_name[(arg1,datatype1,...)] [return datetype] IS select_statement;
注意：1.参数的数据类型后面不能带参数(即精度、范围等)。
2.声明游标后，还可以基于游标用%ROWTYPE定义记录变量。
格式：record_var cursor_name%rowtype;
- 打开游标：
打开游标后得到的结果集是静态的。
格式： open cursor_name[(arg1,datatype1,...)];
- 提取游标：
打开游标后，游标的指针指向第一个数据行。执行fetch语句提取一行数据后，自动将游标指针移动到下一个数据行。每次执行完fetch语句后，检查游标属性%found就可以判断fetch语句是否执行成功地返回了一个数据行，以便确定是否给对应的变量赋了值。
语法格式：fetch cursor_name into {variable_list | record_variable};
- 关闭游标：
CLOSE cursor_name;
游标属性
无论是显式游标、隐式游标，都有%ISOPEN、%FOUND、%NOTFOUND、%ROWCOUNT四种属性，描述与游标操作相关的DML语句的执行情况。
1. %ISOPEN：若游标打开，则为true
2. %FOUND：若最近一次提取游标操作成功则为true，否则为false
3. %NOTFOUND：若最近一次提取游标操作成功则为false，否则为true
4. %ROWCOUNT：最近一次提取到得数据行的行序号。游标打开之后提取之前访问为0。
- 隐式游标
隐式游标是由pl/sql控制的。当执行一条DML语句或SELECT ... INTO语句时，都会创建一个隐式游标，名称为SQL。但是不能对其进行OPEN、FETCH、CLOSE语句，Oracle自动进行。
注意：当使用select语句时，sql游标一次只能返回一行或0行数据，若返回多行，则报异常，这是就用显式游标处理。当使用insert、undate、delete时，sql游标可以处理多行。
- 隐式游标的属性：
属性 |
值 |
select |
insert |
update |
delete |
SQL%ISOPEN |
false |
false |
false |
false |
|
SQL%FOUND |
true/false |
有结果/无结果 |
true/false |
true/false |
true/false |
SQL%NOTFOUND |
true/false |
无结果/有结果 |
false/true |
false/true |
false/true |
SQL%ROWCOUNT |
返回行数，只能为1 |
插入的行数 |
修改的行数 |
删除的行数 |
- 游标FOR循环
一个游标for循环可以隐式的实现open、fetch、close游标以及循环处理结果集的功能。当调用exit、goto或发生异常时，pl/sql均能自动关闭游标。
语法格式：
FOR index_variable IN cursor_name[(实际参数)] LOOP
语句段；
END LOOP；
通过游标可以实现一行一行的处理select语句的结果集。
- 使用游标更新或删除数据
要求：在声明/打开游标的查询语句中必须使用FOR UPDATE子句。目的：以便在打开游标时锁定游标结果集在数据库中对应的数据行，从而不被其他用户更改或删除。
使用FOR UPDATE选项的游标查询语句的语法格式为：
SELECT 列名... FROM 表名 FOR UPDATE [NOWAIT];
打开游标之后，就可以在update、delete语句中使用where current of子句，修改或删除游标结果集中所对应的数据库表中的数据行，语法格式：
不带where条件的update/delete语句 where current of 已定义的游标名；
- 过程
过程用于执行特定的操作。当建立过程时，既可以指定输入参数(in)--可以将数据传递到执行部分，也可以指定输出参数(out)--可以将执行部分的数据传递到应用环境。在sqlplus中可以使用create procedure命令来建立过程。
建立过程
create[ or replace] procedure 过程名(IN参数...OUT参数...INOUT参数)
is | as
/*声明部分*/
begin
/*执行部分*/
exception
/*异常处理*/
end 过程名;
注：对于in实参--常量/变量， 对于out/inout实参--必须是变量。
调用过程(sqlplus)
实参到形参的传递关系有如下几种方法：
1.按位置传递 -- 在调用时按形参的排列顺序，依次写出实参的名称。
2.按名称传递 -- 在调用时按形参的名称与实参的名称，写出实参对应的形参，而将形参与实参关联起来进行传递。实参与形参是独立的。语法格式：形参名称=>实参名称
3.组合传递 -- 位置传递在前，名称传递在后；
在SQL *PLUS中，运行exec[ute]/call来调用的：
exec[ute] 过程名(参数列);
call 过程名(参数列);
tip:在控制台上打印结果： print :变量；
查看、删除过程
创建过程后，oracle将把过程及执行代码放到数据字典中。通过查询USER_SOURCE，可以显示当前用户的所有过程及源代码。注：过程名要大写。
select text from USER_SOURCE where name='过程名'；
DROP PROCEDURE命令来删除该过程。
DROP PROCEDURE 过程名；
java中调用oracle的存储过程
1.加载驱动： Class.forName("oracle.jdbc.driver.OracleDriver");
2.得到连接： Connection ct =DriverManager.getConnection("jdbc:oracle:thin:@IP地址:1521:数据库名","用户名","用户密码");
3.创建callableStatement：CallableStatement cs =ct.prepareCall("{call 过程名(参数列)}");
4.给参数列中的参数赋值： cs.setString(1,'实际参数1'); cs.setString(2, '实际参数2');...
5.执行： cs.excute();
6.关闭资源： cs.close(); ct.close();
- 函数
函数用于返回特定的数据，当建立函数时，在函数的头部必须包含return子句，而在函数体内必须包含return语句返回的数据。
建立函数
create [or replace ]function 函数名(IN参数...OUT参数...INOUT参数)
return 返回类型
is | as
/*声明部分*/
begin
--执行语句(内有变量赋值)
return 返回变量;
exception
异常处理部分
end 函数名;
/
注：对于in实参--常量/变量， 对于out/inout实参--必须是变量。
在sqlplus中调用函数
实参到形参的传递关系有如下几种方法：
1.按位置传递 -- 在调用时按形参的排列顺序，依次写出实参的名称。
2.按名称传递 -- 在调用时按形参的名称与实参的名称，写出实参对应的形参，而将形参与实参关联起来进行传递。实参与形参是独立的。语法格式：形参名称=>实参名称
3.组合传递 -- 位置传递在前，名称传递在后；
在SQL *PLUS中，运行exec[ute]/call来调用的：
新建一个变量temp；
调用：exec[ute] :temp:= 函数名(参数列);
call :temp:=函数名(参数列);
在控制台上打印结果： print :变量;
查看、删除函数
创建函数后，oracle将把过程及执行代码放到数据字典中。通过查询USER_SOURCE，可以显示当前用户的所有函数及源代码。注：函数名要大写。
select text from USER_SOURCE where name='函数名'；
DROP FUNCTION命令来删除该函数。
DROP FUNCTION 函数名；
在java中调用函数
select 函数名('实际参数列表') from dual; //这样就可以通过rs.getInt(1)得到返回的结果。
过程/函数使用：
- 一般原则：返回多个值或不返回值，就使用过程；只返回一个值，就使用函数。
- 过程一般用于执行一个指定的动作，函数一般用于计算和返回一个值。可以在SQL语句内部调用函数来完成复杂的计算问题，但不能调用过程。
- 程序包
- 介绍：
程序包用于将逻辑相关的块或元素等组织在一起，它由包规范和包体两部分组成，两者独立的存储在数据字典中。包规范/说明是包和应用程序的接口，只包含了过程和函数的声明，但是没有过程和函数的实现代码；包体用于实现包规范中的过程和函数。
对包体的更新不需要重新编译调用包的应用程序，而对说明部分更新则需重新编译每一个调用包的应用程序。
优势：模块化、简化应用程序的设计、信息隐藏、更好的效率。
- 创建包规范/说明 --create package命令
create[ or replace] package 包名 is | as
[PRAGMA SERIALLY_REUSABLE;] --决定包是否被连续调用。
公用数据类型定义、公用变量声明、公用常量声明、公用异常错误声明、公用游标声明、公用函数声明、公用过程声明
end 包名;
/
详细说明：该段语句创建了一个包，并且声明该包有一个过程和一个函数。
- 创建包体 --create package body命令
create [or replace ]package body 已声明的包 is | as
[PRAGMA SERIALLY_REUSABLE;]
私有数据类型的定义、私有变量声明、私有常量声明、私有异常错误声明、私有函数声明和定义、私有过程声明和定义、公用游标定义、公用函数定义、公用过程定义
begin
--执行过程(初始化部分)；
end 包名;
tips:查看编译错误：show errors;
- 调用包
当调用包的过程或函数时，在过程和函数前需要带有包名。如果要访问其它方案的包，还需要包名前加方案名。语法格式：程序包名.组件名称;
- 查看、删除包
创建包后，oracle将把过程及执行代码放到数据字典中。通过查询USER_SOURCE，可以显示当前用户的所有程序包及源代码。注：包名要大写。
select text from USER_SOURCE where name='程序包名'；
只删除包体，可以使用DROP PACKAGE BODY命令，
DROP PACKAGE BODY 包名；
同时删除包说明和包体，可以使用DROP PACKAGE BODY命令，
DROP PACKAGE 包名；
- 触发器
- 简介：
触发器是指当某些事件发生时Oracle自动执行的存储过程(包括声明、执行、异常处理部分，存于数据库)。触发器的功能：用来完成由数据库的完整性约束难以完成的复杂业务规则的约束，或用来监视对数据库的各种操作，实现审计的功能。
几个概念：
触发事件：引起触发器触发的事件，DML语句、DDL语句、数据库系统事件、用户事件。
触发条件：由WHEN子句指定的逻辑表达式为TRUE时触发。
触发对象：包括表、视图、模式、数据库。
触发操作：触发器所要执行的pl/sql程序。
注意事项：
1.触发器不接受参数。
2.一个表上最多有12个触发器，但不能有同一时间、同一事件、同一类型的触发器。
3.触发器最大为32KB。
4.触发器的执行部分只能是DML语句，不能是DDL语句。
5.触发器中不能包含事务控制语句(commit、rollback、savepoint)。
6.在触发器调用的任何过程、函数，都不能使用事务控制语句。
7.在触发器主体中不能声明任何long、blob变量。新值new、旧值old也不能指向表中的任一long、blob列。
8.不同类型的触发器的语法格式是不同的。
Oracle触发器的分类
1.DML触发器：当对表或视图执行DML操作时触发。
2.INSTEAD OF触发器：只定义在视图上，用来替换实际的操作语句。
3.系统触发器：在对数据库系统进行操作(DDL/启动或关闭数据库等系统事件)时触发。
-
DML触发器
基本要点：
1.触发时机：BEFORE/AFTER
2.触发事件：DML操作或多个触发事件的组合(只能用OR逻辑组合)
3.条件谓词：当触发多个事件组合时，为针对不同事件的不同处理，需使用Oracle提供的条件谓词：INSERTING---insert事件 UPDATING [(列...)] --UPDATE事件 DELETING --delete事件
4.触发对象：表、视图
5.触发类型：语句级触发器 行级触发器
6.触发条件：WHEN指定的逻辑表达式
7.触发顺序：BEFORE表级触发器-->BEFORE行级-->AFTER行级-->另一个BEFORE行级-->另一个AFTER行级-->...-->AFTER表级触发器 先创建先执行
8.功能特点：为保证数据库满足特定的规则，可以使用约束、触发器、子程序。选择顺序：约束-->触发器-->子程序
DML触发器可以用于实现数据操作的安全保护、数据审计、数据完整性、参数完整性、数据复制等。
语句级触发器
语句级触发器指当执行DML操作时，以语句为单位执行的触发器。其语法格式：
create [or replace ]trigger 触发器名称
{before|after}
{insert|delete|update}
[or {insert|delete|update} ...]
on 表名
pl/sql块 或 调用子程序；
注意：在语句级触发器中不能使用":new"或":old"限定词对列值进行访问和操作。
用途：可以实现数据操作的安全保护
行级触发器
行级触发器是指当执行DML操作时，以数据行为单位执行的触发器，即每一行都执行一次触发器。其语法格式是：
create [or replace ]trigger 触发器名称
{before|after}
{insert|delete|update[ of 列1，列2...]}
[or {insert|delete|update [ of 列3，列4...]} ...]
on 表名
for each row
[when 逻辑表达式]
pl/sql块 或 调用子程序；
注意：在行级触发器的执行过程中，pl/sql或sql语句中可以访问受触发语句影响的每行的列值。即："old."限定词--表示变化前得值；"new."限定词--表示变化后的值。在pl/sql或sql语句引用时，前面加":"，但是在WHEN condition子句引用时不用加":".
根据触发语句的不同，old.和new.是不同的，可能没有意义。
- INSTEAD OF触发器
instead of触发器只能定义在复杂视图上。复杂视图一般是不可更新视图，即不允许在该视图上执行DML操作，但通过创建instead of触发器，就可以在其上执行DML操作了。
与DML触发器不同，instead of触发器并不执行它的DML操作，而是用触发器执行部分来代替DML操作。
注意事项：
1.只能被创建在视图上，并且该视图没有指定with check option选项
2.不能指定before|after选项
3.for each row是可选的，instead of触发器只能在行级上触发
4.没有必要对一个表的视图创建instead of触发器。
语法格式：
create [or replace ]trigger 触发器名
instead of
{insert|delete|update[ of 列1，列2...]}
[or {insert|delete|update [ of 列3，列4...]} ...]
on 视图名
for each row
[when 逻辑表达式]
pl/sql 或 子程序调用;
- 系统触发器
-
触发器的管理
查询触发器
可以使用数据字典中的USER_TRIGGERS、ALL_TRIGGERS、DBA_TRIGGERS视图来查询触发器的定义及其状态信息。
查看USER_TRIGGERS视图的字段：DESC user_triggers；
禁用或启用触发器
1.禁用、启用触发器的语法格式是：ALTER TRIGGER 触发器名 DISABLE|ENABLE;
2.一次全部禁用或启用一个表上的触发器：ALTER TABLE 表名 DISABLE|ENABLE ALL TRIGGERS；
重新编译触发器
当修改表的结构时，会使该表上的触发器变为invalid状态。若要重新发挥作用，需重新编译。
语法格式：alter trigger 触发器名 COMPILE；
删除触发器
DROP TRIGGER 触发器名;
上一页1...-1-1-1-1-1-1-1...-1下一页