Pl-sql

Stored Routines (Procedures and Functions) are supported in version MySQL 5.0. Stored Procedure is a set of statements, which allow ease and flexibility for a programmer because stored procedure is easy to execute than reissuing the number of individual SQL statements. Stored procedure can call another stored procedure also. Stored Procedure can very useful where multiple client applications are written in different languages or it can be work on different platforms but they need to perform the same database operations. Store procedure can improve the performance because by using the stored procedure less information needs to be sent between the server and the client. It increase the load on the database server because less work is done on the client side and much work is done on the server side.

CREATE PROCEDURE Syntax The general syntax of Creating a Stored Procedure is : CREATE PROCEDURE proc_name ([proc_parameter[......]]) routine_body proc_name : procedure name proc_parameter : [ IN | OUT | INOUT ] param_name type routine_body : Valid SQL procedure statement The parameter list is available with in the parentheses. Parameter can be declared to use any valid data type, except that the COLLATE attribute cannot be used. By default each parameter is an IN parameter. For specifying other type of parameter used the OUT or INOUT keyword before the parameter name. An IN parameter is used to pass the value into a procedure. The procedure can be change the value but when the procedure return the value then modification is not visible to the caller. An OUT parameter is used to pass the value from the procedure to the caller but its visible to the caller. An INOUT parameter is initialized by the caller and it can be modified by the procedure, and any change made by the procedure is visible to the caller. For each OUT or INOUT parameter you have to pass a user –defined variable because then the procedure returns the value then only you can obtain it values. But if you invoking the procedure from the other procedure then you can also pass a routine parameter or variable as an IN or INOUT parameter. The routine_body contains the valid SQL procedure statement that can be a simple statement like SELECT or INSERT or they can be a compound statement written using BEGIN and END. Compound statement can consists declarations, loops or other control structure. Now we are describing you a example of a simple stored procedure which uses an OUT parameter. It uses the mysql client delimiter command for changing the statement delimiter from ; to // till the procedure is being defined. Example : mysql> delimiter // mysql> CREATE PROCEDURE Sproc(OUT p1 INT) -> SELECT COUNT(*) INTO p1 FROM Emp; -> // Query OK, 0 rows affected (0.21 sec) mysql> delimiter ; mysql> CALL Sproc(@a); Query OK, 0 rows affected (0.12 sec) mysql> select @a; +------+ | @a |

+------+ | 5 | +------+ 1 row in set (0.00 sec)

CREATE FUNCTION Syntax The general syntax of Creating a Function is : CREATE FUNCTION func_name ([func_parameter[,...]]) RETURNS type routine_body func_name : Function name func_parameter : param_name type type : Any valid MySQL datatype routine_body : Valid SQL procedure statement The RETURN clause is mandatory for FUNCTION. It used to indicate the return type of function. Now we are describing you a simple example a function. This function take a parameter and it is used to perform an operation by using an SQL function and return the result. In this example there is no need to use delimiter because it contains no internal ; statement delimiters. Example : mysql> CREATE FUNCTION func(str CHAR(20)) -> RETURNS CHAR(50) -> RETURN CONCAT('WELCOME TO, ',str,'!'); Query OK, 0 rows affected (0.00 sec) mysql> SELECT func('RoseIndia'); +------------------------+ | func('RoseIndia') | +------------------------+ | WELCOME TO, RoseIndia! | +------------------------+ 1 row in set (0.00 sec)

ALTER PROCEDURE and ALTER FUNCTION Syntax For creating the procedure or function we used the CREATE PROCEDURE | FUNCTION statement and for altering the procedure we used the ALTER PROCEDURE | FUNCTION statement. Alter Procedure statement is used to change access permissions that preserves by the procedure. And ALTER PROCEDURE needs the use of the same encryption and recompile option as the original CREATE PROCEDURE command. ALTER PROCEDURE | FUNCTION statement can be used for renaming the stored procedure or function and for changing it characteristics also. We can specify the more than one changes also in an ALTER PROCEDURE | FUNCTION statement. But for this you required the ALTER ROUTINE privilege. ALTER {PROCEDURE | FUNCTION} {proc_name | func_name} [characteristic ...] characteristic: SQL SECURITY {DEFINER | INVOKER}| COMMENT 'string' Example : mysql> ALTER PROCEDURE Sproc SQL SECURITY DEFINER; Query OK, 0 rows affected (2.00 sec)

With the ALTER PROCEDURE Statement you can change only the characteristics and if you want to change in statement list then you have to DROP the procedure and CREATE again.

DROP PROCEDURE and DROP FUNCTION Syntax DROP PROCEDURE | FUNCTION Statement is used to drop a Procedure or Function. But for dropping them you must have the ALTER ROUTINE privilege. If IF NOT EXISTS clause is available then its prevents you from occurring an error when the procedure or function does not exist its produced only a warning. DROP {PROCEDURE | FUNCTION} [IF EXISTS] {proc_name | func_name}; The following example shows you a syntax of Dropping procedure and function if it exists : Examples : mysql> DROP Query OK, 0 mysql> DROP Query OK, 0

FUNCTION IF EXISTS func; rows affected (0.30 sec) PROCEDURE IF EXISTS Sproc; rows affected (0.00 sec)

But when you want to drop the procedure and function that does not exists it shows you only a warning. Examples : mysql> DROP Query OK, 0 (0.02 sec) mysql> DROP Sproc; Query OK, 0 (0.00 sec)

FUNCTION IF EXISTS func; rows affected, 1 warning PROCEDURE IF EXISTS rows affected, 1 warning

