oracle學習總結5(遊標、觸發器、過程、方法)

標籤：

1:捕獲plsql拋出的異常

declare
v_ename emp.ename%type;
begin
select ename into v_ename from emp where empno=10;
exception
when too_many_rows then
dbms_output.put_line(‘太多值了‘);
when others then
dbms_output.put_line(‘error‘);
end;
/

2:遊標

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;
/

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迴圈：

declare
cursor c is
select * from emp;
v_emp c%rowtype;
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) is
select * from emp where deptno = v_deptno;
v_emp c%rowtype;
begin
for v_emp in c(‘10‘) loop
dbms_output.put_line(v_emp.ename);
end loop;
end;
/

3:procedure預存程序

create or replace procedure p(v_a in number,v_b number,v_retu out number,v_temp in out number)
is
begin
if(v_a>v_b) then
v_retu := v_a;
else
v_retu :=v_b;
end if;
v_temp := v_temp+1;
end;
/

調用預存程序 p:

declare
v_a number :=3;
v_b number :=4;
v_retu number;
v_temp number :=5;
begin
p(v_a,v_b,v_retu,v_temp);
dbms_output.put_line(v_retu);
dbms_output.put_line(v_temp);
end;
/

4:函數

create or replace function sal_tax(sal number)
return number
is
begin
if(sal>3000) then
return 0.1;
elsif(sal>4000) then
return 0.2;
else
return 0.3;
end if;
end;
/

5:觸發器
create or replace trigger trig
after insert or update or delete on emp for each row
begin
if inserting then
insert into emp_log values(USER ,‘insert‘,sysdate);
elsif updating then
insert into emp_log values(USER ,‘update‘,sysdate);
elsif deleting then
insert into emp_log values(USER ,‘delete‘,sysdate);
end if;
end;
/

 

oracle學習總結5(遊標、觸發器、過程、方法)