What is a Stored Procedure?
A stored procedure is a named in PL/SQL block which performs one or more specific task. This is similar to a procedure in other programming languages.
We can pass parameters to procedures in three ways.
1) IN-parameters
2) OUT-parameters
3) IN OUT-parameters
1) IN-parameters
2) OUT-parameters
3) IN OUT-parameters
A procedure may or may not return any value.
***************************************************
Simple Syntax to create a procedure is:
DELIMITER $$
DROP PROCEDURE IF EXISTS `lib`.`ProNew` $$
CREATE PROCEDURE `lib`.`ProNew` ()
BEGIN
END $$
DELIMITER ;
DROP PROCEDURE IF EXISTS `lib`.`ProNew` $$
CREATE PROCEDURE `lib`.`ProNew` ()
BEGIN
END $$
DELIMITER ;
**********************************************
Standard Syntax to create a procedure is:
CREATE [OR REPLACE] PROCEDURE proc_name [list of parameters]
IS
Declaration section
BEGIN
Execution section
EXCEPTION
Exception section
END;
********************************************
Real Structure of Procedure Given Below
First create table Class_Master
DROP TABLE IF EXISTS `lib123`.`class_master`;CREATE TABLE `lib123`.`class_master` (
`Id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`ShortName` varchar(10) NOT NULL,
`Course` varchar(30) CHARACTER SET utf8 NOT NULL,
`Year` varchar(10) DEFAULT NULL,
`DIVN` varchar(10) DEFAULT NULL,
`Subject` varchar(20) CHARACTER SET utf8 NOT NULL,
`IsDeleted` bit(1) NOT NULL DEFAULT b'0',
`AddedBy` int(10) unsigned zerofill NOT NULL DEFAULT '0000000000',
`AddedDateTime` datetime DEFAULT NULL,
`UpdatedBy` int(10) unsigned zerofill NOT NULL DEFAULT '0000000000',
`UpdatedDateTime` datetime DEFAULT NULL,
PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Then Create Procedure
DELIMITER $$
DROP PROCEDURE IF EXISTS `Sp_Class_Master_SAVE` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `Sp_Class_Master_SAVE`(
out Param_return tinyint,
inout Param_Id smallint(5),
in Param_ShortName varchar(10) ,
in Param_Course varchar(30) ,
in Param_Year varchar(10) ,
in Param_DIVN varchar(10) ,
in Param_Subject varchar(20),
in Param_IsDeleted bit,
in Param_AddedBy int(10),
in Param_AddedDateTime datetime,
in Param_UpdatedBy int(10),
in Param_UpdatedDateTime datetime
)
BEGIN
if exists(select Id from class_master where Id!=Param_Id and
ShortName like Param_ShortName) then
set Param_Return=0;
else
if(Param_Id>0) then
update class_master
set Id=Param_Id
,ShortName=Param_ShortName,Course=Param_Course,
Year=Param_Year,DIVN=Param_DIVN,Subject=Param_Subject,
IsDeleted=Param_IsDeleted,AddedBy=Param_AddedBy,
AddedDateTime=Param_AddedDateTime,
UpdateBy=Param_UpdatedBy,UpdateDateTime=Param_UpdatedDateTime
where Id=Param_Id;
set Param_Return=2;
else
Insert into class_master(Id,ShortName,Course,Year,DIVN,Subject,IsDeleted,AddedBy,
AddedDateTime,UpdatedBy,UpdatedDateTime) VALUES
(Param_Id,Param_ShortName,Param_Course,Param_Year,Param_DIVN,
Param_Subject,Param_IsDeleted,
Param_AddedBy,Param_AddedDateTime,Param_UpdatedBy,Param_UpdatedDateTime);
SET Param_Return = 1;
SET Param_Id = LAST_INSERT_ID();
END IF;
END IF;
END $$
DELIMITER ;
