您现在的位置是：主页 > news > 沈阳做网站软件/点击seo软件
沈阳做网站软件/点击seo软件
admin2025/5/2 12:27:09【news】
简介沈阳做网站软件,点击seo软件,ui设计零基础到精通自学,html个人网站怎么做当前位置:我的异常网 SQL Oracle 定位没使用绑定变量的SQLOracle 定位没使用绑定变量的SQLwww.myexceptions.net 网友分享于：2013-09-16 浏览：13次Oracle 定位没有使用绑定变量的SQL[ora10ghzmc ~]$ export ORACLE_SIDdrb[ora10ghzmc ~]$ sqlplus &qu…
当前位置:我的异常网» SQL » Oracle 定位没使用绑定变量的SQL
Oracle 定位没使用绑定变量的SQL
www.myexceptions.net 网友分享于：2013-09-16 浏览：13次
Oracle 定位没有使用绑定变量的SQL
[ora10g@hzmc ~]$ export ORACLE_SID=drb
[ora10g@hzmc ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.3.0 - Production on Sat Sep 25 12:27:49 2010
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> create table t1 as select sql_text from v$sqlarea;
Table created.
SQL> alter table t1 add sql_text_wo_constants varchar2(1000);
Table altered.
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 ) then
20 l_query := l_query || l_char;
21 end if;
22 end loop;
23 l_query := translate( l_query, '0123456789', '@@@@@@@@@@' );
24 for i in 0 .. 8 loop
25 l_query := replace( l_query, lpad('@',10-i,'@'), '@' );
26 l_query := replace( l_query, lpad(' ',10-i,' '), ' ' );
27 end loop;
28 return upper(l_query);
29 end;
30 /
Function created.
SQL> update t1 set sql_text_wo_constants = remove_constants(sql_text);
4569 rows updated.
SQL> commit;
Commit complete.
SQL> select sql_text_wo_constants, count(*) from t1
2 group by sql_text_wo_constants having count(*) > 100 order by 2
3 /
SQL_TEXT_WO_CONSTANTS
--------------------------------------------
COUNT(*)
----------
DELETE FROM MC$DR_BACKUPPIECE_DATAFILE WHERE BSET_KEY=@
1385
DELETE FROM MC$DR_BACKUPPIECE WHERE BSET_KEY=@
1385
DELETE FROM MC$DR_BACKUPSET WHERE BS_ID=@ AND SUB_ID=@ AND DB_KEY=@ AND DB_ID=@
AND BSET_KEY=@
1386
SQL_TEXT_WO_CONSTANTS
--------------------------------------------
COUNT(*)
文章评论
