專案需將 SQL Server 升級腳本移植至 Oracle。兩者 T-SQL 與 PL/SQL 語法差異顯著,但遵循系統性轉換流程可大幅降低錯誤率。
| 項目 | T-SQL (MSSQL) | PL/SQL (Oracle) |
|---|---|---|
| 區塊結構 | BEGIN ... END | DECLARE ... BEGIN ... END; / |
| 變數宣告 | DECLARE @var TYPE | var TYPE; (在 DECLARE 區塊) |
| 賦值 | SET @var = val / SELECT @var = val | var := val; / SELECT ... INTO var |
| 游標 | 全域,需 OPEN/FETCH/CLOSE/DEALLOCATE | 區域,FOR 迴圈自動管理 |
| 流程控制 | IF ... ELSE BEGIN ... END | IF ... THEN ... ELSIF ... ELSE ... END IF; |
| 迴圈 | WHILE ... BEGIN ... END | LOOP ... END LOOP; / FOR ... LOOP ... END LOOP; |
| 動態 SQL | EXEC (@sql) / sp_executesql | EXECUTE IMMEDIATE sql_str; |
| 列印輸出 | PRINT / RAISERROR | DBMS_OUTPUT.PUT_LINE |
| 錯誤處理 | TRY...CATCH / @@ERROR | EXCEPTION WHEN ... THEN ... |
| 系統表 | sys.objects, sys.columns | user_tables, user_tab_columns, all_objects |
| 臨時表 | #temp / ##global | GLOBAL TEMPORARY TABLE / PL/SQL 集合 |
| 識別欄 | IDENTITY(1,1) | SEQUENCE / IDENTITY (12c+) |
| 字串聯結 | + / CONCAT | || / CONCAT |
| 日期函數 | GETDATE(), DATEADD, DATEDIFF | SYSDATE, ADD_MONTHS, MONTHS_BETWEEN |
-- T-SQL
BEGIN
DECLARE @x INT;
-- logic
END
-- Oracle
BEGIN
DECLARE
x NUMBER;
BEGIN
-- logic
END;
END;
/
VARCHAR → VARCHAR2, DATETIME → DATE, BIT → NUMBER(1), INT → NUMBER(10)-- T-SQL 显式游標
DECLARE cur CURSOR FOR SELECT col FROM tbl;
OPEN cur; FETCH NEXT FROM cur INTO @v;
WHILE @@FETCH_STATUS = 0 BEGIN ... FETCH NEXT ... END
CLOSE cur; DEALLOCATE cur;
-- Oracle 隱式游標 FOR 迴圈 (推薦)
FOR rec IN (SELECT col FROM tbl) LOOP
-- 直接使用 rec.col
END LOOP;
-- 或顯式游標 (需複雜控制)
DECLARE CURSOR cur IS SELECT col FROM tbl;
BEGIN
OPEN cur;
LOOP FETCH cur INTO v; EXIT WHEN cur%NOTFOUND; ... END LOOP;
CLOSE cur;
END;
-- T-SQL
IF @x > 0 BEGIN ... END ELSE BEGIN ... END
-- Oracle
IF x > 0 THEN ... ELSIF x < 0 THEN ... ELSE ... END IF;
-- T-SQL WHILE
WHILE @i < 10 BEGIN SET @i += 1; END
-- Oracle LOOP
LOOP EXIT WHEN i >= 10; i := i + 1; END LOOP;
-- T-SQL
EXEC (@sql); -- 或 sp_executesql @sql, N'@p int', @p
-- Oracle
EXECUTE IMMEDIATE sql_str USING bind_vars; -- 繫結變數防注入
OBJECT_ID() → 查詢 user_objectsISNULL() → NVL() / COALESCE()GETDATE() → SYSDATETOP n → FETCH FIRST n ROWS ONLY (12c+) 或 ROWNUM <= nIDENTITY → CREATE SEQUENCE + Trigger 或 12c GENERATED AS IDENTITY原文提供的雙向腳本完美示範了上述轉換規則。關鍵點:
EXECUTE IMMEDIATE 執行 DDLUSER_TABLES/USER_PROCEDURES 替代 sysobjectsWHEN NO_DATA_FOUND THEN NULL 替代 IF EXISTSDBMS_OUTPUT 替代 PRINT語法轉換只是開始,效能調優、鎖定行為、交易語義差異才是遷移成敗關鍵。