PL/SQL面试题汇总

1. What is the purpose of the PL/SQL language?

PL/SQL is an extension of SQL.

SQL is non-procedural.

PL/SQL is a procedural language designed by Oracle to overcome the limitations that exist in SQL.

2. Say True or False . IF False , explain why.

Routines written in PL/SQL can be called in Oracle call interface, Java, Pro*C/C++, COBOL etc.     (True)

 3. State few notable characteristics of PL/SQL.

• Block-structured language
• Stored procedures help better sharing of application
• Portable to all environments that support Oracle
• Integration with the Oracle data dictionary.

4. Name few schema objects that can be created using PL/SQL?

• Storage procedures and functions
• Packages
• Triggers
• Cursors

5. State some features or programming constructs supported by PL/SQL.

• Variable and constants
• Embedded SQL support
• Flow control
• Cursor management
• Exception handling
• Stored procedures and packages
• Triggers

6.What are the three basic sections of a PL/SQL block?

• Declaration section
• Execution section
• Exception section

7.What is wrong in the following assignment statement?

balance = balance + 2000;

Use of wrong assignment operator. The correct syntax is: balance := balance + 2000;

8.Write a single statement that concatenates the words ‘Hello’ and ‘World’ and assign it in a variable named greeting.

greeting := ‘Hello’ || ‘World’;

9.What is the purpose of %type data type? Explain with example.

It assigns a variable the same data type used by the column, for which the variable is created. For example,

dcode := dept.detpno%type;

The variable dcode is created with the same data type as that of the deptno column of the dept table.

10.What is the purpose of %rowtype data type? Explain with example.

It declares a composed variable that is equivalent to the row of a table. After the variable is created, the fields of the table can be accessed, using the name of this variable.

For example

emptype := emp%rowtype;

name := emptype.empname;

11.What is a PL/SQL package?

A package is a file that groups functions, cursors, stored procedures, and variables in one place.

12.What is a trigger?

A trigger is a PL/SQL program that is stored in the database and executed immediately before or after the INSERT, UPDATE, and DELETE commands.

 

13. What are the PL/SQL cursors?

Oracle uses