CREATE TABLE `_projects` (
  `p_id` int NOT NULL AUTO_INCREMENT,
  `p_name` varchar(45) NOT NULL,
  PRIMARY KEY (`p_id`)
);

CREATE TABLE `_tests` (
  `t_id` int NOT NULL AUTO_INCREMENT,
  `t_date` datetime NOT NULL,
  `t_name` varchar(45) NOT NULL,
  `t_rating` float NOT NULL,
  `p_id` int NOT NULL,
  PRIMARY KEY (`t_id`),
  KEY `fk_project_tests_idx` (`p_id`),
  CONSTRAINT `fk_project_tests` FOREIGN KEY (`p_id`) REFERENCES `_projects` (`p_id`)
);

<dto-class name="Test" ref="tests" />

<dto-class name="Test" ref="" >
  <field column="t_id" type="int" />
  <field column="t_date" type="java.util.Date" />
  <field column="t_name" type="String" />
  <field column="t_rating" type="java.math.BigDecimal" />
</dto-class>

<query method="get_test_rating(int t_id)" return-type="java.math.BigDecimal" ref="select get_test_rating(?)" />

CREATE FUNCTION `get_test_rating`(pt_id int) RETURNS float
  DETERMINISTIC
BEGIN
  DECLARE res numeric(10, 2);
  select t_rating into res from _tests where t_id = pt_id;
  RETURN res;
END

<query method="get_test_rating(int t_id)" return-type="java.math.BigDecimal" ref="select get_test_rating(?)" />

CREATE PROCEDURE `get_tests_by_rating`(in p_rating float)
BEGIN
  select * from _tests where t_rating >= p_rating;
END

<query-dto-list method="get_tests_by_rating(double t_rating)" dto="Test" ref="call get_tests_by_rating(?)" />

CREATE PROCEDURE `sp_get_all`(rating float)
BEGIN
  select distinct p.* from _projects p inner join _tests t on t.p_id = p.p_id
  where t.t_rating > rating;
  select * from _tests where t_rating > rating
  order by t_rating;
END

<exec-dml method="sp_get_all(float rating, [on_project:Project, on_test:Test])" ref="call sp_get_all(?)" />

CREATE PROCEDURE `set_test_rating`(in p_t_id int, in p_rating float)
BEGIN
  update _tests set t_rating = p_rating where t_id = p_t_id;
END

<exec-dml method="set_test_rating(int t_id, double t_rating)" ref="call set_test_rating(?, ?)" />

CREATE PROCEDURE `sp_get_film_title`(p_film_id int, out p_title varchar(200))
BEGIN
    select title into p_title from film where film_id = p_film_id;
END

CREATE PROCEDURE `sp_test_in_out_params`(inout res int)
BEGIN
    set res = res * 10;
END

<exec-dml method="sp_get_film_title(film_id, title)" ref="call sp_get_film_title(?, ?)" />

<exec-dml method="sp_test_in_out_params(res)" ref="call sp_test_in_out_params(?)" />

CREATE TABLE "PROJECTS"
(
  "P_ID" NUMBER GENERATED ALWAYS AS IDENTITY,
  "P_NAME" VARCHAR2(200) NOT NULL,
   CONSTRAINT "PROJECTS_PK" PRIMARY KEY ("P_ID")
);

CREATE TABLE "TESTS"
(
  "T_ID" NUMBER GENERATED ALWAYS AS IDENTITY,
  "T_DATE" DATE NOT NULL,
  "T_NAME" VARCHAR2(200) NOT NULL,
  "T_RATING" NUMBER(10,2) NOT NULL,
  "P_ID" INT NOT NULL,
  CONSTRAINT "TEST_PK" PRIMARY KEY ("T_ID")
);

CREATE OR REPLACE NONEDITIONABLE TYPE TYPE_OF_TEST AS OBJECT
(
  T_ID INT,
  T_DATE DATE,
  T_NAME VARCHAR2(200),
  T_RATING NUMERIC(10,2)
);

CREATE OR REPLACE NONEDITIONABLE TYPE TYPE_OF_TEST_TABLE AS TABLE OF TYPE_OF_TEST;

CREATE OR REPLACE NONEDITIONABLE FUNCTION GET_TEST_RATING(P_T_ID IN INT) RETURN NUMBER
AS
  RES NUMERIC(10,2);
