oracle function¿ª·¢

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