sp-code.test

-- source include/have_debug.inc

SET sql_mode=ORACLE;

--echo #
--echo # Testing exceptions in the top-level blocks
--echo #

--echo # No HANDLER declarations, no exceptions
DELIMITER /;
CREATE FUNCTION f1 RETURN INT
AS
BEGIN
  RETURN 10;
END;
/
DELIMITER ;/
SHOW FUNCTION CODE f1;
SELECT f1();
DROP FUNCTION f1;

--echo # No HANDLER declarations, no code, no exceptions
DELIMITER /;
CREATE PROCEDURE p1 ()
IS
BEGIN
END;
/
DELIMITER ;/
SHOW PROCEDURE CODE p1;
CALL p1;
DROP PROCEDURE p1;


--echo # No HANDLER declarations, no code, some exceptions
DELIMITER /;
CREATE PROCEDURE p1 (v IN OUT INT)
IS
BEGIN
EXCEPTION
  WHEN 1002 THEN v:=225;
END;
/
DELIMITER ;/
SHOW PROCEDURE CODE p1;
set @v= 10;
CALL p1(@v);
SELECT @v;
DROP PROCEDURE p1;


--echo # No HANDLER declarations, some code, some exceptions
DELIMITER /;
CREATE PROCEDURE p1 (v IN OUT INT)
IS
BEGIN
  v:=224;
EXCEPTION
  WHEN 1002 THEN v:=225;
END;
/
DELIMITER ;/
SHOW PROCEDURE CODE p1;
set @v= 10;
CALL p1(@v);
SELECT @v;
DROP PROCEDURE p1;


--echo # Some HANDLER declarations, no code, no exceptions
DELIMITER /;
CREATE PROCEDURE p1 (v IN OUT INT)
IS
  EXIT HANDLER FOR 1000
  BEGIN
   v:=123;
  END;
BEGIN
END;
/
DELIMITER ;/
SHOW PROCEDURE CODE p1;
set @v= 10;
CALL p1(@v);
SELECT @v;
DROP PROCEDURE p1;


--echo # Some HANDLER declarations, no code, some exceptions
DELIMITER /;
CREATE PROCEDURE p1 (v IN OUT INT)
IS
  EXIT HANDLER FOR 1000
  BEGIN
   v:=123;
  END;
BEGIN
EXCEPTION
  WHEN 1002 THEN v:=225;
END;
/
DELIMITER ;/
SHOW PROCEDURE CODE p1;
set @v= 10;
CALL p1(@v);
SELECT @v;
DROP PROCEDURE p1;


--echo # Some HANDLER declarations, some code, no exceptions
DELIMITER /;
CREATE PROCEDURE p1 (v IN OUT INT)
IS
  EXIT HANDLER FOR 1000
  BEGIN
   v:=123;
  END;
BEGIN
  v:=223;
END;
/
DELIMITER ;/
SHOW PROCEDURE CODE p1;
set @v= 10;
CALL p1(@v);
SELECT @v;
DROP PROCEDURE p1;

--echo # Some HANDLER declarations, some code, some exceptions
DELIMITER /;
CREATE PROCEDURE p1 (v IN OUT VARCHAR2(20))
IS
  EXIT HANDLER FOR 1000
  BEGIN
    v:=123;
  END;
  CONTINUE HANDLER FOR 1001
  BEGIN
    SET v=223;
  END;
BEGIN
  v:= 1;
EXCEPTION
  WHEN 1002 THEN SET v=225;
END;
/
DELIMITER ;/
SHOW PROCEDURE CODE p1;
DROP PROCEDURE p1;


--echo #
--echo # Testing EXCEPTIONS in internal blocks
--echo #

--echo # No HANDLER declarations, no code, no exceptions
DELIMITER /;
CREATE PROCEDURE p1 (v IN OUT INT)
IS
BEGIN
  v:=123;
  BEGIN
  END;
END;
/
DELIMITER ;/
SHOW PROCEDURE CODE p1;
SET @v=10;
CALL p1(@v);
SELECT @v;
DROP PROCEDURE p1;