CALL Statement Syntax The CALL statement is used to call a procedure, which has been defined previously. CALL can return the values to its caller through its parameters that are declared as OUT or INOUT parameters. This statement is also used to returns the number of rows affected that a client program can obtain at the SQL level by calling the ROW_COUNT(). The general syntax of CALL Statement is : CALL p_name([parameter[,...]]) The following example shows you the use of CALL statement. Example : mysql> delimiter // mysql> CREATE PROCEDURE Sp1(OUT p VARCHAR(20),OUT p1 VARCHAR(20),IN p2 INT) -> SELECT Ename,City INTO p,p1 FROM Emp WHERE Eid=p2; -> // Query OK, 0 rows affected (0.02 sec) mysql> delimiter ; mysql> CALL Sp1(@Name,@City,1); Query OK, 0 rows affected (0.00 sec) mysql> SELECT @Name,@City; +-------+-------+ | @Name | @City | +-------+-------+ | Rahul | Delhi | +-------+-------+ 1 row in set (0.00 sec)

BEGIN.....END Compound Statement Syntax Stored Procedure or Functions can contain multiple statement by using the BEGIN…..END compound statement. The general syntax of BEGIN....END compound statement is : BEGIN [statement_list] END

statement_list means a list of one or more statements but each statements must be terminated by a semicolon. statement_list is optional means compound statement can be empty. In the following example firstly we are inserting the record and then we are selecting the record. Example : mysql> SELECT * FROM Emp; +-----+---------+----------+------------------+--------+-------+ | Eid | Ename | City | Designation | Salary | Perks | +-----+---------+----------+------------------+--------+-------+ | 1 | Rahul | Delhi | Manager | 10300 | 853 | | 2 | Gaurav | Mumbai | Assistant Manager | 10300 | 853 | | 3 | Chandan | Banglore | Team Leader | 15450 | 999 | | 5 | Tapan | Pune | Developer | 20600 | 1111 | | 6 | Amar | Chennai | Developer | 16000 | 1124 | | 7 | Santosh | Delhi | Designer | 10000 | 865 | +-----+---------+----------+------------------+--------+-------+ 6 rows in set (0.00 sec) mysql> delimiter // mysql> CREATE PROCEDURE Proc(OUT p VARCHAR(20), OUT p1 VARCHAR(20),IN p2 INT) -> BEGIN -> INSERT INTO Emp VALUES(p2,'Suman','Pune','Web Designer',20000,965); -> SELECT Ename,City INTO p,p1 FROM Emp WHERE Eid=p2; -> END -> // Query OK, 0 rows affected (0.01 sec) mysql> delimiter ; mysql> CALL Proc(@Name,@City,8); Query OK, 0 rows affected (0.03 sec) mysql> SELECT @Name,@City; +-------+-------+ | @Name | @City | +-------+-------+ | Suman | Pune | +-------+-------+ 1 row in set (0.00 sec) mysql> SELECT * FROM Emp; +-----+---------+----------+------------------+--------+-------+ | Eid | Ename | City | Designation | Salary | Perks | +-----+---------+----------+------------------+--------+-------+ | 1 | Rahul | Delhi | Manager | 10300 | 853 | | 2 | Gaurav | Mumbai | Assistant Manager | 10300 | 853 |

| 3 | Chandan | Banglore | Team Leader | 15450 | 999 | | 5 | Tapan | Pune | Developer | 20600 | 1111 | | 6 | Amar | Chennai | Developer | 16000 | 1124 | | 7 | Santosh | Delhi | Designer | 10000 | 865 | | 8 | Suman | Pune | Web Designer | 20000 | 965 | +-----+---------+----------+------------------+--------+-------+ 7 rows in set (0.00 sec)

DECLARE Statement Syntax The DECLARE statement is used to specify the various items. Its allowed only inside the BEGIN….END compound statements. Declarations must follow the order like Cursor must be declare before declaring handlers and the variables and conditions must be declare before declaring either handler or cursors DECLARE Local Variable The general syntax of declaring local variable is : DECLARE var_name[,...] type [DEFAULT value] DECLARE statement is used for declaring the local variables. DEFAULT clause is used for providing a default value to the variable but if this clause is missing then the initial value is NULL. Local variable scope is limited within the BEGIN….END compound block. Variable SET Statement The general syntax of Variable SET statement is: SET var_name = expr [, var_name = expr] ... In stored procedure SET statement is extended version of general SET statement and its implements as part of the preexisting SET Syntax. It allows an extended syntax of j=a , k=b….. where different variables types (like local variables, global variables etc) can be mixed. SELECT......INTO Statement Syntax The general syntax of SELECT....INTO Statement is: SELECT column_name1,column_name2[...] INTO var_name1,var_name2[....] table_expr This SELECT statement is used to store selected columns into variables. But by this we can retrieve only single row. The number of columns and the number of variable name must be same in this statement. In the following example we are demonstrating you the use of all above three statement. Example : mysql> delimiter // mysql> CREATE PROCEDURE Sproced(OUT p VARCHAR(20),OUT p1 VARCHAR(20),IN p2 INT) -> BEGIN -> DECLARE a VARCHAR(20); -> DECLARE b INT; -> SET b=p2; -> SET a='Dev%'; -> SELECT * FROM Emp WHERE Designation LIKE a; -> SELECT Ename,City INTO p,p1 FROM Emp WHERE

Eid=b; -> END -> // Query OK, 0 rows affected (0.07 sec) mysql> delimiter ; mysql> CALL Sproced(@Name,@City,5); +-----+-------+---------+-------------+-------+-------+ | Eid | Ename | City | Designation | Salary | Perks | +-----+-------+---------+-------------+-------+-------+ | 5 | Tapan | Pune | Developer | 20600 | 1111 | | 6 | Amar | Chennai | Developer | 16000 | 1124 | +-----+-------+---------+-------------+-------+-------+ 2 rows in set (0.05 sec) Query OK, 0 rows affected (0.10 sec) mysql> SELECT @Name,@City; +-------+-------+ | @Name | @City | +-------+-------+ | Tapan | Pune | +-------+-------+ 1 row in set (0.01 sec)

