Stored Procedures

Stored procedures provide a powerful way to code application logic that can be stored on the server. Stored procedures reduce network traffic, improve performance, and improving productivity (statements in a stored procedure only need to be written one time). Additionally, stored procedures can be used to help ensure the integrity of the database (information is entered in a consistent manner).

In a database management system (DBMS), a stored procedure is a set of Structured Query Language (SQL) statements with an assigned name that's stored in the database in compiled form so that it can be shared by a number of programs. The use of stored procedures can be helpful in controlling access to data (end-users may enter or change data but do not write procedures), preserving data integrity.

The syntax for a procedure is:

CREATE [OR REPLACE] PROCEDURE procedure_name [ (parameter [,parameter]) ] IS [declaration_section] BEGIN executable_section [EXCEPTION exception_section] END [procedure_name];

When you create a procedure or function, you may define parameters. There are three types of parameters that can be declared:

• IN - The parameter can be referenced by the procedure or function. The value of the parameter can not be overwritten by the procedure or function.
• OUT - The parameter can not be referenced by the procedure or function, but the value of the parameter can be overwritten by the procedure or function.
• IN OUT - The parameter can be referenced by the procedure or function and the value of the parameter can be overwritten by the procedure or function.

SUBSTR function in Sql

The SUBSTR functions allows you to extract a substring from a string. The syntax for the substr function is:

SUBSTR ( string, start_position, [ length ] )

Where

• string is the source string.
• start_position is the position for extraction. The first position in the string is always 1.
• length is optional. It is the number of characters to extract. If this parameter is omitted, substr will return the entire string.

Note:

• If start_position is 0, then SUBSTR treats start_position as 1 (ie: the first position in the string).
• If start_position is a positive number, then SUBSTR starts from the beginning of the string.
• If start_position is a negative number, then SUBSTR starts from the end of the string and counts backwards.
• If length is a negative number, then SUBSTR will return a NULL value.

For Example:

1. SUBSTR ('This is a Informatica blog', 11, 11) return 'Informatica'
2. SUBSTR ('This is a Informatica blog', 11) return 'Informatica blog'
3. SUBSTR ('This is a Informatica blog', -4, 4) return 'Blog'
4. SUBSTR ('This is a Informatica blog', -16, 4) return 'Info'

INSTR function in Sql

The INSTR function returns the location of a substring in a string. The syntax for the INSTR function is:

INSTR ( string1, string2 [, start_position [, nth_appearance ] ] )

Where

• string1 is the string to search.
• string2 is the substring to search for in string1.
• start_position is the position in string1 where the search will start. This argument is optional. If omitted, it defaults to 1. The first position in the string is 1. If the start_position is negative, the function counts back start_position number of characters from the end of string1 and then searches towards the beginning of string1.
• n^th appearance is the nth appearance of string2. This is optional. If omitted, it defaults to 1.

Note: If string2 is not found in string1, then the INSTR Oracle function will return 0.

Example:

• INSTR ('This is Informatica blog', 'i') return 3; the first occurrence of 'i'
• INSTR ('This is Informatica blog', 'i', 1, 1) return 3; the first occurrence of 'i'
• INSTR ('This is Informatica blog', 'i', 1, 2) return 6; the second occurrence of 'i'
• INSTR ('This is Informatica blog', 'i', 1, 3) return 9; the third occurrence of 'i'
• INSTR ('Tech on the net', 'e', -3, 2) return 2.

What are the Joins?

In SQL, a join is a relational operator that combines data from multiple tables into a single result set. The Joiner transformation acts in much the same manner, except that tables can originate from different databases or flat files.

Types of Joins:

1. Normal joins: With a normal join, the PowerCenter Server discards all rows of data from the master and detail source that do not match, based on the condition.
2. Master Outer join: This join keeps all rows of data from the detail source and the matching rows from the master source. It discards the unmatched rows from the master source.
3. Detail Outer join: This join keeps all rows of data from the master source and the matching rows from the detail source. It discards the unmatched rows from the detail source.
4. Full Outer join: A full outer join keeps all rows of data from both the master and detail sources.

Example:

In EMP, we have employees with DEPTNO 10, 20, 30 and 50. In DEPT, we have DEPTNO 10, 20, 30 and 40. DEPT will be MASTER table as it has less rows.

Normal Join:

All employees of 10, 20 and 30 will be there as only they are matching.

Synax: DEPT.DEPTNO=EMP.DEPTNO

Master Outer Join:

All data of employees of 10, 20 and 30 will be there. There will be employees of DEPTNO 50 and corresponding DNAME and LOC columns will be NULL.

Syntax: {EMP LEFT OUTER JOIN DEPT ON DEPT.DEPTNO=EMP.DEPTNO}

Detail Outer Join:

All employees of 10, 20 and 30 will be there. There will be one record for DEPTNO 40 and corresponding data of EMP columns will be NULL.

Syntax: {EMP RIGHT OUTER JOIN DEPT ON DEPT.DEPTNO=EMP.DEPTNO}

Full Outer Join:

All data of employees of 10, 20 and 30 will be there. There will be employees of DEPTNO 50 and corresponding DNAME and LOC columns will be NULL. There will be one record for DEPTNO 40 and corresponding data of EMP columns will be NULL.

Syntax: {EMP FULL OUTER JOIN DEPT ON DEPT.DEPTNO=EMP.DEPTNO}

Caution: Curly braces are needed in Syntax.

Note: A normal or master outer join performs faster than a full outer or detail outer join.

The frequently used SQL commands:

The purpose of this post is to have a quick reference for SQL syntax.

Select Statement

SELECT "column_name"
FROM "table_name";

Distinct

SELECT DISTINCT "column_name"
FROM "table_name";

Where

SELECT "column_name"
FROM "table_name"
WHERE "condition";

And/Or

SELECT "column_name"
FROM "table_name"
WHERE "simple condition"
{[AND|OR] "simple condition"};

In

SELECT "column_name" FROM "table_name"
WHERE "column_name" IN ('value1', 'value2', ...);

Between

SELECT "column_name"
FROM "table_name"
WHERE "column_name" BETWEEN 'value1' AND 'value2';

Like

SELECT "column_name"
FROM "table_name"
WHERE "column_name" LIKE {PATTERN};

Order By

SELECT "column_name"
FROM "table_name"
[WHERE "condition"]
ORDER BY "column_name" [ASC, DESC];

Count

SELECT COUNT("column_name")
FROM "table_name";

Group By

SELECT "column_name1", SUM("column_name2")
FROM "table_name"
GROUP BY "column_name1";

Having

SELECT "column_name1", SUM("column_name2")
FROM "table_name"
GROUP BY "column_name1"
HAVING (arithematic function condition);

Create Table Statement

CREATE TABLE "table_name"
("column 1" "data_type_for_column_1",
"column 2" "data_type_for_column_2",... );

Drop Table Statement

DROP TABLE "table_name";

Truncate Table Statement

TRUNCATE TABLE "table_name";

Insert Into Statement

INSERT INTO "table_name" ("column1", "column2", ...)
VALUES ("value1", "value2", ...);

Update Statement

UPDATE "table_name"
SET "column_1" = [new value]
WHERE {condition};

Delete From Statement

DELETE FROM "table_name"
WHERE {condition};