Oracle簡單樣本講解

預定義角色：

1.CONNECT角色:    授於終端使用者的典型最基本的權利
ALTER SESSION --修改會話
CREATE CLUSTER --建立聚簇
CREATE DATABASE LINK --建立資料庫連結
CREATE SEQUENCE --建立序列
CREATE SESSION --建立會話
CREATE SYNONYM --建立同義字
CREATE VIEW --建立視圖

2.RESOURCE角色:    是授予開發人員的
CREATE CLUSTER --建立聚簇
CREATE PROCEDURE --建立過程
CREATE SEQUENCE --建立序列
CREATE TABLE --建表
CREATE TRIGGER --建立觸發器
CREATE TYPE --建立類型

3.DBA角色：擁有系統所有系統級許可權

4.IMP_FULL_DATABASE角色、EXP_FULL_DATABASE角色：
BACKUP ANY TABLE --備份任何錶
EXECUTE ANY PROCEDURE --執行任何操作
SELECT ANY TABLE --查詢任何錶

5.DELETE_CATALOG_ROLE角色：
授予使用者這個角色，使用者就可以從表sys.aud$中刪除記錄，
sys.aud$表中記錄著審計後的記錄，使用這個角色可以簡化審計蹤跡管理。

6.SELECT_CATALOG_ROLE角色、EXECUTE_CATALOG_ROLE角色：
SELECT_CATALOG_ROLE角色具有從資料字典查詢的權利，
EXECUTE_CATALOG_ROLE角色具有從資料字典中執行部分過程和函數的權利。

SQL*PLUS基礎
在上一貼中，我們掌握了些基本的oracle操作，如建立、授權使用者，建立資料庫等。在OEM(Oracle Enterprise Manager)可視化的視窗環境中，雖然我們也可以很方便地做這些事，但是事實上，用SQL語言書寫在開發上更有效率！！oracle提供的SQL*Plus就是個不錯的工具，如果大家喜歡視窗的開發環境，用SQLPlus Worksheet也行！下面說點基本的西西！

SQL(Structure Query Language)語言是結構化查詢語言 (SQL)，是資料庫的核心語言，是面向集合的描述性非過程化語言。
SQL語言共分為四大類：資料查詢語言DQL,資料操縱語言DML，資料定義語言 (Data Definition Language)DDL,資料庫控制語言DCL。

1.資料查詢語言DQL的基本結構是由select子句，from子句，where子句組成的查詢塊：
select <欄位名表> from <表或視圖名> where <查詢條件>

2.資料操縱語言DML完成在資料庫中確定、修改、添加、刪除某一資料值的任務(以下是部分常用DML語句):
insert    增加資料行到表
delete    從表中刪除資料行
Update    更改表中資料

3.資料定義語言 (Data Definition Language)DDL完成定義資料庫的結構，包括資料庫本身、資料表、目錄、視圖等資料庫元素(以下是部分常用DDL語句)
create table    建立表
create index    建立索引
create view    建立視圖
alter table    增加表列，重定義表列，更改儲存分配
drop table    刪除表
drop index    刪除索引

4.資料庫控制語言DCL用來授予或回收訪問資料庫的某種特權，並控制資料庫操縱事務發生的時間及效果，對資料庫實行監視等。如：
grant        將許可權或角色授予使用者或其它角色
revoke        回收使用者權限
roll        復原，是當某個對話更改了資料庫中的資料後，由於某種原因使用者不想提交此更改時，oracle所採取的保護操作。這是一個把資訊恢複到使用者使update、insert、delete前最後提交的狀態。
commit        提交。在完成資料庫的插入，刪除和修改操作時，只有當事務提交到資料庫才算完成，有提交前只有操作資料庫的本人才能看到，別人只有在最後提交完成才可以看到。

