Java,J2EE,Weblogic,Oracle

          java項(xiàng)目隨筆
          隨筆 - 90, 文章 - 6, 評(píng)論 - 61, 引用 - 0

          導(dǎo)航

          常用鏈接

          留言簿(4)

          最新隨筆

          最新評(píng)論

          閱讀排行榜

          評(píng)論排行榜

          Oracle學(xué)習(xí)筆記(5) 在PLSQL中使用游標(biāo)獲取數(shù)據(jù)

          這是第五章的學(xué)習(xí)筆記,學(xué)習(xí)完第四章的數(shù)據(jù)庫操作和事務(wù)之后,開始要學(xué)習(xí)游標(biāo)的使用了……,希望大家能多給俺一些支持啊!
              編程時(shí)使用的工具是PLSQL Developer 7.1.4

          隱式游標(biāo)
          隱式游標(biāo)的屬性等在第四章筆記中已記錄了一部分
          如果要確保屬性指向想要的SQL語句,那么就在SQL語句執(zhí)行后,立即將屬性值保存到一個(gè)本地變量中
          用存儲(chǔ)過程來實(shí)現(xiàn)這種效果:
          先定義一個(gè)存儲(chǔ)過程remove_from_emp ,用于從雇員中刪除一個(gè)員工
          在存儲(chǔ)過程的參數(shù)中 in 表示輸入,out 表示輸出
          Sql代碼 復(fù)制代碼
          1. create or replace procedure remove_from_emp(empno_in in employee.empno%type)   
          2. is  
          3. begin  
          4.    delete from employee where empno = empno_in;   
          5.    dbms_output.put_line('刪除了' || sql%rowcount || '條記錄!');   
          6. end;   
          7. /   
          8. -- 定義一個(gè)存儲(chǔ)過程來調(diào)用存儲(chǔ)過程remove_from_emp,并保存隱式游標(biāo)的屬性值   
          9. create or replace procedure show_emp_count is  
          10.    i_count integer;   
          11.    i_numfound pls_integer;   
          12. begin  
          13.    select count(*) into i_count from employee;   
          14.    -- 將屬性值做一個(gè)快照   
          15.    i_numfound := sql%rowcount;   
          16.    -- 注意employee表中沒有編號(hào)為99的員工   
          17.    -- 調(diào)用存儲(chǔ)過程remove_from_emp   
          18.    remove_from_emp(1);   
          19.    -- 現(xiàn)在可以用前一條語句的屬性值輸出   
          20.    dbms_output.put_line(i_numfound);   
          21. end;   
          22. /  

          顯式游標(biāo)(PL/SQL塊中使用顯示游標(biāo)的本個(gè)步驟:聲明、打開、提取記錄、關(guān)閉)
          在程序包規(guī)范中聲明一個(gè)游標(biāo)類型(我們可以在PL/SQL塊或者包的定義部分聲明游標(biāo)類型)
          Sql代碼 復(fù)制代碼
          1. create or replace package types   
          2. is  
          3. type emp_cur5 is ref cursor;   
          4. end;   
          5. /   
          6. declare  
          7.    -- 定義一個(gè)不帶參數(shù)的游標(biāo)emp_cur1,游標(biāo)的結(jié)果是employee表中員工的集合   
          8.    cursor emp_cur1 is select * from employee;   
          9.    -- 定義一個(gè)帶參數(shù)的游標(biāo)emp_cur2,游標(biāo)的結(jié)果集是匹配由游標(biāo)傳遞過來的員工編號(hào)的員工姓名和加入公司的時(shí)間   
          10.    cursor emp_cur2 (empno_in in number) is select name,hiredate from employee where empno = empno_in;   
          11.    -- 定義一個(gè)帶有return 子句的游標(biāo),游標(biāo)的結(jié)果集是員工編號(hào)為1的employee表中所有的列   
          12.    cursor emp_cur3 return employee%rowtype is select * from employee where empno = 1;   

          如果想通過游標(biāo)更新數(shù)據(jù),必須在select 語句后加上for update 子句,
          該select 語句涉及的所有行都會(huì)被鎖住
          如果是加上了for update of 列名,那么只有在for update 子句中引用了某個(gè)表的列時(shí),
          該表中的行才會(huì)被鎖住,of 列表并不限制只能更改列給出的列,它只讓我們知道要更改什么
          可以在for update 子句后添加一個(gè)nowait 關(guān)鍵字,用于告訴Oracle如果表已經(jīng)被其他用戶
          鎖住,就不需要等待了,這樣控制權(quán)會(huì)立即返回給我們的程序,如果沒有nowait子句進(jìn)程就
          會(huì)阻塞,直到表可用(commit或rollback)為止
          可以在Select into、Fetch into、Returning into子句中使用Bulk Collect將數(shù)據(jù)輸出到集合中
          Sql代碼 復(fù)制代碼
          1.    cursor emp_cur4 (empno_in in number) is select name,salary from employee where empno = empno_in for update;   
          2.    -- 應(yīng)該總是將游標(biāo)行提取到用%rowtype定義的記錄中,這樣更靈活(表字段改變了不需要更改fetch語句)   
          3.    emp_row1 employee%rowtype;   
          4.    -- 基于游標(biāo)定義一個(gè)記錄   
          5.    emp_row2 emp_cur2%rowtype;   
          6.    emp_row3 emp_cur3%rowtype;   
          7.    emp_row4 emp_cur4%rowtype;   
          8.    n_salary number(10,2);   
          9.    n_empno employee.empno%type := &員工編號(hào):;   
          10.       
          11.    -- 游標(biāo)變量和ref cursor   
          12.    -- 定義ref cursor 類型的游標(biāo)變量   
          13.    -- 創(chuàng)建一個(gè)強(qiáng)類型的引用游標(biāo)類型   
          14.    type emp_ref_type1 is ref cursor return employee%rowtype;   
          15.    -- 創(chuàng)建一個(gè)弱類型的引用游標(biāo)類型(弱類型的游標(biāo)變量比強(qiáng)類型的游標(biāo)變量更靈活)   
          16.    type emp_ref_type2 is ref cursor;   
          17.    -- 定義實(shí)際的游標(biāo)變量   
          18.    emp_ref1 emp_ref_type1;   
          19.    emp_ref2 emp_ref_type2;   
          20.    -- 從Oracle9i 開始提供了一個(gè)名為sys_refcursor的預(yù)定義的Oracle系統(tǒng)游標(biāo),   
          21.    -- 它相當(dāng)于弱類型游標(biāo),使用它不需要定義游標(biāo)變量   
          22.    sys_cursor sys_refcursor;   
          23.    -- 定義一個(gè)行類型的集合   
          24.    type emp_table_type is table of employee%rowtype index by binary_integer;   
          25.    emp_table emp_table_type;   
          26.    type emp_info_type is record(name employee.name%type,job employee.job%type);   
          27.    emp_info emp_info_type;   
          28. begin  

          打開游標(biāo):open 顯式游標(biāo)名 (參數(shù)列表)
          一旦打開了顯式游標(biāo),就可以從游標(biāo)中提取記錄,直到?jīng)]有記錄留在游標(biāo)中
          打開游標(biāo)時(shí),PL/SQL就開始執(zhí)行該游標(biāo)的Select 查詢,但是實(shí)際上并不返回任何行,
          返回行的任務(wù)是由后面的Fetch(讀取)…… into(賦值給)……  語句完成的:
          fetch 游標(biāo)名 into 記錄或變量列表
          Sql代碼 復(fù)制代碼
          1. open emp_cur1;   
          2. -- 提取記錄   
          3. -- 如果游標(biāo)只返回一行可以用if、loop或for來判斷獲得數(shù)據(jù),如果游標(biāo)返回多行可以用loop或for來循環(huán)獲得數(shù)據(jù)   
          4. loop   
          5.    fetch emp_cur1 into emp_row1;   
          6.    exit when emp_cur1%notfound;   
          7.    dbms_output.put_line('員工' || emp_row1.name || '的工資是:' || emp_row1.salary);   
          8. end loop;   
          9. -- 關(guān)閉游標(biāo)并釋放資源   
          10. close emp_cur1;   
          11. -- 打開帶參數(shù)的游標(biāo)   
          12. -- 游標(biāo)for 循環(huán)能很好的簡(jiǎn)化游標(biāo)的開發(fā),我們不再需要聲明記錄,不再需要Open、Fetch和Close語句   
          13. -- 也不再需要%found屬性檢測(cè)記錄,一切Oracle隱式的幫我們完成了   
          14. for emp_row2 in emp_cur2(n_empno) loop   
          15.    dbms_output.put_line('員工' || emp_row2.name || '加入公司的時(shí)間是 ' || emp_row2.hiredate);   
          16. end loop;   
          17. -- 打開帶return 子句的游標(biāo)   
          18. open emp_cur3;   
          19. fetch emp_cur3 into emp_row3;   
          20. if emp_cur3%found then  
          21.    dbms_output.put_line('員工' || emp_row3.name || '其職位是' || emp_row3.job || ',加入公司的時(shí)間是 ' || emp_row3.hiredate);   
          22. end if;   
          23. close emp_cur3;   
          24. -- 打開帶for update 子句的游標(biāo),將指定編號(hào)的員工工資增加500元   
          25. open emp_cur4(n_empno);   
          26. fetch emp_cur4 into emp_row4;  

          where current of 游標(biāo)名 子句能很容易的修改最近提取的數(shù)據(jù)行(也就是當(dāng)前游標(biāo)指向的位置),
          這樣的好處是,如果表表結(jié)構(gòu)發(fā)生了改變,我們只需要更改Select語句的Where子句即可,而不
          需要更新每個(gè)SQL語句
          Sql代碼 復(fù)制代碼
          1.    if emp_cur4%found then  
          2.       update employee set salary = salary + 500 where current of emp_cur4; --returning salary into n_salary;   
          3.    end if;   
          4.    commit;   
          5.    n_salary := emp_row4.salary + 500;   
          6.    dbms_output.put_line('員工' || emp_row4.name || '原來的工資是' || emp_row4.salary || '元,增加工資后現(xiàn)在的工資是' || n_salary || '元');   
          7.    close emp_cur4;   
          8.       
          9.    -- 打開強(qiáng)類型游標(biāo)變量   
          10.    open emp_ref1 for select * from employee order by salary;   
          11.    -- 在游標(biāo)變量中提取數(shù)據(jù)   
          12.    fetch emp_ref1 bulk collect into emp_table;   
          13.    for i in 1..emp_table.count loop   
          14.        dbms_output.put_line(emp_table(i).name || '   本月工資 ' || emp_table(i).salary);   
          15.    end loop;   
          16.    -- 關(guān)閉游標(biāo)變量   
          17.    close emp_ref1;   
          18.    -- 打開弱類型游標(biāo)變量   
          19.    open emp_ref2 for select name,job from employee;   
          20.    loop   
          21.       fetch emp_ref2 into emp_info;   
          22.       exit when emp_ref2%notfound;   
          23.       dbms_output.put_line(emp_info.name || '的工作是 ' || emp_info.job);    
          24.    end loop;   
          25.    close emp_ref2;   
          26.    -- 打開Oracle系統(tǒng)游標(biāo)   
          27.    open sys_cursor for select name,hiredate from employee order by hiredate desc;   
          28.    loop   
          29.       fetch sys_cursor into emp_info;   
          30.       exit when sys_cursor%notfound;   
          31.       dbms_output.put_line(emp_info.name || '加入公司的時(shí)間是 ' || emp_info.job);    
          32.    end loop;   
          33.    close sys_cursor;   
          34. exception   
          35.    when NO_DATA_FOUND then dbms_output.put_line('查詢不到員工編號(hào)為' || n_empno || '的員工!');   
          36.    when TOO_MANY_ROWS then dbms_output.put_line('數(shù)據(jù)完整性錯(cuò)誤,員工編號(hào)' || n_empno || '重復(fù)!');   
          37.    when OTHERS then dbms_output.put_line('PL/SQL執(zhí)行錯(cuò)誤!' || sqlerrm);   
          38. end;   
          39. /  
          轉(zhuǎn)載:http://www.javaeye.com/topic/624079

          posted on 2010-04-05 00:27 龔椿深 閱讀(1935) 評(píng)論(0)  編輯  收藏


          只有注冊(cè)用戶登錄后才能發(fā)表評(píng)論。


          網(wǎng)站導(dǎo)航:
           
          主站蜘蛛池模板: 耿马| 股票| 上栗县| 阿坝| 峨边| 凤山县| 玛曲县| 鞍山市| 玉环县| 沾化县| 延安市| 北安市| 阜城县| 平湖市| 洛阳市| 南平市| 磐石市| 济宁市| 蓬莱市| 胶南市| 长顺县| 于田县| 樟树市| 尉犁县| 青海省| 大安市| 二连浩特市| 登封市| 卓尼县| 灌云县| 鄢陵县| 临夏县| 双牌县| 宁晋县| 醴陵市| 成武县| 永春县| 丰顺县| 灵宝市| 昭平县| 威海市|