PL/SQL程序
1.PL/SOL程序块
整个PL/SQL块分三部分：声明部分、执行部分、异常处理部分；
示例：
declare --变量声明
v_sno varchar2(10) := ‘04001’;
v_cno varchar2(10) :=‘001’;
v_grade number := 90;
begin --程序入口
insert into sc values (v_sno,v_cno,v_grade);
commit; //insert update和delete的提交步骤
end;
2.特殊类型
%type ：已声明的变量和前边给出的变量类型相同，若前边变量类型改变，该声明变量自动改变
declare
n sc.sno%type;
m sc.cno%type;
x sc.grade%type;
y x%type;
%rowtype : 返回一个记录类型，其数据类型和数据库表的数据结构相一致。
declare
a1 sc%rowtype;
begin
select *
into a1
from sc
where sno=‘04001’ and cno=‘003’ ；
end; //a1--一个类似于sc的数据结构；
3.运算符
一般运算符
：= 赋值号 => 关系号(一般用于给形参赋值) .. 范围运算符 || 字符连接符
关系运算符
<> != ~= ^= 不等于
4.控制语句
①顺序语句：
goto语句：无条件跳转到指定的标号
null语句：说明“不用做任何事情”的意思，相当于一个占位符
②条件语句:
if 语句
if <布尔表达式> then
pl/sql 和 sql语句
elsif < 其它布尔表达式> then
其它语句
elsif < 其它布尔表达式> then
其它语句
else
其它语句
end if;
③循环语句:
loop…end loop语句
while语句
for循环语句
loop
要执行的语句;
whexiten <条件语句>
end loop;
while <布尔表达式> loop
要执行的语句;
end loop;
if-else示例：
declare
n sc.sno%type;
m sc.cno%type;
x sc.grade%type;
begin
select sno,cno,grade
into n,m,x
from sc
where sno='105698' and cno='2092508'; //对一个学生的一门科目进行成绩评级输出
if x<60 then
dbms_output.put_line(n||','||m||','||x||','||'不及格');
elsif x>=60 and x<80 then
dbms_output.put_line(n||','||m||','||x||','||'及格');
elsif x>=80 and x<90 then
dbms_output.put_line(n||','||m||','||x||','||'良好');
else
dbms_output.put_line(n||','||m||','||x||','||'优秀');
end if;
exception
when no_data_found then dbms_output.put_line('没有查询结果');
end;
for循环示例：
begin
for x in 1..10 loop
dbms_output.put_line('x 的当前值为:'||x);
end loop;
end;
游标
1.声明游标
cursor <游标名>[(<游标参数>[, <游标参数>]…)]
is <查询语句>;
游标参数只能为输入参数，其格式为：
parameter_name [in] datatype [{:= | default} expression]
2. 打开游标
open <游标名> [ ( [ <参数名> => ] <实参> [,…n] ) ];
3. 提取游标
fetch <游标名> into {variable_list | record_variable };
4.游标关闭
当提取和处理完游标结果集合数据后，应及时关闭游标，以释放该游标所占用的系统资源，并使该游标的工作区变成无效，不能再使用fetch 语句取其中数据。
close <游标名>;
代码改进
declare
n sc.sno%type;
m sc.cno%type;
x sc.grade%type;
cursor c1 is //游标声明
select sno,cno,grade
from sc
where sno='04002' ; //存在同一个人的多个课程信息和成绩
begin
open c1; //游标打开
loop //循环
fetch c1 into n,m,x; //游标提取
exit when c1%notfound; 跳出循环的条件
if x<60 then
dbms_output.put_line(n||','||m||','||x||','||'不及格');
elsif x>=60 and x<80 then
dbms_output.put_line(n||','||m||','||x||','||'及格');
elsif x>=80 and x<90 then
dbms_output.put_line(n||','||m||','||x||','||'良好');
else
dbms_output.put_line(n||','||m||','||x||','||'优秀');
end if;
end loop;
close c1;
end;
……
游标属性
%found：布尔型属性 游标刚打开，指针指向第一行元组之前时，值为null； 游标指针指向查询结果集时，值为true； 其它，值为false。
%notfound：布尔型属性，与%found相反。
%isopen：布尔型属性，当游标已打开时返回 true。
%rowcount：数字型属性，返回已从游标中读取的记录数 。
存储过程
1.创建存储过程
create [or replace] procedure <过程名>
[(<参数名> <参数类型> <数据类型>[default <默认值>][,…n])]
{ is | as }
[<变量声明> ]
begin <过程体>
end[过程名];
注：参数类型：
in：输入参数，参数在执行时被赋值，将值传入到过程体。
out：输出参数，参数在过程中将被赋值，可以传给过程体的外部。
in out：输入输出参数，既可以向过程体传值，也可以在过程体中赋值。
2. 调用存储过程
begin
<过程名>[([<参数名>=>]<实参>[,…n])];
end;
示例1：
创建存储过程，查询信息学院学生的考试成绩，列出学生的姓名、课程名和成绩，将查询结果在过程体中输出
//过程块
create or replace procedure p_select_xx
is
v_sname student.sname%type;
v_cname course.cname%type;
v_grade sc.grade%type;
cursor c1 is select sname, cname,grade from student,sc,course
where student.sno = sc.sno and course.cno = sc.cno and scollege = '信息工程学院';
begin
open c1;
loop
fetch c1 into v_sname,v_cname,v_grade;
exit when c1%notfound;
dbms_output.put_line(v_sname||'，'||v_cname||'，'||v_grade);
end loop;
close c1;
end;
//调用过程块
begin
p_select_xx;
end;
示例2：
创建存储过程，查询指定学院学生的考试成绩，列出学生的姓名、课程名和成绩，将查询结果在过程体中输出
create or replace procedure p_select_xy(v_college in student.scollege%type)
is
v_sname studnet.sname%type;
v_cname course.cname%type;
v_grade sc.grade%type;
cursor c1 is select sname, cname,grade from student,sc,course
where student.sno = sc.sno and course.cno = sc.cno and scollege = v_college;
begin
open c1;
loop
fetch c1 into v_sname,v_cname,v_grade;
exit when c1%notfound;
dbms_output.put_line(v_sname||'，'||v_cname||'，'||v_grade);
end loop;
close c1;
end;
//调用
begin
p_select_xy(&输入学院名称); //调用程序会显示要求输入学院名称，注意在输入时加单引号！！！
end;
示例3：
创建存储过程，统计指定课程的平均成绩和选课人数，将统计结果用输出参数返回给调用的主程序。
create or replace procedure p1
(v_cno in sc.cno%type,avg_grade out number, total out number) is
begin
select avg(grade),count(sno)
into avg_grade,total //2个输出参数
from sc
where cno=v_cno;
end ;
//调用
declare
a1 varchar2(10):='001’;
a2 number;
a3 number;
begin
p1(a1,a2,a3);
//或者
/*p1(avg_grade=>a2,total=>a3,v_cno=>a1);*/
dbms_output.put_line(a2||'，’||a3);
end;
3. 删除存储过程
drop proceurde <存储过程名>
存储过程小结
在创建存储过程中，需要考虑参数的种类和个数；
另外，当过程体中有查询语句时，如果查询结果为多行元组，需要游标；当查询结果为一行元组，则不需要游标，直接使用into语句即可；
触发器
触发器是用户定义在表上的一类由事件驱动的特殊的存储过程，当某个Oracle事件发生时系统自动地运行，所以运行触发器就叫触发。
存储过程通过其他程序来启动运行或直接启动运行，而触发器是由一个事件来启动运行，即触发器是当某个事件发生时自动地隐式运行。
触发器不能接收参数。
1.创建触发器语法格式
create [or replace] trigger <触发器名称>
{before | after | instead of} <触发事件>
//触发事件--insert、update、delete、create、alter、drop等
on {<表名>|<视图名>}
[for each row] [when <触发条件>] //for each row行级触发器
declare
[变量声明;]
begin
<触发动作体>
end;
2.修饰符
:new修饰符，如果激活触发器的语句为Insert，Update，被插入的数据与被更新后的数据在触发体中由:new引用。
:old修饰符，如果激活触发器的语句为Delete，Update，被删除的数据与被更新前的数据在触发体中由:old引用。
示例： 创建触发器实现如下约束：当修改S表中学生学号时，级联更新相应的选课信息。
create or replace trigger tri_update_cascade
after update
on s
for each row
begin
update sc
set sno=:new.sno
where sno=:old.sno;
end tri_update_cascade;
创建触发器实现：当在教师工资表中插入元组或者修改工资表时，要求教授的工资不得低于4000元，如果低于4000元，自动改为4000元。
create or replace trigger zdxg_sal
after insert on sal_teach
for each row
declare
-- local variables here begin
if :new.job='教授' and :new.sal<4000 then
update sal_teach
set sal=4000
where eno=:new.eno;
end if;
end zdxg_sal; 错误
//原因：如果是后触发，触发体中不能对触发器所在的表做修改和查询操作。
create or replace trigger zdxg_sal
before insert or update on sal_teach
for each row
declare
-- local variables here
begin
if :new.job='教授' and :new.sal<4000 then
:new.sal:=4000;
end if; end zdxg_sal;
3.删除触发器
语法格式： drop trigger <触发器名>;
4.总结
行级触发器：当DML语句对每一行数据进行操作时都会引起该触发器的运行。
语句级触发器：无论DML语句影响多少行数据，它所引起的触发器都仅执行一次。
替换触发器：定义在视图之上的触发器。
用户事件触发器：与DDL操作或用户登录、退出数据库等事件相关的触发器。
系统事件触发器：在Oracle数据库系统的事件中触发的触发器，如Oracle实例的启动与关闭。
①一个表上同一时间、同一事件、同一类型的触发器只能有一个;
②在触发器的执行部分只能用DML语句(select, insert, update, delete)，不能使用DDL语句(create, alter, drop);
③在触发语句中不能包含事务控制语句(commit, rollback, savepoint)。在触发器主体中调用的任何过程、函数，都不能使用事务控制语句。//存储过程可以包括；
