oracle存储过程基础学习

 

一、创建存储过程

一个例子：

create or replace procedure CreateTablesByEnterpriseId(EnterpriseId in string) is

 

strSql    varchar2(4800) := '';

exitObj     int := 0;                                               

v_Job    NUMBER :=0;                                                   

JobNum NUMBER(5) := 0;                                         

JobID  NUMBER(5) := 0;

begin

……（省略）

end;

end   CreateTablesByEnterpriseId;

 

语法详解：

create or replace procedure 存储过程名（参数1  in 类型，参数2  out 类型）

--create表示创建，replace表示如果存在则替换 。对于参数，是没有取值范围的，但是一定要标明是in还是out或者in out型

as 

--as和is在存储过程中是没有区别的！可以放心使用

变量1 类型（值范围）:=xxx; --比如strSql    varchar2(4800) := ''; 

--其中:=就是赋值

变量2 类型（值范围）;

Begin  –注意没有分号！（;）

Select count(*) into 变量1 from 表A where列名= 参数1；

--用select。。。into为变量赋值，在判断语句前最好先用count（*）函数判断是否存在该条操作记录

    If (判断条件) then

       Select 列名 into 变量2 from 表A where列名= 参数2；

       Dbms_output。Put_line(‘打印信息’);

    Elsif (判断条件) then

       Dbms_output。Put_line(‘打印信息’);

    Else

       Raise 异常名（NO_DATA_FOUND）;

--在代码中抛异常用 raise+异常名

    End if;

Exception

    When others then

       Rollback;

End;

end 存储过程名;

 

二、基本语法

1、判断语句if

例子：

select count(*) into exitObj from user_objects where object_name = UPPER('Index_BeginTime'|| EnterpriseId);

if   exitObj > 0   then

    begin

        strSql :=  'drop index Index_BeginTime_'|| EnterpriseId ;

         execute   immediate   strSql;

    end;

  end if;

 

语法详解：

if 比较式 then begin end; end if; 

该例中，利用变量得到在user_objects表中是否有名字为Index_BeginTime_xx(xx为EnterpriseId)的记录，有则删除索引。

 

2、 循环语句for

例子（遍历游标，关于游标后面介绍）

Cursor cursor is select name from student;

name varchar(20); 

begin 

for name in cursor LOOP 

begin 

 dbms_output.putline(name);  

end; 

end LOOP; 

 语法详解：

For ... in ... LOOP 

-- 执行语句 

end LOOP; 

 

3、循环语句while

例子

while beginTime<endCaclTime   loop

……（省略）

end loop;

语法详解：

while 条件语句 LOOP 

[begin] 

[end;]

end LOOP; 

三、游标的使用

使用目的

PL/SQL中SELECT语句只返回一行数据。如果超过一行数据，那么就要使用显式游标，INTO子句中要有与SELECT子句中相同列数量的变量。INTO子句中也可以是记录变量。

定义

游标是映射在结果集中一行数据上的位置实体，有了游标，用户就可以访问结果集中的任意一行数据了，将游标放置到某行后，即可对该行数据进行操作，例如提取当前行的数据等。 简单的说就是一个可以遍历的结果集。

普通显式游标（静态游标）

显式游标的使用需要4步：

1. 声明游标，可以有参数也可以没有

CURSOR mycur(vartype number) is

select emp_no,emp_zc from cus_emp_basic

where com_no = vartype;

2. 打开游标

open mycur(000627)

3. 读取数据

fetch mycur into varno, varprice;

4. 关闭游标

close mycur;

静态游标（隐式）：

cusor_1 Cursor is select std_name from student where  ...;  --Cursor 的使用方式1

select class_name into cursor_2 from class where ...;  --Cursor 的使用方式2

For x in cursor LOOP .... end LOOP;--使用该语句来实现对Cursor 的遍历

 

 

REF游标（动态游标）

有一种游标是REF游标。REF游标就是动态关联结果集的临时对象。即在运行的时候动态决定执行查询，他的主要作用就是实现在程序间传递结果集的功能
①声明REF游标
  ⑴强类型REF游标：指定retrun type，REF 游标变量的类型必须和return type一致。
   语法：Type   REF游标名   IS   ref cursor Return  结果集返回记录类型；
  ⑵弱类型REF游标：不指定return type，能和任何类型的CURSOR变量匹配，用于获取任何结果集。
   语法：Type   REF游标名   IS   ref cursor ；
 ②声明Ref 游标类型变量；
  语法：变量名  已声明Ref 游标类型；
 ③打开REF游标，关联结果集 ；
  语法：Open   Ref 游标类型变量   For   查询语句返回结果集；
 ④获取记录，操作记录；
  语法：fetch REF游标名 InTo   临时记录类型变量或属性类型变量列表；
 ⑤关闭游标，完全释放资源；
  语法：Close   REF游标名；

REF游标使用例子：

DECLARE

TYPE CURSOR_TEMP IS REF CURSOR;

v_cursor         CURSOR_TEMP;

--声明一个ref游标变量

OPEN v_cursor FOR 'SELECT DISTINCT(RESULTTABLENAME)  FROM  表名  where 条件;

LOOP

    FETCH v_cursor INTO v_tbl_name;

EXIT WHEN v_cursor%NOTFOUND;

……（省略）

END LOOP;

静态与动态游标的区别：

 

①     静态游标是静态定义，REF 游标是动态关联；

②     使用REF 游标需REF 游标变量。

③     REF 游标能做为参数进行传递，而静态游标是不可能的。

 

四、数组

概念

Oracle 中本是没有数组的概念的，数组其实就是一张表(Table), 每个数组元素就是表中的一个记录。 使用数组时，用户可以使用Oracle 已经定义好的数组类型，或可根据自己的需要定义数组类型。

(1)    Oracle 自带的数组类型

声明：

x array; -- 使用时需要进行初始化

例子：

create or replace procedure test(y out array) is 

 x array;   

 begin 

x := new array(); 

y := x; 

end test; 

(2)    自定义的数组类型

自定义数据类型时，建议通过创建Package 的方式实现，以便于管理)

例子：

create or replace package myPackage is 

Public type declarations   type info is record( 

name varchar(20), y number); 

  type TestArray is table of info index by binary_integer;   

-- 此处声明了一个TestArray 的类型数据，其实其为一张存储Info 数据类型的Table 而已，及TestArray 就是一张表，有两个字段，一个是name ，一个是y 。需要注意的是此处使用了Index by binary_integer 编制该Table 的索引项，也可以不写，直接写成：type TestArray is

table of info ，如果不写的话使用数组时就需要进行初始化：varArray myPackage.TestArray; varArray := new myPackage.TestArray();

end TestArray;

 

用for循环遍历该数组例子：

create or replace procedure test(varArray in myPackage.TestArray) as 

i number; 

begin 

i := 1; 

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

for i in 1..varArray.count LOOP      

dbms_output.putline('The No.'|| i || 'record in varArray is:'||varArray(i));    

end LOOP; 

end test;