Oracle存储过程基本语法（一）

最新推荐文章于 2023-05-16 11:36:06 发布

尘埃的故事

最新推荐文章于 2023-05-16 11:36:06 发布

阅读量674

点赞数

分类专栏： oracle 文章标签： oracle 存储过程

oracle 专栏收录该内容

3 篇文章

订阅专栏

概念：存储过程

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

在大型数据库系统中，存储过程和触发器具有很重要的作用。无论是存储过程还是触发器，都是SQL 语句和流程控制语句的集合。

存储过程和存储函数

存储在数据库中供所有用户程序调用的子程序叫做存储过程,存储函数.
区别:是否可以通过return返回函数值.存储函数可以通过return返回函数值,而存储过程不可以.
由于通过out参数,存储过程也可以返回函数值.所以存储过程和存储函数已经没有太大的区别了.而存储函数仍然存在,是由于oracle不断升级,需要实现向下兼容,所以存储函数就一直存留着.

存储过程基本语法：

1、创建语法：

create or replace procedure 存储过程名（param1 in type,param2 out type） 
as 
变量1 类型（值范围）; --例vs_msg   VARCHAR2(4000); 
变量2 类型（值范围）;
Begin
If (判断条件) then
...
    Else
       Raise 异常名（NO_DATA_FOUND）;
    End if;
Exception
    When others then
       Rollback;
End;

说明：

CREATE OR REPLACE PROCEDURE 是一个SQL语句通知Oracle数据库去创建一个叫做skeleton存储过程, 如果存在就覆盖它;
IS|AS关键词表明后面将跟随一个PL/SQL体。:BEGIN关键词表明
PL/SQL体的开始。
PL/SQL体中至少需要有一句;
END关键词表明PL/SQL体的结束

注意事项：

存储过程参数不带取值范围，in表示传入，out表示输出类型可以使用任意Oracle中的合法类型。
入参IN 按值传递，并且它不允许在存储过程中被重新赋值
变量带取值范围，后面接分号
在判断语句前最好先用count（*）函数判断是否存在该条操作记录
在代码中抛异常用 raise 异常名

2、判断语句 if

if 比较式 then begin end; end if;

create or replace procedure test(x in number)  
is  
begin 
        if x >0 then 
         begin 
         dbms_output.putline(1); 
         end; 
         end if; 
        if x = 0 then 
         begin 
         dbms_output.putline(0); 
         end; 
         end if; 
    end test;

3、循环 for while

For循环

For … in … LOOP
– 执行语句
end LOOP;

(1) 循环遍历游标
例子：

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;

(2) 循环遍历数组
例子：

 create or replace procedure test(varArray in myPackage.TestArray) as 
--( 输入参数varArray 是自定义的数组类型，定义方式见下) 
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;

While 循环

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

例子：

create or replace procedure test 
As
i number; 
begin 
i := 0;
while i < 10 LOOP 
begin     
 i:= i + 1; 
end; 
end LOOP; 
end test;

4、数组

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

(1)在PL/SQL中，数组数据类型是VARRAY（variable array，即可变数组）
定义VARRAY数据类型的语法如下：

TYPE VARRAY_NAME IS VARRAY(SIZE) OF ELEMENT_TYPE [NOT NULL];

例子：

DECLARE   
      TYPE array IS VARRAY(5) OF VARCHAR2(25);  
      V_ORG_VARRAY ORG_VARRAY_TYPE;  
    BEGIN  
      V_ORG_VARRAY := ORG_VARRAY_TYPE('1','2','3','4','5');  
      DBMS_OUTPUT.PUT_LINE('输出1：' || V_ORG_VARRAY(1) || '、'|| V_ORG_VARRAY(2) || '、'|| V_ORG_VARRAY(3) || '、'|| V_ORG_VARRAY(4));  
      DBMS_OUTPUT.PUT_LINE('输出2：' || V_ORG_VARRAY(5));  
      V_ORG_VARRAY(5) := '5001';  
      DBMS_OUTPUT.PUT_LINE('输出3：' || V_ORG_VARRAY(5));  
END;

Declare 是定义变量，在存储过程外pl/sql块中使用。

(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;

5、游标的使用

Oracle 中Cursor 是非常有用的，用于遍历临时表中的查询结果。其相关方法和属性也很多，现仅就常用的用法做一二介绍：

(1)Cursor 型游标( 不能用于参数传递)
create or replace procedure test() is
cusor_1 Cursor is select std_name from student where …; –Cursor 的使用方式1 cursor_2 Cursor;
begin
select class_name into cursor_2 from class where …; –Cursor 的使用方式2
可使用For x in cursor LOOP …. end LOOP; 来实现对Cursor 的遍历
end test;

(2)SYS_REFCURSOR 型游标，该游标是Oracle 以预先定义的游标，可作出参数进行传递
create or replace procedure test(rsCursor out SYS_REFCURSOR) is
cursor SYS_REFCURSOR;
name varhcar(20);
begin
OPEN cursor FOR select name from student where … –SYS_REFCURSOR 只能通过OPEN 方法来打开和赋值
LOOP
fetch cursor into name –SYS_REFCURSOR 只能通过fetch into 来打开和遍历 exit when cursor%NOTFOUND; –SYS_REFCURSOR 中可使用三个状态属性： —%NOTFOUND( 未找到记录信息) %FOUND( 找到记录信息) —%ROWCOUNT( 然后当前游标所指向的行位置)
dbms_output.putline(name);
end LOOP;
rsCursor := cursor;
end test;

补充：
1、基本转载于：
http://www.360doc.com/content/13/0724/16/1073512_302231407.shtml
2、相关学习：
http://blog.youkuaiyun.com/xiaokui_wingfly/article/details/45953633