Oracle的基本信息詳情見 http://baike.sogou.com/v449760.htm?fromTitle=Oracle資料庫 Oracle Database[1]，又名Oracle RDBMS，或簡稱Oracle。是甲骨文公司的一款關係資料庫管理系統。到目前仍在資料庫市場上占有主要份額

Oracle

Oracle的基本信息詳情見

http://baike.sogou.com/v449760.htm?fromTitle=Oracle資料庫

Oracle簡介

Oracle Database^[1]，又名Oracle RDBMS，或簡稱Oracle。是甲骨文公司的一款關係資料庫管理系統。到目前仍在資料庫市場上占有主要份額。勞倫斯·埃里森和他的朋友，之前的同事Bob Miner和Ed Oates在1977年建立了軟體開發實驗室咨詢公司（SDL，Software Development Laboratories）。作為一個通用的資料庫系統，它具有完整的數據管理功能；作為一個關係資料庫，它是一個完備關係的產品；作為分散式資料庫它實現了分散式處理功能。但它的所有知識，只要在一種機型上學習了ORACLE知識，便能在各種類型的機器上使用它。

伺服器

Oracle Server是一個對象一關係資料庫管理系統。它提供開放的、全面的、和集成的信息管理方法。每個Server由一個 Oracle DB和一個 Oracle Server實例組成。它具有場地自治性（Site Autonomy）和提供數據存儲透明機制，以此可實現數據存儲透明性。每個 Oracle資料庫對應唯一的一個實例名SID，Oracle資料庫伺服器啟動後，一般至少有以下幾個用戶：Internal，它不是一個真實的用戶名，而是具有SYSDBA優先順序的Sys用戶的別名，它由DBA用戶使用來完成資料庫的管理任務，包括啟動和關閉資料庫；Sys，它是一個 DBA用戶名，具有最大的資料庫操作許可權；System，它也是一個 DBA用戶名，許可權僅次於 Sys用戶。

客戶端

為資料庫用戶操作端，由應用、工具、SQL* NET組成，用戶操作數據庫時，必須連接到一伺服器，該資料庫稱為本地資料庫（Local DB）。在網路環境下其它伺服器上的 DB稱為遠程資料庫（Remote DB）。用戶要存取遠程 DB上的數據時，必須建立資料庫鏈。

Oracle資料庫的體繫結構包括物理存儲結構和邏輯存儲結構。由於它們是相分離的，所以在管理數據的物理存儲結構時並不會影響對邏輯存儲結構的存取。

主要特點

完整的數據管理功能：

1）數據的大量性

2）數據的保存的持久性

3）數據的共用性

4）數據的可靠性

2、完備關係的產品：

1）信息準則---關係型DBMS的所有信息都應在邏輯上用一種方法，即表中的值顯式地表示；

2）保證訪問的準則

3）視圖更新準則---只要形成視圖的表中的數據變化了，相應的視圖中的數據同時變化

4）數據物理性和邏輯性獨立準則

3、分散式處理功能：

ORACLE資料庫自第5版起就提供了分散式處理能力，到第7版就有比較完善的分散式資料庫功能了，一個ORACLE分散式資料庫由oraclerdbms、sql*Net、SQL*CONNECT和其他非ORACLE的關係型產品構成。

4、用ORACLE能輕鬆的實現數據倉庫的操作。

這是一個技術發展的趨勢，不在這裡討論。

優點

可用性強

可擴展性強

數據安全性強

穩定性強

Oracle的基本操作

表空間

創建表空間： create tablespace 表空間名

datafile ‘表空間的地址.DBF’ size ?M

autoextend on(是否自動擴展（OFF不）);

刪除表空間： drop tablespace 表空間名;

查看表空間: select * from dba_data_files where tablespace_name=’?’

創建用戶並授權

創建新用戶步驟

a) .創建表的臨時表空間

b).創建數據的表空間

c).創建用戶

d).分配許可權

a). 創建表的臨時表空間

--刪除表空間

drop tablespace user_temp;

--創建表空間

create temporary tablespace user_temp

tempfile 'F:\Oracle\temp\user_temp.dbf'

size 10M maxsize 50M

autoextend on

next 10M maxsize 50M

extend management local;

b). 創建資料庫表空間 **/

--刪除表空間

drop tablespace user_data;

--創建表空間

create tablespace user_data

