oracle存储过程使用(包含游标、递归查询数据)

杨扬树

已于 2024-01-04 12:32:09 修改

阅读量1.8k

点赞数 26

分类专栏： oracle 文章标签： oracle 数据库

于 2024-01-02 17:31:02 首次发布

本文链接：https://blog.csdn.net/qq_23135259/article/details/135344848

版权

oracle 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

本文详细介绍了Oracle数据库中的存储过程，包括其整体流程、变量声明（基础型、引用型、记录型和集合型）、游标使用方法（声明、打开、读取和关闭），以及流程控制和循环的运用。通过实例演示如何创建存储过程来处理数据，如区划名称的递归修改和合并父级区划。

摘要由CSDN通过智能技术生成

oracle存储过程，不区分大小写

一、存储过程整体流程

二、存储过程——声明变量（存储过程中的变量是用来接收数据的，类似于java中设置的变量）

1、基础型变量：

2、引用型变量（变量类型的长度取决于表中字段的类型和长度）：

3、记录型变量（接受表中的一整行记录，相当于java中的一个对象）

4、集合类型的变量，游标（用于存储一个查询返回的多行数据）

【1】游标语法（声明——打开——读取——关闭）：

一、存储过程整体流程

（无入参）

create procedure 存储过程名称 as

声明变量

begin

方法体，处理逻辑

end;

调用存储过程: call 存储过程名();

（有入参的存储过程）

create procedure 存储过程名称(变量 in 变量类型) as

声明变量

begin

方法体，处理逻辑

end;

调用存储过程: call 存储过程名(参数);

二、存储过程——声明变量（存储过程中的变量是用来接收数据的，类似于java中设置的变量）

语法：变量名变量类型

变量分类：基础型变量、引用型变量、记录型变量、集合变量

变量区别：

基础型变量：用于接收单个字段，需要设置变量类型及长度。

引用型变量：用于接收单个字段，变量类型取决于引用字段的类型和长度，不需要设置变量类型，会自动识别出引用字段的类型，当做变量的类型。

记录型变量：用于接收一行数据，接收表中的一整行记录。

集合变量：用于接收多行数据，也就是一个数据集合。

1、基础型变量()：

        （1）、直接赋值——变量名:=数值如:name:='chen'
        v_name varchar(30);
        v_name:='张三'
         （2）、语句赋值——使用select ....into ...赋值
        语句赋值将张三赋值给v-name
        insert '张三' into v_name from dual;
        select name,code into v_name,v_code into pub_region;//将pub_region表中的name，和code赋值给变量v_name,v_code

2、引用型变量（变量类型的长度取决于表中字段的类型和长度）：

通过表名.列名%TYPE 指定变量类型和长度，例：v_name emp.ename%TYPE

3、记录型变量（接受表中的一整行记录，相当于java中的一个对象）

语法：变量名表名%ROWTYPE ,例：v_name emp%rowtype;

4、集合类型的变量，游标（用于存储一个查询返回的多行数据）

【1】游标语法（声明——打开——读取——关闭）：

       （1）游标声明： cursor 游标名[(参数列表)] is 查询语句;//查询语句的结果被放置在了游标中
       （2）游标打开：open 游标名;
       （3）游标取值：fetch 游标名 into 变量列表 //取出游标中数据放到变量中，fetch代表取，into 代表赋值给变量
       （4）游标关闭: close 游标名;

【2】游标的属性

       %ROWCOUNT 整型       获得fetch语句返回的数据行数
       %FOUND   布尔值最近的fetch语句返回一行数据为真，否则为加
       %NOTFOUND 布尔类型与%FOUND属性返回值相反，通常用来做退出循环
       %ISOPEN       布尔类型游标已经打开为真，否则为假

三、方法体中常用的处理

【1】流程控制if

语法：注意elsif ，不是else，少了一个e
   begin
       if 条件1 then 执行1
       elsif 条件2 then 执行2
       else 执行3
       end if;
   end;

【2】循环loop

loop循环语法（loop循环必须要写退出条件）：
begin
   loop
   exit when 退出条件
   end loop;
end;

四、整体例子：

实现效果：将pub_region表中的区划名称，变成带有父级区划的新名称，比如，当前区划名称是：东明县，处理为：山东省-菏泽市-东明县

