-
-使用记录
- -因为记录没有自己的值,因此不能测试记录是否为 null、
- -是否相等或者不相等。以下语句是非法的:
IF course_rec IS NULL THEN ……
IF course_rec1 = course_rec2 THEN ……
- -用户定义的记录
- -创建用户定义记录的通用语法如下所示(方括号中的内容是可选的):
type type_name is record
(field_name1 datatype1 [ not null] [: = 默认值] ,
field_name2 datatype2 [ not null] [: = 默认值] ,
……
field_nameN datatypeN [ not null] [: = 默认值]) ;
record_name type_name;
- -了解:
- -记录的兼容性
- -用户定义记录的约束及默认值
- -封装记录的使用
- -包含记录的集合。重点
declare
- -声明游标
cursor name_cur is
select first_name,last_name
from student
where rownum < = 4;
- -声明索引表集合类型.其中放基于游标的记录。
- -注意:name_cur%rowtype就是记录类型的名字
type name_type is table of name_cur%rowtype
index by pls_integer;
- -声明集合变量
name_tab name_type;
v_index pls_integer : = 0;
begin
- -遍历游标,将拿到 4条的记录放到集合中
for name_rec in name_cur loop
v_index : = v_index + 1;
name_tab(v_index) : = name_rec;
end loop;
- -从集合中取出记录并打印
for i in 1..name_tab. count loop
dbms_output.put_line(name_tab(i).first_name);
dbms_output.put_line(name_tab(i).last_name);
end loop;
end;
- -创建使用触发器
- -数据库触发器是存储在数据库中的命名PL / SQL语句块,
- -当触发事件发生时它们会隐含地执行。
- -触发事件可以是如下任何一种:
- -DML语句(如 INSERT、 UPDATE或者 DELETE)。
- -DDL语句(如 CREATE或者 ALTER)。
- -系统事件,如数据库启动或者关闭
- -用户事件,如登录和注销。
- -创建触发器的通用语法如下所示(中括号中的保留字是可选的):
CREATE [ OR REPLACE] TRIGGER trigger_name
{ BEFORE| AFTER} Triggering_event ON table_name
[ FOR EACH ROW]
[FOLLOWS another_trigger]
[ENABLE /DISABLE]
[ WHEN condition]
DECLARE
declaration statements
BEGIN
executable statements
EXCEPTION
exception -handling statements
END;
- -写触发器的限制:
- - 1)不要在触发器中使用 commit、 rollback等事务控制语句。
- -因为触发器执行的操作是和客户端在同一个事务中执行的,
- -事务的结束应该由客户端来控制。这个规则的一个例外是:
- -在触发器中使用自治事务
- - 2)不要在触发器中使用long类型的变量
- - before触发器。
- -是指触发器的执行时间是在dml操作执行之前。
- -示例 1:这个触发器针对STUDENT表的 INSERT语句之前执行,
- -填充STUDENT_ID、CREATED_DATE、MODIFIED_DATE、
- -CREATED_BY和MODIFIED_BY等列
create or replace trigger student_bi
before insert on student
for each row
begin
- -直接给新行的 5个列赋值
: new.student_id : = student_id_seq.nextval;
: new.created_by : = user;
: new.created_date : = sysdate;
: new.modified_by : = user;
: new.modified_date : = sysdate;
dbms_output.put_line( '触发器执行完毕');
end student_bi;
- -客户端测试语句:
INSERT INTO student(first_name, last_name, zip, registration_date) VALUES ( 'John', 'Smith', '00914', SYSDATE);
- -观察某些列值会被触发器提供的值覆盖掉
INSERT INTO student(student_id, first_name,
last_name, zip,registration_date, created_by,
created_date, modified_by,modified_date)
VALUES ( 800, 'John', 'Smith', '00914', SYSDATE,
'张三', SYSDATE, '张三', SYSDATE);
- - after触发器:在dml语句执行完后再执行
- -示例 2:建立针对INSTRUCTOR表的 UPDATE或者
- - DELETE操作执行之后触发的触发器。该触发器
- -在statistics表中记录用户对INSTRUCTOR表进行的最后修改
- -信息
create table statistics(
TABLE_NAME VARCHAR2( 30),
TRANSACTION_NAME VARCHAR2( 10),
TRANSACTION_USER VARCHAR2( 30),
TRANSACTION_DATE DATE
);
create or replace trigger instructor_aud
after update or delete on instructor
declare
- -
v_type varchar2( 10);
begin
- -确定用户的操作
if updating then
v_type : = 'UPDATE';
elsif deleting then
v_type : = 'DELETE';
end if;
- -更新统计表
update statistics
set transaction_user = USER,
transaction_date = SYSDATE
WHERE table_name = 'INSTRUCTOR'
AND transaction_name = v_type;
- -判断更新是否成功,如果没有,则插入一行
insert into statistics
values( 'INSTRUCTOR', v_type, USER, SYSDATE);
dbms_output.put_line( '记录完毕');
end instructor_aud;
- -客户端测试
update instructor
set first_name = 'zs'
where instructor_id = 107;
delete from instructor
where instructor_id = 110;
- -可看到两条记录
select * from statistics;
- -客户端回滚
rollback;
- -看到触发器所做的工作也被撤销了
select * from statistics;
- -问题:当客户端撤销事务时,如何保留触发器所做的
- -工作而不被同时撤销掉?
- -在触发器中使用自治事务
- -自治事务:
- -是由其它事务(通常被称为主事务)发起的独立事务。
- -也就是说,自治事务也许会执行多个DML语句,
- -并且提交或者回滚操作,而不会提交或者回滚主事务
- -执行的DML语句
- -修改上面的触发器,使用自治事务
create or replace trigger instructor_aud
after update or delete on instructor
declare
- -
v_type varchar2( 10);
- -声明使用自治事务
pragma autonomous_transaction;
begin
- -确定用户的操作
if updating then
v_type : = 'UPDATE';
elsif deleting then
v_type : = 'DELETE';
end if;
- -更新统计表
update statistics
set transaction_user = USER,
transaction_date = SYSDATE
WHERE table_name = 'INSTRUCTOR'
AND transaction_name = v_type;
- -判断更新是否成功,如果没有,则插入一行
if sql%notfound then
insert into statistics
values( 'INSTRUCTOR', v_type, USER, SYSDATE);
end if;
dbms_output.put_line( '记录完毕');
- -提交自治事务
commit;
end instructor_aud;
- -客户端测试
update instructor
set first_name = 'zs'
where instructor_id = 107;
delete from instructor
where instructor_id = 110;
- -可看到两条记录
select * from statistics;
- -客户端回滚
rollback;
- -看到触发器所做的工作仍然保留
select * from statistics;
- -示例 4:理解触发器
CREATE TRIGGER student_au
AFTER UPDATE ON STUDENT
FOR EACH ROW
WHEN (NVL( NEW.ZIP, '') <> OLD.ZIP)
Trigger Body.. .
- -在 WHEN子句中,使用伪记录: OLD可以访问当前被处
- -理的数据行。要注意,当在 WHEN子句的条件中使用时,
- - : NEW和: OLD都不再使用冒号作为前缀
- -对于 update操作,即可以使用: new,也可以使用: old
UPDATE student SET zip = '01247' WHERE zip = '02189';
- -ZIP列的值 01247是个新值,并且触发器使用: NEW.ZIP来引
- -用它。 02189是ZIP列的先前值,使用: OLD.ZIP来引用
- -对于 INSERT语句而言, : OLD是未定义的;
- -对于 DELETE语句而言, : NEW是未定义的。
- -当触发事件是 INSERT或者 DELETE时,如果在触发器中分别
- -使用: OLD或者: NEW,PL / SQL编译器并不会产生语法错误,
- -在这种情况下, : OLD或者: NEW伪记录的字段值会被
- -设置为 NULL
- -简单的说,: new引用的是将要写入表中的数据,
- -而: old引用的是表中已有的数据
- -行触发器
- -行触发器指的是触发器被触发的次数等同于触发语句所影响的行数。当子句 FOR EACH ROW出现在 CREATE TRIGGER语句中,该触发器就是行触发器。例如:
CREATE OR REPLACE TRIGGER course_au
AFTER UPDATE ON course
FOR EACH ROW
……
- -该触发器是行触发器。如果某 UPDATE语句导致COURSE表
- -中 20条记录被修改,则该触发器会执行 20次
- -语句触发器
- -对于语句触发器而言,每执行一次触发语句,该触发器就会执行一次,也就是说,不管触发语句影响多少数据行,该触发器只会执行一次。为创建语句触发器,应该忽略 FOR EACH ROW子句。例如:
CREATE OR REPLACE TRIGGER enrollment_ad
AFTER UPDATE ON enrollment
……
- -当对ENROLLMENT表执行一个 DELETE语句时,该触发器就
- -会执行一次。不管该 DELETE语句从ENROLLMENT表删除 1行
- -或者多行数据,该触发器都只会触发一次
- -问题:写触发器时如何确定是写成行级的还是语句的?
- -如果在触发器中没有使用到: new或者: old,就写成语句级的。
- -如果希望限制只能在上班时间访问某个表,
- -就应该使用语句触发器
- -示例 8:限制客户只能在上班时间访问instructor表
create or replace trigger instructor_buid
before update or insert or delete
on instructor
declare
v_day varchar2( 9);
begin
- -得到服务器当前日期所代表的那一天的名字
v_day : = rtrim(to_char(sysdate, 'DAY'));
- -判断是周六日吗?
if v_day in ( 'SATURDAY', 'SUNDAY') then
raise_application_error( - 20000, '周六日应该休息');
end if;
end;
- -记住:触发器抛异常就可以阻止客户端事务的完成。
- -触发器的其它内容:
- - 1、变异表问题:
- -dml操作正在处理的表叫做变异表。对于触发器而言,
- -就是在其上定义触发器的表叫变异表。
- -变异表问题专门针对行级触发器:对于行级触发器,不允许
- -在触发器体中访问变异表。
create trigger student_au
after update on student
for each row
begin
- -不能修改student表
update student
set first_name = 'zs'
where student_id = 102;
end student_au;
- -客户端测试
update student
set first_name = 'ls'
where student_id = 101;
- -抛出异常:
- - -ORA - 04091: table STUDENT.STUDENT is mutating, trigger / function may not see it
- -ORA - 06512: at "STUDENT.STUDENT_AU", line 2
- -ORA - 04088: error during execution of trigger 'STUDENT.STUDENT_AU'
- - 2、 11g新增的复合触发器
- -处理动态 sql语句
- -动态 SQL:
- - SQL语句在程序被编译时是未知的(例如,包含变量),
- -涉及的数据库对象(例如,表)可以是运行时才创建的
- -为处理动态 SQL语句,需要使用 EXECUTE IMMEDIAT
- -或者 OPEN FOR语句
- - EXECUTE IMMEDIATE被用于单行 SELECT语句、
- -所有的DML和DDL语句。
- - OPEN FOR语句用于多行 SELECT语句以及引用游标
- - EXECUTE IMMEDIATE 语句
EXECUTE IMMEDIATE dynamic_SQL_string
[ INTO defined_variable1, defined_variable2, ...]
[ USING [ IN | OUT | IN OUT] bind_argument1, bind_argument2,...]
[{RETURNING | RETURN} INTO bind_argument1, bind_argument2, ...]
- -intio子句:专门接收 select语句返回行(只有一行)的
- -各个列值
- - using子句:专门用来给绑定参数传值
- -returning into子句:专门接收dml语句中returning into
- -子句返回的列值
- -示例 1:
DECLARE
sql_stmt VARCHAR2( 100);
plsql_block VARCHAR2( 300);
v_zip VARCHAR2( 5) : = '11106';
v_total_students NUMBER;
v_new_zip VARCHAR2( 5);
v_student_id NUMBER : = 151;
begin
- - 1 /创建my_student表
- -拼凑动态 sql语句
sql_stmt : = 'create table my_student '||
' as select * from student where zip='||v_zip;
- -执行动态 sql
execute immediate sql_stmt;
- - 2、查询my_student表中的学生人数并打印
execute immediate 'select count(*) from my_student'
into v_total_students;
dbms_output.put_line( '学生人数是:'||v_total_students);
- - 3、更新my_student表的记录,并得到更新后的列值
sql_stmt : = 'update my_student set zip=11105 '||
'where student_id=:1 returning zip into :2';
execute immediate sql_stmt
using v_student_id
returning into v_new_zip;
dbms_output.put_line( '新的邮编值:'||v_new_zip);
end;
- -当使用 EXECUTE IMMEDIATE语句时,
- -要避免常见的ORA错误
- -示例 2:不正确的例子
- -ORA - 01027: bind variables not allowed for data definition operations
- -ORA - 06512: at line 12
DECLARE
sql_stmt VARCHAR2( 100);
v_zip VARCHAR2( 5) : = '11106';
begin
- - 1 /创建my_student表
- -拼凑动态 sql语句
sql_stmt : = 'create table my_student1 '||
' as select * from student where zip=:zip';
- -执行动态 sql
execute immediate sql_stmt
using v_zip;
end;
- -结论:动态ddl语句中不允许使用绑定变量。但是
- -使用普通变量没问题
- -ORA - 00903: invalid table name
- -ORA - 06512: at line 11
DECLARE
sql_stmt VARCHAR2( 100);
v_zip VARCHAR2( 5) : = '11106';
v_total_students NUMBER;
begin
- -表名使用绑定变量
sql_stmt : = 'select count(*) from :my_table';
- -执行动态 sql
execute immediate sql_stmt
into v_total_students
using 'my_student';
end;
- -结论:不能把表的名称作为绑定参数传递给动态 SQL语句。
- -为了在运行时提供表的名称,需要使用字符串连接的写法:
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM'||my_table
INTO v_total_students;
- -动态 SQL语句的结尾不应该有分号(;)
- -ORA - 00911: invalid character
- -ORA - 06512: at line 11
DECLARE
sql_stmt VARCHAR2( 100);
v_zip VARCHAR2( 5) : = '11106';
v_total_students NUMBER;
begin
- -表名使用绑定变量
sql_stmt : = 'select count(*) from my_student;';
- -执行动态 sql
execute immediate sql_stmt
into v_total_students;
end;
- -如何给绑定变量传 null值?
- -示例 5:
- -ORA - 06550: line 8, column 12:
- -PLS - 00457: expressions have to be of SQL types
- -ORA - 06550: line 7, column 5:
- -PL / SQL: Statement ignored
DECLARE
sql_stmt VARCHAR2( 100);
BEGIN
sql_stmt : = 'UPDATE course'||
' SET prerequisite = :some_value';
EXECUTE IMMEDIATE sql_stmt
USING NULL;
end;
- -可见,不能使用字面量 null来给绑定变量传递 null值。
- -变通方法是:声明一个未初始化的变量,再将该变量
- -传递给绑定变量。
DECLARE
sql_stmt VARCHAR2( 100);
v_null varchar2( 1);
BEGIN
sql_stmt : = 'UPDATE course'||
' SET prerequisite = :some_value';
EXECUTE IMMEDIATE sql_stmt
USING v_null;
end;
- -为了处理返回多行结果集的动态 select语句,
- -需要使用 open for语句以游标的形式进行处理
- -语法:
- - OPEN cursor_variable FOR dynamic_SQL_string
- -[ USING bind_argument1 , bind_argument2 , ...]
- -注意:cursor_variable不是使用 cursor... is..定义的
- -游标,而是一个 ref cursor类型的变量。称为游标变量。
- -所以,为了使用 open for语句,首先应该定义一个游标变量。
- -输入 11236测试
declare
- -声明 ref cursor类型
type student_cur_type is ref cursor;
- -声明游标变量
student_cur student_cur_type;
v_zip VARCHAR2( 5) : = '&sv_zip';
v_first_name VARCHAR2( 25);
v_last_name VARCHAR2( 25);
begin
- -打开游标变量,使其和某个动态 select查询的结果集关联
open student_cur for 'select first_name,last_name '||
' from student where zip=:1' using v_zip;
- -游标检索
loop
fetch student_cur into v_first_name,v_last_name;
exit when student_cur%notfound;
- -处理拿到的行
dbms_output.put_line(v_first_name|| ' '||v_last_name);
end loop;
- -关闭游标变量
close student_cur;
end;
- -存储过程
- -过程是可以执行一个或者多个动作的命名模块,
- -它不需要返回任何值
- -创建过程的语法如下所示:
CREATE OR REPLACE PROCEDURE name
[( parameter [, parameter , ...])]
{ IS | AS }
[ local declarations]
BEGIN
executable statements
[ EXCEPTION
exception handlers]
END [ name];
- -创建一个过程,对于有 8个以上学生学习的课程,将其费用
- -打 95折
create or replace procedure discount
is
- -声明游标,保存需要打折的课程编号及名称
cursor c_group_discount is
select distinct c.course_no, c.description
from enrollment e, section s,course c
where e.section_id = s.section_id
and s.course_no = c.course_no
group by c.course_no, c.description,s.section_id
having count( *) > = 8;
begin
- -遍历游标,取出每个课程编号,进行打折
for r_discount in c_group_discount loop
update course
set cost = cost * 0. 95
where course_no =r_discount.course_no;
dbms_output.put_line( '95折给了课程:'||
r_discount.course_no|| ' '||r_discount.description);
end loop;
rollback;
end discount;
- -如何调用存储过程?
- - 1、直接在plsql块中以名字来调用
begin
discount;
end;
- - 2、直接在sqlpls中使用 execute命令调用
execute discount;
- - 3、在Java程序中使用jdbc调用
- -存储过程和事务:
- -因为过程主要是提供给客户端调用的,客户端是事务的
- -发起者,事务的结束应该由客户端来决定。所以,
- -在过程中一般不使用 commit或者 rollback。
- -从数据字典查询有关过程的信息
select object_name,object_type,status
from user_objects
where object_name = 'DISCOUNT';
- -显示discount过程的源代码
select text
from user_source
where name = 'DISCOUNT';
- -关于过程的参数:
- -有 3中模式:
- - in模式:输入参数,用来给过程传值,值可以是字面量或者
- -已赋值的变量
- - out模式:输出参数,过程负责给这些参数赋值。过程执行完后,
- -调用者就可以从这些参数中拿到值。输出参数必须是变量。
- - in out模式:输入输出参数。必须是变量。一方面用来给
- -过程传递一个值,另一方面过程也将修改该参数的值。
- -过程传参的语法:
- - 1)位置表示法
- - 2)命名表示法
create or replace procedure find_sname(
i_student_id in number,
o_first_name out varchar2,
o_last_name out varchar2
)
is
begin
select first_name,last_name
into o_first_name,o_last_name
from student
where student_id =i_student_id;
exception
when no_data_found then
DBMS_OUTPUT.PUT_LINE( 'Error in finding student_id: '||i_student_id);
end find_sname;
- -测试
declare
v_first_name varchar2( 25);
v_last_name varchar2( 25);
begin
- -位置表示法传参
find_sname( 145,v_first_name,v_last_name);
dbms_output.put_line(v_first_name||v_last_name);
- -命名表示法传参
find_sname(i_student_id = > 145,
o_first_name = > v_first_name,
o_last_name = > v_last_name);
dbms_output.put_line(v_first_name||v_last_name);
end;
- -使用输入输出参数
create or replace procedure test_io(
io_greeting in out varchar2
)
is
begin
- -首先使用输入的值
dbms_output.put_line( '你给我的是:'||io_greeting);
- -给参数重新赋值
io_greeting : = '我给你的是:bye bye';
end test_io;
- -测试
declare
v_greeting varchar2( 20) : = '你好';
begin
test_io(io_greeting = > v_greeting);
dbms_output.put_line(v_greeting);
end;
- -因为记录没有自己的值,因此不能测试记录是否为 null、
- -是否相等或者不相等。以下语句是非法的:
IF course_rec IS NULL THEN ……
IF course_rec1 = course_rec2 THEN ……
- -用户定义的记录
- -创建用户定义记录的通用语法如下所示(方括号中的内容是可选的):
type type_name is record
(field_name1 datatype1 [ not null] [: = 默认值] ,
field_name2 datatype2 [ not null] [: = 默认值] ,
……
field_nameN datatypeN [ not null] [: = 默认值]) ;
record_name type_name;
- -了解:
- -记录的兼容性
- -用户定义记录的约束及默认值
- -封装记录的使用
- -包含记录的集合。重点
declare
- -声明游标
cursor name_cur is
select first_name,last_name
from student
where rownum < = 4;
- -声明索引表集合类型.其中放基于游标的记录。
- -注意:name_cur%rowtype就是记录类型的名字
type name_type is table of name_cur%rowtype
index by pls_integer;
- -声明集合变量
name_tab name_type;
v_index pls_integer : = 0;
begin
- -遍历游标,将拿到 4条的记录放到集合中
for name_rec in name_cur loop
v_index : = v_index + 1;
name_tab(v_index) : = name_rec;
end loop;
- -从集合中取出记录并打印
for i in 1..name_tab. count loop
dbms_output.put_line(name_tab(i).first_name);
dbms_output.put_line(name_tab(i).last_name);
end loop;
end;
- -创建使用触发器
- -数据库触发器是存储在数据库中的命名PL / SQL语句块,
- -当触发事件发生时它们会隐含地执行。
- -触发事件可以是如下任何一种:
- -DML语句(如 INSERT、 UPDATE或者 DELETE)。
- -DDL语句(如 CREATE或者 ALTER)。
- -系统事件,如数据库启动或者关闭
- -用户事件,如登录和注销。
- -创建触发器的通用语法如下所示(中括号中的保留字是可选的):
CREATE [ OR REPLACE] TRIGGER trigger_name
{ BEFORE| AFTER} Triggering_event ON table_name
[ FOR EACH ROW]
[FOLLOWS another_trigger]
[ENABLE /DISABLE]
[ WHEN condition]
DECLARE
declaration statements
BEGIN
executable statements
EXCEPTION
exception -handling statements
END;
- -写触发器的限制:
- - 1)不要在触发器中使用 commit、 rollback等事务控制语句。
- -因为触发器执行的操作是和客户端在同一个事务中执行的,
- -事务的结束应该由客户端来控制。这个规则的一个例外是:
- -在触发器中使用自治事务
- - 2)不要在触发器中使用long类型的变量
- - before触发器。
- -是指触发器的执行时间是在dml操作执行之前。
- -示例 1:这个触发器针对STUDENT表的 INSERT语句之前执行,
- -填充STUDENT_ID、CREATED_DATE、MODIFIED_DATE、
- -CREATED_BY和MODIFIED_BY等列
create or replace trigger student_bi
before insert on student
for each row
begin
- -直接给新行的 5个列赋值
: new.student_id : = student_id_seq.nextval;
: new.created_by : = user;
: new.created_date : = sysdate;
: new.modified_by : = user;
: new.modified_date : = sysdate;
dbms_output.put_line( '触发器执行完毕');
end student_bi;
- -客户端测试语句:
INSERT INTO student(first_name, last_name, zip, registration_date) VALUES ( 'John', 'Smith', '00914', SYSDATE);
- -观察某些列值会被触发器提供的值覆盖掉
INSERT INTO student(student_id, first_name,
last_name, zip,registration_date, created_by,
created_date, modified_by,modified_date)
VALUES ( 800, 'John', 'Smith', '00914', SYSDATE,
'张三', SYSDATE, '张三', SYSDATE);
- - after触发器:在dml语句执行完后再执行
- -示例 2:建立针对INSTRUCTOR表的 UPDATE或者
- - DELETE操作执行之后触发的触发器。该触发器
- -在statistics表中记录用户对INSTRUCTOR表进行的最后修改
- -信息
create table statistics(
TABLE_NAME VARCHAR2( 30),
TRANSACTION_NAME VARCHAR2( 10),
TRANSACTION_USER VARCHAR2( 30),
TRANSACTION_DATE DATE
);
create or replace trigger instructor_aud
after update or delete on instructor
declare
- -
v_type varchar2( 10);
begin
- -确定用户的操作
if updating then
v_type : = 'UPDATE';
elsif deleting then
v_type : = 'DELETE';
end if;
- -更新统计表
update statistics
set transaction_user = USER,
transaction_date = SYSDATE
WHERE table_name = 'INSTRUCTOR'
AND transaction_name = v_type;
- -判断更新是否成功,如果没有,则插入一行
insert into statistics
values( 'INSTRUCTOR', v_type, USER, SYSDATE);
dbms_output.put_line( '记录完毕');
end instructor_aud;
- -客户端测试
update instructor
set first_name = 'zs'
where instructor_id = 107;
delete from instructor
where instructor_id = 110;
- -可看到两条记录
select * from statistics;
- -客户端回滚
rollback;
- -看到触发器所做的工作也被撤销了
select * from statistics;
- -问题:当客户端撤销事务时,如何保留触发器所做的
- -工作而不被同时撤销掉?
- -在触发器中使用自治事务
- -自治事务:
- -是由其它事务(通常被称为主事务)发起的独立事务。
- -也就是说,自治事务也许会执行多个DML语句,
- -并且提交或者回滚操作,而不会提交或者回滚主事务
- -执行的DML语句
- -修改上面的触发器,使用自治事务
create or replace trigger instructor_aud
after update or delete on instructor
declare
- -
v_type varchar2( 10);
- -声明使用自治事务
pragma autonomous_transaction;
begin
- -确定用户的操作
if updating then
v_type : = 'UPDATE';
elsif deleting then
v_type : = 'DELETE';
end if;
- -更新统计表
update statistics
set transaction_user = USER,
transaction_date = SYSDATE
WHERE table_name = 'INSTRUCTOR'
AND transaction_name = v_type;
- -判断更新是否成功,如果没有,则插入一行
if sql%notfound then
insert into statistics
values( 'INSTRUCTOR', v_type, USER, SYSDATE);
end if;
dbms_output.put_line( '记录完毕');
- -提交自治事务
commit;
end instructor_aud;
- -客户端测试
update instructor
set first_name = 'zs'
where instructor_id = 107;
delete from instructor
where instructor_id = 110;
- -可看到两条记录
select * from statistics;
- -客户端回滚
rollback;
- -看到触发器所做的工作仍然保留
select * from statistics;
- -示例 4:理解触发器
CREATE TRIGGER student_au
AFTER UPDATE ON STUDENT
FOR EACH ROW
WHEN (NVL( NEW.ZIP, '') <> OLD.ZIP)
Trigger Body.. .
- -在 WHEN子句中,使用伪记录: OLD可以访问当前被处
- -理的数据行。要注意,当在 WHEN子句的条件中使用时,
- - : NEW和: OLD都不再使用冒号作为前缀
- -对于 update操作,即可以使用: new,也可以使用: old
UPDATE student SET zip = '01247' WHERE zip = '02189';
- -ZIP列的值 01247是个新值,并且触发器使用: NEW.ZIP来引
- -用它。 02189是ZIP列的先前值,使用: OLD.ZIP来引用
- -对于 INSERT语句而言, : OLD是未定义的;
- -对于 DELETE语句而言, : NEW是未定义的。
- -当触发事件是 INSERT或者 DELETE时,如果在触发器中分别
- -使用: OLD或者: NEW,PL / SQL编译器并不会产生语法错误,
- -在这种情况下, : OLD或者: NEW伪记录的字段值会被
- -设置为 NULL
- -简单的说,: new引用的是将要写入表中的数据,
- -而: old引用的是表中已有的数据
- -行触发器
- -行触发器指的是触发器被触发的次数等同于触发语句所影响的行数。当子句 FOR EACH ROW出现在 CREATE TRIGGER语句中,该触发器就是行触发器。例如:
CREATE OR REPLACE TRIGGER course_au
AFTER UPDATE ON course
FOR EACH ROW
……
- -该触发器是行触发器。如果某 UPDATE语句导致COURSE表
- -中 20条记录被修改,则该触发器会执行 20次
- -语句触发器
- -对于语句触发器而言,每执行一次触发语句,该触发器就会执行一次,也就是说,不管触发语句影响多少数据行,该触发器只会执行一次。为创建语句触发器,应该忽略 FOR EACH ROW子句。例如:
CREATE OR REPLACE TRIGGER enrollment_ad
AFTER UPDATE ON enrollment
……
- -当对ENROLLMENT表执行一个 DELETE语句时,该触发器就
- -会执行一次。不管该 DELETE语句从ENROLLMENT表删除 1行
- -或者多行数据,该触发器都只会触发一次
- -问题:写触发器时如何确定是写成行级的还是语句的?
- -如果在触发器中没有使用到: new或者: old,就写成语句级的。
- -如果希望限制只能在上班时间访问某个表,
- -就应该使用语句触发器
- -示例 8:限制客户只能在上班时间访问instructor表
create or replace trigger instructor_buid
before update or insert or delete
on instructor
declare
v_day varchar2( 9);
begin
- -得到服务器当前日期所代表的那一天的名字
v_day : = rtrim(to_char(sysdate, 'DAY'));
- -判断是周六日吗?
if v_day in ( 'SATURDAY', 'SUNDAY') then
raise_application_error( - 20000, '周六日应该休息');
end if;
end;
- -记住:触发器抛异常就可以阻止客户端事务的完成。
- -触发器的其它内容:
- - 1、变异表问题:
- -dml操作正在处理的表叫做变异表。对于触发器而言,
- -就是在其上定义触发器的表叫变异表。
- -变异表问题专门针对行级触发器:对于行级触发器,不允许
- -在触发器体中访问变异表。
create trigger student_au
after update on student
for each row
begin
- -不能修改student表
update student
set first_name = 'zs'
where student_id = 102;
end student_au;
- -客户端测试
update student
set first_name = 'ls'
where student_id = 101;
- -抛出异常:
- - -ORA - 04091: table STUDENT.STUDENT is mutating, trigger / function may not see it
- -ORA - 06512: at "STUDENT.STUDENT_AU", line 2
- -ORA - 04088: error during execution of trigger 'STUDENT.STUDENT_AU'
- - 2、 11g新增的复合触发器
- -处理动态 sql语句
- -动态 SQL:
- - SQL语句在程序被编译时是未知的(例如,包含变量),
- -涉及的数据库对象(例如,表)可以是运行时才创建的
- -为处理动态 SQL语句,需要使用 EXECUTE IMMEDIAT
- -或者 OPEN FOR语句
- - EXECUTE IMMEDIATE被用于单行 SELECT语句、
- -所有的DML和DDL语句。
- - OPEN FOR语句用于多行 SELECT语句以及引用游标
- - EXECUTE IMMEDIATE 语句
EXECUTE IMMEDIATE dynamic_SQL_string
[ INTO defined_variable1, defined_variable2, ...]
[ USING [ IN | OUT | IN OUT] bind_argument1, bind_argument2,...]
[{RETURNING | RETURN} INTO bind_argument1, bind_argument2, ...]
- -intio子句:专门接收 select语句返回行(只有一行)的
- -各个列值
- - using子句:专门用来给绑定参数传值
- -returning into子句:专门接收dml语句中returning into
- -子句返回的列值
- -示例 1:
DECLARE
sql_stmt VARCHAR2( 100);
plsql_block VARCHAR2( 300);
v_zip VARCHAR2( 5) : = '11106';
v_total_students NUMBER;
v_new_zip VARCHAR2( 5);
v_student_id NUMBER : = 151;
begin
- - 1 /创建my_student表
- -拼凑动态 sql语句
sql_stmt : = 'create table my_student '||
' as select * from student where zip='||v_zip;
- -执行动态 sql
execute immediate sql_stmt;
- - 2、查询my_student表中的学生人数并打印
execute immediate 'select count(*) from my_student'
into v_total_students;
dbms_output.put_line( '学生人数是:'||v_total_students);
- - 3、更新my_student表的记录,并得到更新后的列值
sql_stmt : = 'update my_student set zip=11105 '||
'where student_id=:1 returning zip into :2';
execute immediate sql_stmt
using v_student_id
returning into v_new_zip;
dbms_output.put_line( '新的邮编值:'||v_new_zip);
end;
- -当使用 EXECUTE IMMEDIATE语句时,
- -要避免常见的ORA错误
- -示例 2:不正确的例子
- -ORA - 01027: bind variables not allowed for data definition operations
- -ORA - 06512: at line 12
DECLARE
sql_stmt VARCHAR2( 100);
v_zip VARCHAR2( 5) : = '11106';
begin
- - 1 /创建my_student表
- -拼凑动态 sql语句
sql_stmt : = 'create table my_student1 '||
' as select * from student where zip=:zip';
- -执行动态 sql
execute immediate sql_stmt
using v_zip;
end;
- -结论:动态ddl语句中不允许使用绑定变量。但是
- -使用普通变量没问题
- -ORA - 00903: invalid table name
- -ORA - 06512: at line 11
DECLARE
sql_stmt VARCHAR2( 100);
v_zip VARCHAR2( 5) : = '11106';
v_total_students NUMBER;
begin
- -表名使用绑定变量
sql_stmt : = 'select count(*) from :my_table';
- -执行动态 sql
execute immediate sql_stmt
into v_total_students
using 'my_student';
end;
- -结论:不能把表的名称作为绑定参数传递给动态 SQL语句。
- -为了在运行时提供表的名称,需要使用字符串连接的写法:
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM'||my_table
INTO v_total_students;
- -动态 SQL语句的结尾不应该有分号(;)
- -ORA - 00911: invalid character
- -ORA - 06512: at line 11
DECLARE
sql_stmt VARCHAR2( 100);
v_zip VARCHAR2( 5) : = '11106';
v_total_students NUMBER;
begin
- -表名使用绑定变量
sql_stmt : = 'select count(*) from my_student;';
- -执行动态 sql
execute immediate sql_stmt
into v_total_students;
end;
- -如何给绑定变量传 null值?
- -示例 5:
- -ORA - 06550: line 8, column 12:
- -PLS - 00457: expressions have to be of SQL types
- -ORA - 06550: line 7, column 5:
- -PL / SQL: Statement ignored
DECLARE
sql_stmt VARCHAR2( 100);
BEGIN
sql_stmt : = 'UPDATE course'||
' SET prerequisite = :some_value';
EXECUTE IMMEDIATE sql_stmt
USING NULL;
end;
- -可见,不能使用字面量 null来给绑定变量传递 null值。
- -变通方法是:声明一个未初始化的变量,再将该变量
- -传递给绑定变量。
DECLARE
sql_stmt VARCHAR2( 100);
v_null varchar2( 1);
BEGIN
sql_stmt : = 'UPDATE course'||
' SET prerequisite = :some_value';
EXECUTE IMMEDIATE sql_stmt
USING v_null;
end;
- -为了处理返回多行结果集的动态 select语句,
- -需要使用 open for语句以游标的形式进行处理
- -语法:
- - OPEN cursor_variable FOR dynamic_SQL_string
- -[ USING bind_argument1 , bind_argument2 , ...]
- -注意:cursor_variable不是使用 cursor... is..定义的
- -游标,而是一个 ref cursor类型的变量。称为游标变量。
- -所以,为了使用 open for语句,首先应该定义一个游标变量。
- -输入 11236测试
declare
- -声明 ref cursor类型
type student_cur_type is ref cursor;
- -声明游标变量
student_cur student_cur_type;
v_zip VARCHAR2( 5) : = '&sv_zip';
v_first_name VARCHAR2( 25);
v_last_name VARCHAR2( 25);
begin
- -打开游标变量,使其和某个动态 select查询的结果集关联
open student_cur for 'select first_name,last_name '||
' from student where zip=:1' using v_zip;
- -游标检索
loop
fetch student_cur into v_first_name,v_last_name;
exit when student_cur%notfound;
- -处理拿到的行
dbms_output.put_line(v_first_name|| ' '||v_last_name);
end loop;
- -关闭游标变量
close student_cur;
end;
- -存储过程
- -过程是可以执行一个或者多个动作的命名模块,
- -它不需要返回任何值
- -创建过程的语法如下所示:
CREATE OR REPLACE PROCEDURE name
[( parameter [, parameter , ...])]
{ IS | AS }
[ local declarations]
BEGIN
executable statements
[ EXCEPTION
exception handlers]
END [ name];
- -创建一个过程,对于有 8个以上学生学习的课程,将其费用
- -打 95折
create or replace procedure discount
is
- -声明游标,保存需要打折的课程编号及名称
cursor c_group_discount is
select distinct c.course_no, c.description
from enrollment e, section s,course c
where e.section_id = s.section_id
and s.course_no = c.course_no
group by c.course_no, c.description,s.section_id
having count( *) > = 8;
begin
- -遍历游标,取出每个课程编号,进行打折
for r_discount in c_group_discount loop
update course
set cost = cost * 0. 95
where course_no =r_discount.course_no;
dbms_output.put_line( '95折给了课程:'||
r_discount.course_no|| ' '||r_discount.description);
end loop;
rollback;
end discount;
- -如何调用存储过程?
- - 1、直接在plsql块中以名字来调用
begin
discount;
end;
- - 2、直接在sqlpls中使用 execute命令调用
execute discount;
- - 3、在Java程序中使用jdbc调用
- -存储过程和事务:
- -因为过程主要是提供给客户端调用的,客户端是事务的
- -发起者,事务的结束应该由客户端来决定。所以,
- -在过程中一般不使用 commit或者 rollback。
- -从数据字典查询有关过程的信息
select object_name,object_type,status
from user_objects
where object_name = 'DISCOUNT';
- -显示discount过程的源代码
select text
from user_source
where name = 'DISCOUNT';
- -关于过程的参数:
- -有 3中模式:
- - in模式:输入参数,用来给过程传值,值可以是字面量或者
- -已赋值的变量
- - out模式:输出参数,过程负责给这些参数赋值。过程执行完后,
- -调用者就可以从这些参数中拿到值。输出参数必须是变量。
- - in out模式:输入输出参数。必须是变量。一方面用来给
- -过程传递一个值,另一方面过程也将修改该参数的值。
- -过程传参的语法:
- - 1)位置表示法
- - 2)命名表示法
create or replace procedure find_sname(
i_student_id in number,
o_first_name out varchar2,
o_last_name out varchar2
)
is
begin
select first_name,last_name
into o_first_name,o_last_name
from student
where student_id =i_student_id;
exception
when no_data_found then
DBMS_OUTPUT.PUT_LINE( 'Error in finding student_id: '||i_student_id);
end find_sname;
- -测试
declare
v_first_name varchar2( 25);
v_last_name varchar2( 25);
begin
- -位置表示法传参
find_sname( 145,v_first_name,v_last_name);
dbms_output.put_line(v_first_name||v_last_name);
- -命名表示法传参
find_sname(i_student_id = > 145,
o_first_name = > v_first_name,
o_last_name = > v_last_name);
dbms_output.put_line(v_first_name||v_last_name);
end;
- -使用输入输出参数
create or replace procedure test_io(
io_greeting in out varchar2
)
is
begin
- -首先使用输入的值
dbms_output.put_line( '你给我的是:'||io_greeting);
- -给参数重新赋值
io_greeting : = '我给你的是:bye bye';
end test_io;
- -测试
declare
v_greeting varchar2( 20) : = '你好';
begin
test_io(io_greeting = > v_greeting);
dbms_output.put_line(v_greeting);
end;