Conditions and Handlers Some conditions needs specific handling and these conditions can be related to errors or may be general flow control inside a routine. Handlers are the methods of handling conditions that need to be dealt with. Before describing conditions and handlers lets try to produce some errors. Example : mysql> INSERT INTO Emp VALUES(1,'AAA','Delhi','Manager',20000,583); ERROR 1062 (23000): Duplicate entry '1' for key 1

In the above example MySQL produced an error, ERROR 1062. And the number between the brackets (23000) is the SQLSTATE that can be the same for a number of errors. Another example : mysql> INSERT INTO Emp VALUES(11,NULL,'Delhi','Manager',20000,583); ERROR 1048 (23000): Column 'Ename' cannot be null

But in this example we get a different error number 1048 but the same SQLSTATE. If these errors occur in our functions and procedures then they will terminate out programs. To deal with these conditions we have to create a handler. The general syntax of Handler is as follows: DECLARE handler_type HANDLER FOR condition_value[,...] statement Firstly we have to use DECLARE for creating a handler, handler_type can be of the following like CONTINUE, EXIT or UNDO. If we are using CONTINUE the the program will carry on the process after the handler has been called. When we are using EXIT then the program will end immediately. The UNDO is used on transactional tables to rollback work carried out up to that point. HANDLER FOR tells the compiler, we are declaring a handler. Condition_value is used so that the handler fires when a define conditions met. The statement is section of code that we want to execute when the handler is fired.

Now we are creating a simple procedure and in this we are trying to deal with duplicate entry. In this procedure we are not handling the error. Example : mysql> delimiter // mysql> CREATE PROCEDURE hproc(OUT p VARCHAR(30)) -> BEGIN -> INSERT INTO Emp VALUES(1,'aaa','Delhi','Manager',20000,535); -> SET p='Can not Insert'; -> END -> // Query OK, 0 rows affected (0.19 sec) mysql> delimiter ; mysql> CALL hproc(@b); ERROR 1062 (23000): Duplicate entry '1' for key 1 mysql> SELECT @b; +------+ | @b | +------+ | | +------+ 1 row in set (0.02 sec)

In the above example we got the error message but our parameter is empty because error stopped the procedure. But if we wouldn’t want that then we must use handler. In the following example lets include a handler to the procedure. Example : mysql> delimiter // mysql> CREATE PROCEDURE hproc(OUT p VARCHAR(35)) -> BEGIN -> DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @b=' With Errors'; -> INSERT INTO Emp VALUES(1,'AAA','Delhi','Manager',20000,698); -> SET p=CONCAT('Can not Insert ',@b); -> END -> // Query OK, 0 rows affected (0.00 sec) mysql> delimiter ; mysql> CALL hproc(@c); Query OK, 0 rows affected (0.00 sec) mysql> SELECT @c; +---------------------------+ | @c | +---------------------------+ | Can not Insert With Errors| +---------------------------+ 1 row in set (0.00 sec)

Now in this example we didn’t get the error message and our parameter also passed out the value because when the error occurred then handler deal with the problem and continue the procedure processing. In the above example we are using a handler to deal with SQLSTATE but the handler can deal with a set of different errors. Now in the following example we are taking the different error numbers but they had the same SQLSTATE that situation we looked at earlier. Example : mysql> delimiter // mysql> CREATE PROCEDURE hproc(OUT p VARCHAR(35)) -> BEGIN

-> DECLARE CONTINUE HANDLER FOR 1062 SET @b=' With Error 1062'; -> DECLARE CONTINUE HANDLER FOR 1048 SET @b=' With Error 1048'; -> INSERT INTO Emp VALUES(1,'AAA','Delhi','Manager',20000,698); -> SET p=CONCAT('Can not Insert',@b); -> END -> // Query OK, 0 rows affected (0.00 sec) mysql> delimiter ; mysql> CALL hproc(@c); Query OK, 0 rows affected (0.00 sec) mysql> SELECT @c; +--------------------------------+ | @c | +--------------------------------+ | Can not Insert With Error 1062 | +--------------------------------+ 1 row in set (0.01 sec) mysql> DROP PROCEDURE hproc; Query OK, 0 rows affected (0.09 sec) mysql> delimiter // mysql> CREATE PROCEDURE hproc(OUT p VARCHAR(35)) -> BEGIN -> DECLARE CONTINUE HANDLER FOR 1062 SET @b=' With Error 1062'; -> DECLARE CONTINUE HANDLER FOR 1048 SET @b=' With Error 1048'; -> INSERT INTO Emp VALUES(11,NULL,'Delhi','Manager',20000,698); -> SET p=CONCAT('Can not Insert',@b); -> END -> // Query OK, 0 rows affected (0.09 sec) mysql> delimiter ; mysql> CALL hproc(@c); Query OK, 0 rows affected (0.04 sec) mysql> SELECT @c; +--------------------------------+ | @c | +--------------------------------+ | Can not Insert With Error 1048 | +--------------------------------+ 1 row in set (0.03 sec)

In the above section we have seen how we can handle various conditions. Additionally MySQL allows us to define our own named conditions. But these conditions only be linked to SQLSTATE values or mysql_error_code. Syntax for creating a conditions is as follows : DECLARE condition_name CONDITION FOR condition_value condition_value can be the SQLSTATE [value] or mysql_error_code. In the following example we are describing how we can create a condition and how we can use it within a handler. Example : mysql> delimiter // mysql> CREATE PROCEDURE condproc(OUT p VARCHAR(35)) -> BEGIN -> DECLARE duplicate CONDITION FOR SQLSTATE '23000';

