欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

Oracle存儲過程新手入門教程(附學習用例)

 更新時間：2024年01月25日 09:18:29   作者：可樂丿不加冰  
存儲過程就是作為可執(zhí)行對象存放在數(shù)據(jù)庫中的一個或多個SQL命令,下面這篇文章主要給大家介紹了關于Oracle存儲過程新手入門教程的相關資料,文中通過實例代碼介紹的非常詳細,需要的朋友可以參考下

本文是基于oracle編寫的存儲過程，以下所說的存儲過程均為oracle中的存儲過程，以及下面所說的存過都是存儲過程的簡稱

一、存儲過程的創(chuàng)建

創(chuàng)建存儲過程的方式兩種，一種是基于PLSQL創(chuàng)建，一種是ORACLE語法創(chuàng)建

PLSQL創(chuàng)建步驟：

1、連接plsql

2、找到左邊對象下的procedures，點擊右鍵

3、點擊新建進入到創(chuàng)建存過頁面

4、填寫存儲過程名稱以及輸入?yún)?shù)和輸出參數(shù)，點擊確定后生成存儲過程的基本框架內(nèi)容

ORACLE語法創(chuàng)建：（注意：如果沒有參數(shù)請把括號去掉，否則會報錯）

直接在文本中編寫存過的基本框架內(nèi)容

create or replace procedure pro_test(Name in out type, Name in out type, …) is
begin

end pro_test;

打開plsql或者oracle命令行模式直接復制執(zhí)行

二、存儲過程語法說明

1.存儲過程的基本結構

a、帶參存儲過程語法

	CREATE OR REPLACE PROCEDURE 存儲過程名字	(
	    --輸入?yún)?shù) IN NUMBER,
	    --輸出參數(shù) OUT NUMBER --可以有多個輸入?yún)?shù)和輸出參數(shù)
	) IS
	    --這個區(qū)間可以用來定義變量和創(chuàng)建游標
	BEGIN
	    --執(zhí)行游標或者執(zhí)行語句
	     commit; --對上面的內(nèi)容進行提交
	exception//存儲過程異常
	   when others then
	   	   --異常處理方法，可以是打印錯誤，然后進行回滾等操作，下面操作一樣，看自己情況決定
	   	   rollback;
	   	   dbms_output.put_line(sqlcode);
	   	   dbms_output.put_line(substr(sqlerrm, 1, 512));
	END 存儲過程名字;
	注意：其中參數(shù)IN表示輸入?yún)?shù)，是參數(shù)的默認模式。
				OUT表示返回值參數(shù)，類型可以使用任意Oracle中的合法類型。
				OUT模式定義的參數(shù)只能在過程體內(nèi)部賦值，表示該參數(shù)可以將某個值傳遞回調(diào)用他的過程
				IN OUT表示該參數(shù)可以向該過程中傳遞值，也可以將某個值傳出去。

b、無參存儲過程語法

	CREATE OR REPLACE PROCEDURE 存儲過程名字	
	IS
		    --這個區(qū)間可以用來定義變量和創(chuàng)建游標
		BEGIN
		    --執(zhí)行游標或者執(zhí)行語句
		     commit; --對上面的內(nèi)容進行提交
		exception//存儲過程異常
		   when others then
		   	   --異常處理方法，可以是打印錯誤，然后進行回滾等操作，下面操作一樣，看自己情況決定
		   	   rollback;
		   	   dbms_output.put_line(sqlcode);
		   	   dbms_output.put_line(substr(sqlerrm, 1, 512));
		END 存儲過程名字;

三.變量的聲明和賦值

變量的聲明：

    a、按照數(shù)據(jù)類型進行聲明
    格式：變量名 變量的數(shù)據(jù)類型;
    樣例：v_name varchar2(500);
    b、根據(jù)表字段進行聲明
    格式：變量名 表名.字段名%TYPE
    樣例：v_name userinfo.name%TYPE;
    注意：變量的數(shù)據(jù)類型和表名的字段數(shù)據(jù)類型一致
    c、根據(jù)表的行記錄進行聲明
    格式：變量名 表名/游標名%ROWTYPE
    樣例：v_user_row userinfo%ROWTYPE;
    注意：變量的數(shù)據(jù)類型和表的行數(shù)據(jù)類型一致

