PL/SQL Best Practices

PL/SQL Best Practices

In enterprise data-driven applications, the quality of SQL statements used in stored procedures plays a vital role in applications' performance. Bad SQL statements can break the application. In this article, I discuss some best practices for using PL/SQL.

SQL Best Practices

The key aspect and purpose of coding standards have always been to make development & maintenance easier for developers. Best Practices make the job even more accessible. Coding standards must address several areas of the development process to satisfy the requirement for making maintenance easier for developers.

Identifiers

Case

• Use all Pascal cases for table and view names
• Use Pascal case for column names
• Use camel case for variables
• Use Pascal case for the stored procedure name
• Column names with white spaces between two words should be written within the brackets[]. E.g., [Student Name]
• Avoid using keywords for columns or table names.

Prefixes and suffixes

Use the following standard prefixes for database objects,

Use the following standard prefixes for scripts,

Save all scripts using the .sql extension. Use the full table name if a column references an Id in another table.

For example, use TitleId in table TB_AUTHOR to reference column Id or TitleId in table TB_TITLE.

Use all lowercase for system names, statements, variables, and functions,

• Reserved words (begin, end, table, create, index, go, identity).
• Built-in types (char, int, varchar).
• System functions and stored procedures (cast, select, convert).
• System and custom extended stored procedures (xp_cmdshell).
• System and local variables (@@error, @@identity, @value).
• References to system table names (syscolumns).

Stored Procedures (and other DML scripts)

Use the following outline for creating stored procedures,

USE{database name}  

IF OBJECT_ID('{owner}.{procedure name}', 'IsPRocedure') IS NOT NULL
BEGIN
DROP PROCEDURE {owner}.{procedure name};
END
GO

CREATE PROCEDURE {owner}.{procedure name}  
      [{parameter}  {data type}]  
as  
/*******************************************************************  
* PROCEDURE: {procedure name}  
* PURPOSE: {brief procedure description}  
* NOTES: {special set up or requirements, etc.}  
* CREATED:  {developer name} {date}  
* MODIFIED   
* DATE            AUTHOR                  DESCRIPTION  
*-------------------------------------------------------------------  
* {date}          {developer} {brief modification description}  
*******************************************************************/  
DECLARE {variable name} {data type};
-- Add more variables as needed
SET {session variables}
-- Add more session variables as needed
{initialize variables};
{body of procedure};
RETURN;
EXCEPTION
WHEN {error type} THEN
{error handler};
END;
GO

Using the above SQL syntax, create a stored procedure in a SQL database. USE a statement to switch to the specified database and then check if the stored procedure already exists; if it does, drop it. Then CREATE PROCEDURE statement is used to create a new stored procedure, which is given a name, an owner, and a list of parameters; their data types are also provided.

Then DECLARE statement creates local variables, and the SET statement sets variables as a session. In the next step, it initializes variables, executes defined SQL statements, and finishes by including a RETURN statement and an EXCEPTION block that is used to catch and handle any errors that may occur while the procedure is running.

Formatting

Use single-quote characters to delimit strings. Nest single quotes to express a single quote or apostrophe within a string,

set @Example = 'Bills example'

Use parenthesis to increase readability, especially when working with branch conditions or complicated expressions,

if((select 1 where 1 = 2) isnot null)

Use BEGIN and END blocks only when multiple statements are present within a conditional code segment.

Whitespace

• Use one blank line to separate code sections.
• Do not use white space in identifiers

Comments

• Use single-line comment markers where needed (--). Reserve multi-line comments (/*..*/) for blocking out sections of code.
• Comment only where the comment adds value. Don't over-comment, and try to limit comments to a single line. An overuse of multi-line comments may indicate a design that is not elegant. Choose identifier names that are self-documenting whenever possible.

DML Statements (select, insert, update, delete)

• A correlated subquery using "exists" or "not exists" is preferred over the equivalent "in" or "not in" subquery due to performance degradation potential in some cases using "not in".
• Avoid the use of cross-joins if possible.

When a result set is unnecessary, use syntax that does not return a result set.

IF EXISTS (SELECT 1 
           FROM dbo.TB_Location 
           WHERE Type = 50)  
BEGIN  
IF ((SELECT COUNT(Id) FROM dbo.TB_Location WHERE Type = 50) > 0) 
END

• If more than one table is involved in a from clause, each column name must be qualified using either the complete table name or an alias. The alias is preferred.

• Always use column names in an "order by" clause. Avoid positional references.

Select

Do not use a select statement to create a new table (by supplying an into a table that does not exist).

Always supply a friendly alias to the client when returning a variable or computed expression.

SELECT @identity AS ExamId,   
(@pointsReceived/ @pTotalPoints)AS Average 

Opt for a more descriptive alias.

SELECT @identity AS UserId   
--is preferred over   
SELECT @identity AS Id  

Use the following outline for select statements. Each column in the select list should appear on its line. Each unrelated constraint within the where clause should appear on its line.

SELECT 
    t.TaskId
