不带任何参数的函数：
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
?