How to Loop in SQL

I recently saw a request for a For Loop executed with plain old SQL so I decided to create my own version.

The report you see was built by running the code once for each character in the name.

This required four queries: one to set it up, and one to end it. The other two are the actual code, and then one to decide whether to loop or not.

(If you want to see everything behind the scenes, set OnOff in SetUpLoop to 'On'. This will turn on feedback, verify, echo and the terminal wherever those statements are found.)

SetUpLoop creates a temp table to hold the Counter and then calls the query that determines where we are in the loop.

SetUpLoop.SQL
Define OnOff = Off Set Verify &OnOff Feedback &OnOff Echo &OnOff Create Global Temporary Table GTT_CountHolder On Commit Preserve Rows As Select 1 As Counter From Dual; @Looper

Looper uses case statements to set two amper-variables. They are both used with the '@' sign to first either run the code or, at the end drop the temp table. The second tells the system to call this same query again, or just drop out.

The terminal is turned off unless debugging so the output from this query is not shown to the user.

Looper.SQL

Set Term &OnOff Column RunProg1 New_Value Run_Code_Or_End column RunProg2 New_Value Run_Loop_Or_Stop Select Case When Counter <= (Select Length(Ename) From Emp Where Rownum <2) Then 'Code' Else 'EndLoop' End As RunProg1, -------------------------------------------------- Case When Counter <= (Select Length(Ename) From Emp Where Rownum <2) Then 'Looper' Else ' ' End As RunProg2 From GTT_CountHolder ; Set Term On @&Run_Code_Or_End @&Run_Loop_Or_Stop

Code is the actual code that we want to run multiple times. It runs the query and then updates the Counter in the temp table.

Code.SQL

Column Character Format A9 Select Ename , ------------------------------- -- Show position ------------------------------- (Select Counter from GTT_CountHolder ) As Position, -------------------------------- -- Show character -- Substr(field,position,length) -------------------------------- Substr(Ename /* Field */ , (Select Counter from GTT_CountHolder ) /* Position */ ,1) /* Length */ As Character ------------------------------- From Emp Where Rownum < 2; Update GTT_CountHolder Set Counter = Counter + 1;

EndLoop is just a little clean-up.

EndLoop.SQL
Truncate Table GTT_CountHolder ; Drop Table GTT_CountHolder ;

Validate a Date

This function validates a date and returns a boolean value. The date must be in one of these formats:

d-mmm-yy

dd-mmm-yy

d-mmm-yyyy

dd-mmm-yyyy

It will return either True for a valid date or False for an invalid date. The month can be capitalized or not.

An example of a call to the function:

Set Serveroutput On
Begin
   Dbms_Output.Put_Line(Case When ValDt('&test_Date')
                             Then 'This is valid' 
                             Else 'This is invalid' 
                         End
                       );
End;
/

rem +--------------------------------------------------------------------------+ rem | Script ID: ValDt.sql rem | rem | Purpose: This function is looking for a date in one of these formats: rem | d-mmm-yy dd-mmm-yy d-mmm-yyyy dd-mmm-yyyy rem | It will return either True for a valid date or rem | False for an invalid date rem | rem | Developer: Lynn Tobias rem | Script Date: 11/14/2008 rem | Oracle Ver: 10g rem +--------------------------------------------------------------------------+ Create Or Replace Function ValDt(Test_Date Varchar2) Return Boolean As v_Return Boolean ; v_Day Number(2) ; v_Mth Varchar2(3); v_Yr Number(4) ; Begin v_Day := Substr(Test_Date,1,Instr(Test_Date,'-',1,1) -1 ) ; v_Mth := Substr(Test_Date,Instr(Test_Date,'-',1, 1 ) +1 ,Instr(Test_Date,'-',1, 2 ) -Instr(Test_Date,'-',1, 1 ) -1 ) ; v_Yr := Substr(Test_Date,Instr(Test_Date,'-',1, 2 ) +1 ) ; v_Return := Case When v_Day Between 1 And To_Char( Last_Day('01-'||v_Mth||'-'||v_Yr) ,'dd' ) Then True Else False End; Return v_Return; End; /

Create a New User

--+------------------------------------------------------------
--| CrUser.Sql
--| Script To Create New User
--+------------------------------------------------------------
Accept UserName Prompt 'Enter name of user to create: '

Drop User &UserName Cascade;

Create User &UserName 
   Identified By &UserName
   Default Tablespace Users
   --Temporary Tablespace Temp  /* automatic in 10 and 11 */
   Quota 0 On System
   Quota Unlimited On Users
   --Quota Unlimited On Indx
   Password Expire;

--+------------------------------------------------------------
--| Allows User To Logon
--+------------------------------------------------------------
Grant Connect To &UserName;    

--+------------------------------------------------------------
--| Provides User With Tablespace
--+------------------------------------------------------------
Grant Resource To &UserName;  

--+------------------------------------------------------------
--| Get connected to system instead of typing connect user/user
--+------------------------------------------------------------
Grant Create Session To &UserName; 

Basic code received from anonymous DBA

Display Active Database Sessions

The code was just a tad long, and nothing particularly interesting, so it can be downloaded from my web site.

It is named Active_DB.sql

Oracle's HR Schema

I'm trying to break up with Scott.

This is one of the new schemas Oracle creates as a learning tool.

Note all table names are plural.

HR is for Human Resources.

I have this shown as a model from the new Oracle SQL Developer Data Modeling.



For more detail, see Oracle documentation on HR.

Update a Table Based on Data from Another Table

This first update is supplying data to TableA from TableB if either field is missing.

Update TableA a
   Set (
         a.Field1
       , a.Field2
       ) = (
            Select b.Field1
                 , b.Field2
              From TableB b
             Where b.Key = a.Key
           )
Where Field1 Is Null
   Or Field2 Is Null;

The second example updates TableA with the data from TableB. The 'Where Exists' is necessary if TableA has records with keys that does not find a match on TableB. Without it, the data in TableA would be set to null when a key can't be found in TableB.

Update TableA a
   Set (
         a.Field1
       , a.Field2
       ) = (
            Select b.Field1
                 , b.Field2
              From TableB b
              Where b.Key = a.Key
           )
Where Exists (
              Select 1
                From TableB b
               Where b.Key = a.Key
             );

For examples and more details, see an article written by Duke Ganote at http://tinyurl.com/2vphxg.

Flashback Table to One Minute Ago

What to do when you've messed up?

Alter Table table1
  Enable Row Movement;

Flashback Table table1
  To Timestamp (Systimestamp - Interval '1' Minute);