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;