mysql dba training session 14 stored procedures and functions in mysql
TRANSCRIPT
MySQL DBA Training Session 14.Stored Procedures and Functions in MySQLRAM N SANGWAN
WWW.RNSANGWAN.COM
YOUTUBE CHANNEL : HTTP://YOUTUBE.COM/USER/THESKILLPEDIA
TO LEARN OR TEACH JOIN WWW.THESKILLPEDIA.COM
Coming up in this Session
• Definition –MySQL Stored Routines
• Features of MySQL Stored Procedures
• Disadvantages
• Getting Started with MySQL Stored Routines
• Work Flow
• Checking Stored Procedure
• Stored Functions
• Examples
• Stored Routine Parameter Types
Coming up in this Session
• Stored Procedure- Complete Syntax
• Stored Procedure/Function Syntax
• Control Flow Statements
• Control-Flow Example Code
• Case Statement
• Repeat Loop
• While Loop
• Declaring a handler
• Stored Routine Security
Definition
• A stored procedure is a segment of declarative SQL statements stored inside the databasecatalog.
• MySQL does not support recursive stored procedure very well.
• Stored Procedures were added to MySQL version 5.0.
Features
• MySQL stored procedures are compiled on demand.
• After compiling a stored procedure, MySQL puts it to a cache.
• If an application uses a stored procedure multiple times in a single connection, thecompiled version is used, otherwise the stored procedure works like a query.
• Stored procedures helps reduce the traffic between application and database server.
• Stored procedures are reusable and transparent to any applications.
• Stored procedures are secure.
Disadvantages
• If you use a lot of stored procedures, the memory usage of every connection that is usingthose stored procedures will increase substantially.
• A constructs of stored procedures make it more difficult to develop stored procedures thathave complicated business logic.
• MySQL does not provide facilities for debugging stored procedures.
• It is not easy to develop and maintain stored procedures.
Getting Started..
Suppose we want to keep track of the total salaries of employeesworking for each department
We need to write a procedure to
update the salaries in the deptsal
table
Step -1
Change the delimiter (i.e., terminating character) of SQL statement from
semicolon (;) to something else (e.g., //) So that you can distinguish between
the semicolon of the SQL statements in the procedure and the terminating
character of the procedure definition
Step 2
1. Define a procedure called updateSalary which takes as input a department
number.
2. The body of the procedure is an SQL command to update the totalsalary
column of the deptsal table.
3. Terminate the procedure definition using the delimiter you had defined in step
1 (//)
Step 3
Change the delimiter back to semicolon (;)
Done!!
Call the procedure to update the totalsalary for each
department
Check it!
Show the updated total salary in the deptsal table
Looking Back!
• Use show procedure status to display the list of stored procedures you have created
Use drop procedure to remove a stored procedure
Stored Functions
function <function-name> (param_spec1, …, param_speck)
returns <return_type>
[not] deterministic
Begin
-- execution code
end;
where param_spec is:
[in | out | in out] <param_name> <param_type>
◦ You need ADMIN privilege to create functions on mysql-user server
Example of Functions
Example of Functions
Stored Routine Parameter Types
• IN. This is the default. It is passed to the routine and can be changed insidethe routine, but remains unchanged outside.
• OUT. No value is supplied to the routine (it is assumed to be NULL), but itcan be modified inside the routine, and it is available outside the routine.
• INOUT. The characteristics of both IN and OUT parameters. A value can bepassed to the routine, modified there as well as passed back again.
Control Flow Statements
IF search_condition THEN
statement_list
[ELSEIF search_condition THEN
statement_list] ….
[ELSE
statement_list]
END IF
Control-Flow Example Code
DELIMITER //
CREATE FUNCTION SimpleCompare(i INT, j INT) RETURNS VARCHAR(20)
BEGIN
DECLARE s VARCHAR(20);
IF j > i THEN
set s = '>';
ELSEIF i = j THEN
set s = '=';
ELSE
set s = '<'
END IF;
set s = CONCAT(j, ' ', s, ' ', i);
RETURN s;
END //
DELIMITER ;
Case Statement
CASE VarName
WHEN VarValue1 THEN Statement/s
WHEN VarValue2 THEN Statement/s
......
ELSE Statement/s
END CASE
Repeat Loop
DELIMITER //
CREATE PROCEDURE SimpleRepeat(p1 INT)
BEGIN
SET @x = 0;
REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
END
//
DELIMITER ;
While Loop
CREATE PROCEDURE SimpleWhile()
BEGIN
DECLARE v1 INT DEFAULT 5;
WHILE v1 > 0 DO
...
SET v1 = v1 - 1;
END WHILE;
END
Declaring a handler
To declare a handler, use the DECLARE HANDLER statement as follows:
DECLARE action HANDLER FOR condition_value statement;
If a condition whose value matches the condition_value, MySQL will execute the
statement and continue or exit the current code block based on the action.
The action accepts one of the following values:
• CONTINUE: the execution of the enclosing code block ( BEGIN … END)
continues.
• EXIT: the execution of the enclosing code block, where the handler is declared,
terminates.
• The condition_value specifies a particular condition or a class of conditions that
activates the handler.
Declaring a handler Contd..
The condition_value accepts one of the following values:
• A MySQL error code.• A standard SQLSTATE value. Or it can be an SQLWARNING, NOTFOUND or
SQLEXCEPTION condition, which is shorthand for the class of SQLSTATE values.
The NOTFOUND condition is used for a cursor or SELECT INTO variable_list statement.
• A named condition associated with either a MySQL error code or SQLSTATE value.
• The statement could be a simple statement or a compound statement enclosing by
the BEGIN and END keywords.
Stored Routine Security
• Access to an SR must be explicitly granted to users that did not create it and plan to use it.
• It is possible to give greater privileges to tables than those users that execute the SR.
• It is possible to create an environment more resistant to SQL injections.
Thank You