{"id":253,"date":"2018-07-13T06:51:28","date_gmt":"2018-07-13T06:51:28","guid":{"rendered":"http:\/\/itp9.epgpbooks.inflibnet.ac.in\/?post_type=chapter&p=253"},"modified":"2018-12-07T11:35:21","modified_gmt":"2018-12-07T11:35:21","slug":"stored-procedures-in-mysql","status":"publish","type":"chapter","link":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/chapter\/stored-procedures-in-mysql\/","title":{"rendered":"Stored Procedures in MYSQL"},"content":{"raw":"
\"epgp<\/a>\r\n<\/span><\/div>\r\n
\r\n\r\n \r\n\r\n \r\n\r\nIntroduction<\/strong>\r\n\r\n \r\n

\u2022 A stored procedure or a function is a named Pl\/SQL block which resides in the Database engine\u2019s tables. A stored procedure can be invoked by other procedure, triggers or by other applications like PHP, java etc.<\/p>\r\n

\u2022 It is a logically grouped set of SQL and Pl\/SQL statements which are written to perform a specific task.<\/p>\r\n

\u2022 Procedures and functions are also referred to as Database objects and such objects can be invoked or called by any Pl\/SQL block within the application.<\/p>\r\n

\u2022 The initial versions of MySQL did not have support for stored procedures, functions or triggers. It was only after version 5.0 that support for this objects was added to increase reusability.<\/p>\r\n

\u2022 It is like defining an API for our application which helps in enhancing reusability in different applications<\/p>\r\n\u2022 Before they are actually stored, MySQL engine parses and compiles the procedure.\r\n\r\n \r\n\r\nWhy Stored Procedures<\/strong>\r\n\r\n \r\n

\u2022 Stored procedures provide security as the DBA can grant permission to the applications to access the stored procedure without giving permissions on the underlying table.<\/p>\r\n

\u2022 Stored procedures enhances reusability so the developers can use once written code and multiple resources are not wasted for single task.<\/p>\r\n

\u2022 Stored procedures are invoked by some calling routine which pass only the name and parameters hence reducing database traffic , which otherwise has to send multiple sql statements<\/p>\r\n

\u2022 The caching and buffering of stored procedures in MySQL is on demand , they are compiled on demand. And after compilation MySQL puts it in cache<\/p>\r\n \r\n\r\nLimitations<\/strong>\r\n\r\n \r\n\r\n\u2022 Expert skill set required\r\n\r\n\u2022 MySQL does not provide with debugging facilities\r\n

\u2022 Memory consumption will be more if many procedures are used or many logical operations in a procedure.<\/p>\r\n\r\n<\/div>\r\n

\r\n\r\n\u00a0 <\/strong>\r\n\r\n\u00a0 Types of Parameters in Stored Procedure<\/strong>\r\n\r\n \r\n

\u2022 Procedures can also be made dynamic. The dynamism for procedures can be achieved by passing parameters before execution. The behavior of the procedure changes depending on the parameters passed.<\/p>\r\n\u2022 The different types of parameters available with stored procedure are\r\n\r\n\u2022 IN : IN is the default parameter passed to the procedure which can be changed in the procedure\r\n\r\n\u2022 OUT : No value is passed to the procedure , but the value is returned out from the procedure.\r\n\r\n\u2022 IN OUT : Here , both value can be accepted and can be returned.\r\n\r\n \r\n\r\nStructure of Stored Procedure<\/strong>\r\n\r\n \r\n\r\n\u2022 Stored procedure consists of\r\n\r\n\u2022 Declarative part\r\n

\u2022 The declarative part contains the declarations about variables, constants, cursors, exceptions etc. The scope of these declarations is local to the procedure<\/p>\r\n\u2022 Executable part\r\n\r\n\u2022 The executable part contains the actions procedure is expected to take.\r\n\r\n \r\n\r\n\"\"\r\n\r\n<\/div>\r\n

\r\n\r\n \r\n\r\nNow, let us understand different types of examples, with reference to stored procedures.\r\n\r\n \r\n\r\nAll the below code blocks are taken from MySQL editor and displayed here as it is.\r\n\r\n \r\n\r\n1.\u00a0 Simple example to fetch data from employee table\r\n\r\n \r\n\r\nDELIMITER $$\r\n\r\nCREATE DEFINER=`root`@`localhost` PROCEDURE `get_sal`()\r\n\r\nBEGIN\r\n\r\nselect name,salary from emp;\r\n\r\nEND\r\n\r\n \r\n\r\nTo run on command prompt SQL > call get_sal();\r\n\r\nThe above statement will display the name and salary from emp table.\r\n\r\n \r\n\r\n2. Example to demonstrate IN parameter\r\n\r\n \r\n\r\nDELIMITER $$\r\n\r\nCREATE DEFINER=`root`@`localhost` PROCEDURE `raise_sal`(emp_no varchar(10), amount int)\r\n\r\nBEGIN\r\n

UPDATE emp<\/p>\r\n

SET salary = salary + amount WHERE empid = emp_no;<\/p>\r\n

END<\/p>\r\n \r\n

In the above example, emp_no and amount are passed as IN parameters, and the salary is updated based on the amount provided by the user. By default, parameters are IN.<\/p>\r\n \r\n\r\nTo run on command prompt SQL > call raise_sal(1,100);\r\n\r\n \r\n\r\nThe above statement will update the salary with 100 of empid : 1 , Hence the old value was 5400 which will be updated to 5500.\r\n\r\n<\/div>\r\n \r\n\r\n3. Example to demonstrate OUT parameter<\/span>\r\n

\r\n\r\n \r\n\r\nDELIMITER $$\r\n

CREATE DEFINER=`root`@`localhost` PROCEDURE `fetch_name`(empno varchar(10), out v_name varchar(10))research<\/p>\r\nBEGIN\r\n\r\nselect name into v_name from emp where empid=empno;\r\n\r\nEND\r\n\r\n \r\n

The above example demonstrates the use of OUT parameter and IN parameter. Empno is passed as IN parameter and name is returned as OUT parameter.<\/p>\r\n \r\n\r\n4.\u00a0 Example to demonstrate IN OUT parameter\r\n\r\n \r\n\r\nDELIMITER $$\r\n\r\nCREATE DEFINER=`root`@`localhost` PROCEDURE `raise_sal1`(empno varchar(10), Inout amount int)\r\n\r\nBEGIN\r\n\r\ndeclare v_amt int; declare v_sal int;\r\n\r\nselect salary into v_sal from emp where empid = empno; set v_amt = v_sal + amount;\r\n\r\nupdate emp set salary = v_amt where empid = empno; set amount=v_amt;\r\n\r\nEND\r\n\r\n \r\n

The above example display the functionality of InOUT parameter where the variable amount is accepting the value, it is then processed and the same amount is returned back the value.<\/p>\r\n \r\n

5. Example to demonstrate creating and executing a stored procedure on SQL prompt , Example of IN OUT parameter<\/p>\r\n \r\n\r\nCreating the procedure\r\n\r\nSQL> create procedure square (INOUT P int) set P=P*P;\r\n\r\nTo assign value to the variable SQL> set @number=10;\r\n\r\nTo call the procedure\r\n\r\nSQL> call square (@number);\r\n\r\nTo display variable value<\/span>\r\n\r\nSQL> select @number ;<\/span>\r\n\r\nTo drop a procedure<\/span>\r\n\r\nSQL> drop procedure square;<\/span>\r\n\r\n<\/div>\r\n