存储过程的一个例子

 

spool gec13_merge_security_master.log;

prompt create temp table
select to_char(sysdate, 'HH24:MI:SS') as begin_time from dual;
CREATE table GECDBA.GEC_ASSET_TEMP
(
  ASSET_ID NUMBER(38, 0),
  CUSIP VARCHAR2(9),
  ISIN VARCHAR2(12),
  SEDOL VARCHAR2(7),
  QUIK VARCHAR2(5),
  TICKER VARCHAR2(50),
  DESCRIPTION VARCHAR2(50),
  SOURCE_FLAG VARCHAR2(1),
  TRADE_COUNTRY_CD VARCHAR2(3),
  ASSET_TYPE_ID VARCHAR2(1)
);


prompt insert to temp table
select to_char(sysdate, 'HH24:MI:SS') as begin_time from dual;
@gec_insert_to_temp.sql
commit;

prompt create index on temp table
select to_char(sysdate, 'HH24:MI:SS') as begin_time from dual;
create index GECDBA.GEC_ASSET_TEMP_IDX2 on GECDBA.GEC_ASSET_TEMP (CUSIP);



prompt alter table/trigger/package
select to_char(sysdate, 'HH24:MI:SS') as begin_time from dual;

alter table GECDBA.gec_asset add temp_asset_id number(38,0);

-- create temp package
@gec_asset_day0_pkg.sql

prompt set security in gec1.3
select to_char(sysdate, 'HH24:MI:SS') as begin_time from dual;
call GECDBA.GEC_ASSET_DAY0_PKG.UPDATE_ASSET_INVEST_TYPE();

prompt update trade country cd
select to_char(sysdate, 'HH24:MI:SS') as begin_time from dual;
call GECDBA.GEC_ASSET_DAY0_PKG.UPDATE_TRADE_COUNTRY_CD_TEMP();

--prompt update asset for I
--select to_char(sysdate, 'HH24:MI:SS') as begin_time from dual;
--call GECDBA.GEC_ASSET_DAY0_PKG.UPDATE_ASSET_FOR_I();


prompt update exist security
select to_char(sysdate, 'HH24:MI:SS') as begin_time from dual;
call GECDBA.GEC_ASSET_DAY0_PKG.UPDATE_OLD_RECORDS();

alter trigger GECDBA.gec_asset_tr disable;

prompt insert new records
select to_char(sysdate, 'HH24:MI:SS') as begin_time from dual;
call GECDBA.GEC_ASSET_DAY0_PKG.INSERT_NEW_RECORDS();

prompt insert identifies
select to_char(sysdate, 'HH24:MI:SS') as begin_time from dual;
call GECDBA.GEC_ASSET_DAY0_PKG.INSERT_IDENTIFIERS();

prompt update source flag to 'S'
select to_char(sysdate, 'HH24:MI:SS') as begin_time from dual;
call GECDBA.GEC_ASSET_DAY0_PKG.UPDATE_SOURCE_FLAG();

--prompt set load_data_flag  for corp bonds
--select to_char(sysdate, 'HH24:MI:SS') as begin_time from dual;
--call GECDBA.GEC_ASSET_DAY0_PKG.UPDATE_lOAD_FLAG();

commit;

prompt droping
select to_char(sysdate, 'HH24:MI:SS') as begin_time from dual;
DROP PACKAGE GECDBA.GEC_ASSET_DAY0_PKG;


UPDATE GECDBA.gec_asset set temp_asset_id = null;
commit;

alter table GECDBA.gec_asset drop column temp_asset_id;
alter trigger GECDBA.gec_asset_tr enable;
drop table GECDBA.GEC_ASSET_TEMP;

prompt finish
select to_char(sysdate, 'HH24:MI:SS') as end_time from dual;

spool off;

 

 

1. @gec_insert_to_temp.sql 表示执行这段SQL

2. gec_insert_to_temp.sql这个文件要放到C:/Documents and Settings/a501279下

3.在SQL DEVELOPER中调用一段存储过程，可以

call

test_file_pkg.generate_asset_insert('GEC_FILE_DIRECTORY','G1SEC_D1100930_T082007.DAT','insertAssetTempForCorpBond.txt');

 

或

begin

 proc();

end;