--echo # No HANDLER declarations, no code, some exceptions
DELIMITER /;
CREATE PROCEDURE p1 (v IN OUT INT)
IS
BEGIN
  v:=123;
  BEGIN
  EXCEPTION
    WHEN 20002 THEN v:=335;
  END;
END;
/
DELIMITER ;/
SHOW PROCEDURE CODE p1;
SET @v=10;
CALL p1(@v);
SELECT @v;
DROP PROCEDURE p1;

--echo # No HANDLER declarations, some code, no exceptions
DELIMITER /;
CREATE PROCEDURE p1 (v IN OUT INT)
IS
BEGIN
  v:=123;
  BEGIN
    v:=223;
  END;
END;
/
DELIMITER ;/
SHOW PROCEDURE CODE p1;
SET @v=10;
CALL p1(@v);
SELECT @v;
DROP PROCEDURE p1;

--echo # No HANDLER declarations, some code, some exceptions
DELIMITER /;
CREATE PROCEDURE p1 (v IN OUT INT)
IS
BEGIN
  v:=123;
  BEGIN
    v:=223;
  EXCEPTION
    WHEN 20002 THEN v:=335;
  END;
END;
/
DELIMITER ;/
SHOW PROCEDURE CODE p1;
SET @v=10;
CALL p1(@v);
SELECT @v;
DROP PROCEDURE p1;

--echo # Some HANDLER declarations, no code, no exceptions
DELIMITER /;
CREATE PROCEDURE p1 (v IN OUT INT)
IS
BEGIN
  v:=123;
  DECLARE
    EXIT HANDLER FOR 1000
    BEGIN
      v:=323;
    END;
  BEGIN
  END;
END;
/
DELIMITER ;/
SHOW PROCEDURE CODE p1;
SET @v=10;
CALL p1(@v);
SELECT @v;
DROP PROCEDURE p1;

--echo # Some HANDLER declarations, no code, some exceptions
DELIMITER /;
CREATE PROCEDURE p1 (v IN OUT INT)
IS
BEGIN
  v:=123;
  DECLARE
    EXIT HANDLER FOR 1000
    BEGIN
      v:=323;
    END;
  BEGIN
  EXCEPTION
    WHEN 20002 THEN v:=335;
  END;
END;
/
DELIMITER ;/
SHOW PROCEDURE CODE p1;
SET @v=10;
CALL p1(@v);
SELECT @v;
DROP PROCEDURE p1;

--echo # Some HANDLER declarations, some code, no exceptions
DELIMITER /;
CREATE PROCEDURE p1 (v IN OUT INT)
IS
BEGIN
  v:=123;
  DECLARE
    EXIT HANDLER FOR 1000
    BEGIN
      v:=323;
    END;
  BEGIN
   v:= 324;
  END;
END;
/
DELIMITER ;/
SHOW PROCEDURE CODE p1;
SET @v=10;
CALL p1(@v);
SELECT @v;
DROP PROCEDURE p1;

--echo # Some HANDLER declarations, some code, some exceptions
DELIMITER /;
CREATE PROCEDURE p1 (v IN OUT INT)
IS
BEGIN
  v:=123;
  DECLARE
    EXIT HANDLER FOR 1000
    BEGIN
      v:=323;
    END;
  BEGIN
    v:= 324;
  EXCEPTION WHEN 2002 THEN v:= 325;
  END;
END;
/
DELIMITER ;/
SHOW PROCEDURE CODE p1;
SET @v=10;
CALL p1(@v);
SELECT @v;
DROP PROCEDURE p1;

--echo #
--echo # Testing EXIT statement
--echo #

DELIMITER /;
CREATE FUNCTION f1 RETURN INT
IS
  i INT := 0;
BEGIN
  LOOP
    i:= i + 1;
    IF i >= 5 THEN
      EXIT;
    END IF;
  END LOOP;
  RETURN i;
END;
/
DELIMITER ;/
SHOW FUNCTION CODE f1;
SELECT f1() FROM DUAL;
DROP FUNCTION f1;


DELIMITER /;
CREATE FUNCTION f1 RETURN INT
IS
  i INT := 0;
