Stored procedure maintenance

One of the nice features of Oracle stored procedures, functions and packages is that you can create or recreate the database side code in the same statement. The advantage of this is that during upgrades/patches you can run ALL database code scripts again, which ensures nothing is missed - and also that the changes you have made haven't caused breaking changes in another procedure!

For example, the following will create or replace the following security package header..

CREATE OR REPLACE PACKAGE PKG_SECURITY
AUTHID DEFINER
AS
/*  ------------------------------------------------------
    Adds authentication for specified user to system    
    ------------------------------------------------------ */
PROCEDURE proc_AddUserAuthentication(
  userId                IN USERSECURITY.USER_FK%TYPE,
  encryptedPassword     IN USERSECURITY.PASSWORD%TYPE,
  saltValue             IN USERSALT.SALT%TYPE,
  auditUserId           IN USERSECURITY.AUDITUSERID%TYPE);
END;
/

My recent digging around has revealed that you can also mimic this CREATE or REPLACE functionality in MySQL and Sql Server.

In MySQL you can call the DROP PROCEDURE IF EXISTS method....

DROP PROCEDURE IF EXISTS premierenvoy.proc_AddUserAuthentication;
CREATE
DEFINER = CURRENT_USER
PROCEDURE proc_AddUserAuthentication(
............

...and in Sql Server you can use the OBJECT_ID function and check whether the returning value is not null to determine whether to drop the procedure.

IF OBJECT_ID('proc_AddUserAuthentication') IS NOT NULL
DROP PROCEDURE proc_AddUserAuthentication;
GO
CREATE PROCEDURE proc_AddUserAuthentication(
............

Creating copies of tables in Oracle, SqlServer and MySQL

As the sub-title of my blog suggests, I am from an Oracle background. But I also have dealings with SQL Server and MySQL databases. In fact the latest project I'm working on is going to have to work with all three databases!

One issue I have encountered recently is how to create copies of tables in SQL Server and MySQL. In Oracle you would use the AS keyword in the CREATE table statement and select from the table that is being copied. For example:

CREATE TABLE myCopyTable AS
SELECT * FROM myOriginalTable;

You can add a WHERE clause to restrict the records that are included in the copy. Making this WHERE clause into a condition that is not achievable (such as WHERE 1 =2) will result in a copy of the table without the data.
Note: This copy does not include any indexes/constraints on the original table.

In MySQL it is similar but you do not need the AS keyword. For example:

CREATE TABLE myCopyTable
SELECT * FROM myOriginalTable;

Alternatively you can actually create an exact copy including all constraints and indexes. This is achieved by using the LIKE keyword.

CREATE TABLE myCopyTable
LIKE myOriginalTable;

Note: This won't take the data across, you would have to insert that afterwards
(e.g. INSERT INTO myCopyTable SELECT * FROM myOriginalTable;)

Finally, SQL Server... which wins the prize for the strangest implementation. You actually use a SELECT command to create your new table :o

SELECT *
INTO myCopyTable
FROM myOriginalTable;

I really don't like the way this is implemented as there is no mention of a table being created in the SQL statement. Even though this is what the user is trying to achieve.
Anyhow, no point complaining at least they provided a way of copying a table :)