plsql执行多条mysql_PostgreSQL执行动态SQL返回多个结果集

PostgreSQL执行动态SQL返回多个结果集

废话少说直接上代码

创建存储

CREATE OR REPLACE FUNCTION test(ref1 refcursor, ref2 refcursor,aa varchar)

RETURNS SETOF refcursor AS $BODY$

DECLARE

sqlString varchar(4000) = ‘’; --查询sql

countSqlString varchar(4000) = ‘’; --统计sql

BEGIN

– IF(aa= ‘*’) THEN

– Begin

– SqlString := ‘’; – 拼接条件

– End;

– ELSE

– Begin

– SqlString := ‘’;

– End;

– END IF;

– IF ends_with IS NOT NULL THEN

– sql := sql || ‘’; --拼接sql

– END IF;

SqlString := ‘SELECT * FROM act_ge_property WHERE value_=’’’||aa||’’’ limit 10 OFFSET 0’;

countSqlString := ‘SELECT COUNT(1) FROM act_ge_property WHERE value_=’’’||aa||’’’ limit 10 OFFSET 0’;

– EXECUTE(SqlString);

– EXECUTE(countSqlString);

– RETURN QUERY EXECUTE SqlString;

– 打印SQL

– raise notice ‘sql=>%’,SqlString;

open $1 FOR EXECUTE(SqlString);

RETURN NEXT $1;

open $2 FOR EXECUTE(countSqlString);

RETURN NEXT $2;

RETURN;

END;

$BODY$LANGUAGE plpgsql VOLATILE

COST 100

ROWS 1000

执行查询

BEGIN;

SELECT test(‘a’,‘b’,‘1’);

FETCH ALL in “a”;

FETCH ALL in “b”;

COMMIT;