SQL Server vs Oracle: 10 Essential Syntax and Feature Differences

1. Data Types

SQL Server supports types such as

int, smallint, char, varchar, nchar, nvarchar, ntext, datetime, smalldatetime, money, decimal, float, bit

, while Oracle uses number(p,s), char, varchar2, date, LOB. When inserting a string date in Oracle, prepend date (e.g.,

insert into table_name values('1','张三','男', date'2012-3-5');

).

2. Current Date Functions

SQL Server retrieves the current timestamp with getdate(); Oracle uses sysdate. Formatting a date in Oracle can be done with to_char(sysdate,'yyyy-mm-dd').

3. Default Constraints

SQL Server adds a default constraint using

alter table table_name add DF_table_name default('男') for sex;

. In Oracle, the equivalent is alter table table_name modify(sex default('男'));.

4. String Concatenation

SQL Server concatenates strings with the + operator (e.g., print 'aaaa' + @name;), whereas Oracle uses the double pipe || (e.g., dbms_output.put_line('aaa' || name);).

5. Auto‑Increment Columns

SQL Server provides identity columns directly (e.g., id int identity(1,1)). Oracle lacks identity columns and instead uses sequences:

create sequence se_id start with 1 increment by 1;

Access the next value with se_id.nextval.

6. IF…ELSE Syntax

SQL Server syntax:

if condition
begin
    -- statements
end
else
begin
    -- statements
end

Oracle syntax:

if condition then
    -- statements;
elsif condition2 then
    -- statements;
else
    -- statements;
end if;

7. CASE Statements

SQL Server example:

select stuno as '学号', case
    when grade>=90 and grade<=100 then '★★★★'
    when grade>=80 and grade<90 then '★★★'
    when grade>=70 and grade<80 then '★★'
    when grade>=60 and grade<70 then '★'
    else '差'
end as '等级' from score;

Oracle example:

declare
    nums number := &nos;
begin
    case nums
        when 100 then dbms_output.put_line('满分也，不错');
        when 90 then dbms_output.put_line('90 分页很不错了');
    end case;
end;

8. Trigger Creation

SQL Server workflow includes checking existence, dropping if present, and creating the trigger with variable handling and error checking. Example snippets:

if exists (select * from sys.sysobjects where name='tr_delete')
    drop trigger tr_delete;
create trigger tr_delete on bookInfo instead of delete as
    declare @bookid int;
    select @bookid = Bookid from deleted;
    delete from borrowinfo where bookid=@bookid;
    delete from backinfo where bookid=@bookid;
    delete from BookInfo where BookId=@bookid;
    if @@error <> 0 begin
        print '删除失败';
        rollback transaction;
    end else begin
        print '删除成功';
    end;

Oracle uses a single statement trigger:

create or replace trigger tri_test
    before insert or update or delete on table_name
    [for each row]
    declare
        nums varchar2(20);
    begin
        select 'F' || lpad('aa',5,0) into nums from dual;
    end;

9. Stored Procedures

SQL Server procedure template includes existence check, drop, create, parameter definition, and execution with optional output parameters. Example:

if exists(select * from sys.sysobjects where name='proc_name') drop proc proc_name;
create proc proc_name @param1 datatype, @param2 datatype as
    -- body
    go;
exec proc_name @param1='aaa', @param2=@outVar out;

Oracle procedure with cursor and loop:

create or replace procedure proc_selCurrent(names varchar2) as
    cursor cursor_sel is
        select DepositSum, cardType, name, state from CurrentAccount where name like '%'||names||'%';
    dd number; cc number; nn varchar2(20); sta number;
begin
    open cursor_sel;
    loop
        fetch cursor_sel into dd, cc, nn, sta;
        dbms_output.put_line('存款金额：' || dd || '姓名：' || nn);
        exit when cursor_sel%notfound;
    end loop;
    close cursor_sel;
end;

Calling the procedure:

begin
    proc_selCurrent('a');
end;

10. User Creation

SQL Server creates a login and then a user, with commands to alter, enable/disable, and drop them, plus granting permissions:

create login login_name with password='pwd';
alter login login_name with name='new_name' and password='new_pwd';
alter login login_name disable;
create user user_name for login login_name;
alter user user_name with name='new_user';
grant select, insert, update, delete on table_name to user_name;

Oracle creates a user directly with tablespace and quota settings, alters passwords, grants roles, and drops users with cascade:

create user user_name identified by pwd
    default tablespace users
    temporary tablespace temp
    quota 10M on users;
alter user user_name identified by new_pwd;
grant create session to user_name;
drop user user_name cascade;