テキトウにストアドプロシジャを作成
CREATE OR REPLACE PROCEDURE public.pr_mst_item_select_00(
IN p_sub_fr numeric,
IN p_sub_to numeric,
IN p_item_fr numeric,
IN p_item_to numeric,
INOUT p_num numeric[],
INOUT p_msg text[],
INOUT p_result numeric,
INOUT p_cur refcursor)
LANGUAGE 'plpgsql'
AS $BODY$
declare
sub_fr numeric := p_sub_fr;
sub_to numeric := p_sub_to;
item_fr numeric := p_item_fr;
item_to numeric := p_item_to;
begin
p_result := 99;
if sub_fr = 0 then
sub_fr := 0;
end if;
if sub_to = 0 then
sub_to := 9999;
end if;
if item_fr = 0 then
item_fr := 0;
end if;
if item_to = 0 then
item_to := 9999999999999;
end if;
open p_cur for
select m0.*
, m1.name as subname
from (
select *
from mst_item
where sub >= sub_fr
and sub <= sub_to
and item >= item_fr
and item <= item_to
) m0
inner join (
select *
from mst_subject
where sub >= sub_fr
and sub <= sub_to
) m1
on m0.sub = m1.sub
order by m1.turn, m0.sub, m0.item
;
p_num := array[5,4,3,2,1,0,-1,-2,-3,-4,-5];
p_msg := array['AAAAA','BBBBB','CCCCC',to_char(now(),'YYYYMMDDHH24MISSMS')];
p_result := 0;
end;
$BODY$;
ALTER PROCEDURE public.pr_mst_item_select_00(numeric, numeric, numeric, numeric, numeric[], text[], numeric, refcursor)
OWNER TO postgres;