1.构建临时数据比较(Oracle中需要哑表dual辅助)
Oracle: select 'stuNo1' as f_stuno from dual union all select 'stuNo2' from dual
-------------------------------------------------------------------------------------------------
SqlServer: select 'stuNo1' as f_stuno union all select 'stuNo2'
2.空值替换函数
Oracle: select nvl(字段名,'替换的字符') from 表
-------------------------------------------------------------------------------------------------
SqlServer:select isnull(字段名,'替换的字符') from 表
3.判断表是否存在
Oracle:
declare
i_l_count pls_integer;--判断表是否存在标识
begin
select count(1) into i_l_count from user_tables t
where t.TABLE_NAME ='表名' ;
if i_l_count>0
then
dbms_output.put_line('Exist....');
else
dbms_output.put_line('Not Exist....');
end if;
end;
------------------------------------------------------------------------------------------
SqlServer:
if exists (select * from sysobjects where xtype='U' and id=object_id(N'表名'))
print 'exist...'
else
print 'not exist...'
4.case when 的用法俩者基本相同
如:select case 字段 when 字段值1 then '已确认' else '未确认' end as f_ackname from 表名;
5.游标的使用(在俩种数据库环境下还是有些区别的)
Oracle:
declare
curName varchar2(32);
cursor pointNameList is select distinct f_name from tdac_yc;
begin
open pointNameList;
fetch pointNameList into curName;
while pointNameList%found loop
fetch pointNameList into curName;
dbms_output.put_line(curName);
end loop;
close pointNameList;
end;
------------------------------------------------------------------------------------------
SqlServer:
declare @currentName varchar(32)
declare pointNameList cursor for select distinct f_name from tdac_yc;
open pointNameList;
fetch pointNameList into @currentName;
while @@fetch_status=0
begin
print @currentName;
fetch pointNameList into @currentName;
end;
close pointNameList;
deallocate pointNameList;
6.创建存储过程
Oracle :
create or replace procedure p_function
(params varchar)
is
begin
dbms_output.put_line(params);
end;
--------------------------------------------------------------------------------------------
SqlServer:
create procedure p_function
@params varchar(64)
as
begin
print @params;
end;
7.视图的创建与删除
Oracle:
--create view
create or replace view v_view
as select * from tdac_yc;
--drop view
drop view v_view;
-------------------------------------------------------------------------------------------
SqlServer:
--create view
create view v_view
as
select * from tdac_yc;
--drop view
drop view v_view;
8.将日期类型字段按特定格式输出
如2003-01-02 00:02:35.000=>输出1:2003-01-02,输出2: 00:02:35
Oracle :
select to_char(sysdate,'yyyy-MM-dd') from dual;
select to_char(sysdate,'hh24:mi:ss') from dual;
-------------------------------------------------------------------------------------------------------
SqlServer:
select convert(char(11),getdate(),120)
select convert(varchar(100), getdate(), 24)
9.交叉表的使用(俩者基本相同)
如:表Stu 字段 f_score 分数 f_class 课程 f_stuname 学生姓名
select f_stuname,sum(case f_class when '语文' then f_score else 0) end as 语文,sum(case f_class when '数学' then f_score else 0) end as 数学
from Stu group by f_stuname
10.返回动态数据表:
Oracle: (联合数组的使用)
--自定义类型
create or replace type t_test as object(
id integer,
devcode varchar2(32),
devdetail varchar2(600)
);
create or replace type t_test_table as table of t_test;
--创建临时数据
create or replace function f_test_array(n in number default null) return t_test_table
as
v_test t_test_table := t_test_table();
begin
--for i in 1 .. nvl(n,100) loop
v_test.extend();
v_test(1) := t_test(1,'g10_nh_gis','devDetail');
v_test.extend();
v_test(2) := t_test(2,'g21_rf_gis','devDetail');
v_test.extend();
v_test(3) := t_test(3,'g37_nh_gis','devDetail');
v_test.extend();
v_test(4) := t_test(4,'File','Files');
--end loop;
return v_test;
end f_test_array;
--调用数据
select * from Table(f_test_array);
------------------------------------------------------------------------------------------------------------------------------
SqlServer:
可以直接创建临时表,类似的方式如:select f_devcode,f_devname into #tab from tdac_yc; select * from #tab; drop table #tab;