OraFAQ Forum: SQL & PL/SQL » consistency constraint ON store Procedure

Home » SQL & PL/SQL » SQL & PL/SQL » consistency constraint ON store Procedure (12c, window)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

consistency constraint ON store Procedure [message #671287]

Tue, 21 August 2018 01:39

Bilal Khan
Messages: 128
Registered: April 2010
Location: Pakistan

Senior Member

Dear Experts.
i am creating procedure but need help.
i create table and then inserted data like:

CREATE TABLE TRIPLEG
    (
        T#                NUMBER(10)       NOT NULL,
        LEG#              NUMBER(2)        NOT NULL,
        DEPARTURE         VARCHAR(30)      NOT NULL,
        DESTINATION       VARCHAR(30)      NOT NULL,
        CONSTRAINT  TRIPLEG_PKEY PRIMARY KEY (T#, LEG#)
     );

INSERT INTO TRIPLEG VALUES( 1, 1, 'Sydney', 'Melbourne');
INSERT INTO TRIPLEG VALUES( 1, 2, 'Melbourne', 'Adelaide');
INSERT INTO TRIPLEG VALUES( 1, 3, 'Adelaide', 'Sydney');
INSERT INTO TRIPLEG VALUES( 2, 1, 'Sydney', 'Melbourne');
INSERT INTO TRIPLEG VALUES( 2, 2, 'Melbourne', 'Adelaide');
INSERT INTO TRIPLEG VALUES( 2, 3, 'Adelaide', 'Sydney');
INSERT INTO TRIPLEG VALUES( 3, 1, 'Sydney', 'Melbourne');
INSERT INTO TRIPLEG VALUES( 3, 2, 'Melbourne', 'Adelaide');
INSERT INTO TRIPLEG VALUES( 3, 3, 'Adelaide', 'Sydney');
INSERT INTO TRIPLEG VALUES( 4, 1, 'Sydney', 'Melbourne');

we have data like this:

     T#       LEG# DEPARTURE                      DESTINATION
---------- ---------- ------------------------------ ------------------------------
         1          1 Sydney                         Melbourne
         1          2 Melbourne                      Adelaide
         1          3 Adelaide                       Sydney
         2          1 Sydney                         Melbourne
         2          2 Melbourne                      Adelaide
         2          3 Adelaide                       Sydney
         3          1 Sydney                         Melbourne
         3          2 Melbourne                      Adelaide
         3          3 Adelaide                       Sydney
         4          1 Sydney                         Melbourne

after that i create a simple store procedure like

CREATE OR REPLACE PROCEDURE INSERT_TRIPLEG
(
V_T# IN TRIPLEG.T#%TYPE,
V_LEG# IN TRIPLEG.LEG#%TYPE,
V_DEPARTURE IN TRIPLEG.DEPARTURE%TYPE,
V_DESTINATION IN TRIPLEG.DESTINATION%TYPE
)
IS 
BEGIN

INSERT INTO TRIPLEG(T#, LEG#, "DEPARTURE", "DESTINATION")
VALUES (V_T#, V_LEG#, V_DEPARTURE, V_DESTINATION);


COMMIT;
END;

i want to enforces the following consistency constraint on table TRIPLEG.

For all trip legs that belong to the same trip and such that leg_number > n where n > 1

a value of departure attribute must be equal to a value of destination attribute in a trip leg
where leg_number = n-1.
It means that for all trip legs except the first one a departure location must be the same as a destination location of the previous trip leg.

To simplify the problem, assume that the rows are inserted into a relational table TRIPLEG in the increasing order of an attribute leg_number and that a row where a value of attribute leg_number = n is immediately followed by a row where a value of attribute leg_number = n + 1.

please help me to handle this problem, thanks and regards,