PL/SQL – Complex Queries (Job Interviews)

PL/SQL Complex SQL Queries

1. What are the differences between Procedure and Function?

Procedures: We can do Insert, Update and delete. It may or may not return values.
Functions: We can do only SELECT operation. It ALWAYS returns value.

2. How do we call function?
Functions we can give as Aggregate of column. Second way we can also give in WHERE clause

3. How do we call Procedure?
Procedures we call TWO ways:

CALL procedure_name(‘X’,’X’)
EXECUTE Procedure_name(‘X’,’X’)

4. What is package?
A package contains Related Procedures, and UDF (user defined functions)
Package has two parts: Package specification, and  Package Body.

5. How to drop a package?
DROP PACKAGE package_name  It drops both Body and specification
DROP PACKAGE BODY package_name  It drops only Body

6. How to raise error in PL/SQL?
RAISE value_error;   To do this we need to call RAISE_APPLICATION_ERROR procedure.

7. What is trigger?
A Trigger is fired when INSERT, UPDATE and DELETE. Also, trigger is fired when CREATE, ALTER, DROP, GRANT and REVOKE

8. How many triggers are there?
There are 3 types of triggers. One is
ROW LEVEL Trigger: It triggers for each row
STATEMENT LEVEL TRIGGER: It triggers once for each statement
Compound Trigger: It is to resolve Mutating problem. Default is “Statement level trigger”.

For Row level Triggers, we need to use NEW and OLD correlation identifiers.

9. INSTEAD OF INSERT on Views?
This we will use to create a Trigger in Views

10. What is mutating table error?
You are updating a row, and the same time you are selecting the column where you created Trigger for the same table. Then you will get this error.

Basically, Updating and selecting on the same Table. Solution is use Compound Trigger.

11. What are Bind variables and Substitution variables?
Bind variables:
:Bind_var   It improves performance. This can be used in executing SQL and PL/SQL statements. It has scope for entire script.
&sub_var  It prompts user to enter value
12. What is IN , OUT and IN OUT?
In stored procedure, these are related to variables. “IN” is default.

13. What are variables?
Variable is used to store data. If we store only one value, It is called Scalar variable.

14. %TYPE and %ROWTYPE?
%TYPE  The same data type of column
%ROWTYPE => The same data type of row

15. Basic structure of PL/SQL?
BEGIN
 NULL;
END;

Note: Other sections are optional
16. Life cycle of cursor?
OPEN cursor_name;
Fetch cursor_name INTO va_name;
Close cursor_name;

17. What is SETSERVEROUTPUT ON?
It enables buffer to store CHAR data printed to output.

18. What is Collections?
A collection is a list of related values.
TYPE type_name IS VARRAY(3) of NUMBER;
Col_name   type_name := type_name(NULL,NULL,NULL)

19. What is BULK COLLECT?
We use it to retrieve list of values into Collection, by SELECT statement
20. SQLERRM?
It is an error function. We can use it to get exact error and its number.