Page 685
Oracle DBA Code Examples
V_ORDER_STATUS NUMBER;
V_SALES_REP_ID NUMBER;
V_PRODUCT_ID NUMBER;
V_PROD_PRICE NUMBER;
V_CHANGE_PRICE NUMBER;
V_QUANTITY NUMBER;
V_ORDER_TOTAL NUMBER;
V_ITEMS_COUNT NUMBER;
N NUMBER;
M NUMBER;
-- 97 customers
TYPE CUST_ID_TYPE IS VARRAY(100) OF INTEGER;
V_CUST_IDS CUST_ID_TYPE := CUST_ID_TYPE
(341,342,343,344,345,346,347,348,349,350,351,352,360,361,363,378,380,447,448,4
49,450,451,452,453,454,458,463,466,467,468,470,473,474,475,476,477,478,479,480
,481,482,483,487,488,492,496,605,606,607,609,615,621,627,712,713,715,717,719,7
21,727,729,731,754,755,756,757,766,767,768,769,770,771,772,782,825,826,827,828
,829,830,831,832,833,834,835,836,837,838,839,840,841,842,843,844,845,846,847);
-- 275 PRODUCTS
TYPE PROD_ID_TYPE IS VARRAY(275) OF INTEGER;
V_PROD_IDS PROD_ID_TYPE := PROD_ID_TYPE
(1750,1755,1761,1763,1768,1769,1770,1772,1774,1775,1778,1779,1780,1781,1782,17
87,1788,1791,1792,1794,1797,1799,1801,1803,1804,1805,1806,1808,1820,1822,1825,
1910,1912,1940,1948,2004,2005,2030,2049,2056,2058,2091,2093,2144,2152,2211,223
1,2236,2243,2245,2252,2253,2254,2255,2257,2259,2260,2261,2262,2264,2266,2268,2
270,2272,2274,2276,2278,2289,2293,2299,2302,2308,2311,2316,2319,2322,2323,2326
,2330,2334,2335,2336,2337,2339,2340,2350,2351,2359,2365,2370,2371,2373,2374,23
75,2377,2378,2380,2381,2382,2384,2387,2394,2395,2396,2400,2402,2403,2404,2406,
2408,2409,2410,2411,2412,2414,2415,2416,2417,2418,2419,2422,2423,2424,2430,243
9,2449,2452,2453,2457,2459,2462,2464,2467,2468,2470,2471,2492,2493,2494,2496,2
497,2522,2536,2537,2594,2596,2631,2638,2721,2722,2725,2751,2752,2761,2779,2782
,2783,2808,2810,2870,2878,2879,2944,2976,2982,2986,2995,2999,3000,3001,3003,30
04,3020,3051,3054,3057,3060,3061,3064,3065,3069,3071,3072,3073,3077,3082,3083,
3086,3087,3088,3090,3091,3097,3099,3101,3106,3108,3110,3112,3114,3117,3123,312
4,3127,3129,3133,3134,3139,3140,3143,3150,3155,3163,3165,3167,3170,3171,3172,3
173,3175,3176,3177,3178,3179,3182,3183,3187,3189,3191,3193,3197,3204,3208,3209
,3216,3220,3224,3225,3234,3245,3246,3247,3248,3250,3251,3252,3253,3255,3256,32
57,3258,3260,3262,3277,3290,3300,3301,3331,3334,3337,3350,3353,3354,3355,3359,
3361,3362,3391,3399,3400,3501,3502,3503,3511,3515);
BEGIN
FOR I IN 1..P_ROWS LOOP
SELECT SEQ_ORDER_ID.NEXTVAL INTO V_ORDER_ID FROM DUAL;
V_ORDER_DATE := TRUNC(SYSDATE- P_DAYS );
N := ROUND(DBMS_RANDOM.VALUE(1,2));
IF N = 1 THEN
V_ORDER_MODE := 'direct';
ELSE
V_ORDER_MODE := 'online';
END IF;
V_CUSTOMER_ID := ROUND(DBMS_RANDOM.VALUE(1,96));
V_ORDER_STATUS:= ROUND(DBMS_RANDOM.VALUE(1,10));
V_SALES_REP_ID:= ROUND(DBMS_RANDOM.VALUE(153,163));
INSERT INTO ORDERS
(ORDER_ID,ORDER_DATE,ORDER_MODE,CUSTOMER_ID,ORDER_STATUS,
ORDER_TOTAL,SALES_REP_ID)
VALUES
( V_ORDER_ID,
V_ORDER_DATE,
V_ORDER_MODE,