全面对比T-SQL与PL-SQL.doc

全面对比T-SQL与PL/SQL
1)数据类型

TSQL
PL/SQL

numeric(p,s)
numeric(p,s) or NUMBER(p,s)

decimal(p,s)
decimal(p,s) or NUMBER(p,s)

char(m)
Char(m)

varchar(m)
varchar2(m)

datetime
date
记录

Record
表字段

%type
表记录

%rowtype
表

Table
自动增长变量
AUTOINCREMENT

2)变量声明、赋值与引用

TSQL
PL/SQL
声明
declare
@ls_casher char(1),
@ln_payAmt decimal(14,4)
declare
on_hand INTEGER;
ls_casher char(1);
赋值
select @ls_casher = 'A'
ls_casher:=’A’;
引用
if @ ls_casher = 'A'
…
if ls_casher = 'A' then
…
在SQL语句中赋值
SELECT @ls_casher=sal FROM emp WHERE empno = emp_id;
SELECT sal INTO ls_casher FROM emp WHERE empno = emp_id;
在SQL语句中引用
SELECT * FROM emp WHERE sal = @ls_casher;
SELECT * FROM emp WHERE sal = ls_casher;

3)函数与操作符
字符串

TSQL
PL/SQL
连接
+
||

TRIM
LTRIM、RTRIM

SUBSTRING
SUBSTR、SUBSTRB

INSTR、INSTRB

right(str,n)
substr(str,-n)

日期

TSQL
PL/SQL
系统日期
getdate()
SYSDATE

空值判断与处理

TSQL
PL/SQL
判断
IS NULL
IS NULL
空值替换
Isnull(para,0)
NVL(para,0)
REPLACE(old_string, NULL, my_string)

转换

TSQL
PL/SQL
字符->日期
Convert(datetime, expr, style)
To_Date(format, expr)
字符<-日期、数值
Convert(char(n), expr, style)
To_char(expr,format)
数值

To_Number()
语句

TSQL
PL/SQL
statement block
BEGIN...END
BEGIN...END;
conditional
1) IF…ELSE…
2) IF…ELSE IF…else…
3) CASE
1)IF..then...ELSE…end if;
2)If…then…
elsif…else…endif
3)decode
Repeat
WHILE Boolean_expression
{statement_block}
[BREAK]
{statement_block}
[CONTINUE]

1)Loop …exit;…end loop;
2)loop…exit when…end loop;
3)WHILE condition LOOP
sequence_of_statements;
EXIT WHEN boolean_expression;
END LOOP;
3)for…in [reverse]…loop
…
end loop;
GOTO
GOTO label
…
label:
…
GOTO label;
…
<<label>>
…
Exits unconditionally
RETURN
Return;
Sets a delay for statement execution
WAITFOR

Comment
--
/*…*/
--
/*…*/
PRINT
PRINT string
Set serveroutput on
dbms_output.put_line(string);
RAISERROR
RAISERROR

EXECUTE
EXECUTE

NULL statement

NULL;

4)游标

TSQL
PL/SQL
DECLARE
DECLARE cursor_name CURSOR
[LOCAL | GLOBAL]
[FORWARD_ONLY | SCROLL]
[STATIC | KEYSET | DYNAMIC | FAST_FORWARD]
[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC]
[TYPE_WARNING]
FOR select_statement
[FOR UPDATE [OF column_name [,...n]]]
DECLARE
CURSOR cursor_name IS
SELECT_statement;
open
Open cursor_name
Open cursor_name;
Fetch
Fetch cursor_name into
var1,var2…
Fetch cursor_name into
var1,var2…
||
%rowtype_var;
Close
Close cursor_name
Close cursor_name;
Attribute
@@FETCH_STATUS
@@CURSOR_ROWS
CURSOR_STATUS
%found
%notfound
%isopen
%rowcount
DEALLOCATE
DEALLOCATE cursor_name

隐式cursor

Select…into (仅可处理单行记录)

5)触发器

