游標和存儲過程

1. 請寫出遊標和存儲過程的概念及其在資料庫編程中發揮什麼樣的作用

轉載
以下記錄的上由於工顫清洞作需要寫的Oracle的<br>使用游標的儲存過程，個人覺得比較有代表性。希望給初學者一定的幫助，也給自己加深一下印象。

在ORACLE中，他以一個語句塊為茄枯一個默認的事務。也就是說，如果你就單單只執行一段ORACLE的語句塊，他默認是以事務的形式執行的。

01 CREATE OR REPLACE PROCEDURE sp_EditInlayOut(

02 FID NUMBER, --修改記錄的ID T_INLAYOUT表的主鍵

03 InlayBoxIDs varchar2, --修改的記錄

04 BoxCount number, --裝箱數量

05 ApplyUserID varchar2, --申請人編號

06 StoreUserID varchar2, --庫管編號

07 ConfirmState char, --確認狀態

08 ExistState char, --存在狀態

09 strErr OUT varchar2 --存儲過程執行結果。成功返回空，失敗返回錯誤原因

10 )

11 AS

12 --定義變數

13 v_Now DATE;

14 v_Now2 date;

15 v_LogID number;

16 v_ChipID number;

17 v_sql varchar2(2000);

18 BEGIN

19

20 --記錄日誌

21 INSERT INTO T_InlayOut_Log(F_InlayBoxIDs,f_Boxcount,f_Applyuserid,f_Storeuserid,f_Addtime,f_Confirmstate

22 ,f_Existstate, f_modifyid, f_modifytime, f_modifyuserid )

23 ((SELECT F_InlayBoxIDs,f_Boxcount,f_Applyuserid,f_Storeuserid,f_Addtime,f_Confirmstate,f_Existstate

24 ,FID,SYSDATE,StoreUserID FROM T_InlayOut WHERE F_ID=FID));

25 --取剛插入記錄的ID

26 select seq_t_inlayout_log.currval into v_LogID from al;

27 --定義游標

正雀28 DECLARE CURSOR myCusor IS SELECT F_ID FROM T_CHIP WHERE F_InlayBoxID IN (SELECT f_ID FROM

29 T_InlayBox where F_InlayOutID = FID);

30 --開始使用游標取數據

31 BEGIN

32 OPEN myCusor;

33

34 LOOP

35 FETCH myCusor INTO v_ChipID;

36 --游標取不到數據則退出

37 EXIT WHEN myCusor%NOTFOUND;

38

39 SELECT MIN(F_CurrentTime) INTO v_Now FROM t_Chipstatehistory WHERE

40 (F_HistoryState = 'Confirm_InlayIn') AND F_ChipID = v_ChipID;

41 --改變晶元表的狀態

42 UPDATEt_chip SET f_State = 'Confirm_InlayIn',F_CompareTime = v_Now WHERE F_ID = v_ChipID;

43 --保存晶元狀態歷史記錄

44 INSERT INTO T_CHIPSTATEHISTORY(f_chipid, f_Historystate,F_TABLEID,f_Currenttime,F_TABLENAME)

45 VALUES

46 (v_ChipID,'Confirm_InlayIn',v_LogID,SYSDATE,'T_InlayOut_Log');

47

48 END LOOP;

49 CLOSE myCusor;

50 END;

51

52 --選擇最近晶元狀態變更時間

53 --SELECT MIN(F_CURRENTTIME) INTO v_NOW FROM T_CHIPSTATEHISTORY WHERE F_HISTORYSTATE = 20

54 AND F_CHIPID IN (SELECT F_ID FROM T_CHIP WHERE F_InlayBoxID=(SELECT F_ID FROM T_InlayBox

55 WHERE F_InlayOutID=FID));

56

57 --將晶元表中晶元狀態更新到以前狀態

58 --UPDATE T_CHIP SET F_State=20,F_CompareTime=v_NOW WHERE F_InlayBoxID IN (SELECT F_ID FROM

59 T_InlayBox WHERE F_InlayOutID =FID);

60 --記錄晶元狀態變更日誌

61 --INSERT INTO T_ChipStateHistory (F_ChipID,f_Historystate,f_Tableid,f_Currenttime,f_Tablename)VALUES

62 --((SELECT F_ID FROM T_CHIP WHERE F_InlayBoxID=(SELECT F_ID FROM T_InlayBox WHERE F_InlayOutID=FID)),

63 20,v_LogID,SYSDATE,'T_InlayOut_Log');

64

65

66 --將Inlay出庫箱表中以前的數據更新到以前狀態

67 UPDATE T_InlayBox SET F_State=2,F_InlayOutID=null WHERE F_InlayOutID =FID;

68

69 --編輯時將新的INLAY出庫信息更新

70 UPDATE T_InlayOut SET F_InlayBoxIDs=InlayBoxIDs,f_Boxcount=BoxCount,f_Applyuserid=ApplyUserID,

71 f_Storeuserid=StoreUserID,f_Confirmstate=ConfirmState,F_ExistState=ExistState,F_ConfirmTime=null

72 WHERE F_ID=FID;

73

74 --更新T_InlayBox 新的狀態

75 --UPDATE T_InlayBox SET F_State=3,F_InlayOutID=FID WHERE F_ID in (InlayBoxIDs);

76 v_sql := 'UPDATE T_InlayBox SET F_State=3,F_InlayOutID='||FID||' WHERE F_ID in ('||InlayBoxIDs||')';

77 --立即執行v_sql

78 EXECUTE IMMEDIATE v_sql;

79

80 SELECT SYSDATE INTO v_Now2 FROM DUAL;

81 --更新晶元表狀態

82 UPDATE T_Chip SET F_State='No_Confirm_InlayOut',F_CompareTime=v_Now2 WHERE F_InlayBoxID IN

83 (SELECT F_ID FROM T_InlayBox WHERE F_InlayOutID=FID);

84 --記錄當前操作日誌

85 INSERT INTO T_ChipStateHistory (F_ChipID,f_Historystate,f_Tableid,f_Currenttime,f_Tablename)

86 SELECT F_ID,'No_Confirm_InlayOut',v_LogID,v_Now2,'T_InlayOut_Log' FROM T_CHIP WHERE F_InlayBoxID IN

87 (SELECT F_ID FROM T_InlayBox WHERE F_InlayOutID=FID);

88 --提交

89 COMMIT;

90 --發生異常時返回錯誤碼

91 EXCEPTION

92 WHEN OTHERS THEN

93 strErr := substr(sqlerrm,1,100);

94 ROLLBACK;

95 END sp_EditInlayOut;

但是在SQLSERVER中，除非你將所有的T-SQL語句塊以顯示的方式【BEGIN TRANSACTION ....END TRANSACTION】申明在事務中，否則SQLSERVER會將語句塊中的每一句作為一個單獨的默認事務執行。

此外，游標是一種比較佔I/O資源的操作，使用完後應該及時關閉，以釋放系統資源。

2. oracle資料庫的游標和存儲過程怎麼寫

--創建存儲
程
CREATE
OR
REPLACE
PROCEDURE
xxxxxxxxxxx_p
(
--參數IN表示輸入參數
OUT表示輸入參數
類型
使用任意Oracle
合
類型
is_ym
IN
CHAR
)
AS
--定義變數
vs_msg
VARCHAR2(4000);
--錯誤信息變數
vs_ym_beg
CHAR(6);
--起始月份
vs_ym_end
CHAR(6);
--終止月份
vs_ym_sn_beg
CHAR(6);
--同期起始月份
vs_ym_sn_end
CHAR(6);
--同期終止月份
--定義游標(簡單
說
遍歷
結
集)
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
--用輸入參數給變數賦初值
用
Oralce
SUBSTR
TO_CHAR
ADD_MONTHS
TO_DATE
等
用
函數
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');
--先刪除表
特定條件
數據
DELETE
FROM
xxxxxxxxxxx_T
WHERE
ym
=
is_ym;
--
用內置
DBMS_OUTPUT
象
put_line
列印
影響
記錄行數
其
用
系統變數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||'條');
--遍歷游標處理
更新
表
遍歷游標
幾種
用for語句
其
比較直觀
種
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;
--錯誤處理部
OTHERS表示除
聲明外
任意錯誤
SQLERRM
系統內置變數保存
前錯誤
詳細信息
EXCEPTION
WHEN
OTHERS
THEN
vs_msg
:=
'ERROR
IN
xxxxxxxxxxx_p('||is_ym||'):'||SUBSTR(SQLERRM,1,500);
ROLLBACK;
--
前錯誤記錄進
志表
INSERT
INTO
LOG_INFO(proc_name,error_info,op_date)
VALUES('xxxxxxxxxxx_p',vs_msg,SYSDATE);
COMMIT;
RETURN;
END;

3. 存儲過程為什麼要用游標，什麼情況下使用游標

游標一般用於把通過腳本得到的結果集的內容在用於其它的SQL語句中。但是游標執行會影響腳本執行速度，所以使用時請慎重。 在存儲過程或觸發器中使用 SQL 游標的典型過程為： 聲明SQL 變數包含游標返回的數據。為每個結果集列聲明一個變數。聲明足夠大的變數來保存列返回的值，並聲明變數的類型為可從列數據類型隱式轉換得到的數據類型。

使用 DECLARE CURSOR 語句將 SQL 游標與 SELECT 語句相關聯。另外，DECLARE CURSOR 語句還定義游標的特性，例如游標名稱以及游標是只讀還是只進。

使用 OPEN 語句執行 SELECT 語句並填充游標。

使用 FETCH INTO 語句提取單個行，並將每列中的數據移至指定的變數中。然後，其他 SQL 語句可以引用那些變數來訪問提取的數據值。SQL 游標不支持提取行塊。

使用 CLOSE 語句結束游標的使用。關閉游標可以釋放某些資源，例如游標結果集及其對當前行的鎖定，但如果重新發出一個 OPEN 語句，則該游標結構仍可用於處理。由於游標仍然存在，此時還不能重新使用該游標的名稱。DEALLOCATE 語句則完全釋放分配給游標的資源，包括游標名稱。釋放游標後，必須使用 DECLARE 語句來重新生成游標。

請採納。

4. 資料庫系統裡面，什麼是存儲過程、游標、觸發器

存儲過程（Stored Procere）是在大型資料庫系灶散統中，一組為了完成特定功能的SQL 語句集，經編譯後存儲在資料庫中，用戶通過指定存儲過程的名字並給出參數（如果該存儲過程帶有參數）來執行它。存儲過程是資料庫中的一個重要對象，任何一個設計良好的資料庫應用程序都應該用到存儲過程。

游標（cursor）是系統為用戶開設的一個數據緩沖區，存放SQL語句的執行結果。手漏每個游標區都有一個名字,用戶可以用SQL語句逐一從游標中獲取記錄，並畢辯爛賦給主變數，交由主語言進一步處理。
觸發器（trigger）是SQL server 提供給程序員和數據分析員來保證數據完整性的一種方法，它是與表事件相關的特殊的存儲過程，它的執行不是由程序調用，也不是手工啟動，而是由事件來觸發，比如當對一個表進行操作（ insert，delete， update）時就會激活它執行。

5. oracle SQL語句中的游標，函數和存儲過程的區別

游標有顯示游標和隱式游標之分，顯示游標需要自己去定義，declare cursor（這個自己看書去吧），這樣使用游標一條條地取值使用。而隱式游標針對所有的select語句都是的。
函數和存儲過程幾乎沒有區別，只是前者有返回值，後者沒有，如果後者定義了出參，那就和函數的功能是一樣的了。

6. 游標屬於存儲過程嗎

存儲過程就是可以傳入參數進行一定的內部運算，然後再傳出結果，比視圖要復雜點，含枯鄭但功能會很多的。。游標就是在存儲過程中，會對中間表的每一行數談頌據進行讀取，然後其他操作，現在一般不講究游標了，基本上游標都可以用別的代替敗毀，可以多用連接

7. 游標屬於存儲過程嗎

游標是變數，
存儲過程
是
數據滾磨庫對象野高
。
create
procere
p1
@aa
varchar(10)
as
begin
declare
@bb
varchar(10)
select
@bb
=
bb
from
t1
where
aa
=
@aa
end
定義一個名字為p1的存儲頌備尺過程，傳入的參數是字元類型的@aa；
定義字元類型的變數@bb；
將t1表中的符合條件的值賦給該變數。

8. oracle資料庫的游標和存儲過程怎麼寫

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(043925);
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記錄看作是一個用戶自定義的數據類型。

9. 資料庫基礎詳解：存儲過程、視圖、游標、SQL語句優化以及索引

寫在文章前：本系列文章用於博主自己歸納復習一些基礎知識，同時也分享給可能需要的人，因為水平有限，肯定存在諸多不足以及技術性錯誤，請大佬們及時指正。

存儲過程 是事先經過編譯並存儲在資料庫中的一段SQL語句的集合。想要實現相應的功能時，只需要調用這個存儲過程就行了（類似於函數，輸入具有輸出參數）。

優點 ：

缺點 ：

Delete用來刪除表的全部或者部分數據，執行delete之後，用戶需要提交之後才會執行，會觸發表上的DELETE觸發器（包含一個OLD的虛擬表，可以只讀訪問被刪除的數據），DELETE之後表結構還在，刪除很慢，一行一行地刪，因為會記錄日誌，可以利用日誌還原數據；

