Oracle PL/SQL:帮助解决"PLS-00103:遇到符号"LOOP"当期望以下之一时: [英] Oracle PL/SQL: Help resolving "PLS-00103: Encountered the symbol "LOOP" when expecting one of the following: if"
问题描述
请参阅第二次编辑.新的编码得到不同的错误.
See Second Edit. New coding gets different error.
我收到在遇到if时遇到循环"错误.我的目标是返回已由特定project_id更新的表的列表. project_id存储在每个表上标有project_id的列中.
I am getting the "encountered a loop when expecting an if" error. My goal is to return a list of tables that have been updated by a specific project_id. The project_id is stored in a column on each table labeled project_id.
我使用了with语句来创建2个表.一个表(sb_table)是我要查看的所有表的单列.还有更多表，但我认为如果我先缩小列表范围，它将加快处理速度.我正在创建的另一个表(项目)返回一个值，将提供的prjt_name转换为实际项目编号(不要问我为什么，但这是我公司的设置方式，用户创建了prct_name却从不知道项目编号).
I used a with statement to create 2 tables. One table (sb_table) is a single column of all the tables that I want to look at. There are many more tables, but I figured it would speed things up if I narrowed down the list first. The other table I am creating (project) returns a single value turning the prjt_name provided into the actual project number (don't ask me why, but this is how my company has it set up, user creates a prct_name and is never aware of the project number).
然后，我尝试遍历各个表，以查看它们的project_id列中是否具有项目编号.如果没有，我将它们从sb_table中删除.
Then I am trying to loop through the tables to see if they have the project number in their project_id column. If they do not, I delete them from the sb_table.
最终，我将要从所有更新的表中获取所有更新的行，但是我目前仍停留在获取更新表的列表中.
Ultimately, I am going to want to get all of the updated rows from all of the updated tables, but I am currently stuck on getting a list of the updated tables.
declare
query varchar2(10000);
table_count NUMBER;
update_count number;
prjt_name varchar2 not null := "01213264B";
cursor my_cur is select sbt.table_name from sb_table sbt;
begin
with sb_tables as (select table_name from all_tab_columns@db2 where
column_name = 'PROJECT_ID' and owner = 'SANDBOX'),
project as (select project_id from sandbox.sb_project@db2 where
project_name = upper(prjt_name))
--select sbt.table_name
--from sb_table sbt
for tableName in my_cur loop
query := 'select count(t.project_id) as "CNT" '||
'from sandbox.' || tableName || '@db2 t, project p '||
' where t.project_id = p.project_id ';
Execute immediate query
into update_count;
if update_count <= 0 then
query := 'DELETE FROM sb_tables where table_name = ' || tableName;
execute immediate query ;
end loop;
end;
根据注释，将select语句移至声明并现在遍历my_cur.我仍然遇到相同的错误.
Edit 1: Per comments, moved the select statement to the declare and am looping through my_cur now. I still get the same error.
根据建议更新了编码.现在，我得到了另一个错误消息.
Edit 2: Updated coding based on suggestions. I now get a different error message.
NEW ERROR: ORA-06550: line 12, column 16:
PLS-00306: wrong number or types of arguments in call to '||'
ORA-06550: line 12, column 7:
我在子查询中遗漏了一个=，从而产生了错误第13行缺少表达式".
Edit 3: I was missing an = in my sub query which produced the error "Missing expression at line 13."
现在，我得到一些结果，然后出现以下消息，出错了
Edit 4: Now I get some results then error out with the following message
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file ext_qsp_benefit.dat in DATA_DIR not found
ORA-02063: preceding 3 lines from ADHOC_POS15
ORA-06512: at line 13
成功！显然我无法查询某些表.所以我只是把那些桌子拿出来了.
Edit 5: Success! Apparently I cannot query certain tables. So I just took those tables out.
最终编码为:
declare
query varchar2(10000);
update_count integer := 0;
prjt_name varchar2(100) := '01213264B';
cursor my_cur is (select table_name from all_tab_columns@db2 where column_name = 'PROJECT_ID' and owner = 'SANDBOX' and table_name in ('X') );
tableName varchar2(100);
begin
open my_cur;
loop
fetch my_cur into tableName;
exit when my_cur%NOTFOUND;
update_count := 0;
execute immediate
'select count(project_id) as "CNT" from sandbox.' || tableName || '@db2 '
|| ' where project_id = (select project_id from sandbox.sb_project@db2 where project_name = ''' || prjt_name || ''' ) '
into update_count;
if update_count > 0 then
dbms_output.put_line (tableName);
end if;
end loop;
close my_cur;
end;
这并不能完全满足我的要求.它将结果发送到dbms_output.但这是一个开始！谢谢大家的帮助！
This doesn't do exactly what I wanted. It sends the results to dbms_output. But It is a start! Thanks everyone for you help!
推荐答案
DECLARE
update_count integer := 0;
prjt_name varchar2(100) := 'tttt';
mysql varchar2(100);
tablename varchar2(100);
cursor my_cur is
select 'DUAL'
from dual
where 'PROJECT_ID' = 'PROJECT_ID' and 'SANDBOX' = 'SANDBOX';
begin
open my_cur;
LOOP
FETCH my_cur into tablename;
EXIT WHEN my_cur%NOTFOUND;
update_count := 0;
mysql := 'select count(*) ' || ' from '
|| tablename
|| ' where ''TTTT'' = upper('''
|| prjt_name
|| ''')' ;
Execute immediate mysql INTO update_count;
dbms_output.put_line (mysql);
dbms_output.put_line (update_count);
END LOOP;
CLOSE my_cur;
end;
我尝试过像你这样的人.成功执行
I tried one like yours. This executes successfully
declare
query varchar2(10000);
update_count integer := 0;
prjt_name varchar2(100) := '01213264B';
cursor my_cur is
select table_name
from all_tab_columns@adhoc_pos15
where column_name = 'PROJECT_ID' and owner = 'SANDBOX';
tableName varchar2(100);
begin
open my_cur;
loop
fetch my_cur into tableName;
exit when my_cur%NOTFOUND;
update_count := 0;
query := 'select count(t.project_id) as ''CNT'' from sandbox.'
|| tableName
|| '@adhoc_pos15 t'
|| ' where t.project_id = (select project_id from sandbox.sb_project@adhoc_pos15 where project_name = ''' || prjt_name || ''') ' ;
execute immediate query into update_count;
dbms_output.put_line (query);
dbms_output.put_line (update_count);
end loop;
close my_cur;
end;
这篇关于Oracle PL/SQL:帮助解决"PLS-00103:遇到符号"LOOP"当期望以下之一时:的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
