Oracle与SQL Server的sql差异总结(一)

第一个项目用的是SQL Server, 用了一年多,虽然写sql的本事没有啥变化,但能用的顺手.然而,新的项目用的是Oralce,于是就犯下了各种错误,特此总结.

备份表的数据

SQL Server

1.备份的表不存在

SELECT * INTO BACK_TABLE_NAME FROM TABLE_NAME;

2.备份的表存

INSERT INTO BACK_TABLE_NAME SELECT * FROM TABLE_NAME;

Oracle

1.备份的表不存在

CREATE BACK_TABLE_NAME AS SELECT * FROM TABLE_NAME;

2.备份的表存

INSERT INTO BACK_TABLE_NAME SELECT * FROM TABLE_NAME;

变量赋值

SQL Server

DECLARE @num INT

SET @num = 10;

或者

SELECT xx = @num FROM TABLE_NAME WHERE .....;

Oracle

DECLARE V_NUM INT;

V_NUM := 10;

或者

SELECT XX INTO V_NUM FROM TABLE_NAME WHERE ....;

IF语句

SQL Server

IF (xxxx) BEGIN .......... END ELSE BEGIN ...... END;

Oracle

IF (XXXX) THEN ....... ELSE ....... END IF

IF EXISTS 语句

SQL Server

IF EXISTS(SELECT TOP1 ID FROM TABLE_NAME) BEGIN UPDATE TABLE_NAME SET ..... END ELSE BEGIN INSERT ..... END;

Oracle

Oracle没有这个,替代方法如下

DECLARE V_IS_TRUE INT; SELECT CASE WHEN EXISTS (SELECT ID FROM TABLE_NAME) THEN 1 ELSE 0 INTO V_IS_TRUE FROM DUAL IF V_IS_TRUE = 1 THEN .....; ELSE ......; END IF;

循环表数据

SQL Server

SQL Server没有具体函数,只有用游标或者临时表来实现,游标还需慎重,一不小心影响性能,反正我写不好游标

DECLARE @temp_id VARCHAR(40); DECLARE @temp TABLE ( id INT IDENTITY(1,1), xxx_id VARCHAR(40), xxx_name VARCHAR(40), ) INSERT INT0 @temp SELECT id,xx_id,name FROM TABLE_NAME WHILE EXISTS (SELECT id FROM @temp) BEGIN SELECT id = @temp_id ..... FROM @temp; ........ DELETE FROM @temp WHERE id = @temp_id END

Oracle

Oracle则简单许多,因为由FOR函数

BEGIN FOR TL IN (SELECT * FROM TABLE_NAME) LOOP ........; COMMIT; END LOOP; END;