Oracle SQL學習筆記本: 1月 2014

PURGE TABLESPACE介紹

語法說明

可從Recycle Bin(資源回收筒)刪除指定的資料表空間

格式

PURGE TABLESPACE tablespace_name

[USER user_name];

tablespace_name : 資料表空間名稱

user_name : 使用者名稱,指定USER子句可使使用者得以再度使用資料表空間

語法示範

說明 : 從Recycle Bin刪除emp資料表空間

purge tablespace emp;

PURGE介紹

語法說明

可從Recycle Bin(資源回收筒),刪除特定的表格物件或索引物件,釋放已刪除物件的一切關聯空間

格式

PURGE {TABLE | INDEX} object_name;

TABLE : 表格型態物件

INDEX : 索引型態物件

object_name : 物件名稱

語法示範

說明 :從Recycle Bin刪除emp2表格

purge table emp2;

DROP PROFILE介紹

語法說明

刪除使用者的控制檔

格式

DROP PROFILE profile_name [CASCADE];

profile_name : 控制檔名稱

語法示範

說明 :刪除控制檔hr_user_profile,解除所有關聯至此控制檔的使用者

drop profile hr_user_profile cascade;

ALTER PROFILE介紹

語法說明

針對使用者的控制檔作變更

格式

ALTER PROFILE profile_name LIMIT

[FAILED_LOGIN_ATTEMPTS {times | UNLIMITED}]

[PASSWORD_LIFE_TIME {days | UNLIMITED}]

[{PASSWORD_REUSE_TIME | PASSWORD_REUSE_MAX}

{days_or_times | UNLIMITED}]

[PASSWORD_LOCK_TIME {days | UNLIMITED}]

[PASSWORD_GRACE_TIME {days | UNLIMITED}]

[PASSWORD_VERIFY_FUNCTION {function_name | NULL | DEFAULT}]

[SESSIONS_PER_USER {n | UNLIMITED | DEFAULT}]

[CPU_PER_SESSION {n | UNLIMITED | DEFAULT}]

[CPU_PER_CALL {n | UNLIMITED | DEFAULT}]

[CONNECT_TIME {n | UNLIMITED | DEFAULT}]

[IDLE_TIME {n | UNLIMITED | DEFAULT}]

[LOGICAL_READS_PER_SESSION {n | UNLIMITED | DEFAULT}]

[COMPOSITE_LIMIT {n | UNLIMITED | DEFAULT}]

[PRIVATE_SGA {n | UNLIMITED | DEFAULT}];

profile : 控制檔名稱

times : 次數

days : 天數

days_or_times : 天數或次數

function :　函數名稱

n : 設定數值

FAILED_LOGIN_ATTEMPTS : 容許登入失敗的次數

PASSWORD_LIFE_TIME : 可使用相同密碼認證的天數

PASSWORD_REUSE_TIME : 如果你指定天數期間內,密碼變更次數達到PASSWORD_REUSE_MAX指定次數前,密碼無法再使用,如果PASSWORD_REUSE_TIME和 PASSWORD_REUSE_MAX任一者指定UNLIMITED參數,密碼無法再使用,兩者指定為UNLIMITED,且無視這些參數

PASSWORD_LOCK_TIME : 連續登入失敗到達指定次數後,帳戶鎖定天數

PASSWORD_GRACE_TIME : 仍允許登入但提出警告的猶豫天數

PASSWORD_VERIFY_FUNCTION : 指定檢查密碼函數,指定NULL則不進行密碼驗證

SESSIONS_PER_USER : 限制使用者可同時執行的作業階段數

CPU_PER_SESSION : 作業階段使用的CPU時間限制(100分之1秒)

CPU_PER_CALL : 單一叫用的統計CPU時間限制(100分之1秒)

CONNECT_TIME : 單一作業階段經過時間統計限制(分)

IDLE_TIME : 作業階段中,連續的非活動時間長度(分)

LOGICAL_READS_PER_SESSION : 單一作業階段中讀入資料區塊限制

