pl/sql 编程（十）

• 无参函数的创建

create function myuser return varchar2
is
       v_qnty varchar2(20);
begin
   select sys.login_user into v_qnty from dual;
   return v_qnty;
end;
 select myuser()  from dual

• 有参数的函数创建

create or replace function fun_goodsinfo (g_id in number,g_name out varchar2,g_remark out varchar2)
return varchar2 is
       v_g_name varchar2(400);
       v_g_remark varchar2(400);
       begin
       select goodsname,remark into v_g_name,v_g_remark from goods where goodsid =g_id;
       g_name :=v_g_name;
       g_remark :=v_g_remark;
       return v_g_name;
       end;
       
       declare
       v_name varchar2(200);
       v_remark varchar(200);
       v_result varchar(200);
       begin
       v_result := fun_goodsinfo(4,v_name,v_remark );
       dbms_output.put_line(v_result);
       dbms_output.put_line(v_name);
       dbms_output.put_line(v_remark);
       end;

• 查看函数

　　　　select object_name ,object_id,status from user_objects where object_type = 'FUNCTION';
       select * from user_source where type ='FUNCTION' and name='MYUSER' order by line;

• 重新编译函数

alter function MYUSER compile;

• 删除函数

   drop function MYUSER;