明净

  博客园  :: 首页  :: 新随笔  :: 联系 :: 订阅 订阅  :: 管理
oracle 存储过程 包 【转】

 

一、为什么要用存储过程？

如果在应用程序中经常需要执行特定的操作，可以基于这些操作简历一个特定的过程。通过使用过程可以简化客户端程序的开发和维护，而且还能提高客户端程序的运行性能。

二、过程的优点？

1、预编译：存储过程预先编译好放在数据库内，减少编译语句所花的时间。

2、缓存：预编译的存储过程会进入缓存，所以对于经常执行的存储过程，除了第一次执行外，其它次数的执行速度会明显提高。

3、减少网络传输：特别是对于一些处理数据的存储过程，不必像直接使用SQL语句那样多次传送数据到客户端。

4 、可维护性高：更新存储过程通常要比更改、测试和部署应用程序需要的时间和精力要少。

5、代码的重用：一个可以重用的存储过程可以应用到应用程序的多个位置。

6、增强安全性：通过对用户授权对存储过程的访问权限，它们可以提供对特定数据的访问；提高数据安全性，来防止SQL注入。

三、缺点：

1、如果需要对存储过程的输入输出参数做更改的话，还要更改程序。

2、可移植性差：因为存储过程将应用程序的业务处理绑定到数据库中，以此使用存储过程来处理业务逻辑限制了应用程序的可移植性。

四、创建存储过程

 1 --1、简单的存储过程
 2 create or replace procedure procedure_test
 3 (p_id in varchar,p_status out varchar)  --p_id为输入参数 ,p_status为输出参数
 4 as
 5  t_name varchar2(20);
 6  t_count number:=0;
 7 begin
 8  select votetitle,vatesum into t_name,t_count from votemaster where id=p_id; --注意：此处没有：来赋值
 9  if t_count <=0 then
10      p_status:= t_name||':差';
11  elsif t_count >0 and t_count <3 then
12      p_status:= t_name||':良好';
13  else
14      p_status:= t_name||':优秀';
15  end if;
16 end;
17 --执行
18 declare 
19  out_param varchar2(50);
20 begin 
21  procedure_test('1',out_param);
22   dbms_output.put_line(out_param);
23 end;
24 
25 --2、带游标的存储过程
26 create or replace procedure procedure_cursor_test
27 (p_id in varchar2,p_status out varchar2)
28 as
29   vote votemaster%rowtype; --声明一个对象(votemaster)类型的对象
30   cursor my_cur is select * from votemaster; --声明一个游标并填充数据
31 begin
32   open my_cur; --打开游标
33        loop
34           fetch my_cur into vote ; --循环游标，并放入对象
35           exit when my_cur%notfound; --如果没有数据，则直接exit
36           if vote.id=p_id then
37              p_status := vote.votetitle||'：'||vote.vatesum;
38              --如果想终止循环，可以直接exit;
39           end if;
40        end loop;
41   close my_cur; --关闭游标
42 end;
43 --执行
44 declare 
45  out_param varchar2(50);
46 begin 
47   procedure_cursor_test('1',out_param);
48   dbms_output.put_line(out_param);
49 end;

 

 五、程序包

1、程序包：包是一组相关过程、函数、变量、游标、常量等PL/SQL程序设计元素的组合。它具有面向对象程序设计语言的特点，是对这些PL/SQL程序设计元素的封装。包类似于C++或Java程序中的类，而变量相当于类中的成员变量，过程和函数相当于方法，把相关的模块归类成为包，可使开发人员利用面向对象的方法进行存储过程的开发，从而提高系统性能。与类相同，包中的程序元素也分为公用元素和私有元素两种，这两种元素的区别是他们允许访问的程序范围不同，即他们的作用域不同。公用元素不仅可以被包中的函数、过程调用，也可以被包外的PL/SQl块调用。而私有元素只能被该包内部的函数或过程调用。

2、使用程序包的优点：在PL/SQL设计中，使用包不仅可以使程序模块化，对外隐藏包内所使用的信息，而写程序包可以提高程序的运行效率。因为，当程序首次调用程序包内部的函数或过程时，Oracle将整个程序包调入内存，当再次调用程序包中的元素时，Oracle直接从内存中读取，而不需要进行磁盘的IO操作，从而使程序的执行效率提高。

3、一个程序包分为两部分组成：

(1)、包定义：包定义部分声明包内数据类型、变量、常量、游标、子程序和函数等元素，这些元素为包的共有元素。

(2)、包主体：包主题则定义了包定义部分的具体实现，在包主体中还可以声明和实现私有元素。

 1 --包定义
 2 create or replace package t_package
 3 is
 4   --定义过程
 5   procedure append_proc(t varchar2,a out varchar2);
 6   --过程的重载
 7   procedure append_proc(t number,a out varchar2);
 8   --定义函数
 9   function append_fun(t varchar2) return varchar2;
10   
11 end;
 1 --包主题
 2 create or replace package body t_package
 3 is
 4   v_t varchar2(30);
 5   --私有成员函数
 6   function private_fun(t varchar2) return varchar2 is
 7   begin
 8      v_t := t||'hello';
 9      return v_t;
10   end;
11   --实现过程
12   procedure append_proc(t varchar2,a out varchar2) is
13   begin
14    a := t||'hello'; 
15   end;
16   --过程的重载
17   procedure append_proc(t number,a out varchar2) is 
18   begin 
19     a := t||'hello'; 
20   end;
21   --实现函数
22   function append_fun(t varchar2) 
23   return varchar2 is
24   begin
25      v_t := t||'hello';
26      return v_t;
27   end;
28 end;

http://zxf-noimp.iteye.com/blog/1145442

 

 

 

  1. CREATE OR REPLACE PROCEDURE testp(p_value IN VARCHAR2)  
  2.        IS  
  3.     TYPE ref_cursor_type IS REF CURSOR; --定义一个动态游标  
  4.     users ref_cursor_type;   --定义游标类型  
  5.     user Users%ROWTYPE; --定义变量类型  
  6.     vSql VARCHAR2(255);  
  7. BEGIN  
  8.   vSql := 'select * from Users'; --要查询的sql字符串，可拼接起来  
  9.   OPEN users FOR vSql;  --打开游标  
  10.   LOOP  
  11.     FETCH users INTO users; --循环遍历users列表给user，user为临时对象  
  12.     exit when bills%notfound;   
  13.     -- user相当于表对象可以直接拿来用  
  14.     --比如要打印user对象里面的name  
  15.     dbms_outpt.put_line(user.name);  
  16.   end loop;  
  17.   CLOSE bills;  
  18. END;  

 

 

 

 1 CREATE OR REPLACE PROCEDURE PRODUCT_TEMP_UPDATE_PRC AS  --第1行表示创建存储过程，名称为PRODUCT_TEMP_UPDATE_PRC 。
 2   PC_DELESTR   VARCHAR2(50); --删除临时表记录语句            第2~7行表示声明变量。
 3   PC_CREATESTR VARCHAR2(500); --创建临时表
 4   TABEXT       VARCHAR2(10); --用于判断临时表是否存在中间变量
 5 
 6   CUR_CTGY   PRODUCTINFO.CATEGORY%TYPE;
 7   CUR_PRTIFO PRODUCTINFO%ROWTYPE;
 8                                                         --第9~11行表示创建游标cur_category;
 9   CURSOR CUR_CATEGORY --产品表中的  产品类型  游标
