oracle存储过程转换为mysql存储过程实例总结

最近在接触数据库的有关知识,因为水平有限,对数据库方面的一些知识缺乏了解,这次遇见的主要是 数据库的存储过程,根据公司项目需求,将oracle的存储过程切换为mysql的存储过程,首先oracle的存储过程与mysql的存储过程差别是很大的(语法差别很大),通过好多天的网上查询资料,与同事们的交流,简单的实现了基本的转换,在这里肯定还有一些错误的地方,也欢迎各位大牛指出,让我更加深入的了解 数据库的存储过程,有疑问的大家也可以一起交流进步。

下面就进入这次的话题:oracle 存储过程 转换为mysql存储过程 实例总结

首先我门先看一下我最近大致转换的存储过程的一个例子代码,首先是原始的 oracle的存储过程:

 

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
CREATE OR REPLACE PROCEDURE xp_stat_BigDiseaseAnalyse(
                               v_beginDate varchar2, --统计开始日期,格式:yyyyMMdd
                               v_endDate   varchar2, --统计结束日期, 格式:yyyyMMdd
                               v_orgCode   varchar2, --检查医疗机构
                               v_dataCur   out PK_base.cur, --返回结果集
                               v_total     out number, --返回查询记录总数
                               v_msg       out varchar2 --返回查询记录总数
                               ) is
    v_sql varchar2(5000);
begin
  v_sql := 'select * from ( select max(ehr.forg_code) forg_code , ' '冠心病' ' fdiag_name ,count(a.fehrid) FDiseaseCount,sum(famount)
 
famount,sum(famount_insurance) famount_insurance,sum(famount_self) famount_self
  from tbl_ehr_comm_disease a 
  join  tbl_ehr_archives ehr on ehr.fid=a.fehrid
  join tbl_ehr_zy_payway pay on pay.fehrid=a.fehrid
  where ehr.fdata_type=' '3001' '
  and a.fdiag_name like ' '%冠心病%' '
  and ehr.fehr_date >= ' '20160301' '
and ehr.fehr_date <= ' '' || v_endDate || '' '  ' ;
if v_orgCode is not null then
v_sql := v_sql || ' and ehr.forg_code =' '' || v_orgCode || '' '' ;
end if;
v_sql := v_sql || ' union select max(ehr.forg_code) forg_code,  ' '椎基底供血不足' ' fdiag_name,count(a.fehrid)
 
FDiseaseCount,sum(famount) famount,sum(famount_insurance) famount_insurance,sum(famount_self) famount_self
  from tbl_ehr_comm_disease a 
  join  tbl_ehr_archives ehr on ehr.fid=a.fehrid
  join tbl_ehr_zy_payway pay on pay.fehrid=a.fehrid
where  ehr.fdata_type=' '3001' ' and a.fdiag_name like ' '%椎基底供血不足%' '
and ehr.fehr_date  >= ' '20160301' '
and ehr.fehr_date <= ' '' || v_endDate || '' '  ' ;
if v_orgCode is not null then
v_sql := v_sql || ' and ehr.forg_code =' '' || v_orgCode || '' '' ;
end if;
v_sql := v_sql || ' union select max(ehr.forg_code) forg_code,  ' '糖尿病' ' fdiag_name,count(a.fehrid) FDiseaseCount,sum
 
(famount) famount,sum(famount_insurance) famount_insurance,sum(famount_self) famount_self
  from tbl_ehr_comm_disease a 
  join  tbl_ehr_archives ehr on ehr.fid=a.fehrid
  join tbl_ehr_zy_payway pay on pay.fehrid=a.fehrid
where ehr.fdata_type=' '3001' ' and a.fdiag_name like ' '%糖尿病%' '
and ehr.fehr_date  >= ' '20160301' '
and ehr.fehr_date <= ' '' || v_endDate || '' '  ' ;
if v_orgCode is not null then
v_sql := v_sql || ' and ehr.forg_code =' '' || v_orgCode || '' '' ;
end if;
v_sql := v_sql || ' union select max(ehr.forg_code) forg_code,  ' '脑出血' ' fdiag_name,count(a.fehrid) FDiseaseCount,sum
 
(famount) famount,sum(famount_insurance) famount_insurance,sum(famount_self) famount_self
  from tbl_ehr_comm_disease a 
  join  tbl_ehr_archives ehr on ehr.fid=a.fehrid
  join tbl_ehr_zy_payway pay on pay.fehrid=a.fehrid
