从Oracle的 PLSQL 移植Word格式.docx
- 文档编号:8599196
- 上传时间:2023-05-12
- 格式:DOCX
- 页数:13
- 大小:20.25KB
从Oracle的 PLSQL 移植Word格式.docx
《从Oracle的 PLSQL 移植Word格式.docx》由会员分享,可在线阅读,更多相关《从Oracle的 PLSQL 移植Word格式.docx(13页珍藏版)》请在冰点文库上搜索。
v_versionvarchar)
RETURNvarcharIS
BEGIN
IFv_versionISNULLTHEN
RETURNv_name;
ENDIF;
RETURNv_name||'
/'
||v_version;
END;
/
SHOWerrors;
让我们读一遍这个函数然后看看PL/pgSQL与之的不同:
在函数原型里的RETURN(不是函数体里的)关键字到了PostgreSQL里就是RETURNS。
还有,IS变成AS,并且你还需要增加一个LANGUAGE子句,因为PL/pgSQL并非唯一可用的函数语言。
在PostgreSQL里,函数体被认为是一个字串文本,所以你需要使用单引号或者美元符包围它,这个包围符代替了Oracle最后的那个/。
在PostgreSQL里没有showerrors命令,不需要这个命令是因为错误是自动报告的。
下面是这个函数移植到PostgreSQL之后的样子:
RETURNSvarcharAS$$
$$LANGUAGEplpgsql;
Example36-6演示了如何移植一个创建另外一个函数的函数的方法,以及演示了如何处理引号逃逸的问题。
Example36-6.从PL/SQL向PL/pgSQL移植一个创建其它函数的函数
下面的过程从一个SELECT语句中抓取若干行,然后为了提高效率,又用IF语句中的结果制作了一个巨大的函数。
请特别注意在游标和FOR循环里面的不同。
这是Oracle的版本:
CREATEORREPLACEPROCEDUREcs_update_referrer_type_procIS
CURSORreferrer_keysIS
SELECT*FROMcs_referrer_keys
ORDERBYtry_order;
func_cmdvarchar(4000);
BEGIN
func_cmd:
='
CREATEORREPLACEFUNCTIONcs_find_referrer_type(v_hostINVARCHAR,
v_domainINVARCHAR,v_urlINVARCHAR)RETURNVARCHARISBEGIN'
;
forreferrer_keyinreferrer_keysloop
=func_cmd||
'
IFv_'
||referrer_key.kind
||'
LIKE'
'
||referrer_key.key_string
THENRETURN'
||referrer_key.referrer_type
ENDloop;
=func_cmd||'
RETURNNULL;
END;
EXECUTEIMMEDIATEfunc_cmd;
/
showerrors;
下面是这个函数在PostgreSQL里面的样子:
CREATEORREPLACEFUNCTIONcs_update_referrer_type_proc()RETURNSvoidAS$func$
DECLARE
referrer_keyRECORD;
--声明一个在FOR里用的通用的记录
func_bodytext;
func_cmdtext;
func_body:
BEGIN'
--请注意我们是如何在一个FOR循环中使用FOR<
record>
构造
--扫描各条结果的
FORreferrer_keyINSELECT*FROMcs_referrer_keysORDERBYtry_orderLOOP
=func_body||
||quote_literal(referrer_key.key_string)
||quote_literal(referrer_key.referrer_type)
;
ENDLOOP;
=func_body||'
=
CREATEORREPLACEFUNCTIONcs_find_referrer_type(v_hostvarchar,
v_domainvarchar,
v_urlvarchar)
RETURNSvarcharAS'
||quote_literal(func_body)
LANGUAGEplpgsql;
EXECUTEfunc_cmd;
$func$LANGUAGEplpgsql;
请注意函数体是如何独立制作并且传递给quote_literal,对其中的单引号复制双份的。
我们需要这个技巧是因为我们无法使用美元符包围定义新函数:
我们没法保证referrer_key.key_string字段过来的字串会解析成什么样子。
(我们可以假设referrer_key.kind可以是只有host,domain,或者url,但是referrer_key.key_string可能是任何东西,特别是它可能包含美元符。
)这个函数实际上是对原来Oracle版本的一个改进,因为如果在referrer_key.key_string或者referrer_key.referrer_type包含单引号的时候,它不会生成有毛病的代码。
Example36-7演示了如何移植一个带有OUT参数和字串处理的函数。
PostgreSQL里面没有内置instr函数,但是你可以用其它函数的组合来绕开它。
在Section36.11.3里有一个PL/pgSQL的instr实现,你可以用它让你的移植变得更简单些。
Example36-7.从PL/SQL向PL/pgSQL移植一个字串操作和OUT参数的过程
下面的OraclePL/SQL过程用于分析一个URL并且返回若干个元素(主机,路径和命令)。
下面是Oracle的版本:
CREATEORREPLACEPROCEDUREcs_parse_url(
v_urlINvarchar,
v_hostOUTvarchar,--这个变量是要传回的
v_pathOUTvarchar,--这个也是
v_queryOUTvarchar)--还有这个
IS
a_pos1integer;
a_pos2integer;
v_host:
=NULL;
v_path:
v_query:
a_pos1:
=instr(v_url,'
//'
);
IFa_pos1=0THEN
RETURN;
a_pos2:
a_pos1+2);
IFa_pos2=0THEN
=substr(v_url,a_pos1+2);
=substr(v_url,a_pos1+2,a_pos2-a_pos1-2);
?
a_pos2+1);
=substr(v_url,a_pos2);
=substr(v_url,a_pos2,a_pos1-a_pos2);
=substr(v_url,a_pos1+1);
下面就是把这个过程翻译成PL/pgSQL可能的样子:
CREATEORREPLACEFUNCTIONcs_parse_url(
v_urlINVARCHAR,
v_hostOUTVARCHAR,--这个将被传回
v_pathOUTVARCHAR,--这个也传回
v_queryOUTVARCHAR)--还有这个
AS$$
a_pos1INTEGER;
a_pos2INTEGER;
这个函数可以这么用:
SELECT*FROMcs_parse_url('
Example36-8演示了如何一个使用各种Oracle特有的特性的过程。
Example36-8.从PL/SQL向PL/pgSQL移植一个过程
Oracle版本:
CREATEORREPLACEPROCEDUREcs_create_job(v_job_idininteger)
a_running_job_countinteger;
PRAGMAAUTONOMOUS_TRANSACTION;
(1)
LOCKTABLEcs_jobsINEXCLUSIVEMODE;
(2)
SELECTcount(*)INTOa_running_job_countFROMcs_jobsWHEREend_stampISNULL;
IFa_running_job_count>
0THEN
COMMIT;
--freelock(3)
raise_application_error(-20000,'
Unabletocreateanewjob:
ajobIScurrentlyrunning.'
DELETEFROMcs_active_job;
INSERTINTOcs_active_job(job_id)values(v_job_id);
BEGIN
INSERTINTOcs_jobs(job_id,start_stamp)values(v_job_id,sysdate);
EXCEPTION
WHENdup_val_on_indexTHENNULL;
--如果已经存在,别担心
showerrors
象这样的过程可以很容易用返回void的函数移植到PostgreSQL里。
我们对这个过程特别感兴趣是因为它可以教我们一些东西:
在PostgreSQL里没有PRAGMA语句。
如果你在PL/pgSQL里做一个LOCKTABLE,那么这个锁在调用该命令的事务完成之前将不会释放。
(3)
你不能在PL/pgSQL函数里发出COMMIT。
函数是在外层的事务里运行的,因此COMMIT蕴涵着结束函数的执行。
不过,在这个特殊场合下,这是不必要的了,因为LOCKTABLE获取的锁将在我们抛出错误的时候释放。
EXCEPTIONWHEN不得不用一个IF语句替代.
下面是我们把这个过程移植到PL/pgSQL里的一种方法:
CREATEORREPLACEFUNCTIONcs_create_job(v_job_idinteger)RETURNSvoidAS$$
RAISEEXCEPTION'
ajobiscurrentlyrunning'
INSERTINTOcs_jobs(job_id,start_stamp)VALUES(v_job_id,now());
EXCEPTION
WHENunique_violationTHEN
(2)
--don'
tworryifitalreadyexists
RAISE的语法和Oracle的类似语句差别相当明显。
PL/pgSQL里支持的例外的名字和Oracle的不同。
PL/pgSQL内置的例外名要大的多(参阅AppendixA)。
目前还不能声明用户定义的例外名。
整个过程和Oracle的等效的主要的功能型差别是,在cs_jobs上持有的排他锁将保持到调用的事务结束。
同样,如果调用者后来退出(比如说因为错误),这个过程的效果将被回滚掉。
36.11.2.其它要注意的东西
本节解释几个从OraclePL/SQL函数向PostgreSQL移植的几个其它方面的事情。
36.11.2.1.例外后的隐含回滚
在PL/pgSQL里,如果一个例外被EXCEPTION子句捕获,那么所有自这个块的BEGIN以来的数据库改变都会被自动回滚。
也就是说,这个行为等于你在Oracle里的
SAVEPOINTs1;
...codehere...
WHEN...THEN
ROLLBACKTOs1;
如果你在把这么使用SAVEPOINT和ROLLBACKTO的Oracle过程翻译过来,那么你的活儿很好干:
只要省略SAVEPOINT和ROLLBACKTO即可。
如果你要翻译的过程使用了不同的SAVEPOINT和ROLLBACKTO,那么就需要想想了。
36.11.2.2.EXECUTE
PostgreSQL版本的EXECUTE运转得类似PL/SQL的,不果你必须记住要象Section36.6.4里描述的那样用quote_literal和quote_string。
如果你不用这些函数,那么象EXECUTE'
SELECT*FROM$1'
这样的构造是不会运转的。
36.11.2.3.优化PL/pgSQL函数
PostgreSQL给你两个创建函数的修饰词用来优化执行:
"
volatility(易变的)"
(在给出的参数相同时,函数总是返回相同结果)和strictness(严格的)(如果任何参数是NULL,那么函数返回NULL)。
参考CREATEFUNCTION的手册获取细节。
如果要使用这些优化属性,那么你的CREATEFUNCTION语句可能看起来像这样:
CREATEFUNCTIONfoo(...)RETURNSintegerAS$$
...
$$LANGUAGEplpgsqlSTRICTIMMUTABLE;
36.11.3.附录
本节包含Oracle-兼容的instr,你可以用它简化你的移植过程。
--
--模拟Oracle概念的instr函数
--语法:
instr(string1,string2,[n],[m])这里的[]表示可选参数
--
--从string1的第n个字符开始寻找string2的第m个出现。
--如果n是负数,则从后向前着。
如果没有传递m,假定胃1(从第一个字符开始找)。
CREATEORREPLACEFUNCTIONinstr(varchar,varchar)RETURNSintegerAS$$
posinteger;
pos:
=instr($1,$2,1);
RETURNpos;
CREATEFUNCTIONinstr(stringvarchar,string_to_searchvarchar,beg_indexinteger)
RETURNSintegerAS$$
posintegerNOTNULLDEFAULT0;
temp_strvarchar;
beginteger;
lengthinteger;
ss_lengthinteger;
IFbeg_index>
temp_str:
=substring(stringFROMbeg_index);
pos:
=position(string_to_searchINtemp_str);
IFpos=0THEN
RETURN0;
ELSE
RETURNpos+beg_index-1;
ss_length:
=char_length(string_to_search);
length:
=char_length(string);
beg:
=length+beg_index-ss_length+2;
WHILEbeg>
0LOOP
=substring(stringFROMbegFORss_length);
IFpos>
RETURNbeg;
=beg-1;
CREATEFUNCTIONinstr(stringvarchar,string_to_searchvarchar,
beg_indexinteger,occur_indexinteger)
stringALIASFOR$1;
string_to_searchALIASFOR$2;
beg_indexALIASFOR$3;
occur_indexALIASFOR$4;
posintegerNO
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 从Oracle的 PLSQL 移植 Oracle