Stored Procedures

Question 1

What are different usages of Stored Procedure?

Answer 1

Stored Procedures are named blocks which are used to add the business logic to different programs. Procedures are used in data validation most of the times. If there is a functional requirement where user needs to validate the data according to the customer requirement, then this logic has been added in Stored Procedure. Complex Functionalities needs huge amount of data to be processed. Stored Procedures are used to process huge amount of data at a same time. Following are bullet points of usages of Stored Procedure:

• Data Validation Purpose
• Huge Data Processing
• Improve System Performance
• Adding complex logic centralized
• Access Control Mechanism

Question 2

What is difference between Stored Procedure and Function?

Answer 2

Compilation:
SP =stored in db in compiled format
Function =compile at runtime

Return type
SP =directly only return integers (return not required)
Function =can return scalar or table (must return)

Multiple return values
SP = can return more than one value
Function =does not support parameters

DML statements
SP =can have DML
Function =NO DML

Execution
SP = can execute function, cant use where/having/select subqueries
Function =cannot execute SP, can be part of subquery w where/having/select

Exception handling
SP =can have try catc
Function = cannot have try catc

Question 3

Can Procedures be called from inside functions? Yes or No, Why?

Answer 3

1. Stored Procedure may contain DML statements.
2. Function can’t contain DML statements.

So executing Function inside stored procedure will never break rule 1.
But executing stored procedure inside function may break rule no 2.

Question 4

Are you able to call DDL in Stored Procedure?

Answer 4

Yes we can call the DDL statement in Stored Procedure. Using Execute Immediate we can call the DDL statements in Stored Procedures.

Question 5

What are external procedures and when they are used?

Answer 5

External procedures are Extended stored procedures only. They let you create your own external routines in a programming language such as C. Extended stored procedures are DLLs that an instance of Microsoft SQL Server can dynamically load and run.Extended stored procedures run directly in the address space of an instance of SQL Server and are programmed by using the SQL Server Extended Stored Procedure API.

Question 6

What are the advantages of using a stored procedure?

Answer 6

The following are the advantages of using a stored procedure: –
• It reduces the network usage between the client and the server and an immediate processing is performed on the database server. It hence reduces the unnecessary data transfer.
• Security is improved and the user access to the stored procedure is managed by the administrator.
• Development cost reduces and reliability increases.
• It improves the performance of the database.
• These are used to encapsulate the logic and hence the code can be changed without affecting the clients.
• The access to other database objects becomes more secure.
• SQL injection attacks can be avoided using this.

Question 7

What are the disadvantages of using the stored procedures?

Answer 7

Everything which has advantages has disadvantages too. The disadvantages of using stored procedures are listed below:
• Specialized skills are required for writing and maintaining the code of a stored procedure.
• Debuggers are not available for a stored procedure.
• The language in which a stored procedure is written may differ from one database to the other.
• Exception handling in a stored procedure is not up to the mark.
• These are tightly coupled to the database.
• You may not be able to use objects.
• Sometimes the logic may not be understood by the programmers.

Question 8

What are the Differences between Functions and Procedures

Answer 8

1) A function always returns a value using the return statement while a procedure may return one or more values through parameters or may not return at all. Although, OUT parameters can still be used in
functions, they are not advisable neither are there cases where one might find a need to do so. Using OUT parameter restricts a function from being used in a SQL Statement.
2) We can use try-catch exception handling in SP but we cannot do that in Functions
3) We can use transaction in SP but it is not possible in functions
4) Functions can be used in typical SQL statements like SELECT, INSERT, UPDATE, DELETE, MERGE, while procedures can’t.
5) We can call function from stored procedure but cannot call stored procedure from a function
6) Functions are normally used for computations whereas procedures are normally used for executing business logic.
7) Oracle provides the provision of creating “Function Based Indexes” to improve the performance of the subsequent SQL statement. This applies when performing the function on an indexed column in where clause of a query.