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

[gkrv]

Great video for Beginners. Sir,
1) i want to catch the error code and description and want to insert in my own log kind of table? How can write the code for that?

2) If i have multiple insert, delete, update and select in my procedure,
should i write DECLARE EXIT HANDLER for each statement separately or single declaration is enough?
( In oracle entire code can be covered by EXCEPTION, and we can write the code there for insert the error message )

Kindly give me the link/video/page above scenario? Thanks in advance

[rahulyadav-qbnw]

One of the worst teacher for MySQL🤣😂😂😂 . He knows how to do MySQL but he don't know how to teach.