Q2) Attempt any FOUR of the following (Out of FIVE) : [4 × 4 = 16]
Explain % type and % row type with an example.
%TYPE: is used to defined the data type of variable as the column name datatype specified for a table. If table column has datatype varchar2(30) then %type variable has same datatype varchar2(30).
Define as: vEmployeeName Employee.Name%TYPE;
Example
SET SERVEROUTPUT ON
DECLARE
vEmployeeName Employee.Name%TYPE;
BEGIN
SELECT Name INTO vEmployeeName
FROM Employee
WHERE ROWNUM = 1;
DBMS_OUTPUT.PUT_LINE(vEmployeeName);
END;
/
%ROWTYPE: Used to declare a record with the same types as found in the specified table, (it is like an array which defined all columns of a table with their datatype representation used with variable name with column_name of table).
Define as:rEmployee Employee%ROWTYPE;
Example
SET SERVEROUTPUT ON
DECLARE
rEmployee Employee%ROWTYPE;
BEGIN
rEmployee.Name := 'RAM';
rEmployee.sex := 'MALE';
DBMS_OUTPUT.PUT_LINE(rEmployee.Name);
DBMS_OUTPUT.PUT_LINE(rEmployee.Age);
END;
/
List and explain properties of transaction.
Transactions
provide the ACID properties:
- Atomicity. :-
The changes in a transaction are
atomic: either all operations that are part of the transaction occur or
none occurs. Either all or
none of the transaction operation is done.
- Consistency:- A transaction moves data between consistent
states. A transaction transfer
from one consistent (correct) state to another consistent state.
- Isolation :- Even though transactions can be executed
concurrently, no transaction sees another transaction's work in progress.
The transactions seem to run serially.
- A transaction
is isolated from other transactions. i.e. A transaction is not affected by
another transaction. Although multiple transactions execute concurrently
it must appear as if the transaction are running serially (one after the
other).
- Durability :- After a transaction completes successfully, its
changes survive subsequent failures. The
results of a transaction are permanent i.e. the result will never be lost
with subsequent failure, durability refers to long lasting i.e.
permanency.
A deadlock is a condition
where two or more transactions are waiting indefinitely for one another to give
up locks.
1.Wait Die Scheme(non
preemitive)
1.If the requesting
transaction is older than the transaction that holds the lock on the requested
data item, the requesting transaction is allowed to wait.
2.If the requesting
transaction is younger than the transaction that holds the lock on the
requested data item, the requesting transaction is aborted and rolled back
Example: Suppose
transactions T., T, and T. having timestamps for 5,10,15 respectively. If
T1 requests a data item held by T2 then T, will wait,.If T3 Requests a data
item held by T2 then T3 will be rolled back.
2.Wound Wait Scheme(Preemptive)
1.If the requesting
transaction is older than the transaction that holds the look on the requested
data item, the holding (younger) traction is aborted and rolled back Le, the
younger transaction in wonders by the older transaction.
2.If the requesting
transaction is younger than the transaction that holds the lock on the
requested data item, the requesting transaction in allowed to wait. That means,
the younger transactions wait for older ones.
3.Example: Suppose
transactions T1, T2, and T3, having timestamps 5, 10 and 15 respectively. In
the first case, transaction T1, (older) requests T2, (younger for the data
item. In the second case, T3. (younger) requests to T2 (older)
3.Timeout-Based
Schemes
A transaction waits
for a lock only for a specified amount of time. After that, the wait times out
and the transaction is rolled back.
Explain two-phase locking protocol in
detail.
- Two-Phase Locking (2PL) is a concurrency control
method which divides the execution phase of a transaction into three
parts.
- It ensures conflict serializable schedules.
- If read and write operations introduce the first
unlock operation in the transaction, then it is said to be Two-Phase
Locking Protocol.
This protocol can be divided into two phases,
1. In Growing Phase, a transaction obtains locks, but may not
release any lock.
2. In Shrinking Phase, a transaction may release locks, but may not
obtain any lock.
Explain RDBMS packages in detail.
A
package is an object in the form of a schema that segregates logically
connected items, types, and subprograms in PL/SQL. A package consists of two
parts: Package specification and Package body or definition.
The
package specification is like an interface to the application and is used to
declare variables, constants, types, exceptions, cursors, and subprograms. The
body of a package implements the subprograms and cursors declared in the
package specification.
Advantages
Of PL/SQL Package
·
It gives modularity to our code.
·
It helps to design the application easily.
· PLSQL Package helps to hide information with the help of
public and private items, data types, and subprograms.
· Package allows maintaining information over all the transactions without requiring to store it in the database.
·
Packages help improve performance of execution.
Package Specification
A package specification is
like an interface to the package. It takes care of the declaration of
variables, constants, types, exceptions, cursors, and subprograms.
|
CREATE
PACKAGE citi AS FUNCTION
p_strng RETURN VARCHAR2; END
citi; / |
Package Body
The
package body contains the implementation of the cursors and subprograms
declared in the package specification.
|
CREATE
OR REPLACE PACKAGE BODY citi AS --function
implemented FUNCTION
p_strng RETURN VARCHAR2 IS BEGIN RETURN
'Software Testing Help!'; END
p_strng; END
citi; / |
What is function ? Explain with an example.
Procedure and a function is, a function must always return a value, and on the other hand a procedure may or may not return a value. Except this, all the other things of PL/SQL procedure are true for PL/SQL function too.
Syntax to create a function:
1.CREATE [OR REPLACE] FUNCTION function_name[parameters]
2.[(parameter_name [IN | OUT | IN OUT] type [,...])]
3.RETURN return_datatype
4.{IS | AS}
5.BEGIN
6.< function_body >
7.END [function_name];
Here:
oFunction_name: specifies the name of the function.
o[OR REPLACE] option allows modifying an existing function.
oThe optional parameter list contains name, mode and types of the parameters.
oIN represents that value will be passed from outside and OUT represents that this parameter will be used to return a value outside of the procedure.
The function must contain a return statement.
oRETURN clause specifies that data type you are going to return from the function.
oFunction_body contains the executable part.
oThe AS keyword is used instead of the IS keyword for creating a standalone function.
PL/SQL Function Example
1.create or replace function adder(n1 in number, n2 in number)
2.return number
3.is
4.n3 number(8);
5.begin
6.n3 :=n1+n2;
7.return n3;
8.end;
9./
What is cursor ? Describe different types of cursor.
A cursor contains information on a select statement and the rows of data accessed by it.A cursor is used to referred to a program to fetch and process the rows returned by the SQL statement, one at a time. There are two
types of cursors:
- Implicit Cursors
- Explicit Cursors
The following table specifies the status of the cursor with each of its attribute.
Implicit Cursors
|
Attribute |
Description |
|
%FOUND |
Its return value is TRUE if DML statements like INSERT,
DELETE and UPDATE affect at least one row or more rows or a SELECT INTO
statement returned one or more rows. Otherwise it returns FALSE. |
|
%NOTFOUND |
Its return value is TRUE if DML statements like INSERT,
DELETE and UPDATE affect no row, or a SELECT INTO statement return no rows.
Otherwise it returns FALSE. It is a just opposite of %FOUND. |
|
%ISOPEN |
It always returns FALSE for implicit cursors, because
the SQL cursor is automatically closed after executing its associated SQL
statements. |
|
%ROWCOUNT |
It returns the number of rows affected by DML
statements like INSERT, DELETE, and UPDATE or returned by a SELECT INTO
statement. |
Explicit Cursors
Steps:
You must follow these steps while working with an explicit cursor.
1. Declare the cursor to initialize in the memory.
2. Open the cursor to allocate memory.
3. Fetch the cursor to retrieve data.
4. Close the cursor to release allocated memory.
Ex Explain data types in PL/SQL.
NUMBER(p,s)
Range: p= 1 to 38 s= -84 to 127
This datatype is used to store numeric data. Here, p is precision s is scale.
Example:
1. Age NUMBER(2); where , Age is a variable that can store 2 digits
2. percentage NUMBER(4,2); where, percentage is a variable that can store 4 (p)
digits before decimal and 2 (s) digits after decimal.
CHAR(size)
Range: 1 to 2000 bytes
- This datatype is used to store alphabetical string of fixed length.
- Its value is quoted in single quotes.
- Occupies the whole declared size of memory even if the space is not utilized by the data.
VARCHAR(size)
Range: 1 to 2000 bytes
- This datatype is used to store alphanumeric string of variable length.
- Its value is quoted in single quotes.
- utilized by the data.
- VARCHAR2(size)
Range: 1 to 4000 bytes
- This datatype is used to store alphanumeric string of variable length.
- Its value is quoted in single quotes.
- It releases the unused space in memory, hence saving the unused space.
DATE
Range: 01-Jan-4712 BC to 31-DEC-9999
- It stores the data in date format DD-MON-YYYY
- The value for this datatype is written in single quotes.
%TYPE
- It stores value of that variable whose datatype is unknown and when we want the variable to inherit the datatype of the table column.
- Also, its value is generally being retrieved from an existing table in the database, hence it takes the datatype of the column for which it is used.
BOOLEAN
- This datatype is used in conditional statements.
- It stores logical values.
- It can be either TRUE or FALSE