-> DECLARE CONTINUE HANDLER FOR duplicate SET @b=' With Duplicate Error'; -> INSERT INTO Emp VALUES(1,'AAA','Delhi','Manager',20000,568); -> SET p=CONCAT('Can not Insert',@b); -> END -> // Query OK, 0 rows affected (0.12 sec) mysql> CALL condproc(@c); -> // Query OK, 0 rows affected (0.14 sec) mysql> delimiter ; mysql> CALL condproc(@c); Query OK, 0 rows affected (0.00 sec) mysql> SELECT @c; +-------------------------------------+ | @c | +-------------------------------------+ | Can not Insert With Duplicate Error | +-------------------------------------+ 1 row in set (0.00 sec)

In the following example we are using error code. Example : mysql> delimiter // mysql> CREATE PROCEDURE condproc(OUT p VARCHAR(35)) -> BEGIN -> DECLARE not_null CONDITION FOR 1048; -> DECLARE CONTINUE HANDLER FOR not_null SET @b=' With Not Null Error'; -> INSERT INTO Emp VALUES(11,NULL,'Delhi','Manager',20000,698); -> SET p=CONCAT('Can not Insert',@b); -> END -> // Query OK, 0 rows affected (0.00 sec) mysql> delimiter ; mysql> CALL condproc(@c); Query OK, 0 rows affected (0.00 sec) mysql> SELECT @c; +------------------------------------+ | @c | +------------------------------------+ | Can not Insert With Not Null Error | +------------------------------------+ 1 row in set (0.00 sec)

VIEW is a virtual table, which acts like a table but actually it contains no data. That is based on the result set of a SELECT statement. A VIEW consists rows and columns from one or more than one tables. A VIEW is a query that’s stored as an object. A VIEW is nothing more than a way to select a subset of table’s columns. When you defined a view then you can reference it like any other table in a database. A VIEW provides as a security mechanism also. VIEWS ensures that users are able to modify and retrieve only that data which seen by them. By using Views you can ensure about the security of data by restricting access to the following data: •

Specific columns of the tables.

•

Specific rows of the tables.

•

Specific rows and columns of the tables.

•

Subsets of another view or a subset of views and tables

•

Rows fetched by using joins.

•

Statistical summary of data in a given tables.

CREATE VIEW Statement CREATE VIEW Statement is used to create a new database view. The general syntax of CREATE VIEW Statement is: CREATE VIEW view_name [(column_list)] [WITH ENCRYPTION] AS select_statement [WITH CHECK OPTION] View_name specifies the name for the new view. column_list specifies the name of the columns to be used in view. column_list must have the same number of columns that specified in select_statement. If column_list option is not available then view is created with the same columns that specified in select_statement. WITH ENCRYPTION option encrypts the text to the view in the syscomments table. AS option specifies the action that is performed by the view. select_statement is used to specify the SELECT statement that defines a view. The optional WITH CHECK OPTION clause applies to the data modification statement like INSERT and UPDATE statements to fulfill the criteria given in the select_statement defining the view. This option also ensures that the data can visible after the modifications are made permanent. Some restrictions imposed on views are given below : •

A view can be created only in the current database.

•

The view name must follow the rules for identifiers and

•

The view name must not be the same as that of the base table

•

A view can be created only that time if there is a SELECT permission on its base table.

•

A SELECT INTO statement cannot be used in view declaration statement.

•

A trigger or an index cannot be defined on a view.

•

The CREATE VIEW statement cannot be combined with other SQL statements in a single batch.

Example : In the following example we have two table Client and Products. And if you want to see only those client records that are active in Products table also means right now they are supplying us the products. For this we are creating the view by the name of Supp_Client. mysql> SELECT * FROM Client; +------+--------------+----------+ | C_ID | Name | City | +------+--------------+----------+ | 1 | A K Ltd | Delhi | | 2 | V K Associate | Mumbai | | 3 | R K India | Banglore |

| 4 | R S P Ltd | Kolkata | | 5 | A T Ltd | Delhi | | 6 | D T Info | Delhi | +------+--------------+----------+ 6 rows in set (0.00 sec) mysql> SELECT * FROM Products; +---------+------------+------+ | Prod_ID | Prod_Detail | C_ID | +---------+------------+------+ | 111 | Monitor | 1 | | 112 | Processor | 2 | | 113 | Keyboard | 2 | | 114 | Mouse | 3 | | 115 | CPU | 5 | +---------+------------+------+ 5 rows in set (0.00 sec)

Example : Create View Statement mysql> CREATE VIEW Supp_Client AS -> SELECT * FROM Client -> WHERE C_ID IN ( -> SELECT C_ID FROM Products) -> WITH CHECK OPTION; Query OK, 0 rows affected (0.05 sec) mysql> SELECT * FROM Supp_Client; +------+--------------+----------+ | C_ID | Name | City | +------+--------------+----------+ | 1 | A K Ltd | Delhi | | 2 | V K Associate | Mumbai | | 3 | R K India | Banglore | | 5 | A T Ltd | Delhi | +------+--------------+----------+ 4 rows in set (0.03 sec)

In the following example we include the WHERE clause with the select statement of view. Then MySQL adds this condition to the VIEW definition when executing the statement for further restricting the result. Example : mysql> SELECT * FROM Supp_Client WHERE City='Delhi'; +------+---------+-------+ | C_ID | Name | City | +------+---------+-------+ | 1 | A K Ltd | Delhi | | 5 | A T Ltd | Delhi | +------+---------+-------+ 2 rows in set (0.04 sec)

