Toad 中的无效 sql 语句 - oracle db [英] invalid sql statement in Toad - oracle db

问题描述

下面的查询得到了我

无效的sql语句

在蟾蜍中

IF EXISTS (SELECT T_BASIS_ACCESS_ID FROM ECKERNEL_MCA.T_BASIS_ACCESS WHERE ROLE_ID LIKE 'MCA.GFS.LEAD') 
    BEGIN
       SELECT OBJECT_ID, NAME  FROM ECKERNEL_MCA.OV_AREA WHERE END_DATE IS NULL AND OBJECT_ID  IN
       (SELECT DISTINCT REPLACE(REPLACE(REPLACE(ATTRIBUTE_TEXT, '(', '' ),')',''), '''', '') 
       FROM ECKERNEL_MCA.T_BASIS_OBJECT_PARTITION WHERE T_BASIS_ACCESS_ID IN 
       (SELECT T_BASIS_ACCESS_ID FROM ECKERNEL_MCA.T_BASIS_ACCESS WHERE ROLE_ID LIKE 'MCA.GFS.LEAD') )
    END
ELSE
    BEGIN
       SELECT OBJECT_ID, NAME  FROM ECKERNEL_MCA.OV_AREA WHERE END_DATE IS NULL
    END    

推荐答案

据我所知，这段代码确实无效.接下来的可能会编译.请注意:

This code really is invalid, as far as I can tell. The one that follows might compile. Note that:

• 我创建了用于保存 SELECT 语句结果的局部变量.你在 PL/SQL 中需要它们
• 我尝试向您展示应该处理异常.IF-THEN-ELSE"没有多大用处，因为 - 如果第一个 SELECT(我将其命名为A")失败，它不会返回 NULL 而是引发 NO_DATA_FOUND 异常.我不知道你的表或你存储在那里的数据，所以 - 也许这段代码没有异常，我不知道.
• 我无法测试它，因为我没有你的桌子，也不想自己创建它们.

• I created local variables which will hold the result of SELECT statements. You need them in PL/SQL
• I tried to show you that exceptions should be handled. There's no much use in "IF-THEN-ELSE" because - if the first SELECT (I named it "A") fails, it won't return NULL but raise NO_DATA_FOUND exception. I don't know your tables nor data you store in there, so - maybe this code is exception-free, I can't tell.
• I couldn't test it as I don't have your tables, and didn't feel like creating them myself.

declare
  l_t_basis_access_id   eckernel_mca.t_basis_access.t_basis_access_id%type;
  l_object_id           eckernel_mca.ov_area.object_id%type;
  l_name                eckernel_mca.ov_area.name%type;
begin
  -- SELECT "A"
  select t_basis_access_id 
    into l_t_basis_access_id
    from eckernel_mca.t_basis_access
    where role_id like 'MCA.GFS.LEAD';

  -- SELECT "A" returned a single value - proceed with the next SELECT "B"

  -- SELECT "B"
  select object_id, name 
  into l_object_id,l_name
  from eckernel_mca.ov_area
  where end_date is null
    and object_id in (select distinct replace(replace(replace(attribute_text,'(',''),')',''),'''','')
                      from eckernel_mca.t_basis_object_partition
                      where t_basis_access_id in (select t_basis_access_id
                                                  from eckernel_mca.t_basis_access
                                                  where role_id like 'MCA.GFS.LEAD'
                                                 )
                     );
exception
  when no_data_found then
    -- SELECT "A" didn't return anything and raised no_data_found; but, SELECT "B" might 
    -- raise it as well
    select object_id, name 
    into l_object_id,l_name
    from eckernel_mca.ov_area
    where end_date is null;

  when too_many_rows then
    -- Any SELECT involved in this code could return it, so - handle it, if necessary
end;

这篇关于Toad 中的无效 sql 语句 - oracle db的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！