Interview Questions.

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

Answer :

Using pragma concept.

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

Answer :

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

PL/SQL Interview Questions
Question 3. What Is The Difference Between Stored Procedures And Stored Functions In Oracle?

Answer :

Both(functions and Procedures) are the Oracle Objects that paintings explicitly toward database gadgets like Tables,Views.

The diff. B/t Stored Procedures and Functions:

The techniques doesn't return values. While the feature returns price.
The procedures be given a couple of argument while the capabilities no longer.
Question 4. How Can We Call Stored Procedures Inside Store Procedures?

Answer :

Alter system CallAnotherProcedure as exec sp_helptext CallAnotherProcedure 

MySQL Tutorial
Question 5. What Is Stored Procedure?

Answer :

A stored system is a group of SQL statements that form a logical unit and carry out a selected venture. Stored tactics are used to encapsulate a fixed of operations or queries to execute on a database server. For instance, operations on an worker database (rent, fire, promote, research) will be coded as stored tactics carried out by using software code. Stored procedures may be compiled and performed with different parameters and results, and they may have any mixture of enter, output, and input/output parameters.

RDBMS Interview Questions
Question 6. Explain About The Process Which Takes Place To Execute A Stored Routine?

Answer :

CREATE PROCEDURE and CREATE FUNCTION statement are used to create saved ordinary. It can act as a characteristic or a procedure. A procedure can be referred to as by means of using a call statement and pass output with the help of output variables. It can call different Stored routines and it is able to be referred to as from the inside of a statement.

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

Answer :

There are many problems for enforcing pre compiled statements as it must have all the arguments provided to it at some stage in assemble time. It also depends upon the database and configuration. Performance additionally varies and it in large part relies upon upon whether or not it is a regular question or person described features.

DB2 Using SQL Tutorial MySQL Interview Questions
Question eight. Where The Procedures Are Stored In Database?

Answer :

A stored system is a subroutine to be had to applications getting access to a relational database system. Stored processes (every so often known as a proc, sproc, StoPro, or SP) are actually saved inside the database facts dictionary

Question nine. How Many Types Of Stored Procedure?

Answer :

Stored Procedure are of  types

person define saved system
System define Stored system.
DB2 Using SQL Interview Questions
Question 10. Explain The Benefits Of Running Stored Procedure On A Database Engine?

Answer :

Stored approaches can run without delay run on a statistics base engine. In industries where automation is the important thing a stored process can run completely on the facts base furnished to it and this runs on a specialized records base server. Network verbal exchange can be prevented. Also this manner is beneficial for execution of complex SQL statements. 

SAS Programming Tutorial
Question eleven. What Is Cursors?

Answer :

Cursors are supported by tactics, capabilities and triggers. Syntax of the cursors is embedded in SQL. Cursor should be declared before affirming handles. Before asserting cursors it's far vital to declare variables and conditions. 

SAS Programming Interview Questions
Question 12. State The Different Extensions For Stored Procedures?

Answer :

 Most of the database structures have proprietary and supplier primarily based extensions. Microsoft permits procedures to be written the usage of 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 lets in users to have their personal useful language which include pl/PHP and pl/Perl.

PL/SQL Interview Questions
Question 13. Does Storing Of Data In Stored Procedures Increase The Access Time? Explain?

Answer :

Data stored in saved strategies may be retrieved tons quicker than the information saved in SQL database. Data can be precompiled and saved in Stored methods. This reduces the time hole among question and compiling because the records has been precompiled and saved in the manner. To avoid repetitive nature of the statistics base announcement caches are used. 

DocumentDB SQL Tutorial
Question 14. How One Call Ddl Statement Using Stored Procedures In Oracle?

Answer :

Yes, we are able to call DDL command using EXECUTE IMMEDIATE command

Question 15. What Are External Procedures ? Why And When They Are Used?

Answer :

External techniques are Extended stored procedures simplest. They permit you to create your personal outside exercises in a programming language inclusive of C. Extended stored methods are DLLs that an instance of Microsoft SQL Server can dynamically load and run.Extended stored strategies run without delay in the address space of an instance of SQL Server and are programmed by using the use of the SQL Server Extended Stored Procedure API.

SQL Interview Questions
Question 16. What Are The Uses Of Stored Procedure?

Answer :

Stored strategies are regularly used for facts validation and as access control mechanism. Logic applied in packages may be centralized and saved in packages. Complex tactics and functionalities which require big amount of records processing and common sense implementation access their information by using methods. Data is stored in those methods and accessed with the aid of strategies.

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

Answer :

A user defined characteristic and stored approaches are almost similar however there exists a difference between their implementation tactics inside the code. Stored manner wishes to be invoked while a UDF can be used like some other statement. 

SSIS(SQL Server Integration Services) Interview Questions
Question 18. Explain About The Implementation Of Business Logic In Stored Procedures?

Answer :

Stored procedures put into effect commercial enterprise logic into the database. It is embedded as API and this reduces the implementation of Logic code once more explicitly. Implementation of commercial enterprise good judgment internally reduces the possibilities of records turning into corrupt.

RDBMS Interview Questions
Question 19. Explain About Recursive Stored Procedures?

Answer :

Recursive stored processes are used for appearing repetitive duties. Recursive characteristic is disabled via default however can be activated by the use of the following command on the server max_sp_recursion_depth, also don?T forget about to rename the system variable to a non zero variable.

Question 20. State About The Security Aspects Of Stored Procedures?

Answer :

Stored tactics ought to be written very cautiously because they save complicated and logical records. Security permissions ought to be very finely carried out and that is viable by means of carefully written code. Permissions for purchaser information need to be set in this sort of manner that it could be handy best by means of the purchaser and the method of get admission to should be in step with the procedure laid down within the Stored processes. 

SSRS(SQL Server Reporting Services) Interview Questions
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?

Answer :

By Dynamic square.

Question 22. Explain About The Implementation Of Stored Procedures?

Answer :

Implementation of saved process varies for different databases and vendors. Stored procedures are very bendy and that they can be applied in kind of languages. Stored strategies written in non SQL languages may additionally have a totally little compatibility with SQL

Question 23. Explain About The Return Statement?

Answer :

A RETURN assertion is used to terminate the execution of a saved feature. This inturn returns a cost of expr to the feature caller. In a stored feature there must be as a minimum a single go back announcement. If there exists multiple features it may have more than one exit points.

MYSQL DBA Interview Questions