Oracle 遊标示例

一，建表

create table dept(
       deptno number(10) primary key,
       dname varchar2(30),
       loc varchar2(30)
)；

create table emp(
       empno number(10) primary key,
       ename varchar2(30),
       job varchar2(30),
       mgr varchar2(30),
       hiredate number(10),
       sal number(10),
       comm number(10),
       deptno number(10),
       foreign key(deptno) references dept(deptno)
)；

insert into dept values(1, '技術部' ,'南泥灣');
insert into dept values(2, 'SALES' ,'深圳市');
insert into dept values(3, '事業部' ,'北京市');
insert into dept values(4, '服務部' ,'延安');
insert into dept values(5, '生産部' ,'南京市');
insert into dept values(6, '宣傳部' ,'上海市');
insert into dept values(7, '打雜部' ,'廣州市');
insert into dept values(8, '司令部' ,'重慶市');
insert into dept values(9, '衛生部' ,'長沙市');
insert into dept values(10, '文化部' ,'武岡市');
insert into dept values(11, '娛樂部' ,'紐約');
insert into dept values(12, '管理部' ,'倫敦');
insert into dept values(13, '行政部' ,'天津市');

insert into emp values(1, '關羽羽', 'CLERK' ,'劉備備', 20011109, 2000, 1000, 3);
insert into emp values(2, 'SMITH', 'CLERK' ,'劉備備', 20120101, 2000, 800, 6);
insert into emp values(3, '劉備備', 'MANAGER' ,'宋祖英', 20080808, 9000, 4000, 3);
insert into emp values(4, 'TOM', 'ENGINEER' ,'Steve', 20050612, 3000, 1000, 1);
insert into emp values(5, 'Steve', 'MANAGER' ,'宋祖英', 20110323, 80000, 9000, 1);
insert into emp values(6, '張飛飛', 'CLERK' ,'劉備備', 20101010, 2000, 1000, 3);
insert into emp values(7, 'SCOTT', 'CLERK' ,'劉備備', 20071204, 2000, 1000, 3);
insert into emp values(8, '宋祖英', 'Boss' ,'無', 20060603, 2000, 1000, 8);
insert into emp values(9, '曹仁人', 'SALESMAN' ,'曹操操', 20120130, 2000, 1000, 2);
insert into emp values(10, '曹操操', 'MANAGER' ,'宋祖英',20090815, 2000, 1000, 2);
insert into emp values(11, '醬油哥', 'HAPI' ,'毛澤東',20090215, 3, 1, 2);

二，示例

-- 聲明遊标；CURSOR cursor_name IS select_statement

--For 循環遊标
--（1）定義遊标
--（2）定義遊标變量
--（3）使用for循環來使用這個遊标
declare
       --類型定義
       cursor c_job
       is
       select empno,ename,job,sal
       from emp
       where job='MANAGER';
       --定義一個遊标變量v_cinfo c_emp%ROWTYPE ，該類型為遊标c_emp中的一行資料類型
       c_row c_job%rowtype;
begin
       for c_row in c_job loop
         dbms_output.put_line(c_row.empno||'-'||c_row.ename||'-'||c_row.job||'-'||c_row.sal);
       end loop;
end;


      
--Fetch遊标
--使用的時候必須要明确的打開和關閉

declare 
       --類型定義
       cursor c_job
       is
       select empno,ename,job,sal
       from emp
       where job='MANAGER';
       --定義一個遊标變量
       c_row c_job%rowtype;
begin
       open c_job;
         loop
           --提取一行資料到c_row
           fetch c_job into c_row;
           --判讀是否提取到值，沒取到值就退出
           --取到值c_job%notfound 是false 
           --取不到值c_job%notfound 是true
           exit when c_job%notfound;
            dbms_output.put_line(c_row.empno||'-'||c_row.ename||'-'||c_row.job||'-'||c_row.sal);
         end loop;
       --關閉遊标
      close c_job;
end;