BEGIN
  SELECT T_RATING INTO RES FROM TESTS WHERE T_ID = P_T_ID;
  RETURN RES;
END GET_TEST_RATING;

CREATE OR REPLACE NONEDITIONABLE FUNCTION GET_TEST(P_T_ID IN INT) RETURN TYPE_OF_TEST_TABLE
AS
  RES TYPE_OF_TEST_TABLE;
BEGIN
  RES := TYPE_OF_TEST_TABLE();
  SELECT TYPE_OF_TEST(T_ID, T_DATE, T_NAME, T_RATING)
    BULK COLLECT INTO RES FROM TESTS WHERE T_ID = P_T_ID;
  RETURN RES;
END GET_TEST;

<query-dto method="get_test(t_id)" dto="Test" ref="select * from GET_TEST(?)" />

CREATE OR REPLACE NONEDITIONABLE FUNCTION GET_TESTS_BY_RATING(P_T_RATING IN NUMERIC)
  RETURN TYPE_OF_TEST_TABLE
AS
  RES TYPE_OF_TEST_TABLE;
BEGIN
  RES := TYPE_OF_TEST_TABLE();
  SELECT TYPE_OF_TEST(T_ID, T_DATE, T_NAME, T_RATING)
    BULK COLLECT INTO RES FROM TESTS WHERE T_RATING >= P_T_RATING;
  RETURN RES;
END GET_TESTS_BY_RATING;

<query-dto-list method="get_tests_by_rating(rating)" dto="Test" ref="select * from get_tests_by_rating(?)" />

CREATE OR REPLACE FUNCTION GET_RC(P_RATING IN NUMBER) RETURN SYS_REFCURSOR
AS
  my_cursor SYS_REFCURSOR;
BEGIN
  OPEN my_cursor FOR
    SELECT *
    FROM   tests
    WHERE  t_rating >= p_rating
    ORDER BY t_rating;
  RETURN my_cursor;
END GET_RC;

<exec-dml method="get_rc(on_test:Test, p_rating)" ref="BEGIN ?:= GET_RC(?); END;" />

CREATE OR REPLACE PROCEDURE SP_GET_RS_TEST (P_RATING IN NUMBER, P_RECORDSET OUT SYS_REFCURSOR)
AS
BEGIN
  OPEN P_RECORDSET FOR
    SELECT *
    FROM   TESTS
    WHERE  T_RATING >= P_RATING
    ORDER BY T_RATING;
END SP_GET_RS_TEST;

CREATE OR REPLACE NONEDITIONABLE PROCEDURE SP_2_RC(p_rating IN NUMBER, P_TASKS OUT SYS_REFCURSOR, P_PROJECTS OUT SYS_REFCURSOR)
AS
  rc1 SYS_REFCURSOR;
  rc2 SYS_REFCURSOR;
BEGIN
  OPEN rc1 FOR
    SELECT *
    FROM   Tests
    WHERE t_rating >= p_rating
    ORDER BY t_rating;
  P_TASKS := rc1;
  OPEN rc2 FOR
    SELECT * FROM  projects;
  P_PROJECTS := rc2;
END SP_2_RC;

<exec-dml method="sp_get_rs_test(p_rating, on_test:Test)" ref="begin SP_GET_RS_TEST(?, ?); end;" />

<exec-dml method="sp_2_rc(p_rating, on_test:Test, on_project:Project)" ref="BEGIN SP_2_RC(?, ?, ?); END;"/>

CREATE OR REPLACE NONEDITIONABLE PROCEDURE SP_IMPLICIT_RC (P_RATING IN NUMBER)
AS
  rc SYS_REFCURSOR;
BEGIN
  OPEN rc FOR
    SELECT *
    FROM   TESTS
    WHERE  T_RATING >= P_RATING
    ORDER BY T_RATING;
  DBMS_SQL.RETURN_RESULT(rc);
END SP_IMPLICIT_RC;

CREATE OR REPLACE NONEDITIONABLE PROCEDURE SP_IMPLICIT_2_RC (P_P_ID IN NUMBER)
AS
  rc1 SYS_REFCURSOR;
  rc2 SYS_REFCURSOR;
