PLSQL - ADVANCED KNOWLEDGE

PLSQL - ADVANCED KNOWLEDGE

Multiple Choice

What is a stored procedure?

• A collection of precompiled SQL statements saved in the database

• A collection of precompiled Oracle statements saved in the database

• A collection of precompiled Microsoft statements saved in the database

• A collection of precompiled PLSQL statements saved in the database

Multiple Choice

What is the purpose of the 'FOR UPDATE' clause in a cursor in PL/SQL?

• To specify the condition for cursor execution

• To indicate that the cursor should be executed for each row affected by the triggering event

• To lock the rows in the result set so that they cannot be modified by other transactions

• To specify the timing of the cursor execution

Multiple Choice

Begin and End are mandatory in PLSQL code?

• yes

• no

• may be

• not at all

Multiple Choice

Name 4 attributes of explicit cursors?

• %FOUND,%NOTFOUND,%ROWCOUNT,%ISOPEN

• %FOUND,%NOTFOUND,%ISCLOSE,%ISOPEN

• %FOUND,%OPEN,%ROWCOUNT,%ISOPEN

• None of these

Multiple Choice

What is the advantage of using stored procedures?

• Difficult to debug

• Not reusable

• Reduce traffic between application and database server

• Increase memory usage of connections

Multiple Choice

The pre-defined exception TOO_MANY_ROWS is raised when

• PL/SQL ran out of memory or memory was corrupted.

• A cursor fetches value in a variable having incompatible data type.

• SELECT INTO statement returns more than one row.

• SELECT INTO statement returns one row.

Multiple Choice

Which of the following is not among the types of PL/SQL records?

• Table-based

• View-based

• Cursor-based records

• User-defined records

Multiple Choice

Which of the following is not a subprogram?

• Procedure

• Function

• Package

• Anonymous PL/SQL block

Multiple Choice

A set of statements in PLSQL are called as

• function

• program

• procedure

• Block

Multiple Choice

how to print a string "welcome to plsql" in PLSQL code?

• dbms_output.put_line('welcome to plsql')

• print('welcome to plsql')

• show('welcome to plsql')

• System.out.println("welcome to plsql")

Multiple Choice

What is the main purpose of using a trigger in a database?

• To perform a specific action when a certain event occurs in the database

• To create a new table in the database

• To modify the database schema

• To execute a stored procedure

• None of the given answers

CASE Statements

CASE statement gives you a clear way to handle conditional logic within PL/SQL blocks. It is a conditional control statement that allows you to execute different blocks of code based on the specified conditions.
It is particularly useful when dealing with multiple conditions and provides a more readable and maintainable alternative to nested IF-ELSE statements.
There are two primary types of CASE statements in PL/SQL:
Simple CASE and Searched CASE.

Simple CASE Statements

In a Simple CASE statement, the value of an expression is compared to constant values (predefined).
It is useful when you want to match a single expression with different constant values.
CASE statement is feature that is used to handle conditional logic within PL/SQL blocks. CASE statement provides more clear and readable alternative to nested IF-ELSE statements. By efficiently managing branching logic, the CASE statement enhances code clarity and maintainability in PL/SQL programming.

Simple CASE Statements

​The variable day_number is set to 1 that representing Monday.

• The Simple CASE statement then checks the value of day_number and assigns the corresponding day name to the variable day_name.

• The output will be The day is: Monday

Searched CASE Statements

In a Searched CASE statement each condition is evaluated independently.
It allows for more complex conditions such as comparisons, logical operators, and functions.

Searched CASE Statements

• The variable product_price is set to 120.50.

• The Searched CASE statement evaluates different conditions based on the value of product_price and assigns the appropriate category to the variable product_category.

• The output will be The product falls into the category: High Cost.

Multiple Choice

Consider the following code snippet: what will be the output?

DECLARE

a number(2) ;

BEGIN

FOR a IN REVERSE 10 .. 20 LOOP

END LOOP;

dbms_output.put_line(a);

END;

• 20

• 29

• 10

• 30

Multiple Choice

In which statement each condition is evaluated independently?

• Searched

• Case

• Simple

• Statements don't evaluated condition indepedently

PL/SQL NULL Statement

Within this language, the Null statement plays a crucial role in enhancing code readability and functionality.
The Null statement in PL/SQL is a construct or feature that represents a no-operation or empty statement. It is used when a statement is syntactically necessary but no action needs to be performed on that statement. Essentially, it signifies the placeholder where no code execution occurs.

The NULL statement works like a stand-in within PL/SQL code. It’s needed by the syntax but doesn’t do any particular job. It shows that no specific code has to run at that moment. This helps keep the code in good shape when a statement is a must but the statement does not need to take actual action.

It doesn’t do any task at all, it is just a placeholder signifying that nothing particular needs to be done. It’s like saying, “No code required here!”

PL/SQL NULL Statement

Creating Placeholders for Subprograms

Another significant application of the Null statement is creating placeholders within subprograms.
For instance, when defining a procedure or function body, placeholders might be needed:

In this case, the Null statement serves as a placeholder for potential future modifications or additions to the subprogram’s logic, allowing developers to structure code architecture more effectively.

PL/SQL NULL Statement

Using PL/SQL NULL Statement to Provide a Target for a GOTO

The Null statement can also act as a target for a GOTO statement, aiding in controlling program flow:












By utilizing the Null statement as a target for GOTO in the above code can navigate program execution to a designated placeholder within the code very easily.

Multiple Choice

Does CASE expression supports Searched CASE?

• Yes

• No

• No given information