Oracle - MSSQL Convert Tips (1)-优快云博客

博主参与将Oracle和MSSQL的SQL语句进行转换的项目并有所收获。介绍了自增列问题，在SQL Server和Oracle中的不同实现；插入时自动设置系统日期的触发器；语法差异与相似处；Oracle的connect by在SQL Server需用用户定义函数实现；还提及了IsNull和nvl函数的使用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

These days I participate a project to convert sql statements between Oracle and MSSQL and learn a lot.

1.Auto increament column Issue
sql server : when create table define this column property identity(0,1)
oracle : must use a sequence and a trigger as the following:

CREATE SEQUENCE SEQUENCE_NAME
START WITH 0
NOMAXVALUE
MINVALUE 0
NOCYCLE
NOCACHE
NOORDER;

CREATE OR REPLACE TRIGGER TRIGGER_NAME
BEFORE INSERT
ON TABLENAME
FOR EACH ROW
BEGIN
SELECT SEQUENCE_NAME.NEXTVAL
INTO :NEW.id
FROM DUAL;
END;

2. Trigger Auto set System Date
when insert an entry, set a certain column to sysdate.
This makes only one clock ,and reduce one column operation in programming .
both use trigger like the following:

sql server case:
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[TRIGGER_NAME]') AND OBJECTPROPERTY(id, N'IsTrigger') = 1)
DROP TRIGGER [TRIGGER_NAME]
GO
CREATE TRIGGER [TRIGGER_NAME] ON [TABLE_NAME]
FOR INSERT
AS
    DECLARE @id int;
    SELECT @id = inserted.id FROM inserted;
    UPDATE TABLE_NAME SET COLUMN_NAME = getdate() WHERE id = @id;
GO

Oracle case :
CREATE OR REPLACE TRIGGER TRIGGER_NAME
BEFORE INSERT
ON TABLE_NAME
FOR EACH ROW
BEGIN
SELECT sysdate
INTO :NEW.COLUMN_NAME
FROM DUAL;
END;

3. Sytax difference and similiar
modify column ,alter column
Integrated constraint like Primary Key, Foreign Key, Check, Unique ,Index

4. Oracle connect by, SQL Server should use User Defined Function to do so.

5 .Misc
sqlserver: IsNull(a,b)
oracle: nvl(a,b)