pl/sql基礎

結構化查詢語言pls/ql的基本單位是塊，結構由三部分組成，聲明部分、執行部分和異常處理部分。

[DECLARE]          ---聲明部分，包括變數、常量、類型等
  BEGIN            ---執行開始語言
     ---------     ---執行語句
   [EXCEPTION]     ---異常處理部分
  END;             ---執行結束

例如： 表info(id,name,price)

DECLARE   
 v_result   number(10,2);    
  BEGIN                                        
     select id into v_result from info where name='飲料';   
     dbms_output.put_line('結果為：'||v_result);
   exception 
     WHEN no_data_found THEN
       dbms_output.put_line('沒有找到數據');
  END; 

1.定義變數與常量

    ---%type類型

DECLARE  
  v_productid      info.id%TYPE;    --%TYPE聲明變數，類型同表info.id的一致
  v_productname    VARCHAR2(10);
  v_produceprice   NUMBER(10,2);
  v_desperation    CONSTANT v_productname%TYPE :='測試'; --利用%TYPE 引用v_productname的類型，並聲明一個常量
  v_date           DATE :=SYSDATE;  
  BEGIN                                       
     select id,name,price into v_productid,v_productname, v_produceprice
      from info where name='飲料'; 
      DBMS_OUTPUT.put_line('結果為：'||v_productid);
      DBMS_OUTPUT.put_line('結果為：'||v_productname);
      DBMS_OUTPUT.put_line('結果為：'||v_produceprice);
      DBMS_OUTPUT.put_line('結果為：'||v_desperation);
      DBMS_OUTPUT.put_line('結果為：'||v_date);
   EXCEPTION 
     when no_data_found then
       dbms_output.put_line('沒有找到數據');
  END;         
  

   ---record類型

DECLARE  
TYPE product_rec IS RECORD          --記錄類型的聲明
(
  v_productid      info.id%TYPE,   --%TYPE聲明變數，類型同表info.id的一致
  v_productname    VARCHAR2(10),
  v_produceprice   NUMBER(10,2)
);
 v_product product_rec;
  BEGIN                                       
     select id,name,price into v_product
      from info where name='飲料'; 
      DBMS_OUTPUT.put_line('結果為：'||v_product.v_productid);
      DBMS_OUTPUT.put_line('結果為：'||v_product.v_productname);
      DBMS_OUTPUT.put_line('結果為：'||v_product.v_produceprice);

   EXCEPTION 
     when no_data_found then
       dbms_output.put_line('沒有找到數據');
  END;         
  

  --%ROWPYTE

DECLARE  
 v_product info%ROWTYPE;
  BEGIN                                       
     select * into v_product
      from info where name='飲料'; 
      DBMS_OUTPUT.put_line('結果為：'||v_product.id);
      DBMS_OUTPUT.put_line('結果為：'||v_product.name);
      DBMS_OUTPUT.put_line('結果為：'||v_product.price);

   EXCEPTION 
     when no_data_found then
       dbms_output.put_line('沒有找到數據');
  END;         
  

2.條件控制語句

    ---IF..ELSE..  

DECLARE  
  v_result number(10);
  BEGIN                                       
     v_result :=1;
     IF v_result>10 THEN
      DBMS_OUTPUT.put_line('大於10：'||v_result);
     ELSIF v_result=10 THEN
      DBMS_OUTPUT.put_line('等於10：'||v_result);
     ELSIF v_result<10 THEN
      DBMS_OUTPUT.put_line('小於10：'||v_result);
      END IF;      
  END;         
  

    ---CASE

DECLARE  
  v_result number(10);
  BEGIN                                       
     v_result :=1;
     CASE v_result
       WHEN  '10' THEN
        DBMS_OUTPUT.put_line('等於10：'||v_result);
       WHEN  '11' THEN
        DBMS_OUTPUT.put_line('等於11：'||v_result);
       WHEN  '12' THEN
        DBMS_OUTPUT.put_line('等於12：'||v_result);
      ELSE
        DBMS_OUTPUT.put_line('其他：'||v_result); 
        END CASE;    
  END;    

DECLARE  
  v_result number(10);
  BEGIN                                       
     v_result :=1;
     CASE 
       WHEN v_result>10 THEN
        DBMS_OUTPUT.put_line('大於10：'||v_result);
       WHEN v_result=10 THEN
        DBMS_OUTPUT.put_line('等於10：'||v_result);
       WHEN v_result<10THEN
        DBMS_OUTPUT.put_line('小於10：'||v_result);
      ELSE
        DBMS_OUTPUT.put_line('其他：'||v_result); 
        END CASE;    
  END;         
  

3.迴圈控制語句

   ---loop

declare
v_num number(10):=1;
begin  
  <<basic_loop>>            --可以省略，結束時候直接 exit；
  loop
    dbms_output.put_line('結果是：'||v_num);
    v_num:=v_num+1;
    if v_num>100 then          --可以使用 exit basic_loop when v_num>10;
      exit basic_loop;
     end if;
  end loop;
end;

declare
sum_i int:=1;
i int:=1;
begin
 loop
  i:=i+1;
  sum_i :=sum_i +i;
 exit when i =100;
end loop;
dbms_output.put_line('前100個自然數的和是：'||sum_i);
end;

  ---for

declare
sum_i int:=0;
begin
 for i in reverse 1..1000 loop -- reverse反向  表示i從100遞減
  sum_i:= sum_i+i;
 end loop;
dbms_output.put_line('前100個自然數的和是：'||sum_i);
end;

   ---while

declare
sum_i int :=0;
i int :=0;
begin
 while i<1000 loop
  i:=i+1;
  sum_i:=sum_i+i;
 end loop;
 dbms_output.put_line('前100個自然數的和是：'||sum_i);
end;

 4.異常處理

   預定義異常

exception
 when too_many_rows then                          
  dbms_output.put_line('返回記錄超過一行');
 when no_data_found then                         
  dbms_output.put_line('無數據記錄');

自定義異常

declare
v_price info.price%TYPE;
primary_iterant exception;  --定義一個異常變數
pragma exception_init(primary_iterant,-00001);  --關聯錯誤號和異常變數名
begin
 select price into v_price from info where id = 1;
     if v_price < 1 then 
        raise primary_iterant;
     end if;
exception
 when primary_iterant then
  dbms_output.put_line('價格不能小於1！');
end;

5函數編寫（商品數量低於50 的打75折，高於50的打9折）

create or replace function pric     --or replace 覆蓋同名函數
(v_pric in number,v_qnty in number)
return number     --函數的返回類型
is
begin
  if v_qnty<50 then
    return(v_pric *0.75);
    else
      return(v_pric*0.9);
   end if;
 end;

調用方法

1
declare 
avgl number;
begin
  avgl:=pric(100,40);
    dbms_output.put_line(avgl);
 end;

2
select pric(100,40) from tab;

--刪除函數
drop function get_avg_pay;

 查看函數

col name format a15              --格式化欄位長度
col text   format a80
select name,line,text from user_source where name ='pric';