PL/SQL塊
  declare

  begin
   --SQL語句
   --直接寫的SQL語句(DML/TCL)
   --間接寫execute immediate <DDL/DCL命令字符串>
   --select 語句
        <1>必須帶有into子句
         select empno into eno from emp
           where empno =7369;
        <2>只能查到一行**********
        <3>字段個數(shù)必須和變量的個數(shù)一致

  exception
    --異常
    when <異常名字> then --特定異常
        <處理語句>
    when others then  --所有異常都可捕獲
        <處理語句>  
  end;
0
<例子>
   編寫程序 向DEPT表中插入一條記錄，
   從鍵盤輸入數(shù)據(jù)，如果
      數(shù)據(jù)類型輸入錯誤要有提示
      無法插入記錄 也要有提示
      只能輸入正數(shù),如果有負(fù)數(shù)提示
   declare
    n number;
    no dept.deptno%type;
    nm dept.dname%type;
    lc dept.loc%type;
    exp exception;   --異常的變量
    exp1 exception;
    num number:=0;  --計數(shù)器
    pragma exception_init(exp,-1); --預(yù)定義語句
         --(-1錯誤和異常變量關(guān)聯(lián))
    pragma exception_init(exp1,-1476);
    e1 exception; --自定義異常變量
   begin
    --輸入值
     no := '&編號';--&<字段>，會彈出輸入框
     num := num + 1;
     if no < 0 then
        raise e1;    --自定義異常的引發(fā)
     end if;

     nm := '&名稱';
     num := num +1;

     lc := '&地址'; 
     num := num +1;

     n := 10 /0;    

     insert into dept values (no,nm,lc);
     num := num +1;
     commit;

   exception
     --自定義異常
     when e1 then
        dbms_output.put_line('編號不能為負(fù)數(shù)');
     --數(shù)據(jù)類型不對
     when value_error then
        if num =0 then 
         dbms_output.put_line('編號數(shù)據(jù)類型不對');
        elsif num = 1 then
         dbms_output.put_line('名稱數(shù)據(jù)類型不對');
        elsif num =2 then
         dbms_output.put_line('地址數(shù)據(jù)類型不對');
        end if;
        rollback;
     --主鍵沖突
     when exp then
         --sqlcode全局變量 異常錯誤號
         --sqlerrm全局變量 異常的文字信息 
         --dbms_output.put_line('異常的編號:'||sqlcode);
         --dbms_output.put_line('異常的內(nèi)容:'||sqlerrm);
         --dbms_output.put_line('編號已存在') ;
         rollback;
     --非預(yù)定義異常(關(guān)聯(lián)錯誤號)
     when exp1 then
         --dbms_output.put_line('0做了除數(shù)') ;
         raise_application_error(-20001,'0做了除數(shù)'); --引起一個自定義的錯誤
         --預(yù)先保留-20001 到 -29999編號
         rollback;
     --其他的異常
     when others then
         dbms_output.put_line('異常的編號:'||sqlcode);
         dbms_output.put_line('異常的內(nèi)容:'||sqlerrm);
        -- dbms_output.put_line('出現(xiàn)錯誤');
         rollback;
   end; 

--    insert into dept values (40,'asdf','asdf');
<簡單的做法>
  --存放異常的
   create table save_exp(
      bh number,
      wz varchar2(1000)
   );

   declare
    no dept.deptno%type;
    nm dept.dname%type;
    lc dept.loc%type;
    errno number;
    errtext varchar2(1000);
   begin
     no := '&編號';
     nm := '&名稱';
     lc := '&地址';
 
     insert into dept values (no,nm,lc);
     commit;
   exception
    when others then
      rollback;
      errno := sqlcode;
      errtext := sqlerrm;      
      insert into save_exp values (errno,errtext);
      commit;
   end;

