其它控制语句
更新时间 2025-02-14 10:22:32
最近更新时间: 2025-02-14 10:22:32
本页介绍天翼云TeleDB数据库存储过程开发的其它控制语句。
动态执行
teledb=# CREATE OR REPLACE PROCEDURE p_execute() AS
$$
DECLARE
v_sql TEXT;
v_relname TEXT;
BEGIN
v_sql := 'SELECT relname FROM pg_class limit 1';
EXECUTE v_sql INTO v_relname;
RAISE NOTICE 'relname = %',v_relname;
END;
$$
LANGUAGE plpgsql;
CREATE PROCEDURE
teledb=# CALL p_execute();
NOTICE: relname = pg_stat_statements
CALL
teledb=#也可以使用immediate。
teledb=# CREATE OR REPLACE PROCEDURE p_execute() AS
$$
DECLARE
v_sql TEXT;
v_relname TEXT;
BEGIN
v_sql := 'SELECT relname FROM pg_class limit 1';
EXECUTE immediate v_sql INTO v_relname;
RAISE NOTICE 'relname = %',v_relname;
END;
$$
LANGUAGE plpgsql;
CREATE PROCEDURE
teledb=# CALL p_execute();
NOTICE: relname = s1
CALL
teledb=#动态执行就是拼SQL 语句，然后使用 EXECUTE 命令执行。
执行一个没有结果的命令
teledb=# CREATE OR REPLACE PROCEDURE p_perform() AS
$$
BEGIN
perform md5(random()::text);
END;
$$
LANGUAGE plpgsql;
CREATE PROCEDURE
teledb=# call p_perform();
CALL
teledb=# 获取执行结果
teledb=# CREATE OR REPLACE PROCEDURE p_found() AS
$$
DECLARE
v_relname TEXT;
BEGIN
SELECT relname INTO v_relname FROM pg_class limit 1;
IF FOUND THEN
RAISE NOTICE '查询到记录，值为%',v_relname;
ELSE
RAISE NOTICE '查不到记录' ;
END IF;
END;
$$
LANGUAGE plpgsql;
CREATE PROCEDURE
teledb=# CALL p_found();
NOTICE: 查询到记录，值为pg_stat_statements
CALL获取影响行数
teledb=# CREATE OR REPLACE PROCEDURE p_row_count() AS
$$
DECLARE
v_row_count BIGINT;
BEGIN
delete from t1;
GET DIAGNOSTICS v_row_count = ROW_COUNT;
RAISE NOTICE '查询到的记录数为 % ',v_row_count;
END;
$$
LANGUAGE plpgsql;
CREATE PROCEDURE
teledb=# call p_row_count();
NOTICE: 查询到的记录数为 3
CALL
teledb=#
