以前一直用mssql 做开发,最近的项目都Oracle,
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)cursor
|
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)trigger
|
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)procedure
|
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
)