接下來，我們在SQL*Plus中實戰一下，為我們下面將要做的打好基礎。
用system登陸到SQL*Plus後，我們做如下操作(這次沒有，有詳細的說明)
SQL>create user maxuan identified by max; #建立口令為max的使用者maxuan
SQL>grant connect,resource to maxuan; #為使用者maxuan授權
SQL>conn maxuan/max; #以使用者maxuan進行串連
SQL>create table test(a number); #建立一個名為test的表，只有欄位名為A的一列，資料類型為數字
SQL>insert into test values(1); #插入一條記錄
SQL>select * from test; #查詢記錄，此時A列的第一行為1
SQL>update test set a=2; #更改記錄，此時A列的第一行已改為2
SQL>commit; #提交
SQL>delete from test; #刪除test表中所有的記錄，此時test表中沒有記錄
SQL>roll; #復原到提交前，此時再查詢test表，A列第一行值又回複到2

oracle的資料類型
在資料庫中建立資料表的時候，我們需要定義表中所有欄位的類型，資料類型大致分為：character,numberic,date,lob和raw等，這些是最基本的資料類型。當然在oracle中也允許自訂資料類型！

在oracle中提供的character資料類型:
char(<size>:固定長度字串，最大長度為2000位元組，如果不指定長充，預設為1個位元組長。
varchar2(<size>:可變長度的字串，最大長度為4000位元組，具體定義時指明最大長度，這種類型可以放數字、字母以及ASCII碼字元集(或者EBCDIC等資料庫系統接受的字元集標準)中的所有符號。如果資料長度沒有達到最大值，oracle會根據資料大小自動調節欄位長度。是最長用的資料類型。
nchar(<size>:根據字元集而定的固定長度字串，最大長度2000位元組。
nvarchar2(<size>:根據字元集而定的可變長度字串，最大長度4000位元組。
long:可變長字元列，最大長度限制為2GB，用於不需要作字串搜尋的長串資料。此類型是一個遺留下來的而且將來不會被支援的資料類型，逐漸被BLOB，CLOB，NCLOB等大的資料類型所取代。

numberic資料類型用來儲存負的和正的整數，分數和浮點型資料，在oracle中提供的numberic資料類型：
number(<m>,<n>:可變長的數值列，允許0、正值及負值，m是所有的有效數位位元，n是小數點以後的位元。

在oracle中提供的date資料類型:
date:預設格式是dd-mon-yy(日-月-年)

在oracle中提供的lob資料類型:
blob、clob、nclob：三種大型物件(lob)，用來儲存較大的圖形檔案或帶格式的文字檔，如word文檔，以及音頻、視頻等非文字檔，最大長充是4GB。暈些資料存放區在資料庫內部儲存。
bfile:在資料庫外部儲存的大型二進位對象檔案，最大長度是4GB，這種外部的LOB類型，通過資料庫記錄變化情況，但是資料的具體儲存是在資料庫外部進行的。

在oracle中提供的raw資料類型:
raw(<size>:可變長位元據，具體定義欄位時必須指明最大長度，這種格式用來儲存較小的圖形檔案或帶格式的文字檔，它也是一種較老的資料類型，將被lob資料類型所取代。
long raw:可變長位元據，最大長度是2GB，可以用來儲存較大的圖形或帶格式的文字檔，以及音頻、視頻等非文字檔，這也是一種較老的資料類型，將被lob資料類型所取代。

其它的資料類型：
rowid:這是oracle資料表中的一個偽例，它是資料表中每行資料內在的唯一標識
integer:整數類型

建立購物網站後台資料庫

現在我們回到用J2EE體系開發購物網站的主題，開始實戰建購物網站的後台資料庫。
為了實現購物網站的基本的功能，我們需要建立四個表：商品列表(products)、商品類型表(item)、訂單列表(orders)和管理員列表(admin)。表結構如下所示：

item表結構（商品類型表)
欄位名稱    資料類型        允許空    主鍵/外鍵    備忘    
type_id    INTEGER(自動編號)    否    主鍵    商品類別ID標記
type    varchar2(30)    否        商品類別名稱

product表結構(商品列表)
欄位名稱    資料類型        允許空    主鍵/外鍵    備忘
product_id    INTEGER(自動編號)    否    主鍵    商品ID標記
title    varchar2(30)    否        商品名稱
type_id    INTEGER        否    外鍵    商品類別標記
info    varchar2(80)    是        商品簡介
price    number(16,2)    否        商品價格

orders表結構(訂單列表)
欄位名稱    資料類型        允許空    主鍵/外鍵    備忘
order_id    INTEGER(自動編號)    否    主鍵    訂單ID標記
name    varchar2(20)    否        顧客姓名
address    varchar2(100)    是        發貨地址
tel    number(16)    是        聯絡電話
email    varchar2(30)    否        聯絡email
btime    date        是        訂購日期
product_id    INTEGER        否    外鍵    商品標記
uword    varchar2(100)    是        顧客留言

admin表結構(管理員列表)
欄位名稱    資料類型        允許空    主鍵/外鍵    備忘
admin_id    INTEGER(自動編號)    否    主鍵    管理員ID標記
adminname    varchar2(20)    否        管理員名稱
password    varchar2(20)    否        管理員密碼

設計完表結構後，我們就要開始建立了。
建立表我想已經不是什麼難事了，那麼我們要注意的是product、item、orders這三個表之間的關聯，還有自動編號。

下面是完整的SQL語句，在後面我會給出詳細的說明，你可以在SQL*Plus裡對照著輸入，也可以將它存為SQL指令檔，在SQL*Plus或SQLPlus Worksheet裡執行。當然也可以把代碼直接拷貝到SQL*Plus裡執行！

rem ///BY MAXUAN 開始///

create table item(
type_id integer not null,
type varchar2(30),
constraint item_pk primary key(type_id)
);

create table product(
product_id integer not null,
title varchar2(30) not null,
type_id integer not null,
info varchar2(80),
price number(16,2) not null,
constraint product_pk primary key (product_id),
constraint product_fk foreign key(type_id) references item(type_id)
);

create table orders(
order_id integer not null,
name varchar2(20) not null,
address varchar2(100),
tel number(16),
email varchar2(30) not null,
btime date,
product_id integer not null,
uword varchar2(100),
constraint orders_pk primary key(order_id),
constraint orders_fk foreign key(product_id) references product(product_id)
);

create table admin(
admin_id integer not null,
adminname varchar2(20) not null,
password varchar2(20) not null,
constraint admin_pk primary key(admin_id)
);

create sequence type_id increment by 1 start with 1;
create sequence product_id increment by 1 start with 1;
create sequence order_id increment by 1 start with 1;
create sequence admin_id increment by 1 start with 1;

rem ///BY MAXUAN 結束///

說明一：建立表之間的關聯
　　product、item、orders三個表通過公用域，通常稱為鍵域(Key Field)進行關聯，存在兩種類型的鍵：主鍵(Primary key)和外部鍵(Foreign key)。主鍵使表中的資料行保持唯一，在表product中，product_id為主鍵，表orders中也包含有product_id,此時的product_id就是外部鍵。一個表的外部鍵從其它表中擷取資訊。看看上面的SQL語句，應該會了吧！建立主外鍵的SQL語句詳解如下：
constraint 主鍵名 primary key (欄位名)
constraint 外部鍵名 foreign key(欄位名) references 對應表(對應欄位)
當不指名主鍵名或外部鍵名時，系統將使用預設名稱！
如果你要刪除一個主鍵或外部鍵時，用如下寫法：
ALTER TABLE 表名 DROP CONSTRAINT 主鍵名或外部鍵名
如果是主/唯一關鍵字被外部鍵引用，先要刪除外部鍵，然後才能刪除主鍵！！

說明二：關於自動編號
　　在access中有自動編號的資料類型，MSSQL和MYSQL也都有自動成長的資料類型，插入記錄時不用操作此欄位，會自動獲得資料值，而oracle沒有自動成長的資料類型，我們需要建立一個自動成長的序號，插入記錄時要把序號的下一個值賦於此欄位，可以預見的是，有此功能，我們可以把資料從ACCESS、MSSQL或MYSQL遷移到oracle了！
create sequence type_id increment by 1 start with 1;
這句中，type_id為序號的名稱，每次增長為1，起始序號為1。
如果要刪除序列，用drop sequence 序列名就可以了！！