Creating stored procedure to populate empty table using pl/sql

oracleoracle-11gplsqltrigger

I have created a table and inserted a values one row at a time. However, I am trying to populate the table using a "stored procedure" or "trigger". The values can be a random string or number. I have been researching on it, but couldn't find any related examples.

Could some please provide an example or suggest some reference to it? Cheers!!

CREATE TABLE BOOKING 
(
  BOOKING_ID VARCHAR2(7) PRIMARY KEY
, BOOKING_DATE DATE 
, BRANCH_ID VARCHAR2(7) 
, ROOM_PRICE NUMBER(15) 
, IS_BOOKED VARCHAR(5))
TABLESPACE TS02
PCTFREE 20  PCTUSED 70
STORAGE (INITIAL 200K NEXT 500K PCTINCREASE 20);

--to insert value to BOOKING table for one row per at a time
INSERT INTO BOOKING
VALUES ('HIM001','02-FEB-2016','KTM',500,'NO');

Best Answer

You can do it with simple SQL statements and DBMS_RANDON as demonstrated below:

CREATE TABLE BOOKING 
(
  BOOKING_ID VARCHAR2(7) PRIMARY KEY, BOOKING_DATE DATE 
, BRANCH_ID VARCHAR2(7) 
, ROOM_PRICE NUMBER(15) 
, IS_BOOKED VARCHAR(5));



--You may create a sequence object to generate IDs. 
CREATE SEQUENCE booking_id_seq
 START WITH     1
 INCREMENT BY   1
 NOCACHE
 NOCYCLE;

INSERT INTO booking
SELECT booking_id_seq.nextval, TRUNC(SYSDATE + DBMS_RANDOM.value(0,366)),
dbms_random.string('U',trunc(dbms_random.value(1,7))), trunc(dbms_random.value(1000,100000)), dbms_random.string('U',trunc(dbms_random.value(1,5)))
FROM  dual
CONNECT BY level <= 12;-- Here you can define the number of rows that you want to insert.




SQL> INSERT INTO booking
SELECT booking_id_seq.nextval, TRUNC(SYSDATE + DBMS_RANDOM.value(0,366)),
dbms_random.string('U',trunc(dbms_random.value(1,7))), trunc(dbms_random.value(1000,100000)), dbms_random.string('U',trunc(dbms_random.value(1,5)))
FROM  dual
CONNECT BY level <= 12;  

12 rows created.

SQL> select * from booking;

BOOKING BOOKING_D BRANCH_ ROOM_PRICE IS_BO
------- --------- ------- ---------- -----
158     06-NOV-16 ULQXM        81615 SH
159     18-DEC-16 RTG          99655 XDY
160     15-JUN-17 UF           45335 MCY
161     01-JUL-17 I            23249 FGS
162     13-MAY-17 KLLL          2177 T
163     07-AUG-17 HFC          10259 UKD
164     12-FEB-17 TVIZQY       98038 PJL
165     10-JUN-17 BIP          94314 ZSRS
166     22-JUL-17 ERQO         45985 QYC
167     27-AUG-16 EJKYB        39139 NJO
168     12-APR-17 AASMB        86838 NY

BOOKING BOOKING_D BRANCH_ ROOM_PRICE IS_BO
------- --------- ------- ---------- -----
169     24-FEB-17 NCQPMS       38296 Z

12 rows selected.

SQL>

Here are some good examples of using DBMS_RANDOM PL/SQL Package.

Related Question