BEGIN
  OPEN rc1 FOR
    SELECT *
    FROM   TESTS
    WHERE  P_ID = P_P_ID
    ORDER BY T_RATING;
  DBMS_SQL.RETURN_RESULT(rc1);
  OPEN rc2 FOR
    SELECT *
    FROM  PROJECTS
    WHERE  P_ID = P_P_ID;
  DBMS_SQL.RETURN_RESULT(rc2);
END SP_IMPLICIT_2_RC;

<exec-dml method="sp_implicit_rc(p_id, [on_test:Test])" ref="begin SP_IMPLICIT_RC(?); end;" />

<exec-dml method="sp_implicit_2_rc(p_id, [on_test:Test, on_project:Project])" ref="begin SP_IMPLICIT_2_RC(?); end;" />

CREATE OR REPLACE NONEDITIONABLE PROCEDURE SET_TEST_RATING (P_T_ID IN INT, P_T_RATING IN NUMBER)
AS
BEGIN
  UPDATE TESTS SET T_RATING = P_T_RATING WHERE T_ID = P_T_ID;
END SET_TEST_RATING;

<exec-dml method="set_test_rating(t_id, rating)" ref="begin SET_TEST_RATING(?, ?); end;" />

dao.set_test_rating(642, 5)

create or replace NONEDITIONABLE PROCEDURE SP_TEST_OUT_PARAMS(P_ID IN NUMBER, P_RATING OUT NUMBER) AS
    id number;
BEGIN
    id := P_ID; -- failed while using WHERE t_id = P_ID
    SELECT t_rating INTO P_RATING
    FROM tests
    WHERE t_id = id;
END SP_TEST_OUT_PARAMS;

create or replace PROCEDURE SP_TEST_INOUT_PARAMS(PARAM1 IN OUT NUMBER) AS
BEGIN
  PARAM1 := PARAM1 * 10;
END SP_TEST_INOUT_PARAMS;

<exec-dml method="sp_test_inout_params(res)" ref="begin SP_TEST_INOUT_PARAMS(?); end;"/>
<exec-dml method="sp_test_out_params(t_id, out_rating)" ref="begin SP_TEST_OUT_PARAMS(?, ?); end;"/>

CREATE TABLE IF NOT EXISTS public.projects
(
    p_id serial NOT NULL,
    p_name character varying(200),
    CONSTRAINT projects_pkey PRIMARY KEY (p_id)
);

CREATE TABLE IF NOT EXISTS public.tests
(
    t_id serial NOT NULL,
    t_date timestamp without time zone NOT NULL,
    t_name character varying(200),
    t_rating numeric(10,2) NOT NULL,
    p_id integer NOT NULL,
    CONSTRAINT tests_pkey PRIMARY KEY (t_id)
);

<query method="get_test_rating(t_id)" return-type="java.math.BigDecimal" ref="select get_test_rating(?)" />

CREATE OR REPLACE FUNCTION public.get_test_rating(p_t_id integer)
    RETURNS numeric
    LANGUAGE 'plpgsql'
AS $BODY$DECLARE
  res NUMERIC;
BEGIN
  select t_rating into res from tests where t_id = p_t_id;
  RETURN res;
END;$BODY$;

CREATE OR REPLACE FUNCTION public.get_tests_by_rating(p_t_rating numeric)
    RETURNS TABLE (r_id integer, r_date date, r_name character varying, r_rating numeric)
    LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
    var_r record;
BEGIN
   FOR var_r IN(SELECT t_id, t_date, t_name, t_rating FROM tests WHERE t_rating >= p_t_rating)
   LOOP
        r_id := var_r.t_id;
        r_date := var_r.t_date;
        r_name := var_r.t_name;
        r_rating := var_r.t_rating;
        RETURN NEXT;
   END LOOP;
END; $BODY$;

<dto-class name="Test3" ref="select * from get_tests_by_rating(?)" />

<query-dto-list method="get_tests_by_rating(t_rating)" dto="Test3" ref="select * from get_tests_by_rating(?)" />

CREATE OR REPLACE FUNCTION public.get_tests_setof_table(p_rating numeric) RETURNS SETOF tests
  LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
  r record;
BEGIN
  for r in SELECT t_id, t_date, concat('1. ', t_name), t_rating, p_id FROM tests WHERE t_rating >= p_rating order by t_rating loop
    return next r;
  end loop;
  for r in SELECT t_id, t_date, concat('2. ', t_name), t_rating, p_id FROM tests WHERE t_rating < p_rating order by t_rating loop
    return next r;
  end loop;
  return;
