oracle存储过程基础知识

一、 创建存储过程

一个例子：

create or replace procedure CreateTablesByEnterpriseId(EnterpriseId in string) is

trSql 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 beginTimeend loop;

语法详解：

while 条件语句 LOOP

[begin]

[end;]

end LOOP;

三、 游标的使用

使用目的

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;