變量的賦值：

	a、直接賦值，需要使用<font color='red'>:=</font>進行賦值
		使用的條件：只能在變量的聲明a和b才能使用直接賦值
		先聲明在賦值（聲明在上面a和b）
		樣例：v_name:= '小花';
		聲明和賦值同時進行
		v_name varchar(500) := '小花'
	b、select 字段 into 變量 from 表名 進行賦值
			單個字段復制：
			select name into v_name from userinfo;
			使用條件：也是只限于a和b使用
			行記錄復制：
			select * into v_user_row from userinfo;
			使用條件：也是只限于c使用
	c、execute immediate sql語句 into 變量 進行賦值
		 v_name varchar2(500);
		 v_name_sql varchar2(500) :='select name from userinfo where id=1';
		 execute immediate v_name_sql into v_name ;
		 語句含義是like執(zhí)行v_name_sql 語句并將結果賦值給v_name。

四、存儲過程語句調(diào)試

如果只是調(diào)試存儲過程語句可直接用下面語法，在plsql的sql框中直接執(zhí)行，比如下面介紹的控制語句都可以直接使用，輸出結果去輸出框查看。

        基本能語法：
                DECLARE
                變量定義
                BEGIN
                ......
                END;

五、流程控制語句

1、條件控制語句 IF THEN

	基本語法：
		IF 條件表達式1 THEN
		語句段1
		ELSIF 條件表達式2 THEN
		語句段2
		......
		ELSIF 條件表達式n
		語句段n
		END IF;
	樣例：如果flag_num=0則輸出'早上好'，如果flag_num=1 則輸出 '中午好'，否則輸出'晚上好'，
	      declare 
	    	flag_num int:= 0;
	    begin
			if flag_num=0 then 
				dbms_output.put_line('早上好');
			elsif flag_num=1 then
				dbms_output.put_line('中午好');
			else
				dbms_output.put_line('晚上好');
			end if;
		end;

2、條件控制語句 CASE

	基本語法：
		CASE
		WHEN 條件表達式1 THEN
		語句段1;
		WHEN 條件表達式2 THEN
		語句段2;
		......
		ELSE
		語句段n;
		END CASE;
   樣例：如果flag_num=0則輸出'早上好'，如果flag_num=1 則輸出 '中午好'，否則輸出'晚上好'
	   	declare 
	   flag_num int:= 0;
	   begin
	   case 
	   when flag_num=0 then dbms_output.put_line('早上好');
	   when flag_num=1 then dbms_output.put_line('中午好');
	   else dbms_output.put_line('晚上好');
	  end case;
	  end;

五、循環(huán)控制語句

1、LOOP 循環(huán)

 		基本語法：
	     		LOOP
				EXIT [WHEN 條件表達式] --循環(huán)終止條件,為ture時，退出循環(huán)，否則再次執(zhí)行循環(huán)體
				語句段;
				END LOOP;
		樣例：
		declare 
		  i int:= 0;
		begin
		  loop
		      EXIT WHEN i > 3;
		      i:=i+1;
		      dbms_output.put_line(i);
	    end loop;
      end;
      語句解釋：初始變量i為0，當i進入loop循環(huán)時，i>3則退出循環(huán)，i等于i+1,進行打印，然后依次循環(huán)，直至i>3時退出循環(huán)。

2、While循環(huán)

 		基本語法：
	     		WHILE 條件表達式 LOOP
				語句段;
				END LOOP;
		樣例：
		declare
			  i int := 0;
		begin
			  while i < 3 loop
			    i := i + 1;
			    dbms_output.put_line(i);
			  end loop;
		end;
		語句解釋：初始變量i為0，當i進入loop循環(huán)時，i<3進入循環(huán)，i等于i+1,進行打印，然后依次循環(huán)，直至i不小于3時退出循環(huán)。