--1：任意執行一個update操作，用隐式遊标sql的屬性%found,%notfound,%rowcount,%isopen觀察update語句的執行情況。
       begin
         update emp set ENAME='ALEARK' WHERE EMPNO=7469;
         if sql%isopen then
           dbms_output.put_line('Openging');
           else
             dbms_output.put_line('closing');
             end if;
          if sql%found then
            dbms_output.put_line('遊标指向了有效行');--判斷遊标是否指向有效行
            else
              dbms_output.put_line('Sorry');
              end if;
              if sql%notfound then
                dbms_output.put_line('Also Sorry');
                else
                  dbms_output.put_line('Haha');
                  end if;
                   dbms_output.put_line(sql%rowcount);
                   exception 
                     when no_data_found then
                       dbms_output.put_line('Sorry No data');
                       when too_many_rows then
                         dbms_output.put_line('Too Many rows');
                         end;
declare
       empNumber emp.EMPNO%TYPE;
       empName emp.ENAME%TYPE;
       begin
         if sql%isopen then
           dbms_output.put_line('Cursor is opinging');
           else
             dbms_output.put_line('Cursor is Close');
             end if;
             if sql%notfound then
               dbms_output.put_line('No Value');
               else
                 dbms_output.put_line(empNumber);
                 end if;
                 dbms_output.put_line(sql%rowcount);
                 dbms_output.put_line('-------------');
                 
                 select EMPNO,ENAME into  empNumber,empName from emp where EMPNO=7499;
                 dbms_output.put_line(sql%rowcount);
                 
                if sql%isopen then
                dbms_output.put_line('Cursor is opinging');
                else
                dbms_output.put_line('Cursor is Closing');
                end if;
                 if sql%notfound then
                 dbms_output.put_line('No Value');
                 else
                 dbms_output.put_line(empNumber);
                 end if;
                 exception 
                   when no_data_found then
                     dbms_output.put_line('No Value');
                     when too_many_rows then
                       dbms_output.put_line('too many rows');
                       end;
                   
                 
       
--2,使用遊标和loop循環來顯示所有部門的名稱
--遊标聲明
declare 
       cursor csr_dept
       is
       --select語句
       select DNAME
       from Depth;
       --指定行指針,這句話應該是指定和csr_dept行類型相同的變量
       row_dept csr_dept%rowtype;
begin
       --for循環
       for row_dept in csr_dept loop
           dbms_output.put_line('部門名稱:'||row_dept.DNAME);
       end loop;
end;


--3,使用遊标和while循環來顯示所有部門的的地理位置（用%found屬性）
declare
       --遊标聲明
       cursor csr_TestWhile
       is
       --select語句
       select  LOC
       from Depth;
       --指定行指針
       row_loc csr_TestWhile%rowtype;
begin
  --打開遊标
       open csr_TestWhile;
       --給第一行喂資料
       fetch csr_TestWhile into row_loc;
       --測試是否有資料，并執行循環
         while csr_TestWhile%found loop
           dbms_output.put_line('部門地點：'||row_loc.LOC);
           --給下一行喂資料
           fetch csr_TestWhile into row_loc;
         end loop;
       close csr_TestWhile;
end; 
select * from emp



       
--4,接收使用者輸入的部門編号，用for循環和遊标，列印出此部門的所有雇員的所有資訊(使用循環遊标)
--CURSOR cursor_name[(parameter[,parameter],...)] IS select_statement;
--定義參數的文法如下:Parameter_name [IN] data_type[{:=|DEFAULT} value]  

declare 
      CURSOR 
      c_dept(p_deptNo number)
      is
      select * from emp where emp.depno=p_deptNo;
      r_emp emp%rowtype;
begin
        for r_emp in c_dept(20) loop
            dbms_output.put_line('員工号：'||r_emp.EMPNO||'員工名：'||r_emp.ENAME||'工資：'||r_emp.SAL);
        end loop;
end;
select * from emp   
--5：向遊标傳遞一個工種，顯示此工種的所有雇員的所有資訊(使用參數遊标)
declare 
       cursor
       c_job(p_job nvarchar2)
       is 
       select * from emp where JOB=p_job;
       r_job emp%rowtype;
begin 
       for r_job in c_job('CLERK') loop
           dbms_output.put_line('員工号'||r_job.EMPNO||' '||'員工姓名'||r_job.ENAME);
        end loop;
end;
SELECT * FROM EMP

--6：用更新遊标來為雇員加傭金：(用if實作,建立一個與emp表一摸一樣的emp1表，對emp1表進行修改操作),并将更新前後的資料輸出出來 
--http://zheng12tian.iteye.com/blog/815770 
        create table emp1 as select * from emp;
        
declare
        cursor
        csr_Update
        is
        select * from  emp1 for update OF SAL;
        empInfo csr_Update%rowtype;
        saleInfo  emp1.SAL%TYPE;
