My contemplations and diurnal novelties are publicized here

Archive for the ‘Mysql’ Category

How to create a cursor in MySql


I have had trouble creating in Mysql cursor so i decide to post this.

Don’t use same name for cursor variable similar to Column name of the fetched recordset.

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `INVESTOR2PROFILE`(
)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE a CHAR(16);
DECLARE _PID INT;
DECLARE _AID INT;
DECLARE _CIC ,_IN ,_rn ,_ct,_AN ,_AT,_OI ,_AOD, _ATI ,_E ,_ISP varchar(45);
DECLARE cur1 CURSOR FOR SELECT CNIC,InvestorName,registrationNo,categoryType,AccountNumber,AccountTitle,OperatingInstruction,AccountOpeningDate,
ATT.AccountTypeID, Email, 'y' IsPrimary from temptableforbulkimport temp left join AccountTypes ATT 
on temp.AccountType = ATT.AccountType;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  OPEN cur1;
  
  read_loop: LOOP
    FETCH cur1 INTO _CIC ,_IN  ,_rn ,_ct,_AN ,_AT,_OI ,_AOD, _ATI ,_E ,_ISP;
    IF done THEN
      LEAVE read_loop;
    END IF;
    Select _CIC ;
    Select _IN  ;
    Select _rn; 
    Select _ct;
    Select _AN ;
    Select _AT;
    Select _OI ;
    Select _AOD;
    Select _ATI ;
    Select _E ;
    Select _ISP;
    /**********************Create Profile**********************/
    INSERT INTO profiles(CNIC,ProfileName) VALUES (_CIC,_IN);    
        Set _PID = LAST_INSERT_ID();
    /**********************Create Account**********************/
        INSERT INTO accounts(AccountNumber ,AccountTitle ,OperatingInstructions ,AccountOpeningDate , AccountTypeID) values (_AN ,_AT,_OI ,_AOD, _ATI);
        Set _AID = LAST_INSERT_ID();
    /**********************Create ProfileAccount**********************/
    select _PID;
    select _AID;
    INSERT INTO profileaccount(AccountID ,ProfileID,IsPrimary, Email) values (_AID ,_PID,_IsPrimary, _E);
    /**********************Create ProfileInvestor**********************/
    INSERT INTO ProfileInvestor(ReferenceID ,InvestorCategory,ProfileID) values (_rn ,_categoryType,_PID);    
  END LOOP;

  CLOSE cur1;  
END

Multi statement Stored Procedure in MySQL


I tried different syntax to find out multi statement sp in mysql and a co-worker help me find this.

DELIMITER $$

CREATE PROCEDURE Mubashir
(
parameter1 VARCHAR(255),
parameter2 INTEGER
)
BEGIN
insert into accounttype(accounttype) select distinct AccountType from temptableforbulkimport;

SELECT * from temptableforbulkimport;
END$$

DELIMITER ;

%d bloggers like this: