资源描述：
《tsql与plsql的比较》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。
1、TSQL与PL/SQL的比较1)数据类型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(14,4)decl
2、areon_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*FROMempWHEREsal=l
3、s_casher;3)函数与操作符字符串TSQLPL/SQL连接+
4、
5、TRIMLTRIM、RTRIMSUBSTRINGSUBSTR、SUBSTRBINSTR、INSTRBright(str,n)substr(str,-n)日期TSQLPL/SQL系统日期getdate()SYSDATE数值TSQLPL/SQL空值判断与处理TSQLPL/SQL判断ISNULLISNULL空值替换Isnull(para,0)NVL(para,0)REPLACE(old_string,NULL,my_string)转换TSQLPL/SQL字符->日期Convert(date
6、time,expr,style)To_Date(format,expr)字符<-日期、数值Convert(char(n),expr,style)To_char(expr,format)数值To_Number()其它TSQLPL/SQL1)语句TSQLPL/SQLstatementblockBEGIN...ENDBEGIN...END;conditional1)IF…ELSE…2)IF…ELSEIF…else…3)CASE1)IF..then...ELSE…endif;2)If…then…elsif…else…endif3)decodeRepeatWHI
7、LEBoolean_expression{statement_block}[BREAK]{statement_block}[CONTINUE]1)Loop…exit;…endloop;2)loop…exitwhen…endloop;3)WHILEconditionLOOPsequence_of_statements;EXITWHENboolean_expression;ENDLOOP;3)for…in[reverse]…loop…endloop;GOTOGOTOlabel…label:…GOTOlabel;…<
8、tionallyRETURNReturn;SetsadelayforstatementexecutionWAITFORComment--/*…*/--/*…*/PRINTPRINTstringSetserveroutputondbms_output.put_line(string);RAISERRORRAISERROREXECUTEEXECUTENULLstatementNULL;2)cursorTSQLPL/SQLDECLAREDECLAREcursor_nameCURSOR[LOCAL
9、GLOBAL][FORWARD_ONLY
10、SCROLL][ST
11、ATIC
12、KEYSET
13、DYNAMIC
14、FAST_FORWARD][READ_ONLY
15、SCROLL_LOCKS
16、OPTIMISTIC][TYPE_WARNING]FORselect_statement[FORUPDATE[OFcolumn_name[,...n]]]DECLARECURSORcursor_nameISSELECT_statement;openOpencursor_nameOpencursor_name;FetchFetchcursor_nameintovar1,var2…Fetchcursor_nameintovar1,var2…
17、
18、
19、%rowtype_var;CloseClosecursor_nameClosecursor_n
