update_rpt

CREATE OR REPLACE procedure update_rpt
(
 z_table_name   in varchar2,
 z_scrapqty     in number,
 z_defect_code  in varchar2,
 z_assy_lot     in varchar2,
 
 Z_SQLCODE      IN OUT NUMBER, 
 Z_SQLERRM      IN OUT VARCHAR2, 
 Z_DBERROR      IN OUT NUMBER
)
is
   sql_str varchar2(1000);
   v_column_name varchar2(30);
begin
  Z_DBERROR := 108100;
  select a.column_name into v_column_name from user_tab_columns a
  where  a.table_name=z_table_name and substr(a.column_name,1,5)=z_defect_code;
 
sql_str :='UPDATE  '||z_table_name||'  SET '||v_column_name||'='||z_scrapqty||' where assy_lot='||''''||z_assy_lot||'''';
execute immediate sql_str;
   commit;
  
   EXCEPTION
    --异常处理
    WHEN NO_DATA_FOUND THEN
        Z_SQLCODE := SQLCODE;
        Z_SQLERRM := SQLERRM;
        Z_DBERROR := Z_DBERROR + 1;
        ROLLBACK;

    WHEN TOO_MANY_ROWS THEN
        Z_SQLCODE := SQLCODE;
        Z_SQLERRM := SQLERRM;
        Z_DBERROR := Z_DBERROR + 2;
        ROLLBACK;

    WHEN OTHERS THEN
        Z_SQLCODE := SQLCODE;
        Z_SQLERRM := SQLERRM;
        Z_DBERROR := Z_DBERROR + 9;
        ROLLBACK;
end update_rpt;