oracle存储过程

--不带参数的存储过程
create or replace procedure out_time
is
begin
  dbms_output.put_line(systimestamp);
end;

--带IN输入参数的

create or replace procedure add_employee
(eno number,name varchar2,sal number,
job varchar2 ,dno number)
is
e_integrity exception;
pragma exception_init(e_integrity,-2291);
begin
insert into emp(empno,ename,sal,job,deptno)
    values(eno,name,sal,job,dno); 
exception
     when dup_val_on_index then
  raise_application_error(-20000,'雇员号不能重复');
     when e_integrity  then
   raise_application_error(-20001,'部门号不存在');
end;

--带OUT参数的过程
create or replace procedure query_employee(
v_no number,v_ename out varchar2 ,v_sal out number 
)
is
begin
select ename,sal into v_ename,v_sal from emp where empno=v_no;
exception
when no_data_found then
raise_application_error(-2000,'该雇员不存在');
end;
--执行
/
var name varchar2(10)
var salary number
exec query_employee(7788,:name,:salary)

--IN OUT

create or replace procedure compute
(num1 in out number,num2 in out number)
is
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 compute(:n1,:n2)
print n1 n2