FROM Task.dbo.TASK t
INNER JOIN Task.dbo.ENROLLMENT et
    ON t.TaskId = et.TaskId
WHERE et.MemberId = @pMemberId
    AND (
        (t.Due_DT <= @pStartDate)
        OR (t.DueDaTe >= @pEndDate)
        OR (et.FLAG = 1)
    )

Inserts

Always list column names within an insert statement. Never perform inserts based on column position alone.

Do not call a stored procedure during an insert, as in,

INSERT INTO SUBSCRIBE
EXECUTE SUBSCRIBERS_BUILDNEW_SYSTEM;

Use the following outline to insert statements moving values or variables into a single row. Place each column name and value on its line and indent both to match as shown.

Example,

INSERT INTO [dbo].[TB_Decision]
(
    [Id],
    [TestId],
    [qid],
    [DecisionId],
    [Comments]
)
VALUES
(
    1,
    1234,
    253535,
    1,
    'hello'
);
GO

UPDATE [dbo].[TB_Decision]
SET 
    [TestId] = 12343,
    [qid] = 111,
    [DecisionId] = 111,
    [Comments] = 111
WHERE 
    [Id] = 1;

DELETE FROM [dbo].[TB_Decision]
WHERE Id = 1;

BEGIN TRANSACTION [{transaction name}]

{statements}

IF {error}
BEGIN
    ROLLBACK TRANSACTION [{transaction name}];
END;
ELSE
BEGIN
    COMMIT TRANSACTION [{transaction name}];
END;

IF ({condition})
BEGIN
    {statement}
END;
ELSE IF ({condition})
BEGIN
    {statement}
END;
ELSE
BEGIN
    {statement}
END;

WHILE ({condition})
BEGIN
    {statement}
END;

SELECT 
    CASE [{column or variable}]
        WHEN {value | expression} THEN {result if this value}
        [WHEN {value | expression} THEN {result if this value}]
        [ELSE {default result}]
    END

IF OBJECT_ID('dbo.Types') IS NOT NULL
BEGIN
    DROP TABLE Types;
END;
GO

CREATE TABLE [dbo].[Types](
    [uid] INT IDENTITY(1, 1) NOT NULL,
    [DocumentDescription] NVARCHAR(200) NOT NULL,
    [QuestionID] VARCHAR(20) NOT NULL
);
GO

IF NOT EXISTS (
    SELECT 1
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = 'dbo'
    AND TABLE_NAME = 'Questions'
    AND COLUMN_NAME = 'IsMandatory'
)
BEGIN
    ALTER TABLE [dbo].[Questions]
    ADD IsMandatory BIT NULL;
END;
GO

IF EXISTS (
    SELECT 1
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = 'dbo'
    AND TABLE_NAME = 'Elements'
    AND COLUMN_NAME = 'Abbreviation'
)
BEGIN
    ALTER TABLE [dbo].[Elements]
    DROP COLUMN [Abbreviation];
END;
GO

IF OBJECT_ID('PK_Info') IS NULL
BEGIN
    ALTER TABLE [dbo].[Info]
    ADD CONSTRAINT [PK_Info]
    PRIMARY KEY CLUSTERED ([uid] ASC);
END;
GO

IF NOT EXISTS (
    SELECT 1
    FROM sys.foreign_keys
    WHERE name = 'FK_Payments_Type'
)
BEGIN
    ALTER TABLE [dbo].[Payments]
    ADD CONSTRAINT [FK_Payments_Type]
    FOREIGN KEY ([TypeID]) REFERENCES [dbo].[Type]([TypeID])
    ON DELETE NO ACTION
    ON UPDATE NO ACTION;
END;
GO

ALTER TABLE [dbo].[QA]
ADD CONSTRAINT [DF_QA_Type_2]
DEFAULT 'C' FOR [Type];
GO

IF EXISTS (
    SELECT * FROM dbo.sysobjects
    WHERE id = OBJECT_ID(N'[DF__QA__Type__2]')
    AND type = 'DA'
)
BEGIN
    ALTER TABLE [dbo].[QA]
    DROP CONSTRAINT [DF__QA__Type__2];
END;
GO

IF EXISTS (
    SELECT * FROM sys.objects
    WHERE type = 'P'
    AND name = 'AddRequest'
)
BEGIN
    DROP PROCEDURE [dbo].[AddRequest];
END;
GO

CREATE PROCEDURE [dbo].[AddRequest]
AS
BEGIN  
   -- add the body of the procedure here
END;
GO

IF NOT EXISTS (
    SELECT * FROM [TYPE]
    WHERE TYPEID = 'RESUBMIT'
)
BEGIN
    INSERT INTO [TYPE](TYPEID)
    VALUES('RESUBMIT');
END;

IF EXISTS (
    SELECT * FROM TYPE
    WHERE TYPEID = 'RESUBMIT'
)
BEGIN
    DELETE FROM TYPE
    WHERE TYPEID='RESUBMIT'
END;