{Denodo}VQLストアドプロシージャ

※ Denodo 8.0 2023 年 3 月アップデートで追加

https://community.denodo.com/docs/html/browse/8.0/en/vdp/developer/developing_extensions/developing_stored_procedures/developing_vql_stored_procedures

SELECT *
FROM GET_ELEMENTS()
where type = 'storedProcedure'
and subtype = 'user defined - vql'
;

CREATE OR REPLACE VQL PROCEDURE proc01
(param1 IN VARCHAR, param2 OUT VARCHAR)
AS (
   name VARCHAR;
   exception1 EXCEPTION;
   nombre VARCHAR;
)
BEGIN
    name := 'Test';
    IF name = 'Test' THEN
        RAISE exception1;
    END IF;

    EXCEPTION
        WHEN exception1 THEN
        nombre := param1;
        RETURN ROW (param2) VALUES (nombre);
END;

select * 
from proc01()
where param1 = 'A'
;

CREATE OR REPLACE VQL PROCEDURE proc02
(oid IN INTEGER, valormax OUT INTEGER)
AS (
    a INTEGER;
    b INTEGER;
    c INTEGER;
    abcmax INTEGER;
)
BEGIN
    a:=oid;
    b:=(a/5);
    c:=(b*7);
    IF a > b THEN
      IF a > c THEN
         abcmax:=a;
      ELSE
         abcmax:=c;
      END IF;
    ELSE
      IF b > c THEN
       abcmax:=b;
      ELSE
       abcmax:=c;
      END IF;
    END IF;
    RETURN ROW (valormax) VALUES (abcmax);
END;

select * from proc02()
where oid = 100
;

CREATE OR REPLACE VQL PROCEDURE proc03
(col1 OUT VARCHAR, col2 OUT VARCHAR, col3 OUT VARCHAR)
AS (
  CURSOR cur1 IS 'SELECT col1, col2, col3 FROM db01.b_tab1 WHERE col4 > 1';
  c1 cur1%ROWTYPE;
)
BEGIN
    OPEN cur1;
    LOOP
      FETCH cur1 INTO c1;
      RETURN ROW ( col1, col2, col3) VALUES (c1.col1, c1.col2, c1.col3);
      EXIT WHEN cur1%NOTFOUND;
    END LOOP;
    CLOSE cur1;
END
;

select * from proc03();