3、FOR循環(huán)

 		基本語法：（注意：如加上reverse表示倒敘循環(huán)執(zhí)行語句）
	     		FOR 循環(huán)變量 in [REVERSE] 初值表達式..終值表達式 LOOP
				語句段;
				END LOOP;
		樣例：
		declare 
		  i int := 0;
		begin
		  for i IN reverse 1..5 loop
		    dbms_output.put_line(i);
		  end loop;
		end;
		語句解釋：初始變量i為0，循環(huán)遍歷1..5，for循環(huán)默認是遞增，但是加上reverse 會變成遞減，將5賦給i,打印i,直至將1..5全部循環(huán)遍歷，結束循環(huán)，for循環(huán)只適用于已知循環(huán)次數(shù)的遍歷

六、游標

游標（Cursor）的定義：

   游標是SQL的一個內(nèi)存工作區(qū)，由系統(tǒng)或用戶以變量的形式定義。游標的作用就是用于臨
   時存儲從數(shù)據(jù)庫中提取的數(shù)據(jù)塊。在某些情況下，需要把數(shù)據(jù)從存放在磁盤的表中調(diào)到計
   算機內(nèi)存中進行處理，最后將處理結果顯示出來或最終寫回數(shù)據(jù)庫。這樣數(shù)據(jù)處理的速度
   才會提高，否則頻繁的磁盤數(shù)據(jù)交換會降低效率。

上面是游標的官方定義，我們可以把游標理解為一個用于封裝多條數(shù)據(jù)的集合。因此對于集合的遍歷操作，那么游標也可以使用，下面我們會講到。

那么游標存在隱式游標和顯式游標

隱式游標

DML操作和單行SELECT語句會使用隱式游標，它們分別是：

* 插入操作：INSERT。 
* 更新操作：UPDATE。 
* 刪除操作：DELETE。 
* 單行查詢操作：SELECT ... INTO ...。

隱式游標游標屬性使用樣例：

1.創(chuàng)建一個包含name,age,addr三個字段的表

	create table userinfo(
						name varchar2(500);
						age number(20);
					 	addr varchar2(500);
	)
2.編寫插入的代碼并用上面的游標屬性進行監(jiān)控

	declare
	begin
	  insert into userinfo (name,age,addr) values('zs',18,'sc');
	  dbms_output.put_line('游標所影響的行數(shù)：'||SQL%rowcount);
	  if SQL%NotFound then --沒有找到上面執(zhí)行的sql
	     dbms_output.put_line('SQL%NotFound：true');
	  else
	    dbms_output.put_line('SQL%NotFound：false');
	  end if;
	  if SQL%Found then --找到上面執(zhí)行的sql
	    dbms_output.put_line('SQL%Found：true');
	  else
	    dbms_output.put_line('SQL%Found：false');
	  end if;
	  if SQL%isopen then  --游標是否打開
	    dbms_output.put_line('SQL%isopen：true');  
	  else
	    dbms_output.put_line('SQL%isopen：false'); 
	  end if;
	end;
3.執(zhí)行的結果：

	游標所影響的行數(shù)：1
	SQL%NotFound：false
	SQL%Found：true
	SQL%isopen：false

4.編寫一個查詢的代碼對游標屬性進行監(jiān)控

	declare
	v_info userinfo %ROWTYPE;
	begin
	  select * into v_info from TEST_TABLE where name ='zs';
	  dbms_output.put_line('name：'||v_info.name);
	  dbms_output.put_line('age：'||v_info.age);
	  dbms_output.put_line('addr：'||v_info.addr);
	  dbms_output.put_line('游標所影響的行數(shù)：'||SQL%rowcount);
	  if SQL%NotFound then 
	     dbms_output.put_line('SQL%NotFound：true');
	  else
	    dbms_output.put_line('SQL%NotFound：false');
	    
	  end if;
	  if SQL%Found then 
	    dbms_output.put_line('SQL%Found：true');
	  else
	    dbms_output.put_line('SQL%Found：false');
	  end if;
	  if SQL%isopen then   
	    dbms_output.put_line('SQL%isopen：true');  
	  else
	    dbms_output.put_line('SQL%isopen：false'); 
	  end if;
	end;
6.查詢的結果為：

	name：zs
	age：18
	addr：sc
	游標所影響的行數(shù)：1
	SQL%NotFound：false
	SQL%Found：true
	SQL%isopen：false

顯示游標

1.游標的聲明

a.帶參數(shù)的游標聲明

