61) What are the actual parameters and formal parameters?
Actual parameters:
The actual parameters are the variables or an expression which is referred to as parameters that
appear in the procedure call statement.
Example:
raise_sal(emp_num, merit+amount);
In this above example, “emp_num” and “amount” are the two actual parameters.
Formal parameters:
The formal parameters are the variables which are declared in the procedure header and are
referenced in the procedure body.
Example:
PROCEDURE raise_sal( emp_id INTEGER) IS
curr_sal REAL:
………..
BEGIN
SELECT sal INTO cur_sal FROM emp WHERE empno = emp_id;
…….
END raise_sal;
In the above example, “emp_id” acts as a formal parameter.
62) What do you understand by Exception handling in PL/SQL?
In PL/SQL, the exception is raised when an error occurs. In other terms, to handle unpredicted events such as when the PL/SQL scripts are terminated unexpectedly than an error-handling code is included in the program. In PL/SQL, all exception handling code is placed in an EXCEPTION section. There are three types of Exception Handling in PL/SQL.
1. Predefined Exceptions: These are the exceptions which have common errors with predefined names.
2. Undefined Exceptions: These are the exceptions which have less common errors with no predefined names.
3. User-defined Exceptions: These exceptions don’t cause runtime error but violate the business rules.
63) What are Views and why are they used?
A View is a logical representation of data subsets from one or more tables. It is a logical table that is based on a SQL table or another view. A view doesn’t contain the data of its own but is like a window through which data from tables can be viewed or changed. The tables on which a view is based are called Base Tables. The View is stored as a SELECT statement in the data dictionary. View definitions can be retrieved from the data dictionary table: USER_VIEWS.
The views are used for the following purposes.
• For restricting the data access.
• For making the complex queries easier.
• For providing the data Independence.
• Views provide groups of the user for accessing the data according to their requirement.
64) What is the difference between functions, procedures, and packages in PL/SQL?
Function: A function is specified with a return type and must return a value specified in that type. The main purpose of a PL/SQL function is to compute and return a single value.
Procedure: A procedure doesn’t have a return type and it doesn’t return any value, but it can have a return statement which stops its execution and returns to the caller. A procedure returns multiple values, unlike a general function.
Package: A package is a group of functions, procedures, variables, and record TYPE statements. It is a schema object which groups logically related PL/SQL types, items, and subprograms. It provides modularity for application development. It is used for hiding the information from unauthorized users.
65) Explain the difference between Triggers and Constraints?
Triggers:
• Triggers are stored as separate objects.
• It is fired when an event occurs and so the triggers are fired after constraints.
• Triggers perform faster in comparing table to table.
• It is for the entire table.
• Triggers are the stored procedures that are automatically executed and so it won’t check for data integrity
Constraints:
• A constraint on a table is stored along with the table definition.
• Constraints are fired soon when the tables are used.
• Constraints performance is slow while comparing the memory location to the table.
• The constraint is applied only for the column of a table.
• Constraints prevent duplicate and invalid data entries.
66) What is meant by Literal in PL/SQL?
Literals in PL/SQL are the specific string, character, numerical, or Boolean values not constituted by an identifier.
Below are the different types of literals that PL/SQL supports:-
• Numeric Literals
• String Literals
• Date and Time Literals
• Boolean Literals
• Character Literals
67) What is meant by Global Variables?
In PL/SQL, global variables are stated within the farthest block or a package. These variables can store a maximum length of 255 characters of the character string. Moreover, we cannot formally declare global variables similar to the local ones.
68) What is a Ref Cursor and its different types in PL/SQL?
In PL/SQL, Ref Cursor is a data type whose value is the memory location of a database query workspace. Two types of Ref Cursors are available- Strong and Weak Ref Cursors.
69) Mention the different types of PL/SQL packages.
The PL/SQL package includes the following:-
• Functions
• Procedures
• Cursors
• Variables
• Table and Statements of Record type
• Names of Exceptions
• Pragmas for connecting an error number with an anomaly or exception
70) What is meant by Raise_application_error?
This package procedure enables us to send a user-defined error message from a stored program/block. It raises an exception based on the error provided by the user. We can report an error to the caller using this process rather than returning unhandled exceptions.
71) What are different methods to trace the PL/SQL code?
Tracing the code is a technique that measures performance during the runtime. The different methods for tracing the code includes the following:
• DBMS_APPLICATION_INFO
• DBMS_TRACE
• DBMS_SESSION and DBMS_MONITOR
• trcsess and tkprof utilities
72) Can you use IF statement inside a SELECT statement? How?
Yes. The IF statement is used as a DECODE in versions 9 and above.
Example:
SELECT day_of_week,
DECODE (number, 0, ‘Sunday’,
1, ‘Monday’,
2, ‘Tuesday’,
3, ‘Wednesday’,
4, ‘Thursday’,
5, ‘Friday’,
6, ‘Saturday’,
‘No match’) result FROM weekdays;
73) What is the difference between %TYPE and %ROWTYPE? Give an example
%TYPE:
It is the attribute that declares a variable of the same data type as of a table column.
Example:
DECLARE
studentId
students.student_id%TYPE;
%ROWTYPE:
It is the attribute that declares a variable of type RECORD which has the same structure as that
of a table row. The row is a RECORD that contains fields having the same data types and names
as that of columns of a table or view.
Example:
DECLARE
Stud_rec
students.%ROWTYPE;
74) Why do we use database triggers? Give the syntax of a trigger.
The trigger is a stored procedure which invokes automatically when an event occurs. The event could be any of these DML commands like insert, update, delete, etc.
Syntax:
create trigger [trigger_name]
[before | after]
on [table_name]
[for each row]
[trigger_body]
75) What are the different types of cursors in PL/SQL?
There are two different types of cursors available in PL/SQL.
Implicit cursor: PL/SQL applies these implicit cursors for the DML commands such as INSERT, UPDATE, DELETE and SELECT statements which return a single row.
Explicit cursor: It is created by the programmer for queries that return more than a single row.
Syntax:
CURSOR is
SELECT statement;
OPEN ;
FETCH INTO ;
CLOSE ;
76) List some cursor attributes in PL/SQL?
• %ISOPEN: This attribute checks if the cursor is open.
• %ROWCOUNT: This attribute returns the number of rows that are updated, deleted or fetched.
• %FOUND: This attribute checks if the cursor has fetched any row and returns boolean if it finds the record.
• %NOT FOUND: This attribute checks if the cursor has fetched any row and returns boolean if it doesn’t find any record.
77) When a DML statement is executed, in which cursor attributes, the outcome of the statement is saved?
The outcome of the statement is saved in 4 cursor attributes. They are.
1. SQL%FOUND
2. SQL%NOTFOUND
3. SQL%ROWCOUNT
4. SQL%ISOPEN
78) Explain the difference between commit and save point.
• COMMIT: It is applied for making the database changes permanent. All the save points are erased and the transaction ends. Once it is committed, a transaction cannot be rolled back.
• SAVEPOINT: It is applied to set points during a transaction such that a programmer can roll-back it later. It is helpful during a series of transactions that can be divided into groups having a savepoint.
79) What are the ways of commenting in a PL/SQL code?
Comments are the text which is used while implementing the code to enhance the readability such that a reader is able to understand the code. These comment codes are not executed. There are two types of comments in PL/SQL.
Single line comment: This comment starts with double “–”.
Example:
DECLARE
num NUMBER(2); — it is a local variable.
BEGIN
Multi-line comment: This comment starts with “/*” and ends with “*/”.
Example:
BEGIN
num := &p_num; /* This is a host variable used in program body */
……….
END
80) What are the three modes of parameter?
The three modes of parameters are.
1. IN parameters: These parameters allow you to pass values to the procedure being called and can be initialized to default values. It acts like a constant and cannot be assigned any value.
2. OUT parameters: These parameters return value to the caller and they must be specified. It acts like an uninitialized variable and cannot be used in an expression.
3. IN OUT parameters: These parameters pass initial values to a procedure and return updated values to the caller. It acts like an initialized variable and should be assigned a value.
