Re: T-SQL to PL/SQL Please

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 18 Apr 2002 06:56:48 -0700
Message-ID: <a9mjb0023i1@drn.newsguy.com>

In article <g4msbusrm2a3ma59rtunc9fihoog9b2arf_at_4ax.com>, Steve says...
>
>Can anyone make a literal conversion of the following T-SQL script to
>Oracle PL/SQL for me?
>I have no idea where to start.
>
>Any help greatly appreciated,
>Steve.
>
>DECLARE @FLOWCONTROL INTEGER
>SET @FLOWCONTROL = @@ERROR
>
>IF @FLOWCONTROL = 0
> BEGIN
> PRINT ' '
> PRINT 'SetScript.sql STEP 1'
> IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.tables WHERE
>TABLE_NAME = 'CUSTOMERMAP')
> CREATE TABLE EMPOWER.CUSTOMERMAP (
> MAPSCRIPT IMAGE,
> LASTMOD DATETIME)
> END
>else
> BEGIN
> PRINT ' '
> PRINT 'SetScript.sql Step 1'
> raiserror(57012,12,1) with seterror
> END
>GO
>
>DECLARE @FLOWCONTROL INTEGER
>SET @FLOWCONTROL = @@ERROR
>
>IF @FLOWCONTROL = 0
> BEGIN
> PRINT ' '
> PRINT 'SetScript.sql STEP 2'
> IF NOT EXISTS(SELECT * FROM EMPOWER.LAST_MOD where TABLENAME =
>'CUSTOMERMAP')
> INSERT INTO EMPOWER.LAST_MOD (TABLENAME, CATEGORY, DESCRIPTION)
> VALUES ('CUSTOMERMAP','CUSTOMER CENTRIC','Import/Export Script')
> END
>else
> BEGIN
> PRINT ' '
> PRINT 'SetScript.sql Step 2'
> raiserror(57012,12,1) with seterror
> END
>GO
>
>DECLARE @FLOWCONTROL INTEGER
>SET @FLOWCONTROL = @@ERROR
>
>IF @FLOWCONTROL = 0
> BEGIN
> PRINT ' '
> PRINT 'SetScript.sql STEP 13'
> IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.[COLUMNS] WHERE
>TABLE_NAME = 'INSPINFO' AND COLUMN_NAME = 'PCTOFCOVG')
> ALTER TABLE EMPOWER.INSPINFO ADD PCTOFCOVG FLOAT(53)
> END
>else
> BEGIN
> PRINT ' '
> PRINT 'SetScript.sql Step 13'
> raiserror(57012,12,1) with seterror
> END
>GO
>

You'll want to read the PLSQL manual from cover to cover to see what PLSQL does for you. The above bunch of stuff can boil down to:

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> set serveroutput on ops$tkyte_at_ORA817DEV.US.ORACLE.COM> declare

  2      table_already_exists exception;
  3      pragma exception_init( table_already_exists, -955 );
  4      column_already_exists exception;
  5      pragma exception_init( column_already_exists, -1430 );
  6  begin
  7      dbms_output.put_line( 'Step 1' );
  8      begin
9          execute immediate 'create table customermap ( mapscript blob, lastmod
date )';
 10      exception
11          when table_already_exists then dbms_output.put_line( 'Table exists,
skipping' );
 12      end;
 13  
 14      dbms_output.put_line( 'Step 2' );
 15      insert into last_mod
 16      select 'CUSTOMERMAP', 'CUSTOMER CENTRIC', 'Import/Export Script'
 17        from dual
18       where NOT EXISTS ( select null from last_mod where tablename =
'CUSTOMERMAP' );
 19      dbms_output.put_line( 'Inserted ' || sql%rowcount || ' rows' );
 20  
 21      dbms_output.put_line( 'Step 3' );
 22      begin
 23          execute immediate 'alter table INSPINFO add PCTOFCOVG float(53)';
 24      exception
25          when column_already_exists then dbms_output.put_line( 'Columns
exists, skipping' );
 26      end;

 27 end;
 28 /
Step 1
Step 2
Inserted 1 rows
Step 3

PL/SQL procedure successfully completed.

And just to see that is handles the exceptional conditions properly, we run again:

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> /
Step 1
Table exists, skipping
Step 2
Inserted 0 rows
Step 3
Columns exists, skipping

PL/SQL procedure successfully completed.

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Thu Apr 18 2002 - 08:56:48 CDT