CURSOR 游標名 (參數(shù)1 數(shù)據(jù)類型,......)
IS
SELECT語句;

b.不帶參數(shù)的游標聲明

CURSOR 游標名
IS
SELECT語句;

注意：SELECT語句是對表或視圖的查詢語句，甚至也可以是聯(lián)合查詢?？梢詭HERE條件、ORDER BY或GROUP BY等子句，但不能使用INTO子句。

2．打開游標

在可執(zhí)行部分，按以下格式打開游標： 
OPEN 游標名(參數(shù)1,.....) 				--存在參數(shù)
或者
OPEN 游標名 					--不存在參數(shù)
打開游標時，SELECT語句的查詢結果就被傳送到了游標工作區(qū)。

3．提取數(shù)據(jù)

FETCH 游標名 INTO 變量; 
游標打開后有一個指針指向數(shù)據(jù)區(qū)，F(xiàn)ETCH語句一次返回指針所指的一行數(shù)據(jù)，要返回多行需重復
執(zhí)行，可以使用循環(huán)語句來實現(xiàn)?？刂蒲h(huán)可以通過判斷游標的屬性來進行。

變量的定義有兩種情況

1.單個變量：記錄單個字段數(shù)據(jù)，變量名是用來從游標中接收數(shù)據(jù)的變量，需要事先定義。變量的個數(shù)和類型應與SELECT語句中
的字段變量的個數(shù)和類型一致。 
2.行變量：記錄一條數(shù)據(jù)，一次將一行數(shù)據(jù)取到記錄變量中，需要使用%ROWTYPE事先定義記錄變量，這種形式使用起來比較方便，不必分別定義和使用多個變量。
定義方式采用上面變量聲明c:
變量名 表名|游標名%ROWTYPE；

4．關閉游標

CLOSE 游標名;

顯式游標打開后，必須顯式地關閉。游標一旦關閉，游標占用的資源就被釋放，游標變成無效，必須重新打開才能使用。

游標屬性

通過游標的屬性，來了解游標的執(zhí)行狀態(tài)和結果，進而控制游標的執(zhí)行操作。

隱式游標可以使用名字SQL來訪問，但要注意，通過SQL游標名總是只能訪問前一個DML操作或單行SELECT操作的游標屬性。

通常在剛剛執(zhí)行完操作之后，立即使用SQL游標名來訪問屬性。

游標的屬性有四種:

	sql%found （布爾類型，默認值為null）
	
	sql%notfound（布爾類型,默認值為null）
	
	sql%rowcount(數(shù)值類型默認值為0)
	
	sql%isopen(布爾類型)

因為游標在運行DML語句時打開，完成后關閉，使用SQL%ISOPEN總是為false。所以隱式游標只使用SQL%FOUND,SQL%NOTFOUND,SQL%ROWCOUNT三個屬性。

1.SQL%FOUND和SQL%NOTFOUND

在執(zhí)行任何DML語句前SQL%FOUND和SQL%NOTFOUND的值都是NULL,在執(zhí)行DML語句后，
SQL%FOUND的屬性值將是：
	.TRUE :INSERT --判斷是否插入成功
	.TRUE :DELETE和UPDATE，至少有一行被DELETE或UPDATE.--判斷修改和刪除成功
	.TRUE :SELECT INTO至少返回一行  --判斷查詢成功

當SQL%FOUND為TRUE時,SQL%NOTFOUND為FALSE。

