Oracle基礎 各種語句的定義格式

Oracle基礎 各種語句的定義格式

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、 子程式

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