应该具备的Oracle基本功(2)
---PL/SQL 一个简单的存储过程 分为四块1.声明declare 2.begin 3.exception 4.end
set?serveroutput?on; ?
?declare?
v_num?number?:=0; ?
begin?
v_num?:=2/v_num; ?
dbms_output.put_line(v_num); ?
exception ?
when?others?then?
dbms_output.put_line('error'); ?
end;?
?
---%type 变量声明的好处。
declare?
v_empno2?emp.empno%type; ?
begin?
dbms_output.put_line('test'); ?
end;?
?
---Table 变量类型
declare?
type?type_table_emp_empno?is?table?of?emp.empno%type?index?by?binary_integer; ?
v_empnos?type_table_emp_empno; ?
begin?
v_empnos(0)?:=?2999; ?
v_empnos(1)?:=?2434; ?
v_empnos(-1)?:=?8989; ?
dbms_output.put_line(v_empnos(-1)); ?
end;?
---Record 变量类型
declare?
type?type_record_dept?is?record ?
( ?
deptno?dept.deptno%type, ?
dname??dept.dname%type, ?
loc?dept.loc%type ?
); ?
v_temp?type_record_dept; ?
begin?
v_temp.deptno?:=?20; ?
v_temp.dname?:=?'tianyuexing'; ?
v_temp.loc?:=?'qhd'; ?
dbms_output.put_line(v_temp.dname?||?'?'?||v_temp.loc); ?
end;?
---使用 %rowtype声明record变量
declare?
v_temp?dept%rowtype;??
begin?
v_temp.deptno?:=?20; ?
v_temp.dname?:=?'yuexingtian'; ?
v_temp.loc?:=?'qhd'; ?
dbms_output.put_line(v_temp.dname?||?'?'?||v_temp.loc); ?
end;
?
---SQL语句的运用
declare?
v_ename?emp.ename%type; ?
v_sal?emp.sal%type; ?
begin?
select?ename,sal?into?v_ename,v_sal?from?emp?where?empno?=?7369;??
dbms_output.put_line(v_ename?||'?'||v_sal);??
end;???
?
declare?
v_emp?emp%rowtype;??
begin?
select?*?into?v_emp?from?emp?where?empno?=?7369;??
dbms_output.put_line(v_emp.ename);??
end;??
?
--insert?语句 ?
declare?
v_deptno?dept.deptno%type?:=?50; ?
v_dname?dept.dname%type?:='yuexingtian'; ?
v_loc?dept.loc%type?:=?'秦皇岛'; ?
begin?
insert?into?dept2?values?(v_deptno,v_dname,v_loc); ?
commit; ?
end;?
?
---sql%rowcount 多少条记录被影响
declare?
v_deptno?emp2.deptno%type?:=?10; ?
v_count?number; ?
begin?
update?emp2?set?sal?=?sal/2?where?deptno?=?v_deptno; ?
dbms_output.put_line(sql%rowcount?||'条记录被影响'); ?
end;?
--create语句
begin?
execute?immediate?'create?table?T?(nnn?varchar2(20)?default?''yuexingtian'')'; ?
end;?
---if语句，取出7369的薪水，如果<1200,则输出'low',如果<2000则输出'middle',否则输出'high'.
declare?
v_sal?emp.sal%type; ?
begin?
select?sal?into?v_sal?from?emp ?
where?empno?=?7369;??
if(v_sal?<?1200)?then?
dbms_output.put_line('low');??
elsif(v_sal?<?2000)?then?
dbms_output.put_line('middle');??
else?
dbms_output.put_line('high');??
end?if;??
end;?
?
---循环 loop (相当于do while)
declare?
i?binary_integer?:=?1; ?
begin?
loop ?
dbms_output.put_line(i); ?
i?:=?i+1; ?
exit?when?(i>=11); ?
end?loop; ?
end;?
?
---when?……loop?（相当于while） ?
declare?
j?binary_integer?:=?1;? ?
begin?
while?j<11?loop ?
dbms_output.put_line(j); ?
j?:=?j+1; ?
end?loop; ?
end;? ?
---for?...in...?loop? ?
begin?
for?k?in?1..10?loop ?
dbms_output.put_line(k); ?
end?loop; ?
for?k?in?reverse?1..10?loop?--逆序 ?
dbms_output.put_line(k); ?
end?loop;? ?
end;?
?
--- 异常处理
declare?
v_temp?number(4);
begin?
select?empno?into?v_temp?from?emp?where?deptno?=?10; ?
exception ?
when?too_many_rows?then?--多条记录的异常 ?
dbms_output.put_line('记录太多了'); ?
when?others?then?
dbms_output.put_line('error'); ?
end; ?
declare?
v_temp?number(4); ?
begin?
select?empno?into?v_temp?from?emp?where?empno?=?4444; ?
exception ?
when?no_data_found?then?
dbms_output.put_line('没有数据'); ?
end;?
?
---记录数据库错误信息的errorlog
create?table?errorlog ?
( ?
id?number?primary?key, ?
errcode?number, ?
errmsg?varchar2(1024), ?
errdate?date?
); ?
?
create?sequence?seq_errorlog_id?start?with?1?increment?by?1;?--创建递增序列 ?
--PL/SQL ?
declare?
v_deptno?dept.deptno%type?:=10; ?
v_errcode?number; ?
v_errmsg?varchar2(1024); ?
begin?
delete?from?dept?where?deptno?=?v_deptno; ?
commit; ?
exception ?
when?others?then?
rollback; ?
v_errcode?:=?SQLCODE; ?
v_errmsg?:=?SQLERRM; ?
insert?into?errorlog?values?(seq_errorlog_id.nextval,v_errcode,v_errmsg,sysdate); ?
commit; ?
end;??
?
?
select?to_char(errdate,'YYYY-MM-DD?HH24:MI:ss')?from?errorlog;?---具体的出错时间。?
?
---游标
declare?
cursor?c?is? ?
select?*?from?emp; ?
v_emp?c%rowtype; ?
begin?
open?c; ?
fetch?c?into?v_emp; ?
dbms_output.put_line(v_emp.ename); ?
close?c; ?
end; ?
---游标，循环取出所有的记录。 ?
declare?
cursor?c?is?
select?*?from?emp; ?
v_emp?c%rowtype; ?
begin?
open?c; ?
loop ?
fetch?c?into?v_emp; ?
exit?when?(c%notfound); ?
dbms_output.put_line(v_emp.ename); ?
end?loop; ?
close?c;? ?
end;? ?
---游标while?循环 ?
declare?
cursor?c?is?
select?*?from?emp; ?
v_emp?c%rowtype; ?
begin?
open?c; ?
fetch?c?into?v_emp; ?
while?(c%found)?loop ?
dbms_output.put_line(v_emp.ename); ?
fetch?c?into?v_emp; ?
end?loop; ?
close?c; ?
end;? ?
---for循环?不用声明变量，不用open游标?不用close游标?不用fetch ?
declare?
cursor?c?is?
select?*?from?emp; ?
begin?
for?v_emp?in?c?loop ?
dbms_output.put_line(v_emp.ename); ?
end?loop; ?
end;?
---带参数的游标
declare?
cursor?c(v_deptno?emp.deptno%type,?v_job?emp.job%type) ?
is?
select?ename,sal?from?emp?where?deptno?=?v_deptno?and?job?=?v_job; ?
begin?
for?v_temp?in?c(30,'CLERK')?loop ?
dbms_output.put_line(v_temp.ename); ?
end?loop; ?
end;
?
---更新的游标
declare?
cursor?c? ?
is?
select?*?from?emp2?for?update; ?
begin?
for?v_temp?in?c?loop ?
if?(v_temp.sal?<?2000)?then?
update?emp2?set?sal?=?sal?*?2?where?current?of?c;? ?
elsif?(v_temp.sal?=?5000)?then?
delete?from?emp2?where?current?of?c; ?
end?if; ?
end?loop; ?
commit; ?
end;?
?
----创建存储过程
create?or?replace?procedure?p ?
is?
cursor?c?is?
select?*?from?emp2?for?update; ?
begin?
for?v_emp?in?c?loop ?
if?(v_emp.deptno?=?10)?then?
update?emp2?set?sal?=?sal?+?10?where?current?of?c; ?
elsif?(v_emp.deptno?=?20)?then?
update?emp2?set?sal?=?sal?+?20?where?current?of?c;??
else?
update?emp2?set?sal?=?sal?+?50?where?current?of?c;??
end?if;??
end?loop;??
commit;??
end;???
?
---执行存储过程??
exec?p;??
---或者??
begin?
p;??
end;?
?
---带参数的存储过程
create?or?replace?procedure?
max_num(v_a?in?number,v_b?number,v_ret?out?number,v_temp?in?out?number) ?
is?
begin?
if(v_a?>?v_b)?then?
v_ret?:=?v_a; ?
else?
v_ret?:=?v_b; ?
end?if; ?
v_temp?:=?v_temp?+?1; ?
end; ?
---调用这个存储过程 ?
declare?
v_a?number?:=3; ?
v_b?number?:=4; ?
v_ret?number; ?
v_temp?number?:=5; ?
begin?
max_num(v_a,?v_b,?v_ret,?v_temp); ?
dbms_output.put_line(v_ret); ?
dbms_output.put_line(v_temp); ?
end;
?
---函数
create?or?replace?function? ?
sal_tax(v_sal?number) ?
return?number ?
is?
begin?
if?(v_sal?<?2000)?then?
return?0.10; ?
elsif?(v_sal?<?2750)?then?
return?0.15; ?
else?
return?0.20; ?
end?if; ?
end;? ?
--调用这个函数(别的函数怎么用，这个函数就怎么用) ?
select?ename,sal,sal_tax(sal)?from?emp;?
----触发器
--创建一个日志表 ?
create?table?emp2_log ?
( ?
uname?varchar2(20), ?
action?varchar2(10), ?
atime?date?
); ?
--创建一个触发器 ?
create?or?replace?trigger?trig ?
after?insert?or?delete?or?update?on?emp2?for?each?row ?
begin?
if?inserting?then?
insert?into?emp2_log?values?(user,'insert',sysdate);??
elsif?updating?then?
insert?into?emp2_log?values?(user,'update',sysdate);??
elsif?deleting?then?
insert?into?emp2_log?values?(user,'delete',sysdate);??
end?if;??
end;??
?
--调用这个触发器??
update?emp2?set?sal?=?sal*2?where?deptno?=?30;?
?
---更改有依赖关系的表的字段值的建立的一个触发器
create?or?replace?trigger?trip_change ?
after?update?on?dept2 ?
for?each?row ?
begin?
update?emp2?set?deptno?=?:NEW.deptno?where?deptno?=?:OLD.deptno; ?
end;???
?
---触发这个触发器 ?
update?dept2?set?deptno?=?99?where?deptno?=?10;?
---树状结构的存储与展示
drop?table?article; ?
create?table?article ?
( ?
id?number?primary?key, ?
cont?varchar2(4000), ?
pid?number, ?
isleaf?number(1),--0?代表非叶子节点，1?代表叶子节点 ?
alevel?number(2) ?
); ?
insert?into?article?values(1,'蚂蚁大战大象',0,0,0); ?
insert?into?article?values(2,'蚂蚁大战大象',1,0,1); ?
insert?into?article?values(3,'蚂蚁大战大象',2,1,2); ?
insert?into?article?values(4,'蚂蚁大战大象',2,0,2); ?
insert?into?article?values(5,'蚂蚁大战大象',4,1,3); ?
insert?into?article?values(6,'蚂蚁大战大象',1,0,1); ?
insert?into?article?values(7,'蚂蚁大战大象',6,1,2); ?
insert?into?article?values(8,'蚂蚁大战大象',6,1,2); ?
insert?into?article?values(9,'蚂蚁大战大象',2,0,2); ?
insert?into?article?values(10,'蚂蚁大战大象',9,1,3); ?
commit;?
---用存储过程展示树状结构(用递归的方式实现)
create?or?replace?procedure?p_tree(v_pid?article.pid%type,?v_level?binary_integer)?is?
cursor?c?is?select?*?from?article?where?pid?=?v_pid; ?
v_preStr?varchar2(1024)?:=''; ?
begin?
for?i?in?1..v_level?loop ?
v_preStr?:=?v_preStr?||?'****'; ?
end?loop; ?
for?v_article?in?c?loop ?
dbms_output.put_line(v_preStr?||?v_article.cont); ?
if(v_article.isleaf?=?0)?then?
p_tree(v_article.id,?v_level?+?1); ?
end?if; ?
end?loop; ?
end;? ?
--执行这个存储过程 ?
exec?p_tree(0,0);? ?
--SQL>?exec?p_tree(0,0); ?
--蚂蚁大战大象 ?
--****蚂蚁大战大象 ?
--********蚂蚁大战大象 ?
--********蚂蚁大战大象 ?
--************蚂蚁大战大象 ?
--********蚂蚁大战大象 ?
--************蚂蚁大战大象 ?
--****蚂蚁大战大象 ?
--********蚂蚁大战大象?????????????????????? ?
--********蚂蚁大战大象?????????????????????? ?
--PL/SQL?过程已成功完成。??
