openGauss每日一练第17天 | 学习笔记

原创手机用户2634 2021-12-30

290

第十七课 openGauss定义游标

学习目标

学习openGauss定义游标

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

课程学习

连接数据库

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

学习内容如下

1.准备数据
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);

SELECT语句，用一个游标读取一个表
–开始一个事务
start transaction;

–建立一个名为cursor1的游标。
CURSOR cursor1 FOR SELECT * FROM tpcds.reason ORDER BY 1;

–在系统视图pg_cursors中查看可用游标
select * from pg_cursors;

–抓取头3行到游标cursor1里
FETCH FORWARD 3 FROM cursor1;

–从当前关联位置开始，抓取前面的1行
FETCH BACKWARD 1 FROM cursor1;

–关闭游标并提交事务
CLOSE cursor1;
select * from pg_cursors;
end;

VALUES子句，用一个游标读取VALUES子句中的内容
–建立一个名为cursor2的游标
start transaction;
CURSOR cursor2 FOR VALUES(1,2),(0,3) ORDER BY 1;
FETCH FORWARD 2 FROM cursor2;
CLOSE cursor2;
end;
WITH HOLD游标的使用
–声明该游标在创建它的事务结束后仍可继续使用
DECLARE cursor1 CURSOR WITH HOLD FOR SELECT * FROM tpcds.reason ORDER BY 1;

–抓取接下来的3行
FETCH FORWARD 3 FROM cursor1;
CLOSE cursor1;

5.移动游标
START TRANSACTION;
CURSOR cursor1 FOR SELECT * FROM tpcds.reason ORDER BY 1;

–忽略游标cursor1的前3行
MOVE FORWARD 3 FROM cursor1;

–抓取游标cursor1的前4行
FETCH 4 FROM cursor1;
CLOSE cursor1;
end;

6.存储过程中使用游标
create or replace procedure test_cursor_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_cursor_1();
drop procedure test_cursor_1;

7.清理数据
drop schema tpcds cascade;
drop table company;

课程作业

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

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

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

4.清理数据


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 cursor1 FOR SELECT * FROM tpcds.reason ORDER BY 1;
select * from pg_cursors;

MOVE FORWARD 2 FROM cursor1;
FETCH 3 FROM cursor1;

CLOSE cursor1;
select * from pg_cursors;
end;


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

    cursor c1_all is 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 proc1();
drop procedure proc1;



drop schema tpcds cascade;
drop table company;

课后作业记录


omm=# 
omm=# create schema tpcds;
CREATE SCHEMA
omm=# omm(# CREATE TABLE tpcds.reason
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);
ERROR:  relation "company" already exists
omm=# 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=# start transaction;
omm=# START TRANSACTION

omm=# CURSOR cursor1 FOR SELECT * FROM tpcds.reason ORDER BY 1;
DECLARE CURSOR
omm=# 
omm=# select * from pg_cursors;
  name   |                         statement                         | is_holdable | is_binary | is_s
crollable |         creation_time         
---------+-----------------------------------------------------------+-------------+-----------+-----
----------+-------------------------------
 cursor1 | CURSOR cursor1 FOR SELECT * FROM tpcds.reason ORDER BY 1; | f           | f         | t   
          | 2021-12-30 18:38:08.586362+08
(1 row)

omm=# 
omm=# FETCH FORWARD 3 FROM cursor1;
 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=# 
omm=# FETCH BACKWARD 1 FROM cursor1;
 r_reason_sk |   r_reason_id    |                                            r_reason_desc           
                                  
-------------+------------------+--------------------------------------------------------------------
----------------------------------
           4 | AAAAAAAABAAAAAAA | reason 3                                                           
                                 
(1 row)

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

omm=# end;
COMMIT
omm=# 
omm=# 
omm=# 
omm=# start transaction;
START TRANSACTION
omm=# CURSOR cursor2 FOR VALUES(1,2),(0,3) ORDER BY 1;
DECLARE CURSOR
omm=# FETCH FORWARD 2 FROM cursor2;
 column1 | column2 
---------+---------
       0 |       3
       1 |       2
(2 rows)