END; $BODY$;

<query-dto-list method="get_tests_setof_table(t_rating)" dto="Test" ref="select * from get_tests_setof_table(?)" />

CREATE OR REPLACE FUNCTION public.get_tests_by_rating_rc(p_rating numeric) RETURNS REFCURSOR
  LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
  ref REFCURSOR;
BEGIN
  OPEN ref FOR SELECT t_id, p_id, t_date, t_name, t_rating
  FROM tests WHERE t_rating >= p_rating;
  RETURN ref;
END;$BODY$;

CREATE FUNCTION public.get_rc_and_out_param(IN p_rating numeric, OUT p_avg_rating NUMERIC) RETURNS REFCURSOR
    ...

CREATE FUNCTION public.get_rc_and_out_param(IN p_rating numeric, INOUT p_avg_rating NUMERIC) RETURNS REFCURSOR
    ...

CREATE OR REPLACE FUNCTION public.get_tests_multiple(p_rating integer)
    RETURNS SETOF REFCURSOR 
    LANGUAGE 'plpgsql'
AS $BODY$DECLARE
    ref1 REFCURSOR; 
    ref2 REFCURSOR;                             
BEGIN
    OPEN ref1 FOR SELECT *, 1 as sign FROM tests WHERE t_rating >= p_rating
    ORDER by t_rating;
    RETURN NEXT ref1;                                                                            
    OPEN ref2 FOR SELECT *, 2 as sign FROM tests WHERE t_rating < p_rating
    ORDER by t_rating;
    RETURN NEXT ref2;                                                                              
END;$BODY$;

CREATE OR REPLACE PROCEDURE public.sp_out_ref_cursor(p_rating NUMERIC, INOUT p_ref REFCURSOR)
LANGUAGE 'plpgsql'
AS $BODY$ BEGIN
    OPEN p_ref FOR SELECT * FROM tests WHERE t_rating >= p_rating
    ORDER BY t_rating;
END$BODY$;

<exec-dml method="sp_out_ref_cursor(p_rating)" ref="CALL sp_out_ref_cursor(?, 'ref1')" />
<query-dto-list dto="Test" ref="fetch all from ref1" method="fetch_out_ref_cursor()" />

<exec-dml method="set_test_rating(t_id, t_rating)" ref="call set_test_rating(?, ?)" />

CREATE OR REPLACE PROCEDURE public.set_test_rating(
  p_t_id integer,
  p_t_rating numeric)
  LANGUAGE 'plpgsql'
AS $BODY$BEGIN
  update tests set t_rating = p_t_rating where t_id = p_t_id; 
END;$BODY$;

CREATE OR REPLACE PROCEDURE public.sp_test_in_out_param(INOUT res int)
LANGUAGE 'plpgsql'
AS $BODY$begin
    res := res * 10;
end;$BODY$;

<exec-dml method="sp_test_in_out_param(Object res)" ref="call sp_test_in_out_param(?)" />

CREATE OR REPLACE PROCEDURE public.sp_test_out_string_param(INOUT res CHARACTER VARYING)
LANGUAGE 'plpgsql'
AS $BODY$begin
    res := 'Hello from sp_test_out_string_param!';
end$BODY$;

<exec-dml method="sp_test_out_string_param(Object res)" ref="call sp_test_out_string_param(?)" />

CREATE OR REPLACE PROCEDURE public.sp_out_ref_cursor(p_rating numeric, INOUT p_ref REFCURSOR, OUT p_avg NUMERIC)

CREATE OR REPLACE PROCEDURE public.sp_out_ref_cursor2(p_rating NUMERIC, INOUT p_ref REFCURSOR, INOUT p_avg NUMERIC)
LANGUAGE 'plpgsql'
AS $BODY$ BEGIN
    OPEN p_ref FOR SELECT * FROM tests WHERE t_rating >= p_rating
    ORDER BY t_rating;
    
    SELECT AVG(t_rating) INTO p_avg FROM tests WHERE t_rating >= p_rating;
END$BODY$;

<!-- don't use "exec-dml" with out params /> -->
<!-- <exec-dml method="sp_out_ref_cursor2(p_rating, ref1, p_avg)" ref="CALL sp_out_ref_cursor(?, ?, ?)" /> -->
<query method="sp_out_ref_cursor2(p_rating, ref1, p_avg)" ref="CALL sp_out_ref_cursor(?, ?, ?)" />
<query-dto-list dto="Test" ref="" external-sql="true" method="fetch_out_ref_cursor()" />