COMPOSITE_LIMIT : 單一作業階段的資源與成本,按服務單位指定

PRIVATE_SGA : 單一作業階段中,共享資料區內可分配的私有空間大小,可用位元為KB(K),MB(M),GB(G),TB(T),PB(P),EF(E)等單位指定

語法示範

說明 : 變更控制檔hr_user_profile,使用相同密碼認證天數為30天

alter profile hr_user_profile

password_life_time 30;

CREATE PROFILE介紹

語法說明

建立使用者的控制檔,只要建立使用者都要指定設定檔,ORACLE預設的設定檔為DEFAULT

格式

CREATE PROFILE profile_name LIMIT

[FAILED_LOGIN_ATTEMPTS {times | UNLIMITED}]

[PASSWORD_LIFE_TIME {days | UNLIMITED}]

[{PASSWORD_REUSE_TIME | PASSWORD_REUSE_MAX}

{days_or_times | UNLIMITED}]

[PASSWORD_LOCK_TIME {days | UNLIMITED}]

[PASSWORD_GRACE_TIME {days | UNLIMITED}]

[PASSWORD_VERIFY_FUNCTION {function_name | NULL | DEFAULT}]

[SESSIONS_PER_USER {n | UNLIMITED | DEFAULT}]

[CPU_PER_SESSION {n | UNLIMITED | DEFAULT}]

[CPU_PER_CALL {n | UNLIMITED | DEFAULT}]

[CONNECT_TIME {n | UNLIMITED | DEFAULT}]

[IDLE_TIME {n | UNLIMITED | DEFAULT}]

[LOGICAL_READS_PER_SESSION {n | UNLIMITED | DEFAULT}]

[COMPOSITE_LIMIT {n | UNLIMITED | DEFAULT}]

[PRIVATE_SGA {n | UNLIMITED | DEFAULT}];

profile : 控制檔名稱

times : 次數

days : 天數

days_or_times : 天數或次數

function :　函數名稱

n : 設定數值

FAILED_LOGIN_ATTEMPTS : 容許登入失敗的次數

PASSWORD_LIFE_TIME : 可使用相同密碼認證的天數

PASSWORD_REUSE_TIME : 如果你指定天數期間內,密碼變更次數達到PASSWORD_REUSE_MAX指定次數前,密碼無法再使用,如果PASSWORD_REUSE_TIME和PASSWORD_REUSE_MAX任一者指定UNLIMITED參數,密碼無法再使用,兩者指定為UNLIMITED,且無視這些參數

PASSWORD_LOCK_TIME : 連續登入失敗到達指定次數後,帳戶鎖定天數

PASSWORD_GRACE_TIME : 仍允許登入但提出警告的猶豫天數

PASSWORD_VERIFY_FUNCTION : 指定檢查密碼函數,指定NULL則不進行密碼驗證

SESSIONS_PER_USER : 限制使用者可同時執行的作業階段數

CPU_PER_SESSION : 作業階段使用的CPU時間限制(100分之1秒)

CPU_PER_CALL : 單一叫用的統計CPU時間限制(100分之1秒)

CONNECT_TIME : 單一作業階段經過時間統計限制(分)

IDLE_TIME : 作業階段中,連續的非活動時間長度(分)

LOGICAL_READS_PER_SESSION : 單一作業階段中讀入資料區塊限制

COMPOSITE_LIMIT : 單一作業階段的資源與成本,按服務單位指定

PRIVATE_SGA : 單一作業階段中,共享資料區內可分配的私有空間大小,可用位元為KB(K),MB(M),GB(G),TB(T),PB(P),EF(E)等單位指定

語法示範

說明 : 建立hr_user_profile控制檔,在單一作業階段,連續的非活動時間達到15分鐘,使用者會自動斷線

create profile hr_user_profile limit

idle_time 15;

DROP PROCEDURE介紹

語法說明

刪除預儲函數

格式

DROP PROCEDURE [schema.]procedure_name;

schema : 結構名稱