Truncate刪除表中的所有數據，這個操作不能回滾，也不會觸發這個表上的觸發器。操作比DELETE快很多（直接把表drop掉，再創建一個新表，刪除的數據不能找回）。如果表中有自增（AUTO_INCREMENT）列，則重置為1。

Drop命令從資料庫中刪除表，所有的數據行，索引和約束都會被刪除。不能回滾，不會觸發觸發器。

觸發器（TRIGGER）是由事件（比如INSERT/UPDATE/DELETE）來觸發運行的操作（不能被直接調用，不能接收參數）。在資料庫里以獨立的對象存儲，用於保證數據完整性（比如可以檢驗或轉換數據）。

約束（Constraint）類型：

從資料庫的基本表中通過查詢選取出來的數據組成的虛擬表（資料庫中只存放視圖的定義，而不存放視圖的數據）。可以對其進行增/刪/改/查等操作。視圖是對若干張基本表的引用，一張虛表，查詢語句執行的結果，不存儲具體的數據（基本表數據發生了改變，視圖也會跟著改變）。

可以跟基本表一樣，進行增刪改查操作( 增刪改操作有條件限制，一般視圖只允許查詢操作 )，對視圖的增刪改也會影響原表的數據。 它就像一個窗口，透過它可以看到資料庫中自己感興趣的數據並且操作它們。 好處：

用於定位在查詢返回的結果集的特定行，以對特定行進行操作。使用游標可以方便地對結果集進行移動遍歷，根據需要滾動或對瀏覽/修改任意行中的數據。主要用於互動式應用。它是一段私有的SQL工作區，也就是一段內存區域，用於暫時存放受SQL語句影響的數據，簡單來說，就是將受影響的數據暫時放到了一個內存區域的虛表當中，這個虛表就是游標。

游標是一種能從包括多條數據記錄的結果集中每次提取一條記錄的機制。即游標用來逐行讀取結果集。游標充當指針的作用。盡管游標能遍歷結果中的所有行，但他一次只指向一行。

游標的一個常見用途就是保存查詢結果，以便以後使用。游標的結果集是由SELECT語句產生，如果處理過程需要重復使用一個記錄集，那麼創建一次游標而重復使用若干次，比重復查詢資料庫要快的多。通俗來說，游標就是能在sql的查詢結果中，顯示某一行（或某多行）數據，其查詢的結果不是數據表，而是已經查詢出來的結果集。

簡單來說：游標就是在查詢出的結果集中進行選擇性操作的工具。

讓緩存更高效。對於連接查詢，如果其中一個表發生變化，那麼整個查詢緩存就無法使用。而分解後的多個查詢，即使其中一個表發生變化，對其它表的查詢緩存依然可以使用。分解成多個單表查詢，這些單表查詢的緩存結果更可能被其它查詢使用到，從而減少冗餘的查詢。減少鎖競爭。

索引是對資料庫表中一列或多列的值進行排序的一種結構（說明是在列上建立的），使用索引可快速訪問資料庫表中的特定信息。如果想按特定職員的姓來查找他或她，則與在表中搜索所有的行相比，索引有助於更快地獲取信息。索引的一個主要目的就是加快檢索表中數據，亦即能協助信息搜索者盡快的找到符合限制條件的記錄ID的輔助數據結構。

當表中有大量記錄時，若要對表進行查詢，第一種搜索信息方式是全表搜索，是將所有記錄一一取出，和查詢條件進行一一對比，然後返回滿足條件的記錄，這樣做會消耗大量資料庫系統時間，並造成大量磁碟I/O操作。第二種就是在表中建立索引，然後在索引中找到符合查詢條件的索引值，最後通過保存在索引中的ROWID（相當於頁碼）快速找到表中對應的記錄。

例如這樣一個查詢：select * from table1 where id=10000。如果沒有索引，必須遍歷整個表，直到ID等於10000的這一行被找到為止。有了索引之後(必須是在ID這一列上建立的索引)，即可在索引中查找。由於索引是經過某種演算法優化過的，因而查找次數要少的多。可見，索引是用來定位的。

從應用上分， 主鍵索引（聚集） ， 唯一索引（聚集/非聚集） ， 普通索引 ， 組合索引 ， 單列索引和全文索引

10. 存儲過程跟游標之間有什麼關聯么

兩者沒有什麼必然的聯系
游標一般不單獨使用，可以在存儲過程\函數等手嫌中使用，使畢搏手用完後要關閉釋放。
存儲過程可以是一個對象，存儲起來，下銀鍵次再調用。