oracleノート(ストレージ・プロシージャstoreprocedure、関数function、トリガtrigger)

ストレージ・プロシージャ:名前付きpl-sqlストレージ・ブロック

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;


--    
create or replace procedure p 
	-- in    out     in out      in
	(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 
	p(v_a,v_b,v_ret,v_temp); 
	dbms_output.put_line(v_ret); 
	dbms_output.put_line(v_temp); 
end; 
/ 

ストレージ・プロシージャのエラーはshow errorの使用エラーを報告していません.
-------------------------------------------------
---
関数function----------------------

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;

--------フリップフロップ-------

create table emp2_log
(
uname varchar2(20),
action varchar2(10),
atime date
);
--       

create or replace trigger trig
	--after/befor
	--for each row              
	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;

drop trigger trig;

create or replace trigger trig
	after update on dept 
	for each row 
begin
	update emp set deptno =:NEW.deptno  where deptno =:OLD.deptno;
end;

update dept set deptno =99 where deptno =10;