从sybase的存储过程转向oracle的存储过程

从sybase的存储过程转向oracle的存储过程[@more@]

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

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

另：http://topic.csdn.net/f/1805/7501-8000.html

从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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/14377/viewspace-902687/