ALTER VIEW Statement By the ALTER VIEW Statement we can change the definition of a view. This statement is useful to modify a view without dropping it. ALTER VIEW statement syntax is similar to CREATE VIEW Statement and effect is same as the CREATE OR REPLACE VIEW. The general syntax of ALTER VIEW Statement is : ALTER VIEW view_name [(column_list)] [WITH ENCRYPTION] AS select_statement [WITH CHECK OPTION] In the following example we are altering the view definition that we have created above. In this we add one more column by the name of Prod_Detail of Products table. Example of Altering the View Statement : mysql> ALTER VIEW Supp_Client AS -> SELECT Client.C_ID, Client.Name, Client.City, -> Products.Prod_Detail from Client, Products -> WHERE Client.C_ID=Products.C_ID; Query OK, 0 rows affected (0.01 sec) mysql> SELECT * FROM Supp_Client; +------+---------------+---------+-------------+ | C_ID | Name | City | Prod_Detail | +------+---------------+---------+-------------+ | 1 | A K Ltd | Delhi | Monitor | | 2 | V K Associate | Mumbai | Processor | | 2 | V K Associate | Mumbai | Keyboard | | 3 | R K India | Banglore | Mouse | | 5 | A T Ltd | Delhi | CPU | +------+---------------+---------+-------------+ 5 rows in set (0.02 sec)

DROP VIEW Statement For dropping a view you can use the DROP VIEW Statement. When view is dropped but it has no effect on the underlying tables. After dropping a view if you issue any query that reference a dropped view then you get an error message. But dropping a table that reference any view does not drop the view automatically you have to dropt the view explicitly. The

general syntax of DROP VIEW Statement is : DROP VIEW view_name; In the following example we are dropping the view that we have created above. Example of Dropping the View Statement : mysql> DROP VIEW Supp_Client; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM Supp_Client; ERROR 1146 (42S02): Table 'employee.supp_client' doesn't exist

Sometimes you required the data from more than one table. When you select the data from more than one table this is known as Joining. A join is a SQL query that is used to select the data from more than one table or views. When you define multiple tables or views in the FROM clause of a query the MySQL performs a join that linking the rows from multiple tables together. Types of Joins : •

INNER Joins

•

OUTER Joins

•

SELF Joins

We are going to describe you the Join with the help of following two tables : mysql> SELECT * FROM Client; +------+--------------+----------+ | C_ID | Name | City | +------+--------------+----------+ | 1 | A K Ltd | Delhi | | 2 | V K Associate | Mumbai | | 3 | R K India | Banglore | | 4 | R S P Ltd | Kolkata | +------+--------------+----------+ 4 rows in set (0.00 sec) mysql> SELECT * FROM Products; +---------+------------+------+ | Prod_ID | Prod_Detail | C_ID | +---------+------------+------+ | 111 | Monitor | 1 | | 112 | Processor | 2 | | 113 | Keyboard | 2 |

| 114 | Mouse | 3 | | 115 | CPU | 5 | +---------+------------+------+ 5 rows in set (0.00 sec)

INNER Joins The INNER join is considered as the default Join type. Inner join returns the column values from one row of a table combined with the column values from one row of another table that satisfy the search condition for the join. The general syntax of INNER Join is : SELECT , FROM INNER JOIN ON <join_conditions> The following example takes all the records from table Client and finds the matching records in table Product. But if no match is found then the record from table Client is not included in the results. But if multiple results are found in table Product with the given condition then one row will be return for each. Example : mysql> SELECT * FROM Client -> INNER JOIN Products -> ON Client.C_ID=Products.C_ID; +------+---------------+---------+---------+-------------+------+ | C_ID | Name | City | Prod_ID | Prod_Detail | C_ID | +------+---------------+---------+---------+-------------+------+ | 1 | A K Ltd | Delhi | 111 | Monitor | 1 | | 2 | V K Associate | Mumbai | 112 | Processor | 2 | | 2 | V K Associate | Mumbai | 113 | Keyboard | 2 | | 3 | R K India | Banglore | 114 | Mouse | 3 | +------+---------------+---------+---------+-------------+------+ 4 rows in set (0.04 sec)

OUTER Joins Sometimes when we are performing a Join between the two tables, we need all the records from one table even there is no corresponding record in other table. We can do this with the help of OUTER Join. In other words an OUTER Join returns the all rows that returned by an INNER Join plus all the rows from one table that did not match any row from the other table. Outer Join are divided in two types : LEFT OUTER Join, RIGHT OUTER Join LEFT OUTER Join LEFT OUTER Join is used to return all the rows that returned by an INNER Join plus all the rows from first table that did not match with any row from the second table but with the NULL values for each column from second table. The general syntax of LEFT OUTER Join is : SELECT , FROM LEFT OUTER JOIN ON <join_conditions> In the following example we are selected every row from the Client table which don’t have a match in the Products Table. Example :

mysql> SELECT * FROM CLIENT -> LEFT OUTER JOIN Products -> ON Client.C_ID=Products.C_ID; +------+---------------+---------+---------+-------------+------+ | C_ID | Name | City | Prod_ID | Prod_Detail | C_ID | +------+---------------+---------+---------+-------------+------+ | 1 | A K Ltd | Delhi | 111 | Monitor | 1 | | 2 | V K Associate | Mumbai | 112 | Processor | 2 | | 2 | V K Associate | Mumbai | 113 | Keyboard | 2 | | 3 | R K India | Banglore | 114 | Mouse | 3 | | 4 | R S P Ltd | Kolkata | NULL | | NULL | +------+---------------+---------+---------+-------------+------+ 5 rows in set (0.00 sec)

In the following example we are using the ORDER BY Clause with the LEFT OUTER Join. mysql> SELECT * FROM Client -> LEFT OUTER JOIN Products -> ON Client.C_ID=Products.C_ID -> ORDER BY Client.City; +------+---------------+---------+---------+-------------+------+ | C_ID | Name | City | | Prod_Detail | C_ID | +------+---------------+---------+---------+-------------+------+ | 3 | R K India | Banglore | | Mouse | 3 | | 1 | A K Ltd | Delhi | | Monitor | 1 | | 4 | R S P Ltd | Kolkata | | | NULL | | 2 | V K Associate | Mumbai | | Keyboard | 2 | | 2 | V K Associate | Mumbai | | Processor | 2 | +------+---------------+---------+---------+-------------+------+ 5 rows in set (0.08 sec)

