Oracle存储过程语法代码示例详解

目录

• 简介
• 总体结构
• 样例准备
• 过程声明部分
• 声明格式
• 参数定义
• 变量声明
• 游标声明
• 行数据类型
• 过程执行部分
• 变量赋值
• 条件判断
• WHILE循环
• 游标使用
• 异常处理部分
• 异常捕捉
• 异常处理

简介

存储过程是一系列SQL语句的集合，可以封装复杂的逻辑，实现特定的功能，可以提高执行速度和代码的复用性，预先编译后存储在数据库中，可以通过指定存储过程的名称对其进行调用。

本文主要讲解oracle存储过程语法，包括：总体结构、存储过程声明、参数定义、变量声明、游标声明、行数据类型声明、变量赋值、条件判断、WHILE循环、FOR循环、游标使用、异常捕捉、异常处理、存储过程调用、存储过程代码示例、调用代码示例、调用结果展示。

总体结构

一个完整的存储过程结构主要包括：过程声明部分、过程执行部分、异常处理部分，如下所示。

CREATE OR javascriptREPLACE PROCEDURE 存储过程名称(参数定义部分)

AS或IS

变量声明部分

BEGIN

  过程执行部分

EXCEPTION

  异常处理部分

END;

样例准备

先准备样例表TESTTABLE01，数据如下图，后续所有代码示例查询的数据都来自这个表。

过程声明部分

声明格式

CREATE OR REPLACE PROCEDURE 存储过程名称(参数定义部分)

AS或IS

其中，【OR REPLACE】表示如果已存在同名的存储过程，则直接替换，即将其覆盖掉，这部分也可以省略，省略后，执行编译时，如果发现同名的存储过程，则会报错提示ORA-00955，如下图。AS或IS两种写法效果相同，任选其一。

参数定义

格式：【参数名 输入输出类型 数据类型】，可定义输入或输出的参数，可以不带参数，也可以定义一个或多个参数，多个参数用英文逗号隔开，如下所示。

过程声明和参数定义示例

CREATE OR REPLACE PROCEDURE TESTSP01(
para01 in VARCHAR2,
para02 in INT,
para03 out VARCHAR2
)
AS

para01、para02、para03为自定义的参数名；in或out表示输入或输出参数，输入参数是在调用存储过程时传入的，输出参数是在存储过程内部赋值的，可以输出；VARCHAR2、INT表示参数的数据类型。

说明：【in out】表示该参数既是输入参数也是输出参数。

变量声明

格式：【变量名 数据类型】，变量声明是在BEGIN前面。

可以定义存储过程中需要用到的变量，每个变量用英文分号结尾，如下所示。

var01 VARCHAR2(10);
var02 VARCHAR2(20);
var03 INT;
var04 NUMBER(5);
var05 VARCHAR2(50);

游标声明

格式：【CURSOR  游标名称 IS 查询语句】，游标是内存中用于存储和检索查询结果集的一种数据结构，可以用来遍历的有多条数据的查询结果。

示例：

CURSOR cursor01 IS
SELECT COL01,COL02,COL03
FROM TESTTABLE01
WHERE COL02 IS NOT NULL;

行数据类型

声明游标之后，需要再声明一个行数据类型，用以存储游标中的某一行数据，也可以存储表中的一行数据。

格式：

行变量名称 游标名称%ROWTYPE;

行变量名称 表名称%ROWTYPE;

示例：

row01 cursor01%ROWTYPE;
row02 TESTTABLE01%ROWTYPE;

过程执行部分

变量赋值

格式：【变量名:=变量值】，如下所示，可以将常量、变量等赋值给变量。

  var01:='hello';
  var02:=var01;
  var03:=8;
  var04:=1.8;
  var05:=var02||' world';

也可以在变量声明的时候直接赋值，如下所示。

var03 INT:=8;
var04 NUMBER(5,2):=1.8;
var05 VARCHAR2(50):='hello world';

还可以把SQL语句的查询结果赋值给变量，格式：【select 字段名或表达式 into 变量名 from ...】，如下所示。

  select count(1) into var03 from TESTTABLE01;
  select col02 into var01 from TESTTABLE01 where col01='id01';
  select replace(col02,'value','hello')||' world' into var05 from TESTTABLE01 where col01='id01';

条件判断

格式如下：若条件判断表达式01为真，则执行代码块01，当不满足表达式01时，继续判断，若条件判断表达式02为真，则执行代码块02，若前面的条件都不满足，则执行代码块03。其中ELSIF（注意写法不是ELSEIF）和ELSE部分都可以省略。

