sql - 如何从表名称为局部变量的表中选择(informix)

我想从表中选择，其中表名是一个局部变量，列名是另一个局部变量，但我得到以下错误：
指定的表ll_tb_name不在数据库中

CREATE PROCEDURE rmg_request_file ( al_task_code integer)
RETURNING integer as byte;


DEFINE ll_task_code integer;
DEFINE ll_pdf_column varchar(50);
DEFINE ll_tb_name varchar(60);
DEFINE ll_request_file REFERENCES BYTE;


SELECT pdf_column,table_name INTO  ll_pdf_column ,ll_tb_name  FROM rnmtask WHERE task_code = al_task_code;


FOREACH

SELECT  ll_pdf_column into  ll_request_file FROM ll_tb_name

return ll_request_file  with resume ;
end foreach;

end procedure

最佳答案

假设您有一个足够新的informix（11.70）版本，那么您应该能够在spl中使用动态sql，如下所示：

BEGIN;

CREATE TABLE rnmtask
(
    pdf_column VARCHAR(32) NOT NULL,
    table_name VARCHAR(32) NOT NULL,
    task_code  INTEGER NOT NULL PRIMARY KEY
);

INSERT INTO rnmtask VALUES("symbol", "elements", 1);
INSERT INTO rnmtask VALUES("name", "elements", 2);
INSERT INTO rnmtask VALUES("atomic_number", "elements", 3);

CREATE PROCEDURE rmg_request_file(al_task_code INTEGER)
    RETURNING VARCHAR(255) AS colval;

    DEFINE ll_pdf_column    VARCHAR(50);
    DEFINE ll_tb_name       VARCHAR(60);
    DEFINE stmt             VARCHAR(255);
    DEFINE result           VARCHAR(255);

    SELECT pdf_column, table_name
      INTO ll_pdf_column, ll_tb_name
      FROM rnmtask
     WHERE task_code = al_task_code;

    LET stmt = "SELECT " || ll_pdf_column || " FROM " || ll_tb_name;
    PREPARE p FROM stmt;
    DECLARE C CURSOR FOR p;
    OPEN C;
    WHILE sqlcode = 0
        FETCH C INTO result;
        IF sqlcode != 0 THEN
            EXIT WHILE;
        END IF;
        RETURN result WITH RESUME;
    END WHILE;

    CLOSE C;
    FREE C;
    FREE p;

END PROCEDURE;

EXECUTE PROCEDURE rmg_request_file(1);
EXECUTE PROCEDURE rmg_request_file(2);
EXECUTE PROCEDURE rmg_request_file(3);

ROLLBACK;

这假设您在数据库中有一个方便的元素表：

CREATE TABLE elements
(
    atomic_number   INTEGER NOT NULL PRIMARY KEY CONSTRAINT c1_elements
                    CHECK (atomic_number > 0 AND atomic_number < 120),
    symbol          CHAR(3) NOT NULL UNIQUE CONSTRAINT c2_elements,
    name            CHAR(20) NOT NULL UNIQUE CONSTRAINT c3_elements,
    atomic_weight   DECIMAL(8, 4) NOT NULL,
    period          SMALLINT NOT NULL
                    CHECK (period BETWEEN 1 AND 7),
    group           CHAR(2) NOT NULL
                    -- 'L' for Lanthanoids, 'A' for Actinoids
                    CHECK (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');

关于sql - 如何从表名称为局部变量的表中选择(informix)，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/14746540/