10   IS
11     SELECT CATEGORY FROM PRODUCTINFO GROUP BY CATEGORY;
12  --第13~19行表示创建游标CUR_PROINFO；该游标带有参数，其参数代表产品类型的编码。游标根据产品的类型不同，获取产品类型中价格最低的数据。
13   CURSOR CUR_PROINFO(CTGY VARCHAR) IS
14     SELECT *
15       FROM (SELECT *
16               FROM PRODUCTINFO
17              WHERE CATEGORY = CTGY
18              ORDER BY PRODUCTPRICE ASC)
19      WHERE ROWNUM < 2;
20 
21 BEGIN
22   SELECT COUNT(1)--第22~25表示判断临时表productinfo_tmp是否存在。此处利用select into语句把结果放到变量tabext中，如果该表存在结果为1，否则为0.tabext变量将在第37行使用。
23     INTO TABEXT
24     FROM ALL_TABLES
25    WHERE TABLE_NAME = 'productinfo_tmp';
26 
27   PC_DELESTR   := 'delete from productinfo_tmp';
28   PC_CREATESTR := 'create global temporary table productinfo_tmp
29   (productid varchar2(10) not null,
30   productname varchar2 (20),
31   productprice number(8,2),
32   quantity number(10),
33   category varchar2(10),
34   desperaction varchar2(1000),
35   origin varchar2(10))on commit preserve rows';
36 --第37~44行完成分析步骤中的第一步：创建临时表productinfo_tmp。首先判断临时表是否存在，如果不存在，则创建，如果存在则删除表中数据。这里使用了execute immediate语句，利用它执行DDL语句及动态语句。
37   IF TABEXT = 0 THEN
38     --不存在临时表就创建一个
39     EXECUTE IMMEDIATE PC_CREATESTR;
40     DBMS_OUTPUT.PUT_LINE('创建临时表成功！');
41   ELSE
42     EXECUTE IMMEDIATE PC_DELESTR;
43     DBMS_OUTPUT.PUT_LINE('删除记录完成！');
44   END IF;
45   OPEN CUR_CATEGORY;--第45~49行表示打开游标cur_category（产品类型  游标），并进入流循环取值。当游标的%nofound属性为true时退出。
46   LOOP
47     FETCH CUR_CATEGORY
48       INTO CUR_CTGY;
49     EXIT WHEN CUR_CATEGORY%NOTFOUND;
50     OPEN CUR_PROINFO(CUR_CTGY);--第50~53行表示打开游标cur_proinfo，它的参数是cur_category中的结果。
51     FETCH CUR_PROINFO
52       INTO CUR_PRTIFO;
53     IF CUR_PROINFO%FOUND THEN
54       IF CUR_PRTIFO.PRODUCTPRICE < 20 THEN--第54~58行表示判断价格是否低于20，如果低于20输出到屏幕。
55         ---产品价格低于20
56         DBMS_OUTPUT.PUT_LINE('产品ID' || CUR_PRTIFO.PRODUCTID || '产品名称' ||
57                              CUR_PRTIFO.PRODUCTNAME || '产品价格' ||
58                              CUR_PRTIFO.PRODUCTPRICE);
59       ELSE
60         --非低于20价格的产品输入到临时表productinfo_tmp            第60~69行表示如果非低于20的插入表productinfo_tmp中。
61         EXECUTE IMMEDIATE 'insert into productinfo_tmp(
62         productid,productname,productprice,quantity,category,desperaction,origin) values
63         (''' || CUR_PRTIFO.PRODUCTID || ''',''' ||
64                           CUR_PRTIFO.PRODUCTNAME || ''',''' ||
65                           CUR_PRTIFO.PRODUCTPRICE || ''',''' ||
66                           CUR_PRTIFO.QUANTITY || ''',''' ||
67                           CUR_PRTIFO.CATEGORY || ''',''' ||
68                           CUR_PRTIFO.DESPERACTION || ''',''' ||
69                           CUR_PRTIFO.ORIGIN || ''')';
70       END IF;
71     END IF;
72     CLOSE CUR_PROINFO;
73   END LOOP;
74   COMMIT;
75   CLOSE CUR_CATEGORY;
76   EXECUTE IMMEDIATE 'update productinfo_tmp set desperaction = ''热销产品''';--第76行表示将productinfo_tmp表中的数据修改为热销产品。
77 END;

【执行】

SQL>exec PRODUCT_TEMP_UPDATE_PRC ;

转自:http://www.cnblogs.com/Rainbow-G/articles/4301131.html

 

posted on 2016-12-08 16:54  明净  阅读(2253)  评论(0)  编辑  收藏  举报