处理操作：

1）使用存储过程，游标遍历数据，对每条数据处理，

2）递归查询出每个区划的父级区划名称，通过区划编码关联递归查询，将数据插入到新表pub_region_all_name中，递归查询使用了start with connect by ，

3）使用listagg 函数处理递归查询到的数据（Listagg函数是Oracle内置函数之一，用于将多行数据串联在一起，形成一个字符串。它接收一个分隔符作为参数，通过该分隔符将多行数据串联成一个字符串）

详细示例：

【1】pub_region 的表结构及创建测试表pub_region：

//1、原始查询数据表：建表pub_region语句

create table PUB_REGION
(
  code        VARCHAR2(12) not null,
  name        VARCHAR2(30) not null,
  short_code  VARCHAR2(12),
  parent_code VARCHAR2(12),
  type        VARCHAR2(2)
);

comment on column PUB_REGION.code
  is '区划编码';

comment on column PUB_REGION.name
  is '区划名称';

comment on column PUB_REGION.short_code
  is '区划编码缩写';

comment on column PUB_REGION.parent_code
  is '父级区划编码';

comment on column PUB_REGION.type
  is '类型，暂时没用';

//2、插入测试数据

insert into pub_region (CODE, NAME, SHORT_CODE, PARENT_CODE, TYPE)
values ('370000000000', '山东省', null, '#', '1');

insert into pub_region (CODE, NAME, SHORT_CODE, PARENT_CODE, TYPE)
values ('370100000000', '济南市', null, '370000000000', '1');

insert into pub_region (CODE, NAME, SHORT_CODE, PARENT_CODE, TYPE)
values ('37010000000O', '市直机关', null, '370100000000', '0');

insert into pub_region (CODE, NAME, SHORT_CODE, PARENT_CODE, TYPE)
values ('370102000000', '历下区', '370102', '370100000000', '1');

insert into pub_region (CODE, NAME, SHORT_CODE, PARENT_CODE, TYPE)
values ('370102001000', '解放路街道', '370102001', '370102000000', '1');

insert into pub_region (CODE, NAME, SHORT_CODE, PARENT_CODE, TYPE)
values ('370102002000', '千佛山街道', '370102002', '370102000000', '1');

insert into pub_region (CODE, NAME, SHORT_CODE, PARENT_CODE, TYPE)
values ('370102003000', '趵突泉街道', '370102003', '370102000000', '1');

insert into pub_region (CODE, NAME, SHORT_CODE, PARENT_CODE, TYPE)
values ('370102004000', '泉城路街道', '370102004', '370102000000', '1');

//3、处理完后的数据，插入到新表中
create table PUB_REGION_ALL_NAME
(
  code    NVARCHAR2(12) not null,
  name    NVARCHAR2(64) not null,
  allname NVARCHAR2(255)
)

【2】创建存储过程语句：

create or replace  procedure opera_pub_region_name as

 --(1)声明游标，用来保存获取到的数据集合，以便后续对这个数据集进行处理，将pub_region中的数据赋- 
 --值给v_obj中
 cursor v_obj is select code,name from pub_region ;

 --(2)声明变量pub_name,pub_code接收游标中的name和code，pub_name_new用来存储处理后的区划名称
 pub_name pub_region.name%type;
 pub_code pub_region.code%type;
 pub_name_new varchar(200):='空';
begin
 --打开游标
  open v_obj;
       --循环开始
       loop
         --取出v_obj 并赋值给pub_code,pub_name
         fetch v_obj into pub_code,pub_name;
         --当数据都循环一遍后，就退出循环
         exit when v_obj%notfound;
         --将递归查询结果，通过listagg处理后，赋值给pub_name_new，语句是按照pub_code，向上查询所属的县、市、省
        select listagg(name,'-')  within group(order by code) into pub_name_new from pub_region o start with o.code =pub_code connect by  o.code= prior o.parent_code;
        -- dbms_output.put_line('code:'||pub_code||'name:'||pub_name||'pub_name_new:'||pub_name_new);
         --数据插入到新表中
         insert into pub_region_all_name (code,name,allname) values(pub_code,pub_name,pub_name_new);
         commit;
        --循环结束
       end loop;
   --关闭游标
  close v_obj;

end;

【3】调用存储过程：