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)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 )