Oracle遊標（游標）

標籤：



watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvdGFuZ2xpdXFpbmc=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast" >

表、select語句、遊標：返回結果都能是一個集合。

注意：遊標的結果是一個集合。

--查詢並列印員工的姓名和薪水set serveroutput on/*游標:1. 游標的屬性： %isopen    %rowcount(返回的行數)              %notfound  %found2. 預設情況下。一次性開啟300個游標SQL> show parameter  cursorNAME                                 TYPE        VALUE------------------------------------ ----------- ---------------cursor_sharing                       string      EXACTcursor_space_for_time                boolean     FALSEopen_cursors                         integer     300session_cached_cursors               integer     20改動游標:alter system set open_cursors=400;*/declare  --定義一個游標  cursor cemp is select ename,sal from emp;  pename emp.ename%type;引用變數  psal   emp.sal%type;引用變數begin  open cemp;  loop    --取一條記錄    fetch cemp into pename,psal;    --退出： fetch沒有取到    exit when cemp%notfound;        dbms_output.put_line(pename||'的薪水是'||psal);    end loop;  close cemp;end;/

--查詢某個部門的員工姓名set serveroutput ondeclare  cursor cemp(dno number) is select ename from emp where deptno=dno;  pename emp.ename%type;begin  open cemp(20);  loop    fetch cemp into pename;    exit when cemp%notfound;        dbms_output.put_line(pename);  end loop;  close cemp;end;/

/*SQL語句select to_char(hiredate,'YYYY') from emp--> 游標 --> 迴圈 --> 允出準則變數:count80 number := 0;count81 number := 0;count82 number := 0;count87 number := 0;*/set serveroutput ondeclare  cursor cemp is select to_char(hiredate,'YYYY') from emp;  phiredate varchar2(4);    count80 number := 0;  count81 number := 0;  count82 number := 0;  count87 number := 0;begin  open cemp;  loop    --取一個員工的年份    fetch cemp into phiredate;    exit when cemp%notfound;        --推斷年份    if phiredate = '1980' then count80 := count80+1;      elsif phiredate = '1981' then count81 := count81+1;      elsif phiredate = '1982' then count82 := count82+1;      else count87 := count87 + 1;    end if;  end loop;  close cemp;    --輸出  dbms_output.put_line('Total:'||(count80+count81+count82+count87));  dbms_output.put_line('1980:'||count80);  dbms_output.put_line('1981:'||count81);  dbms_output.put_line('1982:'||count82);  dbms_output.put_line('1987:'||count87);end;/     

/*SQL語句:select empno,sal from emp order by sal--> 游標 --> 迴圈  --> 允出準則: 1. 總額> 5w  2. 全部人漲完變數:漲工資的人數： countEmp number := 0;漲後的工資總額: salTotal number;                1. select sum(sal) into salTotal from emp;                2. 漲後 = 漲前 + sal * 0.1練習： 工資總額不能超過5w                */set serveroutput ondeclare  cursor cemp is select empno,sal from emp order by sal;  pempno emp.empno%type;  psal   emp.sal%type;  --漲工資的人數：   countEmp number := 0;    --漲後的工資總額:   salTotal number;begin  --得到工資初始值  select sum(sal) into salTotal from emp;  open cemp;  loop    --1. 總額> 5w     exit when salTotal > 50000;    --取一個員工    fetch cemp into pempno,psal;    --2. 全部人漲完    exit when cemp%notfound;        --漲工資    update emp set sal=sal*1.1 where empno=pempno;    --人數+1    countEmp := countEmp +1;    --總額    salTotal := salTotal + psal * 0.1;  end loop;  close cemp;    commit;  dbms_output.put_line('人數:'||countEmp||'   漲後的工資總額:'||salTotal);end;/      

/*SQL語句:部門: select deptno from dept --> 游標 --> 迴圈  --> 允出準則部門中員工的薪水: select sal from emp where deptno=??                   --> 帶參數的游標 --> 迴圈  --> 允出準則變數:每一個段的人數:count1 number; count2 number; count3 number;部門的工資總額:salTotal number;1. 累加2. select sum(sal) into salTotal from emp where deptno=?
?
*/set serveroutput ondeclare  --部門  cursor cdept is select deptno from dept;  pdeptno dept.deptno%type;  --部門中員工的薪水  cursor cemp(dno number) is select sal from emp where deptno=dno;  psal emp.sal%type;    --每一個段的人數:  count1 number; count2 number; count3 number;  --部門的工資總額:  salTotal number;begin  open cdept;  loop    --取一個部門    fetch cdept into pdeptno;    exit when cdept%notfound;    --初始化    count1:=0; count2:=0; count3:=0;    --部門的工資總額    select sum(sal) into salTotal from emp where deptno=pdeptno;    --部門中員工的薪水    open cemp(pdeptno);    loop      -- 取一個員工的薪水      fetch cemp into psal;      exit when cemp%notfound;            --推斷薪水      if psal < 3000 then count1:=count1+1;        elsif psal>=3000 and psal< 6000 then count2:=count2+1;        else count3:=count3+1;      end if;        end loop;    close cemp;    --儲存當前部門    insert into msg values(pdeptno,count1,count2,count3,nvl(salTotal,0));  end loop;  close cdept;  commit;    dbms_output.put_line('完成');end;/

Oracle遊標（游標）