SQL 在 plsql 中立即执行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4803340/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
EXECUTE IMMEDIATE in plsql
提问by Жасулан Бердибеков
How to get a result from this code
如何从这段代码中得到结果
EXECUTE IMMEDIATE 'SELECT * FROM ' || table_name
through for loop
通过for 循环
The usual method looks like this
通常的方法看起来像这样
for items in (select * from this_table)
loop
htp.p(items.id);
end loop;
回答by René Nyffenegger
If you reallyneed to select * fromdynamic table name, then I'd probably go with dbms_sql
如果你真的需要select * from动态表名，那么我可能会去dbms_sql
Type for a record:
输入记录：
create type tq84_varchar2_tab as table of varchar2(4000);
/
Type for a result set(which is an array of records):
输入结果集（它是一个记录数组）：
create type tq84_varchar2_tab_tab as table of tq84_varchar2_tab;
/
The function that does the select and returns an instance of the result set:
执行选择并返回结果集实例的函数：
create or replace function tq84_select_star_from_table(table_name in varchar2)
return tq84_varchar2_tab_tab
as
stmt_txt varchar2(4000);
cur number;
columns_desc dbms_sql.desc_tab;
column_cnt number;
result_set tq84_varchar2_tab_tab;
begin
stmt_txt := 'select * from ' || table_name;
cur := dbms_sql.open_cursor;
dbms_sql.parse(cur, stmt_txt, dbms_sql.native);
dbms_sql.describe_columns(cur, column_cnt, columns_desc);
dbms_sql.close_cursor(cur);
stmt_txt := 'select tq84_varchar2_tab(';
for i in 1 .. column_cnt loop
if i != 1 then
stmt_txt := stmt_txt || ',';
end if;
stmt_txt := stmt_txt || columns_desc(i).col_name;
end loop;
stmt_txt := stmt_txt || ') from ' || table_name;
-- dbms_output.put_line(stmt_txt);
execute immediate stmt_txt
bulk collect into result_set;
return result_set;
end tq84_select_star_from_table;
The function can then be used with something like:
然后可以将该函数与以下内容一起使用：
declare
records tq84_varchar2_tab_tab;
begin
records := tq84_select_star_from_table('user_objects');
for i in 1 .. records.count loop
dbms_output.put_line (records(i)(5) || ': ' || records(i)(1));
end loop;
end;
/
回答by APC
Here is a simple function which dynamically opens a cursor variable, using the passed table name parameter.
这是一个简单的函数，它使用传递的表名参数动态打开一个游标变量。
create or replace function get_details_by_dno
( p_tab in user_tables.table_name%type
, p_dno in dept.deptno%type )
return sys_refcursor
is
rv sys_refcursor;
stmt varchar2(32767);
begin
stmt := 'select * from '
||p_tab
||' where deptno = :1';
open rv for stmt using p_dno;
return rv;
end;
/
It also uses the DEPTNO as a filter; consequently the function will failif we pass a table which doesn't have such a column.
它还使用 DEPTNO 作为过滤器；因此，如果我们传递一个没有此类列的表，该函数将失败。
Some clients can interpret the ref cursor's metadata. For instance JDBC and ODBC ResultSets can do this. SQL*Plus can do it:
一些客户端可以解释引用游标的元数据。例如 JDBC 和 ODBC ResultSets 可以做到这一点。SQL*Plus 可以做到：
SQL> exec :rc := get_details_by_dno('DEPT', 50)
PL/SQL procedure successfully completed.
SQL> print rc
DEPTNO DNAME LOC REGION
---------- -------------- ------------- ----------
50 HOUSEKEEPING INTERNAL
SQL> exec :rc := get_details_by_dno('EMP', 50)
PL/SQL procedure successfully completed.
SQL> exec :rc := get_details_by_dno('EMP', 50)
PL/SQL procedure successfully completed.
SQL> print rc
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
8085 TRICHLER PLUMBER 8061 08-APR-10 3500 50
8060 VERREYNNE PLUMBER 8061 08-APR-08 4000 50
8061 FEUERSTEIN PLUMBER 7839 27-FEB-10 4500 50
8100 PODER PLUMBER 8061 3750 50
SQL>
PL/SQL cannot do this. So we need to be explicit about the table and column names.
PL/SQL 不能这样做。所以我们需要明确表名和列名。
create or replace procedure print_details_by_dno
( p_tab in user_tables.table_name%type
, p_dno in dept.deptno%type )
is
rc sys_refcursor;
emp_rec emp%rowtype;
dept_rec dept%rowtype;
begin
rc := get_details_by_dno( p_tab , p_dno );
if p_tab = 'EMP' then
fetch rc into emp_rec;
while rc%found loop
dbms_output.put_line('ename='||emp_rec.ename||' empno='||emp_rec.empno);
fetch rc into emp_rec;
end loop;
elsif p_tab = 'DEPT' then
fetch rc into dept_rec;
while rc%found loop
dbms_output.put_line('dname='||dept_rec.dname);
fetch rc into dept_rec;
end loop;
end if;
end;
/
Let's see it running:
让我们看看它运行：
SQL> set serveroutput on
SQL> exec print_details_by_dno('EMP',50)
ename=TRICHLER empno=8085
ename=VERREYNNE empno=8060
ename=FEUERSTEIN empno=8061
ename=PODER empno=8100
PL/SQL procedure successfully completed.
SQL> exec print_details_by_dno('DEPT',50)
dname=HOUSEKEEPING
PL/SQL procedure successfully completed.
SQL>
回答by a_horse_with_no_name
You need to declare a cursor from the dynamic sql and loop through it.
您需要从动态 sql 中声明一个游标并循环遍历它。
An example of this is available in the manual:
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/dynamic.htm#CHDJHAHE
手册中提供了一个示例：http:
//download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/dynamic.htm#CHDJHAHE
回答by vc 74
TYPE RefCurTyp IS REF CURSOR;
sql VARCHAR2(200);
cursor RefCurTyp;
id VARCHAR2(200);
BEGIN
sql := 'SELECT * FROM ' || table_name;
OPEN cursor FOR sql;
LOOP
FETCH cursor INTO id;
htp.p(id);
EXIT WHEN cursor%NOTFOUND;
END LOOP;
CLOSE cursor;
END;
