PL/SQL编程之美

--PL/SQL之于SQL如同DOS批处理之于DOS命令，Linux Shell脚本之于Linux命令

/*

PL/SQL算是4GL(第四代语言)，3GL(如C，C++，JAVA等)

PL/SQL具有如下特点：

1.后端开发工具

2.更好的性能

3.过程化

4.可移植性和兼容性

5.可维护性

6.易用性

7.可处理运行时错误

8.大量的内置程序包

*/

--基本语法，包括程序代码，变量声明，错误处理，过程，函数，触发器等

declare

... --声明变量，函数，过程等的声明，非必需

begin

... --程序执行主体，由plsql语句组成，必需

exception

... --异常处理部分，非必需

end; --结束标志

--匿名块

declare

sid char(6) := 'SH6230'; --':='赋值，每个语句之间以分号分隔，变量 类型 [:= 赋值]

sname varchar2(20) := '张小可';

ssex char(1) := 'M';

sage number := 25;

begin

insert into student values(sid,sname,ssex,sage,sphone);

dbms_output.put_line('数据插入成功.');

end;

--判断结构

--if..then..elsif..then..else..end if

declare

grade char(1);

begin

grade := 'C';

if grade = 'A' then

dbms_output.put_line('GOOD');

elsif grade = 'B' then

dbms_output.put_line('OK');

else

dbms_output.put_line('NO GRADE');

end if;

end;

--case when..then..when..then..else..end case

declare

grade char(1);

begin

grade := 'A';

case

when grade = 'A' then

dbms_output.put_line('GOOD');

when grade = 'B' then

dbms_output.put_line('OK');

else

dbms_output.put_line('NO GRADE');

end case;

end;

--循环结构

--loop..exit when..end loop

declare

nums integer;

begin

dbms_output.put_line('loop');

nums := 1;

loop

dbms_output.put_line('The Number is ' || nums); --'||'字符串连接

nums := nums + 1;

exit when nums = 11;

end loop;

end;

--while..loop..end loop

declare

nums integer;

begin

dbms_output.put_line('while..loop');

nums := 1;

while nums

dbms_output.put_line('The Number is ' || nums); --'||'字符串连接

nums := nums + 1;

end loop;

end;

--for..loop..end loop

declare

nums integer; --for循环中使用的变量可以不用提前申明

begin

dbms_output.put_line('for..loop');

for nums in 1..10 loop --nums从1到循环到10，如果加上reverse参数（for nums in reverse 1..10 loop），则为倒序循环，即10到1

dbms_output.put_line('The Number is ' || nums); --'||'字符串连接

end loop;

end;

--记录类型，在SQL中除基本类型外的特殊类型，相当于C语言中的结构体

--基本用法

declare

type StudentRecord is record

(

sid char(6),

sname varchar2(10),

ssex char(1),

sage integer,

sphone varchar2(12)

);

sr StudentRecord; --实例化记录类型

begin

select * into sr from student where rownum = 1; --select..into 为变量赋值（将查询结果赋给变量）

dbms_output.put_line(sr.sname);

end;

--%type 申明变量，希望该变量和数据库中某一个表的某一个属性的类型和长度都相同的话，需要用到%type,例如 sname student.sname%type;

declare

type StudentRecord is record

(

sid student.sid%type,

sname student.sname%type,

ssex student.ssex%type,

sage student.sage%type,

sphone student.sphone%type

);

sr StudentRecord; --实例化记录类型

begin

dbms_output.put_line('%type');

select * into sr from student where rownum = 1; --select..into 为变量赋值（将查询结果赋给变量）

dbms_output.put_line(sr.sname);

end;

--%rowtype 希望申明的变量和某数据表中的所有属性的类型和长度都相同的话，需要使用%rowtype，例如，上面的声明可以简写为 sr student%rowtype;

declare

sr student%rowtype; --实例化记录类型

begin

dbms_output.put_line('%rowtype');

select * into sr from student where rownum = 1; --select..into 为变量赋值（将查询结果赋给变量）

dbms_output.put_line(sr.sname);

end;

--存储过程

--存储过程是一个命名的PL/SQL块，被存储在数据库中，并且可以被其他PL/SQL块使用

--存储过程基本语法

create [or replace] procedure 存储过程名称

(

[arg1 [in|out|in out]] 数据类型,

[arg2 [in|out|in out]] 数据类型,

......

)

is | as

声明部分

begin

执行部分

exception

异常处理部分

end;

--参数类型

--in 接受来自存储过程调用程序传递的参数

--out 表明该参数不接受值，而是将值传递给调用存储过程的程序

--in out 既可以接受输入值，又会将值输出给调用程序

--创建存储过程

create or replace procedure myproc

(

input in varchar2, --这里不需要指定字符串长度，只需指出类型即可，即不用写成varchar2(size),加上size调用时会报错

print out varchar2,

inout in out varchar2

)

is --也可以使用as关键字

myvar varchar2(50);

begin

myvar := input;

print := 'This is Param for out';

inout := 'This is Param for in and out';

dbms_output.put_line('过程内部变量myvar的值为：' || myvar);

