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>