<游標(biāo)>  內(nèi)存中的一塊區(qū)域,存放的是select 的結(jié)果
  

   1。 隱式游標(biāo)
     單條sql語句所產(chǎn)生的結(jié)果集合
       用關(guān)鍵字SQL表示隱式游標(biāo)
        4個屬性 %rowcount  影響的記錄的行數(shù)  整數(shù)
                %found     影響到了記錄 true
                %notfound  沒有影響到記錄 true
                %isopen    是否打開  布爾值 永遠(yuǎn)是false

         多條sql語句 隱式游標(biāo)SQL永遠(yuǎn)指的是最后一條sql語句的結(jié)果
         主要使用在update 和 delete語句上     

   2。 顯式游標(biāo)
      select語句上 使用顯式游標(biāo)
      明確能訪問結(jié)果集
         for循環(huán)游標(biāo)
         參數(shù)游標(biāo)
           解決多行記錄的查詢問題
         fetch游標(biāo)

顯式游標(biāo)
   需要明確定義
   (1)FOR循環(huán)游標(biāo) (常用的一種游標(biāo))

 --<1>定義游標(biāo)
 --<2>定義游標(biāo)變量
 --<3>使用for循環(huán)來使用這個游標(biāo)

  --前向游標(biāo) 只能往一個方向走
  --效率很高

      declare
        --類型定義
        cursor cc is select empno,ename,job,sal
         from emp where job = 'MANAGER';
        --定義一個游標(biāo)變量
        ccrec cc%rowtype;

      begin
         --for循環(huán)
         for ccrec in cc loop
            dbms_output.put_line(ccrec.empno||'-'||ccrec.ename||'-'||ccrec.job||'-'||ccrec.sal);

         end loop;
      
      end;
   (2) fetch游標(biāo)
     --使用的時候 必須要明確的打開和關(guān)閉

      declare
        --類型定義
        cursor cc is select empno,ename,job,sal
         from emp where job = 'MANAGER';
        --定義一個游標(biāo)變量
        ccrec cc%rowtype;

      begin
        --打開游標(biāo)
         open cc;
        --loop循環(huán)
         loop
            --提取一行數(shù)據(jù)到ccrec中
            fetch cc into ccrec;        
            --判斷是否提取到值，沒取到值就退出
            --取到值cc%notfound 是false
            --取不到值cc%notfound 是true
            exit when cc%notfound;
            dbms_output.put_line(ccrec.empno||'-'||ccrec.ename||'-'||ccrec.job||'-'||ccrec.sal);           

         end loop;
        --關(guān)閉
         close cc; 
      
      end;
  游標(biāo)的屬性4種
       %notfound  fetch是否提到數(shù)據(jù) 沒有true 提到false
       %found      fetch是否提到數(shù)據(jù) 有true 沒提到false
       %rowcount  已經(jīng)取出的記錄的條數(shù)
       %isopen    布爾值 游標(biāo)是否打開

 declare
        --類型定義
        cursor cc is select empno,ename,job,sal
         from emp where job = 'MANAGER';
        --定義一個游標(biāo)變量
        ccrec cc%rowtype;
  

      begin
        --打開游標(biāo)
         open cc;
        --loop循環(huán)
         loop
            --提取一行數(shù)據(jù)到ccrec中
            fetch cc into ccrec;        
            --判斷是否提取到值，沒取到值就退出
            --取到值cc%notfound 是false
            --取不到值cc%notfound 是true
            exit when (cc%notfound or cc%rowcount =3);

            dbms_output.put_line(cc%rowcount||'-'||ccrec.empno||'-'||ccrec.ename||'-'||ccrec.job||'-'||ccrec.sal);           

         end loop;
        --關(guān)閉
         close cc; 
      
      end;

<例子>
  declare
       cursor cc is select dept.dname,
        emp.ename,emp.sal from
         dept,emp where dept.deptno = emp.deptno;
       ccrec cc%rowtype;
  begin
      for ccrec in cc loop
       dbms_output.put_line(ccrec.dname||'-'||ccrec.ename||'-'||ccrec.sal);
      end loop;
  end; 
   (3)參數(shù)游標(biāo)
 按部門編號的順序輸出部門經(jīng)理的名字
     declare
       --部門
       cursor c1 is select deptno from dept;
       --參數(shù)游標(biāo)c2,定義參數(shù)的時候，只能指定類型,不能指定長度 
       --參數(shù)只能出現(xiàn)在select語句=號的右側(cè)
       cursor c2(no number,pjob varchar2) is select emp.* from emp
         where deptno = no and job=pjob;

    /*
       no = 10  pjob = 'MANAGER'
          select * from emp where deptno = 10 and job = 'MANAGER';  
     */
       c1rec c1%rowtype;
       c2rec c2%rowtype;
       --定義變量的時候要指定長度
       v_job varchar2(20);
     begin
       --部門
        for c1rec in c1 loop
          --參數(shù)在游標(biāo)中使用
          for c2rec in c2(c1rec.deptno,'MANAGER') loop
            dbms_output.put_line(c1rec.deptno||'-'||c2rec.ename);
          end loop;
        end loop;
     end;
