assigning variables based on an insertion...

[pto...@gmail.com]

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

[Thomas Kyte]

In article <1103570060....@c13g2000cwb.googlegroups.com>,
pto...@gmail.com says...

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

[pto...@gmail.com]

Thanks, what client will this work in (the first suggestion that you
offer, using the variables)?

TOAD doesn't seem to support variables

[Thomas Kyte]

In article <1103580427.1...@z14g2000cwz.googlegroups.com>,
pto...@gmail.com says...

>
>Thanks, what client will this work in (the first suggestion that you
>offer, using the variables)?
>
>TOAD doesn't seem to support variables
>

you'd have to ask the toadies I guess -- i assumed you were using the scripting
tool that everyone has -- sqlplus.

[DA Morgan]

pto...@gmail.com wrote:

Another possible solution:

DECLARE

myvar users.name%TYPE;

BEGIN
SELECT ...
INTO myvar
FROM users;

... do something else with myvar
END;
/

One query you should definitely consider adding to your
project is:

SELECT keyword
FROM v$reserved_words
WHERE keyword like 'NA%';

and

SELECT keyword
FROM v$reserved_words
WHERE keyword like 'I%';

If you can't see v$reserved_words ask your DBA to grant you
select on it.
--
Daniel A. Morgan
University of Washington
damo...@x.washington.edu
(replace 'x' with 'u' to respond)

-----------== Posted via Newsfeed.Com - Uncensored Usenet News ==----------
http://www.newsfeed.com The #1 Newsgroup Service in the World!
-----= Over 100,000 Newsgroups - Unlimited Fast Downloads - 19 Servers =-----

[Prasad Raju]

You can also do this

Declare
lUserId Users.Id%Type;
lGroupdID Groups.Id%Type;
Begin
Insert Into Users(Id,Name) Values(1,'mary') returning Id into
lUserId;
Insert Into Groups(Id,Name) Values(1,'accounting')returning Id into
lGroupId;

Insert Into Users_Groups(User_Id,Group_Id) Values(lUserId,lGroupId);
End;
/

[pto...@gmail.com]

ah, that works very well. Thank you