Warning: Function created with compilation errors.

最新推荐文章于 2022-03-25 13:22:04 发布

转载最新推荐文章于 2022-03-25 13:22:04 发布 · 759 阅读

本文介绍了一个SQL函数remove_constants的创建过程，该函数用于从输入的SQL字符串中移除数值常量，同时保留字符串内的单引号。通过两段代码对比展示了如何修正语法错误，并成功创建函数。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SQL> create or replace function
  2     remove_constants(p_query in varchar2) return varchar2
  3  as
  4     l_query         long;
  5     l_char          varchar2(1);
  6     l_in_quotes boolean default FLASE;
  7  begin
  8     for i in 1..length(p_query) 
  9     loop
 10             l_char :=substr(p_query,i,1);
 11             if(l_char='''' and l_in_quotes)
 12             then
 13                     l_in_quotes := FALSE;
 14             elsif(l_char='''' and not l_in_quotes)
 15             then
 16                     l_in_quotes := TRUE;
 17                     l_query     := l_query || '''#';
 18             end if;
 19             if(not l_in_quotes)
 20             then
 21                     l_query := l_query || l_char;
 22             end if;
 23     end loop;
 24  
 25     l_query := translate(l_query,'0123456789','@@@@@@@@@@');
 26     for i in 0..8 
 27     loop
 28             l_query := replace(l_query,lpad('@',10-i,'@'),'@');
 29             l_query := replace(l_query, lpad(' ',10-i,' '),' ');
 30     end loop;
 31     return upper(l_query);
 32  end;
 33  /

Warning: Function created with compilation errors.

SQL>

查看并解决：

SQL> show errors
Errors for FUNCTION REMOVE_CONSTANTS:

LINE/COL ERROR
-------- -----------------------------------------------------------------
6/14     PL/SQL: Item ignored
6/30     PLS-00201: identifier 'FLASE' must be declared  #这里看出写错单词了
11/3     PL/SQL: Statement ignored
11/22    PLS-00320: the declaration of the type of this expression is
         incomplete or malformed

19/3     PL/SQL: Statement ignored
19/10    PLS-00320: the declaration of the type of this expression is
         incomplete or malformed

SQL> create or replace function
  2     remove_constants(p_query in varchar2) return varchar2
  3  as
  4     l_query         long;
  5     l_char          varchar2(1);
  6     l_in_quotes boolean default FALSE;
  7  begin
  8     for i in 1..length(p_query) 
  9     loop
 10             l_char :=substr(p_query,i,1);
 11             if(l_char='''' and l_in_quotes)
 12             then
 13                     l_in_quotes := FALSE;
 14             elsif(l_char='''' and not l_in_quotes)
 15             then
 16                     l_in_quotes := TRUE;
 17                     l_query     := l_query || '''#';
 18             end if;
 19             if(not l_in_quotes)
 20             then
 21                     l_query := l_query || l_char;
 22             end if;
 23     end loop;
 24  
 25     l_query := translate(l_query,'0123456789','@@@@@@@@@@');
 26     for i in 0..8 
 27     loop
 28             l_query := replace(l_query,lpad('@',10-i,'@'),'@');
 29             l_query := replace(l_query, lpad(' ',10-i,' '),' ');
 30     end loop;
 31     return upper(l_query);
 32  end;
 33  /

Function created.

SQL>