怎样使字符变量变成系统识别的表名或列名 怎样使字符变量变成系统识别的表名或列名 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 使用execute immediate例如:declare v_sql varchar2(400);v_tablename varchar2(30);l_cnt number;begin v_tablename:= table_name; v_sql := 'select count(1) from '||v_tablename; execute immediate v_sql into l_cnt; dbms_output.put_line(l_cnt);end;/ declare v_tab varchar2;v_tab := 'a'; execute immediate 'select count(1) from '|| V_systab || ';正确吗? declare v_tab varchar2; l_cnt number;begin v_tab := 'a'; execute immediate 'select count(1) from '|| v_tab into l_cnt; -- 使用into ,pl/sql里不能使用select * 返回结果集的. end;/ create or replace procedure SP_Testasv_sql varchar2(400);v_tablename varchar2(30);l_cnt number;begin v_tablename := 'au_zone'; v_sql := 'select count(1) from '||v_tablename; execute immediate v_sql into l_cnt; dbms_output.put_line(l_cnt);end;还是报错. create or replace procedure SP_Test_1as v_tab varchar2(30); l_cnt number;begin v_tab := 'au_zone'; execute immediate 'select count(1) from '|| v_tab into l_cnt; -- 使用into ,pl/sql里不能使用select * 返回结果集的. dbms_output.put_line(l_cnt); end;编译成功,怎么执行出错呢? calll sp_test_1;报错:不是一个有效的函数或存储过程. SQL> set serveroutput onSQL> create or replace procedure SP_Test 2 as 3 --v_sql varchar2(400); 4 v_tablename varchar2(30); 5 l_cnt number; 6 begin 7 v_tablename := 'au_zone'; 8 execute immediate 'select count(1) from '||v_tablename into l_cnt; 9 dbms_output.put_line(l_cnt); 10 end; 11 /Procedure createdSQL> execute SP_Test;0PL/SQL procedure successfully completedSQL> 楼主当中有什么字符敲错了吗? 细致点应该没问题的, 是对的SQL> create or replace procedure SP_Test 2 is 3 v_sql varchar2(400); 4 v_tablename varchar2(30); 5 l_cnt number(10); 6 begin 7 v_tablename := 'au_zone'; 8 v_sql := 'select count(1) into l_cnt from '||v_tablename; 9 execute immediate v_sql; 10 dbms_output.put_line(l_cnt); 11 end SP_Test; 12 / Procedure created SQL> with as语句性能求优化 sql语句如何限制返回查询结果的条数啊? 触发器实现唯一性约束问题 大家来看象这样怎么实现? ★★★请教深圳Appeon(正阳软件)的朋友 Oracle Management Server的登录问题。。。。 向Oracle数据库插入记录必须使用sql语句吗 用批处理写热备份时碰到一个问题! 请教:急(在线等待) 执行SQL语句! 关于Oracle中Job不自动运行的问题 请给我SQL语句
declare
v_sql varchar2(400);
v_tablename varchar2(30);
l_cnt number;
begin
v_tablename:= table_name;
v_sql := 'select count(1) from '||v_tablename; execute immediate v_sql into l_cnt;
dbms_output.put_line(l_cnt);
end;
/
v_tab := 'a'; execute immediate 'select count(1) from '|| V_systab || ';
正确吗?
v_tab varchar2;
l_cnt number;
begin
v_tab := 'a';
execute immediate 'select count(1) from '|| v_tab into l_cnt; -- 使用into ,pl/sql里不能使用select * 返回结果集的.
end;
/
as
v_sql varchar2(400);
v_tablename varchar2(30);
l_cnt number;
begin
v_tablename := 'au_zone';
v_sql := 'select count(1) from '||v_tablename; execute immediate v_sql into l_cnt; dbms_output.put_line(l_cnt);
end;
还是报错.
as
v_tab varchar2(30);
l_cnt number;
begin
v_tab := 'au_zone';
execute immediate 'select count(1) from '|| v_tab into l_cnt; -- 使用into ,pl/sql里不能使用select * 返回结果集的.
dbms_output.put_line(l_cnt);
end;
编译成功,怎么执行出错呢?
SQL> create or replace procedure SP_Test
2 as
3 --v_sql varchar2(400);
4 v_tablename varchar2(30);
5 l_cnt number;
6 begin
7 v_tablename := 'au_zone';
8 execute immediate 'select count(1) from '||v_tablename into l_cnt;
9 dbms_output.put_line(l_cnt);
10 end;
11 /Procedure createdSQL> execute SP_Test;0PL/SQL procedure successfully completedSQL>
SQL> create or replace procedure SP_Test
2 is
3 v_sql varchar2(400);
4 v_tablename varchar2(30);
5 l_cnt number(10);
6 begin
7 v_tablename := 'au_zone';
8 v_sql := 'select count(1) into l_cnt from '||v_tablename;
9 execute immediate v_sql;
10 dbms_output.put_line(l_cnt);
11 end SP_Test;
12 /
Procedure created
SQL>