Math calculation in a function : Function « Procedure Function « SQL / MySQL

1. SQL / MySQL
2. Procedure Function
3. Function

Math calculation in a function

       

CREATE TABLE TEAMS_NEW
      (TEAMNO     INTEGER NOT NULL PRIMARY KEY,
       EmployeeNO   INTEGER NOT NULL,
       DIVISION   SET ('first','second','third','fourth'));

INSERT INTO TEAMS_NEW VALUES (1, 27, 'first')
;
INSERT INTO TEAMS_NEW VALUES (2, 27, 'first,third')
;
INSERT INTO TEAMS_NEW VALUES (3, 27, 'first,third,sixth')
;
INSERT INTO TEAMS_NEW VALUES (4, 27, 'first,fifth')
;
INSERT INTO TEAMS_NEW VALUES (5, 27, NULL)
;
INSERT INTO TEAMS_NEW VALUES (6, 27, 7)
;
INSERT INTO TEAMS_NEW VALUES (7, 27, CONV(1001,2,10))
;
SELECT * FROM TEAMS_NEW;


SELECT   TEAMNO, DIVISION
FROM     TEAMS_NEW
WHERE    DIVISION & POWER(2,3-1) = POWER(2,3-1)
;

delimiter $$

CREATE FUNCTION POSITION_IN_SET
   (P_COLUMN BIGINT, POSITION SMALLINT)
   RETURNS BOOLEAN
BEGIN
   RETURN (P_COLUMN & POWER(2, POSITION-1) = 
          POWER(2,POSITION-1));
END$$

delimiter ;

SELECT   TEAMNO, DIVISION
FROM     TEAMS_NEW
WHERE    POSITION_IN_SET(DIVISION, 3);

drop table teams_new;

   
    
    
    
    
    
    
  

Related examples in the same category