oracle与sqlserver比较
一:Oracle与Sql Server的访问上有很大不同:
1、字段类型不同
2、存储过程有很大差异,Oracle不能直接返回记录集,需要通过一个 out 参数达到目的。在OracleType中有一个OracleType.Cursor 类型与之对应。 Oracle中的存储过程大部分都定义成Funcion, 有返回值。 在定义Command的参数集合时,需要增加一个"ReturnValue"的参数。
3、Oracle中的参数无需"@"符号
4、Oracle的Sql 语句中 在参数前面加冒号":", 而Sql Server 的Sql 在前面加"@"
二:存储过程例子
1.
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)