1、字段类型不同
2、存储过程有很大差异,Oracle不能直接返回记录集,需要通过一个 out 参数达到目的。在OracleType中有一个OracleType.Cursor 类型与之对应。 Oracle中的存储过程大部分都定义成Funcion, 有返回值。 在定义Command的参数集合时,需要增加一个"ReturnValue"的参数。
3、Oracle中的参数无需"@"符号
4、Oracle的Sql 语句中 在参数前面加冒号" :", 而Sql Server 的Sql 在前面加"@"
--
Sql Server的Sql 语句
insert into Table (Column1,Column2) values ( @Value1 , @Value2 )
-- Oracel中的Sql 语句
Insert Into Table (Column1,Column2) values
(:Value1,:Value2)
insert into Table (Column1,Column2) values ( @Value1 , @Value2 )
-- Oracel中的Sql 语句
Insert Into Table (Column1,Column2) values
(:Value1,:Value2)
二:存储过程例子
1.
1
包的定义:
2
create
or
replace
package myTest
3
is
4
type out_cur
is
ref
cursor
;
5
procedure
writeCount(codeid
in
nvarchar2);
6
procedure
testSandyInSert(codeid
in
nvarchar2,counts out
number
);
7
end
myTest;
8![](/Images/OutliningIndicators/None.gif)
9
存储过程的定义:
10
create
or
replace
package body myTest
11
is
12
procedure
writeCount(codeid
in
nvarchar2)
13
is
14
m_count
number
;
15
begin
m_count:
=
0
;
16
select
count
(
1
)
into
m_count
from
code
where
code_id
=
codeid;
17
dbms_output.put_line(
'
输入参数是
'
||
codeid);
18
dbms_output.put_line(
'
查询结果是
'
||
m_count);
19
end
;
20
procedure
testSandyInSert(codeid
in
nvarchar2,counts out
number
)
21
is
22
m_cur out_cur;
23
m_code_sn nvarchar2(
50
);
24
m_code_id nvarchar2(
50
);
25
m_code_name nvarchar2(
50
);
26
m_insertstr nvarchar2(
50
);
27
m_for
number
:
=
0
;
28
begin
counts:
=
0
;
29
open
m_cur
for
select
code_sn,code_id,code_name
from
code
where
code_id
=
codeid;
30
loop
fetch
m_cur
into
m_code_sn,m_code_id,m_code_name;
31
exit
when
m_cur
%
notfound;
32
case
UPPER
(m_code_sn)
when
'
SP_CTRL_L
'
then
m_code_sn:
=
'
0
'
;
33
when
'
dld
'
then
m_code_sn:
=
'
1
'
;
34
else
m_code_sn:
=
'
3
'
;
35
end
case
;
36
if
UPPER
(m_code_sn)
=
'
SP_CTRL_L
'
then
dbms_output.put_line(
'
条件成立执行,测试if语句的使用
'
);
37
else
dbms_output.put_line(
'
条件不成立执行,测试if语句的使用
'
);
38
end
if
;
39
m_insertstr:
=
''''
||
m_code_sn
||
'''
,
'''
||
m_code_id
||
'''
,
'''
||
m_code_name
||
''''
;
40
begin
execute
immediate
'
insert into sandytest(col1,col2,col3) values(
'
||
m_insertstr
||
'
)
'
;
41
dbms_output.put_line(
'
插入表成功!
'
);
42
counts:
=
1
;
43
exception
when
others
then
dbms_output.put_line(
'
插入表失败!
'
);
44
end
;
45
end
loop;
46
for
m_for
in
1
..
10
loop dbms_output.put_line(
'
循环测试:
'
||
m_for);
47
end
loop;
48
close
m_cur;
49
end
;
50
end
myTest;
![](/Images/OutliningIndicators/None.gif)
2
![](/Images/OutliningIndicators/None.gif)
3
![](/Images/OutliningIndicators/None.gif)
4
![](/Images/OutliningIndicators/None.gif)
5
![](/Images/OutliningIndicators/None.gif)
6
![](/Images/OutliningIndicators/None.gif)
7
![](/Images/OutliningIndicators/None.gif)
8
![](/Images/OutliningIndicators/None.gif)
9
![](/Images/OutliningIndicators/None.gif)
10
![](/Images/OutliningIndicators/None.gif)
11
![](/Images/OutliningIndicators/None.gif)
12
![](/Images/OutliningIndicators/None.gif)
13
![](/Images/OutliningIndicators/None.gif)
14
![](/Images/OutliningIndicators/None.gif)
15
![](/Images/OutliningIndicators/None.gif)
16
![](/Images/OutliningIndicators/None.gif)
17
![](/Images/OutliningIndicators/None.gif)
18
![](/Images/OutliningIndicators/None.gif)
19
![](/Images/OutliningIndicators/None.gif)
20
![](/Images/OutliningIndicators/None.gif)
21
![](/Images/OutliningIndicators/None.gif)
22
![](/Images/OutliningIndicators/None.gif)
23
![](/Images/OutliningIndicators/None.gif)
24
![](/Images/OutliningIndicators/None.gif)
25
![](/Images/OutliningIndicators/None.gif)
26
![](/Images/OutliningIndicators/None.gif)
27
![](/Images/OutliningIndicators/None.gif)
28
![](/Images/OutliningIndicators/None.gif)
29
![](/Images/OutliningIndicators/None.gif)
30
![](/Images/OutliningIndicators/None.gif)
31
![](/Images/OutliningIndicators/None.gif)
32
![](/Images/OutliningIndicators/None.gif)
33
![](/Images/OutliningIndicators/None.gif)
34
![](/Images/OutliningIndicators/None.gif)
35
![](/Images/OutliningIndicators/None.gif)
36
![](/Images/OutliningIndicators/None.gif)
37
![](/Images/OutliningIndicators/None.gif)
38
![](/Images/OutliningIndicators/None.gif)
39
![](/Images/OutliningIndicators/None.gif)
40
![](/Images/OutliningIndicators/None.gif)
41
![](/Images/OutliningIndicators/None.gif)
42
![](/Images/OutliningIndicators/None.gif)
43
![](/Images/OutliningIndicators/None.gif)
44
![](/Images/OutliningIndicators/None.gif)
45
![](/Images/OutliningIndicators/None.gif)
46
![](/Images/OutliningIndicators/None.gif)
47
![](/Images/OutliningIndicators/None.gif)
48
![](/Images/OutliningIndicators/None.gif)
49
![](/Images/OutliningIndicators/None.gif)
50
![](/Images/OutliningIndicators/None.gif)