如何使用过程填充数据库 [英] how to populate database using procedures
问题描述
我大约有15个不同的表,其中填充了不同的数据和不同的实体关系.
I have about 15 different Tables filled with different data and different entity relationships.
我需要创建一个脚本,用这些表的内容填充数据库.
I need to create a script which will populate my database with the content of those tables.
脚本完成后,我使用sqlplus在cmd中运行它,然后再启动文件路径
After script is finished, i run it in cmd, using sqlplus and later START path to file
我有两个不同的sql文件,一个名为db_spec.sql,另一个名为db_body.sql.
i have two different sql files, one named db_spec.sql and another db_body.sql.
在db_body.sql中,我创建了一个过程来存储来自两个具有1:N关系的表的数据.
In my db_body.sql i've created a procedure to store data from two tables which have 1:N relationship.
第一张桌子
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));
现在我需要使用数据填充它们.比方说
Now i need to populate them using data. Let's say
LOCATION_NAME =伦敦" POSTOCDE ="394505" ...依此类推
LOCATION_NAME = "London" POSTOCDE = "394505" ...and so on
我已经创建了这个脚本,但是当我运行它时,什么都没有显示出来,因此其中显然有一些错误.
I've created this script, but as i run it, nothing shows up, so there's obviously some error in it.
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;
你们能帮助我解决问题,以便代码正常运行吗?任何输入表示赞赏!
顺便说一句 oprema_stanovanja.polni_kraj = address.fill_location
推荐答案
首先,发布的程序包无法正确编译.
Firstly, the package posted doesn't compile without errors.
您首先需要修复以下问题:
You would need to fix the following first:
- 用
END LOOP
终止FOR LOOP
语句 - 用
END <procedure_name_here>
终止过程BEGIN
- PL/SQL变量名称必须与实际的列名称不同,以避免INSERT语句中的歧义. [您不能使
LOCATION
列名称和PL/SQL变量具有相同的名称. Oracle无法解析在INSERT中的哪个位置将使用哪个]
- Terminate
FOR LOOP
statements withEND LOOP
- Terminate
BEGIN
of procedures withEND <procedure_name_here>
- PL/SQL variable names need to be different than the actual column name to avoid ambiguity in the INSERT statement. [ You can't have
LOCATION
the column name and PL/SQL variable with the same name. Oracle cannot resolve which is to be used where in the INSERT]
为使您入门,我已修复了以下fill_location
程序.对于其他过程,以类似的方式进行.
To get you started, I have fixed the fill_location
procedure below. Proceed similarly for the other procedure.
create or replace package body apartment 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_location_name := p_location_name ||' '|| to_char(num);
insert into location (id_location, location_name, postcode)
values (num, p_location_name, p_postcode);
dbms_output.put_line(num);
end loop;
end fill_location;
修复以上错误/建议,直到获得"PL/SQL成功编译".如果您被错误编译",请使用显示错误"来查找和修复任何其他错误.
Fix the above errors/suggestions until you get 'PL/SQL successfully compiled`. If you get 'compiled with errors' use "show errors" to find and fix any other errors.
这篇关于如何使用过程填充数据库的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!