Scribd
Upload
43 views4 pages

EX NO: 5 (A) Creation of Simple PL (Procedural Language) /SQL Program

Stored procedures allow for easier and more flexible execution of SQL statements compared to issuing individual statements. Stored procedures can call other stored procedures and be used across different client applications and platforms for common database operations. Using stored procedures improves performance by reducing the information needing to be sent between the server and client as more work is done on the server side. Exception handling in stored procedures allows specific conditions like errors to be dealt with using handlers that set variables when certain SQL states or error codes occur. This example shows how handlers can set a variable to indicate the type of error when an insert fails due to a duplicate key or null value violation.

Uploaded by

Chidambaram
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
43 views4 pages

EX NO: 5 (A) Creation of Simple PL (Procedural Language) /SQL Program

Stored procedures allow for easier and more flexible execution of SQL statements compared to issuing individual statements. Stored procedures can call other stored procedures and be used across different client applications and platforms for common database operations. Using stored procedures improves performance by reducing the information needing to be sent between the server and client as more work is done on the server side. Exception handling in stored procedures allows specific conditions like errors to be dealt with using handlers that set variables when certain SQL states or error codes occur. This example shows how handlers can set a variable to indicate the type of error when an insert fails due to a duplicate key or null value violation.

Uploaded by

Chidambaram
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 4

EX NO: 5 (a)

Creation of simple PL(Procedural Language)/SQL program

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


CREATE PROCEDURE proc_name ([proc_parameter[......]]) routine_body 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)

EX NO: 5 (b)

Exception Handling

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. Example 1: 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('Cannot 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 | Cannot Insert With Errors| +---------------------------+ 1 row in set (0.00 sec) | +---------------------------+

Example 2:
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('Cannot 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 | +--------------------------------+ | Cannot 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('Cannot 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 | +--------------------------------+ | Cannot Insert With Error 1048 | +--------------------------------+ 1 row in set (0.03 sec)

You might also like