oracle - 评估动态 PLSQL 语句

Question

此过程的目的是将记录类型的列组合成一行文本。

例子：SALARY= 80000, POSITION=ENG, ID=8

如何动态评估 l_stmt。

请注意，我不想以传统的方式通过连接来实现。

接受其他建议。

谢谢

CREATE TABLE Employee
(
   ID         INTEGER,
   POSITION   VARCHAR2 (32),
   SALARY     NUMBER
);

INSERT INTO EMPLOYEE (ID, POSITION, SALARY)
     VALUES (1, 'ENG', '100000');

INSERT INTO EMPLOYEE (ID, POSITION, SALARY)
     VALUES (2, 'PROGRAMMER', '80000');

COMMIT;


CREATE OR REPLACE PROCEDURE column_to_text (rec employee%ROWTYPE)
AS
   TYPE tb IS TABLE OF VARCHAR2 (30)
      INDEX BY BINARY_INTEGER;

   l_colnames   tb;
   l_stmt       VARCHAR2 (2500);
BEGIN
   SELECT column_name
     BULK COLLECT INTO l_colnames
     FROM sys.all_tab_cols
    WHERE table_name = 'EMPLOYEE';

   FOR i IN 1 .. l_colnames.COUNT
   LOOP
      l_stmt :=
         l_stmt || l_colnames (i) || '=' || 'REC.' || l_colnames (i) || ', ';

   END LOOP;

   DBMS_OUTPUT.PUT_LINE ( l_stmt );

   --SALARY=REC.SALARY, POSITION=REC.POSITION, ID=REC.ID,
END;

标签： oracleplsqloracle11gdynamic-sql