函数

函数与存储过程类似，也是数据库中存储的已命名的PL/SQL程序块，它用于返回特定的数据，如果客户应用经常需要返回特定的数据，就可以基于这些操作建立函数。

函数的创建语法：

CREATE OR REPLACE FUNCTION function_name                             --function_name,函数名称
(argument1[model] datatype1,argument2 [model] datatype2)    --argument指定函数参数、model用于指定参数类型
RETURN  datatype                                            --指定返回值的数据类型
IS|AS                                                              --用于开始PL/SQL块
PL/SQL BLOCK;

案例6：创建无参数的函数，获取当前系统时间，并返回一个字符串类型的时间。

CREATE OR REPLACE FUNCTION func_datetime
RETURN VARCHAR2 IS
BEGIN
       RETURN TO_CHAR(sysdate,’yyyy-MM-DD HH:MI:SS’);
END;

调用无参数的函数调用

BEGIN
       DBMS_OUTPUT.PUT_LINE(FUNC_DATETIME);
END;

案例7：建立有输入参数的函数，根据输入的日期判断是否是周日。

CREATE OR REPLACE FUNCTION func_isHoliday(p_date DATE)
RETURN INTEGER
AS
  v_weekday integrt := -1;
BEGIN
       SELECT TO_CHAR(p_date,’D’) INTO v_weekdayFROM DUAL;
       --DBMS_OUTPUT.PUT_LINE(v_weekday);
       --注意每周是从周日开始作为第一天—
       IF v_weekday=1 OR v_weekday=7 THEN
              RETURN 1;
       ELSE
              RETURN 0;
       END IF;
END;

调用函数：

DECLARE
       vday DATE := DATE ‘2010-11-12’;
BEGIN
       DBMS_OUTPUT.PUT_LINE(func_isHoliday(vday));
END;

案例8：建立有输出参数的函数，根据输入参数员工id输出员工的职位。

CREATE OR REPLACE FUNCTION func_getinfo
(empidNUMBER,position OUT VARCHAR2)
RETURN VARCHAR2 AS
       name employees.name%type;
BEGIN
       SELECT t.name,p.postname INTOname,position FROM employees t,post p
           WHERE t.position=p.postid ANDt.id=empid;
       RETURN name;
END;

案例8函数的调用：

DECLARE
       v_name employees.name%type;
       v_post post.postname%type;
BEGIN
       v_name := FUNC_GETINFO(2,v_post);
       dbms_output.put_line(‘姓名’||v_name||’职位：’||v_post);
END;

函数和存储过程的区别

 

存储过程	函数
作为PL/SQL语句执行	作为表达式的一部分调用
在规范中不能包含RETURN语句	必须在规范中包含RETURN自己
不返回任何值	必须返回单个值
可以包含RETURN语句，但是与 函数不同，他不能用于返回值	必须包含至少一条RETURN语句

存储过程使用限制少一些，功能强大一些，但是却不能直接饮用返回值，而函数的限制较多，功能也少但是可以直接引用返回值，因此存储过程多用于复杂的数据计算，而函数更模块化。

 

程序包是一种数据库对象，它是对相关PL/SQL类型、子程序、游标、异常、变量和常量的封装。

程序包包括程序包规范和程序包主体。在程序包规范中可以声明类型、变量、常量、异常、游标和子程序。程序包主体用于实现在程序包规范中定义的游标、子程序。

程序包规范包含一些应用程序可见的公共对象和类型声明，它是与应用程序的接口，规范包含应用程序所需的程序包资源。如果程序包规范只声明类型、常量、变量和异常，则不需要有程序包主体。只有子程序和游标才具有底层实现或定义，因此他们必须有程序包主体。

程序包规范语法：

CREATE[OR REPLACE] PACKAGE package_name               --package_name是包的名称
IS|AS      
       [Public type and item declarations]                            --声明公共类型、常量、变量、异常和游标等
       [Subprogram specifications]                              --声明PL/SQL子程序，也就是存储过程和函数
END [package_name]

在程序包规范中声明的项可以在程序包之外使用。这样的项成为“公用对象”；

案例9：程序包规范示例。

CREATE OR REPLACE PACKAGE pack_me IS
       PROCEDURE emp_proc(num NUMBER); --子程序的实现定义需要在程序包主体中给出
       FUNCTION emp_fun(empno NUMBER) RETURNVARCHAR2;--子程序的实现定义需要在程序包主体中给出
ENDpack_me;

程序包主体语法：程序包主体包含了程序包规范中声明的每个游标和子程序的具体实现。程序包主体的初始化部分是可选的，它可用于初始化程序包中的变量。程序包的初始化部分既不能调用程序包，也不能将参数传递给程序包，而且，程序包的初始化部分仅运行一次。

程序包主体语法：

CREATE OR REPLACE PACKAGE BODY package_name       --package_name是包的名称，
IS|AS
       [Public type and item declarations]              --声明变量、常量、游标、异常或类型
       [Subprogram bodies]                          --定义公共和私有PL/SQL子程序
[BEGIN
Initialization_statements]
END[package_name]

案例10：程序包主体示例

CREATE OR REPLACE PACKAGE BODY pack_me AS
       PROCEDURE emp_proce(num NUMBER) IS
              e_mail VARCHAR2(50);
       BEGIN
              SELECT t.e_mail INTO e_mail FROM employeest WHERE t.id=num;
              dbms_output.put_line(e_mail);
       END emp_proc;
       FUNCTION emp_fun(empno NUMBER) RETURN VARCHAR2 IS
              emp_name VARCHAR2(10);
       BEGIN
              SELECT t.name INTO emp_name FROM employees t WHERE t.id=empno;
              RETURN emp_name;
       END emp_fun;
ENDpack_me;