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;