 |
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.
©2020 HKR Trainings. All rights Reserved.