<綜合例子>
  求購買的商品包括了顧客"Dennis"所購買商品的顧客（姓名）；**************
   思路:
      Dennis (A,B)
      別的顧客 (A,B,C) (A,C) (B,C)  C
  declare  
   --游標(biāo)聲明
   --Dennis所購買的商品
   cursor cdennis is select productid
         from purcase where customerid=(
           select customerid from
           customer where name = 'Dennis');

   --除Dennis以外的每個顧客
   cursor ccust is select customerid
        from customer where name <> 'Dennis';

   --每個顧客購買的商品
   cursor cprod(id varchar2) is
     select productid from purcase
          where customerid = id;

   j number ;
   i number;
   c1rec cdennis%rowtype;
   c2rec ccust%rowtype;
   c3rec cprod%rowtype;
   cname varchar2(10);

  begin
    --顧客循環(huán)
    for c2rec in ccust loop
     i:=0;
     j:=0;
     --Dennis買的商品循環(huán)
     for c1rec in cdennis loop
        i := i + 1;
       
      --每個顧客買的東西循環(huán)
       for c3rec in cprod(c2rec.customerid) loop
           --判斷其他顧客買的某件商品是否為Dennis也有的
           if (c3rec.productid = c1rec.productid) then
               j := j + 1;
           end if;

        end loop;  
     
     end loop;
                       
       if (i=j) then
        select name into cname from
           customer where customerid = c2rec.customerid;
        DBMS_output.put_line(cname);       
       end if;

    end loop;

  end;
  (4)引用游標(biāo)/動態(tài)游標(biāo)
        select語句是動態(tài)的
     declare
       --定義一個類型(ref cursor)弱類型   
       type cur is ref cursor;
         --強(qiáng)類型(返回的結(jié)果集有要求)
       type cur1 is ref cursor return emp%rowtype;
       --定義一個ref cursor類型的變量  
       cura cur;
       --在PL/SQL中將一個記錄聲明為具有相同類型的數(shù)據(jù)庫行的作法是很常見的。PL/SQL提供了%ROWTYPE運(yùn)算符，使得這樣的操作更為方便
       c1rec emp%rowtype;
       c2rec dept%rowtype;
     begin
  DBMS_output.put_line('輸出員工')   ;      
       open cura for select * from emp;
       loop
       --從游標(biāo)得到一行數(shù)據(jù)使用FETCH命令。每一次提取數(shù)據(jù)后，游標(biāo)都指向結(jié)果集的下一行
       --如果有多行返回結(jié)果，可以使用循環(huán)并用游標(biāo)屬性為結(jié)束循環(huán)的條件
         fetch cura into c1rec;  
         exit when cura%notfound;
         DBMS_output.put_line(c1rec.ename)   ;
       end loop ;
  DBMS_output.put_line('輸出部門')   ;
       open cura for select * from dept;
       loop
         fetch cura into c2rec;  
         exit when cura%notfound;
         DBMS_output.put_line(c2rec.dname)   ;

       end loop; 
       close cura;
    end;

>>>>>存儲過程和函數(shù)
  沒有名字的PL/SQL塊(匿名)
  有名字的PL/SQL塊(子程序-存儲過程和函數(shù))
存儲過程
      create or replace procedure p1
      as
      begin
      exception
      end;

<最簡單的存儲過程>
       create or replace procedure p_jd
       as
         hello varchar2(20); 
       begin
        select 'Hello World' into hello from dual;
        dbms_output.put_line(hello);
       end;
 執(zhí)行存儲過程的方法--PL/SQL中如何執(zhí)行？
 
      <1> execute p_jd;     (SQL*PLUS中SQL>)

      <2> --PLSQL中寫法，上面過程沒有參數(shù)，不用傳
          begin
           p_jd;
          end;
 

