從sybase的儲存過程轉向oracle的儲存過程

http://www.itpub.net/23129,1.html

討論：從sybase的儲存過程轉向oracle的儲存過程的不同點

另：

從sybase的儲存過程轉向oracle的儲存過程有一些慣性專不過來：
1.sybase的@@rowscount系統變數反映update等語句執行時影響的資料表記錄條數，oracle中對應的是什麼？
2.sybase儲存過程中可用select @n_ret 直接返回區域性變數@n_ret的值，再oracle中如何實現相同的功能？
3.sybase儲存過程中引數說明可以為 para1 char(4),在oracle中不允許，是否oracle沒有辦法說明型別為4位字元的引數？初次寫oracle的儲存過程，總是這也不對，那也不對，頭痛！請各位大蝦幫幫忙！

看書之後，瞭解了PL/SQL的一些與SYBASE不同點，寫出來一方面請大蝦指點是否有謬誤，也希望拋磚引玉，大家能進一步提出其它應關注的地方，使我等初學者少走彎路，功德無量。
1. ORACLE儲存過程的引數不能限定位數，引數型別定位為
CHAR(5)是非法的，只能定義為CHAR,具體位數限定有呼叫時的實參決定，這一點確實與SYBASE有很大不同；
2.遊標在PL/SQL中作用極大，遊標的概念滲透到整個PL/SQL的核心，連INSERT,UPDATE等語句都隱含了一個隱式遊標SQL,類似SYBASE的@@ROWCOUNT等系統變數，在ORACLE中定義為遊標屬性SQL%ROWCOUNT;
3.ORACLE中顯示一個變數的語句為SELECT V_VAR FROM DUAL;與SYBASE不同的是必須加FROM DUAL; （sybase是select @n_ret 直接返回區域性變數@n_ret的值）
4.SYBASE儲存過程可以透過類似SELECT * FROM T_TABLE來返回資料集，在ORACLE中似乎不能，所有不帶INTO的SELECT 語句在ORACLE儲存過程中是非法的。這一點變化帶來最大麻煩，因為POWERBUILDER呼叫SYBASE儲存過程很喜歡這種方式。

我也加一點1、在儲存過程中宣告引數不用加 DECLARE

2、在每個塊中只有一個 DECLARE（sybase可有多個declare）

3、引數名不用加 @ 字尾

4、每條語句後要加分號

5、變數附值不同：
myvalue:='abc';
select @myvalue='abc'

SELECT EMPNO into :emp from EMP WHERE ...;
SELECT @emp=EMPNO from emp WHERE...

6、在TRIGGER 中出現exception 不用加ROLLBACK命令

7、oracle 不使用 read locks ,無論是 read-consistent 還是 serializable 事務所以要注意SELECT FOR UPDATE的使用

如：select * from so_charge where receipt_nbr = null;應改為：select * from so_charge where receipt_nbr is null;

8""->''
ORACLE對字串使用單引號，而SYBASE單引號、雙引號均可因此若有如select * from so where state="A";的語句應改為select * from so where state='A';

9、insert -> insert into
SYBASE允許使用insert tablename...,而ORACLE必須使用
insert into tablename...,若有上述情況，應做相應調整。

10、+ --&gt ||字串連線，SYBASE使用'+'，而ORACLE使用'||'。?

11、getdate() --&gt sysdate取系統時間，SYBASE使用getdate()，而ORACLE使用sysdate。

pb的資料視窗如何使用oracle的儲存過程？

pb的資料視窗如何使用oracle的儲存過程？用慣了sybase儲存過程返回資料集給pb資料視窗使用的方式，不知道改用oracle如何實現類似的功能？

移植方案
一.後臺儲存過程利用conv72從SYBASE轉換到ORACLE。

USAGE: conv72 [-P -F -M]
-P指儲存過程，-F指函式例如：conv72 -P filename.sql轉換後,產生檔名字是filename.sql.sql


二.轉換後的調整。

（一）後臺儲存過程的調整

1、NULL
ORACLE對NULL 的條件判斷只能用： 變數 IS NULL 和變數 IS NOT NULL；對NULL 的附值只能用：變數 := NULL；因此需要對所有儲存過程中有關 NULL 的操作做上述相應調整。

2、StoO_error:=0;所有儲存過程中，凡是對資料庫做有效操作之前，包括UPDATE，DELETE，INSERT以及執行子儲存過程或函式，都有必要將ORACLE轉化過程中自動加上的變數StoO_error清零，即在操作之前加上 StoO_error:=0; 。

