5 avg. rating (100% score) - 1 votes
Are you preparing for Stored Procedures interview?. A stored procedure is a set of Structured Query Language (SQL) statements with an assigned name, which are stored in a relational database management system as a group, so it can be reused and shared by multiple programs. The program using the Stored Procedure when triggered, it hits the SP and the sql statements are executed as coded thus communicating with database. There are number of people applying to Stored Procedures jobs. Wisdomjobs focus on providing interview questions and answers to help you in clearing the interview with ease. Stored Procedures interview questions and answers are useful to attend job interviews and get shortlisted for position. Check out interview questions page to get more information.
Using pragma concept.
Both are equivalent. Just replacement of DECLARE keyword in declarative section.
Both(functions and Procedures) are the Oracle Objects that work explicitly towards database objects like Tables,Views.
The diff. b/t Stored Procedures and Functions:
Alter procedure CallAnotherProcedure as exec sp_helptext CallAnotherProcedure
A stored procedure is a group of SQL statements that form a logical unit and perform a particular task. Stored procedures are used to encapsulate a set of operations or queries to execute on a database server. For example, operations on an employee database (hire, fire, promote, lookup) could be coded as stored procedures executed by application code. Stored procedures can be compiled and executed with different parameters and results, and they may have any combination of input, output, and input/output parameters.
CREATE PROCEDURE and CREATE FUNCTION statement are used to create stored routine. It can act as a function or a procedure. A procedure can be called by using a call statement and pass output with the help of output variables. It can call other Stored routines and it can be called from the inside of a statement.
There are many difficulties for implementing pre compiled statements because it should have all the arguments provided to it during compile time. It also depends upon the database and configuration. Performance also varies and it largely depends upon whether it is a generic query or user defined functions.
A stored procedure is a subroutine available to applications accessing a relational database system. Stored procedures (sometimes called a proc, sproc, StoPro, or SP) are actually stored in the database data dictionary
Stored Procedure are of two types
Stored procedures can run directly run on a data base engine. In industries where automation is the key a stored procedure can run entirely on the data base provided to it and this runs on a specialized data base server. Network communication can be avoided. Also this procedure is useful for execution of complex SQL statements.
Cursors are supported by procedures, functions and triggers. Syntax of the cursors is embedded in SQL. Cursor should be declared before declaring handles. Before declaring cursors it is imperative to declare variables and conditions.
Most of the database systems have proprietary and vendor based extensions. Microsoft allows procedures to be written using Transact-SQL. Oracle calls its extension as PL/SQL. DB2 has its extension as PL/SQL. PL/pgSQL is the extension used by Postgre SQL and this allows users to have their own functional language such as pl/PHP and pl/Perl.
Data stored in stored procedures can be retrieved much faster than the data stored in SQL database. Data can be precompiled and stored in Stored procedures. This reduces the time gap between query and compiling as the data has been precompiled and stored in the procedure. To avoid repetitive nature of the data base statement caches are used.
Yes, we can call DDL command using EXECUTE IMMEDIATE command
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.
Stored procedures are often used for data validation and as access control mechanism. Logic applied in applications can be centralized and stored in applications. Complex procedures and functionalities which require huge amount of data processing and logic implementation access their data by procedures. Data is stored in these procedures and accessed by procedures.
A user defined function and stored procedures are almost similar but there exists a difference between their implementation procedures in the code. Stored procedure needs to be invoked whereas a UDF can be used like any other statement.
Stored procedures implement business logic into the database. It is embedded as API and this reduces the implementation of Logic code again explicitly. Implementation of business logic internally reduces the chances of data becoming corrupt.
Recursive stored procedures are used for performing repetitive tasks. Recursive feature is disabled by default but can be activated by using the following command on the server max_sp_recursion_depth, also don?t forget to rename the system variable to a non zero variable.
Stored procedures should be written very carefully because they store complex and logical data. Security permissions should be very finely applied and this is possible by carefully written code. Permissions for client data should be set in such a manner that it could be accessible only by the client and the method of access should be according to the procedure laid down in the Stored procedures.
Question 21. If Get_product_name Is My Procedure Called In My Stored Procedure. Here In My Procedure I Am Passing A Value Directly But I Need To Pass The Value To The Procedure Dynamically. How It Is Possible?
By Dynamic sql.
Implementation of stored procedure varies for different databases and vendors. Stored procedures are very flexible and they can be implemented in variety of languages. Stored procedures written in non SQL languages may have a very little compatibility with SQL
A RETURN statement is used to terminate the execution of a stored function. This inturn returns a value of expr to the function caller. In a stored function there should be at least a single return statement. If there exists multiple functions it can have multiple exit points.
Stored Procedures Related Tutorials
|MySQL Tutorial||DB2 Using SQL Tutorial|
|SAS Programming Tutorial||DocumentDB SQL Tutorial|
Stored Procedures Related Interview Questions
|PL/SQL Interview Questions||RDBMS Interview Questions|
|MySQL Interview Questions||DB2 Using SQL Interview Questions|
|SAS Programming Interview Questions||SQL Interview Questions|
|SSIS(SQL Server Integration Services) Interview Questions||SSRS(SQL Server Reporting Services) Interview Questions|
|MYSQL DBA Interview Questions||DB2 SQL Programming Interview Questions|
|Complex SQL Queries Interview Questions||Common SQL Queries Interview Questions|
Stored Procedures Related Practice Tests
|PL/SQL Practice Tests||RDBMS Practice Tests|
|MySQL Practice Tests||DB2 Using SQL Practice Tests|
|SAS Programming Practice Tests||SQL Practice Tests|
|SSIS(SQL Server Integration Services) Practice Tests||SSRS(SQL Server Reporting Services) Practice Tests|
|MYSQL DBA Practice Tests||DB2 SQL Programming Practice Tests|
Introduction To Client/server Architecture
About Firebird Data Types
Date And Time Types
From Drawing Board To Database
Creating And Maintaining A Database
Expressions And Predicates
Ordered And Aggregated Sets
Overview Of Firebird Transactions In
Programming With Transactions
Introduction To Firebird Programming
Error Handling And Events
Security In The Operating Environment
Configuration And Special Features
Interactive Sql Utility (isql)
Database Backup And Restore (gbak)
Housekeeping Tool (gfix)
Understanding The Lock Manager
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.