Functions , Stored Procedure , cursors and Packages in Database

Procedures and Functions allow 'business logic' to be stored in the database and executed from SQL statements. Advantages are it allows multiple applications to access procedures, a single point of change in case business rules change, can be invoked from dynamic SQL

A function returns a value and a stored procedure doesn’t return a value. 

Functions:-Function will allow only Select statement, it will not allow us to use DML statements. Also, it will allow only input parameters, doesn’t support output parameters.Transactions are not allowed within functions. Transactions are not allowed within functions. Stored procedures can’t be called from function. it can be called from select statement. User defined functions can be used in join clause as a result set. Useful with specialized data types such as images and geometric objects

Eg: An image data type may have associated functions to compare two images for similarity.

Functions that can return tables as results are called table functions. Functions must contain a return statement. To call a function,  pass the required parameters along with the function name and if the function returns a value, then store the returned value.

SQL functions have the following uses −

1. Performing calculations on data
2. Modifying individual data items
3. Manipulating the output
4. Formatting dates and numbers
5. Converting data types

CREATE [OR REPLACE] FUNCTION function_name 

[(parameter_name [IN | OUT | IN OUT] type [, ...])] 

RETURN return_datatype 

{IS | AS} 

BEGIN 

   < function_body > 

END [function_name];

Procedures and Functions allow business logic to be stored in the database and executed from SQL statements.

Functions Example

find no of instructors in a department, given department name.

create function deptcount(dept_name varchar(20))

returns integer

begin

declare dcount integer'

select count(*) into dcount

from instructor

where instructor.dept_name=dept_name

return dcount;

end;

Function usage

Return names and budget of dept with more than 10 instructors

select dept_name, budget

from instructor

where deptcount(dept_name)>10;

Stored Procedure: 

Stored Procedure can have select statements as well as DML statements such as insert, update, deletes etc.It can have both input and output parameters. Transactions can be used in SP's. Table variables and temporary variables can be used. SP's can call functions. Procedures can’t be called from Select/Where/Having etc statements. Execute/Exec statement can be used to call/execute stored procedure. it can't be used in join clause. Prepared SQL code that you save so you can reuse the code repeatedly. To running the same SQL code repeatedly, we can pass parameters to the stored procedure, so depending on what the need is the stored procedure can act accordingly based on the parameter values that were passed.

Eg: creating simple sp from T-SQL

Select * from ProductFactExtracttable

Use SRCSchema

GO

CREATE PROCEDURE dbo.usp_ProductFactExtracttable

AS

Select * from ProductFactExtracttable

GO

To call procedure, EXEC dbo. usp_ProductFactExtracttable

Stored procedures can accept one or more parameter value. To Limit records based on startdate and enddate, we can use parameters @StartDate and @EndDate.

To execute sp, EXEC dbo. usp_ProductFactExtracttable @StartDate = ’ 2016-01-08 21:59:54.137' , @EndDate = ’ 2016-01-08 21:59:54.137'

Use SRCSchema

GO

CREATE PROCEDURE dbo.usp_ProductFactExtracttable @StartDate DATETIME, @EndDate DATETIME

AS

SET NOCOUNT ON;

BEGIN

……………………………

SET NOCOUNT ON Improves SQL Server Stored Procedure Performance. This one line of code put at the top of a stored procedure turns off the messages that SQL Server sends back to the client after each T-SQL statement is executed. This is performed for all SELECT, INSERT, UPDATE, and DELETE statements. To get the number of rows affected by the T-SQL statement that is executing you can still use the @@ROWCOUNT option.

-- not using SET NOCOUNT ON

CREATE PROCEDURE dbo.uspGetAddress @City nvarchar(30)

AS

SET NOCOUNT ON

SELECT *

FROM Person.Address

WHERE City = @City

PRINT @@ROWCOUNT

GO

To drop a single stored procedure you use the DROP PROCEDURE or DROP PROC command as follows.

DROP PROCEDURE dbo.uspGetAddress

GO

-- or

DROP PROC dbo.uspGetAddress

GO

To drop multiple stored procedures with one command you specify each procedure separated by a comma as shown below.

DROP PROCEDURE dbo.uspGetAddress, dbo.uspInsertAddress, dbo.uspDeleteAddress

GO

-- or

DROP PROC dbo.uspGetAddress, dbo.uspInsertAddress, dbo.uspDeleteAddress

GO

Once a stored procedure has been created it is stored within one of the system tables in the database that is was created in.

Modifying an Existing Stored Procedure

Let's say we have the following existing stored procedure:  This allows us to do an exact match on the City.

CREATE PROCEDURE dbo.uspGetAddress @City nvarchar(30)

AS

SELECT *

FROM Person.Address

WHERE City = @City

GO

Let's say we want to change this to do a LIKE instead of an equals.

To change the stored procedure and save the updated code you would use the ALTER PROCEDURE command as follows.

ALTER PROCEDURE dbo.uspGetAddress @City nvarchar(30)

AS

SELECT *

FROM Person.Address

WHERE City LIKE @City + '%'

GO

Cursors

Oracle uses work spaces to execute the SQL commands. when Oracle processes a SQL command, it opens an area in the memory called Private SQL Area. A cursor is an identifier for this private SQL area. It allows programmers to name this area and access it’s information.

SQL%ROW COUNT returns the number of rows that are processed by a SQL statement.  SQL%FOUND returns the Boolean value TRUE if at least one row was processed. SQL%NOT FOUND returns the Boolean value TRUE if no rows were processed. The %NOTFOUND attribute returns true when the last row of the cursor is processed, and no other row is available.

Oracle implicitly declares a cursor to all the DDL and DML commands that return only one row. For queries returning multiple rows, an explicit cursor is created.

PAckages

Packages are schema objects that groups logically related PL/SQL types, variables, and subprograms.

A package will have two mandatory parts −

• Package specification
• Package body or definition