Sunday, 6 April 2014

Stored Procedures Interview Questions

Stored Procedures Interview Questions

Showing Questions 1 - 19 of 19 Questions
Sort by: 
Answers
 | 
Date
Asked By: nikhiljain27 | Asked On: Jul 13th, 2013
1 answer
Can we use SQL-transaction in nested stored procedure(stored procedure calling from stored procedure)? one SQL-transaction object in primary sp. second SQL-transaction object in nested sp(second sp).
Answered by: Akshay Jain on: Jan 29th, 2014
Yes U can... I till behave like a normal transaction
Asked By: preetiv | Asked On: May 28th, 2008
13 answers
What is the difference between view and stored procedure? Can views accept input parameters as stored procedure? If yes,how could I do that? I am new to database and working on ms SQL,if someone could suggest me good reference websites then that would be great.
Star Read Best Answer

Editorial / Best Answer

Answered by: atdhdrolla
View all questions by atdhdrolla   View all answers by atdhdrolla
Member Since Mar-2007 | Answered On : Jul 16th, 2008
View - A View in simple terms is a subset of a table. It can be used to retrieve data from thetables, Insert, Update or Delete from the tables. The Results of using View are not permanently  stored in the database.

Stored Procedure -  A stored procedure is a group of SQL statements which can be stored into the database and can be shared over the netwrok with different users.
Answered by: Test on: Oct 16th, 2013
View does not have parameters , SP can have input / output parameters . View is another way to view table data . SP is pre-compiled object which can change /add data into tables.
Answered by: naveenreddy bolla on: Feb 21st, 2013
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...
Asked By: ksjana | Asked On: Jul 7th, 2013
Ex: type ty_1 is table of number index by PL/SQL_integer; type ty_2 is table of varchar2(10) index by PL/SQL_integer; type ty_3 is table of date index by PL/SQL_integer; type rec_tab is record ( l_num ty_1, l_str ty_2, l_date ty_3); please explain this with an example having lOOPS
Asked By: Interview Candidate | Asked On: Aug 19th, 2005
16 answers
A program running in the database that can take complex actions based on the inputs you send it. Using a stored procedure is faster than doing the same work on a client, because the program runs right inside the database server. Stored procedures are nomally written in PL/SQL or Java.
Answered by: guruprasad on: Oct 3rd, 2012
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.
Answered by: narendra9559 on: May 24th, 2011
Stored Procedure is an already written sql statements that is saved in database.
Asked By: jayshree13 | Asked On: Apr 16th, 2011
4 answers
Explain the scenario of the best usage
Answered by: ptmich on: May 28th, 2012
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.
Answered by: narendra9559 on: May 25th, 2011
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...
Asked By: vssandilya | Asked On: Dec 12th, 2005
17 answers
Answered by: Prasanjeet Das on: May 9th, 2012
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....
Answered by: krishnaindia2007 on: May 26th, 2008
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...
Asked By: plvenkatsenthil | Asked On: Oct 12th, 2011
1 answer
Answered by: sreemon on: Jan 4th, 2012
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...
Asked By: smocherla | Asked On: Jul 27th, 2006
6 answers
Answered by: akharejhansi on: Jun 8th, 2011
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...
Answered by: praveengowda on: May 23rd, 2008
External procedures provide a mechanism for calling out to a non-database program, such as aDLL under NT or a shared library under Unix. Every session calling an external procedure will have its own ...
Asked By: papillon | Asked On: Jan 28th, 2010
2 answers
How do we pass default value as input parameter in a store procedure?
Answered by: nisargkothari on: Sep 16th, 2010
create procedure sp_test
@param1 INT ,
@param2 INT= value

as

select * from testtable1 where value1 = @param1 and value2 = @param2
Answered by: metal on: Jan 31st, 2010
CREATE or REPLACE PROCEDURE proc_test
(name IN varchar2 DEFAULT 'Me' )
AS
BEGIN
dbms_output.put_line('My name is proc_test ' || name);
END;

SQL> exec proc_test('hello');
My name is proc_test hello

SQL> exec proc_test();
My name is proc_test Me
Asked By: deepikazain | Asked On: Mar 9th, 2010
1 answer
What authid clause does in executing dynamic SQL?
Answered by: dipanjan80 on: Aug 25th, 2010
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,...
Asked By: papillon | Asked On: Jan 28th, 2010
1 answer
How do we call stored procedure dynamically?
Answered by: suneesece on: May 19th, 2010
We can call using dynamic SQL, Execute Immediate.
Asked By: Suman-Delhi | Asked On: Feb 8th, 2007
8 answers
Answered by: suneesece on: May 19th, 2010
select * from all_objects where object_type= 'PROCEDURE';
Answered by: Shafik Ismail on: Feb 26th, 2010
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 ...
Asked By: Appu | Asked On: Jun 10th, 2007
4 answers
Answered by: ushanaina on: Nov 24th, 2009
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.
Answered by: krishnaindia2007 on: May 26th, 2008
>>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...
Asked By: asd3 | Asked On: Jun 9th, 2007
3 answers
Answered by: krishnaindia2007 on: May 26th, 2008
Stored procedures are stored in data base in complied form. In dynamic sql the statements are dynamically constructed at run time.
Answered by: sampra on: Mar 10th, 2008
Stored Procedures are compiled and runs on the server. dynamic SQL are not compiled and runs on client.
Asked By: chesy | Asked On: May 26th, 2008
1 answer
Answered by: krishnaindia2007 on: May 26th, 2008
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
Asked By: Shekhar | Asked On: Apr 14th, 2006
5 answers
Answered by: krishnaindia2007 on: May 26th, 2008
Both are same.
Answered by: praveengowda on: May 23rd, 2008
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 ...
Asked By: Hemant Tiwari | Asked On: Feb 20th, 2007
2 answers
Answered by: krishnaindia2007 on: May 26th, 2008
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...
Answered by: Ramesh on: Mar 20th, 2007
Ref Cursor is used to retrieve different Active Set from different Table,
whereas Cursor can retrieve only active set from the same table.
Asked By: Sridhar Rao | Asked On: Oct 10th, 2006
3 answers
Answered by: praveengowda on: May 23rd, 2008
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...
Answered by: sampra on: Mar 10th, 2008
pls explain wd simple example  
Asked By: santanu | Asked On: Aug 8th, 2007
2 answers
Answered by: senkum123 on: Aug 12th, 2007
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
Answered by: soorajsk_84 on: Aug 9th, 2007
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&...
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)