Prod_ID

114 111 NULL 113 112

In the result of LEFT OUTER Join " R S P Ltd " is included even though it has no rows in the Products table. RIGHT OUTER Join RIGHT OUTER Join is much same as the LEFT OUTER JOIN. But RIGHT OUTER Join is used to return all the rows that returned by an INNER Join plus all the rows from second table that did not match with any row from the first table but with the NULL values for each column from first table. The general syntax of RIGHT OUTER Join is : SELECT , FROM RIGHT OUTER JOIN ON <join_conditions>

In the following example we are selected every row from the Products table which don’t have a match in the Client Table. Example : mysql> SELECT * FROM Client -> RIGHT OUTER JOIN Products -> ON Client.C_ID=Products.C_ID; +------+---------------+----------+--------+-------------+------+ | C_ID | Name | City | Prod_ID | Prod_Detail | C_ID | +------+---------------+----------+--------+-------------+------+ | 1 | A K Ltd | Delhi | 111 | Monitor | 1 | | 2 | V K Associate | Mumbai | 112 | Processor | 2 | | 2 | V K Associate | Mumbai | 113 | Keyboard | 2 | | 3 | R K India | Banglore | 114 | Mouse | 3 | | NULL | | | 115 | CPU | 5 | +------+---------------+----------+--------+-------------+------+ 5 rows in set (0.03 sec)

SELF Join SELF Join means a table can be joined with itself. SELF Join is useful when we want to compare values in a column to other values in the same column. For creating a SELF Join we have to list a table twice in the FROM clause and assign it a different alias each time. For referring the table we have to use this aliases. The following example provide you the list of those Clients that belongs to same city of C_ID=1. mysql> SELECT b.C_ID,b.Name,b.City FROM Client a, Client b -> WHERE a.City=b.City AND a.C_ID=1; +------+----------+-------+ | C_ID | Name | City | +------+----------+-------+ | 1 | A K Ltd | Delhi | | 5 | A T Ltd | Delhi | | 6 | D T Info | Delhi | +------+----------+-------+ 3 rows in set (0.00 sec)

we can write this SELF JOIN Query in Subquery like this also : mysql> SELECT * FROM Client -> WHERE City=( -> SELECT City FROM Client -> WHERE C_ID=1); +------+----------+-------+ | C_ID | Name | City | +------+----------+-------+ | 1 | A K Ltd | Delhi | | 5 | A T Ltd | Delhi | | 6 | D T Info | Delhi | +------+----------+-------+ 3 rows in set (0.03 sec)

Cursors are used when the SQL Select statement is expected to return more than one row. Cursors are supported inside procedures and functions. Cursors must be declared and its definition contains the query. The cursor must be defined in the DECLARE section of the program. A cursor must be opened before processing and close after processing. Syntax to declare the cursor : DECLARE <cursor_name> CURSOR FOR <select_statement> Multiple cursors can be declared in the procedures and functions but each cursor must have a unique name. And in defining the cursor the select_statement cannot have INTO clause. Syntax to open the cursor : OPEN <cursor_name> By this statement we can open the previously declared cursor. Syntax to store data in the cursor : FETCH <cursor_name> INTO ,……. The above statement is used to fetch the next row if a row exists by using the defined open cursor. Syntax to close the cursor : CLOSE <cursor_name> By this statement we can close the previously opened cursor. If it is not closed explicitly then a cursor is closed at the end of compound statement in which that was declared. In the following example firstly we are declaring the Cursor and selecting the all record from Emp table. And after opened the cursor we fetch the record one by one from cursor. And then insert these record in Emp2 table. Example : mysql> delimiter // mysql> CREATE PROCEDURE DemoCurs1() -> BEGIN -> DECLARE d INT DEFAULT 0; -> DECLARE id,sal,perk INT; -> DECLARE name,city,desig VARCHAR(20); -> DECLARE cur CURSOR FOR SELECT * FROM Emp; -> DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET d=1; -> DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET d=1; -> OPEN cur; -> lbl: LOOP -> IF d=1 THEN -> LEAVE lbl; -> END IF; -> IF NOT d=1 THEN -> FETCH cur INTO id,name,city,desig,sal,perk; -> INSERT INTO Emp2 VALUES(id,name,city,desig,sal,perk); -> END IF; -> END LOOP; -> CLOSE cur; -> END;

-> // Query OK, 0 rows affected (0.00 sec) mysql> delimiter ; mysql> CALL DemoCurs1(); Query OK, 1 row affected (0.12 sec) mysql> SELECT * FROM Emp2; +-----+---------+----------+------------------+--------+-------+ | Eid | Ename | City | Designation | Salary | Perks | +-----+---------+----------+------------------+--------+-------+ | 1 | Rahul | Delhi | Manager | 10300 | 853 | | 2 | Gaurav | Mumbai | Assistant Manager | 10300 | 853 | | 3 | Chandan | Banglore | Team Leader | 15450 | 999 | | 5 | Tapan | Pune | Developer | 20600 | 1111 | | 6 | Amar | Chennai | Developer | 16000 | 1124 | | 7 | Santosh | Delhi | Designer | 10000 | 865 | | 8 | Suman | Pune | Web Designer | 20000 | 658 | +-----+---------+----------+------------------+--------+-------+ 7 rows in set (0.00 sec)

Triggers:

