《精通Oracle核心技術(shù)和項(xiàng)目實(shí)戰(zhàn)之游標(biāo).pptx》由會(huì)員分享，可在線閱讀，更多相關(guān)《精通Oracle核心技術(shù)和項(xiàng)目實(shí)戰(zhàn)之游標(biāo).pptx（44頁珍藏版）》請?jiān)谘b配圖網(wǎng)上搜索。
1、第13章 游標(biāo),游標(biāo)是在關(guān)系數(shù)據(jù)庫中用來操作查詢出來的數(shù)據(jù)集。引入游標(biāo)也是為了更加方便地訪問數(shù)據(jù)。游標(biāo)在操作數(shù)據(jù)庫時(shí)經(jīng)常用到，它使用相對靈活，容易理解和操作。本章將主要學(xué)習(xí)游標(biāo)的概念、種類、以及如何創(chuàng)建和使用游標(biāo)。,13.1 什么是游標(biāo),游標(biāo)從概念上講基于數(shù)據(jù)庫的表返回結(jié)果集。它指示結(jié)果集中的當(dāng)前位置 ，就像計(jì)算機(jī)屏幕上的光標(biāo)指示當(dāng)前位置一樣，“游標(biāo)”由此得名。,13.1.1 游標(biāo)概念,SQL是面向集合的，其結(jié)果一般是多條記錄。而PL/SQL的變量一般是標(biāo)量，其一組變量一次只能存放一條記錄。所以僅僅使用變量并不能完全滿足SQL語句向應(yīng)用程序輸出數(shù)據(jù)的要求。為此，在PL/SQL中引入了游標(biāo)的概念
2、。,13.1.1 游標(biāo)概念,1.基本原理 在PL/SQL塊中執(zhí)行select、insert、update、delete語句時(shí)，Oracle會(huì)在內(nèi)存中為其分配一個(gè)緩沖區(qū)。游標(biāo)是指向該區(qū)的一個(gè)指針，或是一種結(jié)構(gòu)化數(shù)據(jù)類型。它為應(yīng)用程序提供一種對結(jié)果集中的每一行數(shù)據(jù)分別進(jìn)行單獨(dú)處理的方法。,13.1.1 游標(biāo)概念,可以將游標(biāo)形象地看成一個(gè)變動(dòng)的光標(biāo)。它實(shí)際上是一個(gè)指針，在一段Oracle存放數(shù)據(jù)查詢結(jié)果集或數(shù)據(jù)操作結(jié)果集的內(nèi)存中，這個(gè)指針可以指向結(jié)果集中的任何一條記錄。通過游標(biāo)就可以得到它所指向的數(shù)據(jù)，但初始時(shí)它指向首記錄。這種模型很像編程語言中的數(shù)組。如圖所示。,13.1.2 游標(biāo)種類,Oracl
3、e中游標(biāo)分為靜態(tài)游標(biāo)和動(dòng)態(tài)（REF）游標(biāo)兩類。其中，靜態(tài)游標(biāo)就像一個(gè)數(shù)據(jù)快照，打開游標(biāo)后的結(jié)果集是對數(shù)據(jù)庫的一個(gè)備份，數(shù)據(jù)不隨對表執(zhí)行DML操作后而改變。靜態(tài)游標(biāo)又分為顯式游標(biāo)和隱式游標(biāo)兩類。動(dòng)態(tài)游標(biāo)在下面章節(jié)中講解。游標(biāo)種類如圖所示。,13.2 顯式游標(biāo),顯式游標(biāo)在PL/SQL編程當(dāng)中有著重要的作用，通過顯式游標(biāo)用戶可以操作返回的數(shù)據(jù)，使得一些在編程語言中復(fù)雜的功能更容易實(shí)現(xiàn)。,13.2.1 創(chuàng)建顯式游標(biāo)步驟,顯示游標(biāo)的使用順序可以明確地分成聲明游標(biāo)、打開游標(biāo)、讀取數(shù)據(jù)和關(guān)閉游標(biāo)4個(gè)步驟。具體步驟如下。 1.聲明游標(biāo) 聲明游標(biāo)主要是用來給游標(biāo)命名并且使得游標(biāo)關(guān)聯(lián)一個(gè)查詢。,13.2.1 創(chuàng)建
4、顯式游標(biāo)步驟,聲明游標(biāo)的具體語法如圖所示。 【示例13-1】下面我們?yōu)閏ustomersnew表創(chuàng)建一個(gè)名為cus_cur的簡單游標(biāo).,13.2.1 創(chuàng)建顯式游標(biāo)步驟,（1）聲明列變量 游標(biāo)的好處之一是可以將訪問的對象細(xì)化到記錄的列。因此，在聲明了游標(biāo)變量之后，往往需要聲明相應(yīng)的列變量，以備數(shù)據(jù)訪問之需。 【示例13-2】在示例13-1中所聲明的游標(biāo)變量含有兩列：customer_id、cust_first_name。現(xiàn)欲分別為兩列的訪問預(yù)備兩個(gè)變量，那么可以利用declare命令。 【示例13-3】聲明變量時(shí)，除了使用特定的數(shù)據(jù)類型來聲明變量，還可以直接利用列的數(shù)據(jù)類型來聲明變量類型。,13
5、.2.1 創(chuàng)建顯式游標(biāo)步驟,（2）聲明行變量 Oracle不僅可以利用列類型來定義變量，而且可以直接聲明一個(gè)行類型的變量，來達(dá)到為每個(gè)列統(tǒng)一聲明變量聲明行類型的語法如圖所示。 【示例13-4】我們可以針對表customersnew的行聲明一個(gè)行變量。,13.2.1 創(chuàng)建顯式游標(biāo)步驟,2.打開游標(biāo) 游標(biāo)中對數(shù)據(jù)的任何操作都是建立在游標(biāo)被打開的前提下。游標(biāo)一旦打開，其結(jié)果集都是靜態(tài)的。也就是說結(jié)果集此時(shí)不會(huì)反映出數(shù)據(jù)庫中對數(shù)據(jù)進(jìn)行的增加、刪除、修改操作。打開游標(biāo)語法如圖所示。 【示例13-5】打開游標(biāo)cus_cur。,13.2.1 創(chuàng)建顯式游標(biāo)步驟,3.讀取數(shù)據(jù) 游標(biāo)打開后，就可以取出游標(biāo)中的數(shù)據(jù)
6、，并對其進(jìn)行處理了。從游標(biāo)中取出數(shù)據(jù)的命令是fetch。fetch命令把游標(biāo)指向當(dāng)前記錄賦給PL/SQL聲明的變量。它只能讀出指針當(dāng)前的記錄，一次取出一行數(shù)據(jù)。正常情況下，fetch要和循環(huán)語句一起使用，這樣指針會(huì)不斷前進(jìn)，直到某個(gè)條件不符合條件而退出循環(huán)。具體fetch命令的語法如圖所示。,13.2.1 創(chuàng)建顯式游標(biāo)步驟,4.關(guān)閉游標(biāo) 游標(biāo)在使用完后，應(yīng)該及時(shí)關(guān)閉，釋放它所占用的內(nèi)存空間。關(guān)閉游標(biāo)后，結(jié)果集中的數(shù)據(jù)將不能做任何操作，具體語法如圖所示。,13.2.1 創(chuàng)建顯式游標(biāo)步驟,學(xué)了創(chuàng)建顯式游標(biāo)的步驟，總結(jié)得到顯式游標(biāo)操作過程如圖所示。,13.2.1 創(chuàng)建顯式游標(biāo)步驟,創(chuàng)建返回單條記錄顯
7、式游標(biāo)的語法如圖所示。 【示例13-6】下面我們?yōu)閏ustomersnew表創(chuàng)建一個(gè)名為cus_cur的簡單游標(biāo)，并從游標(biāo)中提取出單行數(shù)據(jù)。 【示例13-7】對上面的PL/SQL塊進(jìn)行修改，在PL/SQL塊中使用記錄變量。,13.2.2 游標(biāo)中的loop語句,通常顯式游標(biāo)提取的數(shù)據(jù)不會(huì)只有一條，而是多條記錄，這時(shí)如果只用一條fetch語句僅能取到游標(biāo)中的一行數(shù)據(jù)。這樣就需要一個(gè)遍歷結(jié)果集的方式，而loop語句就能實(shí)現(xiàn)該功能。語法如圖所示，下面示例將演示在游標(biāo)中如何使用loop語句。,13.2.2 游標(biāo)中的loop語句,【示例13-8】下面我們?yōu)閏ustomersnew表創(chuàng)建一個(gè)名為cus_lo
8、op_cur的游標(biāo)，并從游標(biāo)中提取出city是Philadelphia的顧客數(shù)據(jù)。 【示例13-9】上個(gè)示例中，從游標(biāo)中提取出city是Philadelphia的顧客數(shù)據(jù)，除了使用while方式結(jié)束循環(huán)，現(xiàn)在還可以用另外一種形式的循環(huán)處理游標(biāo)。,13.2.3 使用bulk collect和for語句的游標(biāo),游標(biāo)中通常使用fetch into 語句提取數(shù)據(jù)，這種方式是單條數(shù)據(jù)提取，但是在數(shù)據(jù)量很大的情況下執(zhí)行效率不是很理想。而此時(shí)引用fetch bulk collect into語句可以批量提取數(shù)據(jù)，在數(shù)據(jù)量大的情況下它的執(zhí)行效率比單條提取數(shù)據(jù)的高。語法如圖所示。,13.2.3 使用bulk c
9、ollect和for語句的游標(biāo),【示例13-10】修改上一個(gè)示例的腳本，實(shí)現(xiàn)使用批量的方式從游標(biāo)中查詢數(shù)據(jù)。,13.2.4 使用for loop語句,游標(biāo)中的數(shù)據(jù)一般是通過循環(huán)方式來處理的。在上面的例子中用常規(guī)的循環(huán)方法進(jìn)行處理。PL/SQL還提供了一種更簡便的方法處理游標(biāo)，這種方法利用for循環(huán)，逐行處理游標(biāo)中的行。for loop不需要聲明變量，它可以直接提出行對象類型的數(shù)據(jù)。其語法結(jié)構(gòu)如圖所示。,13.2.4 使用for loop語句,【示例13-11】上個(gè)示例中，從游標(biāo)中提取出city是Philadelphia的顧客數(shù)據(jù)，采用的是while設(shè)置的loop循環(huán)，現(xiàn)在還可以用for循環(huán)處理
10、游標(biāo)。,13.2.5 為游標(biāo)傳遞參數(shù),如果在聲明游標(biāo)時(shí)，select語句都沒有where子句。但是在實(shí)際的情況中，可能要根據(jù)具體需求查詢不同的數(shù)據(jù)。為了通過游標(biāo)對數(shù)據(jù)進(jìn)行更加靈活的處理，可以在使用顯示游標(biāo)時(shí)，指定參數(shù)。這些參數(shù)可以用在where子句中。在打開游標(biāo)時(shí)，指定實(shí)際的參數(shù)值，這樣游標(biāo)在每次打開時(shí)，可以根據(jù)不同的實(shí)際參數(shù)值，返回所需的不同數(shù)據(jù)。一般情況下，指定參數(shù)包括參數(shù)的順序和參數(shù)的類型，如圖所示。,13.2.5 為游標(biāo)傳遞參數(shù),【示例13-12】在表customersnew中，含有存儲賒銷限額的列credit_limit?，F(xiàn)欲獲得顧客賒銷限額大于某一確定值的顧客信息，則可以為搜尋時(shí)的
11、where條件指定參數(shù)。 【示例13-13】對于上述示例，打開游標(biāo)時(shí)，采用參數(shù)傳遞的方式。,13.2.6 顯式游標(biāo)的屬性,通過游標(biāo)，可以處理數(shù)據(jù)。在使用游標(biāo)時(shí)，必須考慮各種特殊情況。如果select語句沒有返回結(jié)果，游標(biāo)是空的，這時(shí)fetch語句將取不到數(shù)據(jù)。利用游標(biāo)的屬性可以了解游標(biāo)當(dāng)前的狀態(tài)，防止各種意外情況的發(fā)生。顯式游標(biāo)有4個(gè)屬性： %isopen：判斷游標(biāo)是否打開, 打開則返回true, 否則返回false。 %found：判斷當(dāng)前游標(biāo)最近的一次fetch是否取到數(shù)據(jù)。是則返回true, 否則返回false。 %notfound：與%found恰好相反。 %rowcount：判斷自游
12、標(biāo)打開以來，到目前為止，用fetch命令獲取的行數(shù)，并非所有總記錄數(shù)。,13.2.6 顯式游標(biāo)的屬性,下面用幾個(gè)例子來了解一下以上4個(gè)屬性的具體使用方法。 1.%isopen屬性 【示例13-14】%isopen的使用。,13.2.6 顯式游標(biāo)的屬性,2.%found屬性 %found一般在多條記錄中使用，以下以%found屬性的用法為例大家演示一下。 【示例13-15】%found的使用。,13.2.6 顯式游標(biāo)的屬性,3.%rowcount屬性 %rowcount屬性能使得不必抽取所有記錄行就可以中斷游標(biāo)操作，以下用loop語句來演示一下它的作用。 【示例13-16】使用%rowcount
13、為emp表提取10條記錄，具體效果如圖13-30所示。 【示例13-17】把customersnew表中編號為983的顧客的city設(shè)置為“taiyuan”。,13.3 隱式游標(biāo),隱式游標(biāo)是數(shù)據(jù)庫服務(wù)器定義的一種游標(biāo)。如果在PL/SQL程序中使用select語句或者DML語句進(jìn)行操作，數(shù)據(jù)庫服務(wù)器將自動(dòng)打開一個(gè)隱式游標(biāo)，用來存放該語句的執(zhí)行結(jié)果。所以這種游標(biāo)無需定義，也不需打開和關(guān)閉。隱式游標(biāo)始終存放最近一條語句的執(zhí)行結(jié)果。本小節(jié)將介紹隱式游標(biāo)的特點(diǎn)及屬性。,13.3.1 隱式游標(biāo)的特點(diǎn),每當(dāng)執(zhí)行select或DML語句時(shí)，PL/SQL會(huì)自動(dòng)打開一個(gè)隱式游標(biāo)。隱式游標(biāo)不受用戶的控制，它有以下幾
14、個(gè)特點(diǎn)： 隱式游標(biāo)由PL/SQL自動(dòng)管理； 隱式游標(biāo)的默認(rèn)名稱是SQL； select或DML操作產(chǎn)生隱式游標(biāo) 隱式游標(biāo)的屬性值始終是最新執(zhí)行的SQL語句的。,13.3.2 隱式游標(biāo)的屬性,隱式游標(biāo)種類與顯式游標(biāo)沒有變，與顯式游標(biāo)相比還是有些區(qū)別。SQL隱式游標(biāo)和cursor for游標(biāo)是Oracle內(nèi)置的游標(biāo)。SQL游標(biāo)與當(dāng)前會(huì)話有關(guān)。當(dāng)前會(huì)話中的更新（update）、刪除操作都會(huì)影響SQL隱式游標(biāo)的屬性。cursor for游標(biāo)用于循環(huán)。,13.3.2 隱式游標(biāo)的屬性,下面來介紹sql隱式游標(biāo)的屬性，如表所示。,13.3.2 隱式游標(biāo)的屬性,【示例13-18】可以在每一條DML語句之后輸出
15、該操作影響的行數(shù)。,13.3.3 使用SQL隱式游標(biāo),使用隱式游標(biāo)不像顯式游標(biāo)聲明一個(gè)游標(biāo)名稱，它無需定義、打開、關(guān)閉。對于每個(gè)隱式游標(biāo)必須要有一個(gè)into子句，因此隱式游標(biāo)的select語句必須只選中一行數(shù)據(jù)或只產(chǎn)生一行數(shù)據(jù)，創(chuàng)建語法如圖所示。 【示例13-19】使用隱式游標(biāo)對customersnew表選中一行編號為“230”數(shù)據(jù)。,13.3.4 使用cursor for隱式游標(biāo),SQL隱式游標(biāo)只能通過屬性信息為PL/SQL編程提供幫助。用戶無法操作SQL隱式游標(biāo)。cursor for游標(biāo)用于循環(huán)。利用該游標(biāo)，用戶可以像使用普通循環(huán)語句一樣來循環(huán)處理select語句所獲得的每一條記錄。 cu
16、rsor for游標(biāo)使用的一般語法如圖所示。,13.3.4 使用cursor for隱式游標(biāo),【示例13-20】cursor for游標(biāo)中的游標(biāo)變量，無需顯式聲明、打開等操作。為了獲得表customersnew中city為Philadelphia的信息，可以利用cursor for游標(biāo)。,13.4 動(dòng)態(tài)（REF）游標(biāo),前面所講的游標(biāo)都是與一個(gè)SQL語句相關(guān)聯(lián)，并且在編譯該塊的時(shí)候此語句己經(jīng)是可知的，是靜態(tài)的，而動(dòng)態(tài)游標(biāo)可以在運(yùn)行時(shí)與不同的語句關(guān)聯(lián)，是動(dòng)態(tài)的。動(dòng)態(tài)游標(biāo)被用于處理多行的査詢結(jié)果集。在同一個(gè)PL/SQL塊中，動(dòng)態(tài)游標(biāo)不同于特定的查詢綁定，而是在打開游標(biāo)時(shí)才確定所對應(yīng)的査詢。因此，動(dòng)態(tài)
17、游標(biāo)可以依次對應(yīng)多個(gè)查詢。動(dòng)態(tài)游標(biāo)可以在游標(biāo)聲明時(shí)不指定其查詢定義，而是在游標(biāo)打開時(shí)進(jìn)行定義。動(dòng)態(tài)游標(biāo)又可以分為兩類：強(qiáng)類型動(dòng)態(tài)游標(biāo)和弱類型動(dòng)態(tài)游標(biāo)。,13.4.1 強(qiáng)類型動(dòng)態(tài)游標(biāo),強(qiáng)類型動(dòng)態(tài)游標(biāo)是指，在游標(biāo)使用之前，雖未指定游標(biāo)的查詢定義，但是游標(biāo)的類型已經(jīng)確定。,13.4.1 強(qiáng)類型動(dòng)態(tài)游標(biāo),1.聲明動(dòng)態(tài)游標(biāo) 動(dòng)態(tài)游標(biāo)變量是ref類型的變量。聲明動(dòng)態(tài)游標(biāo)時(shí)，應(yīng)該使用ref cursor關(guān)鍵字。強(qiáng)類型動(dòng)態(tài)游標(biāo)帶有return語句。所以在聲明強(qiáng)類型動(dòng)態(tài)游標(biāo)時(shí)，則需要為其指定一個(gè)返回類型，其語法形式如圖所示。 【示例13-21】該示例給出了用于游標(biāo)變量的不同聲明。,13.4.1 強(qiáng)類型動(dòng)態(tài)游標(biāo),
18、2.打開游標(biāo)變量 如果要將一個(gè)游標(biāo)變量與一個(gè)特定的select語句相關(guān)聯(lián)，需要使用open for語句，其語法結(jié)構(gòu)如圖所示。,13.4.1 強(qiáng)類型動(dòng)態(tài)游標(biāo),【示例13-22】游標(biāo)變量的打開示例。 【示例13-23】強(qiáng)類型動(dòng)態(tài)游標(biāo)常用于根據(jù)不同的條件來使用不同的游標(biāo)定義。例如，在表categories中，希望能夠獲得“software1”這一商品種類的具體信息。如果返回給用戶的記錄數(shù)目為0，那么用戶希望能夠查看到所有商品種類信息，以便能夠確定“software1”這一信息的確不存在。此時(shí)，可以利用強(qiáng)類型動(dòng)態(tài)游標(biāo)進(jìn)行處理。,13.4.2 弱類型動(dòng)態(tài)游標(biāo),動(dòng)態(tài)游標(biāo)返回的類型的是特定類型。在PL/SQ
19、L語言中，還有一種非受限游標(biāo)，即弱類型動(dòng)態(tài)游標(biāo)。它在聲明的時(shí)候沒有returne子句。一個(gè)非受限游標(biāo)變量可以為任何查詢打開。 使用弱類型動(dòng)態(tài)游標(biāo)沒有return語句，使用弱類型動(dòng)態(tài)游標(biāo)的語法，如圖所示。,13.4.2 弱類型動(dòng)態(tài)游標(biāo),【示例13-24】在表product_information中，如果無法獲得category_id為“22”的商品類別名稱信息，那么希望繼續(xù)查詢表categories以嘗試在該表中獲得category_id為“22”的商品類別名稱信息。表categories的結(jié)構(gòu)及數(shù)據(jù)。,13.5 小結(jié),本章介紹了游標(biāo)的概念定義。游標(biāo)可以幫助開發(fā)人員從大量的數(shù)據(jù)中提取每一條數(shù)據(jù)，是一個(gè)非常重要的知識點(diǎn)。游標(biāo)分為靜態(tài)游標(biāo)和動(dòng)態(tài)游標(biāo)。而靜態(tài)游標(biāo)分為顯示游標(biāo)和隱式游標(biāo)，其中顯示游標(biāo)可以接受開發(fā)者的操作。而隱式游標(biāo)不能接受開發(fā)者的操作，只能利用游標(biāo)的屬性來獲取相關(guān)信息。本章重點(diǎn)是顯示游標(biāo)和隱式游標(biāo)的靈活使用。本章難點(diǎn)是理解動(dòng)態(tài)游標(biāo)。,
精通Oracle核心技術(shù)和項(xiàng)目實(shí)戰(zhàn)之游標(biāo).pptx
