NESTED TABLE
FOREIGN KEY
MANY-TO-MANY RELATIONSHIP
まず、最も一般的なアプローチを使用して関係を実装します。関係のテーブルを作成します。CREATE TABLE tab_bus
( b_id NUMBER PRIMARY KEY
, bus_number VARCHAR2(9) NOT NULL
);
CREATE SEQUENCE seq_bus;
CREATE TABLE tab_driver
( d_id NUMBER PRIMARY KEY
, driver_name VARCHAR2(255) NOT NULL
);
CREATE SEQUENCE seq_driver;
* This source code was highlighted with Source Code Highlighter .
2つのフィールドの主キー(CREATE TABLE bus_driver
( bus_id NUMBER
, driver_id NUMBER
, CONSTRAINT pk_driver_bus PRIMARY KEY (bus_id, driver_id)
, CONSTRAINT fk_bus_id FOREIGN KEY (bus_id) REFERENCES tab_bus (b_id)
, CONSTRAINT fk_driver_id FOREIGN KEY (driver_id) REFERENCES tab_driver (d_id)
);
* This source code was highlighted with Source Code Highlighter .
PRIMARY KEY
COMMIT;
COMMIT;
ここでの選択手順では、CREATE PROCEDURE add_relation
( p_bus NUMBER
, p_driver NUMBER
) AS
BEGIN
INSERT INTO bus_driver VALUES ( p_bus, p_driver );
END ;
CREATE PROCEDURE drop_relation
( p_bus NUMBER
, p_driver NUMBER
) AS
BEGIN
DELETE bus_driver WHERE bus_id = p_bus AND driver_id = p_driver;
END ;
CREATE PROCEDURE select_relation
( p_data OUT SYS_REFCURSOR
) AS
BEGIN
OPEN p_data FOR
SELECT b.bus_number, d.driver_name
FROM tab_bus b, tab_driver d, bus_driver r
WHERE (b.b_id = r.bus_id) AND (r.driver_id = d.d_id);
END ;
* This source code was highlighted with Source Code Highlighter .
WHERE
JOIN
JOIN
既に述べたように、ネストされたテーブルに外部キーを作成することはできないため、独自の自転車を作成することは理にかなっています。これは間違いなく関係の処理速度に悪影響を及ぼします。CREATE OR REPLACE TYPE obj_list AS OBJECT
( r_driver NUMBER
);
CREATE OR REPLACE TYPE nt_list AS TABLE OF obj_list;
ALTER TABLE tab_bus ADD
( bus_drivers nt_list NULL
) NESTED TABLE bus_drivers STORE AS nt_bus_drivers;
* This source code was highlighted with Source Code Highlighter .
最後に、関係の手順を作成、削除、および選択します。 プロシージャの最後にある例外ブロックに注目する価値があります。 実際には、レコードの最初の値はCREATE FUNCTION check_fk
( p_id NUMBER
) RETURN NUMBER IS
fk_count NUMBER;
BEGIN
SELECT COUNT (d_id) INTO fk_count
FROM tab_driver WHERE d_id = p_id;
RETURN fk_count;
END ;
* This source code was highlighted with Source Code Highlighter .
UPDATE
INSERT
テーブルが空の場合に発生するため、削除手順には例外処理もあります。 例外の場合、探しているレコードはもう存在しないため、削除手順に必要なため、何もする必要はありません。CREATE PROCEDURE add_relation
( p_bus NUMBER
, p_driver NUMBER
) AS
BEGIN
IF (check_fk(p_driver) = 1) THEN
INSERT INTO TABLE
( SELECT bus_drivers
FROM tab_bus
WHERE b_id = p_bus
)
VALUES ( obj_list(p_driver) );
ELSE
RAISE_APPLICATION_ERROR(-20665, 'Record doesn' 't exist.' );
END IF ;
DBMS_OUTPUT.PUT_LINE( TO_CHAR( (DBMS_UTILITY.GET_TIME-start_time)/100, '09.99' ) );
EXCEPTION
WHEN OTHERS THEN
UPDATE tab_bus
SET bus_drivers = nt_list ( obj_list(p_driver) )
WHERE b_id = p_bus;
END add_relation;
CREATE PROCEDURE drop_relation
( p_bus NUMBER
, p_driver NUMBER
) AS
BEGIN
IF (check_fk(p_driver) = 1) THEN
DELETE TABLE
( SELECT bus_drivers
FROM tab_bus d
WHERE d.b_id = p_bus
) nt
WHERE nt.r_driver = p_bus;
END IF ;
EXCEPTION
WHEN OTHERS THEN
NULL ;
END drop_relation;
CREATE PROCEDURE select_relation
( p_data OUT SYS_REFCURSOR
) AS
BEGIN
OPEN p_data FOR
SELECT b.bus_number, d.driver_name
FROM tab_bus b, tab_driver d, TABLE (b.bus_drivers) r
WHERE (b.b_id = p_bus) AND (d.d_id = p_driver);
END ;
* This source code was highlighted with Source Code Highlighter .
INSERT INTO tab_driver VALUES ( seq_driver.NEXTVAL, 'Viktor Jeliseev' );
INSERT INTO tab_driver VALUES ( seq_driver.NEXTVAL, 'Stepan Kljavin' );
INSERT INTO tab_driver VALUES ( seq_driver.NEXTVAL, 'Marija Baranka' );
INSERT INTO tab_driver VALUES ( seq_driver.NEXTVAL, 'Arsenij Dubov' );
INSERT INTO tab_bus VALUES ( seq_bus.NEXTVAL, 'p666pp' );
INSERT INTO tab_bus VALUES ( seq_bus.NEXTVAL, 'LT-3216' );
INSERT INTO tab_bus VALUES ( seq_bus.NEXTVAL, 'zox-15' );
INSERT INTO tab_bus VALUES ( seq_bus.NEXTVAL, 'x234oo' );
BEGIN
add_relation (1, 3);
add_relation (1, 4);
add_relation (3, 3);
add_relation (3, 2);
add_relation (2, 2);
drop_relation (3, 2);
drop_relation (2, 2);
END ;
DECLARE
g_data SYS_REFCURSOR;
BEGIN
select_relation (1, 4, g_data);
select_relation (3, 3, g_data);
select_relation (2, 2, g_data);
select_relation (1, 1, g_data);
END ;
* This source code was highlighted with Source Code Highlighter .
テスト手順にもコミットがないことに注意してください。CREATE OR REPLACE
PROCEDURE random_insert_data
( record_count NUMBER
) AS
start_time NUMBER DEFAULT DBMS_UTILITY.GET_TIME;
counter NUMBER;
field_value VARCHAR2(255);
BEGIN
FOR counter IN 1..record_count
LOOP
field_value := DBMS_RANDOM.STRING( 'A' , 9);
INSERT INTO tab_bus (b_id, bus_number) VALUES
( seq_bus.NEXTVAL
, field_value
);
END LOOP;
FOR counter IN 1..record_count
LOOP
field_value := INITCAP(DBMS_RANDOM.STRING( 'L' , 6))|| ' ' ||INITCAP(DBMS_RANDOM.STRING( 'L' , 9));
INSERT INTO tab_driver VALUES
( seq_driver.NEXTVAL
, field_value);
END LOOP;
DBMS_OUTPUT.PUT_LINE( TO_CHAR( (DBMS_UTILITY.GET_TIME-start_time)/100, '09.99' ) );
END ;
-- for standart many-to-many relations
CREATE PROCEDURE random_insert_rel
( rel_count NUMBER
, rel_from NUMBER
, rel_to NUMBER
) AS
start_time NUMBER DEFAULT DBMS_UTILITY.GET_TIME;
rel1_value VARCHAR2(255);
rel2_value VARCHAR2(255);
counter NUMBER;
BEGIN
FOR counter IN 1..rel_count
LOOP
rel1_value := ROUND(DBMS_RANDOM. VALUE (rel_from, rel_to));
rel2_value := ROUND(DBMS_RANDOM. VALUE (rel_from, rel_to));
add_relation(rel1_value, rel2_value);
END LOOP;
DBMS_OUTPUT.PUT_LINE( TO_CHAR( (DBMS_UTILITY.GET_TIME-start_time)/100, '09.99' ) );
END ;
-- for standart many-to-many relations
CREATE PROCEDURE random_delete_rel
( rel_count NUMBER
, rel_from NUMBER
, rel_to NUMBER
) AS
start_time NUMBER DEFAULT DBMS_UTILITY.GET_TIME;
rel1_value VARCHAR2(255);
rel2_value VARCHAR2(255);
counter NUMBER;
BEGIN
FOR counter IN 1..rel_count
LOOP
rel1_value := ROUND(DBMS_RANDOM. VALUE (rel_from, rel_to));
rel2_value := ROUND(DBMS_RANDOM. VALUE (rel_from, rel_to));
drop_relation(rel1_value, rel2_value);
END LOOP;
DBMS_OUTPUT.PUT_LINE( TO_CHAR( (DBMS_UTILITY.GET_TIME-start_time)/100, '09.99' ) );
END ;
-- for alternative many-to-many system with nested table
CREATE PROCEDURE random_insert_rel
( rel_count NUMBER
, rel_from NUMBER
, rel_to NUMBER
) AS
start_time NUMBER DEFAULT DBMS_UTILITY.GET_TIME;
rel1_value VARCHAR2(255);
rel2_value VARCHAR2(255);
counter NUMBER;
BEGIN
FOR counter IN 1..rel_count
LOOP
rel1_value := ROUND(DBMS_RANDOM. VALUE (rel_from, rel_to));
rel2_value := ROUND(DBMS_RANDOM. VALUE (rel_from, rel_to));
add_relation(rel1_value, rel2_value);
END LOOP;
DBMS_OUTPUT.PUT_LINE( TO_CHAR( (DBMS_UTILITY.GET_TIME-start_time)/100, '09.99' ) );
END ;
-- for alternative many-to-many system with nested table
CREATE PROCEDURE random_delete_rel
( rel_count NUMBER
, rel_from NUMBER
, rel_to NUMBER
) AS
start_time NUMBER DEFAULT DBMS_UTILITY.GET_TIME;
rel1_value VARCHAR2(255);
rel2_value VARCHAR2(255);
counter NUMBER;
BEGIN
FOR counter IN 1..rel_count
LOOP
rel1_value := ROUND(DBMS_RANDOM. VALUE (rel_from, rel_to));
rel2_value := ROUND(DBMS_RANDOM. VALUE (rel_from, rel_to));
drop_relation(rel1_value, rel2_value);
END LOOP;
DBMS_OUTPUT.PUT_LINE( TO_CHAR( (DBMS_UTILITY.GET_TIME-start_time)/100, '09.99' ) );
END ;
* This source code was highlighted with Source Code Highlighter .
ウェイレコーディング | サンプル1 | サンプル2 | サンプル3 | サンプル4 | サンプル5 | の作成 |
追加します。 表4 | 00.844 | 00.792 | 00.967 | 01.01 | 02.02 | 00.032 |
c。 表4 | 00.694 | 00.707 | 00.026 | 00.00 | 00.00 | 00.034 |
追加します。 テーブル、1000 | 00.642 | 00.645 | 00.698 | 02.02 | 02.02 | 00.142 |
c。 テーブル、1000 | 00.687 | 00.695 | 00.344 | 00.00 | 00.00 | 00.721 |
追加します。 テーブル、100000 | 00.648 | 00.697 | 01.323 | 00.14 | 00.49 | 14.613 |
c。 テーブル、100000 | 00.741 | 00.829 | 11/11/7 | 00.00 | 00.00 | 84.630 |
SELECT b.bus_number、d.driver_name
FROM tab_bus b、tab_driver d、 bus_driver r WHERE(b.b_id = r.bus_id) AND(r.driver_id = d.d_id); | SELECT b.bus_number、d.driver_name
FROM tab_bus b、tab_driver d、 TABLE(b.bus_drivers)r WHERE(b.b_id = p_bus) AND(d.d_id = p_driver); |