oracle 游标的替代,Oracle中replace函数和translate函数以及简单的游标

简要比较：

replace：字符串级别的代替

如：SELECT REPLACE('acdd','cd','ef') FROM dual; →aefd

translate：字符级别的代替

如：SELECT TRANSLATE('acdd','cd','ef') FROM dual; →aeff

分别详解：

replace:

语法：REPLACE ( char , search_string [, replacement_string] )

REPLACE returns char with every occurrence of search_string replaced with replacement_string. If replacement_string is omitted or null, then all occurrences of search_string are removed. If

search_string is null, then char is returned.

解释：repalce中，每个search_string都被replacement_string所代替。

select replace('acdd','cd','ef') from dual;→ aefd

如果replacement_string为空或为NULL,那么所有的search_string都被移除。

select replace('acdd','cd','') from dual;→ad

如果search_string为null,那么就返回原来的char。

select replace('acdd','','ef') from dual;→acdd

select replace('acdd','','') from dual;→acdd(也是两者都为空的情况)

Both search_string and replacement_string, as well as char, can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The string returned is of VARCHAR2 datatype and is in

the same character set as char.

解释：这段指明了可以用的数据类型。

This function provides functionality related to that provided by the TRANSLATE function. TRANSLATE provides single-character, one-to-one substitution. REPLACE lets you substitute one string for

another as well as to remove character strings.

解释：红色部分也是replace和translate的区别。

translate:

语法：TRANSLATE ( 'char' , 'from_string' , 'to_string' )

TRANSLATE returns char with all occurrences of each character in from_string replaced by its corresponding character in to_string. Characters in char that are not in from_string are not

replaced. The argument from_string can contain more characters than to_string. In this case, the extra characters at the end of from_string have no corresponding characters in to_string. If these

extra characters appear in char, then they are removed from the return value.You cannot use an empty string for to_string to remove all characters in from_string from the return value. Oracle

interprets the empty string as null, and if this function has a null argument, then it returns null.

解释：Translate中，每个from_string中的字符被to_string中相应的字符所代替。

select translate('acdd','cd','ef') from dual;→aeff

如果from_string比to_string长，那么from_string中多余的字符将被移除。

select translate('acdd','acd','ef') from dual;→ef (a由e代替，c由f代替,d就被移除)

select translate('acdd','cda','ef') from dual;→eff(c由e代替，d由f代替，a就被移除)

如果to_string为空，或者两者都为空，那么返回char也为空。所以to_string不能为空。

select translate('acdd','cd','') from dual;→ (空)

select translate('acdd','','') from dual;→(空)

实战：

如何判断一个字符串是否是数字？

解：先转换：由于to_string不能为空，我们巧用#号代替

select translate('abc123','#1234567890.','#') from dual;→abc

from_string中的#被to_string中的#代替，但char中又没有#字符，所以通过这一步躲开了to_string必须不为空的规则。然后后面的数字以及小数点都转换为空，于是原来的字符串中只留下abc三个字符。

转换好后，用nvl2判断即可：

select nvl2(translate('abc123','#1234567890.','#'),'字符串','数字') from dual;→字符串

nvl2的作用就是，NVL2 (expr1, expr2, expr3) ->expr1不为NULL，返回expr2；为NULL，返回expr3。这样我们就可以判断一个字符串是否是数字了!解毕!

游标

close cursor_name

关闭的游标可以再次被打开，因此，可以多次创建活动集。任何对关闭了的游标的操作都会引发INVALID_CURSOR错误。

每个用户可打开的游标的数量都是有限制的，其数量由数据库参数域中的OPEN_CURSORS参数来决定，缺省时OPEN_CURSORS=50.

游标的属性

每个游标有四个属性可以用于访问游标的环境区域：

(1)%NOTFOUND 为了从一个显式游标中处理多行数据，可以定义一个循环，每次循环提取一行数据。对于不成功的提取，%NOTFOUND属性的值为TRUE.

(2)%FOUND 对于成功的数据提取，返回TRUE.

(3)%ROWCOUNT 返回当前从活动集合获取的行数。

(4)%ISOPEN 仅当游标处于打开状态时，才可以从中提取数据。可以使用%ISOPEN游标属性来检测游标是否已被打开。

注意：%ISOPEN返回游标的状态。并不是每次都需要检查%ISOPEN。

游标式的FOR循环

游标式的FOR循环，它的循环索引声明为%ROWTYPE的记录，然后隐式地打开游标，并且从活动集反复提取行的值，当所有行都处理完后，游标将自动关闭。当提取最后一行时，循环会自动终止。

语法：FOR record_name IN cursor_name LOOP

...

END LOOP;

(1)不需要声明控制循环的记录，记录的作用域仅限于FOR循环内部。

(2)如果需要，为游标提供参数，参数放在FOR语句中游标名后的圆括号内。

(3)当需手工处理游标时，不能使用游标式的FOR循环。

发送接口：

//在mz_byl_fr_contract表做update操作后作为触发点

CREATE OR REPLACE TRIGGER BYLERP.BYL_FILTERRODCONTRACT_RECEIVE

after update of useflg on mz_byl_fr_contract

for each Row

//声明变量

Declare

billidVar   Varchar2(36);

dataCount1 int;

dataCount2 int;

begin

If :Old.useflg!=:New.useflg And :new.useflg='1' Then

Select Count(*) Into dataCount1 From BYL_FilterRodContractDet Where :New.Rid=REPLACE(Id, '-', '');

If dataCount1>0 Then

Select billid Into billidVar  From BYL_FilterRodContractDet Where :New.Rid=REPLACE(Id, '-', '');

Select Count(*) Into dataCount2 From BYL_FilterRodContractDet Where billID=billidVar And operationMark='0';

If dataCount2=1 Then

Update BYL_FilterRodContractDet Set operationMark='1' Where :New.Rid=REPLACE(Id, '-', '');

Update BYL_FilterRodContract Set operationMark='1' Where Id = billidVar;

End If;

If dataCount2>1 Then

Update BYL_FilterRodContractDet Set operationMark='1' Where :New.Rid=REPLACE(Id, '-', '');

Update BYL_FilterRodContract Set operationMark='2' Where Id = billidVar;

End If;

End If;

end if;

end;

接收接口数据：这里简单用到了游标的例子：

CREATE OR REPLACE TRIGGER BYLERP.BYL_FILTERRODCONTRACT_SEND

after update of billStatus on BYL_FilterRodContract

for each Row

Declare

cursor contract_cur is Select * From BYL_FilterRodContractDet Where billID=:New.Id;

xu contract_cur%rowtype;

begin

If :Old.billStatus!=:New.billStatus And :new.billStatus='0050' Then

open contract_cur;

fetch contract_cur into xu;

While contract_cur%found

Loop

insert into mz_byl_fr_contract(Rid,ctrno ,i_verno,custcd,cust,address,exedt,

enddt,gcode,gname,qnt,fcy,Trans_time,useflg)

Values(REPLACE( xu.Id, '-', ''),SUBSTR(:New.contractCode,1,30),:New.version,SUBSTR(:New.clientEnterpriseCode,1,8),:New.clientEnterpriseName,:New.destination,:New.signatureDate,

:New.contractEndDate,SUBSTR(xu.filterRodcode,1,16),xu.filterRodName,xu.machineAmounts,xu.unitPrice,

to_date(to_char(Sysdate,'yyyy-MM-dd'),'yyyy-MM-dd'),'0');

fetch contract_cur into xu;

End Loop;

close contract_cur;

end if;

end;