Interview Questions.

Q1. Explain The Benefits Of Running Stored Procedure On A Database Engine?

Stored tactics can run without delay run on a facts base engine. In industries wherein automation is the important thing a stored process can run totally at the records base furnished to it and this runs on a specialised data base server. Network verbal exchange can be avoided. Also this method is beneficial for execution of complicated SQL statements. 

Q2. Explain About The Difficulties Faced By The Database Developer In Implementing Pre Compiled Statements?

There are many difficulties for enforcing pre compiled statements because it ought to have all the arguments provided to it during collect time. It also relies upon upon the database and configuration. Performance also varies and it in large part relies upon upon whether it's far a time-honored query or user defined functions.

Q3. Explain About The Implementation Of Business Logic In Stored Procedures?

Stored approaches put in force enterprise logic into the database. It is embedded as API and this reduces the implementation of Logic code again explicitly. Implementation of enterprise common sense internally reduces the chances of statistics becoming corrupt.

Q4. What Is Cursors?

Cursors are supported by using approaches, features and triggers. Syntax of the cursors is embedded in SQL. Cursor have to be declared before affirming handles. Before declaring cursors it's far vital to declare variables and situations. 

Q5. 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 sq..

Q6. State The Different Extensions For Stored Procedures?

 Most of the database systems have proprietary and seller based extensions. Microsoft allows processes to be written the use of Transact-SQL. Oracle calls its extension as PL/SQL. DB2 has its extension as PL/SQL. PL/pgSQL is the extension utilized by Postgre SQL and this lets in users to have their very own practical language consisting of pl/PHP and pl/Perl.

Q7. Explain About The Return Statement?

A RETURN assertion is used to terminate the execution of a saved feature. This inturn returns a cost of expr to the characteristic caller. In a stored function there have to be at least a unmarried return announcement. If there exists a couple of capabilities it is able to have more than one go out points.

Q8. What Is The Difference Between Stored Procedures And Stored Functions In Oracle?

Both(features and Procedures) are the Oracle Objects that work explicitly closer to database gadgets like Tables,Views.

The diff. B/t Stored Procedures and Functions:

The processes does not return values. Whereas the feature returns fee.

The strategies be given more than one argument whereas the functions not.

Q9. What Are The Uses Of Stored Procedure?

Stored techniques are often used for records validation and as get admission to control mechanism. Logic carried out in applications may be centralized and saved in packages. Complex strategies and functionalities which require huge quantity of information processing and logic implementation get right of entry to their statistics by means of techniques. Data is saved in those methods and accessed with the aid of strategies.

Q10. Where The Procedures Are Stored In Database?

A stored procedure is a subroutine to be had to programs accessing a relational database machine. Stored strategies (now and again called a proc, sproc, StoPro, or SP) are honestly stored inside the database statistics dictionary

Q11. Explain About Recursive Stored Procedures?

Recursive stored strategies are used for performing repetitive responsibilities. Recursive characteristic is disabled with the aid of default however can be activated through using the subsequent command at the server max_sp_recursion_depth, additionally don?T neglect to rename the machine variable to a non 0 variable.

Q12. What Is The Difference Between "is" And "as" While Creating Procedure?

Both are equivalent. Just replacement of DECLARE keyword in declarative section.

Q13. Explain About The Process Which Takes Place To Execute A Stored Routine?

CREATE PROCEDURE and CREATE FUNCTION statement are used to create stored recurring. It can act as a feature or a method. A manner can be called by means of the usage of a name assertion and pass output with the help of output variables. It can call different Stored workouts and it can be known as from the interior of a announcement.

Q14. How Can We Call Stored Procedures Inside Store Procedures?

Alter process CallAnotherProcedure as exec sp_helptext CallAnotherProcedure 

Q15. State About The Security Aspects Of Stored Procedures?

Stored methods should be written very cautiously due to the fact they shop complicated and logical information. Security permissions have to be very finely carried out and that is possible by cautiously written code. Permissions for client information need to be set in the sort of manner that it could be reachable simplest with the aid of the consumer and the method of get entry to must be in keeping with the procedure laid down in the Stored methods. 

Q16. How One Call Ddl Statement Using Stored Procedures In Oracle?

Yes, we are able to call DDL command the use of EXECUTE IMMEDIATE command

Q17. Does Storing Of Data In Stored Procedures Increase The Access Time? Explain?

Data stored in stored techniques can be retrieved plenty quicker than the information stored in SQL database. Data may be precompiled and saved in Stored processes. This reduces the time hole between query and compiling as the facts has been precompiled and stored inside the technique. To keep away from repetitive nature of the facts base announcement caches are used. 

Q18. Can We Use Commit Inside The Trigger? If Not Then How Can We Save The Transaction Made By The Trigger?

Using pragma idea.

Q19. What Are External Procedures ? Why And When They Are Used?

External procedures are Extended saved procedures handiest. They permit you to create your personal outside routines in a programming language along with C. Extended saved processes are DLLs that an example of Microsoft SQL Server can dynamically load and run.Extended saved approaches run directly in the deal with area of an example of SQL Server and are programmed through using the SQL Server Extended Stored Procedure API.

Q20. How Many Types Of Stored Procedure?

Stored Procedure are of two kinds

consumer outline saved system

System define Stored system.

Q21. What Is Stored Procedure?

A stored process is a collection of SQL statements that form a logical unit and perform a selected undertaking. Stored approaches are used to encapsulate a set of operations or queries to execute on a database server. For instance, operations on an worker database (lease, fire, promote, lookup) may be coded as stored strategies done by way of application code. Stored procedures may be compiled and achieved with unique parameters and consequences, and they will have any aggregate of input, output, and enter/output parameters.

Q22. What Is The Difference Between A User Defined Function And A Stored Procedure?

A user defined function and stored processes are nearly similar however there exists a distinction between their implementation approaches in the code. Stored system needs to be invoked whereas a UDF can be used like every other assertion. 

Q23. Explain About The Implementation Of Stored Procedures?

Implementation of saved system varies for distinctive databases and providers. Stored processes are very bendy and they can be carried out in kind of languages. Stored approaches written in non SQL languages may additionally have a totally little compatibility with SQL