PL/SQL详例和解释(三)
er(10),table_name varchar2(30), operation_name varchar2(30), operation_user varchar2(20), operation_date varchar2(20));
create sequence record_operation_id increment by 1;
create or replace trigger emp_table_trig_comp
for insert or update on it_employees
compound trigger
v_date_day varchar2(30);
v_date_time number(10);
v_emp_id it_employees.employee_id%type;
v_record_id operation_record.record_id%type:=record_operation_id.nextval;
v_name operation_record.operation_name%type;
before statement is
begin
v_date_day:=rtrim(to_char(sysdate,'DAY'));
v_date_time:=to_number(to_char(sysdate,'HH24'));
if v_date_day like 'S%' then raise_application_error(-20001,'Today is Weekend, it is invaild day to operate tables!');
else
if v_date_time<9 or v_date_time>=18 then raise_application_error(-20002,'Invaild time to operate tables!');
end if;
end if;
end before statement;
before each row is
begin
if inserting then
select max(employee_id)+1 into v_emp_id from it_employees;
:new.employee_id:=v_emp_id;
end if;
end before each row;
after each row is
begin
if inserting then v_name:='Insert';
elsif updating then v_name:='Update';
end if;
insert into operation_record (record_id,table_name, operation_name, operation_user , operation_date) values (v_record_id,'IT_EMPLOYEES',v_name,user,to_char(sysdate,'DD-MON-YYYY HH24:MI'));
end after each row;
after statement is
begin
DBMS_OUTPUT.PUT_LINE('This Operation has been Completed!');
end after statement;
end emp_table_trig_comp;
insert into it_employees (first_name, last_name, e_mail, phone_number, job_id, salary, manager_id, birth_date, department_id) val
ues ('Hill','Jobs','Hill.J@oracle.com',10167445585,1003,10000,10001,'13-7月 -1992',102);
update it_employees set manager_id=10012 where employee_id=10006;
select * from operation_record;
--实现存放名字的索引表
declare
cursor c_name is select first_name||'.'||last_name name from it_employees;
type t_name_table is table of varchar2(30) index by binary_integer;
name_table t_name_table;
counter number:=0;
begin
for r_name in c_name
loop
counter:=counter+1;
name_table(counter):=r_name.name;
dbms_output.put_line('Name ('||counter||') is: '||name_table(counter));
end loop;
end;
--索引表和嵌套表的方法
declare
type t_num_table1 is table of number(10) index by binary_integer;
num_table1 t_num_table1;
type t_num_table2 is table of number(10);
num_table2 t_num_table2:=t_num_table2(11,21,31,41,51,61,71,81,91,101);
begin
for n in 1..10
loop
num_table1(n):=n+1;
end loop;
if num_table1.exists(10) then dbms_output.put_line('NO.10 is '||num_table1(10));
end if;
dbms_output.put_line('Table1 total has '||num_table1.count);
num_table2.delete(3);
if num_table2.exists(3) then dbms_output.put_line('No.3 is'||num_table2(3));
else dbms_output.put_line('No.3 has been deleted!');
end if;
dbms_output.put_line('Table2 total has '||num_table2.count);
if num_table2.exists(9) then dbms_output.put_line('Prior num is '||num_table2.prior(9)||', next num is '||num_table2.next(9));
end if;
num_table2.trim(2);
dbms_output.put_line('Last number is '||num_table2.last);
end;
--变长数组实现存储2遍名字
declare
cursor c_name is select first_name||'.'||last_name name from it_employe