logging

datafile 'F:\Oracle\log_user_data.dbf'

size 10M

autoextend on

next 10M maxsize 50M

extend management local;

c). 創建用戶

--刪除用戶

drop users martin casade;

--創建用戶

create user users

identified user

default tablespace user_data

temporary tablespace user_temp;

--修改密碼

alter users martin identity by abc123;

d). 分配許可權

grant connect,resource,bda users;

--移除處許可權

revoke connect from users

序列

創建序列

create sequence seq_id

start with 1

increment by 1--增長間隔

maxvalue 100

cycle --達到最大值從頭生成

cache 30;--緩存

訪問：

nextval下一個

currval 當前值

刪除

drop sequence　seq_id;

生成32位編碼

select sys_guid() from dual;

PL/SQL

declare

聲明變數（變數名類型 [:=初始值]）

begin

sql語句

exception

異常處理

end;

游標

2種方法:LOOP和for

① LOOP

declare

--定義變數存放內容

names students.sname%type;

gnames grade.gname%type;

--定義游標

Cursor cur_stu IS

select s.sname,g.gname from students s inner

join grade g on s.gid=g.gid;

Begin

Open cur_stu; --打開游標

Loop --迴圈

fetch cur_stu into names,gnames;

exit

when cur_stu%Notfound;

Dbms_Output.put_line ('第'||cur_stu%Rowcount||'個學員的名字為：'||names||'，所在的班級為：'||gnames);

end Loop;

end;

② For方法

declare

--定義變數存放內容

names students.sname%type;

gnames grade.gname%type;

--定義游標

Cursor cur_stu IS

select s.sname,g.gname from students s inner join grade g on s.gid=g.gid;

Begin

For cur_record in cur_stu

Loop

Dbms_Output.put_line('第'||cur_stu%Rowcount||'個學員的名字為：'||names||'，所在的班級為：'||gnames);

end Loop;

end;

帶參顯示游標

DECLARE

CURSOR emp_cursor(no NUMBER) IS

SELECT ename,sal FROM employee WHERE deptno=no;

emp_record emp_cursor%ROWTYPE;

v_sal employee.sal %TYPE; (游標變數)

BEGIN

IF NOT emp_cursor%ISOPEN THEN

OPEN emp_cursor(10);

END IF;

LOOP

FETCH emp_cursor INTO emp_record;

EXIT WHEN emp_cursor%NOTFOUND;

dbms_output.put_line(emp_record.ename||':'||emp_record.sal);

END LOOP;

CLOSE emp_cursor;

END;

隱式游標

--%NOTFOUND屬性舉例

DECLARE

v_name employee.ename%TYPE;

BEGIN

SELECT ename INTO v_name FROM employee

WHERE empno=45;

IF SQL%NOTFOUND THEN

dbms_output.put_line('NOTFOUND-不存在該員工');

ELSE

dbms_output.put_line('存在該員工');

END IF;

EXCEPTION

WHEN no_data_found THEN

dbms_output.put_line('no_data_found-不存在該員工');

WHEN OTHERS THEN

dbms_output.put_line('其他錯誤');

END;

存儲過程

Oracle存儲過程

1.單獨賦予許可權:

grant execute on 存儲名稱 to 用戶名

2.調用存儲名稱

execute 存儲名稱(參數)

3.存儲過程(Procedure)和函數(function)的區別

不同點:

① function 有返回值,可以在Query中引用function

或使用function返回值

② 最根本: 存儲過程是命令,而函數是表達式的一部分

相同點:

① 都是PL/SQL程式

Oracle存儲過程的基本語法

1.基本結構

Create Or replace procedure 存儲名稱

(

參數1 in number,

參數2 in number

)As

變數名1 integer :=0;

變數2 Date;

Begin

End 存儲過程名稱

存儲過程調用

1. Pl/Sql中執行存儲過程在sql*plus中：

declare

--必要的變數聲明，視你的過程而定begin

execute yourprocudure(parameter1,parameter2,...);

end

2. .Oracle調用Oracle存儲過程

execute 存儲名稱(參數)

3. Hibernate調用Oracle存儲過程