IF 条件判断表达式01 THEN

        代码块01

ELSIF 条件判断表达式02 THEN

        代码块02

ELSE

       代码块03

END IF;

示例：

  IF var01 like 'value%' THEN
    var02:='ret01';
  ELSIF var03>1 THEN
    var02:='ret02';
  ELSE
    var02:='ret03';
  END IF;

WHILE循环

第一种写法如下：如果条件表达式为真，再执行里面的代码块，否则不执行。

WHILE  条件表达式  LOOP

        代码块

END  LOOP ;

第二种写法如下：先执行一次LOOP中的代码块，再判断条件表达式，如果为真，则退出循环，否则，继续执行循环，这种写法与第一种写法的区别在于，不论条件表达式结果如何，LOOP中的代码块会至少执行一次。

LOOP

        代码块

        EXIT WHEN 条件表达式

END LOOP;

FOR循环

格式：每一次执行循环时，会将索引自增一次，从索引范围的最小值开始自增，伴随着每一次循环，直到索引达到索引范围的最大值，就退出循环。

FOR 索引 IN 索引范围 LOOP

        代码块；

END LOOP;

示例：

  -- for循环依次输出1到8
  FOR i IN 1..8 LOOP
     var08:=var08||i;
  END LOOP;
  dbms_output.put_line('for循环输出1到8:'||var08);

游标使用

打开游标：【OPEN 游标名称】；

获取游标数据放入行变量：【FETCH 游标名称 INTO 行变量名称】，其中，FETCH在获取当前行数据的同时，还会把游标指针推进到下一条纪录，一般放在循环结构中遍历获取每一条数据。

游标的属性：

【游标名称%ISOPEN】：表示游标是否打开，正常情况返回布尔型；

【游标名称%FOUND】：表示游标是否获取到数据，正常情况返回布尔型；

【游标名称%NOTFOUND】：表示游标是否没有获取到数据，正常情况返回布尔型；

【游标名称%ROWCOUNT】：返回游标已经遍历获取的记录数，不是总数量，返回INT型。

示例：

  var061:=cursor01%ISOPEN; -- 游标cursor01是否打开
  var062:=cursor01%FOUND; -- 游标cursor01是否获取到数据
  var063:=cursor01%NOTFOUND; -- 游标cursor01是否没有获取到数据
  var064:=cursor01%ROWCOUNT; -- 返回游标cursor01已经遍历获取的记录数，不是总数量

关闭游标：【CLOSE 游标名称】。

示例1，使用while...loop循环方式读取游标数据：

  -- while...loop循环方式
  dbms_output.put_line('while...loop循环方式:');
  OPEN cursor01; -- 打开游标cursor01
  var061:=cursor01%ISOPEN; -- 游标是否打开
  var062:=cursor01%FOUND; -- 游标是否获取到数据
  var063:=cursor01%NOTFOUND; -- 游标是否没有获取到数据
  FETCH cursor01 INTO row01; -- 从游标cursor01获取行数据赋给行变量row01，并将游标推进到下一行。
  WHILE cursor01%FOUND LOOP --如果cursor01%FOUND结果为真，则执行while循环
    BEGIN
      var_col01 := row01.COL01; -- 获取行变量中的具体字段值赋给左边的变量
      var_col02 := row01.COL02; -- 获取行变量中的具体字段值赋给左边的变量
      dbms_output.put_line('获取记录数:'||cursor01%ROWCOUNT); -- 输出内容
      dbms_output.put_line('当前行数据:'||var_col01||','||var_col02); -- 输出内容
      FETCH cursor01 INTO row01; -- 从游标cursor01获取行数据赋给行变量row01，并将游标推进到下一行。
    END; 
  END LOOP;
  CLOSE cursor01; -- 关闭游标cursor01

示例2，使用loop... exit when...循环方式读取游标数据：

  -- loop... exit when...循环方式
  dbms_output.put_line('loop... exit when...循环方式:');
  OPEN cursor01; -- 打开游标cursor01
  LOOP
     FETCH cursor01 INTO row01; -- 从游标cursor01获取行数据赋给行变量row01，并将游标推进到下一行。
     EXIT WHEN cursor01%NOTFOUND; -- 如果cursor01%NOTFOUND结果为真，则退出循环。
     var_col01 := row01.COL01; -- 获取行变量中的具体字段值赋给左边的变量
     var_col02 := row01.COL02; -- 获取行变量中的具体字段值赋给左边的变量
     dbms_output.put_line('获取记录数:'||cursor01%ROWCOUNT); -- 输出内容
     dbms_output.put_line('当前行数据:'||var_col01||','||var_col02); -- 输出内容
  END LOOP ;
  CLOSE cursor01; -- 关闭游标cursor01

