精通Oracle核心技術和項目實戰(zhàn)之游標.pptx

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