variable x refcursor   ---special cursor ---显式cursor
begin
open :x for select username from dba_users;
end;
/
print x;


variable scn number
exec :scn:=dbms_flashback.get_system_change_number
print scn

delete from bigtable;


flashback table bigtable to scn :scn; 注意要alter table bigtable enable row movement
select *
from (select count(*) from bigtable),(select count(*) from bigtable as of scn :scn)



begin
for x in(select username from dba_users)
loop
  insert into t values(x.username);
  commit;
end loop;
end;


create sequence t_seq;

create table t(pk number primary key);

create  or replace trigger t_trigger  ---利用trigger and sequence 确保 record pk
before insert on t
for each row
begin
select t_seq.nextval into :new.pk from dual;
end;
/

构建 pk and fk
create table emp(empno number,name varchar2(3),deptno number);
/
create table dept(deptno number,name varchar2(3));
/
alter table emp add constraint emp_pk primary key(empno);
/
alter table dept add constraint dept_pk primary key(deptno);
/
alter table emp add constraint emp_fk_dept foreign key(deptno) references dept;
/


create or replace trigger tr_insert
after insert on dept
for each row
begin
insert into emp values(1,'zxy',:new.deptno);
end;
/


记录删除的数据
create table del_dept as select * from dept where 1=0;
create or replace trigger tr_record
before delete on dept
for each row
begin
insert into del_dept values(ld.deptno,ld.name);
end;
/


---级联删除(要除父,子也要除掉)
create or replace  trigger tr_del_dept_emp
before delete on dept
for each row
begin
delete from emp where deptno=ld.deptno;
dbms_output.put_line('相关的子表记录也删除了,放心吧!!!');
end;
/


alter trigger tr_insert disable;

/

--plsql
create table t(x int);
/
create or replace procedure proc1
as
begin
for i in 1..10000 loop
execute immediate 'insert into t values(:x)' using i;
commit;
end loop;
end;
/

exec proc1;

---让用户只开一个会话
create profile one_session limit sessions_per_user=1;
alter user scott profile one_session;
alter system set resource_limit=true;


---轻松找到trace文件
alter  session set tracefile_identifier='my trace file';
alter session set sql_trace=true;



oerr ora 12933 ---查询oracle err



/


declare
type vc is table of t.x%type;
l_vc vc;
cursor c is select x from t;
begin
open c;
loop
fetch c bulk collect into l_vc limit 100;
forall i in 1..l_vc.count
update t set;
commit;
exit when c%notfound;
end loop;
close c;
end;
/