内部文件

来源：互联网发布：胆码计算软件编辑：程序博客网时间：2024/04/29 06:32

create or replace procedure etl isbegindeclare        cursor csr_etl_list       is       --select语句       select * from user_320300.etl_list_t       ;       --指定行指针,这句话应该是指定和csr_dept行类型相同的变量       row_etl_list csr_etl_list%rowtype;begin       --for循环       for row_etl_list in csr_etl_list loop        --   dbms_output.put_line('执行表名称:'||row_etl_list.table_name);        --log SP_ETL_LOG        if row_etl_list.Table_Status = 'T' THEN           SP_ETL_LOG('F',row_etl_list.table_name);           SP_REPLACE_TABLE(row_etl_list.table_name);           SP_ETL_LOG('T',row_etl_list.table_name);        end if ;       end loop;end;end;

---

create or replace procedure sp_REPLACE_TABLE(i_tablename in out Varchar) isbegin  declare    v_cnt Number;    v_sql Varchar(20480);    v_create_sql Varchar(20480);  begin    i_tablename := upper(i_tablename);    v_sql := 'drop table '||i_tablename;     v_create_sql := 'create table ' ||i_tablename ||' as select * from etl_320300.'||i_tablename;   select count(*) into v_cnt    from user_tables where table_name = i_tablename    ;    if v_cnt > 0 then      --插入log       SP_REPLACE_TABLE_LOG(i_tablename,'del');      --执行删除      EXECUTE IMMEDIATE (v_sql);     -- dbms_output.put_line(v_sql);    end If;      EXECUTE IMMEDIATE (v_create_sql);    --  dbms_output.put_line(v_create_sql);     SP_REPLACE_TABLE_LOG(i_tablename,'ins');  end;end;

0 0