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, August 15, 2008

Create a Sequence Script

Insert into table values ('Some Name',SeqID.NextVal);
  • To use that value more than once, use CurrVal after the first.
  • Can't be used in subqueries, DISTINCT, UNION, INTERSECT, MINUS, Order by, group by, having.

Define OnOff = Off
rem +--------------------------------------------------------------------------+
rem | Script ID: seq.sql
rem |
rem | Purpose: This creates a sequence that doesn't cycle. I'm not error
rem | checking -- I'll let Oracle tell that what's wrong. If it
rem | is a descending sequence, I'll put in the minus signs.
rem |
rem | Developer: Lynn Tobias
rem | Script Date: August 08, 2008
rem |
rem | Input File(s): none
rem |
rem | Table(s) Used: Dual
rem |
rem | Called by: n/a
rem | Calls: CrSeq.sql (generated in this script)
rem |
rem | Variables: Asc_Dsc - ascending/descending sequence
rem | Cache - choice for cache/nocache
rem | Increment_By - how many to increment by
rem | MaxMin - max for ascending, min for descending
rem | mval - what was keyed for max/min question
rem | NoOrder - order/noOrder
rem | OnOff - Used for debugging
rem | Schema - Schema name
rem | Seq_Name - Sequence name keyed
rem | Sign - negative if descending
rem | Start_With - choice for value to start with
rem |
rem | Output: n/a
rem |
rem | Revisions: Dvl Date Ver Comment/Change
rem | --- -------- --- -----------------------------------------+
rem |
rem +--------------------------------------------------------------------------+

Set Heading Off Echo &OnOff Verify &OnOff Feedback &OnOff Timing &OnOff Trimspool On

Prompt ******
Prompt * This creates a non-cycling sequence
Prompt ******
Prompt

--+- 1 -------------------------------------------------------------------------
--| Get the schema and the sequence name
--+-----------------------------------------------------------------------------

Accept Schema Default &_User -
Prompt 'Enter SCHEMA or press Enter to accept &_User: '
Prompt

Accept Seq_Name -
Prompt 'SEQUENCE Name to be created: '
Prompt
Select '***** This sequence already exists. It will be dropped. ******'
From All_Objects
Where Object_Type = 'SEQUENCE'
And Owner = Upper('&Schema')
And Object_Name = Upper('&Seq_Name');

--+- 3 -------------------------------------------------------------------------
--| Find out Ascending or Descending so it's a positive or negative increment
--| and either Max or Min is used.
--+-----------------------------------------------------------------------------

Prompt
Accept Asc_Dsc Default Asc -
Prompt 'Is this an ascending or descending sequence? (Default = Ascending) a/d: '
Prompt

Column Sign New_Value Sign
Column MaxMin New_Value MaxMin

Set Term &OnOff

Select Case When Upper('&ASC_DSC') in ('A','ASC','ASCENDING')
Then ' ' -- Ascending doesn't take a sign
Else '-' -- Descending needs a negative sign
End as Sign,
------------------------------------------------------
Case When Upper('&ASC_DSC') in ('A','ASC','ASCENDING')
Then 'MAX' -- Ascending is 'MAXVALUE'
Else 'MIN' -- Descending is 'MINVALUE'
End as MaxMin
------------------------------------------------------
From Dual;

Set Term On

--+- 4 -------------------------------------------------------------------------
--| Get the answers to Start_with, Increment_by, Max/MinValue, Cache, order
--+-----------------------------------------------------------------------------

Accept Start_With Default 1 -
Prompt 'START WITH (1st number to be generated) (Default = 1): '
Prompt

--+-----------------------------------------------------------------------------

Accept Increment_By Default 1 -
Prompt 'INCREMENT BY (interval between numbers)(Default = 1): '
Prompt

--+-----------------------------------------------------------------------------

Accept mval Default NO&MaxMin.VALUE -
Prompt '&MaxMin.VALUE The highest or lowest value that can be generated. (Default = No&MaxMin.Value): '
Prompt

--+-----------------------------------------------------------------------------

Accept Cache Default 20 -
Prompt 'CACHE (the # kept in memory for faster access) (Default = 20) or type ''No'' for NoCache: '
Prompt

--+-----------------------------------------------------------------------------

Accept NoOrder Default NoOrder -
Prompt 'ORDER (Important if a timestamp or RAC. (Default = NoOrder) o/n: ';
Prompt

--+- 5 -------------------------------------------------------------------------
--| Create the sequence code and spool
--+-----------------------------------------------------------------------------
--| Basic layout for reference
--| CREATE SEQUENCE [ schema. ]sequence
--| [ { INCREMENT BY | START WITH } integer
--| | { MAXVALUE integer | NOMAXVALUE }
--| | { MINVALUE integer | NOMINVALUE }
--| | { CYCLE | NOCYCLE }
--| | { CACHE integer | NOCACHE }
--| | { ORDER | NOORDER }
--+-----------------------------------------------------------------------------
Spool CrSeq.sql

Select --'Drop Sequence &Schema' ||'.&Seq_Name;' ||chr(10)||
'Create Sequence &Schema'||'.&Seq_Name' ||chr(10)||
'Start With &Sign&Start_With' ||chr(10)||
'Increment By &Sign&Increment_By' ||chr(10)||
-------------------------------------------------------------
Case When Upper('&mval') in ('N','NO','NOMAXVALUE', 'NOMINVALUE')
Then 'No&MaxMin'||'Value NOCYCLE'
Else '&MaxMin'||'Value &mval NOCYCLE'
End ||chr(10)||
-------------------------------------------------------------
Case When Upper('&Cache') in ('N','NO','NOCACHE')
Then 'NoCache'
Else 'Cache &Cache'
End ||chr(10)||
-------------------------------------------------------------
Case When Upper('&NoOrder') in ('N','NO','NOORDER')
Then 'NoOrder ;'
Else 'Order ;'
End
From Dual;

Spool off

--+- 6 -------------------------------------------------------------------------
--| Drop the sequence (just in case), but don't display so no error posted.
--| Turn feedback on so they see when the sequence is created.
--+-----------------------------------------------------------------------------

Set Term &OnOff
Drop Sequence &Schema..&Seq_Name;

Set Term On Feedback On
@CrSeq

Undefine Asc_Dsc Sign Maxmin Mval Schema Start_With Increment_By Cache Noorder _Rc Seq_Name
Set Heading On Verify On

No comments:

Post a Comment