在SQL存储的进程中基于逗号分隔的字符串动态创建表(Dynamically create tables based on comma delimited string in SQL stored pr

在SQL存储的进程中基于逗号分隔的字符串动态创建表(Dynamically create tables based on comma delimited string in SQL stored proceedure)

我正在编写一个SQL存储过程，它接受一个填充名称作为参数，根据填充名称创建一个表，然后填充该表。 只要我知道我提前传递了多少人口名称，它就会很好用，但是当我不知道有多少人口提前时，我需要这个。

我将传递一个逗号分隔的字符串，我需要以下过程来为该字符串中的每个值运行。 存储过程是一系列Union All语句，但我只包括下面的第一个。

DECLARE ID ALIAS FOR $1; POPULATION FOR $2; SQL VARCHAR(32000); BEGIN SQL:='CREATE TABLE DEMO_POPULATION_' ||POPULATION|| '_TABLE AS SELECT ' ||ID|| ' AS ID , '||POPULATION||' AS POPULATION , 'AGE' AS CATEGORY , MAX((SELECT AGE_MAX FROM ID_INSTANCE A JOIN DEMO_SOURCE B ON (A.ID = B.NAME) WHERE ID = ' ||ID|| ' AND POPULATION LIKE '||POPULATION||' )) AS VAL UNION ALL ...

此外，我正在研究IBM Netezza，我知道很多人都没有使用它。 因此对一般SQL的响应会很好，它不能是特定于SQL Server的东西。

I am writing a SQL stored procedure that takes in a population name as a parameter, creates a table based on the population name, and then populates the table. It works great as long as I know how many population names I am passing to it ahead of time, but I need this to work when I don't know how many populations there are in advance.

I will be passing a comma delimited string and I need the following procedure to run for each value in that string. The stored procedure is a series of Union All statements, but I only included the first one below.

DECLARE ID ALIAS FOR $1; POPULATION FOR $2; SQL VARCHAR(32000); BEGIN SQL:='CREATE TABLE DEMO_POPULATION_' ||POPULATION|| '_TABLE AS SELECT ' ||ID|| ' AS ID , '||POPULATION||' AS POPULATION , 'AGE' AS CATEGORY , MAX((SELECT AGE_MAX FROM ID_INSTANCE A JOIN DEMO_SOURCE B ON (A.ID = B.NAME) WHERE ID = ' ||ID|| ' AND POPULATION LIKE '||POPULATION||' )) AS VAL UNION ALL ...

Also I am working on IBM Netezza, which I know is not used by a lot of people. So a response for general SQL would be great, it just can't be something that is specific to SQL Server.

最满意答案

请找到以下工作示例 -

BEGIN v_sql:='Create table empx (id int)'; execute immediate v_sql; END;

Please find following working example -

BEGIN v_sql:='Create table empx (id int)'; execute immediate v_sql; END;