示例3，使用for循环方式读取游标数据：

  -- for循环读取游标数据
  dbms_output.put_line('for循环读取游标数据:');
  FOR row01 IN cursor01 LOOP -- 遍历游标cursor01获取数据赋给行变量row01
     var_col01 := row01.COL01; -- 获取行变量中的具体字段值赋给左边的变量
     var_col02 := row01.COL02; -- 获取行变量中的具体字段值赋给左边的变量
     dbms_output.put_line('获取记录数:'||cursor01%ROWCOUNT); -- 输出内容
     dbms_output.put_line('当前行数据:'||var_col01||','||var_col02); -- 输出内容
  END LOOP;

异常处理部分

异常捕捉

格式：EXCEPTION可以捕捉到存储过程执行中遇到的异常，WHEN后面是具体的异常名称，捕捉到具体异常后，就会执行对应WHEN下面的异常处理代码块，如果捕捉到的异常和前面任何一个WHEN后面的异常名称都不匹配，则直接执行【WHEN OTHERS THEN】下面的异常处理代码。

EXCEPTION

  WHEN 异常01 THEN 

     异常处理代码块01

  WHEN 异常02 THEN 

     异常处理代码块02

  ......

  WHEN OTHERS THEN

    异常处理代码块n

异常处理部分也可以只有OTHERS部分，格式如下，捕捉到任何异常都会跳转到OTHERS对应的异常处理代码块。

EXCEPTION

  WHEN OTHERS THEN

    异常处理代码块

异常处理

SQLCODE：获取错误代码，

SQLERRM：获取具体的错误信息，

ROLLBACK：回滚事务。

异常处理示例：

EXCEPTION
  WHEN NO_DATA_FOUND THEN 
    dbms_output.put_line('未查询到数据！');
  WHEN CURSOR_ALREADY_OPEN THEN 
    dbms_output.put_line('游标已经打开！');
  WHEN OTHERS THEN
    dbms_output.put_line(SQLCODE); -- 输出错误代码
    dbms_output.put_line(SQLERRM); -- 输出错误信息

调用存储过程 调用命令格式

如果存储过程不带参数，调用格式如下。

call 存储过程名称();

如果存储过程只带输入参数，参数值用英文逗号隔开，传入的值要和存储过程定义的参数的顺序和类型保持一致，调用格式如下。

call 存储过程名称(参数值1,参数值2,...);

如果存储过程带输出参数，比如某个存储过程有三个参数，前两个为输入参数，第三个为输出参数，调用格式如下，需要先定义一个变量用来接收输出参数值，数据类型要和输出参数相同。

BEGIN

  DECLARE

    变量名01 数据类型;

  BEGIN

    存储过程名称(传入值01,传入值02,变量名01);

  END;

END;

完整存储过程示例

