学习中记下的SQL语句

create or replace trigger del_deptid
after delete on deptment
for each row
begin
delete from emp where id=:old.id;
end del_deptid;

create or replace trigger insert_dept
after insert on deptment
for each orw
begin
insert into emp(eid,ename,id) values('121','QWERT',:new.id);
end;

create or replace trigger update_dept
after update on deptment
for each row
 begin
 update emp set id=:new.id where id=:old.id;
 end;

create or replace trigger books_delete
after delete on books
for each row
begin
if :old.books_id='0001' then
raise_application_error(-20000,'error');
end if;
end;

create table mylog(curr_user varchar2(100),curr_date date,act cahr(1));
create or replace trigger dml_aa
after insert or delete or update
begin
if inserting then
insert into mylog values(user,sysdate,'I');
elseif deleting then
insert into mylog values(user,sysdate,'D');
else
insert into mylog values(user,sysdate,'U');
end if;
end;

create or replace trigger set_no before insert on auto
for each row
declare
sn number(4);
begin
select myseq.nextval into sn from dual;
:new.a:=sn;
end;

替换触发器只能建在视图上。
create or replace trigger tr_v_d_d
insert of insert on v_emp_dept
for each row
begin
insert into deptment values(:new.id,:new.name);
insert into emp(eid,ename,sex,id)values(:new.eid,:new.ename,:new.sex,:new.id);
end;

 

alter user test identified by test11
alter user test account lock;
alter user test account unlock;
create user qqq identified by qqq123
default tablespace user;
grant connect to qqq;

create role myrole;
grant select on scott.emp to myrole;
conn test/test11
select * from scott.emp
conn / as sysdba
grant myrole to test;
conn test/test11
select * from scott.emp;

 

load data infile 'c:\loader.txt'
append
into table mm(
m1 position(1:3) char,
m2 position(5:7) char)

sqlldr tt/tt11 control=e:\ora_record\cont.ctl data=e:\ora_record\loader.txt

load data infile 'c:\loader.txt'
append
into table mm(
m1  char terminated by ",",
m2 char terminated by ",")

alter system set log_archive_start=true scope=spfile

shutdown immediate
startup mount
alter database archivelog;
alter databse  open;
archive log list;

alter tablespace user begin backup;
拷贝数据文件到其它位置
alter tablespace user end backup;

alter system archive log current;
alter system switch logfile;

shutdown immediate
select * from v$recover_file
恢复
alter database datafile 6 offline drop;
alter database open;
recover datafile 6;
alter database datafile 6 online;

控制文件丢失
alter database backup controlfile to trace;
shutdown immediate
@c:\treate_ctl.txt

=======================
declare
x varchar2(10);
begin
x:='This is ..';
dbms_output.put_line('x的值为：'||x);
end;
/
declare
x varchar2(10);
begin
x:='This is ..';
dbms_output.put('x的值为：'||x);
dbms_output.new_line;
end;

declare
x varchar2(10):='abc'

declare x number;
begin
x:=0;
loop
x:=x+1;
if x>=3 then
exit;
end if;
dbms_output.put_line('in:'||x);
end loop;
dbms_output.put_line('out:'||x);
end;

declare
x number;begin
x:=0
while x<3 loop
x:=x+1
dbms_output.put_line('in:'||x);
end loop;
dbms_output.put_line('out:'||x);
end;
/

begin
 for i in 1..5 loop
 dbms_output.put_line('i='||i);
 end loop;
 dbms_output.put_line('end of for loop');
 end;
 /

begin
 for i in reverse 1..5 loop
 dbms_output.put_line('i='||i);
 end loop;
 dbms_output.put_line('end of for loop');
 end;
 /

declare
x number;
begin
x:=0;
<<repeat_loop>>
x:=x+1;
dbms_output.put_line(x);
if x<3 then
goto repeat_loop;
end if;
end;

系统异常
declare
test varchar2(10);
begin
select name into test from deptment where id='tt';
dbms_output.put_line(test);
exception
when no_data_found then
dbms_output.put_line('没有找到数据 ！');
end;

自定义异常
declare
tname varchar2(10);
e exception;
begin
select name into tname from deptment where id='01';
if tname<>'b部门' then
raise e;
end if;
dbms_output.put_line(tname);
exception
when e then
dbms_output.put_line('错误，不是需要的部门！');
end;
/

declare 
type myrecord is record(
id varchar2(10),
name varchar2(10));
real_record myrecord;
begin
select emp_id,emp_name into myrecord from emp where emp_id='001';
dbms_output.put_line(real_record.id||','||real_record.name);
end;
 

declare
myrec emp%rowtype;
begin
select * into myrec from emp where eid='001';
dbms_output.put_line(myrec.eid||','||myrec.ename||','||myrec.sex);
end;
/

declare
cursor mycur is
select * from emp;
myrecord emp%rowtype;
begin
open mycur;
fetch mycur into myrecord;
while mycur%found loop
dbms_output.put_line(myrecord.ename);
fetch mycur into myrecord;
end loop;
close mycur;
end;

declare
cursor cur_para(id varchar2)is
select books_name from books where books_id=id;
t_name books.bools_name%type
begin
open cur_para('0001');
loop
fetch cur_para into t_name;
exit when cur_para%notfound;
dbms_output.put_line(t_name);
end loop;
close cur_para;
end;
/

declare 
cursor cur_para(id varchar2) is
select books_name from books where books_id=id;
begin
dbms_outuput.put_line('*************result**********');
for cur in cur_para('0001') loop
dbms_output.put_line(cur.books_name);
end loop;
end;
/
declare
t_name books.books_name%type;
cursor cur(id varchar2) is
select books_name from books where books_id=id;
begin
if cur%isopen then
dbms_output.put_line('游标已经被打开！');
else
open cur('0003');
end if;
fetch cur into t_name;
close cur;
dbms_output.put_line(t_name);
end;
/

 declare
 t_name varchar2(10);
 cursor mycur is
 select name from deptment;
 begin
 open mycur;
 loop
 fetch mycur into t_name;
 exit when mycur%notfound or mycur%notfound is null;
 dbms_output.put_line('游标mycur的rowcount是：'||mycur%rowcount);
 end loop;
 close mycur;
 end;

declare
cursor cur is
select name from deptment for update;
test varchar2(10);
begin
open cur;
fetch cur into text;
while cur%found loop
update deptment set name=name||'_t' where current of cur;
fetch cur into text;
end loop;
close cur;
end;
/

begin
for cur in(select name from deptment)loop
dbms_output.put_line(cur.name);
end loop;
end;
/
create or replace procedure myproc(id in varchar2)
is
name varchar2(10);
begin
select ename into name from emp where empno=id;
dbms_output.put_line(name);
end myproc;
/

show errors
show errors procedure myproc;

declare 
tid varchar2(10);
begin
tid:='7369';
myproc(tid);
end;

execute myproc('7369');
/
create or replace procedure myproc2(id varchar2,name out varchar2)
is
begin
select books_name into name from books where books_id=id;
end;
/
declare
tid varchar2(10);
tname varchar2(10);
begin
tid:='0001';
myproc2(tid,tname);