Stored Procedure 5-handling of conditions and exceptions and handling of Stored Procedure exceptions

Stored Procedure 5-handling of conditions and exceptions and handling of Stored Procedure exceptions

Exception Handling can be used for general process control in subprograms. If you want to handle errors during SQL Execution, you can use exception handling. For example, for errors or warnings that may occur in Stored Procedures, triggers, or internal functions, capture related exceptions or exceptions, and then handle them accordingly.

I. Conditions and handling procedures

1. DECLARE Conditions

Syntax:

Declare condition name condition for condition Value

The condition values are as follows:

• SQLSTATE [VALUE] sqlstate_value
• Mysql_error_code

Both sqlstate_value and mysql_error_code can indicate MySQL errors. For example, in ERROR 1146 (42S02), sqlstate_value is 42S02, and mysql_error_code is 1146. This statement specifies the conditions that require special processing. It associates a name with a specified error condition. This name can then be used in the declare handler statement.

Code:

-- 1: Use sqlstate_value DECLARE can_not_find condition for sqlstate '42s02 '; -- 2: Use mysql_error_code DECLARE can_not_find condition for 1146;

2. DECLARE Handler

Syntax:
DECLARE processing type handler for Parameter Error Type [,...] sp_statement

The processing type has the following values:

• CONTINUE: the error is not processed. CONTINUE to run down.
• EXIT: EXIT immediately after an error occurs.
• UNDO: if an error occurs, the operation is recalled. This method is not supported in MySQL.

Parameter error types include the following values:

• SQLSTATE [VALUE] qlstate_value: This format is specifically for ansi SQL, ODBC, and other standards. Not all MySQL ERROR codes are mapped to SQLSTATE.
• Condition_name: Condition name of the DECLARE Condition
• SQLWARNING: shorthand for all SQLSTATE codes starting with 01.
• Not found: a stenographer for all SQLSTATE codes starting with 02. Of course, a cursor can also be reached at the end of the dataset.
• SQLEXCEPTION: A shorthand for all SQLSTATE codes that are NOT captured by SQLWARNING or not found.
• Mysql_error_code: Commonly Used mysql_error_code list http://dev.mysql.com/doc/refman/5.0/en/error-messages-server.html

Code:

-- 1: capture sqlstate_value declare continue handler for sqlstate '42s02' SET @ info = 'can not find '; -- 2: capture mysql_error_code declare continue handler for 1146 SET @ info = 'can not find '; -- 3: first define the CONDITION and then call 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 '; -- 4: Use sqlwarning declare exit handler for sqlwarning set @ info = 'error '; -- Method 5: Use not found declare exit handler for not found set @ info = 'can not find '; -- 6: use sqlexception declare exit handler for sqlexception set @ info = 'error ';

Ii. Instances

Insert a duplicate record to the table. If the id is the same, an exception occurs. In this case, we can handle the exception.

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; 1112 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 '000000' 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 -- returns-130 CALL proc_test_exce (3, 'Chinese', 34, @ result); 31 SELECT @ result;

CALL proc_test_exce (3, 'Chinese', 34, @ result) is executed consecutively. Twice, the first reuslt returns 1, and the second returns-1. Because of the id conflict, if you run the command in 24 rows, the system will not exit and continue to execute, but 22 rows will be executed. Therefore, the second response is-1.

Oracle Stored Procedure Exception Handling

Create an Error Tracking table first
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)
);
-------- Write error information to the tracking table in the exception process:
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
-- Record error information table
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;

When a general stored procedure is used, the condition column names in all three tables cause unclear conditions. How can this problem be solved?

Database Design Methods, specifications and skills
I. Database Design Process
Database technology is the most effective means of information resource management. Database Design refers to constructing the optimal database mode for a given application environment, establishing a database and its application system, and effectively storing data to meet user information requirements and processing requirements.
In the demand analysis phase of database design, the application requirements of various users are integrated (real-world requirements ), in the conceptual design stage, a conceptual model (information world model) independent of the machine characteristics and various DBMS products is formed, which is described by E-R diagram. In the logic design stage, the E-R diagram is converted into the data model supported by the specific database product, such as the relational model, to form the database logic model. Then, based on the user's processing requirements and security considerations, the necessary VIEW is created on the basis of the basic table to form an external data mode. In the physical design phase, you can arrange physical storage based on the characteristics and processing needs of the DBMS, design indexes, and form the internal database mode.
1. Demand analysis stage
Requirement collection and analysis: the data requirement described by the data dictionary is obtained (and the processing requirement described by the data flow diagram ).
Requirement Analysis focuses on investigating, collecting, and analyzing users' information requirements, processing requirements, and security and integrity requirements in data management.
Requirement Analysis Methods: Investigate the organization and organization, investigate the business activities of each department, assist users in clarifying various requirements for the new system, and determine the boundaries of the new system.
Common survey methods include: follow-up operations, opening survey meetings, inviting special personnel to introduce, ask, and designing surveys. Users are requested to fill in and check records.
The methods for analyzing and expressing user requirements mainly include top-down and bottom-up methods. The top-down Structured Analysis method (SA) starts with the upper-level system organization and analyzes the system layer by layer, the data flow diagram and data dictionary are used to describe each layer.
The data flow chart shows the relationship between the data and the processing process. The Data in the system is described using the Data Dictionary (DD.
A data dictionary is a collection of various data descriptions. It describes the data in a database, that is, metadata, rather than the data itself. A data dictionary consists of data items, data structures, data streams, data storage, and processing processes (at least the Data Type of each field and the Primary and Foreign keys in each table should be included ).
Data item description = {data item name, description, alias, data type, length,
Value range, value meaning, and logical relationship with other data items}
Data Structure Description = {Data Structure name, description, composition: {data item or data structure }}
Data Stream description = {data stream name, description, data stream source, data stream destination,
Composition: {data structure}, average traffic, peak traffic}
Data storage description = {data storage name, description, number, inbound data stream, outbound data stream,
Composition: {data structure}, data volume, access method}
Process description = {process name, description, input: {data stream}, output: {data stream },
Handling: {brief description }}
2. Conceptual Structure Design Stage
A conceptual model independent of a specific DBMS is formed through the synthesis, induction and abstraction of user requirements, which can be expressed by a E-R diagram.
The conceptual model is used for modeling the information world. The conceptual model does not depend on the data model supported by a DBMS. A conceptual model can be converted to a specific data model supported by a DBMS on a computer.
Concept model features:
(1) Strong semantic expression ability, which can easily and directly express various semantic knowledge in applications.
(2) It should be simple, clear, and easy to understand. It is a language for communication between users and database designers.
A common method for conceptual model design is the IDEF1X method, which applies the entity-contact method to a Semantic Modeling Technology in the semantic data model and is used to establish a system information model.
The steps to create a E-R model using the IDEF1X method are as follows:
2.1 Step 1 -- initialize the project
In this phase, the task starts from the description of the purpose and scope, determines the modeling target, develops the modeling plan, and organizes the modeling... the remaining full text>