Oracle 与 SqlServer 常用语法比较小结

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;