where ehr.fdata_type=' '3001' ' and a.fdiag_name like ' '%脑出血%' '
and ehr.fehr_date >= ' '20160301' '
and ehr.fehr_date <= ' '' || v_endDate || '' '  ' ;
if v_orgCode is not null then
v_sql := v_sql || ' and ehr.forg_code =' '' || v_orgCode || '' '' ;
end if;
v_sql := v_sql || ' union select max(ehr.forg_code) forg_code,  ' '无指征剖腹产分娩' ' fdiag_name,count(a.fehrid)
 
FDiseaseCount,sum(famount) famount,sum(famount_insurance) famount_insurance,sum(famount_self) famount_self
  from tbl_ehr_comm_disease a 
  join  tbl_ehr_archives ehr on ehr.fid=a.fehrid
  join tbl_ehr_zy_payway pay on pay.fehrid=a.fehrid
where ehr.fdata_type=' '3001' ' and a.fdiag_name like ' '%无指征剖腹产分娩%' '
and ehr.fehr_date >= ' '20160301' '
and ehr.fehr_date <= ' '' || v_endDate || '' '  ' ;
if v_orgCode is not null then
v_sql := v_sql || ' and ehr.forg_code =' '' || v_orgCode || '' '' ;
end if;
v_sql := v_sql || ' union select max(ehr.forg_code) forg_code,  ' '肺炎' ' fdiag_name,count(a.fehrid) FDiseaseCount,sum
 
(famount) famount,sum(famount_insurance) famount_insurance,sum(famount_self) famount_self
  from tbl_ehr_comm_disease a 
  join  tbl_ehr_archives ehr on ehr.fid=a.fehrid
  join tbl_ehr_zy_payway pay on pay.fehrid=a.fehrid
where ehr.fdata_type=' '3001' ' and a.fdiag_name like ' '%肺炎%' '
and ehr.fehr_date >= ' '20160301' '
and ehr.fehr_date <= ' '' || v_endDate || '' '  ' ;
if v_orgCode is not null then
v_sql := v_sql || ' and ehr.forg_code =' '' || v_orgCode || '' '' ;
end if;
v_sql := v_sql || ' union select max(ehr.forg_code) forg_code,  ' '肺恶性肿瘤' ' fdiag_name,count(a.fehrid)
 
FDiseaseCount,sum(famount) famount,sum(famount_insurance) famount_insurance,sum(famount_self) famount_self
  from tbl_ehr_comm_disease a 
  join  tbl_ehr_archives ehr on ehr.fid=a.fehrid
  join tbl_ehr_zy_payway pay on pay.fehrid=a.fehrid
where ehr.fdata_type=' '3001' ' and a.fdiag_name like ' '%肺恶性肿瘤%' '
and ehr.fehr_date >= ' '20160301' '
and ehr.fehr_date <= ' '' || v_endDate || '' '  ' ;
if v_orgCode is not null then
v_sql := v_sql || ' and ehr.forg_code =' '' || v_orgCode || '' '' ;
end if;
v_sql := v_sql || ' union select max(ehr.forg_code) forg_code,  ' '高血压' ' fdiag_name,count(a.fehrid) FDiseaseCount,sum
 
(famount) famount,sum(famount_insurance) famount_insurance,sum(famount_self) famount_self
  from tbl_ehr_comm_disease a 
  join  tbl_ehr_archives ehr on ehr.fid=a.fehrid
  join tbl_ehr_zy_payway pay on pay.fehrid=a.fehrid
where ehr.fdata_type=' '3001' ' and a.fdiag_name like ' '%高血压%' '
and ehr.fehr_date >= ' '20160301' '
and ehr.fehr_date <= ' '' || v_endDate || '' '  ' ;
if v_orgCode is not null then
v_sql := v_sql || ' and ehr.forg_code =' '' || v_orgCode || '' '' ;
end if;
v_sql := v_sql || ' union select max(ehr.forg_code) forg_code,  ' '乳腺恶性肿瘤' ' fdiag_name,count(a.fehrid)
 
FDiseaseCount,sum(famount) famount,sum(famount_insurance) famount_insurance,sum(famount_self) famount_self
  from tbl_ehr_comm_disease a 
  join  tbl_ehr_archives ehr on ehr.fid=a.fehrid
  join tbl_ehr_zy_payway pay on pay.fehrid=a.fehrid
