入门精通7-PRODECUTE、FUNCTION、PACKAGE

过程、函数、包的学习笔记，其中TB1表创建方式CRATE TABLE TB1 AS SELECT * FROM EMP;

对于常用的PL/SQL程序，可以将它编译保存在数据库中，这类程序包括过程、函数、包和触发器。

过程

过程是一系列语句的集合，创建语法为：

CREATE [OR REPLACE] PROCEDURE <过程名>
	(<参数1> [方式] <数据类型>
	[<参数2> [方式] <数据类型>]
...)
AS|IS
<PL/SQL过程体>

[OR REPLACE]的作用为若过程需要修改则可以直接在现有基础上修改而不用删除后新建过程。语法中CREATE [OR REPLACE] PROCEDURE <过程名>(...) IS|AS 类似于匿名过程中DECLARE的作用，该语句后面可以直接接声明过程内部要用到的变量、游标等内容。过程参数的方式有IN、OUT和IN OUT三种，其中IN表示调用过程时要输入的参数。例如需要根据实际参数统计TB1表的部门人数，实现如下：

create or replace procedure COUNT_DE(IN_DNO IN number) AS
OUT_NUM NUMBER;
begin
  SELECT COUNT(TB1.DEPTNO) INTO OUT_NUM FROM TB1 WHERE TB1.DEPTNO=IN_DNO;
  DBMS_OUTPUT.put_line(IN_DNO||'部门人数为'||OUT_NUM);
end COUNT_DE;

这个过程执行时需要一个数字型的参数，指定需要查询部门的编号（deptno）。在SQL中调用过程的语法为CALL <过程名>，要注意的是不过这个过程是否带参数，调用的时候都需要带上()。实际使用如下：

CALL COUNT_DE(10);--查询10部门人数
CALL COUNT_DE(20);--查询20部门人数

PLSQL中执行则不需要CALL关键字，如下

BEGIN
   COUNT_DE(10);--查询10部门人数
   COUNT_DE(20);--查询20部门人数
END;

若需要删除过程则使用该语法DROP PROCEDURE <过程>。

函数

函数（function）与过程的区别在于函数必须有一个返回值。创建语法为：

CREATE [OR REPLACE] FUNCTION <函数>（
	<参数1> <方式> <数据类型> 
	[,<参数2> <方式> <数据类型>]
...）RETURN <返回数据类型>
IS|AS
PLSQL程序体  --必须包含一个RETURN子句

例如根据参数查询部门的人数并返回

create or replace function F_DNUM(IN_DNO IN NUMBER) return number is
  R_FUNC number :=0;
begin
  SELECT COUNT(TB1.DEPTNO) INTO R_FUNC FROM TB1 WHERE TB1.DEPTNO=IN_DNO;
  return(R_FUNC);
end F_DNUM;

在SQL中调用方式如下

SELECT EMP.*
  FROM EMP
 INNER JOIN (SELECT DEPTNO, COUNT(1) AS N FROM TB1 GROUP BY DEPTNO) T
    ON T.DEPTNO = EMP.DEPTNO
 WHERE T.N =F_DNUM(20);

在PLSQL结构中的调用方法如下

BEGIN
   DBMS_OUTPUT.put_line('10部门人数为'||F_DNUM(10));
   DBMS_OUTPUT.put_line('20部门人数为'||F_DNUM(20));
END;

包

程序包(PACKAGE)简称包，是相关PLSQL块或者元素（变量、常量、游标、函数、过程）等的集合。包分为说明和包体两部分，说明部分是程序的接口，只是函数、过程、游标的声明或者名称部分，包体则是它们的具体实现。包体更新不会导致包重新编译，说明部分变动则会，所以应尽量减少说明部分内容。
的创建分为说明部分创建与包体部分创建两步。说明的创建如下

CREATE PACKAGE <包名> IS
	常量、变量、数据类型的定义;
	游标定义头部;
	函数、过程的定义、参数列表以及函数返回类型;
END <包名>;

举例

create or replace package P_TB is
  procedure COUNT_DE(IN_DNO IN number) ;
  function F_DNUM(IN_DNO IN NUMBER) return number; 
end P_TB;

包体的创建为

CREATE PACKAGE BODY <包名> AS
	游标、函数、过程的定义;
END <包名>;

举例

create or replace package body P_TB AS
procedure COUNT_DE(IN_DNO IN number) AS
   OUT_NUM NUMBER;
begin
  SELECT COUNT(TB1.DEPTNO) INTO OUT_NUM FROM TB1 WHERE TB1.DEPTNO = IN_DNO;
  DBMS_OUTPUT.put_line(IN_DNO || '部门人数为' || OUT_NUM);
end COUNT_DE;

function F_DNUM(IN_DNO IN NUMBER) return number is
R_FUNC number := 0;
begin
SELECT COUNT(TB1.DEPTNO) INTO R_FUNC FROM TB1 WHERE TB1.DEPTNO = IN_DNO; return(R_FUNC);
end F_DNUM;
end P_TB;

在说明和包体的创建中，过程、函数的定义不要使用CREATE,直接写PRODECURE或FUNCTION以及它们后面的部分。
包的调用方式为包名.调用内容,示例：

SELECT EMP.* FROM EMP
 INNER JOIN (SELECT DEPTNO, COUNT(1) AS N FROM TB1 GROUP BY DEPTNO) T
    ON T.DEPTNO = EMP.DEPTNO
 WHERE T.N =P_TB.F_DNUM(20);
 
CALL P_TB.COUNT_DE(30);

包的删除与函数、过程类似，其语法为

DROP PACKAGE <包名>