Examples

Create a procedure body with a compound statement that performs the following actions.

  1. Declares SQL variables.
  2. Declares a cursor to return the salary of employees in a department determined by an IN parameter.
  3. Declares an EXIT handler for the condition NOT FOUND (end of file) which assigns the value 6666 to the OUT parameter medianSalary.
  4. Select the number of employees in the given department into the SQL variable v_numRecords.
  5. Fetch rows from the cursor in a WHILE loop until 50% + 1 of the employees have been retrieved.
  6. Return the median salary.
     CREATE PROCEDURE DEPT_MEDIAN 
        (IN  deptNumber   SMALLINT, 
         OUT medianSalary DOUBLE)
        LANGUAGE SQL
        BEGIN
         DECLARE v_numRecords INTEGER DEFAULT 1;
         DECLARE v_counter INTEGER DEFAULT 0;
         DECLARE c1 CURSOR FOR
            SELECT salary FROM staff 
               WHERE DEPT = deptNumber 
               ORDER BY salary;
         DECLARE EXIT HANDLER FOR NOT FOUND
            SET medianSalary = 6666; 
            /* initialize OUT parameter */
            SET medianSalary = 0;
            SELECT COUNT(*) INTO v_numRecords FROM staff
               WHERE DEPT = deptNumber;
            OPEN c1;
            WHILE v_counter < (v_numRecords / 2 + 1) DO
               FETCH c1 INTO medianSalary;
               SET v_counter = v_counter + 1;
            END WHILE;
            CLOSE c1;
         END