首先你得有這個(gè)結(jié)果表
成都創(chuàng)新互聯(lián)于2013年成立，先為槐蔭等服務(wù)建站，槐蔭等地企業(yè)，進(jìn)行企業(yè)商務(wù)咨詢服務(wù)。為槐蔭企業(yè)網(wǎng)站制作PC+手機(jī)+微官網(wǎng)三網(wǎng)同步一站式服務(wù)解決您的所有建站問(wèn)題。
然后，你游標(biāo)循環(huán)的時(shí)候
直接用insert語(yǔ)句就可以
如果數(shù)據(jù)量特別大，百萬(wàn)級(jí)以上的話，你應(yīng)該逐行提交
如果不是很大的話，那么循環(huán)完提交就可以
--創(chuàng)建存儲(chǔ)過(guò)程
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)單的說(shuō)就是一個(gè)可以遍歷的結(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對(duì)象的put_line方法打印出影響的記錄行數(shù)，其中用到一個(gè)系統(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當(dāng)月記錄='||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)置變量保存了當(dāng)前錯(cuò)誤的詳細(xì)信息。
EXCEPTION
WHEN OTHERS THEN
vs_msg := 'ERROR IN xxxxxxxxxxx_p('||is_ym||'):'||SUBSTR(SQLERRM,1,500);
ROLLBACK;
--把當(dāng)前錯(cuò)誤記錄進(jìn)日志表。
INSERT INTO LOG_INFO(proc_name,error_info,op_date)
VALUES('xxxxxxxxxxx_p',vs_msg,SYSDATE);
COMMIT;
RETURN;
END;
Oracle中的游標(biāo)分為顯示游標(biāo)和隱式游標(biāo)。
顯示游標(biāo)：
顯示游標(biāo)是用cursor...is命令定義的游標(biāo)，它可以對(duì)查詢語(yǔ)句(select)返回的多條記錄進(jìn)行處理；顯示游標(biāo)的操作：打開(kāi)游標(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隱式地打開(kāi)SQL游標(biāo)，并在它內(nèi)部處理SQL語(yǔ)句，然后關(guān)閉它。
--打開(kāi)游標(biāo)并提取后面SQL的第一行數(shù)據(jù)放到游標(biāo)中 這里就是打開(kāi)游標(biāo)
open for 是隱式游標(biāo)的寫法 不建議使用 這種游標(biāo)好象不需要關(guān)閉 具體你自己測(cè)試下 而且少了expection 處理
fetch mycur into yang_02;
--循環(huán)插入數(shù)據(jù)
多余了 可以不要 前面有fetch了如果還有這里的話 就只能插入奇數(shù)行
其他沒(méi)什么問(wèn)題了 還有你這個(gè)過(guò)程用ref cursor是浪費(fèi) 沒(méi)必要用 用簡(jiǎn)單的顯示游標(biāo)更好點(diǎn)
你嘗試一下， 使用 函數(shù) 來(lái)處理， 應(yīng)該就可以避免掉 存儲(chǔ)過(guò)程參數(shù)沒(méi)法寫的問(wèn)題。
創(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ǔ)過(guò)程. 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();
// 處理檢索出來(lái)的每一條數(shù)據(jù).
while (testReader.Read())
{
// 將檢索出來(lái)的數(shù)據(jù)，輸出到屏幕上.
Console.WriteLine("調(diào)用函數(shù):{0}; 返回:{1} - {2}",
testCommand.CommandText, testReader[0], testReader[1]
);
}
// 關(guān)閉Reader.
testReader.Close();
}
一
游標(biāo)是什么
游標(biāo)字面理解就是游動(dòng)的光標(biāo)。
用數(shù)據(jù)庫(kù)語(yǔ)言來(lái)描述：游標(biāo)是映射在結(jié)果集中一行數(shù)據(jù)上的位置實(shí)體，有了游標(biāo)，用戶就可以訪問(wèn)結(jié)果集中的任意一行數(shù)據(jù)了，將游標(biāo)放置到某行后，即可對(duì)該行數(shù)據(jù)進(jìn)行操作，例如提取當(dāng)前行的數(shù)據(jù)等。
二
游標(biāo)的分類
顯式游標(biāo)和隱式游標(biāo)
顯式游標(biāo)的使用需要4步：
1.
聲明游標(biāo)
CURSOR
mycur(vartype
number)
is
select
emp_no,emp_zc
from
cus_emp_basic
where
com_no
=
vartype;
2.
打開(kāi)游標(biāo)
open
mycur(000627)
注：000627是參數(shù)
3.
讀取數(shù)據(jù)
fetch
mycur
into
varno,
varprice;
4.
關(guān)閉游標(biāo)
close
mycur;
三
游標(biāo)的屬性
oracle
游標(biāo)有4個(gè)屬性：%ISOPEN，%FOUND，%NOTFOUND，%ROWCOUNT。
%ISOPEN判斷游標(biāo)是否被打開(kāi)，如果打開(kāi)%ISOPEN等于true,否則等于false；
%FOUND
%NOTFOUND判斷游標(biāo)所在的行是否有效，如果有效，則%FOUNDD等于true，否則等于false；
%ROWCOUNT返回當(dāng)前位置為止游標(biāo)讀取的記錄行數(shù)。
四
示例
set
serveroutput
on;
declare
varno
varchar2(20);
varprice
varchar2(20);
CURSOR
mycur(vartype
number)
is
select
emp_no,emp_zc
from
cus_emp_basic
where
com_no
=
vartype;
begin
if
mycur%isopen
=
false
then
open
mycur(000627);
end
if;
fetch
mycur
into
varno,varprice;
while
mycur%found
loop
dbms_output.put_line(varno||','||varprice);
if
mycur%rowcount=2
then
exit;
end
if;
fetch
mycur
into
varno,varprice;
end
loop;
close
mycur;
end;
PL/SQL記錄的結(jié)構(gòu)和C語(yǔ)言中的結(jié)構(gòu)體類似，是由一組數(shù)據(jù)項(xiàng)構(gòu)成的邏輯單元。
PL/SQL記錄并不保存在數(shù)據(jù)庫(kù)中，它與變量一樣，保存在內(nèi)存空間中，在使用記錄時(shí)候，要首先定義記錄結(jié)構(gòu)，然后聲明記錄變量?？梢园裀L/SQL記錄看作是一個(gè)用戶自定義的數(shù)據(jù)類型。
set
serveroutput
on;
declare
type
person
is
record
(
empno
cus_emp_basic.emp_no%type,
empzc
cus_emp_basic.emp_zc%type);
person1
person;
cursor
mycur(vartype
number)is
select
emp_no,emp_zc
from
cus_emp_basic
where
com_no=vartype;
begin
if
mycur%isopen
=
false
then
open
mycur(000627);
end
if;
loop
fetch
mycur
into
person1;
exit
when
mycur%notfound;
dbms_output.put_line('雇員編號(hào):'||person1.empno||',地址:'||person1.empzc);
end
loop;
close
mycur;
end;
典型游標(biāo)for
循環(huán)
游標(biāo)for循環(huán)示顯示游標(biāo)的一種快捷使用方式，它使用for循環(huán)依次讀取結(jié)果集中的行數(shù)據(jù)，當(dāng)form循環(huán)開(kāi)始時(shí)，游標(biāo)自動(dòng)打開(kāi)（不需要open），每循環(huán)一次系統(tǒng)自動(dòng)讀取游標(biāo)當(dāng)前行的數(shù)據(jù)（不需要fetch)，當(dāng)退出for循環(huán)時(shí)，游標(biāo)被自動(dòng)關(guān)閉（不需要使用close）。使用游標(biāo)for循環(huán)的時(shí)候不能使用open語(yǔ)句，fetch語(yǔ)句和close語(yǔ)句，否則會(huì)產(chǎn)生錯(cuò)誤。
set
serveroutput
on;
declare
cursor
mycur(vartype
number)is
select
emp_no,emp_zc
from
cus_emp_basic
where
com_no=vartype;
begin
for
person
in
mycur(000627)
loop
dbms_output.put_line('雇員編號(hào):'||person.emp_no||',地址:'||person.emp_zc);
end
loop;
end;
重慶分公司
重慶分公司
