我再次呼吁你的帮助 . 我正在将流程从Oracle迁移到postgres .
我声明此光标以提取信息并将其插入表中:
esi_cur_fono cursor
for SELECT (select nextval('edef_seq_pr')) seq_nextval,
c_pcodigo_soc_dest,
c_ctac_correlativo,
c_transac,
c_transac||
lpad(c_tr_count, v_transaction_seq,'0')||
lpad(c_rc_count, v_record_seq,'0')||
rpad(esi.pers_codigo, v_exploitation_source_id,' ')||
rpad(translate(esi.nombre,c_cad_n,c_cad_y), v_exploitation_source_name,' ')||
rpad(esi.esty, v_exploitation_source_type,' ')||
lpad(c_tisn_cd, v_exploitation_territory_code,'0')||
lpad(c_tisn_fd, v_exploitation_territory_cvfd,'0')||
rpad(c_tisan, v_exploitation_territory_abbn,' ')||
lpad(c_tisn_fd, v_exploitation_territory_avfd,'0'),
now() fecha,
tipo_dist,
(SELECT currval('edef_seq_pr')) edef_padre,
c_edef_order,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL
FROM (SELECT DISTINCT liprt.pers_codigo,
pers.pers_nombre_completo nombre,
'10' esty,
'MEC' tipo_dist
FROM mocct a
INNER JOIN reort b ON a.reor_correlativo = b.reor_correlativo
INNER JOIN deret c ON c.dere_correlativo = b.dere_correlativo
INNER JOIN dblink('dbname = crd host=100.1.1.138 port=5432', 'select delp_correlativo, lipr_correlativo from fon_detalles_liq_productor') as delpt (delp_correlativo numeric, lipr_correlativo numeric) ON delpt.delp_correlativo = c.delp_correlativo
INNER JOIN dblink('dbname = crd host=100.1.1.138 port=5432', 'select lipr_correlativo, pers_codigo from fon_liquidaciones_productor') as liprt (lipr_correlativo numeric, pers_codigo varchar) ON liprt.lipr_correlativo = delpt.lipr_correlativo
INNER JOIN dblink('dbname = usuarios host=100.1.1.138 port=5432', 'select pers_codigo, pers_nombre_completo from unv_personas') as pers (pers_codigo varchar, pers_nombre_completo varchar) ON liprt.pers_codigo = pers.pers_codigo
WHERE a.mocc_monto != 0
AND b.pers_codigo_socadm = '312951160'
AND a.ctac_correlativo = 7344) esi;
问题出现在变量的声明中,其中光标返回的值被保存,因为不是表,而是子查询,我得到一个错误,所以我诉诸于声明变量的类型和长度最大值每一个人 .
/*declaration of cursor variables*/
v_seq_nextval numeric(10);
v_c_pcodigo_soc_dest varchar(10);
v_c_ctac_correlativo varchar(10);
v_c_transac varchar(10);
v_registro varchar(218);
v_fecha date;
v_tipo_dist varchar(10);
v_edef_padre numeric(10);
v_c_edef_order numeric(1);
v_null_1 varchar(10);
v_null_2 varchar(10);
v_null_3 varchar(10);
v_null_4 varchar(10);
v_null_5 varchar(10);
v_null_6 varchar(10);
v_null_7 varchar(10);
v_null_8 varchar(10);
当我执行它时,它给我以下错误
ERROR: Missing "FROM or IN" at the end of the SQL expression
LINE 107: FETCH esi_cursor_fono INTO v_seq_nextval;
^
SQL state: 42601
Character: 4897
到处看看,所有示例都是表格,甚至我已经有2个工作没有问题,但这是因为查询直接指向表,而不是子查询 .
1 回答
不确定你在
FOR
循环中做了什么,但这里有一个例子,你可以使用并适应你的需求:您不需要将值存储到变量中,只需创建一个新的
RECORD
变量,该变量存储FOR
循环SELECT
语句的当前行 .如果您正在寻找或者我误解了您的问题,请告诉我 .