What is a Stored Procedure?

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

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 ;

 **********************************************

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 ;