2.SQL%ROWCOUNT

 在執(zhí)行任何DML語句之前，SQL%ROWCOUNT的值都是NULL, 對于SELECT INTO語句，
 如果執(zhí)行成功，SQL%ROWCOUNT的值為1,如果沒有成功或者沒有操作(如update、insert、
 delete為0條），SQL%ROWCOUNT的值為0， 而對于update和delete來說表示游標所檢索
 數(shù)據(jù)庫行的個數(shù)即更新或者刪除的行數(shù)。

3.SQL%ISOPEN

 SQL%ISOPEN是一個布爾值，如果游標打開，則為TRUE, 如果游標關閉，則為FALSE.對于隱式
 游標而言SQL%ISOPEN總是FALSE，這是因為隱式游標在DML語句執(zhí)行時打開，結束時就立即
 關閉。

七、學習用例

下面提供幾個可以參考學習的用例：

for循環(huán)結合游標（cursor）樣例1：

declare
v_name varchar2(500) :='zs';
cursor cur_user(v_name varchar2)
is
select * from userinfo where name=v_name;
begin
  for user1 in cur_user(v_name) loop
   dbms_output.put_line('name：'||user1.name);
   dbms_output.put_line('age：'||user1.age); 
   dbms_output.put_line('addr：'||user1.addr);
  end loop;
end;

樣例1輸出結果：

name：zs
age：15
addr：sc
name：zs
age：18
addr：sc

for循環(huán)結合游標（cursor）樣例2 ：

declare
cursor cur_user
is
select * from userinfo where name='zs';
begin
  for user1 in cur_user loop
   dbms_output.put_line('name：'||user1.name);
   dbms_output.put_line('age：'||user1.age); 
   dbms_output.put_line('addr：'||user1.addr);
  end loop;
end;

輸出結果：

name：zs
age：15
addr：sc
name：zs
age：18
addr：sc

loop循環(huán)結合游標（cursor）樣例3：（這一個比較經(jīng)典，可以將上面游標所講內(nèi)容都包含在內(nèi)）

declare

v_name varchar2(500) :='zs'; --定義的常用變量

cursor cur_user(v_name varchar2)  --創(chuàng)建游標
is
select * from userinfo where name=v_name; --查詢語句

v_user cur_user%rowtype;  --定義的行變量，用于接收游標中的數(shù)據(jù)

begin
  open cur_user(v_name); --打開游標
  loop     --循環(huán)開始
    fetch cur_user into v_user; --從游標中提取數(shù)據(jù)
     exit when  cur_user%notfound;--判斷是游標中是否存在數(shù)據(jù)，不存在退出循環(huán)
     dbms_output.put_line('name：'||v_user.name);
	   dbms_output.put_line('age：'||v_user.age); 
	   dbms_output.put_line('addr：'||v_user.addr);
     dbms_output.put_line('------------');
  end loop;   
  close cur_user;  --關閉游標
end;

while循環(huán)結合游標（cursor）樣例4：

declare

v_name varchar2(500) :='zs'; --定義的常用變量

cursor cur_user(v_name varchar2)  --創(chuàng)建游標
is
select * from userinfo where name=v_name; --查詢語句

v_user cur_user%rowtype;  --定義的行變量，用于接收游標中的數(shù)據(jù)

begin
  open cur_user(v_name); --打開游標
    fetch cur_user into v_user; --從游標中提取數(shù)據(jù)
    while(cur_user%found)loop  --判斷是游標中是否存在數(shù)據(jù)，存在進入循環(huán)
     dbms_output.put_line('name：'||v_user.name);
	   dbms_output.put_line('age：'||v_user.age); 
	   dbms_output.put_line('addr：'||v_user.addr);
     dbms_output.put_line('------------'); 
      fetch cur_user into v_user;  --從游標中提取數(shù)據(jù) 
     end loop;
  close cur_user;  --關閉游標
end;

樣例5：（可根據(jù)傳入表名參數(shù)，分批刪除指定表的指定時間段的數(shù)據(jù)）

	create or replace procedure PRO_LAN_DELETE(
	                                            --標記是那種類型的刪除,0是按月份刪除，1是按次數(shù)刪除
	                                            flag_num in number,
	                                            --刪除的表名
	                                            table_name in varchar2,
	                                            --數(shù)據(jù)保留的次數(shù)或者保留數(shù)據(jù)的幾個月，月用負數(shù)，表示減幾個月
	                                            del_times in number,
	                                            str_o_flag out varchar2,
	                                            str_o_sqlcode out varchar2,
	                                            str_o_sqlerrm out varchar2
	)
	as
		  --刪除數(shù)據(jù)的總數(shù)
	    del_counts number;
	    --記錄被刪除的數(shù)量
		  flag_count number:=0;
	    --獲取刪除數(shù)據(jù)總數(shù)的sql
	    sql_count varchar2(1000);
	    --刪除數(shù)據(jù)的sql
	    sql_del varchar2(1000);
	begin
	  if flag_num=0 then
	    dbms_output.put_line('進入按月份刪除的部分');
	  	  sql_count:= 'SELECT COUNT(*) FROM '||table_name||' WHERE TRUNC(CREATE_TIME)<
	      ADD_MONTHS((SELECT MAX(DISTINCT TRUNC(CREATE_TIME)) AS CREATE_TIME FROM '||table_name||'),'||del_times||')';
	      --立刻執(zhí)行sql_count,并賦值給del_counts
	      execute immediate sql_count
	      into del_counts;
	      --如果刪除的總數(shù)大于0就繼續(xù)刪除
	      if del_counts>0 then
	       dbms_output.put_line('開始刪除');
	       loop
	            --刪除的sql
	          sql_del :='DELETE FROM '||table_name||' WHERE TRUNC(CREATE_TIME)<
	      ADD_MONTHS((SELECT MAX(DISTINCT TRUNC(CREATE_TIME))AS CREATE_TIME FROM '||table_name||'),'||del_times||')and rownum<=50000';
	          --立刻執(zhí)行sql_del
	          execute immediate sql_del;
	          --統(tǒng)計被刪除的數(shù)量
	          flag_count:=flag_count+sql%rowcount;
	          dbms_output.put_line('已刪除'||flag_count||'條');
	          commit;
	          --立刻執(zhí)行sql_count,并賦值給del_counts
	          execute immediate sql_count
	          into del_counts;
	           dbms_output.put_line('剩余'||del_counts||'條');
	          --為0時退出
	          exit when del_counts<=0;
	       end loop;
	       dbms_output.put_line('刪除成功');
	      end if;
	       dbms_output.put_line('成功刪除'||flag_count||'條');
	      str_o_flag :='成功刪除'||flag_count||'條';
	  elsif flag_num=1 then
	     dbms_output.put_line('進入按次數(shù)刪除的部分');
	      sql_count := 'SELECT COUNT(*) FROM '||table_name||' WHERE TRUNC(CREATE_TIME) <
	      (SELECT TRUNC(CREATE_TIME) FROM (SELECT CREATE_TIME,row_number()over(order by CREATE_TIME desc) AS RN FROM(SELECT DISTINCT CREATE_TIME FROM '||table_name||'))WHERE RN='||del_times||')';
	      --立刻執(zhí)行sql_count,并賦值給del_counts
	      execute immediate sql_count
	      into del_counts;
      --如果刪除的總數(shù)大于0就繼續(xù)刪除
      if del_counts>0 then
        dbms_output.put_line('開始刪除');
       loop
            --刪除的sql
          sql_del :='DELETE FROM '||table_name||' WHERE TRUNC(CREATE_TIME) <
          (SELECT TRUNC(CREATE_TIME) FROM (SELECT CREATE_TIME,row_number()over(order by CREATE_TIME desc) AS RN FROM(SELECT DISTINCT CREATE_TIME FROM '||table_name||'))WHERE RN='||del_times||')and rownum<=50000';
          --立刻執(zhí)行sql_del
          execute immediate sql_del;
          --統(tǒng)計被刪除的數(shù)量
          flag_count:=flag_count+sql%rowcount;
           dbms_output.put_line('已刪除'||flag_count||'條');
          commit;
          --立刻執(zhí)行sql_count,并賦值給del_counts
          execute immediate sql_count
          into del_counts;
          dbms_output.put_line('剩余'||del_counts||'條');
          --為0時退出
          exit when del_counts<=0;
       end loop;
       dbms_output.put_line('刪除成功');
      end if;
      dbms_output.put_line('成功刪除'||flag_count||'條');
      str_o_flag :='成功刪除'||flag_count||'條';
	   end if;
	--異常處理
	exception
	  when others then
	    rollback;
	    str_o_flag := '失敗';
	    str_o_sqlcode := sqlcode;
	    str_o_sqlerrm := substr(sqlerrm, 1, 512);
	  commit;
	end PRO_LAN_DELETE;

總結 

到此這篇關于Oracle存儲過程新手入門教程的文章就介紹到這了,更多相關Oracle存儲過程入門教程內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

相關文章

最新評論