云数据库 GAUSSDB-FETCH:示例

时间：2024-11-02 18:52:17

云数据库 GAUSSDB

示例

--创建一个SCHEMA。
openGauss=# CREATE SCHEMA tpcds;
 
--创建表tpcds.customer_address。
openGauss=# CREATE TABLE tpcds.customer_address
(
ca_address_sk         INTEGER           NOT NULL,
ca_address_id         CHARACTER(16)     NOT NULL,
ca_street_number      INTEGER                    ,
ca_street_name        CHARACTER (20)
);
 
--向表中插入多条记录。
openGauss=# INSERT INTO tpcds.customer_address VALUES (1, 'AAAAAAAABAAAAAAA', '18', 'Jackson'),(2, 'AAAAAAAACAAAAAAA', '362', 'Washington 6th'),(3, 'AAAAAAAADAAAAAAA', '585', 'Dogwood Washington');

--SELECT语句，用一个游标读取一个表。开始一个事务。
openGauss=# START TRANSACTION;

--建立一个名为cursor1的游标。
openGauss=# CURSOR cursor1 FOR SELECT * FROM tpcds.customer_address ORDER BY 1;

--抓取头3行到游标cursor1里。
openGauss=# FETCH FORWARD 3 FROM cursor1;
 ca_address_sk |  ca_address_id   | ca_street_number |   ca_street_name      
---------------+------------------+------------------+--------------------
             1 | AAAAAAAABAAAAAAA | 18               | Jackson                           
             2 | AAAAAAAACAAAAAAA | 362              | Washington 6th                    
             3 | AAAAAAAADAAAAAAA | 585              | Dogwood Washington        
(3 rows)

--关闭游标并提交事务。
openGauss=# CLOSE cursor1;

--结束一个事务。
openGauss=# END;

--VALUES子句，用一个游标读取VALUES子句中的内容。开始一个事务。
openGauss=# START TRANSACTION;

--建立一个名为cursor2的游标。
openGauss=# CURSOR cursor2 FOR VALUES(1,2),(0,3) ORDER BY 1;

--抓取头2行到游标cursor2里。
openGauss=# FETCH FORWARD 2 FROM cursor2;
column1 | column2
---------+---------
0 |       3
1 |       2
(2 rows)

--关闭游标并提交事务。
openGauss=# CLOSE cursor2;

--结束一个事务。
openGauss=# END;

--WITH HOLD游标的使用，开启事务。
openGauss=# START TRANSACTION;

--创建一个with hold游标。
openGauss=# DECLARE cursor1 CURSOR WITH HOLD FOR SELECT * FROM tpcds.customer_address ORDER BY 1;

--抓取头2行到游标cursor1里。
openGauss=# FETCH FORWARD 2 FROM cursor1;
 ca_address_sk |  ca_address_id   | ca_street_number |   ca_street_name      
---------------+------------------+------------------+--------------------
             1 | AAAAAAAABAAAAAAA | 18               | Jackson                           
             2 | AAAAAAAACAAAAAAA | 362              | Washington 6th                    
(2 rows)

--结束事务。
openGauss=# END;

--抓取下一行到游标cursor1里。
openGauss=# FETCH FORWARD 1 FROM cursor1;
 ca_address_sk |  ca_address_id   | ca_street_number |   ca_street_name      
---------------+------------------+------------------+--------------------
             3 | AAAAAAAADAAAAAAA | 585              | Dogwood Washington        
(1 row)

--关闭游标。
openGauss=# CLOSE cursor1;

--删除表tpcds.customer_address。
openGauss=# DROP TABLE tpcds.customer_address;

--删除SCHEMA。
openGauss=# DROP SCHEMA tpcds CASCADE;