PL/SQL 11g R2 —— 存儲過程

--子程式：過程和函數
--帶有out參數的存儲過程
create or replace procedure query_emp( v_empno in emp.empno%type, v_ename out emp.ename%type, v_sal out emp.sal%type) 
is
begin 
  select emp.ename,emp.sal into v_ename,v_sal from emp where emp.empno=v_empno;
end;
/
--調用
var name varchar2;
var sal number;
exec query_emp(7788,:name,:sal);

--帶有in out的存儲過程
create or replace procedure counts( num1 in out number, num2 in out number) 
as 
  v1 number; 
  v2 number;
begin 
  v1:=num1/num2; 
  v2:=mod(num1,num2); --餘數 
  num1:=v1; 
  num2:=v2;
end;
/
--調用
var n1 number;
var n2 number;
exec :n1:=100;
exec :n2:=30;
exec counts(:n1,:n2);

--為存儲過程傳遞參數
create or replace procedure add_bonus( v_ename bonus.ename%type, v_job bonus.job%type default null, v_sal bonus.sal%type default null) 
as
begin 
  insert into bonus(ename,job,sal) values(v_ename,v_job,v_sal); 
  commit;
end;
/
--調用
--位置傳遞
exec add_bonus(10,'zhangsan',1000);
exec add_bonus(20,'lisi');
exec add_bonus(30);
--名稱傳遞
exec add_bonus(v_job=>'老師',v_ename=>'張三',v_sal=>800);
exec add_bonus(v_ename=>'李四',v_sal=>1200);
--檢視使用者所有的子程式
select t.text from user_source t where t.name='COUNTS';
select * from user_source;