A Trigger is a named database object which defines some action that the database should take when some databases related event occurs. Triggers are executed when you issues a data manipulation command like INSERT, DELETE, UPDATE on a table for which the trigger has been created. They are automatically executed and also transparent to the user. But for creating the trigger the user must have the CREATE TRIGGER privilege. In this section we will describe you about the syntax to create and drop the triggers and describe you some examples of how to use them. CREATE TRIGGER The general syntax of CREATE TRIGGER is : CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_statement By using above statement we can create the new trigger. The trigger can associate only with the table name and that must be refer to a permanent table. Trigger_time means trigger action time. It can be BEFORE or AFTER. It is used to define that the trigger fires before or after the statement that executed it. Trigger_event specifies the statement that executes the trigger. The trigger_event can be any of the DML Statement : INSERT, UPDATE, DELETE. We can not have the two trigger for a given table, which have the same trigger action time and event. For Instance : we cannot have two BEFORE INSERT triggers for same table. But we can have a BEFORE INSERT and BEFORE UPDATE trigger for a same table.

Trigger_statement have the statement that executes when the trigger fires but if you want to execute multiple statement the you have to use the BEGIN…END compound statement. We can refer the columns of the table that associated with trigger by using the OLD and NEW keyword. OLD.column_name is used to refer the column of an existing row before it is deleted or updated and NEW.column_name is used to refer the column of a new row that is inserted or after updated existing row. In INSERT trigger we can use only NEW.column_name because there is no old row and in a DELETE trigger we can use only OLD.column_name because there is no new row. But in UPDATE trigger we can use both, OLD.column_name is used to refer the columns of a row before it is updated and NEW.Column_name is used to refer the column of the row after it is updated. In the following example we are updating the Salary column of Employee table before inserting any record in Emp table. Example : mysql> SELECT * FROM Employee; +-----+---------+----------+------------------+--------+-------+ | Eid | Ename | City | Designation | Salary | Perks | +-----+---------+----------+------------------+--------+-------+ | 1 | Rahul | Delhi | Manager | 10300 | 853 | | 2 | Gaurav | Mumbai | Assistant Manager | 10300 | 853 | | 3 | Chandan | Banglore | Team Leader | 15450 | 999 | | 5 | Tapan | Pune | Developer | 20600 | 1111 | | 6 | Amar | Chennai | Developer | 16000 | 1124 | | 7 | Santosh | Delhi | Designer | 10000 | 865 | | 8 | Suman | Pune | Web Designer | 20000 | 658 | +-----+---------+----------+------------------+--------+-------+ 7 rows in set (0.00 sec) mysql> delimiter // mysql> CREATE TRIGGER ins_trig BEFORE INSERT ON Emp -> FOR EACH ROW -> BEGIN -> UPDATE Employee SET Salary=Salary-300 WHERE Perks>500; -> END; -> // Query OK, 0 rows affected (0.01 sec) mysql> delimiter ; mysql> INSERT INTO Emp VALUES(9,'Rajesh','Delhi','Developer',15000,658); Query OK, 1 row affected (0.05 sec) mysql> SELECT * FROM Employee; +-----+---------+----------+------------------+--------+-------+ | Eid | Ename | City | Designation | Salary | Perks | +-----+---------+----------+-------------------

+--------+-------+ | 1 | Rahul | Delhi | Manager | 10000 | 853 | | 2 | Gaurav | Mumbai | Assistant Manager | 10000 | 853 | | 3 | Chandan | Banglore | Team Leader | 15150 | 999 | | 5 | Tapan | Pune | Developer | 20300 | 1111 | | 6 | Amar | Chennai | Developer | 15700 | 1124 | | 7 | Santosh | Delhi | Designer | 9700 | 865 | | 8 | Suman | Pune | Web Designer | 19700 | 658 | +-----+---------+----------+------------------+--------+-------+ 7 rows in set (0.00 sec)

In the following example we are modifying the salary of Employee table before updating the record of the same table. Example : mysql> delimiter // mysql> CREATE TRIGGER updtrigger BEFORE UPDATE ON Employee -> FOR EACH ROW -> BEGIN -> IF NEW.Salary<=500 THEN -> SET NEW.Salary=10000; -> ELSEIF NEW.Salary>500 THEN -> SET NEW.Salary=15000; -> END IF; -> END -> // Query OK, 0 rows affected (0.01 sec) mysql> delimiter ; mysql> UPDATE Employee -> SET Salary=500; Query OK, 5 rows affected (0.04 sec) Rows matched: 7 Changed: 5 Warnings: 0 mysql> SELECT * FROM Employee; +-----+---------+----------+------------------+--------+-------+ | Eid | Ename | City | Designation | Salary | Perks | +-----+---------+----------+------------------+--------+-------+ | 1 | Rahul | Delhi | Manager | 10000 | 853 | | 2 | Gaurav | Mumbai | Assistant Manager | 10000 | 853 | | 3 | Chandan | Banglore | Team Leader | 10000 | 999 | | 5 | Tapan | Pune | Developer | 10000 | 1111 | | 6 | Amar | Chennai | Developer | 10000 | 1124 | | 7 | Santosh | Delhi | Designer | 10000 | 865 | | 8 | Suman | Pune | Web Designer | 10000 | 658 | +-----+---------+----------+-------------------

+--------+-------+ 7 rows in set (0.00 sec) mysql> UPDATE Employee -> SET Salary=1500; Query OK, 7 rows affected (0.03 sec) Rows matched: 7 Changed: 7 Warnings: 0 mysql> SELECT * FROM Employee; +-----+---------+----------+------------------+--------+-------+ | Eid | Ename | City | Designation | Salary | Perks | +-----+---------+----------+------------------+--------+-------+ | 1 | Rahul | Delhi | Manager | 15000 | 853 | | 2 | Gaurav | Mumbai | Assistant Manager | 15000 | 853 | | 3 | Chandan | Banglore | Team Leader | 15000 | 999 | | 5 | Tapan | Pune | Developer | 15000 | 1111 | | 6 | Amar | Chennai | Developer | 15000 | 1124 | | 7 | Santosh | Delhi | Designer | 15000 | 865 | | 8 | Suman | Pune | Web Designer | 15000 | 658 | +-----+---------+----------+------------------+--------+-------+ 7 rows in set (0.01 sec)