where ehr.fdata_type=' '3001' ' and a.fdiag_name like ' '%乳腺恶性肿瘤%' '
and ehr.fehr_date >= ' '20160301' '
and ehr.fehr_date <= ' '' || v_endDate || '' '  ' ;
if v_orgCode is not null then
v_sql := v_sql || ' and ehr.forg_code =' '' || v_orgCode || '' ' ) tt where tt.FDiseaseCount>0 order by tt.FDiseaseCount desc' ;
end if;
open v_dataCur for v_sql;
   exception
     when others then
       pk_base.xp_saveLog(sqlcode,
                          sqlerrm,
                          'pk_stat.xp_stat_BigDiseaseAnalyse' ,
                          '大病补偿统计出错' );
       open v_dataCur for
       select * from dual where 1 = 2;
end ;

 

下面是 经过大致转换的的mysql 存储过程 :

 

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
DROP PROCEDURE IF EXISTS xp_stat_BigDiseaseAnalyse;
CREATE PROCEDURE xp_stat_BigDiseaseAnalyse(
                               v_beginDate varchar (100), -- 统计开始日期,格式:yyyyMMdd
                               v_endDate   varchar (100), -- 统计结束日期, 格式:yyyyMMdd
                               v_orgCode   varchar (50), -- 检查医疗机构
                               -- v_dataCur    PK_base.cur, --返回结果集
                               out v_total     int , -- 返回查询记录总数
                               out v_msg       varchar (100) -- 返回查询记录总数
                               )
begin
  DECLARE    v_sql varchar (5000);
  DECLARE iPos   int ;
  DECLARE iCount int ;
  DECLARE v_sqltmp varchar (420);
 
DECLARE CONTINUE HANDLER FOR  SQLEXCEPTION
    begin
    CALL xp_saveLog(@sqlcode,@sqlerrm, 'pk_him.xp_stat_BigDiseaseAnalyse' , '大病补偿统计出错' );
    
    COMMIT ;
    end ;
-- =================================================== 异常处理结束
  
 
SET v_sql = ' select * from (select max(ehr.forg_code) forg_code , ' '冠心病' ' fdiag_name
 
,count(a.fehrid) FDiseaseCount,SUM(famount)
famount,SUM(famount_insurance) famount_insurance,SUM(famount_self) famount_self
  from tbl_ehr_comm_disease a 
  join  tbl_ehr_archives ehr on ehr.fid=a.fehrid
  join tbl_ehr_zy_payway pay on pay.fehrid=a.fehrid
  where ehr.fdata_type=' '3001' '
  and a.fdiag_name like ' '%冠心病%' '
  and ehr.fehr_date >=  ' '' ;
 
SET v_sql = CONCAT(v_sql,v_beginDate, ' ' ' and ehr.fehr_date <= ' '' ,v_endDate, '' '' );
 