omm=# CLOSE cursor2;
CLOSE CURSOR
omm=# end;
COMMIT
omm=# 
omm=# 
omm=# 
omm=# DECLARE cursor1 CURSOR WITH HOLD FOR SELECT * FROM tpcds.reason ORDER BY 1;
DECLARE CURSOR
omm=# 
omm=# 
omm=# FETCH FORWARD 3 FROM cursor1;
           4 | AAAAAAAABAAAAAAA | reason 3                                                           
                                 
          10 | AAAAAAAABAAAAAAA | reason 5                                                           
                                 
(3 rows)

 r_reason_sk |   r_reason_id    |                                            r_reason_desc           
                                  
-------------+------------------+--------------------------------------------------------------------
----------------------------------
           3 | AAAAAAAABAAAAAAA | reason 1                                                           
                                 
omm=# CLOSE cursor1;
CLOSE CURSOR
omm=# 

omm=# 
omm=# START TRANSACTION;
START TRANSACTION
omm=# CURSOR cursor1 FOR SELECT * FROM tpcds.reason ORDER BY 1;
DECLARE CURSOR
omm=# 
omm=# MOVE FORWARD 3 FROM cursor1;
MOVE 3
omm=# 
omm=# FETCH 4 FROM cursor1;
 r_reason_sk |   r_reason_id    |                                            r_reason_desc           
                                  
-------------+------------------+--------------------------------------------------------------------
----------------------------------
          10 | AAAAAAAABAAAAAAA | reason 4                                                           
                                 
          10 | AAAAAAAABAAAAAAA | reason 2                                                           
                                 
          20 | AAAAAAAACAAAAAAA | reason 6                                                           
                                 
          30 | AAAAAAAACAAAAAAA | reason 7                                                           
                                 
(4 rows)

omm=# CLOSE cursor1;
CLOSE CURSOR
omm=# end;
COMMIT
omm=# 



omm=# omm$# create or replace procedure test_cursor_1
omm-# as
    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
omm$#     if not c1_all%isopen then
omm$#         open c1_all;
omm$# omm$#     end if;
    loop
omm$#         fetch c1_all into company_name, company_loc, company_no;
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=# call test_cursor_1();
INFO:  company_name: backberry
INFO:  company_name: backberry
INFO:  company_name: macrosoft
INFO:  company_name: macrosoft
INFO:  company_name: oracle
INFO:  company_name: oracle
INFO:  company_name: oracle
 test_cursor_1 
---------------
 
(1 row)

omm=# drop procedure test_cursor_1;
DROP PROCEDURE
omm=# 
omm=# 
omm=# drop schema tpcds cascade;
NOTICE:  drop cascades to table tpcds.reason
DROP SCHEMA
omm=# drop table company;
DROP TABLE
omm=# 
omm=# 


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);
CREATE TABLE
omm=# 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 cursor1 FOR SELECT * FROM tpcds.reason ORDER BY 1;
DECLARE CURSOR
omm=# select * from pg_cursors;
  name   |                         statement                         | is_holdable | is_binary | is_scrollable |         creation_time         
---------+-----------------------------------------------------------+-------------+-----------+---------------+-------------------------------
 cursor1 | CURSOR cursor1 FOR SELECT * FROM tpcds.reason ORDER BY 1; | f           | f         | t             | 2021-12-30 21:39:37.087347+08
(1 row)

omm=# 
omm=# MOVE FORWARD 2 FROM cursor1;
MOVE 2
omm=# FETCH 3 FROM cursor1;
 r_reason_sk |   r_reason_id    |                                            r_reason_desc                                             
-------------+------------------+------------------------------------------------------------------------------------------------------
          10 | AAAAAAAABAAAAAAA | reason 5                                                                                            
          10 | AAAAAAAABAAAAAAA | reason 4                                                                                            
          10 | AAAAAAAABAAAAAAA | reason 2                                                                                            
(3 rows)

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

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

omm=# drop procedure proc1;
DROP PROCEDURE
omm=# 
omm=# 
omm=# drop schema tpcds cascade;
NOTICE:  drop cascades to table tpcds.reason
DROP SCHEMA
omm=# drop table company;
DROP TABLE
omm=#

opengauss

最后修改时间：2021-12-30 21:42:30

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者