Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: assigning variables based on an insertion...
ptomsic_at_gmail.com wrote:
> 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
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 damorgan_at_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 =-----Received on Mon Dec 20 2004 - 19:02:33 CST