MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Why SELECT-INTO, eh?

without comments

Somebody raised the question about writing stored functions while we were discussing scalar subqueries against COMMON_LOOKUP tables. Common look up tables store collections of possible <OPTION> elements. They’re basically generalized tables that contain a set of smaller tables, where the row sets make up a list of unique values.

When you write a function that must return one and only one row, the SELECT-INTO syntax is ideal in Oracle because it automatically raises an exception when the query returns no row or two or more rows. MySQL doesn’t raise an automatic exception when a SELECT-INTO fails to return a row but it does raise an ERROR 1722 when two or more rows are found.

Here are sample implementations written in Oracle’s PL/SQL and MySQL’s SQL/PSM languages. If you’re an Oracle developer and new to MySQL, the biggest oddity may be the delimiter, or it may be the sizing of formal parameters. Hopefully, its not the lack of formal declaration and exception blocks in SQL/PSM. For those coming from MySQL, the OR REPLACE command or dynamic sizing of formal parameter list values are the big changes.

Oracle PL/SQL Function

The Oracle PL/SQL function is very straightforward and helped by pre-defined exceptions for both the no data found and too many rows found errors. Formal parameters inherit their physical size at run time from the calling scope program.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

-- Create or replace existing function with new implementation.
CREATE OR REPLACE FUNCTION get_common_lookup_id
( pv_table   VARCHAR2
, pv_column  VARCHAR2
, pv_type    VARCHAR2 )
RETURN NUMBER IS
 
  -- Declare a return variable.
  lv_return  NUMBER;
 
BEGIN
 
  -- Query data and assign it to a local variable.
  SELECT   common_lookup_id
  INTO     lv_return
  FROM     common_lookup
  WHERE    common_lookup_table = pv_table
  AND      common_lookup_column = pv_column
  AND      common_lookup_type = pv_type;
 
  -- Return the value found.
  RETURN lv_return;
 
EXCEPTION
 
  -- Handle errors.
  WHEN NO_DATA_FOUND THEN
    RAISE;
  WHEN TOO_MANY_ROWS THEN
    RAISE;
 
END;
/

MySQL SQL/PSM Function

The lack of an implicitly raised exception when a SELECT-INTO structure fails to return a row adds several steps to a SQL/PSM function. You must define a custom exception and an if-block to raise the exception, which is trigger by returning a null value into the lv_return local variable.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

/* Conditionally drop the function before trying to create it. */
DROP FUNCTION IF EXISTS get_common_lookup_id;
 
/* Change the delimiter to write a stored procedure into the database. */
DELIMITER $$
 
/* Create a function that replaces a scalar subquery. */
CREATE FUNCTION get_common_lookup_id
( pv_table   VARCHAR(30)
, pv_column  VARCHAR(30)
, pv_type    VARCHAR(30))
RETURNS INT UNSIGNED
BEGIN
 
  /* Declare a return variable. */
  DECLARE lv_return  INT UNSIGNED;
 
  /* Declare a local variable for a subsequent handler. */
  DECLARE no_data_found CONDITION FOR SQLSTATE '99001';
 
  /* Query data and assign it to a local variable. */
  SELECT   common_lookup_id
  INTO     lv_return
  FROM     common_lookup
  WHERE    common_lookup_table = pv_table
  AND      common_lookup_column = pv_column
  AND      common_lookup_type = pv_type;
 
  /* The local variable is only null when no rows are returned by the implicit cursor. */
  IF lv_return IS NULL THEN
    SIGNAL no_data_found SET MESSAGE_TEXT = 'Result was no rows found.';
  END IF;  
 
  /* Return the value found. */
  RETURN lv_return;
 
END;
$$
 
/* Reset the delimiter to the default to run other programs. */
DELIMITER ;

Hope this help solve your problems.

Written by maclochlainn

February 16th, 2011 at 11:03 pm

Posted in MySQL,Oracle,Oracle XE,Persistent Stored Modules,pl/sql,PSM,sql,SQL/PSM

«
»