Procedure Programming in SQL — Winter 2018

Answer the following question. i. What is Index? ii. How can we use %rowtype? iii. How can we use comment in PL/SQL? iv. ___________ method is used to displayed message to the user on the VDU screen. v. What is the use of GOTO statement? vi. The data stored in a Cursor is called __________. vii. Packages allow overloading of Procedure and Function. (True/False)

Discuss Sequence with its keywords and parameters with example.

What do you mean by PL/SQL? Describe the Generic PL/SQL Block in detail.

Write a short note on: Running sum & Decode.

Explain Iterative Control Structure used in PL/SQL in detail with example.

What is Cursor? Explain various types of Cursor with example.

Explain Cursor for loops and Parameterized Cursors.

What is Lock? Explain different level of Lock in detail with example.

How Exception Handling can work with Oracle Engine? Discuss Oracle’s Predefined Exception Handlers.

WORKER(workerid, name, wage_per_hour, specialized_in, manager_id) Write a procedure to display top five highest paid workers who are specialized in “PAINTING”.

Differentiate Procedure and Function. Also discuss the advantages of using Procedure or Function.

What do you mean by Package? Describe various components of a Package in detail with example.

Discuss Overloading Procedure and Function with benefits of Overloading.

What do you mean by Trigger? Explain various types of Trigger used in PL/SQL with example.

Write a database trigger to check that joining_date field is not empty and also check that the joining_date should be greater than birthdate field in the employee table.

What is the use of Varray in PL/SQL? How can we declare Varray in PL/SQL? Explain it in detail.

Write a short note on: Save Point, Commit and Rollback.