end;

--执行存储过程

declare

param_in varchar2(50);

param_out varchar2(50);

param_in_out varchar2(50);

begin

param_in := '传递该值给input参数.';

myproc(param_in,param_out,param_in_out);

dbms_output.put_line('param_out的值来自于参数print：' || param_out);

dbms_output.put_line('param_in_out的值来自于参数inout参数：' || param_in_out);

end;

--创建带默认值的存储过程

create or replace procedure myproc1

(

input varchar2, --这里不需要指定字符串长度，只需指出类型即可，即不用写成varchar2(size),加上size调用时会报错

print varchar2 := 'sss', --参数可以加上默认值，写法两种：1.赋值print out varchar2 := 'sss';2.default关键字print out varchar2 default ’sss'

inout varchar2 default 'aaa'

)

as --也可以使用is关键字

begin

dbms_output.put_line('input的值为：' || input);

dbms_output.put_line('print默认值为：' || print);

dbms_output.put_line('inout值为：' || inout);

end;

--调用执行存储过程

declare

param_in varchar2(50);

begin

param_in := '传递该值给input参数.';

myproc1(param_in); --默认参数

myproc1(param_in,'hhh','kkk'); --顺序赋值参数

myproc1(param_in,inout=>'hhh',print=>'kkk'); --改变顺序赋值

end;

--创建带异常处理的存储过程

create or replace procedure myproc2

(

input varchar2, --这里不需要指定字符串长度，只需指出类型即可，即不用写成varchar2(size),加上size调用时会报错

print varchar2 := 'sss', --参数可以加上默认值，写法两种：1.赋值print out varchar2 := 'sss';2.default关键字print out varchar2 default ’sss'

inout varchar2 default 'aaa'

)

as --也可以使用is关键字

begin

dbms_output.put_line('input的值为：' || input);

dbms_output.put_line('print默认值为：' || print);

dbms_output.put_line('inout值为：' || inout);

exception

WHEN others THEN

dbms_output.put_line('input值为null');

end;

--调用执行存储过程

declare

param_in varchar2(50);

begin

myproc2(param_in); --默认参数

myproc2(param_in,'hhh','kkk'); --顺序赋值参数

myproc2(param_in,inout=>'hhh',print=>'kkk'); --改变顺序赋值

end;

--需要补充异常处理

--函数

--函数基本语法，和存储过程基本一致，将procedure替换为function，增加return返回值关键字

create [or replace] function 函数名称

(

[arg1 [in|out|in out]] 数据类型,

[arg2 [in|out|in out]] 数据类型,

......

)

return 返回值类型

is | as

声明部分

begin

执行部分

exception

异常处理部分

end;

--创建函数

create or replace function myfunc

(

input in integer, --这里不需要指定字符串长度，只需指出类型即可，即不用写成varchar2(size),加上size调用时会报错

print out varchar2,

inout in out integer

)

return integer

is --也可以使用as关键字

results integer;

begin

results := input + inout;

print := 'This is Param for out';

inout := 200;

return results;

end;

--调用执行函数

declare

results integer;

param_in integer := 50;

param_out varchar2(50);

param_in_out integer := 40;

begin

results := myfunc(param_in,param_out,param_in_out);

dbms_output.put_line('param_out的值为：' || param_out);

dbms_output.put_line('param_in_out的值为：' || param_in_out);

dbms_output.put_line('函数返回值为：' || results);

end;

--游标

/*

在PL/SQL块中执行SELECT,INSERT,UPDATE,DELETE语句时，oracle会在内存中为其分配上下文区（context area），游标是指向该区

的指针，它为应用程序提供了一种对具有多行数据的查询结果即中每行数据进行单独处理的方法，是设计交互式应用程序的常用编程接口。

游标分为显式游标和隐式游标。显式游标是由用户申明和操作的一种游标，隐式游标是oracle为所有数据操纵语句自动申明和操纵的一种游标

，在每个会话中，可以同时打开多个游标，其数量由参数OPEN_CURSORS定义。

显示游标的操作及其过程如下：

1.申明游标，在使用游标前需要先申明游标，语法格式如下

cursor 游标名称(参数 参数类型) is select 语句; 注意：游标参数为可选

2.打开游标，为了执行游标中的select语句，查询并得到一个结果集，需要打开游标，格式：open 游标名称;

3.提取游标，打开游标后，游标的指针指向结果集的第一行，如果需要提取结果集中的数据，就需要提取游标，格式：

fetch 游标名称 into 变量;

4.关闭游标，当提取和处理完游标后，应该及时关闭游标，以释放它所占用的系统资源，格式：close 游标名称;

*/

--游标的基本用法

declare

c_sname student.sname%type;

c_sage student.sage%type;

cursor mycursor(param_sid student.sid%type) is

select sname,sage from student where sid=param_sid;

begin

open mycursor('SH6116');

fetch mycursor into c_sname,c_sage;

dbms_output.put_line('姓名：'||c_sname||', 年龄：'||c_sage);

close mycursor;

end;

