资源描述：
《全面对比t-sql与pl sql》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。
1、全面对比T-SQL与PL/SQL1)数据类型TSQLPL/SQLnumeric(p,s)numeric(p,s)orNUMBER(p,s)decimal(p,s)decimal(p,s)orNUMBER(p,s)char(m)Char(m)varchar(m)varchar2(m)datetimedate记录Record表字段%type表记录%rowtype表Table自动增长变量AUTOINCREMENT2)变量声明、赋值与引用TSQLPL/SQL声明declare@ls_casherchar(1),@ln_payAmtdecimal(1
2、4,4)declareon_handINTEGER;ls_casherchar(1);赋值select@ls_casher='A'ls_casher:=’A’;引用if@ls_casher='A'…ifls_casher='A'then…在SQL语句中赋值SELECT@ls_casher=salFROMempWHEREempno=emp_id;SELECTsalINTOls_casherFROMempWHEREempno=emp_id;在SQL语句中引用SELECT*FROMempWHEREsal=@ls_casher;SELECT*FRO
3、MempWHEREsal=ls_casher;3)函数与操作符字符串TSQLPL/SQL连接+
4、
5、-8-TRIMLTRIM、RTRIMSUBSTRINGSUBSTR、SUBSTRBINSTR、INSTRBright(str,n)substr(str,-n)日期TSQLPL/SQL系统日期getdate()SYSDATE空值判断与处理TSQLPL/SQL判断ISNULLISNULL空值替换Isnull(para,0)NVL(para,0)REPLACE(old_string,NULL,my_string)转换TSQLPL/SQL字符->日期C
6、onvert(datetime,expr,style)To_Date(format,expr)字符<-日期、数值Convert(char(n),expr,style)To_char(expr,format)数值To_Number()语句TSQLPL/SQLstatementblockBEGIN...ENDBEGIN...END;conditional1)IF…ELSE…2)IF…ELSEIF…else…3)CASE1)IF..then...ELSE…endif;2)If…then…elsif…else…endif3)decodeRepeat
7、WHILEBoolean_expression{statement_block}1)Loop…exit;…endloop;2)loop…exitwhen…endloop;-8-[BREAK]{statement_block}[CONTINUE]3)WHILEconditionLOOPsequence_of_statements;EXITWHENboolean_expression;ENDLOOP;3)for…in[reverse]…loop…endloop;GOTOGOTOlabel…label:…GOTOlabel;…<
8、ExitsunconditionallyRETURNReturn;SetsadelayforstatementexecutionWAITFORComment--/*…*/--/*…*/PRINTPRINTstringSetserveroutputondbms_output.put_line(string);RAISERRORRAISERROREXECUTEEXECUTENULLstatementNULL;4)游标TSQLPL/SQLDECLAREDECLAREcursor_nameCURSOR[LOCAL
9、GLOBAL][FORWARD_O
10、NLY
11、SCROLL][STATIC
12、KEYSET
13、DYNAMIC
14、FAST_FORWARD][READ_ONLY
15、SCROLL_LOCKS
16、OPTIMISTIC][TYPE_WARNING]DECLARECURSORcursor_nameISSELECT_statement;-8-FORselect_statement[FORUPDATE[OFcolumn_name[,...n]]]openOpencursor_nameOpencursor_name;FetchFetchcursor_nameintovar1,var2…Fetchcurs
17、or_nameintovar1,var2…
18、
19、%rowtype_var;CloseClosecursor_nameClosecursor_name;Attribute@@FETC