-- 存储过程声明，可以带参数，也可以不带参数
CREATE OR REPLACE PROCEDURE TESTSP01(
para01 in VARCHAR2,
para02 in INT,
para03 out VARCHAR2
)
AS
-- 变量定义，可以在变量定义时赋值。
var01 VARCHAR2(10);
var02 VARCHAR2(20);
var03 INT:=0;
var04 NUMBER(5,2):=8.88;
var05 VARCHAR2(50):='initvalue';
var06 INT;
var061 BOOLEAN;
var062 BOOLEAN;
var063 BOOLEAN;
var07 VARCHAR2(30);
var08 VARCHAR2(20):='';
var_col01 VARCHAR2(20);
var_col02 VARCHAR2(20);
-- 游标声明
CURSOR cursor01 IS
SELECT COL01,COL02,COL03
FROM TESTTABLE01
WHERE COL02 IS NOT NULL;
-- 行变量声明
row01 cursor01%ROWTY编程PE;
row02 TESTTABLE01%ROWTYPE;
BEGIN -- 过程执行部分
  -- 变量赋值，可以将常量或变量赋值给变量
  dbms_output.put_line('变量赋值示例:');
  var01:='hello';
  var02:=var01;
  var03:=8;
  var04:=1.8;
  dbms_output.put_line('var01:'||var01||','||'var02:'||var02||','||'var03:'||var03||','||'var04:'||var04);
  -- 通过sql查询给变量赋值
  select col02 into var05 from TESTTABLE01 where col01='id01';
  select count(1) into var06 from TESTTABLE01;
  select replace(col02,'value','hello')||' world' into var07 from TESTTABLE01 where col01='id01';
  dbms_output.put_line('var05:'||var05||','||'var06:'||var06||','||'var07:'||var07);
  -- IF判断示例
  dbms_output.put_line('IF判断示例:');
  IF para01 like 'value%' THEN
    dbms_output.put_line('IF判断满足第一个分支');
  ELSIF para02>1 THEN
    dbms_output.put_line(编程客栈'IF判断满足第二个分支')编程;
  ELSE
    dbms_output.put_line('IF判断满足第三个分支');
  END IF;
  -- while...loop循环方式读取游标数据
  dbms_output.put_line('while...loop循环方式读取游标数据:');
  OPEN cursor01; -- 打开游标cursor01
  var061:=cursor01%ISOPEN; -- 游标cursor01是否打开  
  var062:=cursor01%FOUND; -- 游标cursor01是否获取到数据
  var063:=cursor01%NOTFOUND; -- 游标cursor01是否没有获取到数据
  FETCH cursor01 INTO row01; -- 从游标cursor01获取行数据赋给行变量row01，并将游标推进到下一行。
  WHILE cursor01%FOUND LOOP --如果cursor01%FOUND结果为真，则执行while循环
    BEGIN
      var_col01 := row01.COL01; -- 获取行变量中的具体字段值赋给左边的变量
      var_col02 := row01.COL02; -- 获取行变量中的具体字段值赋给左边的变量
      dbms_output.put_line('获取记录数:'||cursor01%ROWCOUNT); -- 输出内容
      dbms_output.put_line('当前行数据:'||var_col01||','||var_col02); -- 输出内容
      FETCH cursor01 INTO row01; -- 从游标cursor01获取行数据赋给行变量row01，并将游标推进到下一行。
    END; 
  END LOOP;
  CLOSE cursor01; -- 关闭游标cursor01
  -- loop... exit when...循环方式读取游标数据
  dbms_output.put_line('loop... exit when...循环方式读取游标数据:');
  OPEN cursor01; -- 打开游标cursor01
  LOOP
     FETCH cursor01 INTO row01; -- 从游标cursor01获取行数据赋给行变量row01，并将游标推进到下一行。
     EXIT WHEN cursor01%NOTFOUND; -- 如果cursor01%NOTFOUND结果为真，则退出循环。
     var_col01 := row01.COL01; -- 获取行变量中的具体字段值赋给左边的变量
     var_col02 := row01.COL02; -- 获取行变量中的具体字段值赋给左边的变量
     dbms_output.put_line('获取记录数:'||cursor01%ROWCOUNT); -- 输出内容
     dbms_output.put_line('当前行数据:'||var_col01||','||var_col02); -- 输出内容
  END LOOP ;
  CLOSE cursor01; -- 关闭游标cursor01
  -- for循环依次输出1到8
  FOR i IN 1..8 LOOP
     var08:=var08||i;
  END LOOP;
  dbms_output.put_line('for循环输出1到8:'||var08);
  -- for循环读取游标数据
  dbms_output.put_line('for循环读取游标数据:');
  FOR row01 IN cursor01 LOOP -- 遍历游标cursor01获取数据赋给行变量row01
     var_col01 := row01.COL01; -- 获取行变量中的具体字段值赋给左边的变量
     var_col02 := row01.COL02; -- 获取行变量中的具体字段值赋给左边的变量
     dbms_output.put_line('获取记录数:'||cursor01%ROWCOUNT); -- 输出内容
     dbms_output.put_line('当前行数据:'||var_col01||','||var_col02); -- 输出内容
  END LOOP;
  --给输出参数赋值
  para03:='存储过程执行成功！';
EXCEPTION -- 异常处理部分
  WHEN NO_DATA_FOUND THEN 
    dbms_output.put_line('未查询到数据！');
  WHEN CURSOR_ALREADY_OPEN THEN 
    dbms_output.put_line('游标已经打开！');
  WHEN OTHERS THEN
    dbms_output.put_line(SQLCODE); -- 输出错误代码
    dbms_output.put_line(SQLERRM); -- 输出错误信息
END;

调用存储过js程示例

BEGIN
  DECLARE
    -- 定义变量接收输出参数值，数据类型要和输出参数相同
    outpara VARCHAR2(50); 
  BEGIN
    -- 调用存储过程，给输入参数赋值，将事先定义的变量传给输出参数。
    TESTSP01('value_in',8,outpara); 
    -- 打印输出参数
    dbms_output.put_line('输出参数值:'||outpara); 
  END;
END;

调用输出结果展示

到此这篇关于Oracle存储过程语法详解的文章就介绍到这了,更多相关Oracle存储过程语法内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！