BigQuery是否支持EXECUTE IMMEDIATE？能否实现Oracle式动态建表？

Great question! Let's walk through how this works in BigQuery compared to your Oracle example:

First: Does BigQuery support EXECUTE IMMEDIATE? #

Yes, BigQuery does support EXECUTE IMMEDIATE, but with some key differences from Oracle. It’s used to run dynamically generated SQL statements (including DDL like CREATE TABLE), but it can only be used within stored procedures or interactive scripts—you can’t call it from a regular user-defined function (UDF).

Implementing Dynamic Table Creation (Like Your Oracle Function) #

In Oracle, you used an autonomous transaction function to handle dynamic table creation. BigQuery doesn’t allow UDFs to execute DDL, so you’ll need to use a stored procedure instead. Here’s an equivalent implementation that mirrors your Oracle logic:

CREATE OR REPLACE PROCEDURE make_a_table1(
  p_table_name STRING,
  p_column_name STRING,
  p_data_type STRING
)
BEGIN
  DECLARE sydt STRING;
  DECLARE create_sql STRING;
  
  -- Generate timestamp suffix (matches your Oracle format: HH24_MI_SS)
  SET sydt = FORMAT_TIMESTAMP('%H_%M_%S', CURRENT_TIMESTAMP());
  SELECT sydt; -- Simulates dbms_output.put_line to view the timestamp
  
  -- Build the CREATE TABLE statement
  SET create_sql = FORMAT(
    'CREATE TABLE `%s_%s` (%s %s)',
    p_table_name,
    sydt,
    p_column_name,
    p_data_type
  );
  
  -- Print the generated SQL (like your dbms_output.put_line(var))
  SELECT create_sql;
  
  -- Execute the dynamic table creation
  EXECUTE IMMEDIATE create_sql;
END;

To run this procedure: #

CALL make_a_table1('test_table', 'id', 'INT64');

Key Differences from Oracle #

• Function vs. Stored Procedure: BigQuery UDFs (SQL or JavaScript) are strictly for data transformation—they can’t run DDL/DML. Only stored procedures and scripts support EXECUTE IMMEDIATE and DDL operations.
• Transaction Handling: BigQuery automatically commits DDL statements (like CREATE TABLE) by default, so you don’t need a COMMIT statement (unlike your Oracle autonomous transaction function).
• Timestamp Formatting: We use FORMAT_TIMESTAMP in BigQuery to generate the timestamp suffix, which works similarly to Oracle’s TO_CHAR(SYSDATE, ...).

内容的提问来源于stack exchange，提问作者Madhu Alle