Stored Procedures Interview Questions

View: View is virtual table. It is using for security purpose means whenever we need to give only some permissions to the different user then create the view from table with required columns. Their is...

View is a virtual table. physically is not exit. but procedure is physically exit,which has mainly 2 types
1)local procedure and
2)stored procedure,
store procedure maid by the user. and local procedure made by the SQL.

Stored Procedure is a re-usable program which is created once and reused n times .
It stores the procedure at database level
It provides security, better performance.

Stored Procedure is an already written sql statements that is saved in database.

It is best to go for a function when a value needs to be computed but a stored procedure is useful when you need to execute business logic.

Hi, suppose you want top 3 salaries from emp table. syntax for top 3 salaries: select distinct top (3) (sal) from emp order by sal desc we have to use this query dialy or monthly.Mea...

SP 1-It is a subprogram that perform an action. 2-It does not contain any return clause. 3-Can return none,one or many values. 4-Can contain a return statement. SF 1-Invoke as a part of expression....

1. A function is used to compute a value. A porucedure is used to execute business logic.2. A functin must return a value. A procedure may not return a value or may return more than one value using ou...

I feel this Answer is revelent, It will help in understand the concept Difference between Server Jobs and Parallel Jobs Server Jobs works only if the server jobs datastage has been installed in you...

An external procedure, also sometimes referred to as an external routine, is a procedure stored in a dynamic link library (DLL), or libunit in the case of a Java class method. You register the procedu...

External procedures provide a mechanism for calling out to a non-database program, such as a DLL under NT or a shared library under Unix. Every session calling an external procedure will have its own ...

create procedure sp_test
@param1 INT ,
@param2 INT= value

as

select * from testtable1 where value1 = @param1 and value2 = @param2

One can use the AUTHID CURRENT_USER clause to make stored procedures and functions execute with the privileges and schema context of the calling user. You can create one instance of the procedure,...

We can call using dynamic SQL, Execute Immediate.

select * from all_objects where object_type= 'PROCEDURE';

I am not too sure if I understand our question properly. If you wanted to find all the stored procedures defined to the database with a CREATE PROCEDURE command (let's say DB2), then run a query from ...

Would like to add one more point for your answer,

Calling a function from select stament is not recomended as it will degrade the performance.

>>Why the stored functions can not be called from DML Statements?  We can call a stored function from dml statement.Here is simple exampleCREATE OR REPLACE FUNCTION TEST_1 RETURN NUMBER ISB...

Stored procedures are stored in data base in complied form. In dynamic sql the statements are dynamically constructed at run time.

Stored Procedures are compiled and runs on the server. dynamic SQL are not compiled and runs on client.

Yes , we can. Here is simple example.

CREATE OR REPLACE PROCEDURE TEST_PROC(V_NUM OUT NUMBER) AS
BEGIN
V_NUM := 10;
END;
/

CREATE OR REPLACE FUNCTION TEST_FUNC RETURN NUMBER  AS
V_NUM NUMBER;
BEGIN
TEST_PROC(V_NUM);
RETURN V_NUM;
END;
/

SELECT TEST_FUNC() FROM DUAL;

OUTPUT
10

Both are same.

There is no difference as such, We use AS when Stored Procedure is defined in a package if SP is outside pkg we use IS.  Here is the exampleEx: ASPakcage Headercreate package schema.testpkg ...

A ref cursor is basically a data type. A variable created based on such data type is called a cursor variable.Dynamic cursors are declared using reference cursors.Differences:1. Cusor is static in nat...

Ref Cursor is used to retrieve different Active Set from different Table,
whereas Cursor can retrieve only active set from the same table.

A function can be called in a select statement as well as in a stroed proc. As function call would return a value we need to store return value in a variable.Ex:Assume we have a fuction .. FN_TEST(Dat...

pls explain wd simple example  

PRAGMA AUTONOMOUS_TRANSACTION is used to commit the transaction in the PL/SQL block which is inside another PL/SQL block which is on the verge of rolling back.

To make an independent transaction within a PL/SQL block

An autonomous transaction is used to create a seprate transaction which when commited will commit data only belongs to that transactionExample PROCEDURE Proc 1  IS  .  INSERT&...