Mikmak MySQL DML UnitBase

CRUD stored procedures voor de UnitBase tabel. De afspraak is dat de namen voor de stored procedurs beginnen met de naam van de tabel gevolgd door de naam van de CRUD handeling. De naam wordt in pascalnotatie geschreven.

Probleem

We moeten de gegevens van een basiseenheid kunnen inserten, updaten, deleten en selecteren. De selectie moet kunnen gebeuren op basis van de naam en van de code. Voor het maken van de stored procedures baseren we ons op het Mikmak logisch model.

Design

Naam	Beschrijving
UnitBaseInsert	bevat 1 OUTPUT parameter om de nieuw Id te retourneren naar het calling programma
UnitBaseUpdate	deze stored procedure updatet alle kolommen van de rij met de opgegeven Id
UnitBaseSelectOne	lees 1 rij in uit de tabel op basis van de Id, neem alle kolommen mee voor het detail venster
UnitBaseSelectAll	lees alle rijen in uit de tabel maar alleen de kolommen die we in de lijst willen laten zien
UnitBaseSelectByCode	lees 1 rij in uit de tabel op basis van de Code maar alleen de kolommen die we in de lijst willen laten zien
UnitBaseSelectByName	ees 1 rij in uit de tabel op basis van de Name maar alleen de kolommen die we in de lijst willen laten zien

Oplossing

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Wednesday 6th of January 2016 08:10:41 PM
-- DML Insert Stored Procedure for UnitBase 
-- 
USE Webwinkel;
DROP PROCEDURE IF EXISTS UnitBaseInsert;
DELIMITER //
CREATE PROCEDURE `UnitBaseInsert`
(
	IN pName NVARCHAR (255) ,
	IN pDescription NVARCHAR (1024) ,
	IN pShippingCostMultiplier FLOAT ,
	IN pCode NVARCHAR (2) ,
	OUT pId INT 
)
BEGIN
INSERT INTO `UnitBase`
	(
		`UnitBase`.`Name`,
		`UnitBase`.`Description`,
		`UnitBase`.`ShippingCostMultiplier`,
		`UnitBase`.`Code`
	)
	VALUES
	(
		pName,
		pDescription,
		pShippingCostMultiplier,
		pCode
	);
	SELECT LAST_INSERT_ID() INTO pId;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Wednesday 6th of January 2016 08:10:41 PM
-- DML Update Stored Procedure for UnitBase
-- 
USE Webwinkel;
DROP PROCEDURE IF EXISTS UnitBaseUpdate;
DELIMITER //
CREATE PROCEDURE `UnitBaseUpdate`
(
	pName NVARCHAR (255) ,
	pDescription NVARCHAR (1024) ,
	pShippingCostMultiplier FLOAT ,
	pCode NVARCHAR (2) ,
	pId INT 
)
BEGIN
UPDATE `UnitBase`
	SET
		`Name` = pName,
		`Description` = pDescription,
		`ShippingCostMultiplier` = pShippingCostMultiplier,
		`Code` = pCode
	WHERE `UnitBase`.`Id` = pId;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Wednesday 6th of January 2016 08:10:41 PM
-- DML Delete Stored Procedure for UnitBase 
-- 
USE Webwinkel;
DROP PROCEDURE IF EXISTS UnitBaseDelete;
DELIMITER //
CREATE PROCEDURE `UnitBaseDelete`
(
	 pId INT 
)
BEGIN
DELETE FROM `UnitBase`
	WHERE `UnitBase`.`Id` = pId;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Wednesday 6th of January 2016 08:10:41 PM
-- DML SelectOne Stored Procedure for UnitBase 
-- 
USE Webwinkel;
DROP PROCEDURE IF EXISTS UnitBaseSelectOne;
DELIMITER //
CREATE PROCEDURE `UnitBaseSelectOne`
(
	 pId INT 
)
BEGIN
SELECT * FROM `UnitBase`
	WHERE `UnitBase`.`Id` = pId;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Wednesday 6th of January 2016 08:10:41 PM
-- DML SelectAll Stored Procedure for table UnitBase 
-- 
USE Webwinkel;
DROP PROCEDURE IF EXISTS UnitBaseSelectAll;
DELIMITER //
CREATE PROCEDURE `UnitBaseSelectAll`
(
)
BEGIN
SELECT `UnitBase`.`Name`,
	`UnitBase`.`ShippingCostMultiplier`,
	`UnitBase`.`Code`,
	`UnitBase`.`Id`
	FROM `UnitBase`
	ORDER BY `Name`;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Wednesday 6th of January 2016 08:10:41 PM
-- DML SelectByName Stored Procedure for table UnitBase
-- 
USE Webwinkel;
DROP PROCEDURE IF EXISTS UnitBaseSelectByName;
DELIMITER //
CREATE PROCEDURE `UnitBaseSelectByName`
(
	 pName NVARCHAR (255) 
)
BEGIN
SELECT `UnitBase`.`Name`,
	`UnitBase`.`ShippingCostMultiplier`,
	`UnitBase`.`Code`,
	`UnitBase`.`Id`

	FROM `UnitBase`
	WHERE `UnitBase`.`Name` = pName
	ORDER BY `UnitBase`.`Name`;
END //
DELIMITER ;

-- modernways.be
-- created by an orm apart
-- Entreprise de modes et de manières modernes
-- MySql DML
-- Created : Wednesday 6th of January 2016 08:10:41 PM
-- DML SelectByCode Stored Procedure for table UnitBase
-- 
USE Webwinkel;
DROP PROCEDURE IF EXISTS UnitBaseSelectByCode;
DELIMITER //
CREATE PROCEDURE `UnitBaseSelectByCode`
(
	 pCode NVARCHAR (2) 
)
BEGIN
SELECT `UnitBase`.`Name`,
	`UnitBase`.`ShippingCostMultiplier`,
	`UnitBase`.`Code`,
	`UnitBase`.`Id`

	FROM `UnitBase`
	WHERE `UnitBase`.`Code` = pCode
	ORDER BY `UnitBase`.`Code`;
END //
DELIMITER ;

2016-01-11 14:34:56