oracle 存储过程基础知识

oracle 存储过程基础知识

参考：
oracle存储过程常用技巧1:
(http://www.cnblogs.com/chinafine/archive/2010/07/12/1776102.html)
oracle存储过程常用技巧2:
(http://www.cnblogs.com/yw0219/p/5939558.html)

存储过程 PROCEDURE

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。

优点

1. 重复使用。可重复使用，减少数据库开发人员的工作量。
2. 减少网络流量。存储过程位于服务器上，调用的时候只需要传递存储过程的名称以及参数就可以了，因此降低了网络传输的数据量。在服务器上运行，减少对客户机的压力。
3. 安全性高。参数化的存储过程可以防止SQL注入式攻击，而且可以将Grant、Deny以及Revoke权限应用于存储过程。可设定只有某些用户才具有对指定存储过程的使用权。
4. 速度快。存储过程因为SQL语句已经预编绎过了，因此运行的速度比较快。
5. 简单化。当对数据库进行复杂操作时，可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。

缺点

1. 调试麻烦，但是用 PL/SQL Developer 调试很方便！弥补这个缺点。
2. 移植问题，数据库端代码当然是与数据库相关的。但是如果是做工程型项目，基本不存在移植问题。
3. 重新编译问题，因为后端代码是运行前编译的，如果带有引用关系的对象发生改变时，受影响的存储过程、包将需要重新编译（不过也可以设置成运行时刻自动编译）。
4. 当系统大量的使用存储过程，到程序交付使用的时候随着用户需求的增加会导致数据结构的变化，接着就是系统的相关问题了，最后如果用户想维护该系统可以说是很难很难、而且代价是空前的，维护起来更麻烦。

存储过程创建

A. 无参存储过程语法，例如：

CREATE OR REPLACE PROCEDURE 存储过程名() IS
  '变量声明'
  begin
      '执行体'
  end 存储过程名;

B. 带输入或输出参存储过程语法，例如：

存储过程的参数传递有三种方式: IN, OUT, IN OUT .
IN 按值传递，并且它不允许在存储过程中被重新赋值。如果存储过程的参数没有指定存参数传递类型，默认为IN

CREATE OR REPLACE PROCEDURE 存储过程名(param1 in number,..., param2 out varchar2,...) IS
  '变量声明'
  begin
      '执行体'
  end 存储过程名;

存储过程调用

调用方式，例如：

 exec 存储过程名();

变量声明及赋值

变量类型，例如：

1、字符串类型：char、nchar、varchar2、nvarchar2。
2、数值类型：int、number(p,s)、integer、smallint。
3、日期类型：date、interval、timestamp。
4、PL/SQL类型：pls_integer、binary_integer、binary_double(10g)、binary_float(10g)、boolean。plsql类型是不能在sql环境中使用的，比如建表时。
5、自定义类型：type / create type。

变量声明，例如：

 ... 变量声明'''
变量名 变量类型
v_param3 varchar2(100);
v_param4 number ;
v_param5 number(18) := 0; 

变量赋值，例如：

 ... 变量赋值1'''
 变量名 := 值;
 v_param4 := 1;
 v_param4 := param4 + 1;

 ... 变量赋值2'''
 select count(1) into v_param4 from table;

自定义类型type / create type
详情：http://www.cnblogs.com/advocate/p/3729998.html

type自定义类型：

type语法1：type 变量 is table of 类型 
type numbers is table of number index by binary_integer;

type语法2：
 type 变量 is record(
    字段1 类型1,
    字段2 类型2
 );
  Declare
     Type objtype is Record(
        param6 number(4),
        param7 varchar2(10)
     );
   obj1 objtype ; 
 Begin
   obj1.param6:=1;
   ...
   ...
注意：
1、直接用 type 后面用 is
2、type 是创 record 
3、type用在语句块中

create type自定义类型：

 create type语法1：create type 变量 as table of 类型 
 create or replace type objtype as table of varchar2(4000);  

create type语法2：
create type 变量 as object(
    字段1 类型1,
    字段2 类型2
);
1 首先创建一个数据类型
create type objtype as object(
      param8 int ,
      param9 varchar(20)
);
2 创建表
create table aaa(id int ,paramobj objtype );  
3 插入数据
insert into aaa values(1,objtype(1,'23sdf'));
4 查询classPlace
select a.id ,a.objtype.param8 ,a.objtype.param9 from aaa a;
objtype(1,'23sdf') 使用这个方式创建一个自定义类型objtype的对象.
   ...
   ...
注意：
1、用 create 后面用 as
2、create 是创 object
3、create 是的独立的.

判断语法

语法：if 比较式 then begin end; end if;
例如：

if v_param4  = 0 then 
      '处理事务'
end if; 

if v_param4 is not null and/or v_param4<>0 then 
      '处理事务'
else
      '处理事务'
end if; 

if v_param4 = 0 then 
      '处理事务'
elsif v_param4 = 1  then
      '处理事务'
else
      '处理事务'
end if; 

循环语法

For 循环语句

For ... in ... LOOP
    --执行语句
end LOOP;

例如：

 for v_param3 in (
      select * from table
 ) loop
    --执行语句
     v_param4 :=  v_param3. field; 
 end LOOP;

循环遍历游标 , 例如：

create or replace procedure test() as
    Cursor cursor is select name from student; 
    name varchar(20);
begin
   for name in cursor LOOP
     begin
       dbms_output.putline(name); 
     end;
   end LOOP;
end test;

循环遍历数组 , 例如：

（输入参数varArray 是自定义的数组类型）
create or replace procedure test(varArray in myPackage.TestArray) as
    i number;
begin
    i := 1; 
    for i in 1..varArray.count LOOP     
        dbms_output.putline(i || varArray(i));  
    end LOOP;
end test;

注意: --存储过程数组是起始位置是从1开始的，与java、C、C++等语言不同。因为在Oracle中本是没有数组的概念的，数组其实就是一张
--表(Table),每个数组元素就是表中的一个记录，所以遍历数组时就相当于从表中的第一条记录开始遍历

While 循环

while循环语法：

while 条件语句 LOOP
--begin
--end;
end LOOP;

例如：

create or replace procedure test(i in number) as
begin
     while i < 10 LOOP
     begin   
           i:= i + 1;
     end;
     end LOOP;
end test;

EXCEPTION异常

代码块语法遵循标准markdown代码，例如：

 EXCEPTION
    WHEN TOO_MANY_ROWS THEN
     -- dbms_output.put_line('符合条件的记录有多条返回');
    WHEN NO_DATA_FOUND THEN
    -- dbms_output.put_line('没有符合条件的记录返回'); 
    WHEN OTHERS THEN        
     -- dbms_output.put_line('其他异常');          
END;
其他： 
详情说明：
----------
http://www.cnblogs.com/songdavid/articles/2153482.html 
----------
dup_val_on_index　 ora-00001　 -1　 对于数据库表中的某一列，该列已经被限制为唯一索引，程序试图存储两个重复的值
value_error　 ora-06502　 -6502　 在转换字符类型，截取或长度受限时，会发生该异常，如一个字符分配给一个变量，而该变量声明的长度比该字符短，就会引发该异常
storage_error　 ora-06500　 -6500　 内存溢出
zero_divide　 ora-01476　 -1476　 除数为零
case_not_found　 ora-06592　 -6530　 对于选择case语句，没有与之相匹配的条件，同时，也没有else语句捕获其他的条件
cursor_already_open　 ora-06511　 -6511　 程序试图打开一个已经打开的游标
timeout_on_resource　 ora-00051　 -51　 系统在等待某一资源，时间超时

分页

查询某表进行分页处理

CREATE OR REPLACE PROCEDURE 存储过程名() IS
   page   number := 1;--数据页数，从1开始
   pageSize   number := 1000; --每页大小 
   numCount  number;--总记录数
   pageCount number;--该条件下记录页数
   startIndex number;--开始记录
   endIndex number;--结束记录
begin
    select count(1) into numCount from tables;   
    --计算数据记录开始和结束
    pageCount:=Ceil(numCount/pageSize);  
    -- 分页
    WHILE( page <= pageCount)
    LOOP
         startIndex:=(page-1)*pageSize+1;
         endIndex:=page*pageSize; 
         for i in (
           select * from (select rownum ro, t.*
                   from  tables where 
                                rownum<=endIndex   
                                order by field) 
                        where  ro >=startIndex 
          ) loop
          begin 
           [执行体]
          end loop;
        -- 分页 页数加1
        page := page + 1;
    END LOOP; 
END 存储过程名;

目录

用 [TOC]来生成目录：

---