TSQL
PL/SQL
创建
CREATE TRIGGER trigger_name
ON table
[WITH ENCRYPTION]
{FOR {[DELETE][,][INSERT][,] [UPDATE] }
AS
sql_statement [...n]
}
Create or replace trigger t_name
{before|after}{insert|update|delete}
on table_name
[for each row [when conditional]
…
类型(按触发级别和时序)
语句
after
行或语句
before or after
访问数据操纵行的值
通过表Inserted、Deleted访问
通过记录 :New、 :Old访问，仅可用于行级触发器
谓词/函数/属性
Inserting、updating、deleteing
Updating(col)
Update(col)
使能
Alter table tabname {disable|enable} trigger {t_name|all}
Alter trigger t_name {disable|enable}
限制

作为触发语句的一部分，不可用事务控制命令
不能声明和使用LONG、LONG RAW变量和列
删除
Drop trigger t_name
Drop trigger t_name;

6)过程

TSQL
PL/SQL
创建
CREATE PROCEDURE] p_name
[ {@parameter data_type} [VARYING] [= default] [OUTPUT] ] [,...n]
[WITH
{
RECOMPILE | ENCRYPTION
| RECOMPILE, ENCRYPTION
}
]
AS
sql_statement [...n]
Create or replace procedure p_name
[Para1 {in|out|inout} datatype[,…]
[{:=|default} default_value]]
{IS|AS}
…
查询

删除
DROP PROCEDURE p_name
DROP PROCEDURE p_name;
调用
EXEC p_name [para1[,…]]
P_name[(para1[,…])];
参数
按位置传递
1)按位置传递
2)带名传递
P_name(para1=>var1);

debit_account(amount => 500, acct_id => 10261);
7)数据字典/系统表

TSQL
PL/SQL
系统对象表
Dbo.sysobjects
User_source、User_objects(OBJ)、User_tables(TABS)、User_triggers、ALL_tables、All_View、All_catalog、All_objects
对象脚本
sp_helptext
DESC、ALL_source
用户表
Sysusers
All_users
表列

All_tab_columns
依赖

All_dependencies
字典表说明

DICT

8)SQL

TSQL
PL/SQL
Select
Select @var=<value>

Select value into var from dual

Insert
insert / insert into
insert into
Delete

比较

Any, some, all
集合

Union、Union all、Intersect、Minus、
9)全局变量

TSQL
PL/SQL
语句执行成功
error
SQLCODE
select 是否有结果
exists
select...into + SQL%FOUND

10) 命令行查询工具

ISQL
SQL PLUS
读取、执行SQL文件
Isql –Usa –Ppass –Shost –ifile
sqlplus [-s] user/pass@db -@filename

11) 杂项

TSQL
PL/SQL
锁
在SQL语句中
Insert…With tablock
Insert…With Tablockx
Select…for update
Select…for readonly
独立语句
set transcation isolation level to Read uncommited

在SQL语句中
select …for update of…;

独立语句
lock table tabname in row share mode;
lock table tabname in share exclusive mode;

用户连接数

数据库文件
Device
Tablespace

CREATE TABLESPACE testdb DATAFILE 'C:\ORANT\DATABASE\testdb.ORA' SIZE 20M AUTOEXTEND ON NEXT 2M;
CREATE ROLLBACK SEGMENT "RB_TESTDB" TABLESPACE "TESTDB";
ALTER ROLLBACK SEGMENT "RB_TESTDB" ONLINE;

显示DML执行计划
Show plan
Explain plan
保留点
¨ Save transcation Sp_name
¨ ROLLBACK TRANSACTION percentchanged
¨ Savepoint Sp_name
¨ Rollback to savepoint sp_name
对模式对象改名
Rename

分析对象
Analyze
Sp_help?

1. select into 语法

现在有表

tablea
(
cola int ,
colb varchar(20)
)

要把tablea中满足条件(cola <100)的记录生成新的表tableb。

在ms sqlserver 可以直接用select into语法：
select * into tableb
where cola < 100

在oracle中语法如下：

create table tableb
as
(
select * from tablea
where cola <100
)

- 8 -