openGauss每日一练 第17天 游标

学习目标

CURSOR命令定义一个游标，用于在一个大的查询里面检索少数几行数据。

为了处理SQL语句，存储过程进程分配一段内存区域来保存上下文联系。游标是指向上下文区域的句柄或指针。借助游标，存储过程可以控制上下文区域的变化。

游标命令只能在事务块里使用。
FETCH通过已创建的游标来检索数据。

课程学习

连接openGauss
#第一次进入等待15秒
#数据库启动中…
su - omm
gsql -r

课后作业

1.创建游标，且使用select子句指定游标返回的行，分别使用FETCH抓取数据，MOVE重定位游标

-- 准备数据
create schema tpcds;
CREATE TABLE tpcds.reason
(
r_reason_sk integer,
r_reason_id character(16),
r_reason_desc character(100)
);
INSERT INTO tpcds.reason values(3,'AAAAAAAABAAAAAAA','reason 1'),
(10,'AAAAAAAABAAAAAAA','reason 2'),(4,'AAAAAAAABAAAAAAA','reason 3'),
(10,'AAAAAAAABAAAAAAA','reason 4'),(10,'AAAAAAAABAAAAAAA','reason 5'),
(20,'AAAAAAAACAAAAAAA','reason 6'),(30,'AAAAAAAACAAAAAAA','reason 7');

create table company(name varchar(100), loc varchar(100), no integer);
insert into company values ('macrosoft',    'usa',          001);
insert into company values ('oracle',       'usa',          002);
insert into company values ('backberry',    'canada',       003);

-- 创建游标
start transaction;
CURSOR cur_1 FOR SELECT * FROM tpcds.reason ORDER BY 1;
FETCH FORWARD 3 FROM cur_1;

MOVE FORWARD 3 FROM cur_1;
FETCH FORWARD 4 FROM cur_1;
commit;

2.在系统视图pg_cursors中查看游标

select * from pg_cursors;

3.创建一个使用游标的存储过程

create or replace procedure test_cur_1
as
    company_name    varchar(100);
    company_loc varchar(100);
    company_no  integer;

    cursor c1_all is --cursor without args
        select name, loc, no from company order by 1, 2, 3;
begin
    if not c1_all%isopen then
        open c1_all;
    end if;
    loop
        fetch c1_all into company_name, company_loc, company_no;
		RAISE INFO 'company_name: %' ,company_name;
        exit when c1_all%notfound;
    end loop;
    if c1_all%isopen then
        close c1_all;
    end if;
end;
/
 

call  test_cur_1();

4.清理数据

omm=# create schema tpcds;
CREATE SCHEMA
omm=# CREATE TABLE tpcds.reason
omm(# omm-# (
r_reason_sk integer,
omm(# r_reason_id character(16),
omm(# r_reason_desc character(100)
);
omm(# CREATE TABLE
omm=# INSERT INTO tpcds.reason values(3,'AAAAAAAABAAAAAAA','reason 1'),
omm-# (10,'AAAAAAAABAAAAAAA','reason 2'),(4,'AAAAAAAABAAAAAAA','reason 3'),
omm-# (10,'AAAAAAAABAAAAAAA','reason 4'),(10,'AAAAAAAABAAAAAAA','reason 5'),
omm-# (20,'AAAAAAAACAAAAAAA','reason 6'),(30,'AAAAAAAACAAAAAAA','reason 7');
INSERT 0 7
omm=# 
omm=# create table company(name varchar(100), loc varchar(100), no integer);
omm=# CREATE TABLE
insert into company values ('macrosoft',    'usa',          001);
INSERT 0 1
omm=# insert into company values ('oracle',       'usa',          002);
INSERT 0 1
omm=# insert into company values ('backberry',    'canada',       003);
INSERT 0 1
omm=# 
omm=# omm=# -- 创建游标
start transaction;
START TRANSACTION
omm=# CURSOR cur_1 FOR SELECT * FROM tpcds.reason ORDER BY 1;
DECLARE CURSOR
omm=# FETCH FORWARD 3 FROM cur_1;
 r_reason_sk |   r_reason_id    |                                            r_reason_desc                                           
  
-------------+------------------+----------------------------------------------------------------------------------------------------
--
           3 | AAAAAAAABAAAAAAA | reason 1                                                                                           
 
           4 | AAAAAAAABAAAAAAA | reason 3                                                                                           
 
          10 | AAAAAAAABAAAAAAA | reason 5                                                                                           
 
(3 rows)

omm=# 
omm=# MOVE FORWARD 3 FROM cur_1;
MOVE 3
omm=# FETCH FORWARD 4 FROM cur_1;
 r_reason_sk |   r_reason_id    |                                            r_reason_desc                                           
  
-------------+------------------+----------------------------------------------------------------------------------------------------
--
          30 | AAAAAAAACAAAAAAA | reason 7                                                                                           
 
(1 row)

omm=# commit;
COMMIT
omm=# select * from pg_cursors;
 name | statement | is_holdable | is_binary | is_scrollable | creation_time 
------+-----------+-------------+-----------+---------------+---------------
(0 rows)

omm=# create or replace procedure test_cur_1
omm-# as
omm$#     company_name    varchar(100);
omm$#     company_loc varchar(100);
omm$#     company_no  integer;
omm$# omm$# 
    cursor c1_all is --cursor without args
omm$#         select name, loc, no from company order by 1, 2, 3;
omm$# begin
    if not c1_all%isopen then
omm$# omm$#         open c1_all;
    end if;
omm$# omm$#     loop
omm$#         fetch c1_all into company_name, company_loc, company_no;
omm$# 
ABORT                      DECLARE                    LOCK                       SELECT
ALTER                      DELETE FROM                MOVE                       SET
ANALYZE                    DISCARD                    NOTIFY                     SHOW
BEGIN                      DO                         PREPARE                    START
CALL                       DROP                       REASSIGN                   TABLE
CHECKPOINT                 END                        REFRESH MATERIALIZED VIEW  TRUNCATE
CLOSE                      EXECUTE                    REINDEX                    UNLISTEN
CLUSTER                    EXPLAIN                    RELEASE                    UPDATE
COMMENT                    FETCH                      RESET                      VACUUM
COMMIT                     GRANT                      REVOKE                     VALUES
COPY                       INSERT                     ROLLBACK                   WITH
CREATE                     LISTEN                     SAVEPOINT                  
DEALLOCATE                 LOAD                       SECURITY LABEL             
omm$# RAISE INFO 'company_name: %' ,company_name;
omm$#         exit when c1_all%notfound;
omm$#     end loop;
omm$#     if c1_all%isopen then
omm$#         close c1_all;
omm$#     end if;
omm$# end;
omm$# /
CREATE PROCEDURE
omm=#  
omm=# 
omm=# call  test_cur_1();
INFO:  company_name: backberry
INFO:  company_name: macrosoft
INFO:  company_name: oracle
INFO:  company_name: oracle
 test_cur_1 
------------
 
(1 row)

omm=# drop procedure if exists test_cur_1();
DROP PROCEDURE
omm=# drop table tpcds.reason;
omm=# DROP TABLE
omm=# 
drop table company;
DROP TABLE
omm=# drop schema tpcds cascade;
DROP SCHEMA