this.pnumberManager.getHibernateTemplate().execute(

newHibernateCallback()...{

public Object doInHibernate(Sessionsession)

throws HibernateException,SQLException...{

CallableStatement cs=session.connection()

.prepareCall("{call modifyapppnumber_remain(?)}");

cs.setString(1,foundationid);

cs.execute();returnnull;

}

)

關於Oracle存儲過程的若幹問題備忘

1. 在Oracle中,數據表的別名不能加as

分析: select a.appname from appinfo a;--正確

select a.appname from appinfo as a;--錯誤

也許，是怕和Oracle中的存儲過程中的關鍵字as衝突的問題吧

2. 在存儲過程中，select某一欄位時，後面必須緊跟into，

如果select整個記錄，利用游標的話就另當別論了。

分析: select af.keynode into kn from APPFOUNDATION af

where af.appid=aid and af.foundationid=fid;--有into，正確編譯

----------------------------------------------------------------

select af.keynode from APPFOUNDATION af

where af.appid=aid and af.foundationid=fid;--沒有into, 編譯報錯，

提示：Compilation Error:PLS-00428:an INTO clause is expected in this SELECT statemen

3.在利用select...into...語法時，必須先確保資料庫中有該條記錄，否則會報出"nodatafound"異常。

可以在該語法之前，先利用selectcount(*)from查看資料庫中是否存在該記錄，

如果存在，再利用select...into.

4.在存儲過程中，別名不能和欄位名稱相同，否則雖然編譯可以通過，但在運行階段會報錯

分析: select keynode into kn from APPFOUNDATION

where appid=aid and foundationid=fid;--正確運行

---------------------------------------------------

select af.keynode into kn from APPFOUNDATION af

where af.appid=appid and af.foundationid=foundationid; --運行階段報錯，

提示：ORA-01422:exact fetch returns more than requested number of rows

5. 在存儲過程中，關於出現null的問題

假設有一個表A，定義如下：

create table A

(

id varchar2(50) primary key not null,

vcount number(8) not null,

bidvar char2(50) not null--外鍵

);

如果在存儲過程中，使用如下語句：

select sum(vcount) into fcount from A where bid='xxxxxx';

如果A表中不存在bid="xxxxxx"的記錄，則fcount=null

(即使fcount定義時設置了預設值，如：fcountnumber(8):=0依然無效，fcount還是會變成null)，

這樣以後使用fcount時就可能有問題，所以在這裡最好先判斷一下：

fcount is null

then

fcount:=0;

end if;

這樣就一切ok了。

用Java調用Oracle存儲過程總結

一.無返回值的存儲過程

測試表:

--Create table

create table TESTTB

(

ID VARCHAR2(30),

NAME VARCHAR2(30)

)

tablespace BOM

pctfree 10

initrans 1

maxtrans 255

storage

(

initial 64K

minextents 1

maxextents unlimited

);

分頁sql語句

select * from (

select A.* , RN from C

select * from student ) A where RN bettwen (頁數-1)*記錄數/頁 and (頁數*記錄數/頁)

刪除重覆記錄（僅保留一頁）

SQL語句：delete from stuINfo where rowId not in(

Select Max(RowId) from stuInfo

group by stuname,stuAge

having (count(stuAge||stuAge)>1)

union

select Max(rowId) from stuInfo

Group by stuName,stuAge

Having (count(stuAge||stuName)==1)

)

Oracle操作的實用語句

-- 查看ORACLE 資料庫中本用戶下的所有表
SELECT table_name FROM user_tables;

-- 查看ORACLE 資料庫中所有用戶下的所有表
select user,table_name from all_tables;

-- 查看ORACLE 資料庫中本用戶下的所有列
select table_name,column_name from user_tab_columns;

-- 查看ORACLE 資料庫中本用戶下的所有列
select user,table_name,column_name from all_tab_columns;

-- 查看ORACLE 資料庫中的序列號
select * from user_sequences;

-- 上面的所有對象，都可以通過下麵的SQL語句查詢得到
-- 查詢所有的用戶生成的ORACLE對象
SELECT * FROM user_objects;

-- 查看ORACLE 資料庫中所有表的註釋
select table_name,comments from user_tab_comments;

-- 查看ORACLE 資料庫中所有列的註釋
select table_name,column_name,comments from user_col_comments;

-- 給表加ORACLE的註釋
COMMENT ON TABLE aa10 IS '系統參數表';

-- 給列加ORACLE的註釋
COMMENT ON COLUMN aa10.aaa100 IS '參數類別';

-- 通過約束查看表名
select TABLE_NAME from all_constraints where CONSTRAINT_NAME='約束名';

或

select constraint_name,constraint_type,table_name from all_constraints where CONSTRAINT_NAME='約束名';

註意：

1.表應為：all_constraints

2.主鍵約束“PK_ID”必須大寫

-- 通過表名去查列名的長度

select * from user_tables where table_name = upper('表名')—

--查詢出用戶所有表的索引

select * from user_indexes

--查詢用戶表的索引(非聚集索引):

select * from user_indexes where uniqueness='NONUNIQUE'

--查詢用戶表的主鍵(聚集索引):

select * from user_indexes where uniqueness='UNIQUE'

--查詢表的索引

select t.*,i.index_type from user_ind_columns t,user_indexes i where t.index_name = i.index_name and t.table_name='NODE'

--查詢表的主鍵

select cu.* from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = 'P' AND cu.table_name = 'NODE'

--查找表的唯一性約束（包括名稱，構成列）：

select column_name from user_cons_columns cu, user_constraints au where cu.constraint_name=au.constraint_name and cu.table_name='NODE'

--查找表的外鍵

select * from user_constraints c where c.constraint_type = 'R' and c.table_name='STAFFPOSITION'

--查詢外鍵約束的列名：

select * from user_cons_columns cl where cl.constraint_name = 外鍵名稱

--查詢引用表的鍵的列名：

select * from user_cons_columns cl where cl.constraint_name = 外鍵引用表的鍵名

--查看當前線程數

select count(*) from v$session;

--查看最大的線程數

select value from v$parameter where name = 'sessions'

--修改最大線程數(重啟Oracle伺服器才會生效)

Alter system set session=300 scope = spfile

--查看最大進程數

select value from v$parameter where name = 'processes'

--修改最大線程數(重啟Oracle伺服器才會生效)

alter system set sessions=335 scope=spfile;

-- 通過系統表，查看表中列的屬性，包括數據類型，是否非空等
SELECT table_name,COLUMN_ID,column_name,data_type,data_length,DATA_PRECISION,NULLABLE
FROM user_tab_columns
ORDER BY table_name,COLUMN_ID;

--查看所有表空間

selecttablespace_name,sum(bytes)/1024/1024 from dba_data_files group by tablespace_name

--查看未使用表空間大小

selecttablespace_name,sum(bytes)/1024/1024 from dba_free_space group bytablespace_name;

-- 查看資料庫中表、索引占用的資料庫空間大小
SELECT * FROM user_segments;

-- 查看所有表的記錄數
CREATE TABLE table_count(table_name VARCHAR2(50),columns NUMBER(20));

-- 通過PB運行下麵的語句，得到結果集，將結果集在PB下執行，最後提交
select 'insert into table_count values('''||table_name||''', (select count(1)from '||table_name||'));//'||comments from user_tab_comments;

-- 所有表的記錄都在table_count了
SELECT * FROM table_count;

// 將ORACLE資料庫的註釋同步到PB中代碼開始
DELETE FROM PBCATCOL WHERE PBC_TNAM LIKE '%';
DELETE FROM PBCATTBL WHERE PBT_TNAM LIKE '%';

INSERT INTO PBCATTBL
( PBT_TNAM,
PBT_OWNR ,
PBT_CMNT)
SELECT ALL_TAB_COMMENTS.TABLE_NAME,
ALL_TAB_COMMENTS.OWNER,
ALL_TAB_COMMENTS.COMMENTS
FROM ALL_TAB_COMMENTS
WHERE ALL_TAB_COMMENTS.OWNER = 'LH'
AND TABLE_NAME LIKE '%';

// 同步欄位名

INSERT INTO PBCATCOL
( PBC_TNAM,
PBC_OWNR,
PBC_CNAM,
PBC_LABL,
PBC_CMNT,
PBC_HDR)
SELECT ALL_COL_COMMENTS.TABLE_NAME,
ALL_COL_COMMENTS.OWNER,
ALL_COL_COMMENTS.COLUMN_NAME,
ALL_COL_COMMENTS.COMMENTS ,
ALL_COL_COMMENTS.COMMENTS ,
ALL_COL_COMMENTS.COMMENTS
FROM ALL_COL_COMMENTS
WHERE ALL_COL_COMMENTS.OWNER = 'LH'
AND TABLE_NAME LIKE '%';

COMMIT;
-- 將ORACLE資料庫的註釋同步到PB中代碼結束

--將PB註釋同步到ORACLE中
select 'comment on table '||pbt_tnam||' is '''||pbt_cmnt||''';' from pbcattblwhere pbt_tnam not like 'PB%'
UNION
select 'comment on column '||pbc_tnam||'.'||pbc_cnam||' is '''||pbc_cmnt||''';'from pbcatcol where pbC_tnam not like 'PB%';

--查進程
select object_id,session_id,locked_mode from v$locked_object;

selectt2.username,t2.sid,t2.serial#,t2.logon_time
from v$locked_object t1,v$session t2
where t1.session_id=t2.sid order by t2.logon_time;

--殺進程
alter system kill session '3,6666';

oracle操作語句：

1.創建表

create table 表名(

列名1 類型，

列名2 類型

);

2.修改類屬性

alter table 表名 modify(列名類型);

3.添加列

alter table 表名 add(列名類型);

4.添加主鍵約束和非空約束

alter table 表名 add constraint pk_表名 primary key(列名);

alter table 表名 modify(列名 not null);

5.刪除主鍵約束

alter table 表名 drop primary key;

alter table 表名 drop constraint pk_表名;

6.失效約束

alter table 表名 disable primary key;

alter table 表名 disable constraint pk_表名;

7.有效約束

alter table 表名 enable primary key;

alter table 表名 enable constraint pk_表名;

8.刪除列

alter table 表名 drop column 列名;

9.設置某列不可用，然後刪除

alter table 表名 set unused(列名);

alter table 表名 drop unused columns;

10.修改表名

rename 表名1 to 表名2

alter 表名1 rename to 表名2;

11.截斷表

truncate table 表名;

12.截斷表保留行空間

truncate table 表名 resue storage;

13.查看表結構

desc table 表名;

14.刪除表

drop table 表名;

15.插入記錄

例：insert into 表名 values(內容1,內容2,內容3,內容4);

16.帶參數對話方式插入行

例:insert into 表名 values(&列名1,&列名2);

insert into 表名 values(內容1,內容2);

17.插入某幾列記錄

insert into 表名(列名1,列名2) values(內容1,內容2);

18.為列插入空值（其列不能為not null）

insert into 表名 values(內容1,null,null);

19.創建表（包括主鍵及外鍵設置）方法一

create table 表名(

列名1 類型

constraint pk_表名 primary key,

列名2 類型 not null,

列名3 類型

constraint fk_表名 reference 表名(列名),

列名3 類型

constraint ck_表名 check(列名3 in(''內容1'',''內容2'',''內容3''))

);

20.查詢所有行

select * from 表名;

21.查詢某幾列

select 列名1,列名2 from 表名;

22.重覆行消除

select distict 列名 from 表名;

23.where語句查詢

select * from 表名 where 條件 order by 列名;

(註：如number類型查出自動按升序排列，如要按降序排列，則select * from 表名 where 條件 order by 列名desc;)

24.創建表,方法二

create table 表名(

列名1 類型 primary key,

列名2 類型 not null,

列名3 類型check(列名3 in('''','''','''')),

列名4 類型 refernce 表名(列名)

);

25.修改列=‘？’的數據

update 表名 set (列=？) where 列=‘？’;

26.刪除行

delete from 表名 where 條件;

27.事務處理

--事務處理

update 表名

set 列名（日期） = ''30-5月-98''

where 條件;

savepoint mark1;

delete from 表名 where 條件;

savepoint mark2;

rollback to savepoint mark1;

rollback;

28.建立用戶user1，密碼為password

授予用戶connect,resource的許可權

connect角色用於登錄

resource角色用於建表等.

connect system/manager

create user user1 identified bypassword;

grant connect,resource to password;

29.數據控制語言

connect scott/tiger

30.把對錶1查詢和修改的許可權授予user1

grant select,update on 表1 to user1;

31.把對錶表1中列1和列2修改的許可權授予user1

grant update(列1,列2) on 表1 to user1;

32.把對錶表1查詢的許可權授予用戶user1

並且user1用戶還可以把這個許可權授予別的用戶(with grant option)

grant select on 表1 to user1 with grant option;

33.從用戶user1撤銷對錶1查詢和修改的許可權

revoke select,update on 表1 from user1;