Oracle PL/SQL:帮助解决"PLS

本文介绍了Oracle PL/SQL:帮助解决"PLS-00103:遇到符号"LOOP"当期望以下之一时:的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

请参阅第二次编辑.新的编码得到不同的错误.

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_t​​able中删除.

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;