atreemenu
atreemenu
2018-04-19 05:28
采纳率: 50%
浏览 9.0k
已采纳

oracle 用call函数执行存储过程,输出变量怎么写

图片说明

create or replace procedure p_imp_item(v_table_name in varchar2, operator_id in varchar2, v_result out varchar2 ) is
V_SQL long;
v_seq_num varchar2(20);
--v_code varchar2(20);
begin
begin
select concat(to_char(sysdate, 'yyyymmddhh24miss'),
lpad(SEQ_PROC_EXECUTE.NEXTVAL, 6, '0'))
into v_seq_num
from dual;

p_proc(v_seq_num, 'PROC_QC_TABLEDATA_STAT', 'begin', '');

--数据项上传
V_SQL := 'insert into data_items_t
(id, item_name, item_describe, item_ruler, item_type, item_range,
is_multiple_values, is_required, default_value, record_method,
remark, create_operator, create_date)
select id, item_name, item_describe, item_ruler,
decode(item_type, ''字符'', 1, ''数字'' ,2, ''日期'', 3, '''') item_type,
item_range, decode(is_multiple_values, ''Y'', 1, 0) is_multiple_values,
decode(is_required, ''Y'', 1, 0) is_required, default_value,
decode(record_method, ''自动'', 1, ''手动'' , 2, '''') record_method,
remark, '''|| operator_id ||''' , sysdate
from '|| v_table_name ||'';
execute immediate V_SQL;
commit;
p_proc(v_seq_num, 'p_imp_item', 'end', '');
end;
v_result := 1;
exception
when OTHERS then
rollback;
p_proc(v_seq_num, 'p_imp_item', 'error', dbms_utility.format_error_backtrace||':'||sqlerrm);

end p_imp_item;

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • dabocaiqq
    dabocaiqq 2018-04-19 05:44
    已采纳
    点赞 评论
  • yangguangniubi
    一路阳光CCG 2018-04-20 02:38

    select p_proc(v_seq_num, 'p_imp_item', 'error', dbms_utility.format_error_backtrace||':'||sqlerrm) from dual;

    点赞 评论

相关推荐