Scribd
Upload
6 views2 pages

Advanced PLSQL Model Question Paper

The document is a model question paper for Advanced PL/SQL, consisting of three sections: Section A contains 10 one-mark questions defining key PL/SQL concepts, Section B includes 10 three-mark questions explaining various PL/SQL topics, and Section C features 6 five-mark questions requiring the writing of PL/SQL programs, functions, procedures, and triggers.

Uploaded by

risar44847
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
6 views2 pages

Advanced PLSQL Model Question Paper

The document is a model question paper for Advanced PL/SQL, consisting of three sections: Section A contains 10 one-mark questions defining key PL/SQL concepts, Section B includes 10 three-mark questions explaining various PL/SQL topics, and Section C features 6 five-mark questions requiring the writing of PL/SQL programs, functions, procedures, and triggers.

Uploaded by

risar44847
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

MODEL QUESTION PAPER - ADVANCED PL/SQL

SECTION A — 1 MARK QUESTIONS (10 × 1 = 10 Marks)


1. Define PL/SQL.
Answer: PL/SQL is Oracle’s procedural extension of SQL used to write blocks of code.

2. What is a cursor?
Answer: A cursor is a pointer to the result set of a SQL query.

3. Define trigger.
Answer: A trigger is a stored PL/SQL block that executes automatically when an event occurs.

4. What is a stored procedure?


Answer: A stored procedure is a named PL/SQL block stored in the database.

5. What is a function in PL/SQL?


Answer: A function is a stored program that returns a single value.

6. Define exception handling.


Answer: Exception handling is used to handle runtime errors.

7. What is a sequence?
Answer: A sequence generates unique numeric values automatically.

8. What is a view?
Answer: A view is a virtual table based on SQL query results.

9. Define index.
Answer: An index speeds up data retrieval.

10. What is recursion?


Answer: Recursion is when a function calls itself.

SECTION B — 3 MARK QUESTIONS (10 × 3 = 30 Marks)


Explain implicit and explicit cursors.
Answer: Implicit cursors are automatically created. Explicit cursors are created by programmer for
multiple rows.

Explain types of triggers.


Answer: Types include BEFORE, AFTER and INSTEAD OF triggers.

Differentiate between procedure and function.


Answer: Procedure may not return value. Function must return value.

Explain PL/SQL records.


Answer: Record is a collection of related data stored together.

Explain control statements.


Answer: Control statements include IF, LOOP, WHILE and CASE.

SECTION C — 5 MARK QUESTIONS (6 × 5 = 30 Marks)


Write a PL/SQL program using Explicit Cursor.
Answer:
DECLARE
CURSOR emp_cursor IS
SELECT empno, ename FROM emp;

v_empno [Link]%TYPE;
v_ename [Link]%TYPE;

BEGIN
OPEN emp_cursor;

LOOP
FETCH emp_cursor INTO v_empno, v_ename;
EXIT WHEN emp_cursor%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_ename);

END LOOP;

CLOSE emp_cursor;
END;

Write a function to calculate factorial using recursion.


Answer:
CREATE OR REPLACE FUNCTION factorial(n NUMBER)
RETURN NUMBER IS
BEGIN
IF n = 1 THEN
RETURN 1;
ELSE
RETURN n * factorial(n-1);
END IF;
END;

Write a stored procedure to increase salary.


Answer:
CREATE OR REPLACE PROCEDURE raise_salary(emp_id NUMBER) IS
BEGIN
UPDATE emp
SET sal = sal + 1000
WHERE empno = emp_id;

COMMIT;
END;

Write a trigger example.


Answer:
CREATE OR REPLACE TRIGGER salary_trigger
BEFORE INSERT ON emp
FOR EACH ROW
BEGIN
:[Link] := :[Link] + 500;
END;

You might also like