procedure_name : 預儲程序名稱

語法示範

說明 : 刪除預儲程序emp

drop procedure emp;

ALTER PROCEDURE介紹

語法說明

可重新編譯預儲程序,將已編譯(已建立)但狀態變成無效的預儲程序可重新編譯,執行時就不用另外暗中編譯,而能提高效能
但是程序的參數/聲明/處理紀錄部分有所變更時,必須以CREATE OR REPLACE PROCEDURE句重新建立函數

格式

ALTER PROCEDURE [schema.]procedure_name COMPILE [DEBUG];

schema : 結構名稱

procedure_name : 預儲程序名稱

語法示範

說明 : 重新編譯預儲程序emp　

alter procedure emp compile;

CREATE PROCEDURE介紹

語法說明

建立預儲程式,指定 [OR REPLACE] 可以再次建立已建立的程序,此時不需要再次授與該程序的物件權限,也不需再次授與程序權限給己持有該權限的使用者,另外,預儲程序和使用者定義函數的預儲函數不同,前者不能將值傳回叫用來源

格式

CREATE [OR REPLACE] PROCEDURE [schema.]procedure_name

[(argument [IN | OUT | IN OUT][,argument [IN | OUT | IN OUT]...])]

[AUTHID {CURRENT_USER | DEFINER}]

{IS | AS}

procedure_statement

END [procedure_name];

schema : 結構名稱

procedure_name : 要建立的程序名稱

argument : 引數內容

IN : 在程序啟動時接受值,在程序內無法變更此引數值

OUT : 將程序內代入此引數的值,於程序結束時傳回至叫用來源

IN OUT : IN和OUT組成的模式,程序啟動時交出引數,在程序內也能將值代入此引數,且代入到引數的值,於程序結束值,會傳回至叫用來源

CURRENT_USER : 以目前執行中的使用者權限來執行程序,所建立的程序稱為 [執行者權限程序]

DEFINER : 以程序所在的結構擁有者權限來執行,所建立的程式包裝稱為 [定義者權限程序] ,AUTHID子句的預設值為AUTHID DEFINER

語法示範

說明 : 建立預儲程序emp2　

create or replace procedure emp2

is

begin

update emp set salary=2000 where emp_id=101;

end;

/

說明 : 執行預儲程序emp2

execute emp2;

說明 : 建立預儲程序emp3　

create or replace procedure emp3

(empno in number,result out number)

is

begin

delete from emp where emp.emp_no = empno;

result := 0;

exception

when others then

result := 9;

end;

/

說明 : 執行預儲程序emp3

declare

wk_result number := 0;

begin

emp3(1,wk_result);

DBMS_OUTPUT_PUT_LINE(to_char(wk_result));

CREATE PFILE介紹

語法說明

從spfile建立pfile

格式

CREATE PFILE = 'pfile_path' FROM SPFILE = 'spfile_path';

pfile_path : pfile的路徑

spfile_path : spfile的路徑

語法示範

說明 : 從spfile建立pfile

create pfile='/oracle/home/initexample.ora' from spfile='/oracle/home/spfileorcl.ora';

CREATE PACKAGE BODY介紹

語法說明

執行CREATE PACKAGE BODY句可建立程式包裝的本體部分,指定 [OR REPLACE] 可重建已建立的程式包裝本體部分,此時不必再次授與該程式包裝的物件權限,也不必再次授與物件權限給己獲此權限的使用者,程式包裝本體部分描述程式包裝成員 [預儲程式和預儲數的處理內容]

格式

CREATE [OR REPLACE] PACKAGE BODY [schema.]package_name

{IS | AS}

package_specification

END [package_name];

schema : 結構名稱

package_name : 程式包裝名稱

package_specification : 程式包裝規格,類型定義,指標/變數/常數/例外的聲明,PL/SQL子程式的規格和PL/SQL內部紀錄的(或者是)java常式的聲明(叫用規格)等

語法示範

說明 : 建立預儲程式包裝emp_mgmt的程式包裝本體部分

