创建函数:
格式:create or replace function func(参数 参数类型)
Return number
Is
Begin
--------业务逻辑---------
End;
--创建函数create or replace function func (dno number)return numberis t_max number;begin select max(sal) into t_max from emp twhere deptno = dno;return t_max;end ;--调用存储过程和调用函数的例子(区别)declaret_maxsal number;begint_maxsal:=func(10);dbms_output.put_line(t_maxsal);dbms_output.put_line(func(10));end;create or replace procedure proc(dno in number,maxsal out number)is t_maxsal number;beginselect max(sal) into t_maxsal from emp where deptno = dno;maxsal:=t_maxsal;end;declaret_sal number := 0;begin proc(10,t_sal);dbms_output.put_line(t_sal);end;create or replace function func(a number, a number)return numberis t_result number := 0;begin t_result:=a + b;return t_result;end;begindbms_output.put_line(func(10, 20));end;
--删除函数drop function func;
--删除存储过程drop procedure proc;--创建包(包里可以写存储过程和函数)create or replace package packprocedure proadd(a number, b number)procedure prosub(a number, b number)end pack;begin pack.proadd(3,1);pack.prosub(3,2);end;
注:以上调用都是指在plsql里的sql窗口调用