begin
    FOR empInfo IN csr_Update LOOP
      IF empInfo.SAL<1500 THEN
        saleInfo:=empInfo.SAL*1.2;
       elsif empInfo.SAL<2000 THEN
        saleInfo:=empInfo.SAL*1.5;
        elsif empInfo.SAL<3000 THEN
        saleInfo:=empInfo.SAL*2;
      END IF;
      UPDATE emp1 SET SAL=saleInfo WHERE CURRENT OF csr_Update;
     END LOOP;
END;

--7:編寫一個PL/SQL程式塊，對名字以‘A’或‘S’開始的所有雇員按他們的基本薪水(sal)的10%給他們加薪(對emp1表進行修改操作)
declare 
     cursor
      csr_AddSal
     is
      select * from emp1 where ENAME LIKE 'A%' OR ENAME LIKE 'S%' for update OF SAL;
      r_AddSal csr_AddSal%rowtype;
      saleInfo  emp1.SAL%TYPE;
begin
      for r_AddSal in csr_AddSal loop
          dbms_output.put_line(r_AddSal.ENAME||'原來的工資:'||r_AddSal.SAL);
          saleInfo:=r_AddSal.SAL*1.1;
          UPDATE emp1 SET SAL=saleInfo WHERE CURRENT OF csr_AddSal;
      end loop;
end;
--8：編寫一個PL/SQL程式塊，對所有的salesman增加傭金(comm)500
declare
      cursor
          csr_AddComm(p_job nvarchar2)
      is
          select * from emp1 where   JOB=p_job FOR UPDATE OF COMM;
      r_AddComm  emp1%rowtype;
      commInfo emp1.comm%type;
begin
    for r_AddComm in csr_AddComm('SALESMAN') LOOP
        commInfo:=r_AddComm.COMM+500;
         UPDATE EMP1 SET COMM=commInfo where CURRENT OF csr_AddComm;
    END LOOP;
END;

--9：編寫一個PL/SQL程式塊，以提升2個資格最老的職員為MANAGER（工作時間越長，資格越老）
--（提示：可以定義一個變量作為計數器控制遊标隻提取兩條資料；也可以在聲明遊标的時候把雇員中資格最老的兩個人查出來放到遊标中。）
declare
    cursor crs_testComput
    is
    select * from emp1 order by HIREDATE asc;
    --計數器
    top_two number:=2;
    r_testComput crs_testComput%rowtype;
begin
    open crs_testComput;
       FETCH crs_testComput INTO r_testComput;
          while top_two>0 loop
             dbms_output.put_line('員工姓名：'||r_testComput.ENAME||' 工作時間：'||r_testComput.HIREDATE);
             --計速器減一
             top_two:=top_two-1;
             FETCH crs_testComput INTO r_testComput;
           end loop;
     close crs_testComput;
end;
    

--10：編寫一個PL/SQL程式塊，對所有雇員按他們的基本薪水(sal)的20%為他們加薪，
--如果增加的薪水大于300就取消加薪(對emp1表進行修改操作，并将更新前後的資料輸出出來) 
declare
    cursor
        crs_UpadateSal
    is
        select * from emp1 for update of SAL;
        r_UpdateSal crs_UpadateSal%rowtype;
        salAdd emp1.sal%type;
        salInfo emp1.sal%type;
begin
        for r_UpdateSal in crs_UpadateSal loop
           salAdd:= r_UpdateSal.SAL*0.2;
           if salAdd>300 then
             salInfo:=r_UpdateSal.SAL;
              dbms_output.put_line(r_UpdateSal.ENAME||':  加薪失敗。'||'薪水維持在：'||r_UpdateSal.SAL);
             else 
              salInfo:=r_UpdateSal.SAL+salAdd;
              dbms_output.put_line(r_UpdateSal.ENAME||':  加薪成功.'||'薪水變為：'||salInfo);
           end if;
           update emp1 set SAL=salInfo where current of crs_UpadateSal;
        end loop;
end;
     
--11:将每位員工工作了多少年零多少月零多少天輸出出來   
--近似
  --CEIL(n)函數：取大于等于數值n的最小整數
  --FLOOR(n)函數：取小于等于數值n的最大整數
  --truc的用法 http://publish.it168.com/2005/1028/20051028034101.shtml