DROP TRIGGER The general syntax of DROP TRIGGER is : DROP TRIGGER trigger_name This statement is used to drop a trigger. Example of Dropping the Trigger : mysql> DROP TRIGGER updtrigger; Query OK, 0 rows affected (0.02 sec)

LOCKING:

MySQL can manage the contention for table contents by using Locking : •

Internal Locking can be performed in the MySQL server itself for managing the contention for table content by multiple threads. This locking is known as internal because this locking is performed entirely by the server and it does not involve any other programs.

•

External locking is performed when server and other programs lock the table files for coordinating among themselves which program may access the tables at which time.

Internal Locking Methods

In this section we are describing you about internal locking, Internal Locking is performed in the MySQL server itself for managing the contention for table content by multiple threads. This locking is known as internal because this locking is performed entirely by the server and it does not involve any other programs. Table – level locking is used by MySQL to MyISAM and MEMORY, row – level locking for InnoDB tables and page – level locking for BDB tables. In some cases you can guess which locking is best for an application but generally is not easy to say that a given lock is better than another lock type. Actually everything is depend on the application because different parts of application can required different lock types. For deciding this, if you are using storage engine with row – level locking then you must look at what application does and what is the combination of update and select statements it uses. MySQL uses table level locking for storage engines. In MySQL table locking is deadlock free. Deadlock prevention can be managed by requesting all required lock at once at the beginning of query and the lock the tables in the same order. MySQL grants the table WRITE locks are given below: •

When locks are not available on the table then put a write lock on the table.

•

Else in the write lock queue put a lock request.

MySQL grants the table READ locks are given below: •

When write locks are not available on the table then put a read lock on the table.

•

Else in the read lock queue put a lock request

After releasing the lock, lock is available for the threads in the write lock queue and then for the threads in read lock queue. For analyzing the contention of table lock we check the Table_locks_immediate and Table_locks_waited status variables : mysql> show status like 'Table%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | Table_locks_immediate | 12 | | Table_locks_waited | 0 | +-----------------------+-------+ 2 rows in set (0.00 sec)

InnoDB and BDB type tables uses row locks and page locks respectively. All locking are deadlock free except of these two because they acquire the lock between the processing of SQL statements rather than the beginning of transaction. Row level locking advantages are given below: •

In row level locking we can lock a single row for a long time.

•

We can do fewer changes to rollback also.

•

And fewer lock conflicts when accessing different rows in many threads.

Row level locking disadvantages are given below: •

This type of locking needs more memory than table or page level locking.

•

When we used this type of locking on a large part of the table then its slower than table or page level locking because we have to acquire many more locks.

Table locks are more useful rather than page or row level locks in the following cases: •

When most of the statements for the tables are reads.

•

When statements to a table are a combination of reads and writes and writes are deletes or updates to a single row, which can be fetched with one key read.

•

When SELECT statement mixed with INSERT and few DELETE or UPDATE statements

In large tables table locks is much useful than row locks but there are some pitfalls: •

If we are using table locking then many threads can read from a table at the same time but if any threads wish to write in a table then it have to get the exclusive access. And during the update if other threads wish to access this table then they have to wait until the update is complete.

•

Table updates have the higher priority than the table retrievals.

•

Table locks faces some problem in some cases such as when any thread is in waiting condition because the disk is full and its needs some free space then it can be proceed. In this situation, all threads, which want to access the problem table, are also in waiting condition until they get the free space.

Disadvantages of Table locking is given below: •

A client fires a SELECT Statement, which takes a long time to run and at the same time another client fires an UPDATE statement on the same table. Then this client has to wait until the SELECT statement is finished.

•

If another client fires another SELECT statement on the same table then this SELECT has to wait until the UPDATE statement is finished because UPDATE has higher priority than SELECT.

In the following section we are describing you some ways for avoiding the contention caused by table locking: •

Start mysql with low priority updates. This statement is used to give, all statement which update the table , lower priority than SELECT statement.

•

By using the SET LOW_PRIORITY_UPDATES=1 statement, we can define the all updates that fired in a defined connection can be done with low priority.

•

We can give a specific DML Statements (INSERT, DELETE, UPDATE) lower priority with LOW_PRIORITY attribute. And we can also give the higher priority to SELECT statement with the HIGH_PRIORITY attribute.

•

If you are facing problem with combination of INSERT and SELECT statement then you can switch to MyISAM tables because that supports the concurrent SELECT and INSERT statements.

•

If you are using the combination of INSERT and DELETES statement on the same table then INSERT DELAYED can be helpful to you.

•

If you are facing problem with combination of SELECT and DELETE statement then LIMIT option to DELETE can be help to you.

•

Using SQL_BUFFER_RESULT with SELECT statements may be helpful to make the shorter duration of table locks

External Locking External locking is the used of filesystem locking for managing the contention to databases tables by multiple processes. External locking is performed when server and other programs lock the table files for coordinating among themselves which program may access the tables at which time.

This type of locking affects the performance of the server because many times before accessing the tables server must wait for other processes. There is no need of External locking if you are running a single server for accessing a given database directory and other programs are not available like myisamchk require to modify the tables while the server is running. External locking is also not required when you only read the tables with other programs. For mysqld, the system variable skip_external_locking value is used to control the external locking. If this system variable is enabled then external locking is disabled and vice versa. We can also control the use of external locking by –external-locking or –skip-externallocking option at server startup.