create or replace procedure czk is
v_output number;
cursor mycursor is
select table_name from user_all_tables;
begin
for mycur in mycursor loop
insert into sys.test values(mycur.table_name,1);
execute immediate 'select count(*) into v_output from '||mycur.table_name;
end loop;
end czk;
create or replace procedure czk is
v_output number;
cursor mycursor is
select table_name from user_all_tables;
begin
for mycur in mycursor loop
execute immediate 'select count(*) into v_output from '||mycur.table_name;
insert into sys.test values(mycur.table_name,v_output);
end loop;
end czk;--老是报缺少关键字
--老是报缺少关键字是编译的时候还是运行的时候,你上面execute immediate 'select count(*) into v_output from '||mycur.table_name;
是动态sql,所以不会影响编译,只会影响到运行。如果是编译的时候报错的话,就还有其他的问题了。
动态 SQL 中,想将查询出来的结果保存到变量里使用的方式是execute immediate 'select count(*) from dual' into v_output;
execute immediate 'select count(*) into v_output from '||mycur.table_name;
你这一句里边的 v_output 已经不再是你当前运行的存储过程中定义的那个 v_output了
好好查查 execute immediate的语法吧 上边已经有人给出正确答案了
create or replace procedure czk is
v_output number;
cursor mycursor is
select table_name from user_all_tables;
begin
for mycur in mycursor loop
execute immediate 'select count(*) into v_output from '||mycur.table_name;
insert into sys.test values(mycur.table_name as col1,v_output as col2);
end loop;
end czk;
into [参数] 不能放在sql字符串里
SQL>
SQL> create or replace procedure czk is
2 v_output number;
3 cursor mycursor is
4 select table_name from user_all_tables;
5 begin
6 for mycur in mycursor loop
7 execute immediate 'select count(*) into '|| v_output ||' from '||mycur.table_name;
8 insert into test values(mycur.table_name as col1,v_output as col2);
9 end loop;
10 end czk;
11 /Warning: Procedure created with compilation errorsSQL> show errors;
Errors for PROCEDURE CSDN.CZK:LINE/COL ERROR
-------- -----------------------------
8/46 PL/SQL: ORA-00917: 缺失逗号
8/5 PL/SQL: SQL Statement ignoredSQL>
SQL>
SQL> create or replace procedure czk is
2 v_output number;
3 cursor mycursor is
4 select table_name from user_all_tables;
5 begin
6 for mycur in mycursor loop
7 execute immediate 'select count(*) from '||mycur.table_name into v_output;
8 insert into test values(mycur.table_name as col1,v_output as col2);
9 end loop;
10 end czk;
11 /Warning: Procedure created with compilation errorsSQL> show errors;
Errors for PROCEDURE CSDN.CZK:LINE/COL ERROR
-------- -----------------------------
8/46 PL/SQL: ORA-00917: 缺失逗号
8/5 PL/SQL: SQL Statement ignoredSQL>
还是一样的
把那去掉
你这个是编译错,还没有通过编译应该是
第8行
8 insert into test values(mycur.table_name as col1,v_output as col2);
改成
insert into test values(mycur.table_name,v_output); 如果只有这两个字段的话,如果多余两个字段的话
insert into test(col1, col2) values(mycur.table_name,v_output);
第7行的运行错误还是没有改好
7 execute immediate 'select count(*) into '|| v_output ||' from '||mycur.table_name;
改为
execute immediate 'select count(*) from '||mycur.table_name into v_output;
SQL> create or replace procedure czk is
2 v_output number;
3 cursor mycursor is
4 select table_name from user_all_tables;
5 begin
6 for mycur in mycursor loop
7 execute immediate 'select count(*) from '||mycur.table_name into v_output;
8 insert into mytest values(mycur.table_name,v_output);
9 end loop;
10 end czk;
11 /Procedure createdSQL>
v_output number;
cursor mycursor is
select table_name from user_all_tables;
begin
for mycur in mycursor loop
execute immediate 'select count(*) from '||mycur.table_name into v_output;
insert into test values(mycur.table_name as v,v_output as v1);
end loop;
end czk;