Oracle中procedure和function创建举例

Procedure创建与执行：

Case1：
create or replace procedure procedure_name
 (id user.table_name.columne_name%type)
 is 
 begin 
   delete from user.table_name where columne_name=id;
 exception 
    when others then 
      dbms_output.put_line('errors');
 end；

执行
execute procedure_name(传入参数);

Case2：
create or replace procedure procedure_name
 (
  v_empno in varchar2,
  v_sal out number
 ) is
 vsal number(7,2);
 begin
    select sal into vsal from emp where empno=v_empno;
    v_sal:=vsal;
 end;

执行：
var vsal number；
exec procedure_name('7935',:vsal);

Function创建与执行：

Case1：
create or replace function function_name(v_ename varchar2)
return number
is
v_sal number(7,2);
begin
    select nvl(sal,0) into v_sal from emp where lower(ename)=lower(v_ename);
    return v_sal;
end;

执行：
var vsla number;
call function_name('7935') into :vsal;

 

转载于:https://www.cnblogs.com/genezhao/p/6899118.html