玩轉oracle學習第六天，oracle學習第六天

玩轉oracle學習第六天，oracle學習第六天


1.上節回顧
2.PL/SQL的介紹
3.PL/SQL的基礎

理解oracle的pl/sql概念
掌握PL/SQL編程技術（包括編寫過程，函數，觸發器，包。。。）

PL/SQL是什嗎？
PL/SQL（procedural language/sql）是oracle在標準的sql語言的擴充，PL/SQL不僅允許
嵌入sql語言，

資料庫：編寫預存程序，函數，觸發器，使用的是PL/SQL語言，PL/SQL簡化了複雜度
增加程式的模組化，減小網路的傳輸的開銷，提高安全性，提高程式的運行效率
1.過程，函數，觸發器是PL/SQL編寫的
2.過程，函數，觸發器是在oracle中
3.PL/SQL是非常強大的資料庫過程語言
4.過程，函數可以在java程式中調用

傳統的操作資料庫的方法是：
java程式sql-》sql（資料庫編譯）

程式要有靈魂，一個程式員的成長是需要過程的，
PL/SQL不好的地方：
1.移植性不好，只能使用單個資料庫，不能用於其他資料庫

PL/SQL 用什麼編寫PL/SQL
PL/SQL開發工具
 PL/SQL是oracle公司提供的一個工具
 編寫預存程序，向其中插入記錄
 1.建立一個簡單的表
 create table mytest(name varchar2(20),passwd varchar2(30));
 
 2.建立預存程序
 create or replace procedure sp_pro1 is
 begin
 --執行部分
 insert into mytest values('何世陽','m123')
 end;
 /
 replace:表示有就會替換
 如何查看錯誤資訊：
 show error；
 
 如何調用預存程序？兩種方法
 (1)exec sp_pro1(參數1，參數2，。。。);
 (2)call sp_pro1(參數1，參數2，。。。);
 
 使用PL/SQL developer工具開發PL/SQL的預存程序
 create or replace procedure sp_pro2 is
 begin
 delete from mytest where name='何世陽';
 end;
 
 預存程序現在還沒有一個標準，各個資料庫有自己的預存程序編寫規則
 
 PL/SQL編程使用變數和邏輯控制語句，從而編寫很有用的功能模組
 
 簡單分類：
           |--過程
           |--函數
 塊（編程）     
           |--觸發器
           |--包
           
編寫規範：
（1）注釋
   單行注釋 --
   select * from emp where empno=7788;--取得員工資訊
   多行注釋
   /*...*/來劃分
