由于公司项目上的问题,在数据量很大的时候,在SELECT 语句中包含了很多 function 致使查询进行了全表扫描,例如查询语句:select function1(col1) as a1,function2(col2) as a2,function3(col3) as a3,...... from table1 t1 where col5 = function(col5) 像这种SQL语句查询的过程中会变得巨慢,因为在function中又去其他表(比如字典表)中做了查询,一个只有10W记录的表,查询一个类似的SQL语句需要25~30秒的样子,原来的分页存储过程:
应该说这个分页存储过程还是比较快的,但因为业务逻辑中的SQL语句写的太复杂了,以致于就像我上面所说的SQL语句查询也会变得比较慢,为了解决这个问题,我对分页的存储过程又做了修改,主要思想是在最内层仅取出所要查询记录的rowid,然后再在外层对要取出的pagesize调记录调用那些sql方法,这样做效果肯定是提高了,但解析不同的SQL语句就成了一个比较复杂的问题,改动后存储过程仅仅解析SQL语句就需要花费500~700毫秒,但读出记录集确是快了不少,相反以前的存储过程解析SQL语句仅需要50毫秒,而返回出记录集却要20~30秒,相比之下改动后整体上还是提高了不少,下面请看存储过程,我个人觉得在取具体单条记录的效率上是还存在点问题,有时间我将会再改造改造。
可能以上存储过程看上去比较恐怖,但我确实做了 ,唯一觉得不理想的是觉得取单条记录做的不好,然后就是对 在ORACLE中做如此多的字符串查找和截取的效率感到不满意,不知道有没有什么方法可以提高一下,提出来大家探讨一个。
我的技术博客写的比较少,不知道这种文章能不能放到首页上,我只是想让更多人看到探讨一下,就在首页放半天,请DuDu谅解!
1
create or replace package body PK_Pager
2
as
3
procedure GetPager
4
(
5
p_PageSize int, --每页记录数
6
p_PageNo int, --当前页码,从 1 开始
7
p_SqlSelect varchar2, --查询语句,含排序部分
8
p_OutRecordCount out int,--返回总记录数
9
p_OutCursor out mytype
10
)
11
as
12
v_sql varchar2(3000);
13
v_count int;
14
v_heiRownum int;
15
v_lowRownum int;
16
begin
17
----取记录总数
18
v_sql := 'select count(*) from (' || p_SqlSelect || ')';
19
execute immediate v_sql into v_count;
20
p_OutRecordCount := v_count;
21
----执行分页查询
22
v_heiRownum := p_PageNo * p_PageSize;
23
v_lowRownum := v_heiRownum - p_PageSize + 1;
24![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
25
v_sql := 'SELECT *
26
FROM (
27
SELECT A.*, rownum rn
28
FROM ('|| p_SqlSelect ||') A
29
WHERE rownum <= '|| to_char(v_heiRownum) || '
30
) B
31
WHERE rn >= ' || to_char(v_lowRownum) ;
32
--注意对rownum别名的使用,第一次直接用rownum,第二次一定要用别名rn
33
34
OPEN p_OutCursor FOR v_sql;
35
end GetPager;
36![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
37
end PK_Pager;
38![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
6
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
8
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
9
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
10
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
11
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
12
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
13
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
14
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
15
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
16
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
17
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
18
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
19
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
20
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
21
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
22
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
23
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
24
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
25
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
26
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
27
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
28
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
29
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
30
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
31
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
32
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
33
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
34
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
35
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
36
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
37
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
38
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
应该说这个分页存储过程还是比较快的,但因为业务逻辑中的SQL语句写的太复杂了,以致于就像我上面所说的SQL语句查询也会变得比较慢,为了解决这个问题,我对分页的存储过程又做了修改,主要思想是在最内层仅取出所要查询记录的rowid,然后再在外层对要取出的pagesize调记录调用那些sql方法,这样做效果肯定是提高了,但解析不同的SQL语句就成了一个比较复杂的问题,改动后存储过程仅仅解析SQL语句就需要花费500~700毫秒,但读出记录集确是快了不少,相反以前的存储过程解析SQL语句仅需要50毫秒,而返回出记录集却要20~30秒,相比之下改动后整体上还是提高了不少,下面请看存储过程,我个人觉得在取具体单条记录的效率上是还存在点问题,有时间我将会再改造改造。
1
CREATE OR REPLACE Package Body PKAge_Pager_XxjV2 As
2![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
------------------------------------------------------------------------------------------------------------------------------------
4
---获得记录集
5
------------------------------------------------------------------------------------------------------------------------------------
6
Procedure GetRecords(p_PageSize Int, --每页记录数
7
p_PageIndex Int, --当前页码,从 1 开始
8
p_SqlSelect Varchar2, --查询语句,含排序部分
9
p_RecordIndex Int Default 0,--单条记录ROWNUM索引编号:如果值为0则返回分页的记录集,如果值不为0则返回具体的记录
10
p_OutRecordCount Out Int, --返回总记录数
11
p_OutPageCount Out Int, --返回总的页数
12
p_OutCursor Out mytype --返回的记录集游标
13
) As
14![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
15
v_sql Varchar2(8000);
16
v_count Number; v_lowRownum Number; v_heiRownum Number;
17
v_selectfields Varchar2(4000); --要查询的字段
18
v_tablenames Varchar2(1000); --多个表名称
19
v_wherecondition Varchar2(3000); --where条件子句
20
v_posfrom Number; --from的位置
21
v_posselect Number; --select的位置
22
v_poswhere Number; --where条件的位置
23
v_posorderby Number; --order by的位置
24
v_primaryTable Varchar2(1000); --表示记录中的主记录的表
25
v_tmpwhereint Number; --临时保存where的位置
26
v_tmpfromint Number; --临时保存from的位置
27
v_tmpint Number;
28
v_tmpstr Varchar2(8000);
29
v_flag Number;
30
v_tablecountsflag Number;
31
Begin
32
v_flag := 0; --默认状态
33
v_tablecountsflag :=0;
34
------------------------------------------------------------先处理相关变量
35
v_posfrom := instr(lower(p_SqlSelect), 'from ', 1,1); --查找第一个from的位置
36
v_selectfields := substr(p_SqlSelect,1,v_posfrom-1); --取出第一个from之前的select ######
37
--判断语句是否还有select子句
38
v_tmpfromint := instr(lower(v_selectfields),'from ',v_posfrom+5,1); --查找出第二个from的位置
39
If v_tmpfromint > 0 Then
40
--如果第二个from存在,需要判断是否第一个select和第一个from之间是否有select
41
v_posselect := instr(lower(substr(p_SqlSelect,1,v_posfrom)),'select ',1,2);
42
If v_posselect > 0 Then
43
--说明在查询的字段中有 select from 子语句,情况较复杂
44
--格式:S--(S--F)--
--F--
.
45
v_flag := 1;
46
Goto do_flag;
47
Else
48
--说明第二个from是 视图或者where条件中的从句,第一个from就是最外层的 from
49
--格式:S--F--(..F..)--
50
--v_selectfields := substr(p_SqlSelect,1,v_posfrom-1); --取出第一个from之前的select ######
51
--判断两个from之间是否还有where条件
52
v_poswhere := instr(lower(p_SqlSelect), 'where ', v_posfrom+5,1); --查找两个from之间的where,这也是第一个where
53
If v_poswhere < v_tmpfromint And v_poswhere > v_posfrom Then
54
--两个from之间有where ,说明第二个from是where条件中的 select from子句
55
--格式:S--F--W--(..F..)--
56
v_wherecondition := substr(p_SqlSelect,v_poswhere);
57
v_tablenames := substr(p_SqlSelect,v_posfrom+5,v_poswhere-(v_posfrom+5));
58
Goto do_flag;
59
Elsif v_poswhere > v_tmpfromint Then
60
--where的位置大于第二个from ,说明 from的视图或表是一个子查询
61
--格式:S--F--(S--F--W)![](https://www.cnblogs.com/Images/dot.gif)
![](https://www.cnblogs.com/Images/dot.gif)
62
--这里需要判断在第一个where之后是否还有where,
63
v_tmpwhereint := instr(lower(p_SqlSelect), 'where ', v_poswhere+6,1); --取出全局中第二个where的位置
64
If v_tmpwhereint > 0 Then
65
--现在的格式:S--F--S--F--W--..W..-- 情况交复杂
66
v_flag := 1;
67
Goto do_flag;
68
Else
69
--全局中仅有一个where,这里需要判断where是全局查询条件 还是 from的视图查询条件
70
--通过判断第二个from和第一个where之间是否有‘)’符号
71
v_tmpstr := substr(p_SqlSelect,v_tmpfromint+5,v_poswhere-(v_tmpfromint+5));
72
If instr(v_tmpstr,')',-1,1) > 0 Then
73
--说明where是全局查询条件
74
Goto do_sfw1;
75
Else
76
--说明where是from视图的查询条件
77
Goto do_orderby;
78
End If;
79
End If;
80
Else --没有查询到where条件的位置,说明一个where条件都没有
81
Goto do_orderby;
82
End If;
83
End If;
84
End If;
85
-------如果没有select from子句,全局找不到第二个 from -----------------------------------
86
<<do_sfw1>>
87
v_selectfields := substr(p_SqlSelect,1,v_posfrom-1); --取出第一个from之前的select ######
88
--如果没有select from子句,查找where的位置
89
v_poswhere := instr(lower(p_SqlSelect), 'where ', v_posfrom+4,1); --查找第一个where的位置
90
--判断语句中第一个where是否存在
91
If v_poswhere > 0 Then --存在
92
v_wherecondition := substr(p_SqlSelect,v_poswhere);
93
v_tablenames := substr(p_SqlSelect,v_posfrom+5,v_poswhere-(v_posfrom+5));
94
Goto do_flag;
95
End If;
96![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
97
<<do_orderby>>
98
--如果语句中没有where条件,先查询是否有order by 排序条件 从字符串最后一个字符查找
99
v_posorderby := instr(lower(p_SqlSelect), 'order by ', -1,1); --查找order by的位置
100
If v_posorderby >0 Then
101
--如果语句中有order by
102
v_wherecondition := substr(p_SqlSelect,v_posorderby);
103
v_tablenames := substr(p_SqlSelect,v_posfrom+5,v_posorderby-(v_posfrom+5));
104
Else
105
--如果语句中也没有order by
106
v_wherecondition := '';
107
v_tablenames := substr(p_SqlSelect,v_posfrom+5);
108
End If;
109
<<do_flag>>
110
If v_flag <= 0 Then
111
--这里要处理获得primarytable主视图表的名称
112
<<do_primarytable>>
113
v_tablenames := trim(v_tablenames);
114
v_tmpint := instr(v_tablenames,',',1,1);
115
If v_tmpint > 0 Then --判断是否是多个表的联合查询
116
v_primaryTable := substr(v_tablenames,1,v_tmpint-1);
117
v_tablecountsflag := 1; --标识符说明有多个表
118
Else
119
v_tmpint := instr(lower(v_tablenames),'join',1,1);
120
If v_tmpint > 0 Then
121
v_primaryTable := substr(v_tablenames,1,v_tmpint-1);
122
v_primaryTable := Replace(v_primaryTable,'out','');
123
v_primaryTable := Replace(v_primaryTable,'left','');
124
v_primaryTable := Replace(v_primaryTable,'right','');
125
v_primaryTable := Replace(v_primaryTable,'inner','');
126
Else
127
v_primaryTable := v_tablenames;
128
End If;
129
End If;
130
v_primaryTable := lower(trim(v_primaryTable));
131
v_primaryTable := replace(replace(v_primaryTable,chr(13)),chr(10)); --去除换行符
132
--去除多个空格的情况
133
v_primaryTable := Replace(v_primaryTable,' ',' '); --去除3个连续空格
134
v_primaryTable := Replace(v_primaryTable,' ',' '); --去除2个连续空格
135
v_tmpint := instr(v_primaryTable,' ',1,1);
136
If v_tmpint > 0 Then
137
v_primaryTable := trim(substr(v_primaryTable,v_tmpint+1));
138
Else --当没有设置表的别名, 自己给定别名
139
v_tmpstr := v_primaryTable || ' xxjmytb';
140
v_tablenames := Replace(lower(v_tablenames),v_primaryTable,v_tmpstr);
141
--先将WHERE和SELECT变量中的'转义
142
--v_wherecondition := Replace(v_wherecondition,chr(39),'''');
143
--v_selectfields := Replace(v_selectfields,chr(39),'''');
144
--还要替换掉where条件中的表名称的
145
v_wherecondition := lower(v_wherecondition);
146
v_wherecondition := Replace(v_wherecondition,v_primaryTable||'.','xxjmytb.');
147
--还有替换掉selectfields中的表名称
148
v_selectfields :=lower(v_selectfields);
149
v_selectfields := Replace(v_selectfields,v_primaryTable||'.','xxjmytb.');
150
v_tmpint := instr(v_primarytable,'.',-1,1); --查找带点的表名
151
If v_tmpint > 0 Then
152
v_tmpstr := trim(substr(v_primarytable,v_tmpint+1));
153
v_tmpstr := v_tmpstr||'.';
154
v_wherecondition := Replace(v_wherecondition,v_tmpstr,'xxjmytb.');
155
v_selectfields := Replace(v_selectfields,v_tmpstr,'xxjmytb.'); --替换掉selectfields中的表名称
156
End If;
157
v_primarytable := 'xxjmytb';
158
End If;
159
v_tablenames := ' ' || v_tablenames || ' ';
160
---取分页总数-----------------------目的是为了在统计的时候将字段中的相关方法出去
161
v_sql := 'select count(*) from ' || v_tablenames || v_wherecondition;
162
Execute Immediate v_sql Into v_count; p_OutRecordCount := v_count; --将总记录数赋值给返回的参数
163
p_OutPageCount := ceil(v_count/p_PageSize); --将总页数赋值给返回的参数
164
v_heiRownum := p_PageIndex * p_PageSize; v_lowRownum := v_heiRownum - p_PageSize + 1; --计算开始和起始位置
165
v_sql := 'select '|| v_primaryTable ||'.rowid as xxjid,rownum as rownn_xxj from ' || v_tablenames || v_wherecondition;
166
v_sql := 'select xxjid,rownum as rownn_xxj from (' || v_sql || ') where rownn_xxj <=' || to_char(v_heiRownum);
167
v_sql := 'select xxjid from ('|| v_sql ||') where rownn_xxj >= ' || to_char(v_lowRownum);
168
If v_tablecountsflag > 0 Then
169
v_sql := v_selectfields || ',rownum as rownn_xxj from ' || v_tablenames || ',('|| v_sql ||') xxjlast where xxjlast.xxjid='|| v_primaryTable || '.rowid and '|| substr(trim(v_wherecondition),6);
170
Else
171
v_sql := v_selectfields || ',rownum as rownn_xxj from ' || v_tablenames || ',('|| v_sql ||') xxjlast where xxjlast.xxjid='|| v_primaryTable || '.rowid';
172
End If;
173
Else
174
v_sql := 'select count(*) from (' || p_SqlSelect || ')';
175
execute immediate v_sql into v_count;
176
p_OutRecordCount := v_count;
177
p_OutPageCount := ceil(v_count/p_PageSize); --将总页数赋值给返回的参数
178
v_heiRownum := p_PageIndex * p_PageSize; v_lowRownum := v_heiRownum - p_PageSize + 1;
179
v_sql := 'SELECT * FROM (SELECT A.*, rownum rownn_xxj FROM ('|| p_SqlSelect ||') A WHERE rownum <= '|| to_char(v_heiRownum) || ' ) B WHERE rownn_xxj >= ' || to_char(v_lowRownum) ;
180
End If;
181
--#############如果是具体某条记录的详细信息################################################
182
If p_RecordIndex > 0 Then
183
v_sql := 'select * from ('|| v_sql ||') where rownn_xxj='||to_char(p_RecordIndex);
184
End If;
185
--#########################################################################################
186
Open p_OutCursor For v_sql;
187![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
188
End GetRecords;
189![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
190
---------------------------------------------------------------------------------------------------------------------------
191![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
192
End PKAge_Pager_XxjV2;
193![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
6
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
8
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
9
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
10
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
11
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
12
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
13
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
14
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
15
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
16
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
17
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
18
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
19
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
20
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
21
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
22
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
23
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
24
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
25
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
26
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
27
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
28
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
29
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
30
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
31
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
32
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
33
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
34
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
35
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
36
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
37
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
38
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
39
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
40
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
41
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
42
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
43
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
44
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/dot.gif)
![](https://www.cnblogs.com/Images/dot.gif)
45
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
46
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
47
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
48
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
49
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
50
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
51
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
52
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
53
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
54
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
55
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
56
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
57
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
58
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
59
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
60
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
61
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/dot.gif)
![](https://www.cnblogs.com/Images/dot.gif)
62
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
63
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
64
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
65
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
66
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
67
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
68
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
69
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
70
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
71
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
72
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
73
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
74
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
75
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
76
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
77
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
78
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
79
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
80
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
81
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
82
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
83
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
84
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
85
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
86
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
87
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
88
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
89
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
90
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
91
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
92
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
93
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
94
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
95
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
96
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
97
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
98
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
99
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
100
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
101
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
102
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
103
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
104
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
105
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
106
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
107
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
108
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
109
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
110
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
111
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
112
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
113
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
114
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
115
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
116
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
117
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
118
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
119
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
120
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
121
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
122
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
123
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
124
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
125
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
126
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
127
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
128
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
129
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
130
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
131
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
132
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
133
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
134
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
135
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
136
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
137
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
138
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
139
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
140
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
141
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
142
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
143
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
144
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
145
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
146
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
147
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
148
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
149
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
150
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
151
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
152
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
153
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
154
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
155
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
156
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
157
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
158
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
159
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
160
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
161
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
162
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
163
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
164
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
165
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
166
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
167
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
168
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
169
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
170
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
171
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
172
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
173
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
174
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
175
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
176
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
177
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
178
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
179
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
180
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
181
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
182
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
183
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
184
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
185
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
186
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
187
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
188
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
189
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
190
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
191
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
192
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
193
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
可能以上存储过程看上去比较恐怖,但我确实做了 ,唯一觉得不理想的是觉得取单条记录做的不好,然后就是对 在ORACLE中做如此多的字符串查找和截取的效率感到不满意,不知道有没有什么方法可以提高一下,提出来大家探讨一个。
我的技术博客写的比较少,不知道这种文章能不能放到首页上,我只是想让更多人看到探讨一下,就在首页放半天,请DuDu谅解!