Stored Procedures with SELECT INTO






Stored Procedures with SELECT INTO

Just as with embedded SQL, the results of SELECT statements within stored procedures can be retrieved in two ways. If the SELECT statement is guaranteed to return one row at the most, the SELECT INTO statement can be used.

16. Create a stored procedure that calculates the total of the penalties of a certain player. After that, call the procedure for player 27.

CREATE PROCEDURE TOTAL_PENALTIES_PLAYER
   (IN P_PLAYERNO INTEGER,
    OUT TOTAL_PENALTIES DECIMAL(8,2))
BEGIN
   SELECT SUM(AMOUNT)
   INTO   TOTAL_PENALTIES
   FROM   PENALTIES
   WHERE  PLAYERNO = P_PLAYERNO;
END

CALL TOTAL_PENALTIES_PLAYER (27, @TOTAL)

SELECT @TOTAL

Explanation: The result of the SELECT INTO statement is immediately assigned to the output parameter TOTAL_PENALTIES.

Another example in which the SELECT INTO statement could be used well is Figure. The first two SET statements with subqueries could be replaced by one SELECT INTO statement, to improve the processing speed:

SELECT    FATHER_PLAYERNO, MOTHER_PLAYERNO
INTO      V_FATHER, V_MOTHER
FROM      PLAYERS_WITH_PARENTS
WHERE     PLAYERNO = P_PLAYERNO

17. Create a stored procedure that retrieves the address of a player.

CREATE PROCEDURE GIVE_ADDRESS
   (IN  P_PLAYERNO SMALLINT,
    OUT P_STREET VARCHAR(30),
    OUT P_HOUSENO CHAR(4),
    OUT P_TOWN VARCHAR(30),
    OUT P_POSTCODE CHAR(6))
BEGIN
   SELECT TOWN, STREET, HOUSENO, POSTCODE
   INTO   P_TOWN, P_STREET, P_HOUSENO, P_POSTCODE
   FROM   PLAYERS
   WHERE  PLAYERNO = P_PLAYERNO;
END

18.

CREATE TABLE FIBON
      (NUMBER1   INTEGER NOT NULL PRIMARY KEY,
       NUMBER2   INTEGER NOT NULL)

We need a stored procedure to assign an initial value to the two columns; see the next example. The DELETE statement is used to empty the table in case it contains remnants of a previous exercise. Next, we use an INSERT statement to give the columns an initial value:

CREATE PROCEDURE FIBONNACI_START()
BEGIN
   DELETE FROM FIBON;
   INSERT INTO FIBON (NUMBER, NUMBER2) VALUES (16, 27);
END

The original Fibonnaci procedure will now look as follows:

CREATE PROCEDURE FIBONNACI_GIVE
   (INOUT NUMBER INTEGER)
BEGIN
   DECLARE N1, N2 INTEGER;
   SELECT NUMBER1, NUMBER2
   INTO   N1, N2
   FROM   FIBON;
   SET NUMBER = N1 + N2;
   IF NUMBER > 10000 THEN
      SET NUMBER = NUMBER - 10000;
   END IF;
   SET N1 = N2;
   SET N2 = NUMBER;
   UPDATE FIBON
   SET    NUMBER1 = N1,
          NUMBER2 = N2;
END

The last two values are retrieved with a SELECT INTO statement. The procedure is probably obvious. The part of a program in which the procedures are called might look like this:

CALL FIBONNACI_START()

CALL FIBONNACI_GIVE(@C)

SELECT @C

CALL FIBONNACI_GIVE(@C)

SELECT @C

CALL FIBONNACI_GIVE(@C)

SELECT @C

The first advantage of the previous solution is that when a procedure is called, only one parameter has to be passed. The second advantage has to do with the way the Fibonnaci algorithm works: In the second solution, the internal workings are much more hidden from the calling program.

19. Create a stored procedure that removes a player. Imagine that the following rule applies: A player can be removed only if he or she has incurred no penalty and only if he or she is not a captain of a team. It is also assumed that no foreign keys have been defined.

CREATE PROCEDURE DELETE_PLAYER
   (IN P_PLAYERNO INTEGER)
BEGIN
   DECLARE NUMBER_OF_ PENALTIES INTEGER;
   DECLARE NUMBER_OF_TEAMS INTEGER;
   SELECT COUNT(*)
   INTO   NUMBER_OF_PENALTIES
   FROM   PENALTIES
   WHERE  PLAYERNO = P_PLAYERNO;

   SELECT COUNT(*)
   INTO   NUMBER_OF_TEAMS
   FROM   TEAMS
   WHERE  PLAYERNO = P_PLAYERNO_;

   IF NUMBER_OF_PENALTIES = 0 AND NUMBER_OF_TEAMS = 0 THEN
      CALL DELETE_MATCHES (P_PLAYERNO);
      DELETE FROM PLAYERS
      WHERE PLAYERNO = P_PLAYERNO;
   END IF;
END

This stored procedure can be optimized by checking, after the first SELECT statement, whether the number of penalties is not equal to zero. If this is the case, the procedure can be interrupted because the second SELECT statement is no longer necessary.



 Python   SQL   Java   php   Perl 
 game development   web development   internet   *nix   graphics   hardware 
 telecommunications   C++ 
 Flash   Active Directory   Windows