（2）表示符號的命名規範
 1）當定義變數，建議用v_作為首碼
 2）常量，c_作為首碼
 3）遊標，_cursor作為尾碼
 4）例外，e_作為首碼，例如e_error
 
 塊的介紹：
 塊（block）是PL/SQL的基本程式單元，編寫PL/SQL程式實際就是編寫PL/SQL塊
 
 塊的結構圖：
 PL/SQL塊由三部分構成：定義部分，執行部分，例外處理部分
 如下所示：
 declear
 /*定義部分-----定義常量，變數，遊標，例外，複雜資料類型*/
 begin
 /*執行部分-----要執行的PL/SQL語句和sql語句*/
 exception
 /*例外處理部分---處理啟動並執行各種錯誤*/
 end;
 
 特別說明：
 定義部分是從delete開始的
 該部分是可選的
 執行部分是從begin開始的
 該部分是必須的
 例外處理部分是從exception開始的
 該部分是可選的
 
 java程式結果
 
 public static void main(String[] args)
 {
   int a = 1;
   try
   {
      a++；
   }
   catch(Exception e)
   {
    
   }
 }
 
 最簡單的塊：
 set serveroutput on --開啟輸出過程
 begin
 dbms_output.put_line('hello world');
 end;
 
 以上是輸出‘hello world’的塊，說明如下：
 dbms_output 是oracle所提供的包（類似java的開發包），該包包含一些過程，
 put_line就是dbms_output包的一個過程
 
 declear
  v_ename varchar2(5);--定義字串變數
  v_sal   number(7.2);
 begin
  select ename into v_ename，v_sal from emp where empno=&no;
  dbms_output.put_line('僱員名'||v_ename);
 exception
 when no_data_found then
 dbms_output.put_line('朋友，你的編號輸入有誤！');
 end;
 /
 &表示要接受控制台輸入的參數
 
 定義部分，執行部分和例外處理部分
 
 oracle事先預定義了一些例外，例如no_data_found
 
 過程：
    過程用於執行特定的操作，當建立過程時，既可以指定輸入參數，也可以指定輸出參數，通過在過程中
    使用輸入參數，可以將資料傳遞到執行部分，
    通過使用輸出參數，可以將執行部分的資料傳遞到應用環境，在sqlplus中可以使用create procedure命令來建立過程
    執行個體如下：
    （1）編寫一個過程，可以輸入僱員名，新工資，可修改僱員的工資
    （2）調用過程有兩種方法：
    （3）在java程式中調用一個預存程序
 create procedure sp_pro3(spName varchar2,newSal number) is
 --定義變數部分
 begin
 --執行部分，根據使用者名稱去修改工資
 update emp set sal=newSal where ename = spName;
 end;
 /
 
 調用以上預存程序：
 exec sp_pro3('scott',4789);
 
 java程式中調用一個預存程序
 //編寫一個java application程式測試oracle預存程序的調用
 import java.sql.*;
 1.載入驅動
 Class.forName("oracle.jdbc.driver.OracleDriver");
 2.得到串連
 Connection ct = DriverManager.getConnection("");
 3.建立CallableStatement
 CallableStatement cs = 。。。
 
 ?如何使用過程返回值？？
 
 函數：函數和過程本身是一家，函數用於返回特定的資料，當建立函數時，在函數頭部必須包含return語句
 
 函數案例：
  案例：輸入僱員姓名，返回該僱員的年薪
 create function sp_fun2(spName carchar2) return
 number is yearSal number(7,2);
 begin
 --執行部分
 select sal*12+nvl(comm,0)*12 into yearSal from emp where ename=spName;
 return yearSal;
 end;
 調用
 1）在sqlpls中進行函數調用
 tome number
 call sp_fun2('scott') into:abc;
 print abc
 2）同樣我們可以在java程式中調用該函數
 select annual_income('SCOTT') from dual;
 可以通過rs.getInt(1)得到返回的結果
 
 觸發器：觸發器是指隱含的執行的預存程序。當定義觸發器時，必須要指定觸發器的事件和觸發的操作
 
 
 包：包用於在邏輯上組合過程和函數，它由包規範和包體兩部分組成
 建立包(聲明)：
 create package sp_package is
 procedure update_sal(name varchar2,newsal number);
 function annual_income(name varchar2) return number;
 end;
 /
 
 給包實現包體：
 create or replace package sp_package is
 procedure update_sal(name varchar2,newsal number)
 is
 begin
 update emp set sal=newsal where ename=name;
 end;
 function annual_income(name varchar2)
 return number is
 annual_salary number;
 begin
 select sal*12+nvl(comm,0) into annual_salary from emp
 where ename=name;
 return annual_salary;
 end;
 end;
 /
 
 如何調用包，調用包中的過程和函數,調用的時候，需要在過程和函數面前添加包名
 exec sp_package.update_sal('SCOTT',120);
 
 
 PL/SQL基礎 定義並使用變數
 標量（scalar）-常用類型
 在PL/SQL中
 標量定義的案例：
 （1）定義一個變長的字串
 v_ename varchar2(20)
 
 (2)使用一個變數
 資料賦值：    ：=
 declare
 c_tax_rate number(3,2) := 0.03;
 
 v_ename varchar2(5);
 v_sal   number(7,2);
 v_tax_sal number(7,2);
 begin
 --執行
 select ename,sal into v_ename,v_sal from emp where empno = &no;
 --計算所得稅
 v_tax_sal := v_sal*c_tax_rate;
 --輸出
 dbms_output.put_line(姓名是：'' || v_ename ||'工資是：'||v_sal||'交稅：'||v_tax_sal) ；
 end;
 
 標量（scalar）-使用%type類型：
 v_ename emp.ename%type;//表示變數v_ename定義的大小和表emp的欄位enamel大小一致
 
 複合變數（composite）介紹：
 用於存放多個值的變數，主要包括：
 類似進階語言的結構體
 type emp_recode_type is recode
 (
 name emp.ename%type,
 salary emp.sal%type,
 title emp.job%type
 );
 //定義了一個PL/SQL記錄類型emp_recode_type,類型包含是哪個資料name,salary,title
 sp_recode emp_recode_type;//定義了一個變數sp_recode，它的類型為emp_recode_type
 
 具體編寫：
 declare
 type emp_recode_type is record(name emp.ename%type,
 salary emp.sal%type,
 title emp.job%type);
 sp_recode emp_recode_type;
 begin
 select ename,sal,job into sp_record from emp where empno=7788;
 dbms_output.put_line('員工名：' || sp_record.name || '工資是' || sp_record.salary);
 end;
 
 複合類型：
 相當於進階進階語言中的數組，但是需要注意的是在進階語言中數組的下標不能為負數，但是此處是可以為負數的
 
 PL/SQL表執行個體：
 declare
 --index by binary_integer表示下標是整數，正整數和負整數都可以的
 type sp_table_type is table of emp.ename%type index by binary_integer;
 sp_table sp_table_type;
 begin
 select ename into sp_table(0) from emp where empno=7788;
 dbms_output.put_line('員工名：' || sp_table(0));
 end;
 
 ?如何返回多條資料類型？？
 
 複合變數-參照變數
 參照變數是指用於存放數值指標的變數，通過使用參照變數，可以使得應用程式共用相同的對象，從而降低佔用的控制項，在編寫
 PL/SQL程式時，可以使用遊標比那輛(ref cursor)和物件類型變數(ref obj_type)兩種參照變數類型
 
 參照變數-ref cursor遊標變數
 使用遊標時，當定義遊標時不需要
 
 declare
 --定義遊標類型sp_emp_cursor
 type sp_emp_cursor is ref cursor;//定義了一個遊標
 --定義遊標變數
 test_cursor sp_emp_cursor;
 --定義變數
 v_ename emp.ename%type;
 v_sal emp.sal%type;
 begin
 --執行
 --把test_cursor 和一個 select結合,即test_cursor指向結果集
 open test_cursor for select ename,sal from emp where deptno=&no;
loop
 fetch test_cursor into v_ename,v_sal;
 --判斷工資高低，決定是否更新
 
 --判斷是否test_cursor為空白
 exit when test_cursor%notfound;
 dbms_output.put_line('名字：' || v_name || '工資：' || v_sal);
end loop;
 end;
 
 複合變數-巢狀表格
 
 複合變數-複合表