Oracle內建數據類型
一、 字符數據
1、 char(size)
2、 varchar2(size) 最常用，最大長度4000字節
3、 nvhar(size)、nvarchar(size)
4、 varchar(size)
5、 long 建議使用LOB大型數據
6、 raw 存儲二進制，建議使用LOB大型數據
二、 數字
1、 number(p,s)
三、 日期
1、 data
2、 timestamp
3、 timestamp with time zone
4、 timestamp with local time zone
5、 interval year to month
6、 interval day to second
四、 大型對象數據類型
BLOB、CLOB、NCLOB、BFILE 最大長度4G
五、 ANSI、DB2、SQL/DS
六、 用戶自定義類型
create type
create type body
SQL語句分類
一、 數據查詢語句（DQL）
SELECT
二、 數據操縱語句（DML）
INSERT、UPDATE、DELETE
三、 數據定義語句（DDL）
CREATE、ALTER、DROP
四、 數據控制語句（DCL）
GRANT、REVOKE、COMMIT、ROLLBACK、SAVEPOINT
具體SQL語句
一、 create table
創建表
create table<table_name>
(
col_name<datatype>,
col_name<datatype>,
col_name<datatype>
)
二、 alter table
修改表
alter table<table_name>
[add<col_name datatype>]
[modify<col_name datatype>]
[drop column<column_name>]
三、 rename
重命名表
rename old_table_name to new_table_name
四、 truncate table
刪除表中存在的信息，只保留表結構，刪除數據不可恢復
truncate table <table_name>
五、 drop table
刪除表
六、 drop table <table_name>
七、 select
選擇語句
select <column_list>
from <table_name>
[where <codition>]
[group by <group_by expression >]
[having <group_condition>] ——組函數只能寫having之後
[order by<col_name>]
1. dual表
2. sysdate系統時間
3. where條件中用到的運算
+-*/
||連接
=、!=、^=、<>、>、>=、<、<=、any，some，all
not、and、or
in（相當與any）、not in（相當於!=all）
between x and y
not between x and y
is null、is not null （可使用nvl()函數將null轉換成需要的值）
exist
like（_表示一個字符，%表示0個或多個字符）
若字符串中包含“_”“%”，可使用escape ‘\’，如
like ‘%s\_t%’ escape ‘\’用來匹配“s_t”字符串
集合運算符
4. 常用函數
initcap首字母大寫、
instr查找字符位置、l
ength字符長度、
lower轉化為小寫、
upper轉化為大寫、
lpad在左側填充特定長度、
rpad在右側填充特定長度、
rtrim剪切字符及其右側字符、
lrtrim剪切字符及其左側字符、
soundex、發音相似單詞
substr、字符截取
chr、ascii碼代表的字符
ascii、字符的ascii碼
translate、字符替換
repleace、字符或字符串替換
abs絕對值、
ceil向上取整、
floor向下取整、
sqrt平方根、
power乘方、
sign數字正負、
trunc截去小數、
round四捨五入、
exp常數e的次冪、
mod余數、
ln自然對數值、
log以10為底的對數值、
vsize存儲空間、
greatest一組值中的最大、
least一組值中的最大、
add_months在一個日期上加上或減去指定月份、
last_day返回指定月份的最後一天的日期、
next_day返回下一個指定日期的第一天、
months_between兩個日期相隔的月份數、
trunc
日期格式：
SYEAR公元紀年、
YY年、
Q季、
MM月,
RM羅馬月,
Month英文月份,
WW當年第幾周,
W當月第幾周,
DDD當年第幾天,
DD當月第幾天,
D周內第幾天,
DY星期,
HH,HH12,12進制小時數
HH2424小時小時數,
MI分鐘數（0~59）,
SS秒數（0~59））
to_char將日期轉化為字符、
to_date將字符轉化為日期、
to_number將數字轉化為字符、
decode將特定數據轉變成另一種表示
5. 組函數
avg平均數、忽略null
count查詢行數、
max最大值，忽略null、
min最小值，忽略null、
stddev標准差，忽略null、
sum總和，忽略null、
variance方差，忽略null
6. rowin是存儲每條記錄的實際物理地址，對記錄的訪問是基於rowid的，這是存取表中數據的最快的方法。
7. where字句不能夠對group by的結果進行限定，需要用having限定。
8.
八、 insert
insert into <table_name>[col_name, col_name, col_name,…,]
values(value, value, value, value,…);
在sqlplus下，可以用&字符進行參數替換，可以通過工具逐個輸入insert的值
九、 update
update <table_name>
set col_name=value/expression, col_name=value/expression
[where <conditions>]
十、 delete
delete from <table_name>
[where <condition>];
或delete <table_name>
[where <condition>];
truncate和delete的區別：
a、delete可以使用rollback命令進行撤銷，而truncate不可
b、truncate不能觸發任何delete觸發器。
十一、 約束
1、 unique
tel_number char(10) constraint cm_unique unique,
組合constraint cm_unique unique(tel_number,online_email),
alter table cm add constraint cm_unique unique(tel_number),
2、 check
tel_number char(14) check(length(tel_number)=14),
3、 not null
tel_number char(14) not null,
4、 primary key
tel_number char(14) constraint cm_primary primary key,
5、 foreigh key
constraint emp1_foreign foreign key(deptno) references dept1(deptno),
級聯刪除：
deptno number(2) references dept1(deptno) [on delete set null] 刪除子記錄時主記錄相應值為null
constraint emp1_foreign foreign key(deptno) references dept1(deptno) [on delete cascade] 刪除子記錄時級聯刪除主記錄
若不寫則子記錄存在不可刪除主記錄。
十二、 連接
1、 左連接:
表示左表中指定的內容全部返回
select e.ename,d.dname
from emp1 e,dept d
where e.deptno = d.deptno(+)
2、 右連接:
表示右表中指定的內容全部返回
select e.ename,d.dname
from emp1 e,dept d
where e.deptno(+) = d.deptno
3、 自連接
select worker.ename,manager.ename
from emp worker,emp manager
where worker.mgr = manager.empno
4、 嵌套查詢
子查詢中不能有order by分組語句；
oracle中使用exists比使用in查詢速度快。因為在使用exists時，系統會先檢查主查詢，然後運行子查詢知道它找到第一個匹配項；而在系統在執行in語句時，會先執行子查詢並將結果放到一個加了索引的臨時表，在執行子查詢之前，系統先將主查詢掛起。
5、 集合運算
union all：結合兩個select語句結果，可以有重復
union：結合兩個select語句結果，消除任何相同的行
minus：從第一個select結果中消除第二個select結果
intersect：只返回同時出現在兩個select語句中的行
十三、 PL/SQL
declare
<declarations section>
begin
<executable command>
declare
<declarations section>
begin
<executable command>
end;
end;
1、 定義變量和sql定義相似
variable_name [constant] datatype [not null][{:=|default} default_value]
定義常量時使用constant。
2、 交互式輸入變量值
v_empno number(4):=&v_empno;
3、 打印語句
dbms_output.put_line(v_empno);
4、 顯示記錄
type record_name is record(field_definition_list);
例：
type t_emp is record
(
v_empno emp.empno%type,
v_ename emp.ename%type
);
5、 隱式記錄
v_emp emp%rowtype;
6、 index_by表
type type_name is table of element_type [not null] index by binary_interger;
declare
type table_empno_type is table of emp.empno%type index by binary_integer;
table_empno table_empno_type;
i binary_integer:=1;
begin
select empno
into table_empno(i)
from emp
where empno=7369;
end;
7、 可變數組
type type_name is [varray|varying array] (max_size) of element_type [not null]
declare
type varray_empno_type is varray(5) of emp.emono%type;
varray_empno varray_empno_type;
begin
varray_empno:=varray_empno_type(7369.7499);
8、 集合的方法
count：集合中的元素個數
delete：刪除集合中所有元素
delete(x)：刪除下標為x的元素
delete(x,y)：刪除下標從x到y的元素
extend：在集合末尾添加一個元素
extend(x)：在集合末尾添加x個元素
extend(x,n)：在集合末尾添加n個x的副本
first：返回第一個元素的下標號，對於varray始終返回1
last：返回最後一個元素的下標號
limit返回可變數組集合的最大的元素個數
next：返回x之後的元素
prior：返回x之前的元素
trim：從末端刪除一個元素
trim(x)：從末端刪除x個元素
http://www.cnblogs.com/roucheng/
9、 動態sql
excute immediate 動態SQL語句 using 綁定參數列表 returning into輸出參數列表；
str_sql:=’create table’||’ ’||table_name||’(’||field1||’ ’||’datatype1’||’,’|| field2||’ ’||’datatype2’||’)’;
execute immediate str_sql；
10、 if條件語句
if condition then
sequence_of_statements
end if;
if condition then
sequence_of_statement
else
sequence_of_statement
end if;
if condition then
sequence_of_statement
else if condition2 then
sequence_of_statement
else
sequence_of_statement
end if;
11、 case語句
case selector
when expression then sequence_of_statements;
when expression then sequence_of_statements;
when expression then sequence_of_statements;
[else sequence_of_statements;]
end case;
12、 loop循環
loop
sequence_of_statements
if a>0 then
exit 或exit when a>0
end if;
end loop;
13、 for-loop語句
for counter in [reverse] lower_bound..higher_bound loop
sequence_of_statement
end loop;
如：
for I in 1..v_count loop
list(i) :=i*I;
end loop;
14、 while-loop語句
while condition loop
sequence_of_statements
end loop;
15、 游標定義
a、cursor cursor_name [(parameter[,parameter]…)]
[return return_type] is select_statement
b、open cursor_name
c、fetch cursor_name into variable[,variable,…]
d、close cursor_name
例：
declare
cursor c_emp_ename is select ename form emp;
v_ename emp.ename%type;
v_count binary_integer;
begin
select count(rowed)
into v_count
from emp;
open c_emp_ename;
for I in i..v_count loop
fetch c_emp_ename into v_ename;
dbms_output.put_line(vname);
end loop;
close c_emp_ename;
end
16、 cursor for循環及其替代語句
a、 先定義游標，之後用in(cursor_name)的方式使用該循環
cursor cursor_dept is select deptno ,dname from dept order by deptno;
for var in cursor_dept loop
在這裡可以使用var來得到游標所指數據
end loop
b、 采用in(查詢語句)的方式使用該循環
for var in(select deptno ,dname from dept order by deptno;) loop
在這裡可以使用var來得到游標所指數據
end loop
17、 顯示游標屬性
%found：if c_emp_ename %fount then … end if;
% notfount：exit when c_emp_ename %notfound;
%isopen：if c_emp_ename % isopen then … end if;
%rowcount：提取次數if c_emp_name %rowcount >10 then … end if
18、 隱式游標（SQL游標）
用來處理insert、update、delete和返回一行的select into語句，對這幾個語句進行操作時判斷處理結果用的。
不可使用open、fetch和close進行操作。
也包含%fount、%notfount、%isopen(總是false)、%rowcount。
19、 異常處理
a、 異常的拋出方式
pl/sql運行時
raise exception_name
調用raise_application_erroe
b、 exception
when exception_name then
處理代碼；
when exception_name then
處理代碼；
when others then
處理代碼；
c、 自定義異常
declare
exceptin_name exception;
begin
statements;
raise <exception_name>
exception
when <exception_name> then
end;
20、 子程序
http://www.cnblogs.com/roucheng/
1、 存儲過程
create [or replace] procedure <procedure_name>
(<arg1[in|out|in out] ,datatype,……>)
is|as
[local declaration]
begin
executable statements
[exception handler]
edn [procedure_name]
2、 函數
create [or replace] function <function_name>
(<arg1[mode],datatype>,……)
return<datatype> is|as
[local declaration]
begin
executable statements
[exception handler]
end [function_name]
函數和過程都可以通過參數列表接收或返回另個或多個值；函數和過程的主要區別在於他們的調用方式，過程是作為一個獨立的執行語句調用的，而調用函數後需將函數的返回值賦值給某一變量。
3、 包
包定義：
create [or replace] package package_name {as|is}
public_variable_declarations|
public_type_declarations|
public_exception_declarations|
public_cursor_declarations|
function_declarations聲明|
procedure_specifications聲明
end [package_name]
包主體：
create [or replace] package body package_name {as|is}
public_variable_declarations|
public_type_declarations|
public_exception_declarations|
public_cursor_declarations|
function_declarations實現|
procedure_specifications實現
end [package_name]
4、 觸發器
create [or replace] trigger trigger_name 觸發事件 觸發事件
on {table_or_view_name|database}
[referencing [old [as] <old_name>][new [as] <new_name>]] //更新時用
[for each row [when condition]] //加上則為行級觸發，否則為語句級觸發
trigger_body
觸發時間：
before：數據庫動作之前觸發器執行。
after：數據庫動作之後觸發器執行
instead of：觸發器被觸發，但相應的操作並不被執行，而運行的僅是觸發器SQL語句本身。用在 使不可被修改的視圖能夠支持修改。
觸發事件：
insert on：向表或視圖插入一行時
update of：更新表或視圖某一行時
delete on：刪除表或視圖某一行時
create：創建一個數據庫對象時
alter：修改一個數據庫對象時
drop：刪除一個數據庫對象時
start：打開數據庫時觸發觸發器，在事件後觸發
shutdown：關閉數據庫時觸發觸發器，在事件前觸發
logon：當一個會話建立時觸發，事件前觸發
logoff：關閉會話時觸發，事件前觸發
server：服務器錯誤發生時觸發，事件後觸發。
條件謂詞：
inserting、updationg、deleting