/*

游标属性：

%ISOPEN 当游标变量打开时，%ISOPEN属性为true，否则为false

%FOUND 当游标被打开并且在执行fetch之前，%FOUND的值为NULL，执行fetch语句后，如果有返回记录，%FOUND的值为true，如果没有返回记录，则%FOUND的值为false

%NOTFOUND 和%FOUND刚好相反，当游标被打开并且在执行fetch之前，%NOTFOUND的值为NULL，执行fetch语句后，如果有返回记录，%NOTFOUND的值为false，如果没有返回记录，则%NOTFOUND的值为true

%ROWCOUNT 该属性用来返回迄今为止已经从游标中取出的记录数目。

在游标被打开而没有执行fetch语句前，%ROWCOUNT的值为0，执行fetch语句后，没返回一个记录，%ROWCOUNT的值就增加1

*/

--利用游标属性完成更为复杂的程序

declare

c_sname student.sname%type;

c_sage student.sage%type;

c_sphone student.sphone%type;

cursor mycursor1 is

select sname,sphone from student where sage

begin

c_sage := 25;

if mycursor1%ISOPEN =false then

dbms_output.put_line('游标正在打开......');

open mycursor1;

end if;

if mycursor1%ISOPEN then

loop

fetch mycursor1 into c_sname,c_sphone;

if mycursor1%found then

dbms_output.put_line('当前记录：'||mycursor1%rowcount);

dbms_output.put_line('姓名：'||c_sname||', 电话：'||c_sphone);

else

dbms_output.put_line('未发现更多记录...');

end if;

exit when mycursor1%NOTFOUND;

end loop;

close mycursor1;

end if;

end;

--触发器

--触发器是存储在数据库中的过程，当数据库中 某些事件发生的时候，这个过程会触发，或者说这个过程会自动运行而无须用户来干涉

--可以对数据库中的表创建相应的触发器，当表被插入，删除，修改时，触发器就自动执行某些PL/SQL语句块

--基本语法

create or replace trigger 触发器名称

verb_list

on 触发器作用的表名

[referencing ||]

[for each row]

[when (condition)]

PL/SQL程序块

/*

其中，verb_list语法如下：

[or verb_list]

before|after 触发器在动作insert|update|delete发生之前还是之后被触发

referencing 允许在plsql块或者when条件中指定别名

OLD 是sql语句执行前引用字段的值时用的别名

NEW 是sql语句执行后引用字段的值时用的别名

PARANT 是当前表的父表的别名

for each row 指定该触发器为记录级触发器

when 指定触发器被触发的约束条件

*/

--实例一：行级触发器

create or replace trigger updatestudent

before update on student

for each row

begin

dbms_output.put_line('New Value is '|| :new.sname);

dbms_output.put_line('Old Value is '|| :old.sname);

end;

--若没有使用for each row，则该触发器不属于行级触发器，而属于表级触发器，表级触发器不能使用:new和:old

update student set sname='liuhan' where sage = 26;

--实例二：列级触发器

create or replace trigger updatesphone

before update of sphone on student

begin

dbms_output.put_line('Sphone is updated ...');

end;

--该触发器为列级触发器，只针对student表中的sphone这一列进行更新时才会触发该过程，对student表中其他列进行更新，不会触发该过程

--实例三：创建一个触发器监控对student表的所有DML操作

create or replace trigger triggerall

after update or delete or insert on student

for each row

declare

operate char(10);

begin

if inserting then

operate := 'Insert';

elsif updating then

operate := 'Update';

else

operate := 'Delete';

end if;

dbms_output.put_line('Time:'||to_char(sysdate,'YYYY-MM-DD HH24:MI:SS'));

dbms_output.put_line('Operation:'||operate);

dbms_output.put_line('New name is '||:new.sname);

dbms_output.put_line('Old name is '||:old.sname);

end;

update student set sname='刘函' where sage=26;

delete from student where sid='SH6115'; --from 关键字可以省略

--事务

--对数据的保护包含两个方面的内容：

--1.防止合法用户的操作对数据库造成意外的破坏

--2.防止非法用户的操作对数据库造成故意的破坏

--事务具有如下性质：

--1.原子性

--2.一致性

--3.隔离性

--4.保持性

--实例

--创建money表

create table money

(

Account varchar(20),

Balance number

)

--在money表中插入两行数据

insert into money values('Jack',1000);

insert into money values('Rose',2000);

--查询插入结果

select * from money;

--执行jack转账1000给rose

update money set balance=balance-1000 where account='Jack';

update money set balance=balance+1000 where account='Rose';

--查看转账结果

select * from money;

--回滚到转账前的事务

rollback

--增加余额小于10不能转账的约束

alter table money drop constraint CK_money_balance check (balance>=10);

--再次执行jack转账1000给rose

update money set balance=balance-1000 where account='Jack';

update money set balance=balance+1000 where account='Rose';

--查看转账结果

select * from money;

--事务理解

--设置手动提交

set autocommit off

--设置自动提交

set autoommit on

--设置回滚点

savepoint sp1

--回滚到设置的回滚点

rollback to sp1