PL/SQL 11g R2 —— 函数

--带in参数的函数
create or replace function get_ename(v_empno in number)return varchar2
is 
  v_ename emp.ename%type;
begin 
  select emp.ename into v_ename from emp where emp.empno=v_empno; 
  return v_ename;
end;
/
--使用变量接受函数返回值
var name varchar2(2000);
exec :name:=get_ename(7369);
--在sql中使用函数
select get_ename(7499) from dual;
--使用包dbms_output调用
set serveroutput on
exec dbms_output.put_line(get_ename(7521));

--带out参数的函数
create or replace function get_info( v_empno in number, v_sal out number) return varchar2 
as 
  v_ename emp.ename%type;
begin 
  select emp.ename,emp.sal into v_ename,v_sal from emp where emp.empno=v_empno; 
  return v_ename;
end;
/
var name varchar2(2000);
var sal number;
exec :name:=get_info(7788,:sal);

--带in out参数的函数
create or replace function fcounts( num1 in number, num2 in out number) return number 
as 
  no1 number; 
  no2 number;
begin 
  no1:=num1/num2; 
  no2:=mod(num1,num2); 
  num2:=no2; 
  return no1;
end;
/
var no1 number;
var no2 number;
exec :no2:=30;
exec :no1:=fcounts(100,:no2);

--列出当前用户的所有对象
select * from user_objects;
--列出当前用户所有子程序的源代码
select * from user_source;
--列出错误原因
select * from user_errors;
show errors function fcounts;
--列出对象依赖关系
select * from user_dependencies;