BEGIN
  LOOP
    i:= i + 1;
    EXIT WHEN i >=5;
  END LOOP;
  RETURN i;
END;
/
DELIMITER ;/
SHOW FUNCTION CODE f1;
SELECT f1() FROM DUAL;
DROP FUNCTION f1;


DELIMITER /;
CREATE FUNCTION f1 RETURN INT
IS
  i INT := 0;
BEGIN
  LOOP
    BEGIN
      i:= i + 1;
      IF i >= 5 THEN
        EXIT;
      END IF;
    EXCEPTION
      WHEN OTHERS THEN i:= 1000;
    END;
  END LOOP;
  RETURN i;
END;
/
DELIMITER ;/
SHOW FUNCTION CODE f1;
SELECT f1() FROM DUAL;
DROP FUNCTION f1;


DELIMITER /;
CREATE PROCEDURE p1(a IN OUT INT)
IS
  i INT := 0;
BEGIN
  LOOP
    LOOP
      BEGIN
        i:= i + 1;
        IF i >=5 THEN
          EXIT;
        END IF;
      EXCEPTION
        WHEN OTHERS THEN a:=1000;
      END;
    END LOOP;
    i:= i + 100;
    EXIT;
  END LOOP;
  a:= i;
EXCEPTION
  WHEN OTHERS THEN a:=11;
END;
/
DELIMITER ;/
SHOW PROCEDURE CODE p1;
set @v= 10;
CALL p1(@v);
SELECT @v;
DROP PROCEDURE p1;


--echo # Testing RETURN in procedures
DELIMITER /;
CREATE PROCEDURE p1 (a IN OUT INT)
AS
BEGIN
  IF a < 10 THEN
    BEGIN
      a:= a + 1;
      RETURN;
    END;
  END IF;
  a:= 200;
EXCEPTION
  WHEN OTHERS THEN
  BEGIN
    a:= 100;
    RETURN;
  END;
END;
/
DELIMITER ;/
SHOW PROCEDURE CODE p1;
DROP PROCEDURE p1;


--echo # Testing FOR loop statement
DELIMITER /;
CREATE FUNCTION f1 (a INT, b INT) RETURN INT
AS
  total INT := 0;
BEGIN
  FOR i IN 1 .. a
  LOOP
    total:= total + i;
    IF i = b THEN
      EXIT;
    END IF;
  END LOOP;
  RETURN total;
END
/
DELIMITER ;/
SHOW FUNCTION CODE f1;
SELECT f1(3, 100) FROM DUAL;
SELECT f1(3, 2) FROM DUAL;
DROP FUNCTION f1;

DELIMITER /;
CREATE FUNCTION f1 (a INT, b INT) RETURN INT
AS
  total INT := 0;
BEGIN
  FOR i IN REVERSE a..1
  LOOP
    total:= total + i;
    IF i = b THEN
      EXIT;
    END IF;
  END LOOP;
  RETURN total;
END
/
DELIMITER ;/
SHOW FUNCTION CODE f1;
SELECT f1(3, 100) FROM DUAL;
SELECT f1(3, 2) FROM DUAL;
DROP FUNCTION f1;

--echo # Testing labeled FOR LOOP statement

DELIMITER /;
CREATE FUNCTION f1 (a INT, limita INT, b INT, limitb INT) RETURN INT
AS
  total INT := 0;
BEGIN
  <<la>>
  FOR ia IN 1 .. a
  LOOP
    total:= total + 1000;
    <<lb>>
    FOR ib IN 1 .. b
    LOOP
      total:= total + 1;
      EXIT lb WHEN ib = limitb;
      EXIT la WHEN ia = limita;
    END LOOP lb;
  END LOOP la;
  RETURN total;
END;
/
DELIMITER ;/
SHOW FUNCTION CODE f1;
SELECT f1(2, 1, 2, 2) FROM DUAL;
SELECT f1(2, 2, 2, 2) FROM DUAL;
SELECT f1(2, 3, 2, 3) FROM DUAL;
DROP FUNCTION f1;