从存储过程中进行选择并将 SP 结果存储到表中

Question

我正在尝试根据 Informix 数据库中几个存储过程的结果构建几个表，我无法编辑这些表，并且第二个 SP 的参数需要另一个 SP 的值(将被调用几次(。我已经弄清楚该怎么做，但在执行它时遇到问题，主要是因为我无法将我的数据库保存到 Temp 表中，以便我可以修改它。

如果有人能给我一些指示，那就太好了。

我尝试过的一些例子

call <stored procedure(...)> INTO TEMP db

和

create temp table name(...)
INSERT INTO namefrom storedprocedure()

和

create temp table name(...)
Select INTO name from storedprocedure()

亲切问候 狐狸

Answer 1

下面是一个用户定义函数(也称为存储过程(的示例，该函数返回插入到临时表中的多个值。它使用stores_demo数据库。

create function func1()
returning char(15), char(15);
define v_fname, v_lname char(15);
foreach select fname, lname into v_fname, v_lname from customer
return v_fname, v_lname with resume;
end foreach
end function;
create temp table tt1(fname char(15), lname char(15));
insert into tt1 execute function func1();

Answer 2

您可以使用：

SELECT * FROM TABLE(stored_procedure()) INTO TEMP p32;

这会将存储过程返回的行插入到临时表p32中。

可能还有其他方法可以做到这一点，但这似乎有效并且相当容易理解。

^{(测试：在运行macOS 10.14.6 Mojave的Mac上测试Informix 12.10.FC6。}

测试架构

CREATE TABLE elements
(
atomic_number   INTEGER NOT NULL PRIMARY KEY
CHECK (atomic_number > 0 AND atomic_number < 120),
symbol          CHAR(3) NOT NULL UNIQUE,
name            CHAR(20) NOT NULL UNIQUE,
atomic_weight   DECIMAL(8, 4) NOT NULL,
pt_period       SMALLINT NOT NULL
CHECK (pt_period BETWEEN 1 AND 7),
pt_group        CHAR(2) NOT NULL
-- 'L' for Lanthanoids, 'A' for Actinoids
CHECK (pt_group IN ('1', '2', 'L', 'A', '3', '4', '5', '6',
'7', '8', '9', '10', '11', '12', '13',
'14', '15', '16', '17', '18')),
stable          CHAR(1) DEFAULT 'Y' NOT NULL
CHECK (stable IN ('Y', 'N'))
);
INSERT INTO elements VALUES(  1, 'H',   'Hydrogen',        1.0079, 1, '1',  'Y');
INSERT INTO elements VALUES(  2, 'He',  'Helium',          4.0026, 1, '18', 'Y');
INSERT INTO elements VALUES(  3, 'Li',  'Lithium',         6.9410, 2, '1',  'Y');
INSERT INTO elements VALUES(  4, 'Be',  'Beryllium',       9.0122, 2, '2',  'Y');
INSERT INTO elements VALUES(  5, 'B',   'Boron',          10.8110, 2, '13', 'Y');
INSERT INTO elements VALUES(  6, 'C',   'Carbon',         12.0110, 2, '14', 'Y');
INSERT INTO elements VALUES(  7, 'N',   'Nitrogen',       14.0070, 2, '15', 'Y');
INSERT INTO elements VALUES(  8, 'O',   'Oxygen',         15.9990, 2, '16', 'Y');
INSERT INTO elements VALUES(  9, 'F',   'Fluorine',       18.9980, 2, '17', 'Y');
INSERT INTO elements VALUES( 10, 'Ne',  'Neon',           20.1800, 2, '18', 'Y');
INSERT INTO elements VALUES( 11, 'Na',  'Sodium',         22.9900, 3, '1',  'Y');
INSERT INTO elements VALUES( 12, 'Mg',  'Magnesium',      24.3050, 3, '2',  'Y');
INSERT INTO elements VALUES( 13, 'Al',  'Aluminium',      26.9820, 3, '13', 'Y');
INSERT INTO elements VALUES( 14, 'Si',  'Silicon',        28.0860, 3, '14', 'Y');
INSERT INTO elements VALUES( 15, 'P',   'Phosphorus',     30.9740, 3, '15', 'Y');
INSERT INTO elements VALUES( 16, 'S',   'Sulphur',        32.0650, 3, '16', 'Y');
INSERT INTO elements VALUES( 17, 'Cl',  'Chlorine',       35.4530, 3, '17', 'Y');
INSERT INTO elements VALUES( 18, 'Ar',  'Argon',          39.9480, 3, '18', 'Y');

当然，整个表格列出了 118 个元素。

示例程序

DROP PROCEDURE IF EXISTS lightweights;
CREATE PROCEDURE lightweights()
RETURNING INTEGER AS atomic_number, VARCHAR(3) AS symbol, VARCHAR(20) AS name;
DEFINE num INTEGER;
DEFINE sym VARCHAR(3);
DEFINE nam VARCHAR(20);
FOREACH SELECT e.atomic_number, e.symbol, e.name
INTO num, sym, nam
FROM elements AS e
WHERE atomic_number < 10
RETURN num, sym, nam WITH RESUME;
END FOREACH;
END PROCEDURE;

创建临时表的过程执行示例

$ sqlcmd -d stores
SQL[3368]: select * from table(lightweights());
1|H  |Hydrogen            
2|He |Helium              
3|Li |Lithium             
4|Be |Beryllium           
5|B  |Boron               
6|C  |Carbon              
7|N  |Nitrogen            
8|O  |Oxygen              
9|F  |Fluorine            
SQL[3368]: select * from table(lightweights()) into temp p32;;
SQL[3369]: select * from p32;
1|H  |Hydrogen            
2|He |Helium              
3|Li |Lithium             
4|Be |Beryllium           
5|B  |Boron               
6|C  |Carbon              
7|N  |Nitrogen            
8|O  |Oxygen              
9|F  |Fluorine            
SQL[3370]: q;
$

请注意，在足够新的 Informix 版本中，您可以将TEMP替换为STANDARD或RAW以创建永久表而不是临时表(删除TEMP等效于将其替换为STANDARD(。 请参见 SELECT 语句> INTO 表子句。