游標(biāo)操作的優(yōu)化：
創(chuàng)新互聯(lián)建站主要從事成都網(wǎng)站建設(shè)、網(wǎng)站制作、網(wǎng)頁(yè)設(shè)計(jì)、企業(yè)做網(wǎng)站、公司建網(wǎng)站等業(yè)務(wù)。立足成都服務(wù)涿鹿,十年網(wǎng)站建設(shè)經(jīng)驗(yàn),價(jià)格優(yōu)惠、服務(wù)專業(yè),歡迎來電咨詢建站服務(wù):18980820575
-- 有一個(gè)表格，存儲(chǔ)的是用戶的名字，將 emp 表中所有的用戶名轉(zhuǎn)換成小寫，再寫入這個(gè)表格
create table ename_emp(
ename varchar2(50)
);
-- 下面這個(gè)游標(biāo)的操作，需要 43s時(shí)間才能完成
declare
begin
for i in (select ename from emp_liebiao) loop
insert into ename_emp values(lower(i.ename));
commit;
end loop;
end;
-- 因?yàn)橛螛?biāo)是以行為單位進(jìn)行數(shù)據(jù)的操作的，所有游標(biāo)的效率是比較慢的，而且游標(biāo)需要消耗的內(nèi)存也是比較多的，我們需要將游標(biāo)以行進(jìn)行操作的方式，修改成一次性操作所有數(shù)據(jù)的批量的方式。
批量操作在游標(biāo)里面 叫做 bulk collect
第一步，創(chuàng)建一個(gè)表類型
type 表類型的名字 is table of 表名.列名 %type;
變量名 表類型的名字;
第三步，創(chuàng)建一個(gè)游標(biāo)，讀取某個(gè)查詢的結(jié)果
cursor 游標(biāo)名字 is select 查詢語(yǔ)句 ;
第四步，打開游標(biāo)
open 游標(biāo)名字;
第五步，捕獲游標(biāo)的數(shù)據(jù)，將內(nèi)容給到表類型的變量進(jìn)行保存
fetch 游標(biāo)名字 bulk collect into 變量名字 ;
第六步，使用 forall 語(yǔ)句，對(duì)數(shù)據(jù)進(jìn)行批量的操作
forall i in 變量 .first .. 變量 .last DML 語(yǔ)句操作 ;
第七步，關(guān)閉游標(biāo)
close 游標(biāo)名字 ;
declare
-- 創(chuàng)建表類型
type biao is table of emp_liebiao.ename%type;
b biao;
-- 創(chuàng)建游標(biāo)
cursor m is select ename from emp_liebiao;
begin
-- 打開游標(biāo)
open m;
-- 將游標(biāo)的內(nèi)容批量的給到變量
fetch m bulk collect into b;
-- 使用forall批量修改數(shù)據(jù)
forall i in b.first .. b.last insert into ename_emp values(lower(b(i)));
commit;
-- 關(guān)閉游標(biāo)
close m;
end;
練習(xí)：批量修改用戶的編號(hào)，讓編號(hào)+工資等級(jí)+部門，形成一個(gè)新的編號(hào)，存入下面的表格中，使用 bulk collect 來實(shí)現(xiàn)。
2000以下是 C ，2000-3000是 B ，3000以上是 A ，
例如 SMITH ， 新編號(hào)應(yīng)該是 7369_C_20
create table empno_emp(
empno varchar2(50)
);
declare
type biao is table of emp_liebiao%rowtype;
b biao;
cursor m is select * from emp_liebiao;
begin
open m;
fetch m bulk collect into b;
forall i in b.first..b.last
insert into empno_emp values(
b(i).empno||'_'||decode(sign(b(i).sal-2000)+sign(b(i).sal-3000),-2,'C',2,'A','B')||'_'||b(i).deptno
);
commit;
close m;
end;
--創(chuàng)建存儲(chǔ)
程
CREATE
OR
REPLACE
PROCEDURE
xxxxxxxxxxx_p
(
--參數(shù)IN表示輸入?yún)?shù)
OUT表示輸入?yún)?shù)
類型
使用任意Oracle
合
類型
is_ym
IN
CHAR
)
AS
--定義變量
vs_msg
VARCHAR2(4000);
--錯(cuò)誤信息變量
vs_ym_beg
CHAR(6);
--起始月份
vs_ym_end
CHAR(6);
--終止月份
vs_ym_sn_beg
CHAR(6);
--同期起始月份
vs_ym_sn_end
CHAR(6);
--同期終止月份
--定義游標(biāo)(簡(jiǎn)單
說
遍歷
結(jié)
集)
CURSOR
cur_1
IS
SELECT
area_code,CMCODE,SUM(rmb_amt)/10000
rmb_amt_sn,SUM(usd_amt)/10000
usd_amt_sn
FROM
BGD_AREA_CM_M_BASE_T
WHERE
ym
=
vs_ym_sn_beg
AND
ym
=
vs_ym_sn_end
GROUP
BY
area_code,CMCODE;BEGIN
--用輸入?yún)?shù)給變量賦初值
用
Oralce
SUBSTR
TO_CHAR
ADD_MONTHS
TO_DATE
等
用
函數(shù)
vs_ym_beg
:=
SUBSTR(is_ym,1,6);
vs_ym_end
:=
SUBSTR(is_ym,7,6);
vs_ym_sn_beg
:=
TO_CHAR(ADD_MONTHS(TO_DATE(vs_ym_beg,'yyyymm'),
-12),'yyyymm');
vs_ym_sn_end
:=
TO_CHAR(ADD_MONTHS(TO_DATE(vs_ym_end,'yyyymm'),
-12),'yyyymm');
--先刪除表
特定條件
數(shù)據(jù)
DELETE
FROM
xxxxxxxxxxx_T
WHERE
ym
=
is_ym;
--
用內(nèi)置
DBMS_OUTPUT
象
put_line
打印
影響
記錄行數(shù)
其
用
系統(tǒng)變量SQL%rowcount
DBMS_OUTPUT.put_line('del
月記錄='||SQL%rowcount||'條');
INSERT
INTO
xxxxxxxxxxx_T(area_code,ym,CMCODE,rmb_amt,usd_amt)
SELECT
area_code,is_ym,CMCODE,SUM(rmb_amt)/10000,SUM(usd_amt)/10000
FROM
BGD_AREA_CM_M_BASE_T
WHERE
ym
=
vs_ym_beg
AND
ym
=
vs_ym_end
GROUP
BY
area_code,CMCODE;
DBMS_OUTPUT.put_line('ins
月記錄='||SQL%rowcount||'條');
--遍歷游標(biāo)處理
更新
表
遍歷游標(biāo)
幾種
用for語(yǔ)句
其
比較直觀
種
FOR
rec
IN
cur_1
LOOP
UPDATE
xxxxxxxxxxx_T
SET
rmb_amt_sn
=
rec.rmb_amt_sn,usd_amt_sn
=
rec.usd_amt_sn
WHERE
area_code
=
rec.area_code
AND
CMCODE
=
rec.CMCODE
AND
ym
=
is_ym;
END
LOOP;
COMMIT;
--錯(cuò)誤處理部
OTHERS表示除
聲明外
任意錯(cuò)誤
SQLERRM
系統(tǒng)內(nèi)置變量保存
前錯(cuò)誤
詳細(xì)信息
EXCEPTION
WHEN
OTHERS
THEN
vs_msg
:=
'ERROR
IN
xxxxxxxxxxx_p('||is_ym||'):'||SUBSTR(SQLERRM,1,500);
ROLLBACK;
--
前錯(cuò)誤記錄進(jìn)
志表
INSERT
INTO
LOG_INFO(proc_name,error_info,op_date)
VALUES('xxxxxxxxxxx_p',vs_msg,SYSDATE);
COMMIT;
RETURN;
END;
1.游標(biāo)定義：
cursor XXXA is
SELECT 語(yǔ)句;
XXXB cursorName%rowtype;
XXXA: 游標(biāo)名
XXXB: 游標(biāo)行數(shù)據(jù)定義
2. 打開游標(biāo)：
-- 打開之前最好先關(guān)一下，防止上次發(fā)生異常沒有關(guān)掉而引發(fā)不必要的異常
IF XXXA%ISOPEN THEN
CLOSE XXXA;
END IF;
Open XXXA ;
Loop
Fetch XXXA into XXXB;
exit when XXXA%NOTFOUND;
... ... 處理邏輯
end loop;
close XXXA;
Oracle中的游標(biāo)分為顯示游標(biāo)和隱式游標(biāo)。
顯示游標(biāo)：
顯示游標(biāo)是用cursor...is命令定義的游標(biāo)，它可以對(duì)查詢語(yǔ)句(select)返回的多條記錄進(jìn)行處理；顯示游標(biāo)的操作：打開游標(biāo)、操作游標(biāo)、關(guān)閉游標(biāo)；
隱式游標(biāo)：
隱式游標(biāo)是在執(zhí)行插入(insert)、刪除(delete)、修改(update)和返回單條記錄的查詢(select)語(yǔ)句時(shí)由PL/SQL自動(dòng)定義的。PL/SQL隱式地打開SQL游標(biāo)，并在它內(nèi)部處理SQL語(yǔ)句，然后關(guān)閉它。
游標(biāo)能夠根據(jù)查詢條件從數(shù)據(jù)表中提取一組記錄，將其作為一個(gè)臨時(shí)表置于數(shù)據(jù)緩沖區(qū)中，利用指針逐行對(duì)記錄數(shù)據(jù)進(jìn)行操作。
Oracle中的游標(biāo)分為顯示游標(biāo)和隱式游標(biāo) 。
在執(zhí)行SQL語(yǔ)句時(shí)，Oracle會(huì)自動(dòng)創(chuàng)建隱式游標(biāo)，該游標(biāo)是內(nèi)存中處理該語(yǔ)句的數(shù)據(jù)緩沖區(qū)，存儲(chǔ)了執(zhí)行SQL語(yǔ)句的結(jié)果。通過隱式游標(biāo)屬性可獲知SQL語(yǔ)句的執(zhí)行狀態(tài)信息。
%found：布爾型屬性，如果sql語(yǔ)句至少影響到一行數(shù)據(jù)，值為true，否則為false。
%notfound：布爾型屬性，與%found相反。
%rowcount：數(shù)字型屬性，返回受sql影響的行數(shù)。
%isopen：布爾型屬性，當(dāng)游標(biāo)已經(jīng)打開時(shí)返回true，游標(biāo)關(guān)閉時(shí)則為false。
用戶可以顯式定義游標(biāo)。使用顯式游標(biāo)處理數(shù)據(jù)要4個(gè)步驟：定義游標(biāo)、打開游標(biāo)、提取游標(biāo)數(shù)據(jù)和關(guān)閉游標(biāo)。
游標(biāo)由游標(biāo)名稱和游標(biāo)對(duì)應(yīng)的select結(jié)果集組成。定義游標(biāo)應(yīng)該放在pl/sql程序塊的聲明部分。
語(yǔ)法格式：cursor 游標(biāo)名稱(參數(shù)) is 查詢語(yǔ)句
打開游標(biāo)時(shí)，游標(biāo)會(huì)將符合條件的記錄送入數(shù)據(jù)緩沖區(qū)，并將指針指向第一條記錄。
語(yǔ)法格式：open 游標(biāo)名稱(參數(shù));
將游標(biāo)中的當(dāng)前行數(shù)據(jù)賦給指定的變量或記錄變量。
語(yǔ)法格式：fetch 游標(biāo)名稱 into 變量名;
游標(biāo)一旦使用完畢，就應(yīng)將其關(guān)閉，釋放與游標(biāo)相關(guān)聯(lián)的資源。
語(yǔ)法格式：close 游標(biāo)名稱;
declare
cursor c1 is? select sno,cno,grade from sc;
v_sno sc.sno%type;
v_cno sc.cno%type;
v_grade sc.grade%type;
begin
open c1;
loop
? fetch c1 into v_sno,v_cno,v_grade;
? exit when c1%notfound;--緊跟fetch之后
if c1%found then
dbms_output.put_line(to_char(c1%rowcount)||v_cno);
end if;
end loop;
close c1;?
end;
declare
cursor c1 is select sno,cno,grade from sc;
v_sno sc.sno%type;
v_cno sc.cno%type;
v_grade sc.grade%type;
begin
open c1;
fetch c1 into v_sno,v_cno,v_grade;
while c1%found loop
? dbms_output.put_line(v_sno||v_cno||v_grade);
?fetch c1 into v_sno,v_cno,v_grade;
end loop;
close c1;?
end;
第三種：for
declare
cursor c1 is select sno,cno,grade from sc;
begin
for item in c1 loop
dbms_output.put_line(rpad(item.sno,'10',' ')||rpad(item.cno,'10',' ')||rpad(item.grade,'10',' '));
end loop;
end;
你嘗試一下， 使用 函數(shù) 來處理， 應(yīng)該就可以避免掉 存儲(chǔ)過程參數(shù)沒法寫的問題。
創(chuàng)建返回結(jié)果集的函數(shù)
SQL create or replace package pkg_HelloWorld as
2 -- 定義ref cursor類型
3 type myrctype is ref cursor;
4 --函數(shù)申明
5 function getHelloWorld return myrctype;
6 end pkg_HelloWorld;
7 /
程序包已創(chuàng)建。
SQL CREATE OR REPLACE package body pkg_HelloWorld as
2 function getHelloWorld return myrctype
3 IS
4 return_cursor myrctype;
5 BEGIN
6 OPEN return_cursor FOR
7 SELECT 'Hello 1' AS a, 'World 1' AS B FROM dual
8 UNION ALL
9 SELECT 'Hello 2' AS a, 'World 2' AS B FROM dual;
10 return return_cursor;
11 END getHelloWorld;
12 end pkg_HelloWorld;
13 /
程序包體已創(chuàng)建。
注：Oracle 這里的函數(shù)，是一個(gè)返回游標(biāo)類型的函數(shù)， 不是像 SQL Server 的那種叫 “表值函數(shù)” 的東西。
因此下面的寫法會(huì)報(bào)錯(cuò)。
SQL SELECT * FROM pkg_HelloWorld.getHelloWorld();
SELECT * FROM pkg_HelloWorld.getHelloWorld()
*
第 1 行出現(xiàn)錯(cuò)誤:
ORA-00933: SQL 命令未正確結(jié)束
SQL SELECT pkg_HelloWorld.getHelloWorld() FROM dual;
PKG_HELLOWORLD.GETHE
--------------------
CURSOR STATEMENT : 1
CURSOR STATEMENT : 1
A B
------- -------
Hello 1 World 1
Hello 2 World 2
C# 如何調(diào)用上面的 返回結(jié)果集的例子：
/// summary
/// 測(cè)試 調(diào)用 Oracle 返回結(jié)果集的函數(shù).
/// /summary
private void CallFuncWithTable(OracleConnection conn)
{
// 創(chuàng)建一個(gè) Command.
OracleCommand testCommand = conn.CreateCommand();
// 定義需要執(zhí)行的SQL語(yǔ)句. testCommand.CommandText = "pkg_HelloWorld.getHelloWorld";
// 定義好，本次執(zhí)行的類型，是存儲(chǔ)過程. testCommand.CommandType = CommandType.StoredProcedure;
// 定義好，我這個(gè)參數(shù)，是 游標(biāo) + 返回值.
OracleParameter para = new OracleParameter("c", OracleType.Cursor);
para.Direction = ParameterDirection.ReturnValue;
testCommand.Parameters.Add(para);
// 執(zhí)行SQL命令，結(jié)果存儲(chǔ)到Reader中.
OracleDataReader testReader = testCommand.ExecuteReader();
// 處理檢索出來的每一條數(shù)據(jù).
while (testReader.Read())
{
// 將檢索出來的數(shù)據(jù)，輸出到屏幕上.
Console.WriteLine("調(diào)用函數(shù):{0}; 返回:{1} - {2}",
testCommand.CommandText, testReader[0], testReader[1]
);
}
// 關(guān)閉Reader.
testReader.Close();
}
重慶分公司
重慶分公司
