我正在编写一个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;更多推荐
发布评论