ORACLE函数开发

1、建立函数的语法：

CREATE [OR REPLACE] FUNCTION 函数名

[ (arg1 [mode] datatype[,........])] --当指定参数数据类型时，不能指定其长度。mode值：IN、OUT、IN OUT，当mode 未指定时，表示为IN

RETURN datatype --用于指定函数返回值的数据类型，不能指定其长度

IS|AS

pl/sql block; --当建立函数时，在函数头部必须要带有RETURN子句，在函数体内至少要包含一条RETURN语句。

例1、无参函数

SQL> CREATE OR REPLACE function HX_01   --创建HX_01函数
  2  RETURN VARCHAR2
  3  IS
  4    v_name varchar2(20);
  5  begin
  6    select ename into v_name from emp where empno=7369;
  7    return v_name;
  8  end;
  9  /

Function created

SQL> select hx_01 from dual; --执行函数

HX_01
--------------------------------------------------------------------------------
SMITH

例2、带有一个IN参数

SQL> CREATE OR REPLACE function HX_02  --创建HX_02函数
  2  (v_no in emp.empno%type)
  3  RETURN VARCHAR2
  4  IS
  5    v_name varchar2(20);
  6    v_sal emp.sal%type;
  7    v_dname dept.dname%type;
  8    v_return varchar2(100);
  9  begin
 10    select a.ename,a.sal,b.dname into v_name,v_sal,v_dname from emp a,dept b where a.deptno=b.deptno and empno=v_no;
 11    v_return:='姓名：'||rpad(v_name,8)||LPAD('工资：',6)||v_sal||lpad('部门',6)||v_dname;
 12    return v_return;
 13  end;
 14  /

Function created
SQL> begin
  2  dbms_output.put_line(hx_02(7566));  --执行函数
  3  end;
  4  
  5  /

PL/SQL procedure successfully completed
 输出：姓名：JONES   工资：2975  部门RESEARCH
或：
SQL>   select hx_02(7566) from dual;

HX_02(7566)
--------------------------------------------------------------------------------
姓名：JONES   工资：2975  部门RESEARCH

例3、带有IN、OUT参数

  SQL> CREATE FUNCTION hx_03    --创建HX_03函数
  2  (v_n1 emp.empno%type,v_n2 out emp.ename%type,v_n3 out emp.sal%type,v_n4 out dept.dname%type)
  3  return emp.job%type
  4  as
  5   job emp.job%type;
  6  begin
  7    select a.job,a.ename,a.sal,b.dname into job,v_n2,v_n3,v_n4
  8    from emp a,dept b
  9    where a.deptno=b.deptno
 10    and a.empno=v_n1;
 11    return job;
 12  end;
 13  /

Function created


declare 
  v1 emp.job%type;
  v2 emp.ename%type;
  v3 emp.sal%type;
  v4 dept.dname%type;
begin
  v1:=hx_03(7566,v2,v3,v4); --使用刚才定义的变量接收函数的输出值
  dbms_output.put_line('姓名:'||v2||' '||'职业:'||v1||' '||'薪水:'||v3||' '||'部门:'||v4);
end;

输出：姓名:JONES 职业:MANAGER 薪水:2975 部门:RESEARCH

例4、带有in out参数

CREATE OR REPLACE FUNCTION HX_04(v_name in out varchar2)   --创建HX_04函数
RETURN VARCHAR2
AS
BEGIN
 SELECT DEPT.DNAME INTO V_NAME FROM DEPT,EMP WHERE DEPT.DEPTNO=EMP.DEPTNO AND EMP.ENAME=V_NAME;
 RETURN NULL;
END;

DECLARE  --执行函数
  A VARCHAR2(20);
  B VARCHAR2(20);
BEGIN
  A:='WARD';
  B:=HX_04(A);
  DBMS_OUTPUT.put_line('A:='||A||' B:='||B);
END;
输出：A:=SALES B:=

2、删除函数语法

drop function 函数名;