create or replace package body emp_mgmt as

   tot_emps NUMBER; 
   tot_depts NUMBER; 
FUNCTION hire 
   (last_name VARCHAR2, job_id VARCHAR2, 
    manager_id NUMBER, salary NUMBER, 
    commission_pct NUMBER, department_id NUMBER) 
   RETURN NUMBER IS new_empno NUMBER; 
BEGIN 
   SELECT employees_seq.NEXTVAL 
      INTO new_empno 
      FROM DUAL; 
   INSERT INTO employees 
      VALUES (new_empno, 'First', 'Last','first.last@oracle.com', 
              '(123)123-1234','18-JUN-02','IT_PROG',90000000,00, 
              100,110); 
      tot_emps := tot_emps + 1; 
   RETURN(new_empno); 
END; 
FUNCTION create_dept(department_id NUMBER, location_id NUMBER) 
   RETURN NUMBER IS 
      new_deptno NUMBER; 
   BEGIN 
      SELECT departments_seq.NEXTVAL 
         INTO new_deptno 
         FROM dual; 
      INSERT INTO departments 
         VALUES (new_deptno, 'department name', 100, 1700); 
      tot_depts := tot_depts + 1; 
      RETURN(new_deptno); 
   END; 
PROCEDURE remove_emp (employee_id NUMBER) IS 
   BEGIN 
      DELETE FROM employees 
      WHERE employees.employee_id = remove_emp.employee_id; 
      tot_emps := tot_emps - 1; 
   END; 
PROCEDURE remove_dept(department_id NUMBER) IS 
   BEGIN 
      DELETE FROM departments 
      WHERE departments.department_id = remove_dept.department_id; 
      tot_depts := tot_depts - 1; 
      SELECT COUNT(*) INTO tot_emps FROM employees; 
   END; 
PROCEDURE increase_sal(employee_id NUMBER, salary_incr NUMBER) IS 
   curr_sal NUMBER; 
   BEGIN 
      SELECT salary INTO curr_sal FROM employees 
      WHERE employees.employee_id = increase_sal.employee_id; 
      IF curr_sal IS NULL 
         THEN RAISE no_sal; 
      ELSE 
         UPDATE employees 
         SET salary = salary + salary_incr 
         WHERE employee_id = employee_id; 
      END IF; 
   END; 
PROCEDURE increase_comm(employee_id NUMBER, comm_incr NUMBER) IS 
   curr_comm NUMBER; 
   BEGIN 
      SELECT commission_pct 
      INTO curr_comm 
      FROM employees 
      WHERE employees.employee_id = increase_comm.employee_id; 
      IF curr_comm IS NULL 
         THEN RAISE no_comm; 
      ELSE 
         UPDATE employees 
         SET commission_pct = commission_pct + comm_incr; 
      END IF; 
   END; 
END emp_mgmt; 
/

DROP PACKAGE介紹

語法說明

可刪除預儲程式包裝,規格部分和本體部分都會刪除

格式

DROP PACKAGE [schema.]package_name;

schema : 結構名稱

package_name : 程式包裝名稱

語法示範

說明 : 刪除預儲程式包裝pack_emp

drop package pack_emp;

ALTER PACKAGE介紹

語法說明

可重新編譯預儲程式包裝,將已編譯(已建立)但狀態變成無效的預儲程式包裝重新編譯,執行時就不另外編譯,而能提高效能,在ALTER PACKAGE句裡無法個別編譯程式包裝

格式

ALTER PACKAGE [schema.]package_name COMPILE

[DEBUG]

[SPECIFICATION | BODY | PACKAGE];

schema : 結構名稱

package_name : 程式包裝名稱

DEBUG : 對於PL/SQL的編譯器產生除錯用程式碼

SPECIFICATION : 指編譯程式包裝的規格部分

BODY : 指編譯程式包裝的本體部分

PACKAGE : 編譯程式包裝的規格部分和本體部分,此為預設值

語法示範

