从sybase的存储过程转向oracle的存储过程的不同点

引自：http://www.itpub.net/23129.html

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;
4.SYBASE存储过程可以通过类似SELECT * FROM T_TABLE来返回数据集，在ORACLE中似乎不能，所有不带INTO的SELECT 语句在ORACLE存储过程中是非法的。这一点变化带来最大麻烦，应为POWERBUILDER调用SYBASE存储过程很喜欢这种方式。
5、在存储过程中声明参数不用加 DECLARE
6、在每个块中只有一个 DECLARE
7、参数名不用加 @ 后缀
8、每条语句后要加分号
9、变量附值不同：
myvalue:='abc';
select @myvalue='abc'

SELECT EMPNO into :emp from EMP WHERE ...;
SELECT @emp=EMPNO from emp WHERE...
10、在TRIGGER 中出现exception 不用加ROLLBACK命令
11、oracle 不使用 read locks ,无论是 read-consistent 还是 serializable 事务所以要注意SELECT FOR UPDATE的使用
移植方案
一.后台存储过程利用conv72从SYBASE转换到ORACLE。
USAGE: conv72 [-P -F -M] <input_file_name>
-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;

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中，只
有当事物数为最低级时（设计都在前台）才能提交。
由于所有事务都在前台提交，
因此把后台存储过程中所有的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,position,
null,parent_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)+ --> ||
字符串连接，SYBASE使用'+'，而ORACLE使用'||'。?

(5)getdate() --> sysdate
取系统时间，SYBASE使用getdate()，而ORACLE使用sysdate。