Part 6 MySQL Views, Indexes, Procedures, Functions, Exception Handling and Transactions
View :
Advantages of MySQL Views
Create or Replace View
Merge view
Views with joins
Views with insert
Views with update
Views with delete
Not possible cases to implement insert, update
Drop view
Index
Stored procedure :
Advantages :
1. Reduce network traffic
2. Centralize business logic in the database
3. Make database more secure
Disadvantages:
1. Resource usages
2. Troubleshooting
3. Maintenances
RDBMS API web
Procedure call SP
Send parameters
Get the response
Variables :
Declaring variables
Session variables or user-defined variables
Parameters
IN
OUT
INOUT
List of procedures
Drop procedures
Drop procedure test;
Functions
Exception Handling
DECLARE CONTINUE HANDLER FOR NOT FOUND
BEGIN
SET @handler_invoked = 1;
END;
DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
BEGIN
GET DIAGNOSTICS CONDITION 1
@p2 = Message_TEXT;
SELECT 500 AS StatusCode, @p2 AS Message;
END;
Transactions