---oracle 資料庫的設計，PL/SQL（loop,for,if,case,while）

標籤：nextval   sql語句   拋出異常   處理   隱式   沒有   i+1   func   有一個   

1、資料庫的設計（DataBase Design）:

       針對使用者特定的需求，然後我們建立出來一個最實用而且效能高的資料庫！

    資料庫設計的步驟：

      01.需求分析

     02.概念結構設計

     03.邏輯結構設計

    04.物理機構設計

     05.資料庫的實施

    06.資料庫的運行和維護

資料庫的3大範式：

   1.確保每列的原子性！每一列都是一個不可再分的資料！

   2.確保每列都和主鍵相關！

   3.確保每列都和主鍵有直接的關係，而不是間接依賴（傳遞依賴）！

--------------------------------------------------------------------------------------------------------------------------------------

PL/SQL:(Procedural  Language) 過程化sql語言！

  在我們之前的sql語句中增加了選擇或者是邏輯判斷！

資料庫再執行PL/SQL語句的時候，PL和SQL是分別執行的！

oracle  ||  拼接字串   。  

 ：=   賦值運算子  。

  =       相等於java中的==

 

  ..      範圍運算子。如：1..10 就是1到10 之間

!=   <>    ~=  ^=  不等於

 and      邏輯與

or         邏輯或

not       取反

PL/SQL文法：

1.declare   可選部分 ===》聲明

2.begin    必須有  ===》書寫sql和pl/sql

3.exception  可選部分===》異常

4.end      必須有  ==》pl/sql代碼塊結束

 

案例1；loop迴圈：

 declare     --聲明部分    i number; begin        --代碼開始   i:=1;   loop   --迴圈開始         dbms_output.put_line(i);--輸出語句         i:=i+1;         exit when  i=10;    end loop;    -- 迴圈結束end;       --結束部分

　按列2： while迴圈文法：

        while 條件 loop

              執行的語句；

         end loop;　

declare    --代碼聲明  i number; begin     --代碼開始   i:=1;   while i<20 loop     --迴圈語句        dbms_output.put_line(i);  --輸出語句            i:=i+1;    end loop;   --迴圈結束 end;  --結束部分　

按列3 : for迴圈文法

   for 變數 in 範圍  loop

        執行的語句；

    end loop;

declare --聲明部分    i number;begin    --代碼開始    for i in 1..30 loop   --迴圈開始        dbms_output.put_line(i);  --輸出語句    end loop;  --迴圈結束end; -- 結束部分

   案例 4：

       根據老師的薪水輸出不同的語句！

if 選擇結構   和case選擇結構

（1）。if選擇結構

declaret_name  teacher.tname%type;    t_sal     teacher.sal%type;begin   select tname, sal into t_name, t_sal from teacher where tno=1002;        if t_sal>5000  and t_sal<10000 then          dbms_output.put_line(‘一級‘);   elsif t_sal>=10000 and t_sal <20000 then      dbms_output.put_line(‘二級‘);    else        dbms_output.put_line(‘進階‘);      end if; end;

　（2）case 選擇結構

    　

declaret_name  teacher.tname%type;    t_sal     teacher.sal%type;t_result   varchar2(50);begin   select tname, sal into t_name, t_sal from teacher where tno=1002;        if t_sal>5000  and t_sal<10000 then         t_result:=‘一級‘;   elsif t_sal>=10000 and t_sal <20000 then       t_result:=‘二級‘;    else       t_result:=‘進階‘;      end if;             case  t_result        when ‘一級‘ then          dbms_output.put_line(‘哈哈‘);          when  ‘二級‘  then            dbms_output.put_line(‘一般般‘);            when ‘進階‘ then              dbms_output.put_line(‘可以呀！‘);      end case;end;

　　

案例5：函數

    需求  把社會安全號碼中的出生您月日隱藏！

create :  建立

replace :修改

create or replace  :沒有就建立  有就修改

fn_teacher_tid: 函數名稱規範

f_tid : 參數名稱

varchar2: 參數類型

create or replace function fn_teacher_tid(f_tid varchar2)return varchar2     --建立一個函數  傳遞一個varchar2類型的值 返回一個varchar2類型的值is f_result   varchar2(50);  --聲明變數begin     --開始書寫函數內容     if length(f_tid)!=18 then         dbms_output.put_line(‘身份格式不正確！‘);         else           dbms_output.put_line(‘身份格式正確！‘);          -- 如果輸入的格式正確，把輸入的年月日用*代替            f_result:=substr(f_tid,1,6)||‘********‘||substr(f_tid,15);       end if;           return f_result;end fn_teacher_tid;  --函數結束--調用函數select fn_teacher_tid(‘1111111111111111111‘) from dual;

　　

案例6：
遊標 ：
01.是oracle系統給我們使用者開設的一個資料緩衝區！
02.存放的是sql語句執行的結果集！
03.每個遊標區都有一個名稱，使用者通過遊標逐行擷取需要的資料！

分類：
01.隱式遊標： 非查詢語句
只要我們使用pl/sql，程式在執行sql語句的時候 自動建立！ 遊標區===》sql
02.顯示遊標： 返回多行記錄
03.REF遊標（動態資料指標）： 處理運行時才能確定的動態sql查詢結果

遊標的常用屬性：
01.sql%found 影響了一行或者多行資料 返回true
02.sql%notfound 沒有影響行 返回true
03.sql%rowcount 返回true影響行數
04.sql%isopen 遊標是否開啟！始終是false

使用遊標的步驟：
01.聲明遊標
02.開啟遊標
03.使用遊標擷取記錄
04.關閉遊標

 

 

01.隱士遊標

 

-------------------------------------------------------------------------------------------------------------------------------

--隱式遊標begin    -- 隱式遊標   自動建立     update teacher set tname=‘大家辛苦了‘  where tno=1002;  --修改     if sql%found then       dbms_output.put_line(‘教師的資訊已經更改‘ || sql%rowcount);     else        dbms_output.put_line(‘更改失敗‘);        end  if;  end;  

02.顯示遊標

 -- 顯示遊標  declare   --聲明顯示遊標   c_tname  teacher.tname%type;   c_sal   teacher.sal%type;   cursor  teacher_cursor   is   select tname,sal from teacher where tno<1005;  --遊標資料來源 begin     open teacher_cursor; --開啟遊標       fetch teacher_cursor   into c_tname,c_sal;  --使用遊標       while teacher_cursor%found  loop          dbms_output.put_line(‘教師的姓名是==》‘|| c_tname);           dbms_output.put_line(‘教師的薪水是==》‘|| c_sal);           fetch teacher_cursor   into c_tname,c_sal; --逐行讀取        end loop;      close teacher_cursor;  --關閉遊標 end;

　　

　案例7：　觸發器

     觸發器是針對於增刪改！

   update    :old   :new

   insert    :new

   delete    :old

 

：old     代表修改之前的值

：new    代表修改後的值

============================================================

select * from teacher t for update  --建立一個用於儲存teacher操作記錄的表  create table teacher_log  (  logid number not null,  old_value  varchar2(150),  create_date date,  log_type number,  t_no number  );  --建立主鍵alter table teacher_log add constraint pk_teacher_logidprimary key(logid);--建立序列create sequence sq_teacherLog_logidminvalue 1maxvalue 999999999start with 1increment by 1;--建立觸發器create or replace trigger tr_teacherafter insert or update or delete --會在增刪改之後觸發on teacher for each row  -- 作用再teacher表中的每一行declare  --聲明變數old_value   teacher_log.old_value%type;log_type  teacher_log.log_type%type;t_no  teacher_log.t_no%type;begin     if inserting then       log_type:=1; --新增       t_no:=:new.tno;       old_value:=:new.tname||‘******‘||:new.sal;      elsif deleting then        log_type:=2;--刪除        t_no:=:old.tno;        old_value:=:old.tname||‘*****‘||:old.sal;        else          log_type:=3;--修改          t_no:=:old.tno;          old_value:=:old.tname||‘******‘||:old.sal||‘現在的薪水:‘||:new.sal;       end if;--把使用者修改的資料 放入 teacher_log     insert into teacher_logvalues(sq_teacherLog_logid.Nextval,old_value,sysdate,log_type,t_no);end tr_teacher;    --結束

　　

案例8：預存程序
為了完成一個特定的功能而實現編寫一組sql語句的集合！

新增教室時，如果社會安全號碼碼不足18位，報錯！

 

create or replace procedure  pro_addTeacher--預存程序(p_no   teacher.tno%type,p_name  teacher.tname%type,p_tid teacher.tid%type)isex_tidException exception;--異常類型begin     if  length(p_tid)!=18  then       raise ex_tidException;  --拋出異常      end if;                  --新增      insert into teacher(tno,tname,tid)      values(p_no,p_name,p_tid);      commit;---自動認可 exception  --異常處理部分     when ex_tidException then       dbms_output.put_line(‘社會安全號碼不正確‘);      when others then        dbms_output.put_line(‘其他異常‘); end  pro_addTeacher;  --結束          --調用預存程序 call pro_addTeacher(1112,‘小白白‘,‘1122222222222222222‘);

 

 9設定欄位的類型：%type    %rowtype

 

name          teacher.tname%type ：會根據表中欄位的類型，自動改變！

teacherRow        teacher%rowtype: 一整行的記錄，包括很多欄位，自動改變！想要單個欄位可以用它直接點出來就好了（teacherRow.name）

 

---oracle 資料庫的設計，PL/SQL（loop,for,if,case,while）