說明 : 重新編譯預儲程式包裝pack_emp的規格部分和本體部分

alter package pack_emp compile;

CREATE PACKAGE介紹

語法說明

建立程式包裝的規格部分,指定OR REPLACE可再次建立規格部分,此時不必再次授與該程式包裝的物件權限,也不必再次授與物件權限給己獲此權限的使用者
規格部分陳述是可稱為 [程式包裝成員] 的下列物件,這裡聲明的物件也可稱為 [程式包裝物件] 可從程式包裝外部來參照,實際處理內容則紀錄在本體部分

預儲程序和預儲函數

共享變數和常數

例外

格式

CREATE [OR REPLACE] PACKAGE [schema.]package_name

[AUTHID {CURRENT_USER | DEFINER}]

{IS | AS}

package_specification

END [package_name];

schema : 結構名稱

package_name : 程式包裝名稱

package_specification : 程式包裝規格,類型定義,指標/變數/常數/例外的聲明,PL/SQL子程式的規格和PL/SQL內部紀錄的(或者是)java常式的聲明(叫用規格)等

AUTHID : 設定執行程式包裝內之函數和程序的使用者權限,此設定也可適用於程式包裝本體

AUTHID子句指定值

CURRENT_USER : 以目前執行中的使用者權限來執行程式包裝,建立包裝稱為 [執行者權限程式包裝]

DEFINER : 以程式包裝存在的結構使用者權限來執行,建立的程式包裝稱為[定義者權限程式包裝],沒有指定AUTHID子句時,預設值為AUTHID DEFINER
語法示範

說明 : 預儲程式包裝pack_emp的程式包裝規格部分,pack_emp以下列程式包裝構成

變數 : last_name

函數 : check_emp_sf

程序 : update_emp

使用者定義例外 : err_empNotFound

create or replace package pack_emp is

--定義變數--

last_name emp.name%type;

--定義函數check_emp_sf--

function check_emp_sf(empno in number) return boolean;

--定義程序update_emp--

procedure update_emp(empno in number,empname in varchar2);

--使用者定義的例外--

err_empNotFound exception;

end pack_emp;

/

DROP MATERIALIZED VIEW LOG介紹

語法說明

刪除實體化檢視日誌

格式

DROP MATERIALIZED VIEW LOG ON [schema.]table_name;

schema : 結構名稱

table_name : 物件名稱

語法示範

說明 : 刪除實體化檢視日誌

drop materialized view log on hr.emp_log;

ALTER MATERIALIZED VIEW LOG介紹

語法說明

可變更實體化檢視日誌的屬性,變更項目如下

記憶空間特性,分割,平行操作,日誌屬性,有無使用CACHE

附加在實體化檢視日誌記錄的欄位

是否在實體化檢視日誌放入更新前後的資訊

格式

ALTER MATERIALIZED VIEW LOG [FORCE] ON [schema.]table_name

ADD [OBJECT_ID | PRIMARY KEY(column [,column...])

| ROWID | SEQUENCE | (column [,column...])]

[{INCLUDING | EXCLUDING} NEW VALUES];

schema : 結構名稱

table_name : 物件名稱

column : 欄位名稱

OBJECT ID : 記錄對於更新全列的物件識別符,建立物件表格日誌時可以指定

PRIMARY KEY : 記錄更新全列主鍵

ROWID : 記錄更新全列ROWID

SEQUENCE : 紀錄表示附加序列資訊的序列值,進行高速復新時,必須要在實體化檢視日誌裡記錄此值

NEW VALUES子句可指定更新前後的資訊處理方法

NEW VALUES子句指定值

INCLUDING : 記錄更新前後資訊,此為預設值

EXCLUDING : 只記錄更新前的資訊

語法示範

說明 : 附加在實體化檢視日誌記錄的欄位

alter materialized view log on emp add deptno_id number(10);

說明 : 設定附加記錄欄位並記錄更新前後的值

alter materialized view log on my_emp add(comm) including new values;

訂閱：文章 (Atom)