PL/SQL是Oracle下的脚本开发语言,是为了解决数据进行复杂处理而设计的。
在SQL语言的基础之上添加了第三代语言的循环、分支等结构
使用PL/SQL可以带来一定的好处,即效率的提高
存储过程、函数、触发器实质上是在匿名块外面加了一层帽子,其中存储过程又相对重要些
1、匿名块:
PL/SQL的匿名块由四个部分组成
1、declare – 可选 声明各种变量或游标的地方。
2、begin – 必要 开始执行语句。
--单行注释语句用两个连在一起的‘-’表示。
/*多行注释语句,
可以换行*/
3、exception – 可选 出错后的处理。
4、end; – 必要(请注意end后面的分号)结束。
变量必须在declare语句块中声明
PL/SQL中的简单变量类型:
l binary_integer:整数,主要用来计数而不是用来表示字段类型
l number:数字类型
l char:定长字符串
l varchar2:变长字符串
l date:日期
l long:长字符串,最长2GB
l boolean:布尔类型,可以取值为true、false和null值
注:如果声明变量的类型想和某一表字段或另一变量一样,且可以动态的跟踪字段类型的变化,就使用字段或变量的%type属性
PL/SQL中的组合变量:(已经被淘汰了,了解即可!!!)table 类似数组、record 类似结构体
table变量的声明包含两部分,第一部分声明一个包含着具体类型的table类型,然后再用这个具体的类型去声明一个变量
type type_name is table of data_type index by binary_integer;
identifier type_name;
record变量的声明语法规则
type type_name is record
(field_name1 data_type [not null {:= | default } expr],
field_name2 data_type [not null {:= | default } expr],
…);
identifier type_name;
如果声明变量的类型想和某一表结构一样,且可以动态的跟踪表
结构的变化,就使用%rowtype属性
例子:
PL/SQL中的赋值使用 :=,“=”被用作比较操作符
赋值语句中有一些规则需要遵守:
l 字符类型必须使用单引号
l 不可以使用使用组函数
l 可以使用数学操作符或字符串连接操作符
1、PL/SQL中的select语句:
在PL/SQL中,select语句有且只能返回一条记录,否则程序就会出错
select语句中必须加into子句,将查询到得数据放入变量中
2、PL/SQL中的DML语句(insert、update、delete):
l 可以直接使用,跟原先的DML语句写法一样
l DML语句起作用或者回退的话,需要显式的调用commit或者rollback
l sql%rowcount属性来记录最后一条SQL语句影响了多少条记录
Declare
v_deptno emp.deptno%type := 10
Begin
Update emp_copy set sal = sal * 2 where deptno = v_deptno;
Dbms_output.put_line(sql%rowcount||‘条记录被修改’);
End;
3、PL/SQL中的DDL语句:
l 在PL/SQL中不能直接运行DDL语句,如果要运行,需要使用execute immediate语句,且必须将DDL语句用单引号引起来(注意分号的位置):
Begin
Execute immediate 'create table test(a varchar2(20), b number(3))';
End;
PL/SQL中的条件判断:
if condition then statements;
[elsif condition then statements;
[else statements;]
end if;
PL/SQL中的循环语句:
PL/SQL程序中共有三种不同的循环:
l loop循环:
loop
statement
…
exit [when condition];
end loop;
l for循环:
for index in [reverse]
lower .. upper loop
statement;
…
end loop;
在for循环中,不需要对循环变量(index)进行声明,oracle会默认把它当成binary_integer来使用
l while循环:
while condition loop
statement;
…
end loop;
游标:
游标就是指在某个结果集上的指针,通过这个指针的移动,我们得以遍历整个结果集,这样我们就可以一次取出多条记录,然后按照程序的逻辑一条一条的进行处理。
游标的使用步骤:
1. 声明游标
2. 打开游标
3. 处理游标中的数据
4. 关闭游标
最常用的游标属性有以下四个:
1) %isopen,boolean类型变量,用来代表游标是否打开。
2) %notfound,boolean类型变量,如果最近的fetch语句没有返回一条记录,取true。
3) %found,boolean类型变量,如果最近的fetch语句取到了记录,取true。
4) %rowcount,number类型变量,用来代表目前fetch到的记录的总行数。
游标遍历:
l Loop循环遍历游标 利用loop循环和%notfound属性实现游标的遍历
l While循环遍历游标 利用while循环配合%found属性实现游标的遍历
l for循环遍历游标 利用for循环遍历游标,不需要打开游标,也不需要关闭,甚至不用声明循环变量,最简单
带参数的游标:1、并不能确定select语句中的限制条件,需要留到运行时才能确定整条select语句 2、增加程序的弹性
使用游标更新结果集:
l select语句后面添加for update来提示oracle锁定记录以便进行更新
l 用where current of 来指明操作是添加在当前游标所指向的记录上。
使用游标更新结果集:
有时候取出数据并不只是为了查阅,也可能进行更新或者删除,游标用做这种用途的时候必须进行锁定:
通过在select语句后面添加for update来提示oracle锁定记录以便进行更新,然后用where current of c来指明操作是添加在当前游标所指向的记录上。
2、存储过程(重点):
pl/sql匿名块虽然可以一下运行多条语句,或者通过游标访问多条记录,但是整个块只能运行一次,使用不方便,因此数据库提供了给匿名块起名字的办法,存储过程就可以看作一个带名字的PL/SQL语句块,同时可以传递参数,增加了原来pl/sql匿名块的灵活性!:跟匿名块的不同就是用create or replace procedure is 代替了匿名块的declare
定义存储过程的语法声明如下:
create [or replace] procedure procedure_name is
variable declaration;
begin statement;
[exception] statement;
end;
l 编译出错时,存储过程也会创建
带参数的存储过程:参数模式
1. IN,也就是默认模式,表示这个参数值会从调用环境中传进过程。
2. OUT,传出模式,表示这个参数的值是从过程传出到调用环境。
3. IN OUT,传入传出模式,这个参数的值在调用环境和过程间互相传递。
存储过程的例子1:
调用该存储过程:
例子2:
传一个员工号来给这个员工涨工资,规则如下:
如果是10部门的员工就上涨10%
如果是20部门的员工就上涨20%
如果是30部门的员工就上涨30%
其它部门员工不变
但是要求最高的工资也不能超过5000
在一个存储过程中打开一个游标,在游标循环中调用上边的存储过程,一次把所有的员工都按照上边的规则上涨。
3、函数:
函数构建的语法规则如下:
create [or replace] function function_name [(parameter, …)]
return datatype is
variable declaration;
begin
statement;
…
[execption
statement;
…]
end;
函数的参数只有IN模式,只能够由调用环境传入,但可以使用返回值把结果传回到调用环境中。函数的返回类型不能够是PL/SQL数据类型,如table、record等,而只能是oracle建表时所支持的数据类型,例如number、date等,另外函数中也不能出现DML、DDL等语句.
书写一个函数,用来传一个员工号返回它的部门号
4、触发器:
触发器是数据库发生某个操作时自动运行的一类的程序
用于保持数据的完整性或记录数据库操作信息方面
触发器不能够被直接调用,必须依附在某张表上,只能够在某些事件发生时被触发,也就是系统自动进行调用。
触发器的构建语法
create [or replace] trigger trigger_name
before|after
event1 [ or event2 or event3 …]
on table_name [for each row]
begin
statement;
…
end;
event通常是insert、delete或update等DML操作
触发器分为语句级触发器和行级触发器
1、语句级触发器是指每执行一条DML语句,该触发器就执行一次
2、行级触发器是指每个DML操作影响几条记录,就会触发几次
行级触发器中由于涉及到了每条记录的数据变动,所以对于每条记录的数据来说就有新值和旧值之分。用关键字:NEW和:OLD来代表新的记录和旧的记录
由于有外键约束的存在,当执行下面的更新语句是会出错的:
update dept set deptno = 99 where deptno = 10;
因为deptno为10的这条记录被emp表中的记录所参照,要修改10这个值(旧的值),必须也同时将emp表中的deptno为10的记录修改为新的值即99,。使用触发器,就能顺利执行。