Function笔记

Function(函数)
    用于返回特定数据，当经常需要返回特定数据时应考虑建立函数
创建函数时定义参数与过程基本相同
＊＊在函数的头部要指出返回的数据类型（不能指长度）：...RETURN VARCHAR2 IS...
＊＊在函数的执行部分指出返回的变量名：...RETURN V_NAME;...
＊＊RETURN的数据不需要使用变量接收，而OUT的参数必须使用变量接收
＊＊RETURN的数据可以是记录类型或者集合类型
＊＊可以在函数创建过程中使用异常
函数的调用：
    1，函数具有返回值，所以只能作为表达式的一部分使用
    2，当在SQL语句中调用函数时：
        a,只能调用服务器端的函数
        b,不能调用带有OUT参数或者IN OUT参数的函数
        c,调用的函数只能使用SQL支持的标准数据类型，不能使用PL/SQL特有
          的数据类型，如：RECORD TABLE BOOLEAN...
        d,调用的函数不能包括DML和TCL

======================================不带参数的函数====================================

SCOTT@uplookin>> CREATE OR REPLACE FUNCTION get_time RETURN VARCHAR2 IS
  2  BEGIN
  3  RETURN TO_CHAR(SYSDATE,'HH24:MI:SS');
  4  END;
  5  /

Function created.

SCOTT@uplookin>> SELECT GET_TIME FROM DUAL;

GET_TIME
----------------------------------------------------------
11:41:42

SCOTT@uplookin>>

================================带有输入输出参数的函数===================================

SCOTT@uplookin>> ed
Wrote file afiedt.buf

  1  CREATE OR REPLACE FUNCTION get_name(
  2     v_empno IN emp.empno%TYPE,
  3     v_dname OUT dept.dname%TYPE) RETURN VARCHAR2
  4  IS
  5     v_ename emp.ename%TYPE;
  6  BEGIN
  7     SELECT e.ename,d.dname INTO v_ename,v_dname FROM emp e natural join dept d where e.empno=v_empno;
  8     RETURN v_ename;
  9* END;
SCOTT@uplookin>> /

Function created.

SCOTT@uplookin>> declare
  2  department_name dept.dname%type;
  3  begin
  4  dbms_output.put_line(get_name(&empno,department_name));
  5  dbms_output.put_line(department_name);
  6  end;
  7  /
Enter value for empno: 7788
SCOTT
RESEARCH

PL/SQL procedure successfully completed.

SCOTT@uplookin>>

==============================函数的返回数据为集合类型===================================

SCOTT@uplookin>> CREATE OR REPLACE FUNCTION bulk_get_name(
  2  v_deptno IN emp.deptno%type) RETURN ename_table_type IS
  3  ename_table ename_table_type;
  4  BEGIN
  5  SELECT ename BULK COLLECT INTO ename_table FROM emp WHERE deptno=v_deptno;
  6  RETURN ename_table;
  7  END;
  8  /

Function created.

SCOTT@uplookin>> ed
Wrote file afiedt.buf

  1  declare
  2  name_table ename_table_type;
  3  begin
  4  name_table :=bulk_get_name(&deptno);
  5  dbms_output.put_line(name_table.count);
  6  for i in 1..name_table.count loop
  7  dbms_output.put_line(name_table(i));
  8  end loop;
  9* end;
SCOTT@uplookin>> /
Enter value for deptno: 20
5
SMITH
JONES
SCOTT
ADAMS
FORD

PL/SQL procedure successfully completed.

SCOTT@uplookin>>

===============================返回数据为BOOLEAN类型=======================================

SCOTT@uplookin>> ed
Wrote file afiedt.buf

  1  CREATE OR REPLACE FUNCTION valid_deptno(
  2     v_deptno dept.deptno%type)
  3     RETURN BOOLEAN
  4  IS
  5     temp INT;
  6  BEGIN
  7     SELECT COUNT(*) INTO temp FROM dept WHERE deptno=v_deptno;
  8     IF temp=0 THEN
  9             RETURN FALSE;
 10     ELSE
 11             RETURN TRUE;
 12     END IF;
 13* END;
SCOTT@uplookin>> /

Function created.
SCOTT@uplookin>> begin
  2  if valid_deptno(&deptno) then
  3  dbms_output.put_line('YES!');
  4  else
  5  dbms_output.put_line('NO!');
  6  end if;
  7  end;
  8  /
Enter value for deptno: 10
YES!

PL/SQL procedure successfully completed.

SCOTT@uplookin>> /
Enter value for deptno: 12
NO!

PL/SQL procedure successfully completed.

SCOTT@uplookin>>