我有大约 15 个不同的表,其中填充了不同的数据和不同的实体关系。
我需要创建一个脚本,用这些表的内容填充我的数据库。
脚本完成后,我使用 sqlplus 在 cmd 中运行它,然后使用 START文件路径
我有两个不同的 sql 文件,一个名为 db_spec.sql,另一个名为 db_body.sql。
在我的 db_body.sql 中,我创建了一个过程来存储具有 1:N 关系的两个表中的数据。
第一桌
CREATE TABLE LOCATION (
ID_LOCATION INTEGER NOT NULL,
LOCATION_NAME VARCHAR2 (20) NOT NULL,
POSTCODE INTEGER NOT NULL
);
ALTER TABLE LOCATION
ADD (CONSTRAINT PK_LOCATION PRIMARY KEY (ID_LOCATION));
第二张桌子
CREATE TABLE ADDRESS (
ID_ADDRESS INTEGER NOT NULL,
STREET VARCHAR2 (20) NOT NULL,
HOUSE_NUMBER INTEGER NOT NULL,
FK_ID_LOCATION INTEGER NOT NULL
);
ALTER TABLE ADDRESS
ADD (CONSTRAINT PK_ADRESS PRIMARY KEY (ID_ADRESS));
ALTER TABLE ADRESS
ADD (CONSTRAINT FK_ADRESS_ID_LOCATION FOREIGN KEY
(FK_ID_LOCATION) REFERENCES LOCATION(ID_LOCATION));
现在我需要使用数据填充它们。比方说
LOCATION_NAME =“伦敦”
POSTOCDE =“394505”...等等
我已经创建了这个脚本,但是当我运行它时,没有任何显示,所以其中显然存在一些错误。
db_spec.sql 脚本
CREATE OR REPLACE PACKAGE apartment AS
PROCEDURE fill_location(location_number NUMBER);
PROCEDURE fill_address(number_of_addresses NUMBER);
END apartment;
db_body.sql 脚本
SET SERVEROUTPUT ON
SET LINESIZE 400
SET TIMING ON
CREATE OR REPLACE PACKAGE BODY address AS
PROCEDURE fill_location(location_number NUMBER) IS
p_location_name VARCHAR2(20);
p_postcode NUMBER (10,2);
BEGIN
FOR num IN 1..location_number LOOP
p_location_name := 'Location';
p_postcode := dbms_random.value(1000,9600);
p_postcode := p_postcode ||' '|| TO_CHAR(num);
INSERT INTO LOCATION (ID_LOCATION, LOCATION, POSTCODE)
VALUES (num, p_location_name, p_postcode);
dbms_output.put_line(num);
END LOOP;
END fill_location;
PROCEDURE fill_address(number_of_adresses NUMBER)IS
p_street_name VARCHAR(20);
p_house_number NUMBER (10,2);
p_id_address NUMBER(10);
CURSOR data IS
SELECT ID_LOCATION
FROM LOCATION;
BEGIN
FOR num_loop IN data LOOP
FOR num IN 1..number_of_adresses LOOP
p_street_name := 'Ulica';
p_house_number := dbms_random.value(1,99);
p_street_name := p_street_name ||' '|| TO_CHAR(num);
SELECT NVL(MAX(p_id_address)+1,1)
INTO p_id_address
FROM ADDRESS;
INSERT INTO ADDRESS (ID_ADDRESS, FK_ID_LOCATION, STREET, HOUSE_NUMBER)
VALUES (p_id_address, num_loop.ID_LOCATION, p_street_name, p_house_number);
dbms_output.put_line(num_loop.ID_LOCATION);
END LOOP;
END LOOP;
END fill_address;
END;
SHOW ERRORS;
你们能帮我解决这个问题,以便代码可以正常运行吗?任何意见表示赞赏!
Btw oprema_stanovanja.polni_kraj = 地址.fill_location