oracle遊標使用的方方面面

OraFige發表於2011-12-20
Normal 0 false false false EN-US ZH-CN X-NONE-- 宣告遊標;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 desc;

   --計數器

  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(,...)

  --OVER

   --()

  --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;

 

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26464953/viewspace-713610/,如需轉載,請註明出處,否則將追究法律責任。

相關文章