TSQL与PLSQL的比较分析和总结.docx

TSQL与PL/SQL的比较

数据类型

记录表字段表记录表

自动增长变量

TSQL

numeric(p,s)decimal(p,s)char(m)varchar(m)datetime

AUTOINCREMENT

PL/SQL

numeric(p,s)orNUMBER(p,s)decimal(p,s)orNUMBER(p,s)Char(m)

varchar2(m)date

Record

%type

%rowtypeTable

变量声明、赋值与引用

声明

declare

TSQL

declare

PL/SQL

赋值引用

在SQL语句中赋值在SQL语句中引用

TSQLPL/SQL连接

TSQL

PL/SQL

连接

+TRIM

SUBSTRING

right(str,n)

||

LTRIM、RTRIMSUBSTR、SUBSTRBINSTR、INSTRB

substr(str,-n)

@ls_casherchar(1),@ln_payAmtdecimal(14,4)select@ls_casher=A

if@ls_casher=A

…

SELECT@ls_casher=salFROMempWHEREempno=emp_id;

SELECT*FROMemp

WHEREsal=@ls_casher;

on_hand INTEGER;ls_casherchar(1);

ls_casher:=’A’

ifls_casher=Athen

…

SELECTsalINTOls_casherFROMempWHEREempno=emp_id;

SELECT*FROMempWHERE

sal=ls_casher;

日期

TSQL

TSQL

PL/SQL

系统日期

getdate()

SYSDATE

数值

TSQL

TSQL

PL/SQL

空值判断与处理

TSQL

TSQL

PL/SQL

判断

空值替换

ISNULL

Isnull(para,0)

ISNULL

NVL(para,0)REPLACE(old_string,NULL,my_string)

转换

字符-日期

字符-日期、数值数值

其它

TSQL

Convert(datetime,expr,style)Convert(char(n),expr,style)

PL/SQL

To_Date(format,expr)To_char(expr,format)To_Number()

TSQL

TSQL

PL/SQL

语句

statementblock

TSQLBEGIN...END

BEGIN...END;

PL/SQL

conditional

Repeat

IF…ELSE…

IF…ELSEIF…else…

CASE

WHILEBoolean_expression

{statement_block}[BREAK]

{statement_block}[CONTINUE]

IF..then...ELSE…endif;

If…then…elsif…else…endif

3)decode

1)Loop…exit;…endloop;2)loop…exitwhen…endloop;3)WHILEconditionLOOP

sequence_of_statements;

EXITWHENboolean_expression;ENDLOOP;

3)for…in[reverse]…loop

…

endloop;

GOTO

GOTOlabel

…

GOTOlabel;

…

label:

…

label

…

Exitsunconditionally

RETURN

Return;

Sets a delay for

statement

WAITFOR

execution

Comment

--

/*…*/

--

/*…*/

PRINT

PRINTstring

Setserveroutputon

dbms_output.put_line(string);

RAISERROR

RAISERROR

EXECUTE

EXECUTE

NULLstatement

NULL;

5)cursor

TSQL

PL/SQL

DECLARE DECLAREcursor_nameCURSOR DECLARE

[LOCAL|GLOBAL] CURSORcursor_nameIS[FORWARD_ONLY|SCROLL] SELECT_statement;[S