最近一直做数据仓库毕业论文实验，不时需要写点小程序来生成数据。现在记录下来，以当回忆。

下面procdurce运行环境：
NLS_DATE_LANGUAGE = AMERICAN
NLS_DATE_FORMAT = yyyy-mm-dd

如果不是，修要临时环境
ALTER session SET NLS_DATE_LANGUAGE = 'AMERICAN'
ALTER session SET NLS_DATE_FORMAT = 'yyyy-mm-dd'

SET SERVEROUTPUT ON

DECLARE
v_msisdn INTEGER;
v_customer INTEGER(20);
v_minute INTEGER;
v_date VARCHAR2(10);
v_call_type VARCHAR2(10);
v_sql VARCHAR2(200);
v_year VARCHAR2(10);
v_month VARCHAR2(10);
v_day VARCHAR2(10);
v_quarter VARCHAR2(10);
v_time VARCHAR2(20);
v_tmp VARCHAR2(20);
v_tmp2 VARCHAR2(20);
v_tmp3 VARCHAR2(20);

TYPE HASH IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
day_tab HASH;
month_tab HASH;
quarter_tab HASH;
year_tab HASH;

BEGIN
DBMS_OUTPUT.ENABLE(1000000);
v_msisdn := '3481400';
v_customer := '17115100';
v_minute := '0';
v_year := '2008';

FOR v_temp IN 1 .. 100
LOOP
v_msisdn := v_msisdn + 1;
v_customer := v_customer + 1;
v_minute := TRUNC(dbms_random.VALUE(0,100));

DBMS_OUTPUT.PUT_LINE(v_msisdn||'|'|| v_customer ||'|'||v_minute);

v_month := TRUNC(dbms_random.VALUE(1,12));
IF LENGTH(v_month) = 1
THEN
v_month := '0'||v_month;
END IF;

v_day := TRUNC(dbms_random.VALUE(1,28));
v_time := v_year||'-'||v_month||'-'||v_day;
v_date := TO_DATE(v_time,'yyyy-mm-dd');

DBMS_OUTPUT.PUT_LINE(v_year||' '||v_month||' '||v_day||' '||v_time||' '||v_date);

IF NOT day_tab.EXISTS(v_year||v_month||v_day)
THEN
v_sql := 'insert into smc_jup_cdr_detail(customer_id,cust_num_id,start_datetime,deducted_time,call_type) '||
' values('''||v_customer||''','''||v_msisdn||''','''||v_date||''','''||v_minute||''',''1'')';
DBMS_OUTPUT.PUT_LINE(v_sql);
EXECUTE IMMEDIATE v_sql;

day_tab(v_year||v_month||v_day) := '1';
v_sql := '';

v_tmp := TO_CHAR(TO_DATE(v_time,'yyyy-mm-dd'),'yyyymm');

v_sql := 'insert into hp_day(day_id,month_id) values('''||v_date||''','''||v_tmp||''')';
--DBMS_OUTPUT.PUT_LINE(v_sql);
EXECUTE IMMEDIATE v_sql;




v_sql := '';
v_quarter := v_year||'-Q'||TO_CHAR(TO_DATE(v_time,'yyyy-mm-dd'),'Q');

IF NOT month_tab.EXISTS(v_tmp)
THEN
v_sql := 'insert into hp_month(month_id,quarter_id) values('''||v_tmp||''','''||v_quarter||''')';
--DBMS_OUTPUT.PUT_LINE(v_sql);
EXECUTE IMMEDIATE v_sql;

month_tab(v_tmp) := '1';
END IF;

v_sql := '';
v_tmp2 := 'FY'||v_year;
v_tmp3 := TO_CHAR(TO_DATE(v_time,'yyyy-mm-dd'),'Q');
IF NOT quarter_tab.EXISTS(v_tmp3)
THEN
v_sql := 'insert into hp_quarter(year_id,quarter_id) values('''||v_tmp2||''','''||v_quarter||''')';
--DBMS_OUTPUT.PUT_LINE(v_sql);
EXECUTE IMMEDIATE v_sql;

quarter_tab(v_tmp3) := '1';
END IF;

v_sql := '';
v_tmp3 := v_year;

IF NOT year_tab.EXISTS(v_tmp3)
THEN
v_sql := 'insert into hp_year(year_id) values('''||v_tmp2||''')';
--DBMS_OUTPUT.PUT_LINE(v_sql);
EXECUTE IMMEDIATE v_sql;

year_tab(v_tmp3) := '1';
END IF;
END IF;

COMMIT;

v_sql := '';
v_time := '';
v_tmp := '';
v_tmp2 := '';
END LOOP;
END;