PL/SQL 02 宣告變數 declare

語法：
identifier [CONSTANT] datatype [NOT NULL] [:= | DEFAULT expr]

identifier：用於指定變數或常量的名稱。
CONSTANT：用於指定常量。當定義常量時，必須指定它的初始值，並且其數值不能變。
datatype：用於指定變數或常量的資料型別。
NOT NULL：用於強制初始化變數（不能為NULL）。當指定 NOT NULL 選項時，必須要為變數提供數值。
:= 用於為變數和常量指定初始值。
DEFAULT：用於為常量和變數指定初始值。
expr：用於指定初始值的 PL/SQL 表示式，可以是文字值、其他變數、函式等。

例：
v_ename    VARCHAR2(10);
v_sal      NUMBER(6,2);
v_balance  BINARY_FLOAT;  --Oracle 10g 新資料型別
c_tax_rate CONSTANT NUMBER(3,2):=5.5;
v_hiredate DATE;
v_valid    BOOLEAN NOT NULL DEFAULT FALSE;

v_sal emp.sal%TYPE;       --%TYPE
v_tax_sal v_sal%type;     --可以用變數的%TYPE

--複合變數

1、記錄變數（類似高階語言的結構體）
DECLARE
  TYPE emp_record_type IS RECORD(
    name emp.ename%TYPE,
    salary emp.sal%TYPE,
    title emp.job%TYPE);
  emp_record emp_record_type;
BEGIN
  select ename,sal,job into emp_record
  from emp where empno=7788;
  dbms_output.put_line('僱員名：'||emp_record.name); --記錄變數.記錄成員
END;

2、表變數（類似高階語言的陣列）
需要注意，PL/SQL 表變數與高階語言的陣列有所區別，高階語言陣列的下標不能為負，但 PL/SQL 表變數的下標可以為負；高階語言數字的元素個數有限制，而PL/SQL 表變數的元素個數沒有限制，並且其下標沒有上下限。

DECLARE
  TYPE ename_table_type IS TABLE OF emp.ename%TYPE
  INDEX BY BINARY_INTEGER;
  ename_table ename_table_type;
BEGIN
  select ename into ename_table(-1) from emp
  where empno=7788;
  dbms_output.put_line('僱員名：'||ename_table(-1));
END;

3、巢狀表（Nested Table）
巢狀表類似於高階語言的陣列。需要注意，高階語言陣列和巢狀表的下標都不能為負值；高階語言的元素個數是有限制的，而巢狀表的元素個數是沒有限制的。巢狀表和 PL/SQL 表變數非常類似，但巢狀表可以作為表列的資料型別，而 PL/SQL 表變數不能作為表列的資料型別。當在表列中使用巢狀表時，必須首先 CREATE TYPE 語句建立巢狀表型別。

CREATE OR REPLACE TYPE emp_type AS OBJECT(
  name varchar2(10),
  salary number(6,1),
  hiredate date);
/
CREATE OR REPLACE TYPE emp_array IS TABLE OF emp_type;
/

物件型別 emp_type 用於儲存僱員資訊，而 emp_array 是基於 emp_type 的巢狀表型別，它可以用於儲存多個僱員的資訊。當建立了巢狀表型別之後，就可以在表列或物件屬性中將其作為使用者自定義資料型別來引用。但需要注意，當使用巢狀表型別作為表列時，必須要為其指定專門的儲存表。

CREATE TABLE department(
  deptno number(2),
  dname varchar2(10),
  employee emp_arry
) NESTED TABLE employee STORE AS employee;         --employee為儲存表

4、VARRAY（變長陣列）
類似於巢狀表，它可以作為表列和物件型別屬性的資料型別。但需要注意，巢狀表的元素個數沒有限制，而 VARRAY 的元素個數是有限制的。當使用 VARRAY 時，必須首先建立 VARRAY 型別。

CREATE TYPE article_type AS OBJECT(
  title VARCHAR2(20),
  pub DATE
);
/
CREATE TYPE article_arry IS VARCHAR2(20) OF article_type;
/

物件型別 article_type 用於儲存文章資訊，而 article_arry 則用於儲存多篇文章的資訊，並且最多可以儲存 20 篇文章。當建立了 VARRAY 型別之後，可以在表或物件屬性將其作為使用者自定義資料型別來引用。

如：
CREATE TABLE author(
  id number(6),
  name varchar2(10),
  article article_arry
);

注意，巢狀表列資料需要儲存在專門的儲存表中，而 VARRAY 資料則與其他列資料一起存放在表段中。

--參照變數
參照變數是用於存放數值指標的變數。通過使用參照變數，可以使得應用程式共享相同物件，從而降低佔用空間。

