oracle-pl/sql基本操作1

1.

PL/SQL命名块是一次编译可多次执行的PL/SQL程序，因为事先在数据库端编译好了，所以执行效率高
2.
PL/SQL变量的数据类型有4种
标量类型：包括多种数值型，多种字符型，日期时间型，布尔型
复合类型：包括记录型，集合类型（索引表，嵌套表，数组VARRAY）
               记录有多个域组成，可以方便处理单行多列（即记录类型）或多行多列（即记录表类型）
               集合由一个域组成，可以方便处理多行单列数据
引用类型：包括游标类型（REF CURSOR），对象类型（REF）
LOB类型：包括CLOB，BLOB，NCLOB，BFILE
数据在oracle数据库与PL/SQL程序之间是通过变量实现传递的
（PL/SQL这块内容比较多，继续学习，加油）

 

在PL/SQL块中直接使用SELECT INTO语句时，如果返回一行数据，需要使用标量类型或记录型来接收；
如果SELECT INTO语句返回了多行数据，需要使用记录表类型或集合类型来接收

2.
PL/SQL中使用GOTO语句是有所限制的（大致看看应该就懂了，呵呵）
3.
只有在打开游标时，才真正创建缓冲区，并从数据库检索数据；游标一旦打开，就无法再次打开，除非先关闭；
4.
触发器有3类
DML触发器：依据基本表或简单视图建立
INSTEAD OF触发器：依据复杂视图建立
系统事件触发器：依据系统事件或DDL操作建立
（trigger功能强大，但不能多用，因为影响效率）

 

（下面的几个触发器是从书上摘下来的，感觉不错，也很直观，学习了，呵呵）

 

禁止用户在非工作时间段改变教师信息（DML触发器）
create or replace trigger change_teacher
before insert or update or delete on teachers
begin
if (to_char(sysdate, 'HH24') not between '8' and '17') or (to_char(sysdate, 'DY', 'nls_date_language=american') in ('SAT','SUN')) then
case
when INSERTING then raise_application_error(-20001, '在非工作时间不能增加教师信息!');
when UPDATING  then raise_application_error(-20002, '在非工作时间不能修改教师信息!');
when DELETING  then raise_application_error(-20003, '在非工作时间不能删除教师信息!');
end case;
end if;
end change_teacher;
/

 

在student_grade表中的成绩被修改后，保存学生成绩修改的前后值和修改日期，以供审计（DML触发器）
create table student_grade_change(student_id NUMBER(5), course_id NUMBER(5), oldscore NUMBER(4,1), newscore NUMBER(4,1), time_change DATE);

create or replace trigger s_g_change
after update of score ON student_grage
for each row
begin
insert into student_grade_change values(:old.student_id, :old.course_id, :old.score, :new.score, SYSDATE);
end s_g_change;
/

 

基于复杂视图teachers_view2建立INSTEAD OF触发器，触发器通过该视图向多个基表插入数据
create view teachers_view2 as
select t.teacher_id, t.name, d.department_id, d.department_name from teachers t, departments d where t.department_id=d.department_id;

create or replace trigger t_d_change
instead of insert ON teachers_view2
for each row
DECLARE
v_counter INT;
BEGIN
--判断，并向departments表中插入数据
select count(*) into v_counter from departments where department_id = :new.department_id;
if v_counter = 0 then
insert into departments(department_id, department_name) values(:new.department_id, :new.department_name);
end if;
--判断，并向teachers表中插入数据
select count(*) into v_counter from teachers where teacher_id = :new.teacher_id;
if v_counter = 0 then
insert into teachers(teacher_id, name, department_id) values(:new.teacher_id, :new.name, :new.department_id);
end if;
end t_d_change;
/

 

系统事件触发器基于数据库或模式。触发事件包括数据库事件（如STARTUP、SHUTDOWN等）、DDL事件（如CREATE、ALTER、DROP等）
例如：建立系统事件触发器sys_event：当在用户模式中执行DROP操作时，将删除的对象信息存入event_drop表中
create table event_drop(user_name VARCHAR2(20),object_name VARCHAR2(20),object_type VARCHAR2(20),object_owner VARCHAR2(20),date DATE)

 

create or replace trigger sys_event
after drop ON schema
BEGIN
insert into event_drop values(USER, ORA_DICT_OBJ_NAME, ORA_DICT_OBJ_TYPE, ORA_DICT_OBJ_OWNER, SYSDATE)
/

（实际看过之后，感觉也不难，呵呵）

 

参考书籍：《Oracle 10g SQL和PL/SQL编程指南》