3、WHEN NO_DATA_FOUND THEN StoO_error :=0;所有儲存過程中，對資料庫做有效操作之後，通常會用IF StoO_error != 0 THEN來檢驗操作成功與否。對於空操作，即WHERE 子句條件不滿足時，ORACLE會產生例外，而SYBASE 不會出錯。因此為了一致，在 EXCEPTION 中 應加入
WHEN NO_DATA_FOUND THEN StoO_error :=0;

4、示例：
StoO_error :=0;/*操作之前加上 StoO_error:=0*/

BEGIN
DELETE BOAD
WHERE (EXCH_ID = i_exch_id) AND (SWT_ID = i_swt_id)
AND (FRAME_NBR = i_frame_nbr) AND (SHELF_NBR = i_shelf_nbr)
AND (BOAD_NBR = i_boad_nbr);
StoO_rowcnt := SQL%ROWCOUNT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
StoO_error :=0;
WHEN OTHERS THEN
StoO_error := SQLCODE;
END;

/*

一個內在的異常中，SQLCODE返回Oracle錯誤的序號，而SQLERRM返回的是相應的錯誤訊息，錯誤訊息首先顯示的是錯誤程式碼

如果沒有異常被觸發，則SQLCODE返回0，SQLERRM返回“ORA-0000：normal, successful completion”

*/

IF StoO_error != 0 THEN
BEGIN
ROLLBACK TO SAVEPOINT aa;
i_status := -1 ;
RETURN /* */;
END;
END IF;
5、事務設計
ORACLE的儲存過程中，COMMIT 語句將會把此前未提交的所有事務都提交，且對於已提交的事務，無論在後臺儲存過程中，還是在前臺 PB 的指令碼中，都無法回滾。這與SYBASE 不同，因此事務應該結合後臺儲存過程與前臺 PB 指令碼設計。

6、事務的調整在ORACLE中，沒有SYBASE中事務巢狀數的概念，在後臺的儲存過程中，遇到
COMMIT時，將把整個事務（包括前臺和後臺）全部提交。而在SYBASE中，只有當事物數為最低階時（設計都在前臺）才能提交。

sybase資料庫中如何取得當前事務巢狀的數量：

@@trancount ----------事務巢狀層次
@@transtate -----------事務狀態由於所有事務都在前臺提交，因此把後臺儲存過程中所有的COMMIT全部註釋掉。



7、時間引數調整
如果該儲存過程的輸入引數為DATE型時，在POWERBUILDER中建立儲存過程時，將引發錯誤。
為了解決此問題，
在後臺，將這種儲存過程的DATE型輸入引數 全部改為VARCHAR2型。在儲存過程中，將VARCHAR2轉化為DATE，為了前後臺一致，採用統一的轉化格式。

i_start_DATE:=TO_DATE(i_start_string,'YYYY/MM/DD HH24:MI:SS');
i_end_date :=TO_DATE(i_end_string, 'YYYY/MM/DD HH24:MI:SS');
在前臺POWERBUILDER中，將DATE或者DATETIME型變數轉化為STRING型，也採用上述統一格式，再作為儲存過程的引數。

v_STRING=STRING(v_DATE, 'YYYY/MM/DD HH:MM:SS')
v_STRING=STRING(v_DATETIME,'YYYY/MM/DD HH:MM:SS')

（二）用於資料視窗的後臺儲存過程的修改

1、透過特定表返回時，WHERE子句的處理


TABLE: TEST

COLUMN: NAME VARCHAR2(20);
AGE NUMBER;
BIRTH DATE;
REMARKS VARCHAR2(30);
最後的返回語句如下，WHERE子句的處理中，對字元型和日期型進行轉義處理

PBDBMS.PUT_LINE('SELECT NAME,AGE,BIRTH,REMARKS ')
PBDBMS.PUT_LINE(' FROM TEST ');
str_where:=' WHERE NAME='|| ''''||VAR_NAME||''''
||'AND AGE =' || TO_CHAR(VAR_AGE)
||'AND BIRTH=' || ''''||TO_CHAR(VAR_BIRTH)||'''';
PBDBMS.PUT_LINE(str_where);
注意：如果NUMBER型變數是該儲存過程的輸入引數，則要對他進行單獨處理
IF VAR_AGE IS NULL THEN
CONV:=' AND AGE IS NULL ';
ELSE
CONV:=' AND AGE = ' || TO_CHAR(VAR_AGE);
END IF;


