PL/SQL (javatpoint)

Question 1

Differentiate between %ROWTYPE and TYPE RECORD.

Answer 1

%ROWTYPE is used when a query returns an entire row of a table. TYPE RECORD is used when a query returns column of different tables or views.

Question 2

Explain uses of cursor.

Answer 2

Cursor is a named private area in SQL from which information can be accessed. It is required to process each row individually for queries which return multiple rows.

Question 3

Show code of a cursor for loop.

Answer 3

FOR smp_rec in C1 LOOP totalSal = totalSal + smp_rec; END LOOP;

Question 4

Explain the uses of database trigger.

Answer 4

A PL/SQL program unit associated with a particular database table is called a database trigger. It is used for: 1) Audit of data modifications. 2) Log events transparently. 3) Enforce complex business rules. 4) Maintain replica tables. 5) Derive column values. 6) Implement complex security authorizations.

Question 5

What are two types of exceptions?

Answer 5

User defined Predefined

Question 6

List some predefined exceptions.

Answer 6

DUP_VAL_ON_INDEX ZERO_DIVIDE NO_DATA_FOUND TOO_MANY_ROWS CURSOR_ALREADY_OPEN Etc.

Question 7

Q1 What is PL/SQL?

Answer 7

A procedural language which has interactive SQL, as well as constructs like conditional branching and iteration. Developed by Oracle in early 90s.

Question 8

Q2 What is PL/SQL table? Why is it used?

Answer 8

Objects of type tables are called PL/SQL that are modeled after database tables. It is a way to provide arrays. They can be used to move bulk data. They simplify moving collections of data.

Question 9

Q3 What are the datatypes available in PL/SQL?

Answer 9

Scalar: number, varchar2, date, char, long, Boolean, etc. Composite: record, table, etc.

Question 10

Q4 What is the basic structure of PL/SQL?

Answer 10

PL/SQL uses BLOCK structure as its basic structure. Each PL/SQL program consists of SQL and PL/SQL statements which form a PL/SQL block. Each block contains 3 sections: 1. Declaration (optional) 2. Execution 3. Execution handling (optional)

Question 11

Q5 What is the difference between FUNCTION, PROCEDURE, and PACKAGE?

Answer 11

Function: Computes and returns a value. Return type is specified and return value must be of that type. Procedure: Similar to a function but with no return value. Package: A schematic object which groups logically related PL/SQL types, variables, functions, and procedures.

Question 12

Q6 Define exception. What are the types of exceptions?

Answer 12

Exceptions are the error handling part of PL/SQL. pre_defined user_defined

Question 13

Q7 How is exception different from error?

Answer 13

Whenever an Error occurs an Exception arises. Error is a bug whereas exception is a warning or error condition.

Question 14

Q8 What is the main reason behind using an index?

Answer 14

Faster access of data blocks in the table.

Question 15

Q10 What is maximum number of triggers that can be applied on a single table?

Answer 15

12

Question 16

Q11 How many types of triggers?

Answer 16

12 types with combination of: 1. BEFORE or AFTER 2. Row or statement level 3. INSERT, UPDATE, or DELETE

Question 17

Q12 What is stored procedure?

Answer 17

A sequence of statements or a named PL/SQL block which performs one or more functions. It is stored in the database and can be repeatedly executed. It is stored as a schematic object. It can be nested, invoked, and parameterized.

Question 18

Q13 How to execute a stored procedures?

Answer 18

From SQL prompt: EXECUTE procedure_name; EXEC procedure_name; procedure_name;

Question 19

Q14 Advantages of stored procedure?

Answer 19

Modularity, extensibility, reusability, maintainability, and one time compilation

Question 20

Q15 What are the cursor attributes used in PL/SQL?

Answer 20

%ISOPEN: checks whether cursor is open or not. %ROWCOUNT: returns number of rows affected by DML operations: INSERT, UPDATE, DELETE, or SELECT %FOUND: checks whether cursor has fetched any row. If yes, TRUE. %NOTFOUND: checks whether cursor has fetched any row. If no, TRUE.

Question 21

Q16 What is consistency?

Answer 21

It means that each user sees the consistent view of the data. An example is scenario in which money is transferred from account A to Account B. Only after both debit of A and credit of B are completed will users see any change to each account.

Question 22

Q17 What is cursor and why it is required?

Answer 22

It is a temporary work area created in system memory when an SQL statement is executed. A cursor is required to process rows individually for queries.

Question 23

Q18 How many types of cursors are available in PL/SQL?

Answer 23

Implicit Explicit

Question 24

What are schema objects?

Answer 24

Database objects that contain data or govern or perform operations on data. Each schema object belongs to a specific schema. An Oracle database associates a separate schema with each database user. Example of schema objects: Tables Views Indexes Procedures Functions Packages

Question 25

Q9 what are PL/SQL exceptions? Tell me any three.

Answer 25

1) Too_many_rows 2) No_data_found 3) Value_error 4) Zero_error Etc.