if (v_orgCode is not null and v_orgCode != '' ) then
-- SET v_sql = v_sql || ' and ehr.forg_code =''' || v_orgCode || '''';
SET v_sql = CONCAT(v_sql, ' and ehr.forg_code = ' '' ,v_orgCode, '' '' );
end if;
 
 
-- =================================================脑出血
 
-- =======================================================
SET v_sqltmp = ' union select max(ehr.forg_code) forg_code,  ' '脑出血' ' fdiag_name,count
 
(a.fehrid) FDiseaseCount,
sum(famount) famount,sum(famount_insurance) famount_insurance,sum(famount_self) famount_self
  from tbl_ehr_comm_disease a 
  join  tbl_ehr_archives ehr on ehr.fid=a.fehrid
  join tbl_ehr_zy_payway pay on pay.fehrid=a.fehrid
where ehr.fdata_type=' '3001' ' and a.fdiag_name like ' '%脑出血%' '
  and ehr.fehr_date >=  ' '' ;
 
 
SET v_sql = CONCAT(v_sql,v_sqltmp,v_beginDate, ' ' ' and ehr.fehr_date <= ' '' ,v_endDate, '' '' );
 
if (v_orgCode is not null and v_orgCode != '' ) then
-- SET v_sql = v_sql || ' and ehr.forg_code =''' || v_orgCode || '''';
SET v_sql = CONCAT(v_sql, ' and ehr.forg_code = ' '' ,v_orgCode, '' '' );
end if;
 
-- ==========================================椎基底供血不足
 
-- ==============================================================
SET v_sqltmp = ' union select max(ehr.forg_code) forg_code,  ' '椎基底供血不足' '
 
fdiag_name,count(a.fehrid)FDiseaseCount,
sum(famount) famount,sum(famount_insurance) famount_insurance,sum(famount_self) famount_self
  from tbl_ehr_comm_disease a 
  join  tbl_ehr_archives ehr on ehr.fid=a.fehrid
  join tbl_ehr_zy_payway pay on pay.fehrid=a.fehrid
where  ehr.fdata_type=' '3001' ' and a.fdiag_name like ' '%椎基底供血不足%' '
  and ehr.fehr_date >=  ' '' ;
 
 
SET v_sql = CONCAT(v_sql,v_sqltmp,v_beginDate, ' ' ' and ehr.fehr_date <= ' '' ,v_endDate, '' '' );
 
if (v_orgCode is not null and v_orgCode != '' ) then
-- SET v_sql = v_sql || ' and ehr.forg_code =''' || v_orgCode || '''';
SET v_sql = CONCAT(v_sql, ' and ehr.forg_code = ' '' ,v_orgCode, '' '' );
end if;
 
 
-- ========================================糖尿病
 
-- ================================================================
SET v_sqltmp = ' union select max(ehr.forg_code) forg_code,  ' '糖尿病' ' fdiag_name,count
 
(a.fehrid) FDiseaseCount,
sum(famount) famount,sum(famount_insurance) famount_insurance,sum(famount_self) famount_self
  from tbl_ehr_comm_disease a 
  join  tbl_ehr_archives ehr on ehr.fid=a.fehrid
  join tbl_ehr_zy_payway pay on pay.fehrid=a.fehrid
where ehr.fdata_type=' '3001' ' and a.fdiag_name like ' '%糖尿病%' '
  and ehr.fehr_date >=  ' '' ;
 
 
SET v_sql = CONCAT(v_sql,v_sqltmp,v_beginDate, ' ' ' and ehr.fehr_date <= ' '' ,v_endDate, '' '' );
 
if (v_orgCode is not null and v_orgCode != '' ) then
-- SET v_sql = v_sql || ' and ehr.forg_code =''' || v_orgCode || '''';
SET v_sql = CONCAT(v_sql, ' and ehr.forg_code = ' '' ,v_orgCode, '' '' );
end if;
 
 
--
 
--
 
-- ============
SET v_sqltmp = ' union select max(ehr.forg_code) forg_code,  ' '无指征剖腹产分娩' '
 
fdiag_name,count(a.fehrid)FDiseaseCount,
sum(famount) famount,sum(famount_insurance) famount_insurance,sum(famount_self) famount_self
  from tbl_ehr_comm_disease a 
  join  tbl_ehr_archives ehr on ehr.fid=a.fehrid
  join tbl_ehr_zy_payway pay on pay.fehrid=a.fehrid
where ehr.fdata_type=' '3001' ' and a.fdiag_name like ' '%无指征剖腹产分娩%' '
  and ehr.fehr_date >=  ' '' ;
 
 
SET v_sql = CONCAT(v_sql,v_sqltmp,v_beginDate, ' ' ' and ehr.fehr_date <= ' '' ,v_endDate, '' '' );
 
if (v_orgCode is not null and v_orgCode != '' ) then
-- SET v_sql = v_sql || ' and ehr.forg_code =''' || v_orgCode || '''';
SET v_sql = CONCAT(v_sql, ' and ehr.forg_code = ' '' ,v_orgCode, '' '' );
end if;
 
 
-- =======================================================肺炎
 
-- =================================================
SET v_sqltmp = ' union select max(ehr.forg_code) forg_code,  ' '肺炎' ' fdiag_name,count
 
(a.fehrid) FDiseaseCount,
sum(famount) famount,sum(famount_insurance) famount_insurance,sum(famount_self) famount_self
  from tbl_ehr_comm_disease a 
  join  tbl_ehr_archives ehr on ehr.fid=a.fehrid
  join tbl_ehr_zy_payway pay on pay.fehrid=a.fehrid
where ehr.fdata_type=' '3001' ' and a.fdiag_name like ' '%肺炎%' '
  and ehr.fehr_date >=  ' '' ;
 
 
SET v_sql = CONCAT(v_sql,v_sqltmp,v_beginDate, ' ' ' and ehr.fehr_date <= ' '' ,v_endDate, '' '' );
 
if (v_orgCode is not null and v_orgCode != '' ) then
-- SET v_sql = v_sql || ' and ehr.forg_code =''' || v_orgCode || '''';
SET v_sql = CONCAT(v_sql, ' and ehr.forg_code = ' '' ,v_orgCode, '' '' );
end if;
 
 
-- ===============================================肺恶性肿瘤
 
-- =========================================================
SET v_sqltmp = ' union select max(ehr.forg_code) forg_code,  ' '肺恶性肿瘤' '
 
fdiag_name,count(a.fehrid) FDiseaseCount,
sum(famount) famount,sum(famount_insurance) famount_insurance,sum(famount_self) famount_self
  from tbl_ehr_comm_disease a 
  join  tbl_ehr_archives ehr on ehr.fid=a.fehrid
  join tbl_ehr_zy_payway pay on pay.fehrid=a.fehrid
where ehr.fdata_type=' '3001' ' and a.fdiag_name like ' '%肺恶性肿瘤%' '
  and ehr.fehr_date >=  ' '' ;
 
 
SET v_sql = CONCAT(v_sql,v_sqltmp,v_beginDate, ' ' ' and ehr.fehr_date <= ' '' ,v_endDate, '' '' );
 
if (v_orgCode is not null and v_orgCode != '' ) then
-- SET v_sql = v_sql || ' and ehr.forg_code =''' || v_orgCode || '''';
SET v_sql = CONCAT(v_sql, ' and ehr.forg_code = ' '' ,v_orgCode, '' '' );
end if;
 
-- ================================================高血压
 
-- ========================================================
SET v_sqltmp = ' union select max(ehr.forg_code) forg_code,  ' '高血压' ' fdiag_name,count
 
(a.fehrid) FDiseaseCount,
sum(famount) famount,sum(famount_insurance) famount_insurance,sum(famount_self) famount_self
  from tbl_ehr_comm_disease a 
  join  tbl_ehr_archives ehr on ehr.fid=a.fehrid
  join tbl_ehr_zy_payway pay on pay.fehrid=a.fehrid
where ehr.fdata_type=' '3001' ' and a.fdiag_name like ' '%高血压%' '
  and ehr.fehr_date >=  ' '' ;
 
 
SET v_sql = CONCAT(v_sql,v_sqltmp,v_beginDate, ' ' ' and ehr.fehr_date <= ' '' ,v_endDate, '' '' );
 
if (v_orgCode is not null and v_orgCode != '' ) then
-- SET v_sql = v_sql || ' and ehr.forg_code =''' || v_orgCode || '''';
SET v_sql = CONCAT(v_sql, ' and ehr.forg_code = ' '' ,v_orgCode, '' '' );
end if;
 
-- ============================================乳腺恶性肿瘤
 
-- ============================================================
SET v_sqltmp = ' union select max(ehr.forg_code) forg_code,  ' '乳腺恶性肿瘤' '
 
fdiag_name,count(a.fehrid)
 
FDiseaseCount,sum(famount) famount,sum(famount_insurance) famount_insurance,sum
 
(famount_self) famount_self
  from tbl_ehr_comm_disease a 
  join  tbl_ehr_archives ehr on ehr.fid=a.fehrid
  join tbl_ehr_zy_payway pay on pay.fehrid=a.fehrid
where ehr.fdata_type=' '3001' ' and a.fdiag_name like ' '%乳腺恶性肿瘤%' '
  and ehr.fehr_date >=  ' '' ;
 
SET v_sql = CONCAT(v_sql,v_sqltmp,v_beginDate, ' ' ' and ehr.fehr_date <= ' '' ,v_endDate, '' '' );
 
if (v_orgCode is not null and v_orgCode != '' ) then
SET v_sql = CONCAT(v_sql, ' and ehr.forg_code = ' '' ,v_orgCode, ' ' ' ' );
end if;
 
 
SET v_sql = CONCAT(v_sql, ' ) tt order by tt.FDiseaseCount desc' );
 
-- where tt.FDiseaseCount>0
--  创建临时表
create temporary table if not exists tmpTab(
    FID int not null auto_increment,
    forg_code varchar (20),
    fdiag_name varchar (50),
    fDiseaseCount decimal (19,0),
    fAmount decimal (19,2),
    famount_insurance decimal (19,2),
    famount_self decimal (19,2),
    primary key (FID)
  ) ;
 
   truncate TABLE tmpTab; 
-- SET v_sql = 'insert into tmpTab
 
-- (forg_code,fdiag_name,FDiseaseCount,famount,famount_insurance,famount_self) ' || v_sql;
SET v_sql = CONCAT( 'insert into tmpTab
 
(forg_code,fdiag_name,FDiseaseCount,famount,famount_insurance,famount_self) ' ,v_sql);
 
set @s_sql = v_sql;
prepare stmt from @s_sql;
execute stmt;
 
 
SELECT * from tmpTab;
 
end


通过大致阅读上面的代码,我们可以发现,不同的地方有很多,不单单从字面上,从逻辑上也有很多不同之处。

为了更加直观的说明不同之处,下面列出一个表格来大致说明:

 

oracle 与 mysql 存储过程的区别
区别oraclemysql
创建存储过程的语句不同CREATE OR REPLACE PROCEDURE 存储过程名称()DROP PROCEDURE IF EXISTS xp_stat_BigDiseaseAnalyse;
CREATE PROCEDURE 存储过程名称()
参数的不同(参数类型,出参入参格式,)1.入参(类型前加in 可以省略不写),出参必须在参数类型前加 out
2.字符类型为varchar2,后边不用初始化参数大小;
数字类型为number,不用初始化参数大小;
decimal类型,decimal(5,2)表示位数一共有5位,小数点后有两位;
PK_base.cur 为游标类型。
其他的数据类型请参考:oracle数据类型
1.入参(类型前加in也可以省略不写),出参必须在参数前加out(注意与oracle的区别)
2.字符类型为varchar,后面必须初始化参数大小;
数字类型有int,bigint等;
decimal类型,这个和oracle一样。
详细的其他的数据类型请参考:mysql数据类型
变量的定义方式1.定义:直接在存储过程的的创建语句中声明变量,以及类型。
2.变量的赋值:变量名 := 赋值内容 即可。
1.定义:使用declare语句,例如: declare v_sql varchar(5000)
定义一个大小为5000 字符变量 v_sql
2.变量的赋值: set 变量名= 赋值内容
字符串连接的方式oracle中使用 || 连接字符串,
例如:v_sql :='woshi' || 'zifuchuan'
则,v_sql的值为 woshizifuchuan
mysql中字符串的连接使用caoncat()函数,
例如: set v_sql=concat('woshi','zifuchuan')
v_sql 的值为 woshizifuchuan
游标的问题详细参见:oracle游标使用详细参见:mysql游标使用
分页查询的问题oracle中分页查询主要依赖于rownum(它是一个Oracle中的系统变量,无需定义即可使用,有特定的含义)。详细的分页查询例子参见:
oracle分页查询

mysql中的分页查询,主要依赖limit语句来实现 limit 1,10 意思就是从第一行开始查询,查询10个记录。例如:

mysql中分页查询有两种方式, 一种是使用COUNT(*)的方式,具体代码如下
SELECT COUNT(*) FROM foo WHERE b = 1;

SELECT a FROM foo WHERE b = 1 LIMIT 100,10;
另外一种是使用SQL_CALC_FOUND_ROWS
SELECT SQL_CALC_FOUND_ROWS a FROM foo WHERE b = 1 LIMIT 100, 10;
SELECT FOUND_ROWS();

第二种方式调用SQL_CALC_FOUND_ROWS之后会将WHERE语句查询的行数放在FOUND_ROWS()之中,第二次只需


要查询FOUND_ROWS()就可以查出有多少行了。

异常处理当存储过程语句执行错误,或者查询不到数据等情况出现时,就该设置异常,对异常进行处理。
oracle异常处理详见:oracle异常处理

当存储过程语句执行错误,或者查询不到数据等情况出现时,就该设置异常,对异常进行处理。
mysql异常处理详见:mysql异常处理
条件语句if then else 等 具体实例参考:oracle if语句详见:mysql中if语句的使用
函数的区别 (具体如日期转换函数等)有时涉及到日期的转换(比如格式),有时要返回一个日期中的月份,第几天等等,都需要使用函数来实现.详见:oracle函数小结本次涉及到的mysql函数有concat,以及一些日期转换函数。
其他的函数参考:mysql日期函数小结
单引号,双引号问题详见:oracle单引号双引号详见:mysql单引号双引号
注释的问题oracle注释: --注释内容mysql注释: -- 注释内容 (--后面比oracle多一个空格)
可否打包oracle的存储过程可以,装在一个包中,即一个包中可以存在多个存储过程。mysql不可以打包
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页