PL/SQL复习十 函数

不带任何参数的函数：

create or replace function get_user

return varchar2

is

  v_user varchar2(100);

begin

  select username into v_user from user_users;

  return v_user;

end;

/

调用1：

SQL> var v1 varchar2(100);

SQL> exec :v1 :=get_user;

调用2：

SQL> select get_user from dual;

调用3:

SQL> exec dbms_output.put_line(get_user);

--------------------------------------------------------------------------------------------------

带有in参数的函数：

create or replace function get_sal

(

  name in varchar2

)

return number

is

  v_sal emp.sal%type;

begin

  select sal into v_sal from emp where upper(ename) = upper(name);

  return v_sal;

end;

/

调用1：

SQL> var v1 number;

SQL> exec :v1 := get_sal('scott');

调用2：

SQL> select get_sal('scott') from dual;

--------------------------------------------------------------------------------------------------

带有out参数的函数(用于返回多个值):

create or replace function get_info

(

  name in varchar2,

  title out varchar2

)

return varchar2

is

  deptname dept.dname%type;

begin

  select a.job, b.dname into title,deptname from emp a, dept b

  where a.deptno = b.deptno and upper(a.ename) = upper(name);

  return deptname;

end;

/

调用1：

SQL> var job varchar2(20);

SQL> var dname varchar2(20);

SQL> exec :dname := get_info('scott',:job);

 

PL/SQL procedure successfully completed

dname

---------

RESEARCH

job

---------

ANALYST

注意：带有输出参数的函数不能用SQL语句调用

--------------------------------------------------------------------------------------------------

带有 in out类型参数的函数：

create or replace function fun_in_out

(

  num1 in number,

  num2 in out number

)

return number

is 

  v_result number(6);

  v_remainder number;

begin

  v_result := num1/num2;

  v_remainder := mod(num1,num2);

  num2 := v_remainder;

  return v_result;

end;

/

调用：

SQL> var result1 number;

SQL> var result2 number;

SQL> exec :result2 := 30;

 

SQL> exec :result1 := fun_in_out(100,:result2);

 

PL/SQL procedure successfully completed

result1

---------

3

result2

---------

10