Oracle数据库--实用操作(5)过程，函数，程序包

过程（存储过程）

         参数（IN,OUT,INOUT）

         没有返回值。

         封装多个SQL操作

         独立（直接）运行

         过程--可修改数据

         过程相当于---命名了的，且保存编译好的PL/SQL代码块。

         过程是用于完成特定任务的子程序。

子程序：是命名的 PL/SQL 块，编译并存储在数据库中。

子程序的各个部分：

1.       声明部分

2.       可执行部分

3.       异常处理部分(可选)

子程序的分类：

1.       过程 － 执行某些操作

2.       函数 － 执行操作并返回值

子程序的优点：模块化：将程序分解为逻辑模块

可重用性：可以被任意数目的程序调用

可维护性：简化维护操作

安全性：通过设置权限，使数据更安全

PL/SQL代码块：也可以是匿名的；即匿名块：不指定函数名或过程名。

DECLARE vv number:=100;   --匿名代码块，不会像过程那样保存起来。

Begin

 SELECT SAL into vv  from emp whereename='KING';--查出KING的工资，并赋值给vv变量

 

  DBMS_OUTPUT.PUT_LINE('SAL'||vv);

end;

创建  存储过程

创建过程的语法：

CREATE[OR REPLACE] PROCEDURE

   <procedure name> [(<parameterlist>)]

IS|AS

   <local variable declaration>

BEGIN

   <executable statements>

[EXCEPTION

   <exception handlers>]

END;

用show errors procedure procedure_name可以查看到存储过程具体错误

用show errors function function_name查看函数错误

用select text from user_source where name =‘过程名’;来查看代码

SELECT DISTINCT NAME FROM user_source WHERE TYPE='PROCEDURE'

创建  存储过程 例子。http://blog.youkuaiyun.com/ljheee/article/details/51125171

过程参数的三种模式：

      IN

1.       用于接受调用程序的值

2.       默认的参数模式

      OUT

1.       用于向调用程序返回值

      IN OUT

1.       用于接受调用程序的值，并向调用程序返回更新的值

执行过程的语法

CREATE OR REPLACE PROCEDURE   --声明过程

 itemdesc(item_code IN VARCHAR2) –且带参数

IS

 v_itemdesc VARCHAR2(5);   --声明局部变量

BEGIN

 SELECT itemdesc INTO v_itemdesc

 FROM itemfile

 WHERE itemcode = item_code;

 DBMS_OUTPUT.PUT_LINE(item_code||    --控制台输出

         '项目的说明为'||v_itemdesc);

END;

-------------------------------------------------

SET SERVEROUTPUT ON

EXECUTE itemdesc('i201');   --调用执行过程，给定实参；此处在SQL语句中

注意：在PL/SQL语句块中，不能EXECUTE关键字来调用过程，直接用名字调用。

删除过程：

DROP PROCEDURE find_emp;

 

 

函数：

         分类：     系统定义的

                            自定义函数[把常用的计算式，定义成函数，可在多处调用]

                   是可以返回值的命名的 PL/SQL子程序。

                   函数有return,返回单一的值。可有参数。不能修改更新[update]数据。

                   函数写好，需要编译[保存]，才可调用。在SQL语句中直接调用。

createor replace FUNCTION FUNCTION_REVENUE --若已有函数，则替代。

(

  SAL IN NUMBER,     --声明的参数变量

  n number

)RETURN NUMBER AS

 temp number;  --函数 局部变量

BEGIN

  if sal> n then

  temp:= SAL*18;    /*赋值  ：=*/

  else

  temp:= SAL*12;

  End if;

  RETURN temp;     --返回值

ENDFUNCTION_REVENUE;

定义函数的限制：

q  函数只能接受 IN 参数，而不能接受 INOUT 或OUT 参数

q  形参不能是 PL/SQL 类型

q  函数的返回类型也必须是数据库类型

访问函数的两种方式：

q  使用 PL/SQL 块

q  使用 SQL 语句，就像调用系统函数一样。

程序包

      程序包是对相关过程、函数、变量、游标和异常等对象的封装

      程序包由规范和主体两部分组成

      程序包：把写好的函数，存储过程[小程序段]，打包到一起。

      包内可以声明过程、函数。

                                                                   程序包 

声明程序包中

公共对象。包括类型、

变量、常量、异常、                       <------规范

游标规范和子程序规范等                        

                                                                     主体--------->声明程序包私有对象和实现在包规范中声明的子程序和游标

先创建规范[相当于C++里的头文件],再实现主题部分。

CREATE OR REPLACE PACKAGE BODY pack_me AS   --声明程序包pack_me主体部分

 PROCEDURE order_proc (orno VARCHAR2) IS

   stat CHAR(1);                              --SA后面，声明局部变量

 BEGIN

   SELECT ostatus INTO stat FROM order_master

    WHEREorderno = orno;

   ……

  ENDorder_proc;

 FUNCTION order_fun(ornos VARCHAR2)  --子函数：具体实现

 RETURN VARCHAR2

  IS

   icode   VARCHAR2(5);

   ocode   VARCHAR2(5);

 BEGIN

  ……

  ENDorder_fun;

END pack_me;

例---

1.包的规范   --相当于C++的头文件，里面定义的过程和函数，没有具体实现

CREATE OR REPLACE

PACKAGE PACKAGE_LJEEE AS

 

  /*TODO enter package declarations (types, exceptions, methods etc) here */

  --定义函数

 FUNCTION f1(str VARCHAR2) return varchar2 ;

 

  --定义过程

 PROCEDURE swap(a in out number,b in out number);

 

END PACKAGE_LJEEE;

2.包的主体

CREATE OR REPLACE

PACKAGE BODY PACKAGE_LJEEE AS

 

 FUNCTION f1(str VARCHAR2) return varchar2  AS

 BEGIN

 

   RETURN 'hello'||str;

  ENDf1;

 

 PROCEDURE swap(a in out number,b in out number) AS

 temp number;

 BEGIN

   temp := a;

   a:=b;

   b:=temp;

   NULL;

  ENDswap;

END PACKAGE_LJEEE;

程序包优点

      模块化

      更轻松的应用程序设计

      信息隐藏

      新增功能(过程可以重载，可以定义公用变量或游标)

      性能更佳

总结

      子程序是命名的PL/SQL 块，可带参数并可在需要时随时调用

      有两种类型的PL/SQL子程序，即过程和函数

      过程用户执行特定的任务，函数用于执行任务并返回值

      程序包是对相关类型、变量、常量、游标、异常、过程和函数等对象的封装

      程序包由两部分组成，即包规范和包主体

      使用程序包的优点是：模块化、更轻松的程序设计、信息隐藏、新增功能以及性能更佳