Re: assigning variables based on an insertion...

In article <1103570060.216204.12940_at_c13g2000cwb.googlegroups.com>, ptomsic_at_gmail.com says...
>
>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
>GROUPS (ID)
>
>);
>
>
>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
>

variable mary_id number
exec begin select id into :mary_id from ...; end; insert into users_groups (user_id,group_id) values ( :mary_id, ... );

or

insert into users_groups (user_id,group_id) values ( (select id from ... ), (select id from groups where ... ) );

-- 
Thomas Kyte
Oracle Public Sector
http://asktom.oracle.com/
opinions are my own and may not reflect those of Oracle Corporation