2、最後資料的返回透過表DUAL進行
有兩種方式：（1）、用PBDBMS.PUT_LINE。無法處理返回值為空值的情況使用方法見底下示例：注意：對不同變數型別進行了不同處理。
（2）、用PBDBMS.PUT該過程已經過修改，可對各種變數型別進行處理，不在需要在程式中對不同型別進行不同處理，同時也可適應NULL值的情況。使用方法見底下示例：注意：直接使用該引數，多個變數返回時注意中間加PUT(',');
另外：PBDBMS包中行最大長度為255，而PUT函式不換行，因此估計長度快到255時，使用PUT_LINE(',')來實現換行目的。

PROCEDURE AAA
(I_SO_NBR VARCHAR2)
IS
I_NUM INTEGER;
i_date date;
I_NAME VARCHAR2(50);
I_COV VARCHAR2(100);
I_COV2 VARCHAR2(100);
BEGIN
/* 第一種方式*/
I_COV:= ' SELECT '||''''||I_NAME||''''||','||TO_CHAR(I_NUM)||','||''''||TO_CHAR(I_DATE)||'''';
I_COV2:=' FROM DUAL';

PBDBMS.PUT_LINE(i_cov);
PBDBMS.PUT_LINE(I_cov2);

/*第二種方式*/
PBDBMS.PUT_LINE('SELECT ');
PBDBMS.PUT(I_NAME);
PBDBMS.PUT(',');/* 如長度將超過255，此處用PUT_LINE('，')進行換行*/

PBDBMS.PUT(I_NUM);PBDBMS.PUT(',');
PBDBMS.PUT(I_DATE);
PBDBMS.PUT_LINE(' FROM DUAL');

END;


（三）前臺指令碼中嵌入式SQL語句的調整

1、ORACLE 儲存過程前臺指令碼呼叫方式：
DECLARE sosp_cust_i PROCEDURE FOR sosp_cust_i
(:name,:reg_nbr,:li_cust_cat_id,osition,
null,arent_id) ;
execute sosp_cust_i;


SYBASE 儲存過程前臺指令碼呼叫方式：
DECLARE sosp_cust_i PROCEDURE FOR sosp_cust_i
@name = :ls_name,
@reg_nbr = :ls_regnbr,
@cat_id = :ii_cust_cat,
@position = null,
@type_id = :ii_cust_type,
@parent_id = :ll_parent_id ;
execute sosp_cust_i;


2、前臺指令碼中嵌入式的SQL語句中
(1)NULL
ORACLE對NULL 的條件判斷只能用： 欄位名 IS NULL 和 欄位名?IS NOT NULL；而不能用：欄位名 = NULL 和 欄位名?<> NULL?若有上述情況，應做相應調整。 如：select * from so_charge where receipt_nbr = null;應改為：select * from so_charge where receipt_nbr is null;

(2)""->''
ORACLE對字串使用單引號，而SYBASE單引號、雙引號均可因此若有如select * from so where state="A";的語句應改為select * from so where state='A';

(3)insert -> insert into
SYBASE允許使用insert tablename...,而ORACLE必須使用
insert into tablename...,若有上述情況，應做相應調整。

(4)+ --&gt ||字串連線，SYBASE使用'+'，而ORACLE使用'||'。?

(5)getdate() --&gt sysdate取系統時間，SYBASE使用getdate()，而ORACLE使用sysdate。

怎樣將sybase中的資料匯入到oracle中：

1.將sybase中的資料導成SQL語句，然後針對可能不同的資料型別做一些修改。或者將資料導成文字，使用分隔符隔離，然後使用oracle的sqlldr匯入.

在SYBASE的儲存過程中執行動態SQL語句:

declare @sql varchar(255)

select @sql = ...

execute(@sql)

sybase自增量型別欄位設定,用identity：

create table test(id int identity(1,1))

create table dbo.xyxp3 (

pswy numeric(18, 0) identity,

jdm varchar(4) null,

xzdm varchar(2) null,

bxdm varchar(6) null,

dydj decimal(4, 0) null,

azdd varchar(8) null,

qssj datetime null,

zzsj datetime null,

zcs decimal(8, 2) null,

ztf varchar(3) null,

gzbm varchar(10) null,

jxfy decimal(6, 2) null,

tqzk varchar(16) null,

bz varchar(200) null,

zdsj varchar(1) null,

scg varchar(1) null,

scg1 varchar(1) null,

scg2 varchar(1) null,

bdh varchar(2) null,

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/14377/viewspace-902687/，如需轉載，請註明出處，否則將追究法律責任。