declare
  cursor
   crs_WorkDay
   is
   select ENAME,HIREDATE, trunc(months_between(sysdate, hiredate) / 12) AS SPANDYEARS,
       trunc(mod(months_between(sysdate, hiredate), 12)) AS months,
       trunc(mod(mod(sysdate - hiredate, 365), 12)) as days
   from emp1;
  r_WorkDay crs_WorkDay%rowtype;
begin
    for   r_WorkDay in crs_WorkDay loop
    dbms_output.put_line(r_WorkDay.ENAME||'已經工作了'||r_WorkDay.SPANDYEARS||'年,零'||r_WorkDay.months||'月,零'||r_WorkDay.days||'天');
    end loop;
end;
  
--12:輸入部門編号，按照下列加薪比例執行(用CASE實作，建立一個emp1表，修改emp1表的資料),并将更新前後的資料輸出出來
--  deptno  raise(%)
--  10      5%
--  20      10%
--  30      15%
--  40      20%
--  加薪比例以現有的sal為标準
--CASE expr WHEN comparison_expr THEN return_expr
--[, WHEN comparison_expr THEN return_expr]... [ELSE else_expr] END
declare
     cursor
         crs_caseTest
          is
          select * from emp1 for update of SAL;
          r_caseTest crs_caseTest%rowtype;
          salInfo emp1.sal%type;
     begin
         for r_caseTest in crs_caseTest loop
         case 
           when r_caseTest.DEPNO=10
           THEN salInfo:=r_caseTest.SAL*1.05;
           when r_caseTest.DEPNO=20
           THEN salInfo:=r_caseTest.SAL*1.1;
           when r_caseTest.DEPNO=30
           THEN salInfo:=r_caseTest.SAL*1.15;
            when r_caseTest.DEPNO=40
           THEN salInfo:=r_caseTest.SAL*1.2;
         end case;
          update emp1 set SAL=salInfo where current of crs_caseTest;
        end loop;
end;

--13:對每位員工的薪水進行判斷，如果該員工薪水高于其所在部門的平均薪水，則将其薪水減50元，輸出更新前後的薪水，員工姓名，所在部門編号。
--AVG([distinct|all] expr) over (analytic_clause)
---作用：
--按照analytic_clause中的規則求分組平均值。
  --分析函數文法:
  --FUNCTION_NAME(<argument>,<argument>...)
  --OVER
  --(<Partition-Clause><Order-by-Clause><Windowing Clause>)
     --PARTITION子句
     --按照表達式分區(就是分組),如果省略了分區子句,則全部的結果集被看作是一個單一的組
     select * from emp1
DECLARE
     CURSOR 
     crs_testAvg
     IS
     select EMPNO,ENAME,JOB,SAL,DEPNO,AVG(SAL) OVER (PARTITION BY DEPNO ) AS DEP_AVG
     FROM EMP1 for update of SAL;
     r_testAvg crs_testAvg%rowtype;
     salInfo emp1.sal%type;
     begin
     for r_testAvg in crs_testAvg loop
     if r_testAvg.SAL>r_testAvg.DEP_AVG then
     salInfo:=r_testAvg.SAL-50;
     end if;
     update emp1 set SAL=salInfo where current of crs_testAvg;
     end loop;
end;

Oracle 遊标示例

繼續閱讀

英文月日年日期格式的實作

Oracle的基本操作

為什麼要選擇UniDAC

C# 位元組數組 byte[] 快速比較算法

C# 位元組數組、字元串轉化位元組數組轉字元串字元串轉位元組數組位元組數組轉整數整數轉位元組數組字元串轉位元組字元串轉數值數值轉字元串

C#用Zlib壓縮或解壓縮位元組數組

C# 比較兩個位元組數組是否相等

對于VS中scanf報錯永久解決，建立源檔案自動添加#define _CRT_SECURE_NO_WARNINGS 1

《C#進階程式設計》讀書筆記 -索引

CQ V1.0分詞bates(基于雙數組tire樹)—應該是目前最快的中文分詞算法

C#多線程——前台線程和背景線程

windows不能在本地計算機上運作oracleDbConsoleorcl

C#+ArcEngine代碼檢測ArcEngine Runtime是否安裝以及是否授權

SQL語言基礎：常用的資料查詢語句

七牛雲-C#SDK-上傳-前期準備

Oracle 批量查詢傳入List 傳回List