用EXECUTE嵌套调用存储过程：动态执行SQL代码

在动手之前，先来聊聊：啥是动态SQL？想象一下，你要创建一个表，这个表的名字是参数传进来的，或者你要查一个表，这个表名只有程序运行时才知道。这种情况，普通的静态SQL就不够用了——这时候动态执行就派上用场了。

PL/pgSQL给你提供了EXECUTE命令，可以执行你传进去的SQL字符串。这样你就能“现写现跑”SQL代码，根据参数拼出不一样的SQL语句。

动态SQL有啥用？主要有这些理由：

1. 灵活性：可以根据输入动态拼SQL，比如操作那些名字提前不知道的表或列。
2. 自动化：能用独特名字自动创建表或索引。
3. 通用性：不用每次都重写存储过程，能适配不同的数据结构。

举个实际例子：你在做一个分析系统，每来一个新客户就要给他单独建个表存数据。用EXECUTE，这些都能自动搞定。

EXECUTE的语法

用EXECUTE写动态SQL，大概长这样：

EXECUTE 'SQL字符串';

简单例子：

DO $$
BEGIN
  EXECUTE 'CREATE TABLE test_table (id SERIAL PRIMARY KEY, name TEXT)';
END $$;

这段代码会创建一个test_table表。很简单对吧？不过我们还可以玩更花的。

EXECUTE的用法举例

1. 用动态名字创建表

比如你想根据当天日期创建表名，可以这样：

DO $$
DECLARE
  table_name TEXT;
BEGIN
  -- 生成表名
  table_name := 'report_' || to_char(CURRENT_DATE, 'YYYYMMDD');

  -- 用动态名字建表
  EXECUTE 'CREATE TABLE ' || table_name || ' (id SERIAL PRIMARY KEY, data TEXT)';

  -- 打印消息看看
  RAISE NOTICE '表 % 创建成功', table_name;
END $$;

这里表名是用当天日期拼出来的，最后的SQL字符串传给EXECUTE执行。

2. 用动态参数查表

比如你要查哪个表是参数传进来的，可以写个函数：

CREATE OR REPLACE FUNCTION get_data_from_table(table_name TEXT)
RETURNS TABLE(id INTEGER, name TEXT) AS $$
BEGIN
  RETURN QUERY EXECUTE
    'SELECT id, name FROM ' || table_name || ' WHERE id < 10';
END $$ LANGUAGE plpgsql;

调用方法：

SELECT * FROM get_data_from_table('employees');

这种写法很适合做通用工具，比如动态报表系统啥的。

动态SQL的问题和限制

动态SQL很自由，但自由也有代价，主要有这些坑：

1. SQL注入：如果你直接拼字符串参数，黑客可能能执行恶意SQL。

   有漏洞的例子：

   EXECUTE 'SELECT * FROM users WHERE name = ''' || user_input || '''';
   

   如果user_input是'; DROP TABLE users; --，那表users就没了。

2. 不好调试：动态SQL是运行时才拼出来的，调试起来比静态SQL麻烦多了。

3. 性能损失：动态SQL没法用PostgreSQL的执行计划缓存，可能会慢一点。

怎么防SQL注入

想防SQL注入，别直接拼字符串，得用参数化。PL/pgSQL里可以用quote_literal()处理字符串参数，用quote_ident()处理表名、列名这些标识符。

安全写法：

DO $$
DECLARE
  table_name TEXT;
  user_input TEXT := 'John';
BEGIN
  table_name := 'employees';

  EXECUTE 'SELECT * FROM ' || quote_ident(table_name) ||
          ' WHERE name = ' || quote_literal(user_input);
END $$;

实战：动态更新表

比如你要写个过程，能根据参数更新指定表的数据：

CREATE OR REPLACE FUNCTION update_table_data(table_name TEXT, id_value INT, new_data TEXT)
RETURNS VOID AS $$
BEGIN
  EXECUTE 'UPDATE ' || quote_ident(table_name) ||
          ' SET data = ' || quote_literal(new_data) ||
          ' WHERE id = ' || id_value;
END $$ LANGUAGE plpgsql;

调用方法：

SELECT update_table_data('test_table', 1, '已更新的值');

例子：为客户生成报表

比如你要按客户自动建报表表，可以这样：

CREATE OR REPLACE FUNCTION create_client_report(client_id INT)
RETURNS VOID AS $$
DECLARE
  table_name TEXT;
BEGIN
  -- 拼报表表名
  table_name := 'client_report_' || client_id;

  -- 创建报表表
  EXECUTE 'CREATE TABLE ' || quote_ident(table_name) || ' (order_id INT, amount NUMERIC)';

  -- 填充数据
  EXECUTE 'INSERT INTO ' || quote_ident(table_name) ||
          ' SELECT order_id, amount FROM orders WHERE client_id = ' || client_id;

  RAISE NOTICE '客户 % 的报表已创建：表 %', client_id, table_name;
END $$ LANGUAGE plpgsql;

用EXECUTE写动态SQL真的很强大，能让PL/pgSQL自动化和灵活性爆棚。但一定要注意SQL注入风险。想让你的SQL又稳又安全，记得用quote_ident()和quote_literal()。

下节课我们会深入讲怎么写更复杂的存储过程，比如数据校验、记录更新和操作日志。准备好，动态SQL会成为你实现这些功能的基础！

1

调查/小测验

嵌套事务第 53 级，课程 4

不可用

嵌套事务

嵌套事务

打开