結(jié)果：
 

 帶參數(shù)的存儲過程
   --輸入?yún)?shù)in
   --不寫in的參數(shù)都是輸入?yún)?shù)
   --根據(jù)部門編號查員工姓名
    create or replace procedure p_getemp(no  number)
    as
       cursor c1 is select * from emp
       where deptno = no;
       c1rec c1%rowtype;
    begin
       --不能寫no := 20; 因為：輸入?yún)?shù)是不能賦值的
       for c1rec in c1 loop
         dbms_output.put_line(c1rec.ename);
       end loop;
    end;    
   --輸出參數(shù)out
   --根據(jù)部門編號查出部門的平均工資,返回平均工資的值
   -- in 輸入 (在procedure中是不能賦值的)
   --  out 輸出 (在procedure中是能賦值的)
-- 定義參數(shù)是不能指定長度的
 --定義變量是必須指定長度的

    create or replace procedure p_getavgsal(no  number,avgsal out number)
    -- no   輸入?yún)?shù)
    -- avgsal  輸出參數(shù)
    as
     aa varchar2(10); --變量
    begin
       select avg(sal) into avgsal
       from emp where deptno = no; 
    end;    
    調(diào)用它只能使用PL/SQL塊
        declare
         av number;
        begin         
          p_getavgsal(10,av);
          --round函數(shù)為四舍五入
          dbms_output.put_line('平均工資:'||round(av,2));
        end;       
   --一個參數(shù)同時可以輸入,也可以輸出
   --輸入輸出參數(shù)
    create or replace procedure
    p_getavgsal(n in out number)
    as

    begin
       select avg(sal) into n
       from emp where deptno = n; 
    end;
   
   declare
         av number;
        begin         
          av  := 10;
          p_getavgsal(av);
          dbms_output.put_line('平均工資:'||round(av,2));
        end;       
  --帶多個參數(shù)的存儲過程
     create or replace procedure
      p_getM(no number,pjob varchar2) as

       --參數(shù)游標(biāo)c2,定義參數(shù)的時候
       --只能指定類型,不能指定長度 
       --參數(shù)只能出現(xiàn)在select語句=號的右側(cè)
       cursor c2(no1 number,pjob1 varchar2) is select * from emp
         where deptno = no1 and job = pjob1;

       c2rec c2%rowtype;
       --定義變量的時候要指定長度
       v_job varchar2(20);
   
     begin

          --參數(shù)在游標(biāo)中使用
          for c2rec in c2(no,pjob) loop
            dbms_output.put_line(c2rec.deptno||'-'||c2rec.ename);

          end loop;

     end;
   --sql
    調(diào)用方法:execute p_getm(10,'MANAGER'); --按位置
   -- no = 10 , pjob = 'MANAGER'
          
      execute p_getm(pjob => 'MANAGER',no => 10);
   --按參數(shù)的名字 來傳值

  函數(shù):
    必須要有返回值
    只能返回一個值
  
   --根據(jù)部門編號查出部門的平均工資,返回平均工資的值(利用函數(shù))
    create or replace function
    f_getavgsal(no number)
    return number
    as
      avgsal number(7,2);
    begin
       select avg(sal) into avgsal
       from emp where deptno = no;
       --返回值
       return avgsal;  
    end;  
 
--帶輸出參數(shù)
 --每個部門的平均工資和工資總額
 --一個函數(shù)返回2個值
create or replace function
    f_getavgsal(no number,sumsal out number)
    return number
    as
      avgsal number(7,2);
    begin
       --平均工資
       select avg(sal) into avgsal
       from emp where deptno = no;
       --工資總額
       select sum(sal) into sumsal
       from emp where deptno = no;
       --返回值
       return avgsal;  
    end;    

  --調(diào)用方法
     <1>PL/SQL塊調(diào)用
      declare
       aa number;
      begin
        aa := f_getavgsal(10)  ;
        dbms_output.put_line(to_char(aa));
      end; 
    <2> SQL語句來調(diào)用(DML)
       select f_getavgsal(10) from dual;

       select deptno,f_getavgsal(deptno) from dept;

    <3>
       create or replace function f1
       return number
       as
         update emp set comm = 1000
          where job='CLERK';
         return sql%rowcount;
       end;
      --select語句是無法調(diào)用它的,因為其中含有修改語句