以前一直用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
)