oracle自定义函数

http://blog.csdn.net/smartsmile2012/article/details/8242841
创建函数语法如下：

CREATE[OR REPLACE]FUNCTION function_name (arg1 [ { IN | OUT | IN OUT }] type1 [DEFAULT value1], [arg2 [ { IN | OUT | IN OUT }] type2 [DEFAULT value1]], ...... [argn [ { IN | OUT | IN OUT }] typen [DEFAULT valuen]]) [ AUTHID DEFINER | CURRENT_USER ]
RETURN return_type IS|AS     <类型.变量的声明部分>
BEGIN    执行部分     
RETURN expression 
EXCEPTION     异常处理部分 
END function_name;

1.建立无参数的函数
CREATE OR REPLACE FUNCTION cur_datetime
RETURN VARCHAR2
IS
BEGIN
  RETURN TO_CHAR(sysdate,
    'YYYY"年"MM"月"DD"日"HH24"时"MI"分"SS"秒"');
END;

2.建立带有输入参数的函数

CREATE OR REPLACE FUNCTION get_yeardays(select_year varchar2)
RETURN NUMBER
AS
  num_days number;
BEGIN
  num_days := to_date(select_year || '1231', 'yyyymmdd') -
              trunc(to_date(select_year, 'yyyy'), 'year');
  return num_days;
END;
计算全年统计天数：selct get_yeardays（2014）from dual;

3.建立带有输出参数的函数
CREATE OR REPLACE FUNCTION get_info
         (eno NUMBER,title OUT VARCHAR2)
          RETURN VARCHAR2
          AS
            vname scott.emp.ename%TYPE;
          BEGIN
            SELECT ename,job INTO vname,title FROM scott.emp
              WHERE empno=eno;
            RETURN vname;
          END;

4.建立带有输入输出参数的函数
CREATE OR REPLACE FUNCTION get_upd_info
(eno NUMBER,sal_chg IN OUT NUMBER) RETURN VARCHAR2
AS
  name emp.ename%TYPE;
BEGIN
  UPDATE emp SET sal=sal+sal_chg WHERE empno=eno
    RETURNING ename,sal INTO name,sal_chg;
  RETURN name;
END;

http://www.cnblogs.com/dogxuefeng/archive/2012/11/22/2782593.html