【SQL】Oracle的PL/SQL文法及其拓展資料類型總結

文章目錄

• PL/SQL文法
• 自訂資料類型之%type
• 複合資料型別之record
• 複合資料型別之%rowtype
• Oracle擴充資料類型小結

PL/SQL文法

PL/SQL程式由三部分組成，聲明部分、執行部分、異常處理部分。

模板：

DECLARE/*變數聲明*/BEGIN/*程式主體*/EXCEPTION/*異常處理部分*/END;

接收使用者的輸入資訊

PL/SQL  塊還可以接收使用者的輸入資訊，例如：現在要求使用者輸入一個僱員編號，之後根據輸入的內容進行查詢，查詢僱員的姓名。
使用者的輸入資訊使用“&”完成。
範例：

DECLAREt_eno NUMBER ;t_ename VARCHAR2(30) ; BEGIN--  輸入的資訊儲存在 eno 裡t_eno := &no;--  之後根據 eno 的值，對資料庫進行查詢操作SELECT ename INTO t_ename FROM emp WHERE empno=t_eno ; DBMS_OUTPUT.put_line('編號為：'||t_eno||'僱員的姓名為：'||t_ename) ;EXCEPTIONWHEN no_data_found THEN DBMS_OUTPUT.put_line('沒有此僱員') ; END ;

自訂資料類型之%type

定義一個變數，可以將其與表中某個列的資料類型一致。

如上例修改為以下也可運行。

DECLAREt_eno emp.empno%type ;t_ename emp.ename%type; BEGIN  --  輸入的資訊儲存在 eno 裡  t_eno := &no;  --  之後根據 eno 的值，對資料庫進行查詢操作  SELECT ename INTO t_ename FROM emp WHERE empno=t_eno ;   DBMS_OUTPUT.put_line('編號為：'||t_eno||'僱員的姓名為：'||t_ename) ;EXCEPTION  WHEN no_data_found THEN DBMS_OUTPUT.put_line('沒有此僱員') ; END ;

DECLAREt_eno emp.empno%type ;t_ename emp.ename%type; BEGIN  --  輸入的資訊儲存在 eno 裡  t_eno := &no;  --  之後根據 eno 的值，對資料庫進行查詢操作  SELECT ename INTO t_ename FROM emp WHERE empno=t_eno ;   DBMS_OUTPUT.put_line('編號為：'||t_eno||'僱員的姓名為：'||t_ename) ;EXCEPTION  WHEN no_data_found THEN DBMS_OUTPUT.put_line('沒有此僱員') ; END ;

複合資料型別之record

將一個或多個標量封裝成一個對象，由單行多列的標量構成的複合結構，組成類似於一維數組。 
如下例查詢表中兩列將其放入記錄類型並輸出顯示。

DECLAREtype rec_test1 is record(t_empno emp.empno%type,t_ename emp.ename%type); --定義一個record類型rt rec_test1;--使用你剛才定義的record類型，定義了一個變數rt.BEGINselect empno,ename into rt.t_empno,rt.t_ename from emp where empno=1001;dbms_output.put_line('empno:'||rt.t_empno);END ;

DECLARE  type rec_test1 is record(  a1 number,  a2 varchar2(10)  );  rt1 rec_test1;  rt2 rec_test1;BEGIN  rt1.a1:=1;  rt1.a2:='china';  rt2.a1:=2;  rt2.a2:='usa';  dbms_output.put_line('rt1.a2:'||rt1.a2);  dbms_output.put_line('rt2.a2:'||rt2.a2);END ;

複合資料型別之%rowtype

%ROWTYPE可以說是record的升級簡化版。 
區別在與前者結構為表結構，後者為自訂結構。
二者在使用上沒有很大區別。前者方便，後者靈活。
在實際中根據情況來具體決定使用。

使用記錄更新資料，如果用記錄(RECORD)更新資料，那麼只能使用記錄成員；如果用%ROWTYPE更新資料,可以直接使用%ROWTYPE 。如:

DECLARE  vEmp emp%RowType;Begin  select * InTo vEmp from empa where empa.EMPNO = '7934';  update  empa set ROW = vEmp where EMPNO ='1111';   commit;End; 

【注】在進行如上操作前，需要複製表emp到empa。
複製表的結構

CREATE TABLE empa as SELECT * FROM emp WHERE 1=2;

複製表的資料

INSERT INTO empa SELECT * FROM emp;

Oracle擴充資料類型小結

%type:可讓變數的類型與指定表的某列保持一致;
t_empno 表名.列名%type;

%record:單行多列，類似於一位元組;
1.先建立類型 2.再建立該類型的變數
type XXX is record(
列1 資料類型(長度),列2 資料類型(長度)…
);
x XXX; --x是變數, XXX是它的類型
如何引用某列？ x.列名 即可

%rowtype: 是%record的升級簡化版，可讓其與某張表的結構完全一致.

x 表名%rowtype; --x是變數名