程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> 存儲過程之五—條件和異常處理，存儲過程異常處理

存儲過程之五—條件和異常處理，存儲過程異常處理

編輯：MySQL綜合教程

存儲過程之五—條件和異常處理，存儲過程異常處理

　　異常處理可用在子程序中的一般流程控制。當我們希望對sql執行過程中出現的錯誤情況進行處理，就可以用到異常處理。如針對存儲過程、觸發器或函數內部語句可能發生的錯誤或警告信息，需要進行相關異常或稱異常的捕獲，然後作出相應的處理。

一、條件和處理程序

　　1、DECLARE條件

　　語法：

　　DECLARE 條件名稱 CONDITION FOR 條件值

　　條件值有如下取值：

　　SQLSTATE [VALUE] sqlstate_value
　　mysql_error_code

　　sqlstate_value參數和mysql_error_code參數都可以表示MySQL的錯誤。例如ERROR 1146 (42S02)中，sqlstate_value值是42S02，mysql_error_code值是1146。這個語句指定需要特殊處理的條件。它將一個名字和指定的錯誤條件關聯起來。這個名字可以隨後被用在DECLARE HANDLER語句中。

　　代碼：

-- 一：使用sqlstate_value  
DECLARE  can_not_find  CONDITION  FOR  SQLSTATE  '42S02' ;  
-- 二：使用mysql_error_code  
DECLARE  can_not_find  CONDITION  FOR  1146 ;

　　2、DECLARE處理程序

　　語法：
　　DECLARE 處理類型 HANDLER FOR 參數錯誤類型[,...] sp_statement

　　處理類型有如下取值：

　　CONTINUE ：錯誤不進行處理，繼續向下執行。
　　EXIT　　：遇到錯誤後馬上退出。
　　UNDO : 遇到錯誤後撤回之前的操作，MySQL中暫時還不支持這種處理方式。

　參數錯誤類型有如下取值：

SQLSTATE [VALUE] qlstate_value ：這種格式是專門為ANSI SQL 和 ODBC以及其他的標准. 並不是所有的MySQL ERROR CODE 都映射到SQLSTATE。
condition_name ：DECLARE條件的條件名稱
SQLWARNING ：對所有以01開頭的SQLSTATE代碼的速記。
NOT FOUND ：是對所有以02開頭的SQLSTATE代碼的速記，當然也可以代表一個游標到達數據集的末尾。
SQLEXCEPTION ：是對所有沒有被SQLWARNING或NOT FOUND捕獲的SQLSTATE代碼的速記。
mysql_error_code：常用mysql_error_code 列表http://dev.mysql.com/doc/refman/5.0/en/error-messages-server.html

　　代碼：

    -- 一：捕獲sqlstate_value  
    DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info='CAN NOT FIND';  

    -- 二：捕獲mysql_error_code  
    DECLARE CONTINUE HANDLER FOR 1146 SET @info='CAN NOT FIND';  

    -- 三：先定義條件，然後調用  
    DECLARE  can_not_find  CONDITION  FOR  SQLSTATE  '42S02' ;
    -- DECLARE  can_not_find  CONDITION  FOR  1146 ;
    DECLARE CONTINUE HANDLER FOR can_not_find SET @info='CAN NOT FIND';  
    
    -- 四：使用SQLWARNING  
    DECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR';  

    -- 方法五：使用NOT FOUND  
    DECLARE EXIT HANDLER FOR NOT FOUND SET @info='CAN NOT FIND'; 
 
    -- 六：使用SQLEXCEPTION  
    DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='ERROR';

二、實例

