pgsql调试过程脚本

---------调查询过程-------------------- pcx_tzgl_tzzh -------------------------------
DO
$$
DECLARE
ret_ref refcursor;
one_row record;
v_a integer;
v_c varchar;
v_b integer;
i_xmmc varchar;
i_yefw varchar;
begin
call pcx_tzgl_tzzh(v_a,v_c,v_b,ret_ref,1,50,null,null,null,0,'','2000,');
raise notice 'v_a:%,v_c:%', v_a,v_c ;
FETCH ret_ref INTO one_row;
WHILE FOUND LOOP
raise notice 'result:%', one_row ;
FETCH NEXT IN ret_ref INTO one_row;
END LOOP;
CLOSE ret_ref;
END
$$

 

-----------调执行过程----------- PRO_DDGL_DDSC ----------

DO
$$
DECLARE
v_a integer;
v_b varchar;
v_ddh numeric;
begin
call fts.PRO_DDGL_DDSC(v_a,v_b,v_ddh,1,1,1,1,10001,1000,1000,null,0,0,1,1);
raise notice '%|%|%', v_a,v_b,v_ddh;
END
$$

---------------------查询过程如下------------------------------

CREATE OR REPLACE PROCEDURE fts.pcx_tzgl_tzzh(INOUT o_code integer, INOUT o_note character varying, INOUT o_totalrows integer, INOUT o_cursor refcursor, i_pageno integer, i_pagelength integer, i_and_sql character varying, i_sort_sql character varying, i_chg_chn integer, i_userid integer, i_xmmc character varying, i_yefw character varying)
LANGUAGE plpgsql
AS $procedure$
DECLARE
/******************************************************************************
文件名称：PCX_TZGL_TZZH

******************************************************************************/


v_sql varchar(32767);
v_sort_sql varchar(32767);
v_collist varchar(32767);
v_where varchar(4000) := ' and 1=1 ';
V_ZCA varchar(40);
V_ZCB varchar(40);

BEGIN

o_code := 1;
o_note := '查询成功';

IF coalesce(i_userid::varchar, '') = '' THEN
o_code := 301;
o_note := '登录用户id不允许为空';
END IF;

IF coalesce(trim(both i_sort_sql)::varchar, '') = '' THEN
v_sort_sql := ' 1 desc ';
ELSE
v_sort_sql := i_sort_sql;
END IF;


/*--发起通道<=>来源通道
IF (i_chg_chn IS NOT NULL AND i_chg_chn::varchar <> '') THEN
v_where := v_where || ' and D.LYTD = ' || i_chg_chn || ' ';
END IF;*/

---项目名称
IF (i_xmmc IS NOT NULL AND i_xmmc::varchar <> '') THEN
v_where := v_where || ' and C.CPMC like ''%' || i_xmmc || '%''';
END IF;

--余额范围
IF (i_yefw IS NOT NULL AND i_yefw::varchar <> '') then
RAISE NOTICE '--i_yefw----> %', i_yefw;
select split_part(i_yefw,',',1) INTO strict V_ZCA;
select split_part(i_yefw,',',2) INTO strict V_ZCB;
IF (V_ZCA IS NOT NULL AND V_ZCA::varchar <> '') then
v_where := v_where ||
' AND (coalesce(A.XJZC::numeric(20,2),0)-coalesce(A.DJJE::numeric(20,2),0)) >= ' ||
V_ZCA;
END IF;
IF (V_ZCB IS NOT NULL AND V_ZCB::varchar <> '') then
v_where := v_where ||
' AND (coalesce(A.XJZC::numeric(20,2),0)-coalesce(A.DJJE::numeric(20,2),0)) <= ' ||
V_ZCB;
END IF;
END IF;

--拼接sql语句
v_sql := '';

v_sql := v_sql || v_where;

RAISE NOTICE '------> %
<-------', v_sql;

--执行分页查询
call pcx_comm_spli_page(o_code, o_note, o_cursor, i_pageno, i_pagelength, o_totalrows, v_sql, v_collist, true, FALSE, v_sort_sql, FALSE, TRUE) ;
-- call pcx_demo_fy2(o_code, o_note, o_cursor, i_pageno, i_pagelength, o_totalrows, v_sql, v_collist, true, FALSE, v_sort_sql, FALSE, TRUE) ;
EXCEPTION
WHEN OTHERS THEN
o_code := -599;
o_note := '查询参数详情失败' || SQLERRM;
END;
$procedure$
;

 

---------------------执行过程如下------------------------------

CREATE OR REPLACE PROCEDURE fts.pro_ddgl_ddsc(INOUT o_code integer, INOUT o_note character varying, INOUT o_ddh bigint, i_xmid integer, i_htid integer, i_cpid integer, i_dchtid integer, i_ywdm integer, i_je numeric, i_ccqgm numeric, i_bz character varying, i_djyh integer, i_orgid integer, i_lytd integer, i_khid integer)
LANGUAGE plpgsql
AS $procedure$
DECLARE

/*-----------------------------------------------------------------------

过程名称: PRO_DDGL_DDSC

------------------------------------------------------------------------*/
v_ddh numeric; -- 订单号

BEGIN
o_code := 1;
o_note := '成功';

···········

o_ddh := v_ddh;

EXCEPTION
WHEN OTHERS THEN
o_code := -1;
o_note := SQLERRM;
END;
$procedure$
;