如何通过PL/SQL过程和表名作为变量打印动态SQL中的所有表行

Question

我正试图通过DBMS_OUTPUT.PUT_LINE使用动态SQL和循环打印一个表，因为我的表名必须是一个变量，因为它每个月都会更改，下面是我的代码

我还对如何存储数据以及如何将数据放入变量中感到困惑？变量是否需要是CURSOR？为了在下面的代码中进行测试，我使用了HRschema中的演示employee表。

此外，还有另一种数据类型大于varchar2(4000(，因为我需要将SQL语句存储到一个变量中才能使用execute immediately，而该变量需要存储一个非常复杂的SQL查询。

SET SERVEROUTPUT ON SIZE 1000000;
DECLARE
v_TBL_NAME varchar2(200):='EMPLOYEES';
v_sql varchar2(200):='IS select * from ' ||v_TBL_NAME;
CURSOR C1;
BEGIN
EXECUTE IMMEDIATE v_sql INTO C1;
FOR REC IN C1
LOOP
DBMS_OUTPUT.PUT_LINE(REC.EMPLOYEE_ID||' '||REC.FIRST_NAME);
END LOOP;
END;
/

Answer 1

您可以使用引用游标并将其大容量收集到嵌套表中。然后像这样在表格上循环：

DECLARE
TYPE lt_record IS RECORD
(
empID INTEGER,
fname VARCHAR2(100)
);
TYPE lt_recordTable IS TABLE OF lt_record;
l_cTableCursor SYS_REFCURSOR;
l_sTableName VARCHAR2(1000) := 'TABLE NAME';
l_tRecords lt_recordTable;
BEGIN
OPEN l_cTableCursor FOR ('SELECT employee_id, first_name FROM '||l_sTableName);
LOOP
FETCH l_cTableCursor
BULK COLLECT INTO l_tRecords
LIMIT 1000;
EXIT WHEN l_tRecords.COUNT = 0;
FOR i IN 1..l_tRecords.LAST LOOP
dbms_output.put_line(l_tRecords(i).empID||' '||l_tRecords(i).fname);
END LOOP;
END LOOP;
CLOSE l_cTableCursor;
END;
/

Answer 2

您可以使用open ... for游标语法：

declare
v_tbl_name varchar2(30) := 'EMPLOYEES'; -- assume this will be a parameter later
v_sql varchar2(32767) := 'select employee_id, first_name from ' || v_tbl_name;
-- if there is a static table you could use %rowtype, or your own type with column%type
type t_rec is record (employee_id number, first_name varchar2(20));
v_rec t_rec; 
c1 sys_refcursor;
begin
open c1 for v_sql;
loop
fetch c1 into v_rec;
exit when c1%notfound;
dbms_output.put_line(v_rec.employee_id||' '||v_rec.first_name);
end loop;
end;
/
100 Steven
101 Neena
102 Lex
103 Alexander
104 Bruce
...
205 Shelley
206 William

PL/SQL procedure successfully completed.

希望您指定的是实际需要的列，而不是使用*。。。

根据您的应用程序/客户端，您可以直接访问ref游标；此语法适用于SQL*Plus、SQLDeveloper、SQLcl以及可能的一些第三方客户端：

var rc refcursor;
declare
v_tbl_name varchar2(200) := 'EMPLOYEES'; -- assume this will be a parameter later
v_sql varchar2(200) := 'select * from ' || v_tbl_name;
begin
open :rc for v_sql;
end;
/
print rc

产生类似的输出

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                     PHONE_NUMBER         HIRE_DATE  JOB_ID         SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- ---------- ---------- ---------- -------------- ---------- -------------
100 Steven               King                      SKING                     515.123.4567         1987-06-17 AD_PRES         24000                                      90
101 Neena                Kochhar                   NKOCHHAR                  515.123.4568         1989-09-21 AD_VP           17000                       100            90
102 Lex                  De Haan                   LDEHAAN                   515.123.4569         1993-01-13 AD_VP           17000                       100            90
...
205 Shelley              Higgins                   SHIGGINS                  515.123.8080         1994-06-07 AC_MGR          12000                       101           110
206 William              Gietz                     WGIETZ                    515.123.8181         1994-06-07 AC_ACCOUNT       8300                       205           110
107 rows selected.

您可以从JDBC等访问ref游标