Question 239139 in Mysql Question posted in
The official MySQL documentation can be found here.

Create a view from a mysql function

I have a stored procedure returning a table.
Is it possible to have the same output in a view instead of a stored procedure?
Is there any way to return the function in a view?

CREATE PROCEDURE `getReservedDevices`(IN `location_id` INT(11), IN `service_id` INT(11), IN `specification_id` INT(11), IN `entrytime` INT(11), IN `entrydate` DATE)
    NO SQL
BEGIN 

DECLARE slot_length INT;
DECLARE endtime INT;
DECLARE starttime INT;
DECLARE extra INT;

SET extra = entrytime % 60;
IF extra > 0 THEN 
    SET starttime = entrytime + (60 - extra);
ELSE 
    SET starttime = entrytime;
END IF;

(
  SELECT 
    timeslot_length INTO slot_length 
  FROM 
    service_location_device 
  WHERE 
    locationidfk = location_id 
    AND serviceidfk = service_id 
    AND specificationid = specification_id 
  LIMIT 
    1
);

IF slot_length IS NULL THEN 
    SET endtime = starttime;
ELSE 
    SET endtime = starttime + slot_length;
END IF;

SELECT 
  r.deviceid 
FROM 
  reservations r 
  JOIN service_location_device d ON r.deviceid = d.deviceid 
WHERE 
  (
    (
      r.slot_start >= entrytime 
      AND r.slot_start <= endtime
    ) 
    OR (
      r.slot_end >= entrytime 
      AND r.slot_end <= endtime
    )
  ) 
  AND d.locationidfk = location_id 
  AND d.serviceidfk = service_id 
  AND d.specificationid = specification_id 
  AND r.is_cancelled = 0
  AND r.slot_date = entrydate;
END

calling the procedure returns different records and is working fine.

SET @p0='51';  
SET @p1='2';  
SET @p2='2';   
SET @p3='1130';   
SET @p4='2023-08-24'; 

CALL `getReservedDevices`(@p0, @p1, @p2, @p3, @p4);

But i need to have the same output from a view.

Any help is welcome

select deviceid from …. where p0=51, p1=2, p2=2, p3=1130, p4=’2023-08-24′

Tags:

2

Answers


  1. 0

    If you only need to have a view, you make the view in the procedure

    CREATE PROCEDURE `getReservedDevices`(IN `location_id` INT(11), IN `service_id` INT(11), IN `specification_id` INT(11), IN `entrytime` INT(11), IN `entrydate` DATE)
    NO SQL
BEGIN 

DECLARE slot_length INT;
DECLARE endtime INT;
DECLARE starttime INT;
DECLARE extra INT;

SET extra = entrytime % 60;
IF extra > 0 THEN 
    SET starttime = entrytime + (60 - extra);
ELSE 
    SET starttime = entrytime;
END IF;

(
  SELECT 
    timeslot_length INTO slot_length 
  FROM 
    service_location_device 
  WHERE 
    locationidfk = location_id 
    AND serviceidfk = service_id 
    AND specificationid = specification_id 
  LIMIT 
    1
);

IF slot_length IS NULL THEN 
    SET endtime = starttime;
ELSE 
    SET endtime = starttime + slot_length;
END IF;

DROP VIEW IF exists myview;

CREATE VIEW myview
AS
SELECT 
  r.deviceid 
FROM 
  reservations r 
  JOIN service_location_device d ON r.deviceid = d.deviceid 
WHERE 
  (
    (
      r.slot_start >= entrytime 
      AND r.slot_start <= endtime
    ) 
    OR (
      r.slot_end >= entrytime 
      AND r.slot_end <= endtime
    )
  ) 
  AND d.locationidfk = location_id 
  AND d.serviceidfk = service_id 
  AND d.specificationid = specification_id 
  AND r.is_cancelled = 0
  AND r.slot_date = entrydate;
END

    in MySQL you can only make a temporary table in the procedure and select from it, as MySQL has no datatype table(Till now)

    Login or Signup to reply.
  2. 0

    Your question is lacking any context. Why use an integer to represent time instead of the TIME type? Why split date and time? Why add slot_length to rounded up entrytime to create endtime? Why do you want to change your stored procedure to a view?

    The following query provides the same functionality, I think:

    SET @location_id = 51;
SET @service_id = 2;
SET @specification_id = 2;
SET @entrytime = 1130;
SET @entrydate = '2023-08-24';

SELECT r.deviceid
FROM service_location_device d
JOIN reservations r ON r.deviceid = d.deviceid
WHERE d.locationidfk = @location_id
  AND d.serviceidfk = @service_id
  AND d.specificationid = @specification_id
  AND r.is_cancelled = 0
  AND r.slot_date = @entrydate
  AND r.slot_start < (CEIL(@entrytime / 60) * 60) + COALESCE(d.timeslot_length, 0)
  AND r.slot_end > @entrytime;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search