1、REF CURSOR
當使用顯式遊標時，需要在定義顯式遊標時指定相應的 SELECT 語句，這種顯式遊標稱為靜態遊標。當使用遊標變數時，在定義遊標變數時不需要指定 SELECT 語句，而是在開啟遊標時指定 SELECT 語句，從而實現動態的遊標操作。

DECLARE
  TYPE c1 IS REF CURSOR;
  emp_cursor c1;
  v_ename emp.ename%TYPE;
  v_sal emp.sal%TYPE;
BEGIN
  OPEN emp_cursor FOR
    select ename,sal from emp where deptno=10;
  LOOP
    FETCH emp_cursor INTO v_ename,v_sal;
    EXIT WHEN emp_cursor%NOTFOUND;
    dbms_output.put_line(v_ename);
  END LOOP;
  CLOSE emp_cursor;
END;
/

c1 為 REF CURSOR 型別，而 emp_cursor 為遊標變數，並且在開啟遊標變數時指定了其所對應的 SELECT 語句。

2、REF obj_type
當編寫物件型別應用時，為了共享相同物件，可以使用 REF 引用物件型別，REF 實際是指向物件例項的指標。

下面通過示例說明如何使用 REF。首先建立物件型別 home 和物件表 homes，然後插入資料。

CREATE OR REPLACE TYPE home_type AS OBJECT(
  street varchar2(50),
  city varchar2(20),
  state varchar2(20),
  zipcode varchar2(6),
  owner varchar2(10)
);
/
CREATE TABLE homes OF home_type;
insert into homes values('呼倫北路12號','呼和浩特','內蒙','010010','馬明');
insert into homes values('呼倫北路13號','呼和浩特','內蒙','010010','秦斌');
commit;

物件表 homes 存放著家庭所在地以及戶主姓名。假定每個家庭有四口人，當進行人口統計時，為了使得同一家庭的每個家庭成員可以共享家庭地址，可以使用 REF 應用 home_type 物件型別，從而降低佔用空間。示例如下：

create table person(
  id number(6) primary key,
  name varchar2(10),
  addr REF home_type
);
insert into person select 1, '馬文',ref(p)
from homes p where p.owner='馬明';
insert into person select 2, '馬武',ref(p)
from homes p where p.owner='馬明';
insert into person select 3, '馬全',ref(p)
from homes p where p.owner='馬明';
insert into person select 4, '馬才',ref(p)
from homes p where p.owner='馬明';
commit;

當為 person 表插入資料時， addr 列將會存放指向 homes 表相應資料的地址指標。

--非 PL/SQL 變數
當在 SQL*Plus 或應用程式（例如 Pro*C/C++）中與 PL/SQL 塊之間進行資料互動時，需要使用 SQL*Plus 變數或應用程式變數完成。當在 PL/SQL 塊中引用非 PL/SQL 變數時，必須要在非 PL/SQL 變數前加冒號（“:”）。

1、使用 SQL*Plus 變數
在 PL/SQL 塊中引用 SQL*Plus 變數時，必須首先使用 VARIABLE 命令定義變數；而如果要在 SQL*Plus 中輸出變數內容，則需要使用 PRINT 命令。

var name varchar2(10)
begin
  select ename into :name from emp
  where empno=7788;
end;
SQL> print name;

NAME
--------------------------------
SCOTT

2、使用 Procedure Builder 變數
當在 PL/SQL 塊中引用 Procedure Builder 變數時，必須首先使用 CREATE 命令定義變數，而如果在  Procedure Builder 中輸出變數內容，則可以使用包 TEXT_IO。

PL/SQL> CREATE CHAR name LENGTH 10
PL/SQL> BEGIN
     +> SELECT ename INTO :name FROM emp
     +> WHERE empno=7788;
     +> END;
PL/SQL> TEXT_IO.PUT_LINE(:name);
SCOTT

3、使用 Pro*C/C++變數
當在 PL/SQL 塊中引用 Pro*C/C++ 程式的宿主變數時，必須首先定義宿主變數，而如果要輸出變數內容，則可以使用 printf()。

char name[10];
EXEC SQL EXECUTE
  BEGIN
    SELECT ename INTO :name FROM emp
    WHERE empno=7788l
  END;
END-EXEC;
printf("僱員名：%s\n",name);

--三個 SQL*Plus 小例子
1、輸出文字
SQL> DECLARE
  2    txt VARCHAR2(20);
  3  BEGIN
  4    txt:='HuangHeShiZGDeMuQin';
  5    dbms_output.put_line(txt);
  6  END;
  7  /
HuangHeShiZGDeMuQin

PL/SQL procedure successfully completed.

2、替代變數
SQL> DECLARE
  2    a BINARY_INTEGER;
  3    b BINARY_INTEGER;
  4  BEGIN
  5    a:=&x;
  6    b:=&y;
  7    dbms_output.put_line(a/b);
  8  END;
  9  /
Enter value for x: 9
old   5:   a:=&x;
new   5:   a:=9;
Enter value for y: 3
old   6:   b:=&y;
new   6:   b:=3;
3

3、SQL*Plus 變數賦值，並輸出
SQL> var zg VARCHAR2(10)
SQL> BEGIN
  2    :zg:='zhongguo';
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL> print zg;

ZG
--------------------------------
zhongguo

--實驗舉例
declare
  stuname varchar2(10):='張三';                      --宣告中賦值
  stubir date;
begin
  stubir:=to_date('2012-1-1','yyyy-mm-dd');          --程式體中賦值
  dbms_output.put_line('stuname的值是：'||stuname);
  dbms_output.put_line('stubir的值是：'||stubir);
end;

declare
  name emp.ename%type;   --變數型別參考表中某列型別
begin
  name:='john';
  dbms_output.put_line('name is :'||name);
end;

declare
  no001 emp.empno%type;
  name001 emp.ename%type;
begin
  select empno,ename  --將SQL查詢結果存入變數（必須返回一行，不能多行）
  into no001,name001
  from emp
  where empno=7369;
  dbms_output.put_line('no001 is :'||no001);
  dbms_output.put_line('name001 is :'||name001);
end;

declare
  type emptype is record(         --記錄變數（包含多個變數）
       no emp.empno%type,
       name emp.ename%type,
       date emp.hiredate%type
       );
  e emptype;
begin
  select empno,ename,hiredate
  into e
  from emp
  where empno=7369;
  dbms_output.put_line('no is :'||e.no);
  dbms_output.put_line('name is :'||e.name);
  dbms_output.put_line('date is :'||e.date);
end;

declare
  e emp%rowtype;     --記錄變數另一種寫法
begin
  select *
  into e
  from emp
  where empno=7369;
  dbms_output.put_line('no is :'||e.empno);
  dbms_output.put_line('name is :'||e.ename);
  dbms_output.put_line('date is :'||e.hiredate);
end;

declare
  type etype is table of emp.ename%type    --表變數（多個型別相同的變數，相當於陣列）
  index by binary_integer;
  enames etype;
begin
  enames(0):='SMITH';
  enames(1):='ALLEN';
  enames(2):='WARD';
  dbms_output.put_line(enames(0));
  dbms_output.put_line(enames(1));
  dbms_output.put_line(enames(2));
end;

--表變數舉例1

declare
type cc is table of sys_dmp01%rowtype;----表型別
v_tab cc;
i number default 1;
begin
select * bulk collect into v_tab      -----------查詢結果放到表型別中bulk collect (*換成欄位會報錯？)
from sys_dmp01;                       -----------四條記錄
for i in 1..v_tab.count loop
  insert into aa(ml_id,
                mlmc,
                mlyt,
                mllb,
                ztmc,
                ztdx,
                qjs,
                bjs,
                xt_id)
  values(v_tab(i).ml_id,
                v_tab(i).mlmc,
                v_tab(i).mlyt,
                v_tab(i).mllb,
                v_tab(i).ztmc,
                v_tab(i).ztdx,
                v_tab(i).qjs,
                v_tab(i).bjs,
                v_tab(i).xt_id);
end loop;
end;

--表變數舉例2

declare
type t is table of abc_t.s%type;
bl t;
i number default 1;

begin
select s bulk collect  into bl
from abc_t;

for i in 1..bl.count
loop
    dbms_output.put_line(bl(i));
end loop;
end;

--表變數舉例3

declare
  type t is table of abc_t.s%type;
  bl t;
  i number default 1;
  sm number default 0;
begin
 select s bulk collect  into bl
 from abc_t;

 dbms_output.put_line(1);

   while i<=bl.count
     loop
       loop
         sm:=sm+bl(i);
         i:=i+1;
         exit when sm>=60;
       end loop;
       sm:=0;
       dbms_output.put_line(i-1);
     end loop;
end;

--表變數舉例4 （判斷欄位中3個連續的數字）tt2是排序後的tt1表，不然判斷會混亂
declare
  type t is table of tt2%rowtype;
  v_tab t;
begin
  select * bulk collect into v_tab    --*換成欄位會報錯
  from tt2 ;
  for i in 1 .. v_tab.count-2 loop    --這裡減2是因為最後2個數字不用判斷，不然迴圈會報錯
    if v_tab(i+2).id - v_tab(i).id = 2 and v_tab(i+2).name = v_tab(i).name then
      dbms_output.put_line(v_tab(i).name||'-'||v_tab(i).id);
      dbms_output.put_line(v_tab(i+1).name||'-'||v_tab(i+1).id);
      dbms_output.put_line(v_tab(i+2).name||'-'||v_tab(i+2).id);
      dbms_output.put_line('----------');
    end if;     
  end loop;
end;

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/27633655/viewspace-1080383/，如需轉載，請註明出處，否則將追究法律責任。