Oracle 10g XE _ Self Study Step 3

PL/SQL 结构

Section	Description
Declarative(DECLARE )	Contains declarations of all variables, constants, cursors, and user-defined exceptions that are referenced in the executable and exception sections
Executable(BEGIN...END )	Contains SQL statements to retrieve data from the database and PL/SQL statements to manipulate data in the block
Exception(EXCEPTION)	Specifies the actions to perform when errors and abnormal conditions arise in the executable section

 

 

三种类型的PL/SQL程序

• Anonymous blocks: These are the unnamed PL/SQL blocks that are embedded within an application or are issued interactively.
• Procedures: These are the named PL/SQL blocks. These blocks accept inbound parameters but won't explicitly return any value.
• Functions: These are the named PL/SQL blocks. These blocks accept inbound parameters and will always return a value.

 

Anonymous Block Structure:

[DECLARE]

BEGIN

---Executable Statements- --

[EXCEPTION]

END ;

 

 

declare
 v_user varchar2(30);
begin
 select sys_context('userenv','current_user')
 into v_user from dual;
 dbms_output.put_line('Reported at: '||TO_CHAR(SYSDATE, 'DD-Mon-YY HH:MIPM'));
 dbms_output.put_line('User = '||v_user);
end;

 

Syntax for creating a DML trigger

CREATE [OR REPLACE] TRIGGER trigger_name
timing
event1 [OR event2 OR event3]
ON object_name
[ [REFERENCING OLD AS old/NEW AS new]
FOR EACH ROW
[WHEN (condition)] ]
trigger_body

 

创建一个Trigger, 禁止非工作时间修改表内容
create or replace trigger "SECURE_EMP"
BEFORE
insert or update or delete on "EMPLOYEES"
for each row
begin
if (to_char(sysdate,'DY') in ('SAT','SUN'))or
 (to_char(sysdate,'HH24')
   not between '08' and '18') then
 if deleting then raise_application_error(
 -20502,'You may delete from employees table '||
 'only during business hours.');
 elsif inserting then raise_application_error(
 -20500,'You may insert into employees table '||
 'only during business hours.');
 elsif updating then raise_application_error(
 -20503,'You may insert into employees table '||
 'only during business hours.');
end if;
end if;
end;
尝试修改表格，引发Trigger
delete from employees where employee_id = 198

报错
ORA-20502: You may delete from employees table only during business hours.
ORA-06512: at "SHEILA.SECURE_EMP", line 5
ORA-04088: error during execution of trigger 'SHEILA.SECURE_EMP'

 

 

Syntax for disabling a database trigger

ALTER TRIGGER trigger_name DISABLE

Syntax for disabling all triggers for a table

ALTER TABLE table_name DISABLE ALL TRIGGERS

Syntax for recompiling a trigger for a table

ALTER TRIGGER trigger_name COMPILE