 |
PL stands for Procedural Language which is an extension to SQL where the developer writes the complex database interaction by using the procedures, control structures like branching and iteration, modules and functions. It supports both dynamic and static SQL.
In this article, you can go through the set of Oracle PL/SQL interview questions most frequently asked in the interview panel. This will help you crack the interview as the topmost industry experts curate these at HKR training.
Let us have a quick review of the Oracle PL/SQL interview questions.
Ans:
SQL:
PL/SQL:
Ans: The benefits of PL/SQL Packages are.
Ans:
Ans: Tracing the code is a technique that measures performance during the runtime. The different methods for tracing the code includes the following:
Ans: Yes. The IF statement is used as a DECODE in versions 9 and above.
Example:
SELECT day_of_week,
DECODE (number, 0, 'Sunday',
1, 'Monday',
2, 'Tuesday',
3, 'Wednesday',
4, 'Thursday',
5, 'Friday',
6, 'Saturday',
'No match') result FROM weekdays;
Ans:
%TYPE:
It is the attribute that declares a variable of the same data type as of a table column.
Example:
DECLARE
studentId
students.student_id%TYPE;
%ROWTYPE:
It is the attribute that declares a variable of type RECORD which has the same structure as that of a table row. The row is a RECORD that contains fields having the same data types and names as that of columns of a table or view.
Example:
DECLARE
Stud_rec
students.%ROWTYPE;
Ans: The trigger is a stored procedure which invokes automatically when an event occurs. The event could be any of these DML commands like insert, update, delete, etc.
Syntax:
create trigger [trigger_name]
[before | after]
on [table_name]
[for each row]
[trigger_body]
Ans: There are two different types of cursors available in PL/SQL.
Implicit cursor: PL/SQL applies these implicit cursors for the DML commands such as INSERT, UPDATE, DELETE and SELECT statements which return a single row.
Explicit cursor: It is created by the programmer for queries that return more than a single row.
Syntax:
CURSOR is
SELECT statement;
OPEN ;
FETCH INTO ;
CLOSE ;
Ans:
Ans: The outcome of the statement is saved in 4 cursor attributes. They are.
Ans:
Ans: Comments are the text which is used while implementing the code to enhance the readability such that a reader is able to understand the code. These comment codes are not executed. There are two types of comments in PL/SQL.
Single line comment: This comment starts with double “–”.
Example:
DECLARE
num NUMBER(2); — it is a local variable.
BEGIN
Multi-line comment: This comment starts with “/*” and ends with “*/”.
Example:
BEGIN
num := &p_num; /* This is a host variable used in program body */
……….
END
Ans: The three modes of parameters are.
Ans:
Actual parameters:
The actual parameters are the variables or an expression which is referred to as parameters that appear in the procedure call statement.
Example:
raise_sal(emp_num, merit+amount);
In this above example, “emp_num” and “amount” are the two actual parameters.
Formal parameters:
The formal parameters are the variables which are declared in the procedure header and are referenced in the procedure body.
Example:
PROCEDURE raise_sal( emp_id INTEGER) IS
curr_sal REAL:
………..
BEGIN
SELECT sal INTO cur_sal FROM emp WHERE empno = emp_id;
…….
END raise_sal;
In the above example, “emp_id” acts as a formal parameter.
Ans: In PL/SQL, the exception is raised when an error occurs. In other terms, to handle unpredicted events such as when the PL/SQL scripts are terminated unexpectedly than an error-handling code is included in the program. In PL/SQL, all exception handling code is placed in an EXCEPTION section. There are three types of Exception Handling in PL/SQL.
Ans: A View is a logical representation of data subsets from one or more tables. It is a logical table that is based on a SQL table or another view. A view doesn’t contain the data of its own but is like a window through which data from tables can be viewed or changed. The tables on which a view is based are called Base Tables. The View is stored as a SELECT statement in the data dictionary. View definitions can be retrieved from the data dictionary table: USER_VIEWS.
The views are used for the following purposes.
Ans:
Example:
CREATE PROCEDURE get_customer_details @age nvarchar(30), @city nvarchar(10)
AS
BEGIN
SELECT * FROM customers WHERE age = @age AND city = @city;
END;
Ans:
DECLARE
n NUMBER;
avg NUMBER :=0 ;
sum NUMBER :=0 ;
count NUMBER :=0 ;
BEGIN
n := &enter_a_number;
WHILE(n<>0)
LOOP
count := count+1;
sum := sum+n;
n := &enter_a_number;
END LOOP;
avg := sum/count;
DBMS_OUTPUT.PUT_LINE(‘the average is’||avg);
END;
Ans:
Select * from emp where sal= (select max(sal) from emp)
Ans:
Select max(sal) from emp where sal not in (select max(sal) from emp)
Ans:
Function: A function is specified with a return type and must return a value specified in that type. The main purpose of a PL/SQL function is to compute and return a single value.
Procedure: A procedure doesn’t have a return type and it doesn’t return any value, but it can have a return statement which stops its execution and returns to the caller. A procedure returns multiple values, unlike a general function.
Package: A package is a group of functions, procedures, variables, and record TYPE statements. It is a schema object which groups logically related PL/SQL types, items, and subprograms. It provides modularity for application development. It is used for hiding the information from unauthorized users.
Ans:
SET SERVER OUTPUT ON
DECLARE
BEGIN
FOR i IN REVERSE 1..99
LOOP
IF Mod(i,3) = 0 THEN
DBMS_OUTPUT.PUT_LINE(i);
END IF;
END LOOP;
END;
/
Ans:
Triggers:
Constraints:
Ans:
Declare
Cursor c1 is select ename, sal from emp;
v_ename varchar2(10);
v_sal number(10);
begin
open c1;
loop
fetch c1 into v_ename, v_sal;
exist when c1 % notfound;
dbms_output.put_line(v_name ||’ ‘||v_sal);
end loop;
close c1;
end;
/
Ans: The PLV msg allows for.
Ans: PL/Vision offers two packages for managing the transaction processing in PL/SQL. It is PLVcmt and PLVrb.
PLVcmt: This package wraps the logic and complexity to deal with commit processing.
PLVrb: This package provides a programmatic interface to roll-back activity in PL/SQL.
Ans: Use the J format string.
SQL > select to_char(to_date('29-Mar-2013','dd-mon-yyyy'),'J') as julian from dual;
JULIAN
Ans: MERGE is used to combine multiple DML statements into one.
Syntax :
merge into tablename
using(query)
on(join condition)
when not matched then
[insert/update/delete] command
when matched then
[insert/update/delete] command
Ans: The spool command is used for printing the output of SQL statements in a file.
spool/tmp/sql_outtxt
select emp_name, emp_id from emp where dept='accounts';
spool off;
Ans:
PLVprs: It is an extension for string parsing for PL/SQL, and the functionality of string parsing works at the lowest level.
PLVprsps: It is the highest level package used in parsing the PL/SQL source code into separate atomics. It relies on other parsing packages to get the work done.
Batch starts on 3rd Mar 2021, Weekday batch
Batch starts on 7th Mar 2021, Weekend batch
Batch starts on 11th Mar 2021, Weekday batch
©2021 HKR Trainings. All rights Reserved.