CREATE FUNCTION public.get_rc_and_out_param(IN p_rating numeric, OUT p_avg_rating NUMERIC) RETURNS REFCURSOR
    ...

CREATE FUNCTION public.get_rc_and_out_param(IN p_rating numeric, INOUT p_avg_rating NUMERIC) RETURNS REFCURSOR
    ...

<query method="ufnLeadingZeros(value)" ref="select dbo.ufnLeadingZeros(?)" />

ALTER FUNCTION [dbo].[ufnLeadingZeros](@Value int)
RETURNS varchar(8)
WITH SCHEMABINDING 
AS 
BEGIN
    DECLARE @ReturnValue varchar(8);
    SET @ReturnValue = CONVERT(varchar(8), @Value);
    SET @ReturnValue = REPLICATE('0', 8 - DATALENGTH(@ReturnValue)) + @ReturnValue;
    RETURN (@ReturnValue);
END;

<query-list method="ufnDepartmentGroupNames" ref="select * from HumanResources.ufnDepartmentGroupNames()" />

ALTER FUNCTION [HumanResources].[ufnDepartmentGroupNames] ()
RETURNS TABLE
AS
    RETURN SELECT DISTINCT GroupName 
    FROM HumanResources.Department

<dto-class name="Department2" ref="select * from HumanResources.ufnDepartmentsByGroup(?)" />

<query-dto-list method="ufnDepartmentsByGroup(groupName)" dto="Department2"
    ref="select * from HumanResources.ufnDepartmentsByGroup(?)" />

ALTER FUNCTION [HumanResources].[ufnDepartmentsByGroup] (@GroupName varchar(100))
RETURNS TABLE
AS
    RETURN SELECT DepartmentID, Name 
    FROM HumanResources.Department 
    WHERE GroupName = @GroupName

ALTER PROCEDURE [HumanResources].[uspDepartments]
AS
  SELECT DepartmentID, Name, GroupName
  FROM HumanResources.Department ORDER BY Name

<query-list method="get_department_ids()" ref="{call HumanResources.uspDepartments()}" />

<query-dto-list method="get_departments()" dto="Department" ref="{call HumanResources.uspDepartments()}" />

<query-dto-list method="get_departments()" dto="Department" ref="exec HumanResources.uspDepartments" />

<exec-dml method="uspUpdateEmployeePersonalInfo(BusinessEntityID, NationalIDNumber, BirthDate, MaritalStatus, Gender)"
    ref="{CALL HumanResources.uspUpdateEmployeePersonalInfo(?, ?, ?, ?, ?)}" />

<exec-dml method="uspUpdateEmployeePersonalInfo(BusinessEntityID, NationalIDNumber, BirthDate, MaritalStatus, Gender)"
    ref="exec HumanResources.uspUpdateEmployeePersonalInfo ?, ?, ?, ?, ?" />

ALTER PROCEDURE [HumanResources].[uspUpdateEmployeePersonalInfo]
    @BusinessEntityID [int], @NationalIDNumber [nvarchar](15), @BirthDate [datetime], @MaritalStatus [nchar](1), @Gender [nchar](1)
WITH EXECUTE AS CALLER
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        UPDATE [HumanResources].[Employee] 
        SET [NationalIDNumber] = @NationalIDNumber, [BirthDate] = @BirthDate, [MaritalStatus] = @MaritalStatus, [Gender] = @Gender 
        WHERE [BusinessEntityID] = @BusinessEntityID;
    END TRY
    BEGIN CATCH
        EXECUTE [dbo].[uspLogError];
    END CATCH;
END;

<query method="test_sp_return_value" ref="
DECLARE @rv int;
EXEC @rv = [dbo].[test_sp_return_value];
SELECT @rv AS return_value;"/>

ALTER PROCEDURE [dbo].test_sp_return_value
AS
BEGIN
  SET NOCOUNT ON;
  RETURN 123;
END

ALTER PROCEDURE [dbo].[sp_test_inout_params]
	@Param1 int out 
AS
BEGIN
	SET NOCOUNT ON;
	SET @Param1 = @Param1 * 10;
END