游标和触发器练习

declare
cursor c_cursor is
select * from ruanjian181;
– v_info ruanjian181%rowtype;
begin
– open c_cursor;
for v_info in c_cursor loop
dbms_output.put_line(v_info.age);
end loop;
– close c_cursor;
end;

declare
cursor lr_num is --定义一个游标，名为：lr_num
select level lv
from dual
connect by level <= 5; --这段查询即为游标lr_num的具体内容
v_num lr_num%rowtype; --定义一个变量v_num，数据类型为游标 lr_num 的 %ROWTYPE，即lr_num的一行
begin
open lr_num; --打开游标lr_num
loop --开始循环
fetch lr_num into v_num; --把游标 lr_num 中的next一行数据 into 到变量 v_num 中
exit when lr_num%notfound; --如果 lr_num 中找不到next一行数据，即退出循环
dbms_output.put_line(v_num.lv); --输出 v_num 变量中的 lv 字段的值
end loop; --结束循环
close lr_num; --关闭游标
end;

–触发器练习
–复制表头
CREATE TABLE dept_log AS SELECT * FROM ruanjian181 WHERE 1=2;
select * from dept_log;

–登录触发器练习
create or replace trigger logon_trigger
after logon on database
begin
insert into log_event(log_type,username,logonTime)
values(‘logon’,ora_login_user,sysdate);
end;
–登出触发器
create or replace trigger logoff_trigger
before logoff on database
begin
insert into log_event(log_type,username,logoffTime)
values(‘logoff’,ora_login_user,sysdate);
end;
–创建日志记录表
create table log_event(
log_type varchar2(20),
username varchar2(20),
logonTime date,
logoffTime date
);

select * from log_event;

create table student2(
sno varchar2(10) primary key,
sname varchar2(20),
sage number(2),
cno varchar2(2)
);
create table student2bei(
sno varchar2(10) primary key,
sname varchar2(20),
sage number(2),
cno varchar2(2)
);

insert into student2 values(‘1’,‘lilia’,18,‘1’);
insert into student2 values(‘2’,‘lilib’,18,‘1’);