postgresql 参数替换 游标_PostgreSQL 如何方便的查看游标结果做验证

--1.创建模拟数据

DROP TABLE test;

CREATE TABLE test(id int, info text);

INSERT INTO test SELECT generate_series(1, 10), 'test';

--2.创建存储过程

CREATE OR REPLACE FUNCTION test_1() RETURNS refcursor AS

$BODY$

DECLARE

CURSOR c1 FOR SELECT * FROM test;

BEGIN

OPEN c1;

RETURN c1;

END

$BODY$

LANGUAGE plsql;

--3.在命令行中可以查询：

TEST=# BEGIN;

BEGIN

TEST=# SELECT test_1();

TEST_1

--------

C1

(1 row)

TEST=# FETCH 5 IN "C1";

ID | INFO

----+------

1 | test

2 | test

3 | test

4 | test

5 | test

(5 rows)

TEST=# END;

COMMIT

--4.管理工具需要在匿名块中执行：

DO

$$

DECLARE

ret_ref refcursor;

one_row record;

BEGIN

OPEN ret_ref FOR SELECT 1 ;

ret_ref := test_1();

FETCH ret_ref INTO one_row;

WHILE ret_ref%FOUND LOOP

raise notice 'id is: %, text is: %', one_row.id, one_row.info;

FETCH NEXT IN ret_ref INTO one_row;

END LOOP;

CLOSE ret_ref;

END

$$

结果：

00000: id is: 1, text is: test

00000: id is: 2, text is: test

00000: id is: 3, text is: test

00000: id is: 4, text is: test

00000: id is: 5, text is: test

00000: id is: 6, text is: test

00000: id is: 7, text is: test

00000: id is: 8, text is: test

00000: id is: 9, text is: test

00000: id is: 10, text is: test

--注意：如果在存储过程中，已经消费了，则返回的游标需要重新定位到开始

--5.处理out参数的游标，一样的：

CREATE OR REPLACE FUNCTION test_2(v_id int, c1 OUT refcursor) RETURNS refcursor AS

$BODY$

DECLARE

BEGIN

OPEN c1 FOR SELECT * FROM test WHERE id < v_id;

END

$BODY$

LANGUAGE plsql;

DO

$$

DECLARE

ret_ref refcursor;

one_row record;

BEGIN

OPEN ret_ref FOR SELECT 1 ;

ret_ref := test_2(6);

FETCH ret_ref INTO one_row;

WHILE ret_ref%FOUND LOOP

raise notice 'id is: %, text is: %', one_row.id, one_row.info;

FETCH NEXT IN ret_ref INTO one_row;

END LOOP;

CLOSE ret_ref;

END

$$

结果：

00000: id is: 1, text is: test

00000: id is: 2, text is: test

00000: id is: 3, text is: test

00000: id is: 4, text is: test

00000: id is: 5, text is: test

标签：PostgreSQL,验证,text,游标,ret,00000,test,ref,id

来源： https://www.cnblogs.com/kuang17/p/14005841.html