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 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 )
转载于:https://www.cnblogs.com/baiyixianzi/archive/2012/07/23/index_tsqlAndPLSQL.html
