Mastering Subprograms & Triggers in PL/SQL (MIT412) - Updated 2025/2026 Study Notes for, Exercises of Nursing

Download Mastering Subprograms & Triggers in PL/SQL (MIT412) - Updated 2025/2026 Study Notes for and more Exercises Nursing in PDF only on Docsity!

. Mastering Subprograms &

Triggers in PL/SQL (MIT412)

- Updated 2025/2026 Study

Notes for First-Attempt Pass

What does PL/SQL stand for?

Procedural Language/Structured Query Language

What is the primary purpose of PL/SQL?

It is an extension to SQL designed for the Oracle Database.

How does SQL differ from PL/SQL?

SQL is declarative, while PL/SQL is imperative.

What is a key characteristic of PL/SQL?

It combines program logic and control flow with SQL.

What are the three main sections of a PL/SQL block?

Declaration Section, Executable Section, Exception-handling Section.

What is the purpose of the DECLARE keyword in PL/SQL?

It begins the Declaration Section where variables and constants are defined.

What does the BEGIN keyword signify in a PL/SQL block?

It marks the start of the Executable Section.

To manage errors such as hardware failures or application logic errors.

What command must be used to display output in SQL*Plus?

SET SERVEROUTPUT ON

What does the DBMS_OUTPUT.PUT_LINE procedure do?

It outputs a string to the screen.

What is a key feature of PL/SQL's structure?

It is highly structured, readable, and accessible.

What programming language is PL/SQL based on?

The Ada Programming Language.

What is the purpose of nesting blocks in PL/SQL?

To build complex applications by placing blocks within other blocks.

What does the END keyword signify in a PL/SQL block?

It marks the end of the Executable Section and must be terminated with a semicolon.

What is the role of the PL/SQL compiler?

To compile PL/SQL code for execution.

What is a cursor in PL/SQL?

A pointer that allows you to retrieve rows from a result set.

What is the significance of using composite datatypes in PL/SQL?

They allow for the grouping of related data elements.

What is a procedure in PL/SQL?

A named block of code that performs a specific task.

What is a trigger in PL/SQL?

A stored procedure that automatically executes in response to certain events on a table.

What does the term 'dependencies' refer to in PL/SQL?

The relationships between database objects that affect how they interact.

What is a literal in PL/SQL?

A literal is an explicit value such as a numeric, character string, date, or Boolean that can be stored in a variable.

How do you indicate comments in PL/SQL?

Single-line comments use two dashes (--), and multi-line comments start with / and end with /.

What are the scalar data types in PL/SQL?

Scalar data types include numeric types (NUMBER, INTEGER), character types (CHAR, VARCHAR2), Boolean, and date/time types.

What is the syntax for declaring a variable in PL/SQL?

variable_name datatype [NOT NULL] [:= initial_value];

What is the purpose of the %TYPE attribute in PL/SQL?

It allows a variable to inherit the data type of a column in a table, preventing data type mismatch.

What is the basic structure of a PL/SQL block?

The structure includes DECLARE for variable declarations, BEGIN for SQL and PL/SQL statements, EXCEPTION for error handling, and END to close the block.

How do you assign a value to a variable in PL/SQL?

Use the assignment operator (:=) to assign a value to a variable.

What is the purpose of the DBMS_OUTPUT.PUT_LINE function?

It is used to display output in PL/SQL.

What SQL operations can be performed in PL/SQL?

You can perform SELECT, INSERT, UPDATE, and DELETE operations.

What is the difference between CHAR and VARCHAR2 in PL/SQL?

CHAR is a fixed-length data type, while VARCHAR2 is variable-length.

What is a CLOB in PL/SQL?

A CLOB (Character Large Object) is used for very large text data beyond 32,767 characters.

What are the available SQL functions in PL/SQL?

Single-row character functions, number functions, date functions, data-type conversion functions, and miscellaneous functions.

It adds new records to a database table.

What does the UPDATE statement do in PL/SQL?

It modifies existing records in a database table.

What is the function of the DELETE statement in PL/SQL?

It removes records from a database table.

What function is used to get the length of a string in PL/SQL?

LENGTH

How do you convert a string to uppercase in PL/SQL?

Using the UPPER function

What operator is used for string concatenation in PL/SQL?

|| (double pipe)

What is the syntax to declare a variable in PL/SQL?

variable_name datatype := initial_value;

What does the SIGN function do in PL/SQL?

It returns the sign of a number.

How do you calculate the number of months between two dates in PL/SQL?

Using the MONTHS_BETWEEN function.

What are the two types of data-type conversions in PL/SQL?

Implicit conversions and Explicit conversions.

What is an example of an explicit conversion function in PL/SQL?

TO_NUMBER

What is the basic structure of an IF statement in PL/SQL?

IF condition THEN statements; END IF;

What is the purpose of the ELSE clause in an IF statement?

To execute alternative statements if the condition is FALSE.

What are the three types of loop control structures in PL/SQL?

BASIC LOOP, FOR LOOP, and WHILE LOOP.

What is the syntax for a basic LOOP statement?

<> LOOP statements; END LOOP loop_label;

What should be included in the body of a LOOP to avoid infinite loops?

At least one EXIT or EXIT WHEN statement.

What is the output of the following code: DBMS_OUTPUT.PUT_LINE('Hello');?

'Hello' will be printed to the output.

What does the DBMS_OUTPUT.PUT_LINE function do?

It outputs a line of text to the console.

What is the purpose of the LENGTH function in PL/SQL?

To return the length of a string.

How do you declare a variable of type BINARY_INTEGER in PL/SQL?

v_variable_name BINARY_INTEGER;

What is the result of the following code: v_c := TO_NUMBER('-123456') + TO_NUMBER('+987654');?

The result is 864198.

What does the MONTHS_BETWEEN function return?

The number of months between two dates.

What is the purpose of the TRIM function in PL/SQL?

To remove leading and trailing spaces from a string.

What does the INITCAP function do?

It capitalizes the first letter of each word in a string.

What is the purpose of the LOOP statement in PL/SQL?

To execute the loop body at least once and when the number of iterations is uncertain.

What statement allows you to terminate a loop prematurely?

EXIT statement

What are the steps for using explicit cursors?

Declare a cursor, then use OPEN, FETCH, and CLOSE statements.

What is the syntax to declare a cursor?

CURSOR cursor_name IS query;

What does the FETCH statement do?

Retrieves a row from a cursor into specified variables.

What is the purpose of the CONTINUE statement in PL/SQL?

To skip the current iteration of a loop and continue with the next iteration.

What is the structure of a PL/SQL WHILE loop?

WHILE condition LOOP statements; END LOOP;

When does a WHILE loop terminate?

When the condition evaluates to FALSE or NULL.

What happens if the condition of a WHILE loop is FALSE before entering the loop?

The WHILE loop does not execute at all.

What is the purpose of the REVERSE keyword in a FOR LOOP?

To iterate through the loop in reverse order.

What are the attributes of an explicit cursor?

%ISOPEN, %NOTFOUND, %FOUND, %ROWCOUNT.

What does the %NOTFOUND attribute indicate?

Evaluates to TRUE if the most recent FETCH did not return a row.

What does the %FOUND attribute indicate?

Evaluates to TRUE if the most recent FETCH returned a row.

What does the %ROWCOUNT attribute indicate?

Evaluates to the total number of rows fetched so far.