oracle資料庫物件的建立和使用

資料庫中有一worker表，內容如下：

SQL> select * from worker;

  職工號碼 姓名     性別 出生日期    黨員否 參加工作        部門號
---------- -------- ---- ----------- ------ ----------- ----------
    1              孫華     男    1952/3/1      否          1970/10/10        1
     2             陳明     男    2045/8/1      否          1965/1/1             2
     3             程西     女   1980/4/6       否           2002/10/7          3

一、遊標

應用程式，特別是互動式聯機應用程式，並不總能將整個結果集作為一個單元來有效處理。這些應用程式需要一種機制，以便每次處理一行或一部分行。遊標就是用來提供這種機制的結果集擴充。

使用遊標將worker表姓名列加上'_t'：

--聲明遊標

declare
cursor mycur is
select 姓名 from worker for update;

--聲明一個v_text變數，用來存放遊標結果集
v_text worker.姓名%type;
begin

--開啟遊標
open mycur;

--提取第一行資料儲存在v_text

fetch mycur into v_text;
while mycur%found

loop

--在姓名後面加上'_t'

update worker set 姓名=姓名||'_t' where current of mycur;
fetch mycur into v_text;
end loop;

關閉遊標
close mycur;
end;

二、觸發器

觸發器是一種特殊的預存程序，它在指定的表中的資料發生變化時自動生效，即在INSERT、UPDATE、DELETE語句對錶或視圖進行修改時會被自動執行。

 當對worker表中進行增刪改時，將使用者、日期和操作類型記錄在一個tr_log表中：

CREATE OR REPLACE TRIGGER tr_1

AFTER INSERT OR DELETE OR UPDATE ON worker

begin

IF INSERTING THEN

        INSERT INTO mylog VALUES(user,sysdate,’I’);

   ELSIF DELETING THEN

       INSERT INTO mylog VALUES(user,sysdate,’D’);

   ELSE

      INSERT INTO mylog VALUES(user,sysdate,’U’);

 END IF;

 END;

三、視圖

視圖是從一個或者多個表中使用select語句匯出的，在資料庫中，儲存的是視圖的定義，而不是視圖查詢的資料。通過這個定義，對視圖的查詢最終轉換為對基表的查詢。

使用視圖查詢部門1的員工資訊： 

create or replace view myview
as
select * from worker where 部門號=1;

四、預存程序

預存程序是sql語句和可選控制語句的先行編譯集合，它以一個名稱儲存並作為一個單元來處理。

輸入職工號以查詢其個人資訊：

--in為輸入類型，out為輸出類型，%rowtype為萬能行類型，不可指定參數大小

create or replace procedure pr_1(v_id in number,v_info1 out worker%rowtype)
is
begin
select * into v_info from worker where 職工號=v_id;
end;
--程序呼叫
declare
v_info2 worker%rowtype;
begin

--執行，v_info2接收輸出結果

pr_1(1,v_info2);

dbms_output.put_line(v_info2.職工號||' '||v_info2.姓名||' '||v_info2.性別||' '||v_info2.出生日期||' '||v_info2.黨員否||' '||v_info2.參加工作||' '||v_info2.部門號);
end;

五、自訂函數

函數用於返回特定資料。執行時得找一個變數接收函數的傳回值。

輸入職工號以查詢其姓名：

--參加型別宣告時不用指定大小

create or replace function fun_1(v_name1 number)

--指定傳回型別
return varchar
is
declare v_name2 varchar2(8);
begin
select 姓名 into v_name2 from worker where 職工號=v_name1;
return v_name2;
end;
--函數調用

--聲明接收變數
var v_name varchar2(8)

--執行
exec :v_name:=fun_1(1)

六、包

包用於組合邏輯相關的過程和函數，它由包規範和包體兩個部分組成。包規範用於定義公用的常量、變數、過程和函數，建立包規範可以使用CREATE PACKAGE命令，建立包體可以使用CREATE PACKAGE BODY

--建立包 

create or replace package sp_package

is

begin

--函式宣告部份

 function fun_1(v_name1 number) return varchar2;
 --預存程序聲明部份

procedure pr_1(v_id in number,v_info1 out worker%rowtype);
end;

--建立包體

create package body sp_package is

begin

--函數執行部份

function fun_1(v_name1 number)
return varchar
is
v_name2 varchar2(8);
begin
select 姓名 into v_name2 from worker where 職工號=v_name1;
return v_name2;
end;

--預存程序執行部份

procedure pr_1(v_id in number,v_info1 out worker%rowtype)
is
begin
select * into v_info from worker where 職工號=v_id;
end;

end;

--調用包中函數

var v_name varchar2(8)
exec :v_name:=sp_package.fun_1(1)

--調用包中預存程序

declare
v_info2 worker%rowtype;
begin

sp_package.pr_1(1,v_info2);

dbms_output.put_line(v_info2.職工號||' '||v_info2.姓名||' '||v_info2.性別||' '||v_info2.出生日期||' '||v_info2.黨員否||' '||v_info2.參加工作||' '||v_info2.部門號);
end;

七、序列

在oracle中sequence就是所謂的序號，每次取的時候它會自動增加，一般用在需要按序號排序的地方。

在student表中有學號、姓名與性別欄位，學號欄位編排規則從1開始，遞增量為1，代碼如下：

create sequence student_sequence

--遞增量為1
increment by 1

--從1開始
start with 1

--不設最大值
nomaxvalue

--不迴圈
nocycle

--利用序列進行學號的自動插入

insert into student values(student_sequence.nextval,'李靜','女');

八、同義字

Oracle的同義字（synonyms）從字面上理解就是別名的意思，和視圖的功能類似，就是一種映射關係。

例如為worker表建立同義字：

create public synonym wo for worker;

 可以通過select * from wo 語句進行查詢。

public意為公用的，此類同義字所有使用者均可訪問，不加public則意為私人的，表對象所有者可以直接存取，但其它使用者訪問時須在表前加上所有者，無論是否加上public，非表對象所有者訪問此表，前提是須有select此表的許可權。