On the move:

I'm currently moving the following to their own blogs: Unix, HTML for Blogs, Perl & Java.
The links are on the left side.

Friday, June 6, 2008

Use Variables to Insert Data














In this example, a user is asked for three kinds of variables: Character, Date & Number. They are then inserted into an 'INSERT INTO' statement. (In entering the date, the year is assumed to be the current one.) After insertion, a SELECT is performed to show the user the data entered as it is in the table.
Set Verify Off
Set Feedback Off
Prompt Please Enter...

--+------------------------------------------------------------------
--| Ask for data
--+------------------------------------------------------------------

Accept Who Prompt 'Name: '
Accept Month Format 99 Prompt 'Month: '
Accept Day Format 99 Prompt 'Day: '
Accept Amount Prompt 'Amount: '

--+- 1 --------------------------------------------------------------
--| Insert the record based on the values typed (Assume current year)
--+------------------------------------------------------------------

Insert Into TestData Values
(
Test_Seq.NextVal
,
Upper('&Who')
,
&Day
|| '-'
|| Decode(&Month, 1,'JAN', 2,'FEB', 3,'MAR', 4,'APR',
5,'MAY', 6,'JUN', 7,'JUL', 8,'AUG',
9,'SEP', 10,'OCT', 11,'NOV', 12,'DEC',
'???')
|| '-'
|| To_Char(Sysdate,'YY')
,
&Amount
);
Commit;

--+- 2 --------------------------------------------------------------
--| Get the current value
--+------------------------------------------------------------------

Set Term Off

Column CurrVal New_Value CurrVal

Select Test_Seq.Currval CurrVal
From Dual;

--+- 3 --------------------------------------------------------------
--| Display the record just entered.
--+------------------------------------------------------------------

Set Term On

Select *
From Test_Data
Where Test_Id = &CurrVal
;
Undefine Who Month Day Amount
Set Feedback On
Set Verify On

Oracle documentation:

No comments:

Post a Comment