assigning variables based on an insertion...

how do you assign variables based on insertions? for instance, a sql script that does a lot of insertions then needs those IDs for future insertions into join tables.

Something like:

CREATE TABLE USERS (
ID NUMBER(10) NOT NULL,
NAME VARCHAR2(255) NOT NULL,
CONSTRAINT PK1_USERS PRIMARY KEY(ID)
);

CREATE TABLE GROUPS (
ID NUMBER(10) NOT NULL,
NAME VARCHAR2(255) NOT NULL,
CONSTRAINT PK1_GROUPS PRIMARY KEY(ID)
);

CREATE TABLE USERS_GROUPS (

USER_ID       NUMBER(10)    NOT NULL,
GROUP_ID      NUMBER(10)    NOT NULL ,
CONSTRAINT PK1_USERS_GROUPS  PRIMARY KEY ( USER_ID, GROUP_ID ),
CONSTRAINT FK1_USERS_GROUPS  FOREIGN KEY (USER_ID)  	REFERENCES USERS
(ID),
CONSTRAINT FK2_USERS_GROUPS  FOREIGN KEY (GROUP_ID)  	REFERENCES

INSERT INTO USERS (ID, NAME) VALUES (1, 'mary'); INSERT INTO USERS (ID, NAME) VALUES (2, 'joe');

EXECUTE :mary_id := SELECT ID FROM USERS WHERE NAME = 'mary';

INSERT INTO GROUPS (ID,NAME) VALUES (1,'accounting');

EXECUTE :acct_id := SELECT ID FROM GROUPS WHERE NAME = 'accounting';

INSERT INTO USERS_GROUPS (USER_ID, GROUP_ID) VALUES (&mary_id, &acct_id);

Is this possible?

thanks Received on Mon Dec 20 2004 - 13:14:20 CST