創建儲存過程以使用 pl/sql 填充空表

我創建了一個表並一次插入一行值。但是，我正在嘗試使用“儲存過程”或“觸發器”來****填充表。這些值可以是隨機字元串或數字。我一直在研究它，但找不到任何相關的例子。

有人可以提供一個例子或建議一些參考嗎？乾杯！！

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');

您可以使用簡單的 SQL 語句和DBMS_RANDON來完成，如下所示：

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> 

下面是一些使用 DBMS_RANDOM PL/SQL 包的好例子。

引用自：https://dba.stackexchange.com/questions/147955