　　向表中插入重復的一條記錄，由於id相同，會產生異常，此時我們可以進行異常處理。

 1 -- ----------------------------
 2 -- Table structure for course
 3 -- ----------------------------
 4 DROP TABLE IF EXISTS `course`;
 5 CREATE TABLE `course` (
 6   `id` int(11) NOT NULL,
 7   `name` varchar(255) NOT NULL,
 8   `score` int(11) NOT NULL,
 9   PRIMARY KEY (`id`)
10 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
11 
12 
13 DROP PROCEDURE IF EXISTS proc_test_exce;
14 CREATE PROCEDURE proc_test_exce(
15     IN uid INT(11),
16     IN uname VARCHAR(255),
17     IN uscore INT(11),
18     OUT result INT(11)
19 )
20 BEGIN 
21     -- DECLARE EXIT HANDLER FOR SQLSTATE '23000' set result = -1; 
22     DECLARE EXIT HANDLER FOR SQLWARNING,NOT FOUND,SQLEXCEPTION set result=-1; 
23     START TRANSACTION;        
24         INSERT INTO course (id, name, score) VALUES(uid, uname, uscore);
25         set result = 1;  
26     COMMIT; 
27 END;
28 
29 -- id 相同則返回-1
30 CALL proc_test_exce(3 ,'中文', 34, @result);
31 SELECT @result;

　　　連續執行CALL proc_test_exce(3 ,'中文', 34, @result);兩次，第一次reuslt返回1，第二次返回-1。因為id沖突，執行到24行，就不會再退出，不會再往下執行，而會執行22行。所以第二次返回的是-1。

oracle 存儲過程異常處理

先建一個錯誤跟蹤表
create sequence s_error_id;
CREATE TABLE ERROR_RECORD
(
ERROR_ID NUMBER(10) primary key,
ERROR_CODE NUMBER(10),
ERROR_MSG VARCHAR2(2000),
ERROR_DATE DATE,
ERROR_PROGRAM VARCHAR2(200)
);
--------在過程的例外裡向跟蹤表寫入錯誤信息:
create or replace procedure day_task is
v_sqlcode ERROR_RECORD.ERROR_CODE%type;
v_msg ERROR_RECORD.error_msg%type;
begin
------program body
exception when others then
--記錄錯誤信息表
v_sqlcode :=sqlcode;
v_msg :=sqlerrm;
insert into error_record(error_code,error_msg,error_date,error_program)
values (v_sqlcode,v_msg,sysdate,‘day_task’);
end;

使用通用存儲過程時條件列名3個表中都有導致條件不明確怎解決

數據庫設計方法、規范與技巧
一、數據庫設計過程
數據庫技術是信息資源管理最有效的手段。數據庫設計是指對於一個給定的應用環境，構造最優的數據庫模式，建立數據庫及其應用系統，有效存儲數據，滿足用戶信息要求和處理要求。
數據庫設計中需求分析階段綜合各個用戶的應用需求（現實世界的需求），在概念設計階段形成獨立於機器特點、獨立於各個DBMS產品的概念模式（信息世界模型），用E-R圖來描述。在邏輯設計階段將E-R圖轉換成具體的數據庫產品支持的數據模型如關系模型，形成數據庫邏輯模式。然後根據用戶處理的要求，安全性的考慮，在基本表的基礎上再建立必要的視圖（VIEW）形成數據的外模式。在物理設計階段根據DBMS特點和處理的需要，進行物理存儲安排，設計索引，形成數據庫內模式。
1. 需求分析階段
需求收集和分析，結果得到數據字典描述的數據需求（和數據流圖描述的處理需求）。
需求分析的重點是調查、收集與分析用戶在數據管理中的信息要求、處理要求、安全性與完整性要求。
需求分析的方法：調查組織機構情況、調查各部門的業務活動情況、協助用戶明確對新系統的各種要求、確定新系統的邊界。
常用的調查方法有：跟班作業、開調查會、請專人介紹、詢問、設計調查表請用戶填寫、查閱記錄。
分析和表達用戶需求的方法主要包括自頂向下和自底向上兩類方法。自頂向下的結構化分析方法（Structured Analysis，簡稱SA方法）從最上層的系統組織機構入手，采用逐層分解的方式分析系統，並把每一層用數據流圖和數據字典描述。
數據流圖表達了數據和處理過程的關系。系統中的數據則借助數據字典（Data Dictionary，簡稱DD）來描述。
數據字典是各類數據描述的集合，它是關於數據庫中數據的描述，即元數據，而不是數據本身。數據字典通常包括數據項、數據結構、數據流、數據存儲和處理過程五個部分(至少應該包含每個字段的數據類型和在每個表內的主外鍵)。
數據項描述＝｛數據項名，數據項含義說明，別名，數據類型，長度，
取值范圍，取值含義，與其他數據項的邏輯關系｝
數據結構描述＝｛數據結構名，含義說明，組成:｛數據項或數據結構｝｝
數據流描述＝｛數據流名，說明，數據流來源，數據流去向，
組成:｛數據結構｝，平均流量，高峰期流量｝
數據存儲描述＝｛數據存儲名，說明，編號，流入的數據流，流出的數據流，
組成:｛數據結構｝，數據量，存取方式｝
處理過程描述＝｛處理過程名，說明，輸入:｛數據流｝，輸出:｛數據流｝,
處理:｛簡要說明｝｝
2. 概念結構設計階段
通過對用戶需求進行綜合、歸納與抽象，形成一個獨立於具體DBMS的概念模型，可以用E-R圖表示。
概念模型用於信息世界的建模。概念模型不依賴於某一個DBMS支持的數據模型。概念模型可以轉換為計算機上某一DBMS支持的特定數據模型。
概念模型特點：
(1) 具有較強的語義表達能力，能夠方便、直接地表達應用中的各種語義知識。
(2) 應該簡單、清晰、易於用戶理解，是用戶與數據庫設計人員之間進行交流的語言。
概念模型設計的一種常用方法為IDEF1X方法，它就是把實體-聯系方法應用到語義數據模型中的一種語義模型化技術，用於建立系統信息模型。
使用IDEF1X方法創建E-R模型的步驟如下所示:
2.1 第零步——初始化工程
這個階段的任務是從目的描述和范圍描述開始，確定建模目標，開發建模計劃，組織建模......余下全文>>