Accreditation Bodies
Accreditation Bodies
Accreditation Bodies
Supercharge your career with our Multi-Cloud Engineer Bootcamp
KNOW MOREPL SQL is a procedural language that is designed for SQL statements within its syntax. Oracle database server compiles the PL SQL program units and is kept inside a database. Whether you are a beginner or an intermediate or an experienced PL SQL professional, this guide will aid you in increasing your confidence and knowledge of PL/SQL. The below write-up comprises a systematic and sequential set of 95 PL/SQL interview questions and answers with 35 of them being of basic-level, 34 intermediate-level, and 26 advanced-level. These will help you get your dream job as a PL/SQL Developer. With PL SQL interview questions, you can be confident on preparing well for your upcoming interview.
Filter By
Clear all
This is a frequently asked question in PL SQL interview questions.
PL/SQL is an extension for SQL and Oracle. It enhances the capabilities of SQL. Therefore, with every release of the Oracle Database, the feature and functionalities of PL/SQL are enhanced.
It is available in Oracle Database since version 6. However, PL/SQL is also available in TimesTen in-memory database since version 11.2.1 and IBM DB2 since version 9.7.
PL/SQL is strongly integrated with SQL and supports both static and dynamic SQL.
The following are the features in PL/SQL:
PL/SQL is an extension for SQL and Oracle. It enhances the capabilities of SQL. Therefore, with every release of the Oracle Database, the feature and functionalities of PL/SQL is enhanced.
It is available in Oracle Database since version 6. However, PL/SQL is also available in TimesTen in-memory database since version 11.2.1 and IBM DB2 since version 9.7.
TL/SQL is Transact Structure Query language. T-SQL expands on the SQL standard to add procedural programming, local variables, etc. It is Microsoft's and Sybase's proprietary extension to the SQL.
The following are the differences:
Basis | PL/SQL | T/SQL |
---|---|---|
Efficiency | It is more powerful than T/SQL. | T/SQL is less powerful than PL/SQL. |
Relation with SQL | PL/SQL is an extension for SQL and Oracle. It enhances the capabilities of SQL. | T-SQL expands on the SQL standard to add procedural programming, local variables, etc. |
Full Form | PL SQL is Procedural Language Structural Query Language. | TL SQL is Transact Structure Query language. |
Compatibility | PL-SQL works best with Oracle database Server. | T-SQL works best with Microsoft SQL Server |
Easier/ Complex | PL/SQL is considered complex to learn than T/SQL | It is easier to learn and work with T/SQL. |
Developer | It is developed and owned by Oracle. | T/SQL is Microsoft's and Sybase's proprietary extension to the SQL. |
PL/SQL is an extension for SQL and Oracle. It enhances the capabilities of SQL. Therefore, with every release of the Oracle Database, the feature and functionalities of PL/SQL is enhanced. It is available in Oracle Database since version 6. However, PL/SQL is also available in TimesTen in-memory database since version 11.2.1 and IBM DB2 since version 9.7.
SQL is a domain-specific language used in programming. It is a Structured Query Language for database creation, deletion, fetching rows, modifying rows, etc. relational database management system
The following are the differences:
Basis | PL/SQL | SQL |
---|---|---|
Embed | Can have SQL embedded in it. | Cannot have PL/SQL code embedded in it. |
Interaction with database server | There is no interaction with the database server. | Interaction with the database server. |
How/What | PL/SQL defines how it needs to be done. | Defines what needs to be done. |
Writing program/functions/procedures | PL/SQL is used to write program blocks, functions, procedures triggers, etc. | SQL is used to write queries, DDL and DML statements. |
Execution | Executes as a whole block. | Issue a single query or execute a single insert/update/delete. Executes one statement at a time. |
PL/SQL is an extension for SQL and Oracle. It enhances the capabilities of SQL. Therefore, with every release of the Oracle Database, the feature and functionalities of PL/SQL is enhanced. It is strongly integrated with SQL and supports both static and dynamic SQL.
The following are the characteristics of PL/SQL:
Expect to come across this popular question in PL SQL questions.
Oracle forms a memory area when an SQL statement is processed. This memory area is called the context area. A cursor in PL/SQL is a pointer to this context area. It has information about processing the statement. A PL/SQL cursor holds the rows (one or more) returned by a SQL statement.
The following are the two forms of cursors:
Implicit cursors in PL/SQL are created automatically by Oracle when an SQL statement is executed. This occurs when there is no explicit cursor for the statement.
An implicit cursor is associated when INSERT, UPDATE, and DELETE is issued. The attributes include %FOUND, %ISOPEN, %NOTFOUND, etc. With the attributes, you can check which rows got affected by the operation.
The attributes:
To use any of the above attributes, use the following syntax:
sql%attribute_name
Above, set any attribute name under “attribute_name”.Let us consider the following table for our example:
<DEPTBUDGET> +-----------+-----------------+---------------+------------------+ | DEPTID | DEPTNAME | DEPTLOC | BUDGET| +----------+------------------+---------------+------------------+ | 1 | Finance | NORTH | 56678 | | 2 | Marketing | EAST | 87687 | | 3 | Operations | WEST | 95979 | | 4 | Technical | SOUTH | 76878 | | 5 | Accounting | NORTHWEST| 86767 | +----------+------------------+------------------+--------------+
Now, we will update the budget of all the departments and then check what all rows got affected using the implicit cursor attribute. With that, we will use other attributes as well:
DECLARE rows_count number(2); BEGIN UPDATE deptbudget SET budget = budget + 3000; IF sql%notfound THEN dbms_output.put_line('Nothing selected!'); ELSIF sql%found THEN rows_count := sql%rowcount; dbms_output.put_line( rows_count || ' rows affected! '); END IF; END; /
The output:
5 rows affected!
Since we updated the budget for all the departments above, our table rows would look like this:
<DEPTBUDGET> +-----------+-----------------+---------------+------------------+ | DEPTID | DEPTNAME | DEPTLOC | BUDGET| +----------+------------------+---------------+------------------+ | 1 | Finance | NORTH | 59678 | | 2 | Marketing | EAST | 90687 | | 3 | Operations | WEST | 98979 | | 4 | Technical | SOUTH | 79878 | | 5 | Accounting | NORTHWEST| 89767 | +----------+------------------+------------------+--------------+
Explicit cursors are custom defined i.e. programmer-defined cursors that give more control over the context area. Oracle forms a memory area when an SQL statement is processed, which is called a context area.
The following is the syntax to create an explicit cursor:
CURSOR name_of_cursor IS select_statement;
Here,
“name_of_cursor” is the cursor name
As you can see above, an explicit cursor is created on a SELECT statement. Let us now see how to work with Explicit Cursors,
Let us declare a cursor and set a cursor name with the SELECT statement:
CURSOR d_deptbudget IS SELECT deptid, deptname, deptloc, budget FROM deptbudget;
Above, “d_deptbudget” is our cursor.
The memory gets allocated for the cursor when it is opened in PL/SQL. Opening the cursor allocates the memory for the cursor and makes it ready for fetching the rows returned by the SQL statement into it.
OPEN d_deptbudget;
Access one row at a time when a cursor is fetched in PL/SQL. The following is when you fetch a cursor that is already opened:
FETCH d_deptbudget INTO d_deptid, d_deptname, d_deptloc, d_budget;
When a cursor is closed, then the allocated memory is released. To close the above-created cursor:
CLOSE d_deptbudget;
Let us now see an example of working with explicit cursors in PL/SQL:
DECLARE d_deptid deptbudget.deptid%type; d_deptname deptbudget.deptname%type; CURSOR d_deptbudget IS SELECT deptid, deptname FROM deptbudget; BEGIN OPEN d_deptbudget; LOOP FETCH d_deptbudget into d_deptid, d_deptname; EXIT WHEN d_deptbudget%notfound; dbms_output.put_line(d_deptid || ' ' || d_deptname); END LOOP; CLOSE d_deptbudget; END; /
The output display department id and name:
1 Finance 2 Marketing 3 Operations 4 Technical 5 Accounting
The EXIT WHEN statement has a condition under the WHEN clause. The statement acts like NULL before the condition is TRUE.
The following is the syntax:
EXIT WHEN condition;
Here is an example:
DECLARE val number(2) := 2; BEGIN WHILE val < 10 LOOP dbms_output.put_line ('Value = ' || val); val := val + 1; EXIT WHEN val = 5; END LOOP; END; /
The output:
Value = 2 Value = 3 Value = 4
A code with a comment helps the developers in reading the source code properly. Like other programming languages, in PL/SQL, you can easily add comment to the code. Compiler ignores the content under the comment.
The single-line comments under PL/SQL begin with the delimiter:
-- (double hyphen)
The multi-line comments are enclosed by:
/* and */
Let us see both the comments in the following code:
DECLARE -- Variable declaration and initialization car varchar2(15) := 'Bentley'; BEGIN /* * UPPER() method in PL/SQL */ dbms_output.put_line(UPPER(car)); END; /
Above, we have used both the single line and multi-line comment. The output:
BENTLEY
Triggers are stored in a database and fired when some event occurs. The event here can be a Database Manipulation statement i.e. DELETE, INSERT, or UPDATE, or a Database Definition statement (CREATE, ALTER, or DROP). It can even be a database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).
Triggers are used to enforce referential integrity, auditing, or even applying security authorizations. It prevents invalid transactions and gather statistics on table access.
Syntax
The CREATE TRIGGER statement is used in PL/SQL to create a trigger. The syntax:
CREATE [OR REPLACE ] TRIGGER name_of_trigger {BEFORE | AFTER | INSTEAD OF } {INSERT [OR] | UPDATE [OR] | DELETE} [OF col_name] ON table_name [REFERENCING OLD AS o NEW AS n] [FOR EACH ROW] WHEN (condition) DECLARE Declaration-statements BEGIN Executable-statements EXCEPTION Exception-handling-statements END;
Here,
Let us now take an example wherein we will create a trigger, trigger it, and even delete it.
For that, we are considering the following table <DEPTBUDGET>:
<DEPTBUDGET> +-----------+-----------------+---------------+------------------+ | DEPTID | DEPTNAME | DEPTLOC | BUDGET| +----------+------------------+---------------+------------------+ | 1 | Finance | NORTH | 56678 | | 2 | Marketing | EAST | 87687 | | 3 | Operations | WEST | 98979 | | 4 | Technical | SOUTH | 76878 | | 5 | Accounting | NORTHWEST| 86767 | +----------+------------------+------------------+--------------+
Create Trigger
Now we will create a row-level trigger that gets fired for INSERT or UPDATE or DELETE operations performed on the table. This is created to display the budget differences between new and old assigned budgets:
CREATE OR REPLACE TRIGGER budget_diff BEFORE DELETE OR INSERT OR UPDATE ON deptbudget FOR EACH ROW WHEN (NEW.ID > 0) DECLARE budget_diff number; BEGIN budget_diff := :NEW.budget - :OLD.budget; dbms_output.put_line('Old Budget Assigned =' || :OLD.budget); dbms_output.put_line('New Budget Assigned = ' || :NEW.budget); dbms_output.put_line('Differences in Budget = ' || budget); END; /
The above trigger it will fire before any DELETE or INSERT or UPDATE operation on the table, since we added the following above:
BEFORE DELETE OR INSERT OR UPDATE ON deptbudget
We will now perform a DML operation on the DEPTBUDGET table i.e. the UPDATE statement.
The statement would update an existing record in the table:
UPDATE budget SET = budget + 4500 WHERE id = 4;
We updated the record in the DEPTBUDGET table. When we will fire the “budget_diff”, it displays the following:
Old Budget Assigned = 76878 New Budget Assigned = 81378 Differences in Budget = 4500
PL/SQL is an extension for SQL and Oracle. It enhances the capabilities of SQL. Therefore, with every release of the Oracle Database, the feature and functionalities of PL/SQL is enhanced. It is strongly integrated with SQL and supports both static and dynamic SQL.
The following are the advantages of PL/SQL:
PL/SQL has both static and dynamic SQL. Static SQL with DML operations and transaction control from PL/SQL block. SQL allows embedding DDL statements in PL/SQL blocks in Dynamic SQL.
PL/SQL has exception handling that helps in easier debugging.
PL/SQL allows faster update of database. Therefore, beneficial for programmers.
With PL/SQL, we can easily perform faster execution of queries.
PL/SQL executes as a whole block i.e. sends entire block of statements to the database at once.
Since PL/SQL is an extension of SQL, you get access to predefined SQL packages.
PL/SQL also comes with the functionality of Triggers. Triggers are stored in a database and fired when some event occurs. The event here can be a Database Manipulation statement i.e. DELETE, INSERT, or UPDATE, or a Database Definition statement (CREATE, ALTER, or DROP).
PL/SQL and SQL brings optimal efficiency since it runs within the same server process.
Error Management in PL/SQL brings in internally defined, predefined, and user-define exceptions and process to handle them with error codes and messages. With that, PL/SQL also has RAISE statement for your own defined exceptions.
A must-know for anyone heading into a PL SQL interview, this question is frequently asked in PL SQL developer interview questions.
The types of Datatypes are categorized as the following:
1. Scalar Data Type
The Data Types have single values with no internal components. Here are the predefined Scalar Data Types with the description of its data:
2. Composite Data Type
Data items that have internal components that can be accessed individually
3. Reference Data Type
Pointers to other data items
4. Large Object Data Types (LOB)
These Data Types are pointers to large objects that are stored separately from other data items, like text, graphic images, video clips, etc. The following are the data types:
If a PL/SQL program violate the rule of Oracle, then an exception is raised. The exceptions are handled by name, for example, DUP_VAL_ON_INDEX exceptions is raised when an attempt is made to store duplicate values in a database column constrained by a unique index.
Let us learn about the predefined exceptions in PL/SQL:
This exception is raised when an attempt is made to the attributes of an uninitialized object.
This exception is raised when none of the choices in the WHEN clause of a CASE statement is selected and there is no ELSE clause.
This exception is raised when an attempt is made to apply collection methods other than EXISTS to an uninitialized nested table or varray.
This exception is raised when duplicate values are attempted to be stored in a column with unique index.
This exception is raised when attempts are made to make a cursor operation that is not allowed, such as closing an unopened cursor.
This exception is raised when the conversion of a character string into a number fails because the string does not represent a valid number.
This exception is raised when a program attempts to log on to the database with an invalid username or password.
This exception is raised when a SELECT INTO statement returns no rows.
This exception is raised when a database call is issued without being connected to the database.
This exception is raised when there is an internal problem.
This exception is raised when a cursor fetches value in a variable having incompatible data type.
This exception is raised when a member method is invoked, but the instance of the object type was not initialized.
This exception is raised when PL/SQL ran out of memory.
This exception is raised when SELECT INTO statement returns more than one row.
This exception is raised when arithmetic, conversion, or truncation error occurs.
This exception is raised when an attempt is made to divide a number by zero.
Literals are syntactic representation of numeric, character, string, or Boolean value. The literals in PL/SQL are case-sensitive.
The following are the literals in PL/SQL:
'Mystring!' 'Web!'
Examples: TRUE, FALSE
DATE '2018-12-20'; TIMESTAMP '2018-12-15 11:10:30';
In PL/SQL, it is possible to create your own records i.e. user-defined. With that, you can set the different record structures.
Let us take an example of a Laptop’s record. Here is how you can declare:
DECLARE TYPE laptop IS RECORD (brand varchar(50), RAM number, SNO number, ); l1 laptop; l1 laptop;
You can now access the fields using the dot(.) operator.
The following is the example wherein we have created user-define records and accesses the fields:
DECLARE TYPE laptop IS RECORD (brand varchar(50), RAM number, SNO number ); lp1 laptop; lp2 laptop; lp3 laptop; BEGIN -- Laptop 1 specification lp1.brand:= 'Dell'; lp1.RAM:= 4; lp1.SNO:= 87667; -- Laptop 2 specification lp2.brand:= 'Lenevo'; lp2.RAM:= 4; lp2.SNO:= 47656; -- Laptop 3 specification lp3.brand:= 'HP'; lp3.RAM:= 8; lp3.SNO:= 98989; -- Laptop 1 record dbms_output.put_line('Laptop 1 Brand = '|| lp1.brand); dbms_output.put_line('Laptop 1 RAM = '|| lp1.RAM); dbms_output.put_line('Laptop 1 SNO = ' || lp1.SNO); -- Laptop 2 record dbms_output.put_line('Laptop 2 Brand = '|| lp2.brand); dbms_output.put_line('Laptop 2 RAM = '|| lp2.RAM); dbms_output.put_line('Laptop 2 SNO = ' || lp2.SNO); -- Laptop 3 record dbms_output.put_line('Laptop 3 Brand = '|| lp3.brand); dbms_output.put_line('Laptop 3 RAM = '|| lp3.RAM); dbms_output.put_line('Laptop 2 SNO = ' || lp3.SNO); END;
The output:
Laptop 1 Brand = Dell Laptop 1 RAM = 4 Laptop 1 SNO = 87667 Laptop 2 Brand = Lenevo Laptop 2 RAM = 4 Laptop 2 SNO = 47656 Laptop 3 Brand = HP Laptop 3 RAM = 8 Laptop 2 SNO = 98989
It's no surprise that this one pops up often in PLSQL interview questions.
There are two types of exceptions that exist in PL/SQL:
System Defined Exceptions
The predefined exceptions in PL/SQL are System Defined Exceptions. If a PL/SQL program violate the rule of Oracle, then an exception is raised. The exceptions are handled by name, for example, DUP_VAL_ON_INDEX exceptions are raised when an attempt is made to store duplicate values in a database column constrained by a unique index.
Let us learn about the predefined exceptions in PL/SQL:
User-Defined Exceptions
Like other programming languages, PL/SQL also allows users to create their own exceptions. Use the RAISE statement or the procedure DBMS_STANDARD.RAISE_APPLICATION_ERROR to declare and raise the user-defined exception explicitly.
To create your own exception, first consider the following table <DEPTBUDGET>:
<DEPTBUDGET> +-----------+-----------------+---------------+------------------+ | DEPTID | DEPTNAME | DEPTLOC | BUDGET| +----------+------------------+---------------+------------------+ | 1 | Finance | NORTH | 56678 | | 2 | Marketing | EAST | 87687 | | 3 | Operations | WEST | 98979 | | 4 | Technical | SOUTH | 76878 | | 5 | Accounting | NORTHWEST| 86767 | +----------+------------------+------------------+--------------+
Let’s say we created the following procedure and a user-defined exception in it:
DECLARE id deptbudget.id%type := &d_id; deptname deptbudget.deptname%type; deptloc deptbudget.deptloc%type; my_exception EXCEPTION; BEGIN IF d_id <= 0 THEN RAISE my_exception; ELSE SELECT deptname, deptloc INTO deptname, deptloc FROM deptbudget WHERE id = d_id; DBMS_OUTPUT.PUT_LINE ('Name: '|| deptname); DBMS_OUTPUT.PUT_LINE ('Address: ' || deptloc); END IF; EXCEPTION WHEN my_exception THEN dbms_output.put_line('ID is always greater than zero!'); WHEN no_data_found THEN dbms_output.put_line('No such department in the company!'); WHEN others THEN dbms_output.put_line('Error!'); END; /
On executing the above procedure, the following output is visible. Let’s say our input is department id as “-2”, yes, a negative number. When executed, it raises the following user-defined error:
ID is always greater than zero!
Implicit cursors in PL/SQL are created automatically by Oracle when an SQL statement is executed. This occurs when there is no explicit cursor for the statement.
An implicit cursor is associated, when INSERT, UPDATE and DELETE is issued. The attributes include %FOUND, %ISOPEN, %NOTFOUND, etc. With the attributes, you can check which all rows got affected by the operation.
The attributes:
To use any of the above attribute, use the following syntax:
sql%attribute_name
Above, set any attribute name under “attribute_name”.
Let us consider the following table for our example:
<DEPTBUDGET> +-----------+-----------------+---------------+------------------+ | DEPTID | DEPTNAME | DEPTLOC | BUDGET| +----------+------------------+---------------+------------------+ | 1 | Finance | NORTH | 56678 | | 2 | Marketing | EAST | 87687 | | 3 | Operations | WEST | 95979 | | 4 | Technical | SOUTH | 76878 | | 5 | Accounting | NORTHWEST| 86767 | +----------+------------------+------------------+--------------+
Now, we will update the budget of all the departments and then check what all rows got affected using the implicit cursor attribute. With that we will use other attributes as well:
DECLARE rows_count number(2); BEGIN UPDATE deptbudget SET budget = budget + 3000; IF sql%notfound THEN dbms_output.put_line('Nothing selected!'); ELSIF sql%found THEN rows_count := sql%rowcount; dbms_output.put_line( rows_count || ' rows affected! '); END IF; END; /
The output:
5 rows affected!
Since we updated the budget for all the departments above, therefore our table rows would look like this:
<DEPTBUDGET> +-----------+-----------------+---------------+------------------+ | DEPTID | DEPTNAME | DEPTLOC | BUDGET| +----------+------------------+---------------+------------------+ | 1 | Finance | NORTH | 59678 | | 2 | Marketing | EAST | 90687 | | 3 | Operations | WEST | 98979 | | 4 | Technical | SOUTH | 79878 | | 5 | Accounting | NORTHWEST| 89767 | +----------+------------------+------------------+--------------+
A transaction in PL/SQL is an atomic unit that may consist of one or more related SQL statements.
To commit a transaction in PL/SQL, use the COMMIT command:
COMMIT;
The COMMIT statement saves all the changes since the last COMMIT or ROLLBACK. An explicit or implicit request is made to commit a transaction.
Let’s say the following is the INSERT in the table. At the end, execute the COMMIT command if you want to commit the transaction:
INSERT INTO DEPTBUDGET (DEPTID,DEPTNAME,DEPTLOC) VALUES (1, 'Finance', 'NORTH'); INSERT INTO DEPTBUDGET (DEPTID,DEPTNAME,DEPTLOC) VALUES (2, 'Marketing', 'EAST'); INSERT INTO DEPTBUDGET (DEPTID,DEPTNAME,DEPTLOC) VALUES (3, 'Operations', 'WEST'); COMMIT;
In PL/SQL, use the %ROWTYPE attribute with an explicit cursor or cursor variable where each field corresponds to a column in the cursor SELECT statement.
The following is an example:
DECLARE CURSOR cur_dept is SELECT deptid, deptname, deptloc FROM deptbudget; rec_dept cur_dept %rowtype; BEGIN OPEN cur_dept ; LOOP FETCH cur_dept into rec_dept; EXIT WHEN cur_dept %notfound; DBMS_OUTPUT.put_line(rec_dept.deptid || ' ' || rec_dept.deptname || ' ' || rec_dept.deptloc); END LOOP; END; /
The output:
1 Finance NORTH 2 Marketing EAST 3 Operations WEST 4 Technical SOUTH 5 Accounting NORTHWEST PL/SQL procedure successfully completed.
The predefined numeric datatypes are subtypes of the Scalar Data Types.
The following are the subtypes of the numeric datatypes:
Signed integer in range -2,147,483,648 through 2,147,483,647, represented in 32 bits
Signed integer in range -2,147,483,648 through 2,147,483,647, represented in 32 bits
Single-precision IEEE 754-format floating-point number
Double-precision IEEE 754-format floating-point number
ANSI specific fixed-point type with maximum precision of 38 decimal digits
IBM specific fixed-point type with maximum precision of 38 decimal digits
Floating type with maximum precision of 38 decimal digits
ANSI specific floating-point type with maximum precision of 126 binary digits (approximately 38 decimal digits)
ANSI and IBM specific integer type with maximum precision of 38 decimal digits
Floating-point type with maximum precision of 63 binary digits (approximately 18 decimal digits)
A common question in PL/SQL interview questions, don't miss this one.
PL/SQL allocates memory for the variable's value when a variable is declared. The storage location is identified by the variable name.
Declaration
The following is the syntax:
variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value]
Here,
Example for a string variable:
name varchar2(15);
Let us see an example of a number precision limit. This is called constraint declaration that needs less memory:
a number(5, 2)
Initialization
To initialize a variable, you can use the DEFAULT keyword as well as the assignment operator. Let us see them one by one.
Initialization with DEFAULT keyword:
DECLARE msg varchar2(15) DEFAULT 'House'; BEGIN dbms_output.put_line(msg); END; /
The output:
House
Initialization with assignment operator:
DECLARE device varchar2(11) := 'Laptop'; BEGIN dbms_output.put_line(device); END; /
The output:
Laptop
The Datetime Interval data type stores and manipulate dates, times, and intervals.
A variable with date and time data type stores values called datetimes. A variable with interval data type stores values called intervals.
Here are the field names:
YEAR
MONTH
DAY
HOUR
MINUTE
SECOND
TIMEZONE_HOUR
TIMEZONE_MINUTE
TIMEZONE_REGION
TIMEZONE_ABBR
The data type TIMESTAMP stores the year, month, day, hour, minute, and second. TIMESTAMP extends the data type DATE.
The following is the syntax:
TIMESTAMP[(precision)
Here,
Precision: The number of digits in the fractional part of the seconds field. Optional.
The following is an example:
DECLARE mydate TIMESTAMP(2); BEGIN mydate := '20-Dec-2018 07:48:53.275'; DBMS_OUTPUT.PUT_LINE( TO_CHAR(mydate )); END; /
The output:
20-DEC-18 07.48.53.28 AM
In PL/SQL, you can also find a datatype:
TIMESTAMP WITH TIME ZONE
The above datatype includes a time-zone displacement. The time-zone displacement is the difference between local time and Coordinated Universal Time (UTC,) formerly Greenwich Mean Time (GMT). This difference is in hours and minutes.
The following is the syntax:
TIMESTAMP[(precision)] WITH TIME ZONE
Here,
Precision: The number of digits in the fractional part of the seconds field. Optional.
Let us see an example:
DECLARE mydate TIMESTAMP(2) WITH TIME ZONE; BEGIN mydate := '10-Dec-2018 11:28:55.114 AM +03:00'; DBMS_OUTPUT.PUT_LINE( TO_CHAR(mydate )); END; /
The output:
10-DEC-18 11.28.55.11 AM +03:00
A delimiter is a compound symbol with a special meaning to PL/SQL. For example, you use delimiters to represent arithmetic operations such division. For example, a delimiter symbol is also the assignment operator used in PL/SQL:
:=
An example would be:
DECLARE device varchar2(15) := 'Laptop'; BEGIN dbms_output.put_line(device); END; /
The following are the symbols of Delimiters:
Symbol | Meaning |
---|---|
+ | addition operator |
% | attribute indicator |
' | character string delimiter |
. | component selector |
/ | division operator |
( | expression or list delimiter |
) | expression or list delimiter |
: | host variable indicator |
, | item separator |
* | multiplication operator |
" | quoted identifier delimiter |
= | relational operator |
< | relational operator |
> | relational operator |
@ | remote access indicator |
; | statement terminator |
- | subtraction/negation operator |
:= | assignment operator |
=> | association operator |
|| | concatenation operator |
** | exponentiation operator |
<< | label delimiter (begin) |
>> | label delimiter (end) |
/* | multi-line comment delimiter (begin) |
*/ | multi-line comment delimiter (end) |
.. | range operator |
<> | relational operator |
!= | relational operator |
~= | relational operator |
^= | relational operator |
<= | relational operator |
>= | relational operator |
-- | single-line comment indicator |
Explicit cursors are custom defined i.e. programmer defined cursors that gives more control over the context area. Oracle forms a memory area, when an SQL statement is processed, which is called context area.
The following is the syntax to create explicit cursor:
CURSOR name_of_cursor IS select_statement;
Here,
“name_of_cursor” is the cursor name
As you can see above, an explicit cursor is created on a SELECT statement. Let us now see how to work with Explicit Cursors,
Let us declare a cursor and set a cursor name with the SELECT statement:
CURSOR d_deptbudget IS SELECT deptid, deptname, deptloc, budget FROM deptbudget;
Above, “d_deptbudget” is our cursor.
The memory gets allocated for the cursor when it is opened in PL/SQL.
Opening the cursor allocates the memory for the cursor and makes it ready for fetching the rows returned by the SQL statement into it
OPEN d_deptbudget;
Access one row at a time when a cursor is fetched in PL/SQL.
The following is when you fetch a cursor which is already opened:
FETCH d_deptbudget INTO d_deptid, d_deptname, d_deptloc, d_budget;
When a cursor is closed, then the allocate memory is released. To close the above created cursor:
CLOSE d_deptbudget;
Let us now see an example to work with explicit cursors in PL/SQL:
DECLARE d_deptid deptbudget.deptid%type; d_deptname deptbudget.deptname%type; CURSOR d_deptbudget IS SELECT deptid, deptname FROM deptbudget; BEGIN OPEN d_deptbudget; LOOP FETCH d_deptbudget into d_deptid, d_deptname; EXIT WHEN d_deptbudget%notfound; dbms_output.put_line(d_deptid || ' ' || d_deptname); END LOOP; CLOSE d_deptbudget; END; /
The output display department id and name:
1 Finance 2 Marketing 3 Operations 4 Technical 5 Accounting
The modes of parameters in a PL/SQL subprogram includes the following:
The IN parameter is a read-only parameter that allows you to pass a value to the subprogram.
It acts as a constant in the subprogram. A constant, literal, initialized variable, or expression can be passed as an IN parameter.
The OUT parameter acts like a variable in a subprogram. Its value can be changed unlike the IN parameter.The value can be reference after assignment.
The actual parameter is passed by value. The initial value is passed to a subprogram. The updated value is returned to the caller.
Let us see an example:
DECLARE val number; PROCEDURE display(n IN OUT number) IS BEGIN n := n * n; END; BEGIN val:= 42; display(val); dbms_output.put_line('Square of 42 = ' || val); END; /
The output:
Square of 42 = 1764
To join two or more strings i.e. concatenation, PL/SQL comes with concatenation operator (||). This operator returns a string value.
The following is the syntax:
str1 || str2 [ || str_n ]
Here,
The following is an example:
DECLARE one varchar2(20) := 'Website'; two varchar2(20) := ' World!'; BEGIN dbms_output.put_line(one || two); END; /
The output:
Website World!
A record is a data structure in PL/SQL to hold data items of different kinds.
For our examples on records in PL/SQL, we have the following sample table:
<DEPTBUDGET> +-----------+-----------------+---------------+------------------+ | DEPTID | DEPTNAME | DEPTLOC | BUDGET| +----------+------------------+---------------+------------------+ | 1 | Finance | NORTH | 56678 | | 2 | Marketing | EAST | 87687 | | 3 | Operations | WEST | 98979 | | 4 | Technical | SOUTH | 76878 | | 5 | Accounting | NORTHWEST| 86767 | +----------+------------------+------------------+--------------+
PL/SQL handles the following types of records:
To create table-based records, you need to use the %ROWTYPE attribute. Using the same we will fetch the records of a department with id = 3:
DECLARE rec_dept deptbudget%rowtype; BEGIN SELECT * into rec_dept FROM customerdeptbudgets WHERE deptid = 3; dbms_output.put_line('Department ID =' || rec_dept.deptd); dbms_output.put_line('Department Name = ' || rec_dept.deptname); dbms_output.put_line('Department Location = ' || rec_dept.deptloc); dbms_output.put_line('Department Budget = ' || rec_dept.budget); END; /
The output:
Department ID = 3 Department Name = Operations Department Location = WEST Department Budget = 98979 PL/SQL procedure successfully completed.
In PL/SQL, use the %ROWTYPE attribute with an explicit cursor or cursor variable where each field corresponds to a column in the cursor SELECT statement.
The following is an example:
DECLARE CURSOR cur_dept is SELECT deptid, deptname, deptloc FROM deptbudget; rec_dept cur_dept %rowtype; BEGIN OPEN cur_dept ; LOOP FETCH cur_dept into rec_dept; EXIT WHEN cur_dept %notfound; DBMS_OUTPUT.put_line(rec_dept.deptid || ' ' || rec_dept.deptname || ' ' || rec_dept.deptloc); END LOOP; END; /
The output:
1 Finance NORTH 2 Marketing EAST 3 Operations WEST 4 Technical SOUTH 5 Accounting NORTHWEST PL/SQL procedure successfully completed.
In PL/SQL, it is possible to create your own records i.e. user-defined. With that, you can set the different record structures.
Let us take an example of a Laptop’s record. Here is how you can declare:
DECLARE TYPE laptop IS RECORD (brand varchar(50), RAM number, SNO number, ); l1 laptop; l1 laptop;
You can now access the fields using the dot(.) operator.
The following is the example wherein we have created user-define records and accesses the fields:
DECLARE TYPE laptop IS RECORD (brand varchar(50), RAM number, SNO number ); lp1 laptop; lp2 laptop; lp3 laptop; BEGIN -- Laptop 1 specification lp1.brand:= 'Dell'; lp1.RAM:= 4; lp1.SNO:= 87667; -- Laptop 2 specification lp2.brand:= 'Lenevo'; lp2.RAM:= 4; lp2.SNO:= 47656; -- Laptop 3 specification lp3.brand:= 'HP'; lp3.RAM:= 8; lp3.SNO:= 98989; -- Laptop 1 record dbms_output.put_line('Laptop 1 Brand = '|| lp1.brand); dbms_output.put_line('Laptop 1 RAM = '|| lp1.RAM); dbms_output.put_line('Laptop 1 SNO = ' || lp1.SNO); -- Laptop 2 record dbms_output.put_line('Laptop 2 Brand = '|| lp2.brand); dbms_output.put_line('Laptop 2 RAM = '|| lp2.RAM); dbms_output.put_line('Laptop 2 SNO = ' || lp2.SNO); -- Laptop 3 record dbms_output.put_line('Laptop 3 Brand = '|| lp3.brand); dbms_output.put_line('Laptop 3 RAM = '|| lp3.RAM); dbms_output.put_line('Laptop 2 SNO = ' || lp3.SNO); END;
The output:
Laptop 1 Brand = Dell Laptop 1 RAM = 4 Laptop 1 SNO = 87667 Laptop 2 Brand = Lenevo Laptop 2 RAM = 4 Laptop 2 SNO = 47656 Laptop 3 Brand = HP Laptop 3 RAM = 8 Laptop 2 SNO = 98989
The valid values for seconds:
The INSTRB is a function used in PL/SQL to return the location of a string within another string. The value is returned in bytes.
An example:
INSTR('Jack Sparrow', 'c')
The above returns the output in bytes:
3
Like any other language, PL/SQL also provides a functionality to handle exceptions. This is done using the EXCEPTION block. To raise exceptions explicitly, use the RAISE command.
To handle exceptions in PL/SQL, here is the syntax:
DECLARE <declarations section> BEGIN <executable command(s)> EXCEPTION <exception handling > WHEN exception1 THEN exception1-handling-statements WHEN exception2 THEN exception2-handling-statements WHEN exception3 THEN exception3-handling-statements ........ WHEN others THEN exception-handling-statements END;
Above, WHEN is used for exceptions like:
WHEN no_data_found THEN
With that, the following is to be used for default exception:
WHEN others THEN
Before beginning our example, let’s say we have the following table:
<DEPTBUDGET> +-----------+-----------------+---------------+------------------+ | DEPTID | DEPTNAME | DEPTLOC | BUDGET| +----------+------------------+---------------+------------------+ | 1 | Finance | NORTH | 56678 | | 2 | Marketing | EAST | 87687 | | 3 | Operations | WEST | 98979 | | 4 | Technical | SOUTH | 76878 | | 5 | Accounting | NORTHWEST| 86767 | +----------+------------------+------------------+--------------+
The following is our example wherein we are handling exceptions. The dept
DECLARE d_id deptbudget.deptid%type := 20; d_ name deptbudget.deptname%type; d_loc deptbudget.deptloc%type; BEGIN SELECT deptname, deptloc INTO d_name, d_loc FROM deptbudget WHERE deptid = d_id; DBMS_OUTPUT.PUT_LINE ('Name: '|| d_name); DBMS_OUTPUT.PUT_LINE ('Address: ' || d_loc); END IF; EXCEPTION WHEN no_data_found THEN dbms_output.put_line('No such department in the company!'); WHEN others THEN dbms_output.put_line('Error!'); END; /
The output:
No such department in the company
Variable Scope defines that if you declare a variable within an inner bloc, it is not accessible for the outer. On the contrary, if a variable is declared in outer block, then it is accessible in the entire program whether its outer, inner, nested inner blocks.
The following are the scope of variables in PL/SQL:
Local variables
Global variables
Let us now see an example to learn the role of local and global variables in scope of variables in PL/SQL:
DECLARE -- Global variables a number := 5; BEGIN dbms_output.put_line('Outer Variable a = ' || a); DECLARE -- Local variables a number := 10; BEGIN dbms_output.put_line('Inner Variable a = ' || a); END; END; /
The output:
Outer Variable a = 5 Inner Variable a = 10
Nested tables are one of the collection types in PL/SQL. They are created either in PL/SQL block or at schema level. These are like a 1D array, but its size can get increased dynamically.
The following is the syntax:
TYPE type_name IS TABLE OF element_type [NOT NULL]; name_of_table type_name;
The following is an example:
DECLARE TYPE deptname IS TABLE OF VARCHAR2(10); TYPE budget IS TABLE OF INTEGER; names deptname; deptbudget budget; BEGIN names := deptname ('Finance', 'Sales', 'Marketing'); deptbudget := budget (89899, 67879, 98999); FOR i IN 1 .. names.count LOOP dbms_output.put_line('Department = '||names(i)||', Budget = ' || deptbudget(i)); end loop; END; /
The output:
Department = Finance, Budget = 89899 Department = Sales, Budget = 67879 Department = Marketing, Budget = 98999
No, this is not a correct way to implement assignment in PL/SQL. In PL/SQL, the assignment statement is the following:
:=
Let us see in the example:
sum := sum +10;
The following is another example:
DECLARE cost number := 3000; BEGIN dbms_output.put_line(cost); END; /
The output:
3000
This is a frequently asked question in PLSQL interview questions.
The Rollback undone all the changes since the last COMMIT or ROLLBACK.
To commit a transaction in PL/SQL, use the COMMIT command:
COMMIT;
Let’s say the following is the INSERT in the table. At the end, execute the COMMIT command if you want to commit the transaction:
INSERT INTO DEPTBUDGET (DEPTID,DEPTNAME,DEPTLOC) VALUES (1, 'Finance', 'NORTH'); INSERT INTO DEPTBUDGET (DEPTID,DEPTNAME,DEPTLOC) VALUES (2, 'Marketing', 'EAST'); INSERT INTO DEPTBUDGET (DEPTID,DEPTNAME,DEPTLOC) VALUES (3, 'Operations', 'WEST'); COMMIT;
To rollback the transactions, use the ROOLBACK command. The changes you made to the database without COMMIT can be undone with ROLLBACK.
The syntax:
ROLLBACK [TO SAVEPOINT < name_of_savepoint>];
Here, name_of_savepoint is the name of the savepoint.
If savepoint is not used, then simply use the “ROLLBACK” statement to rollback all the changes.
ROLLBACK;
The SAVEPOINT statement forms a savepoint. This allows in performing partial ROLLBACKs.
Savepoints splits a long transaction into smaller units. This is simply done using checkpoints. These checkpoints allow you to roll back to a checkpoint in a long transaction using the following command:
SAVEPOINT name_of_savepoint;
Let us see an example of SAVEPOINT:
INSERT INTO DEPTBUDGET (DEPTID,DEPTNAME,DEPTLOC, BUDGET) VALUES (1, 'Finance', 'NORTH', 20000); INSERT INTO DEPTBUDGET (DEPTID,DEPTNAME,DEPTLOC, BUDGET) VALUES (2, 'Marketing', 'EAST', 30000); SAVEPOINT s1; UPDATE DEPTBUDGET SET BUDGET = BUDGET - 4500; ROLLBACK TO s1; UPDATE DEPTBUDGET SET BUDGET = BUDGET - 4500; WHERE DEPTNAME = 'MARKETING'; COMMIT;
Above, we have created a SAVEPOINT s1, which will allow us to rollback.
The COMMIT statement is used to commit a transaction in PL/SQL, use the:
COMMIT;
The COMMIT statement saves all the changes since the last COMMIT or ROLLBACK. An explicit or implicit request is made to commit a transaction.
Let’s say the following is the INSERT in the table. At the end, execute the COMMIT command if you want to commit the transaction:
INSERT INTO DEPTBUDGET (DEPTID,DEPTNAME,DEPTLOC) VALUES (1, 'Finance', 'NORTH'); INSERT INTO DEPTBUDGET (DEPTID,DEPTNAME,DEPTLOC) VALUES (2, 'Marketing', 'EAST'); INSERT INTO DEPTBUDGET (DEPTID,DEPTNAME,DEPTLOC) VALUES (3, 'Operations', 'WEST'); COMMIT;
A staple in PL SQL interview questions, be prepared to answer this one.
The COLLECTION_IS_NULL exception is raised when a program attempts to apply collection methods other than EXISTS to an uninitialized nested table or varray, or the program attempts to assign values to the elements of an uninitialized nested table or varray.
The dot(.) operator is to be used in PL/SQL to access the fields of a record. This is between the record variable and the field.
Let us take an example of a record. This is how you can declare:
DECLARE TYPE laptop IS RECORD (brand varchar(50), RAM number, SNO number, ); l1 laptop; l1 laptop;
You can now access the fields using the dot(.) operator.
The following is the example wherein we have created user-define records and accesses the fields:
DECLARE TYPE laptop IS RECORD (brand varchar(50), RAM number, SNO number ); lp1 laptop; lp2 laptop; lp3 laptop; BEGIN -- Laptop 1 specification lp1.brand:= 'Dell'; lp1.RAM:= 4; lp1.SNO:= 87667; -- Laptop 2 specification lp2.brand:= 'Lenevo'; lp2.RAM:= 4; lp2.SNO:= 47656; -- Laptop 3 specification lp3.brand:= 'HP'; lp3.RAM:= 8; lp3.SNO:= 98989; -- Laptop 1 record dbms_output.put_line('Laptop 1 Brand = '|| lp1.brand); dbms_output.put_line('Laptop 1 RAM = '|| lp1.RAM); dbms_output.put_line('Laptop 1 SNO = ' || lp1.SNO); -- Laptop 2 record dbms_output.put_line('Laptop 2 Brand = '|| lp2.brand); dbms_output.put_line('Laptop 2 RAM = '|| lp2.RAM); dbms_output.put_line('Laptop 2 SNO = ' || lp2.SNO); -- Laptop 3 record dbms_output.put_line('Laptop 3 Brand = '|| lp3.brand); dbms_output.put_line('Laptop 3 RAM = '|| lp3.RAM); dbms_output.put_line('Laptop 2 SNO = ' || lp3.SNO); END;
The output:
Laptop 1 Brand = Dell Laptop 1 RAM = 4 Laptop 1 SNO = 87667 Laptop 2 Brand = Lenevo Laptop 2 RAM = 4 Laptop 2 SNO = 47656 Laptop 3 Brand = HP Laptop 3 RAM = 8 Laptop 2 SNO = 98989
This question is a regular feature in PL SQL interview questions, be ready to tackle it.
One of the Collection types in PL/SQL is Index-By table. These are also called Associative Arrays. It gets created only in the PL/SQL block. The subscript type for Associative Array is a string or integer.
Associative Array is a set of key-value pairs. The key here can be an Integer or String. The following is the syntax:
TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY subscript_type; name_of_table type_name;
Here, index-by table is the name_of_table. The keys of the subscript_type. The associated values will be of the element_type i.e. NUMBER, etc.
The following is an example:
DECLARE TYPE budget IS TABLE OF NUMBER INDEX BY VARCHAR2(15); dept_budget budget ; dept_name VARCHAR2(20); BEGIN dept_budget ('Operations') := 30000; dept_budget ('Finance') := 25000; dept_budget ('Accounts') := 45000; dept_name := dept_budget.FIRST; WHILE dept_name IS NOT null LOOP dbms_output.put_line ('Budget - ' || dept_name || ' Department = ' || TO_CHAR(dept_budget (dept_name ))); dept_name := dept_budget .NEXT(dept_name ); END LOOP; END; /
The output:
Budget - Accounts Department = 45000 Budget - Finance Department = 25000 Budget - Operations Department = 30000
This is a frequently asked question in PLSQL interview questions.
The TIMESTAMP WITH TIME ZONE datatype is used for date information across geographic regions.
It has a time zone region name or a time zone offset in its value. TIME ZONE offset = difference in hours and minutes between local time and UTC.
A must-know for anyone heading into a PL SQL interview, this question is frequently asked in PL SQL interview questions.
Yes, this is correct. Here, the WHEN clause is used for conditions, which are from top to bottom. The sequence of statements with the WHEN clause whose condition evaluates to TRUE is executed. Only the first statement executes if more than one condition evaluates to TRUE.
The following is the syntax:
CASE WHEN condition_1 THEN statements_1 WHEN condition_2 THEN statements_2 ... WHEN condition_n THEN statements_n [ ELSE else_statements ] END CASE;]
Let us see an example:
DECLARE points number := 100; BEGIN case when points = 20 then dbms_output.put_line('Rank 4'); when points = 50 then dbms_output.put_line('Rank 3'); when points = 75 then dbms_output.put_line('Rank 2'); when points = 100 then dbms_output.put_line('Rank1... Topper'); else dbms_output.put_line('No ranking!'); end case; END; /
The output:
Rank1... Topper
Yes, we can label PL/SQL loops. Here are some key points about labels in a loop:
Let us see an example:
DECLARE i number(1); j number(1); BEGIN << outer >> FOR i IN 1..5 LOOP << inner >> FOR j IN 1..2 LOOP dbms_output.put_line('i = '|| i || ', j = ' || j); END loop inner; END loop outer; END; /
The output:
i = 1, j = 1 i = 1, j = 2 i = 2, j = 1 i = 2, j = 2 i = 3, j = 1 i = 3, j = 2 i = 4, j = 1 i = 4, j = 2 i = 5, j = 1 i = 5, j = 2
The CASE statement in PL/SQL selects one sequence of statements to execute. The statement uses a selector instead multiple Boolean expression.
The following is the syntax:
CASE selector WHEN 'value1' THEN Statement1; WHEN 'value2' THEN Statement2; WHEN 'value3' THEN Statement3; ... ELSE Sn; -- default case END CASE;
The following is an example:
DECLARE points number := 100; BEGIN case points when 20 then dbms_output.put_line('Rank 4'); when 50 then dbms_output.put_line('Rank 3'); when 75 then dbms_output.put_line('Rank 2'); when 100 then dbms_output.put_line('Rank1... Topper'); else dbms_output.put_line('No ranking!'); end case; END; /
The output:
Rank1... Topper
The schema objects created with PL/SQL is Packages, Triggers, Cursors, etc.
Packages are the schema objects that groups logically related PL/SQL types and variables.
Triggers are used to enforce referential integrity, auditing, or even applying security authorizations. It prevents invalid transactions and gather statistics on table access.
Cursors has information about processing the statement. It holds the rows (one or more) returned by a SQL statement.
The %FOUND cursor attribute returns TRUE if record was fetched successfully, FALSE otherwise. Returns TRUE if an INSERT, UPDATE, or DELETE statement affected one or more rows or a SELECT INTO statement returned one or more rows. Otherwise, it returns FALSE.
The %NOTFOUND cursor attribute returns TRUE if record was not fetched successfully, FALSE otherwise. It returns TRUE if an INSERT, UPDATE, or DELETE statement affected no rows, or a SELECT INTO statement returned no rows. Otherwise, it returns FALSE.
Cursor attributes are used like this in PL/SQL:
sql%attribute_name
Above, set any attribute name under “attribute_name”. Here we are learning about %FOUND and %NOTFOUND attribute names.
Let us consider the following table for our example:
<DEPTBUDGET> +-----------+-----------------+---------------+------------------+ | DEPTID | DEPTNAME | DEPTLOC | BUDGET| +----------+------------------+---------------+------------------+ | 1 | Finance | NORTH | 56678 | | 2 | Marketing | EAST | 87687 | | 3 | Operations | WEST | 95979 | | 4 | Technical | SOUTH | 76878 | | 5 | Accounting | NORTHWEST| 86767 | +----------+------------------+------------------+--------------+
Now, we will update the budget of all the departments and then check what all rows got affected using the implicit cursor attribute. With that we will use other attributes as well:
DECLARE rows_count number(2); BEGIN UPDATE deptbudget SET budget = budget + 3000; IF sql%notfound THEN dbms_output.put_line('Nothing selected!'); ELSIF sql%found THEN rows_count := sql%rowcount; dbms_output.put_line( rows_count || ' rows affected! '); END IF; END; /
The output:
5 rows affected!
Since we updated the budget for all the departments above, therefore our table rows would look like this:
<DEPTBUDGET> +-----------+-----------------+---------------+------------------+ | DEPTID | DEPTNAME | DEPTLOC | BUDGET| +----------+------------------+---------------+------------------+ | 1 | Finance | NORTH | 59678 | | 2 | Marketing | EAST | 90687 | | 3 | Operations | WEST | 98979 | | 4 | Technical | SOUTH | 79878 | | 5 | Accounting | NORTHWEST| 89767 | +----------+------------------+------------------+--------------+
To display output, PL/SQL has an in-built package known as DBMS_OUTPUT. With this package, we can also display debugging information.
The following is an example:
DECLARE one varchar2(20) := 'Website'; two varchar2(20) := ' World!'; BEGIN dbms_output.put_line(one || two); END; /
The output:
Website World!
Let us see the subprograms of DBMS_OUTPUT:
Disables message output.
Enables message output. A NULL value of buffer_size represents unlimited buffer size.
Retrieves a single line of buffered information.
Retrieves an array of lines from the buffer.
Puts an end-of-line marker.
Places a partial line in the buffer.
Places a line in the buffer.
A common question in PL SQL interview questions, don't miss this one.
At each iteration in a basic loop in PL/SQL, a sequence of statements is executed. After the execution, the control resumes at the top of the loop. The EXIT statement is a must in the execution part to exit from the loop. The keyword LOOP begins the execution. It ends with the keyword END LOOP.
The following is an example:
DECLARE a number := 2; BEGIN LOOP dbms_output.put_line(a); a := a + 2; IF a > 10 THEN exit; END IF; END LOOP; dbms_output.put_line('After exit, the value of a = ' || a); END; /
The output:
2 4 6 8 10 After exit, the value of a = 12
A subprogram in PL/SQL is either a procedure or a function that can be invoked repeatedly. Each subprogram has a name and a list of parameters. It performs a specific task and the following two subprograms exist in PL/SQL:
The following are the parts of a subprogram:
It contains declarations of cursors, constants, variables, exceptions, etc.
Had statements that perform actions.
The code that handles run-time errors.
The following is the syntax to create a subprogram (procedure):
CREATE [OR REPLACE] PROCEDURE name_of_procedure [(parameter_name [IN | OUT | IN OUT] type [, ...])] {IS | AS} BEGIN < procedure_body > END name_of_procedure;
Here,
Here is an example of a subprogram (procedure):
CREATE OR REPLACE PROCEDURE one AS BEGIN dbms_output.put_line('Welcome!'); END; /
The output displays the procedure created successfully:
Procedure created.
The default value is NULL. PL/SQL assigns NULL as the default value of a variable. To assign another value, you can use the DEFAULT keyword or the assignment operator. Let us see them one by one.Initialization with DEFAULT keyword:
DECLARE msg varchar2(15) DEFAULT 'House'; BEGIN dbms_output.put_line(msg); END; /
The output:
House
Initialization with assignment operator:
DECLARE device varchar2(11) := 'Laptop'; BEGIN dbms_output.put_line(device); END; /
The output:
Laptop
PL/SQL has subtypes of datatypes. This means subtype is a subset of a datatype. The following are the types and subtypes of Numeric data type in PL/SQL
Signed integer in range -2,147,483,648 through 2,147,483,647, represented in 32 bits
Signed integer in range -2,147,483,648 through 2,147,483,647, represented in 32 bits
Single-precision IEEE 754-format floating-point number
Double-precision IEEE 754-format floating-point number
ANSI specific fixed-point type with maximum precision of 38 decimal digits
IBM specific fixed-point type with maximum precision of 38 decimal digits
Floating type with maximum precision of 38 decimal digits
ANSI specific floating-point type with maximum precision of 126 binary digits (approximately 38 decimal digits)
ANSI and IBM specific integer type with maximum precision of 38 decimal digits
Floating-point type with maximum precision of 63 binary digits (approximately 18 decimal digits)
The following are some of the Oracle Database PL/SQL packages:
You can use the database triggers to alert an application when a database values change.
You may have used PUL_LINE in this package to display output from PL/SQL blocks, subprograms, packages, and triggers. This also displays debugging information.
This package lets different sessions to communicate over named pipes
This allows the PL/SQL programs to generate HTML tags.
Using this package, you can send emails over SMTP (Simple Mail Transfer Protocol).
Recursion works the same way as we saw in programming languages like C, C++, Java, C#, etc.
A program or subprogram calling itself is called a recursive call and the process is recursion.
Let us see an example wherein we are displaying the usage of Recursive call:
DECLARE n number; factorial number; FUNCTION fact(a number) RETURN number IS f number; BEGIN IF a=0 THEN f := 1; ELSE f := a * fact(a-1); END IF; RETURN f; END; BEGIN n:= 3; factorial := fact(n); dbms_output.put_line('Value of Factorial '|| n || ' is ' || factorial); END; /
The output:
Value of Factorial 3 is 6
To return the ASCII value of a character, you need to use the ASCII() method. It returns a numeric value, which is the ASCII value.
The following is the syntax:
ASCII(myCharacter)
Here, myCharacter is the character you need to mention to get the ASCICC value.
The following is an example:
DECLARE a varchar(1) := 'B'; BEGIN dbms_output.put_line(ASCII(a)); END; /
The output:
66
NCHR is a function in PL/SQL that returns the character based on the specified number, which is an ASCII value. An example:
NCHR(77)
It displays the associated character:
M
The RPAD function is used to pad the right side of a string with characters. An example here pads the string:
RPAD(jack, 3)
The output is:
jac
A subprogram in PL/SQl is a standalone subprogram at the schema level. The CREATE PROCEDURE or the CREATE FUNCTION statement is used in PL/SQL to create a standalone procedure.
The following is the syntax to create a procedure:
CREATE [OR REPLACE] PROCEDURE name_of_procedure [(parameter_name [IN | OUT | IN OUT] type [, ...])] {AS} BEGIN < procedure_body > END name_of_procedure;
Here,
Here is an example of a procedure:
CREATE OR REPLACE PROCEDURE one AS BEGIN dbms_output.put_line('Chalk and Duster!!'); END; /
The output displays the procedure created successfully:
EXECUTE one;
The above call will give the following output:
Chalk and Duster!
The SIMPLE_INTEGER, SIMPLE_FLOAT, and SIMPLE_DOUBLE Data Types introduced in PL/SQL 11g Release 1. These are predefined subtypes of PLS_INTEGER, BINARY_FLOAT, and BINARY_DOUBLE, respectively.
The SIMPLE_FLOAT and SIMPLE_DOUBLE datatypes are alike their base types, except for their NOT NULL constraint.
The SIMPLE_FLOAT and SIMPLE_DOUBLE has better performance than BINARY_FLOAT and BINARY_DOUBLE.
SIMPLE_INTEGER is a predefined subtype of the PLS_INTEGER data type and differs from it in its overflow semantics. It has the same range as PLS_INTEGER i.e. -2,147,483,648 through 2,147,483,647) and has a NOT NULL constraint.
The DBMS_TRACE package has the interface to trace PL/SQL functions, procedures, and exceptions. The package provides subprograms to start and stop PL/SQL tracing in a session.
The following are the constants of the DBMS_TRACE package:
The access operator (.) is used in PL/SQL to access the attributes and methods of the object using the instance name and the (.). The example shows the usage:
No, stored procedure in PL/SQL does not return a value. Stored Procedure call using CALL keyword.
Here are some of the predefined exceptions in PL/SQL:
The EXIT statement is used to immediately terminate a loop. With this, the program control resumes at the next statement following the loop. It can also be used in a nested loop to stop the execution of the innermost loop.
When the EXIT statement is encountered inside a loop, the loop is immediately terminated and the program control resumes at the next statement following the loop.
The following is the syntax:
EXIT;
The example code:
DECLARE val number(2) := 2; BEGIN WHILE val < 10 LOOP dbms_output.put_line ('value = ' || val); val := val + 1; IF val = 5 THEN EXIT; END IF; END LOOP; END; /
The output:
value = 2 value = 3 value = 4
Once a value is declared in PL/SQL, you cannot change it. The CONSTANT keyword is used in PL/SQL to declare a constant.
The following is a query to find the area of circle, wherein we have set the constant pi as:
pi constant number := 3.141592654;
Above, we have declared a constant.
Let us see the complete code:
DECLARE pi constant number := 3.141592654; radius number(5,2); d number(5,2); area number (10, 2); BEGIN radius := 3; d:= radius * 2; area := pi * radius * radius; dbms_output.put_line('Radius = ' || radius); dbms_output.put_line('Diameter = ' || d); dbms_output.put_line('Area = ' || area); END; /
The output displays the area of circle:
Radius = 3 Diameter = 6 Area = 28.27 Statement processed. 0.01 seconds
Triggers are stored in a database and fired when some event occurs. The event here can be a Database Manipulation statement i.e. DELETE, INSERT, or UPDATE, or a Database Definition statement (CREATE, ALTER, or DROP). It can even be a database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).
Syntax
The CREATE TRIGGER statement is used in PL/SQL to create a trigger. The syntax:
CREATE [OR REPLACE ] TRIGGER name_of_trigger {BEFORE | AFTER | INSTEAD OF } {INSERT [OR] | UPDATE [OR] | DELETE} [OF col_name] ON table_name [REFERENCING OLD AS o NEW AS n] [FOR EACH ROW] WHEN (condition) DECLARE Declaration-statements BEGIN Executable-statements EXCEPTION Exception-handling-statements END;
Here,
Let us now take an example wherein we will create a trigger, trigger it, and even delete it.
For that, we are considering the following table <DEPTBUDGET>:
<DEPTBUDGET> +-----------+-----------------+---------------+------------------+ | DEPTID | DEPTNAME | DEPTLOC | BUDGET| +----------+------------------+---------------+------------------+ | 1 | Finance | NORTH | 56678 | | 2 | Marketing | EAST | 87687 | | 3 | Operations | WEST | 98979 | | 4 | Technical | SOUTH | 76878 | | 5 | Accounting | NORTHWEST| 86767 | +----------+------------------+------------------+--------------+
Create Trigger
Now we will create a row-level trigger that gets fired for INSERT or UPDATE or DELETE operations performed on the table. This is created to display the budget differences between new and old assigned budgets:
CREATE OR REPLACE TRIGGER budget_diff BEFORE DELETE OR INSERT OR UPDATE ON deptbudget FOR EACH ROW WHEN (NEW.ID > 0) DECLARE budget_diff number; BEGIN budget_diff := :NEW.budget - :OLD.budget; dbms_output.put_line('Old Budget Assigned =' || :OLD.budget); dbms_output.put_line('New Budget Assigned = ' || :NEW.budget); dbms_output.put_line('Differences in Budget = ' || budget); END; /
The above trigger it will fire before any DELETE or INSERT or UPDATE operation on the table, since we added the following above:
BEFORE DELETE OR INSERT OR UPDATE ON deptbudget
We will now perform a DML operation on the DEPTBUDGET table i.e. the UPDATE statement.
The statement would update an existing record in the table:
UPDATE budget SET = budget + 4500 WHERE id = 4;
We updated the record in the DEPTBUDGET table. When we will fire the “budget_diff”, it displays the following:
Old Budget Assigned = 76878 New Budget Assigned = 81378 Differences in Budget = 4500
Access one row at a time when a cursor is fetched in PL/SQL.
The following is when you fetch a cursor which is already opened. Here, d_deptbudget is the cursor name:
FETCH d_deptbudget INTO d_deptid, d_deptname, d_deptloc, d_budget;
Let us first see how to create a cursor, open and fetch the cursor. After that we will close it. This is all part of Explicit cursors since they are custom defined i.e. programmer defined cursors.
The following is the syntax to create explicit cursor:
CURSOR name_of_cursor IS select_statement;
Here,
“name_of_cursor” is the cursor name
As you can see above, an explicit cursor is created on a SELECT statement. Let us now see how to work with Explicit Cursors,
Let us declare a cursor and set a cursor name with the SELECT statement:
CURSOR d_deptbudget IS SELECT deptid, deptname, deptloc, budget FROM deptbudget;
Above, “d_deptbudget” is our cursor.
The memory gets allocated for the cursor when it is opened in PL/SQL. Opening the cursor allocates the memory for the cursor and makes it ready for fetching the rows returned by the SQL statement into it
OPEN d_deptbudget;
Access one row at a time when a cursor is fetched in PL/SQL.
The following is when you fetch a cursor which is already opened:
FETCH d_deptbudget INTO d_deptid, d_deptname, d_deptloc, d_budget;
When a cursor is closed, then the allocate memory is released. To close the above created cursor:
CLOSE d_deptbudget;
Let us now see an example:
DECLARE d_deptid deptbudget.deptid%type; d_deptname deptbudget.deptname%type; CURSOR d_deptbudget IS SELECT deptid, deptname FROM deptbudget; BEGIN OPEN d_deptbudget; LOOP FETCH d_deptbudget into d_deptid, d_deptname; EXIT WHEN d_deptbudget%notfound; dbms_output.put_line(d_deptid || ' ' || d_deptname); END LOOP; CLOSE d_deptbudget; END; /
The output display department id and name:
1 Finance 2 Marketing 3 Operations 4 Technical 5 Accounting
A transaction in PL/SQL is an atomic unit that may consist of one or more related SQL statements.
There is a difference between an executed SQL statement and a committed transaction. On successful execution of SQL statement, if transaction containing the statement is not committed, then it can be rolled back and the changes made by the statement can be undone. Here, committed means permanent in the database. However, rolled back means the changes can be undone.
Beginning with transactions, let us understand its concepts:
The COMMIT statement saves all the changes since the last COMMIT or ROLLBACK. An explicit or implicit request is made to commit a transaction.
The Rollback undone all the changes since the last COMMIT or ROLLBACK.
Undone all the changes made since the specified savepoint was made.
The SAVEPOINT statement forms a savepoint. This allows in performing partial ROLLBACKs. A transaction begins when the first SQL statement is encountered after connecting to the database.
To commit a transaction in PL/SQL, use the COMMIT command:
COMMIT;
Let’s say the following is the INSERT in the table. At the end, execute the COMMIT command if you want to commit the transaction:
INSERT INTO DEPTBUDGET (DEPTID,DEPTNAME,DEPTLOC) VALUES (1, 'Finance', 'NORTH'); INSERT INTO DEPTBUDGET (DEPTID,DEPTNAME,DEPTLOC) VALUES (2, 'Marketing', 'EAST'); INSERT INTO DEPTBUDGET (DEPTID,DEPTNAME,DEPTLOC) VALUES (3, 'Operations', 'WEST'); COMMIT;
To rollback the transactions, use the ROOLBACK command. The changes you made to the database without COMMIT can be undone with ROLLBACK.
The syntax:
ROLLBACK [TO SAVEPOINT < name_of_savepoint>];
Here, name_of_savepoint is the name of the savepoint.
If savepoint is not used, then simply use the “ROLLBACK” statement to rollback all the changes.
Savepoints splits a long transaction into smaller units. This is simply done using checkpoints. These checkpoints allow you to roll back to a checkpoint in a long transaction using the following command:
SAVEPOINT name_of_savepoint;
Let us see an example of SAVEPOINT:
INSERT INTO DEPTBUDGET (DEPTID,DEPTNAME,DEPTLOC, BUDGET) VALUES (1, 'Finance', 'NORTH', 20000); INSERT INTO DEPTBUDGET (DEPTID,DEPTNAME,DEPTLOC, BUDGET) VALUES (2, 'Marketing', 'EAST', 30000); SAVEPOINT s1; UPDATE DEPTBUDGET SET BUDGET = BUDGET - 4500; ROLLBACK TO s1; UPDATE DEPTBUDGET SET BUDGET = BUDGET - 4500; WHERE DEPTNAME = 'MARKETING'; COMMIT;
Above, we have created a SAVEPOINT s1, which will allow us to rollback.
The LIKE operator is a comparison operator in PL/SQL through which compares a character, string, or CLOB value to a pattern. The operator returns TRUE if the value matches the pattern and FALSE if it does not.
For an example, we will take a procedure here and check for values:
DECLARE PROCEDURE compare (myvalue varchar2, pattern varchar2 ) is BEGIN IF myvalue LIKE pattern THEN dbms_output.put_line ('True'); ELSE dbms_output.put_line ('False'); END IF; END; BEGIN compare('John', 'J%n'); compare('Jack', 'Ja_k'); compare('Amit', 'Am%'); compare('Sac', 'Sac_'); END; /
The output displays the following:
True True True False Statement processed. 0.00 seconds
Above we have used the following wildcards:
% | It allows to match any string of any length |
_ | It allows to match only a single character |
For example, above we used J%n for “John”, the output was true, since the % wildcard matches any string of any length.
The following are the methods available in PL/SQL to trim characters:
Method | Description |
---|---|
LTRIM(x [, trim_string]); | Trims characters from the left of x. |
RTRIM(x [, trim_string]); | Trims characters from the right of x. |
TRIM([trim_char FROM) x); | Trims characters from the left and right of x. |
The following is how you can use LTRIM() in PL/SQL to trim characters from the left:
DECLARE
cars varchar2(30) := '......Bentley.....';
BEGIN
dbms_output.put_line(LTRIM(cars,'.'));
END;
/
The output:
Bentley.....
The following is how you can use RTRIM() in PL/SQL to trim characters from the right:
DECLARE cars varchar2(30) := '......Bentley.....'; BEGIN dbms_output.put_line(RTRIM(cars,'.')); END; /
The output:
......Bentley
The following is how you can use TRIM() in PL/SQL to trim characters from the right and left:
DECLARE cars varchar2(30) := '......Bentley.....'; BEGIN dbms_output.put_line(TRIM(cars,'.')); END; /
The output:
Bentley
When a cursor is closed, then the allocate memory is released. To close a cursor, you need to use the CLOSE clause with the name of cursor you want to close.
CLOSE name_of_cursor;
Let us first see how to create a cursor, open and fetch the cursor. After that we will close it. This is all part of Explicit cursors since they are custom defined i.e. programmer defined cursors.
The following is the syntax to create explicit cursor:
CURSOR name_of_cursor IS select_statement;
Here,
“name_of_cursor” is the cursor name
As you can see above, an explicit cursor is created on a SELECT statement. Let us now see how to work with Explicit Cursors,
Let us declare a cursor and set a cursor name with the SELECT statement:
CURSOR d_deptbudget IS SELECT deptid, deptname, deptloc, budget FROM deptbudget;
Above, “d_deptbudget” is our cursor.
The memory gets allocated for the cursor when it is opened in PL/SQL. Opening the cursor allocates the memory for the cursor and makes it ready for fetching the rows returned by the SQL statement into it
OPEN d_deptbudget;
Access one row at a time when a cursor is fetched in PL/SQL.
The following is when you fetch a cursor which is already opened:
FETCH d_deptbudget INTO d_deptid, d_deptname, d_deptloc, d_budget;
When a cursor is closed, then the allocate memory is released. To close the above created cursor:
CLOSE d_deptbudget;
Let us now see an example:
DECLARE d_deptid deptbudget.deptid%type; d_deptname deptbudget.deptname%type; CURSOR d_deptbudget IS SELECT deptid, deptname FROM deptbudget; BEGIN OPEN d_deptbudget; LOOP FETCH d_deptbudget into d_deptid, d_deptname; EXIT WHEN d_deptbudget%notfound; dbms_output.put_line(d_deptid || ' ' || d_deptname); END LOOP; CLOSE d_deptbudget; END; /
The output display department id and name:
1 Finance 2 Marketing 3 Operations 4 Technical 5 Accounting
PL/SQL Block Structures and it executes as a whole block.
The block structure can be understood with the concept of subprograms in PL/SQL. A block structure has the following parts:
It contains declarations of cursors, constants, variables, exceptions, etc. Variables are declared here.
Had statements that perform actions. It must have atleast one statement.
The code that handles run-time errors. It use EXCEPTION keyword.
The following is the syntax to create a subprogram (procedure):
CREATE [OR REPLACE] PROCEDURE name_of_procedure [(parameter_name [IN | OUT | IN OUT] type [, ...])] {IS | AS} BEGIN < procedure_body > END name_of_procedure;
Here,
Here is an example of a subprogram (procedure):
CREATE OR REPLACE PROCEDURE one AS BEGIN dbms_output.put_line('Welcome!'); END; /
The output displays the procedure created successfully:
Procedure created.
One of the most frequently posed PLSQL interview questions for experienced, be ready for it.
In PL/SQL, packages are the schema objects that group logically related PL/SQL types and variables. A Package has the following two parts:
The specification has information about the package content. It declares the types, variables, constants, cursors, etc. It excludes the code for subprograms. The objects in the specification are the public object, however, a subprogram not part of the specification is a private object.
The Package Body has the implementation of subprogram declared in the specification. To create Package Body, use the CREATE PACKAGE BODY statement.
The following is our sample table:
<DEPTBUDGET> +-----------+-----------------+---------------+------------------+ | DEPTID | DEPTNAME | DEPTLOC | BUDGET| +----------+------------------+---------------+------------------+ | 1 | Finance | NORTH | 56678 | | 2 | Marketing | EAST | 87687 | | 3 | Operations | WEST | 98979 | | 4 | Technical | SOUTH | 76878 | | 5 | Accounting | NORTHWEST| 86767 | +----------+------------------+------------------+--------------+
The following is an example wherein we have a package “dept_pck”:
CREATE OR REPLACE PACKAGE BODY dept_pck AS PROCEDURE get_budget(d_id deptbudget.id%TYPE) IS d_budget deptbudget.budget%TYPE; BEGIN SELECT budget INTO d_budget FROM deptbudget WHERE id = d_id; dbms_output.put_line('Department Budget: '|| d_budget); END get_budget; END dept_pck; /
On executing the above code, the following is visible:
Package body created.
Now, to access the package variable, procedures, etc, you can use the dot operator as shown below:
package_name.element_name;
Continuing our example:
DECLARE myid deptbudget.id%type := &dd_id; BEGIN dept_pck. get_budget (myid); END; /
When you run the above code, you need to input the department id. Let’s say your input is department id 3, the output would be:
Department Budget: 98979
Expect to come across this popular question in advanced PL SQL interview questions.
With NOT INSTANTIABLE clause in PL/SQL, you can declare an abstract object. To work with it, you must create a subtype of such objects to use its functionalities.
It's no surprise that this one pops up often in PL SQL interview questions for experienced.
The HEXTORAW in PL/SQL was introduced to convert a hexadecimal value into a raw value. An example here displays the same:
HEXTORAW('7E')
The output:
7E
One of the most frequently posed PL SQL developer interview questions, be ready for it.
The INVALID_NUMBER exception is raised when the conversion of a character string into a number fails because the string does not represent a valid number.
The existing trigger is recreated by the optional argument [OR REPLACE] in a CREATE TRIGGER command.
A staple in PL SQL interview questions for experienced, be prepared to answer this one.
Each subprogram in PL/SQL has a name and a list of parameters. With that, it has the following parts:
Let us now see them one by one:
It contains declarations of cursors, constants, variables, exceptions, etc.
Had statements that perform actions.
The code that handles run-time errors.
The following is the syntax to create a subprogram (procedure):
CREATE [OR REPLACE] PROCEDURE name_of_procedure [(parameter_name [IN | OUT | IN OUT] type [, ...])] {IS | AS} BEGIN < procedure_body > END name_of_procedure;
Here,
With PL/SQL, you can easily pass a record as a subprogram parameter in the same way pass any other variable.
The following is an example:
DECLARE TYPE laptop IS RECORD (brand varchar(50), RAM number, SNO number ); lp1 laptop; lp2 laptop; lp3 laptop; PROCEDURE myDevice(lpt laptop) IS BEGIN -- Laptop record dbms_output.put_line('Laptop Brand = '|| lpt.brand); dbms_output.put_line('Laptop RAM = '|| lpt.RAM); dbms_output.put_line('Laptop SNO = ' || lpt.SNO); END; BEGIN -- Laptop 1 specification lp1.brand:= 'Dell'; lp1.RAM:= 4; lp1.SNO:= 87667; -- Laptop 2 specification lp2.brand:= 'Lenevo'; lp2.RAM:= 4; lp2.SNO:= 47656; -- Laptop 3 specification lp3.brand:= 'HP'; lp3.RAM:= 8; lp3.SNO:= 98989; myDevice(lp1); myDevice(lp2); myDevice(lp3); END; /
The output:
Laptop Brand = Dell Laptop RAM = 4 Laptop SNO = 87667 Laptop Brand = Lenevo Laptop RAM = 4 Laptop SNO = 47656 Laptop Brand = HP Laptop RAM = 8 Laptop SNO = 98989
Within a package, you can refer the objects and subprograms like this:
Like other programming languages, PL/SQL also allow users to create own exceptions. Use the RAISE statement or the procedure DBMS_STANDARD.RAISE_APPLICATION_ERROR to declare and raise the user-defined exception explicitly.
To create own exception, firstly consider the following table <DEPTBUDGET>:
<DEPTBUDGET> +-----------+-----------------+---------------+------------------+ | DEPTID | DEPTNAME | DEPTLOC | BUDGET| +----------+------------------+---------------+------------------+ | 1 | Finance | NORTH | 56678 | | 2 | Marketing | EAST | 87687 | | 3 | Operations | WEST | 98979 | | 4 | Technical | SOUTH | 76878 | | 5 | Accounting | NORTHWEST| 86767 | +----------+------------------+------------------+--------------+
Let’s say we created the following procedure and a user-defined exception in it:
DECLARE d_id deptbudget.id%type := &dd_id; d_name deptbudget.deptname%type; d_loc deptbudget.deptloc%type; my_exception EXCEPTION; BEGIN IF d_id <= 0 THEN RAISE my_exception; ELSE SELECT deptname, deptloc INTO d_name, d_loc FROM deptbudget WHERE deptid = d_id; DBMS_OUTPUT.PUT_LINE ('Name: '|| d_name); DBMS_OUTPUT.PUT_LINE ('Address: ' || d_loc); END IF; EXCEPTION WHEN my_exception THEN dbms_output.put_line('ID is always greater than zero!'); WHEN no_data_found THEN dbms_output.put_line('No such department in the company!'); WHEN others THEN dbms_output.put_line('Error!'); END; /
On executing the above procedure, the following output is visible. Let’s say our input is department id as “-2”, yes, a negative number. When execution, it raises the following user-defined error:
ID is always greater than zero!
The Large Object Data Types are pointers to large objects that are stored separately from other data items, like text, graphic images, video clips, etc. The following are the data types:
The %ROWCOUNT cursor attribute returns the number of rows affected by an INSERT, UPDATE, or DELETE statement, or returned by a SELECT INTO statement.
To use any of the above attribute, use the following syntax:
sql%attribute_name
Above, set any attribute name under “attribute_name”.
Let us consider the following table for our example:
<DEPTBUDGET> +-----------+-----------------+---------------+------------------+ | DEPTID | DEPTNAME | DEPTLOC | BUDGET | +----------+------------------+---------------+------------------+ | 1 | Finance | NORTH | 56678 | | 2 | Marketing | EAST | 87687 | | 3 | Operations | WEST | 95979 | | 4 | Technical | SOUTH | 76878 | | 5 | Accounting | NORTHWEST| 86767 | +----------+------------------+------------------+--------------+
Now, we will update the budget of all the departments and then check what all rows got affected using the implicit cursor attribute. With that we will use other attributes as well:
DECLARE rows_count number(2); BEGIN UPDATE deptbudget SET budget = budget + 3000; IF sql%notfound THEN dbms_output.put_line('Nothing selected!'); ELSIF sql%found THEN rows_count := sql%rowcount; dbms_output.put_line( rows_count || ' rows affected! '); END IF; END; /
The output:
5 rows affected!
Since we updated the budget for all the departments above, therefore our table rows would look like this:
<DEPTBUDGET> +-----------+-----------------+---------------+------------------+ | DEPTID | DEPTNAME | DEPTLOC | BUDGET| +----------+------------------+---------------+------------------+ | 1 | Finance | NORTH | 59678 | | 2 | Marketing | EAST | 90687 | | 3 | Operations | WEST | 98979 | | 4 | Technical | SOUTH | 79878 | | 5 | Accounting | NORTHWEST| 89767 | +----------+------------------+------------------+--------------+
To find the factorial of a number, the following is the code:
DECLARE a number :=1; n number := 5; BEGIN while n > 0 loop a:=n*a; n:=n-1; end loop; dbms_output.put_line(a); end;
The output:
120
To include single quotes in a string literal, you need to use it like:
“That wasn’’t a big blow to the team”
Above, we have set two single quotes to display one single quote.
Let us see an example.
Our string is:
It's my life
The following is the code to include single quotes:
DECLARE a varchar2(15):= 'It''s my life!'; BEGIN dbms_output.put_line(a); END; /
The output:
It's my life!
The BETWEEN operator in PL/SQL checks whether a value lies in a specified range or not.
For example, the value a BETWEEN x AND y says that a >= x and a <= y.
Let us see an example:
DECLARE val number(2) := 3; BEGIN IF (val between 1 and 5) THEN dbms_output.put_line('True'); ELSE dbms_output.put_line('False'); END IF; END; /
The output:
True
Let us see another example:
DECLARE val number(2) := 15; BEGIN IF (val between 1 and 10) THEN dbms_output.put_line('True'); ELSE dbms_output.put_line('False'); END IF; END; /
The output:
False
The RAISE statement is used in PL/SQL to raise exceptions. Through this a user can raise exceptions explicitly.
The following is the syntax to display how to use raise exceptions with RAISE statement:
DECLARE exception_name EXCEPTION; BEGIN IF condition THEN RAISE exception_name; END IF; EXCEPTION WHEN exception_name THEN statement; END;
An example can be if the ID is less than zero, the raise an exception:
IF d_id <= 0 THEN RAISE my_exception;
Above “my_exception” is the following we set above:
my_exception EXCEPTION;
Let us see an example wherein we are raising exceptions. The following is the table we are taking into consideration:
<DEPTBUDGET> +-----------+-----------------+---------------+------------------+ | DEPTID | DEPTNAME | DEPTLOC | BUDGET| +----------+------------------+---------------+------------------+ | 1 | Finance | NORTH | 56678 | | 2 | Marketing | EAST | 87687 | | 3 | Operations | WEST | 98979 | | 4 | Technical | SOUTH | 76878 | | 5 | Accounting | NORTHWEST| 86767 | +----------+------------------+------------------+--------------+
Let’s say we created the following procedure and a user-defined exception in it:
DECLARE id deptbudget.id%type := &d_id; deptname deptbudget.deptname%type; deptloc deptbudget.deptloc%type; my_exception EXCEPTION; BEGIN IF d_id <= 0 THEN RAISE my_exception; ELSE SELECT deptname, deptloc INTO deptname, deptloc FROM deptbudget WHERE id = d_id; DBMS_OUTPUT.PUT_LINE ('Name: '|| deptname); DBMS_OUTPUT.PUT_LINE ('Address: ' || deptloc); END IF; EXCEPTION WHEN my_exception THEN dbms_output.put_line('ID is always greater than zero!'); WHEN no_data_found THEN dbms_output.put_line('No such department in the company!'); WHEN others THEN dbms_output.put_line('Error!'); END; /
On executing the above procedure, the following output is visible. Let’s say our input is department id as “-2”, yes, a negative number. When execution, it raises the following user-defined error:
ID is always greater than zero!
This question is a regular feature in PLSQL interview questions, be ready to tackle it.
Actual parameters can be passed as Positional notation, Named notation as well as Mixed notation. In Positional Notation, the same parameters in the same order as they are declared in the procedure are specified.
The actual parameter is associated with the formal parameter in named notation using the arrow symbol ( => ) like:
x => a
The procedure call for this would be:
myFunction(x => a, y => b, z => c);
In PL/SQL, you can mix both notations in a procedure call with n mixed notation. Remember, the positional notation should precede the named notation.
myFunction(x, y, z => c);
The following is an example:
Let’s say we have below given table:
<DEPTBUDGET> +-----------+-----------------+---------------+------------------+ | DEPTID | DEPTNAME | DEPTLOC | BUDGET | +-----------+-----------------+---------------+------------------+ | 1 | Finance | NORTH | 56678 | | 2 | Marketing | EAST | 87687 | | 3 | Operations | WEST | 98979 | | 4 | Technical | SOUTH | 76878 | | 5 | Accounting | NORTHWEST| 86767 | +----------+------------------+------------------+--------------+
Now, we will call with Positional, mixed, and named notations:
DECLARE d_id NUMBER(6) := 2; d_budget NUMBER(6) := 500; PROCEDURE new_budget (id NUMBER, amount_add NUMBER) IS BEGIN UPDATE employees SET budget = budget + amount_add WHERE deptid = id; END new_budget ; BEGIN new_budget (did, d_budget ); -- positional procedure call for actual parameters new_budget (amount_add => d_budget , id=> d_id); -- named parameters new_budget (d_id, amount_add => d_budget ); -- mixed parameters END;
Above, you can see we have a procedure called “new_budget”. We are updating the budget here:
PROCEDURE new_budget (id NUMBER, amount_add NUMBER) IS BEGIN UPDATE employees SET budget = budget + amount_add WHERE deptid = id; END new_budget ;
We are calling one by one with positional, mixed, and named notation:
new_budget (did, d_budget ); -- positional procedure call for actual parameters new_budget (amount_add => d_budget , id=> d_id); -- named parameters new_budget (d_id, amount_add => d_budget ); -- mixed parameters
The TIMESTAMP datatype stores the year, month, and day of the DATE datatype. It also allows you to store hour, minute, and second values.
Expect to come across this popular question in PLSQL interview questions for experienced.
Yes, a %ROWCOUNT attribute returns the total number of rows returned by the FETCH command.
With PL/SQL, we can create a user-defined subtype. It already has predefined subtypes in STANDARD package.
Let us now see how to create an Integer subtype:
DECLARE SUBTYPE info IS varchar2(50); car info; BEGIN car:= 'I love cars!'; dbms_output.put_line(car); END; /
The output:
I love cars!
The WHEN clause has search conditions that output Boolean values in a searched CASE statement.
The following is the syntax of searched CASE that has the WHEN conditions:
CASE WHEN condition_1 THEN statements_1 WHEN condition_2 THEN statements_2 ... WHEN condition_n THEN statements_n [ ELSE else_statements ] END CASE;]
Let us see an example:
DECLARE points number := 100; BEGIN case when points = 20 then dbms_output.put_line('Rank 4'); when points = 50 then dbms_output.put_line('Rank 3'); when points = 75 then dbms_output.put_line('Rank 2'); when points = 100 then dbms_output.put_line('Rank1... Topper'); else dbms_output.put_line('No ranking!'); end case; END; /
The output:
Rank1... Topper
In PL/SQL, packages are the schema objects that groups logically related PL/SQL types and variables. A Package has the following two parts:
The specification has the information about the package content. It declares the types, variables, constant, cursors, etc. It excludes the code for subprograms. The objects in the specification are the public object, however, a subprogram not part of the specification is a private object.
The Package Body has the implementation of subprogram declared in the specification. To create Package Body, use the CREATE PACKAGE BODY statement.
The following are the guidelines:
A must-know for anyone heading into a PLSQL interview, this question is frequently asked in PLSQL developer interview questions.
VARRARY data structure stores a fixed-size sequential collection of elements of the same type. It has contiguous memory locations, with the lowest address as the first element and the highest address as the last element.
The CREATE TYPE statement is used to create a varray type. The following is the syntax for creating a VARRAY type within a PL/SQL block:
TYPE name_of_varray_type IS VARRAY(n) of <type_of_element>
Here,
n is the number of elements, name_of_varray_type is a valid attribute name, and type_of_element is the data type of the elements of the array
The following is an example:
Type marks IS VARRAY(10) OF INTEGER;
Let us see an example wherein we are displaying the appraisal points for employees according to their IDs:
DECLARE type points IS VARRAY(10) OF INTEGER; appraisal_points points; total integer; BEGIN appraisal_points:= points(76, 79, 89, 96, 99, 86, 89, 97); total := appraisal_points.count; FOR i in 1 .. total LOOP dbms_output.put_line('Appraisal Points for Employee ' || i || ' = ' || appraisal_points(i)); END LOOP; END; /
The output:
Appraisal Points for Employee 1 = 76 Appraisal Points for Employee 2 = 79 Appraisal Points for Employee 3 = 89 Appraisal Points for Employee 4 = 96 Appraisal Points for Employee 5 = 99 Appraisal Points for Employee 6 = 86 Appraisal Points for Employee 7 = 89 Appraisal Points for Employee 8 = 97
Sequence of characters are called strings. Just like any other language, a string can be letters, numbers, special characters, etc.
The following are the three types of strings in PL/SQL:
In fixed-length strings, the length is to be specified while declaring a string. The string is right-padded with spaces to the length so specified.
A maximum length up to 32,767 for the string is specified and no padding takes place.
CLOBs are variable-length strings that can be up to 128 terabytes.
Let us see how we can declare a String variable. We have a lot of string datatypes, such as CHAR, NCHAR, VARCHAR2, NVARCHAR2, CLOB, and NCLOB in PL/SQL.
The declaration:
DECLARE cars varchar2(25) := 'Bentley;
Let us see an example where we have two variables:
DECLARE car varchar2(15) := 'Bentley'; device varchar2(11) := 'Laptop'; BEGIN dbms_output.put_line(UPPER(car)); dbms_output.put_line(LOWER(car)); dbms_output.put_line(device); dbms_output.put_line(UPPER(device)); END; /
The output:
BENTLEY bentley Laptop LAPTOP
To pad a string with zeros, you can use RPAD or LPAD function in PL/SQL. Let us see how to pad the right side of a string with zeros:
RPAD(tom, 6)
The output would have a width of 6 characters now. Since the string is of 3 characters, therefore rest of the space would be filled with 0s:
tom000
It's no surprise that this one pops up often in advanced PL SQL interview questions.
The VSIZE() function returns the number of bytes in the internal representation of an expression. An example:
VSIZE('Selection Day')
The output:
13
The PL/SQL programming constructs include:
This is a frequently asked question in PL SQL interview questions.
PL/SQL is an extension for SQL and Oracle. It enhances the capabilities of SQL. Therefore, with every release of the Oracle Database, the feature and functionalities of PL/SQL are enhanced.
It is available in Oracle Database since version 6. However, PL/SQL is also available in TimesTen in-memory database since version 11.2.1 and IBM DB2 since version 9.7.
PL/SQL is strongly integrated with SQL and supports both static and dynamic SQL.
The following are the features in PL/SQL:
PL/SQL is an extension for SQL and Oracle. It enhances the capabilities of SQL. Therefore, with every release of the Oracle Database, the feature and functionalities of PL/SQL is enhanced.
It is available in Oracle Database since version 6. However, PL/SQL is also available in TimesTen in-memory database since version 11.2.1 and IBM DB2 since version 9.7.
TL/SQL is Transact Structure Query language. T-SQL expands on the SQL standard to add procedural programming, local variables, etc. It is Microsoft's and Sybase's proprietary extension to the SQL.
The following are the differences:
Basis | PL/SQL | T/SQL |
---|---|---|
Efficiency | It is more powerful than T/SQL. | T/SQL is less powerful than PL/SQL. |
Relation with SQL | PL/SQL is an extension for SQL and Oracle. It enhances the capabilities of SQL. | T-SQL expands on the SQL standard to add procedural programming, local variables, etc. |
Full Form | PL SQL is Procedural Language Structural Query Language. | TL SQL is Transact Structure Query language. |
Compatibility | PL-SQL works best with Oracle database Server. | T-SQL works best with Microsoft SQL Server |
Easier/ Complex | PL/SQL is considered complex to learn than T/SQL | It is easier to learn and work with T/SQL. |
Developer | It is developed and owned by Oracle. | T/SQL is Microsoft's and Sybase's proprietary extension to the SQL. |
PL/SQL is an extension for SQL and Oracle. It enhances the capabilities of SQL. Therefore, with every release of the Oracle Database, the feature and functionalities of PL/SQL is enhanced. It is available in Oracle Database since version 6. However, PL/SQL is also available in TimesTen in-memory database since version 11.2.1 and IBM DB2 since version 9.7.
SQL is a domain-specific language used in programming. It is a Structured Query Language for database creation, deletion, fetching rows, modifying rows, etc. relational database management system
The following are the differences:
Basis | PL/SQL | SQL |
---|---|---|
Embed | Can have SQL embedded in it. | Cannot have PL/SQL code embedded in it. |
Interaction with database server | There is no interaction with the database server. | Interaction with the database server. |
How/What | PL/SQL defines how it needs to be done. | Defines what needs to be done. |
Writing program/functions/procedures | PL/SQL is used to write program blocks, functions, procedures triggers, etc. | SQL is used to write queries, DDL and DML statements. |
Execution | Executes as a whole block. | Issue a single query or execute a single insert/update/delete. Executes one statement at a time. |
PL/SQL is an extension for SQL and Oracle. It enhances the capabilities of SQL. Therefore, with every release of the Oracle Database, the feature and functionalities of PL/SQL is enhanced. It is strongly integrated with SQL and supports both static and dynamic SQL.
The following are the characteristics of PL/SQL:
Expect to come across this popular question in PL SQL questions.
Oracle forms a memory area when an SQL statement is processed. This memory area is called the context area. A cursor in PL/SQL is a pointer to this context area. It has information about processing the statement. A PL/SQL cursor holds the rows (one or more) returned by a SQL statement.
The following are the two forms of cursors:
Implicit cursors in PL/SQL are created automatically by Oracle when an SQL statement is executed. This occurs when there is no explicit cursor for the statement.
An implicit cursor is associated when INSERT, UPDATE, and DELETE is issued. The attributes include %FOUND, %ISOPEN, %NOTFOUND, etc. With the attributes, you can check which rows got affected by the operation.
The attributes:
To use any of the above attributes, use the following syntax:
sql%attribute_name
Above, set any attribute name under “attribute_name”.Let us consider the following table for our example:
<DEPTBUDGET> +-----------+-----------------+---------------+------------------+ | DEPTID | DEPTNAME | DEPTLOC | BUDGET| +----------+------------------+---------------+------------------+ | 1 | Finance | NORTH | 56678 | | 2 | Marketing | EAST | 87687 | | 3 | Operations | WEST | 95979 | | 4 | Technical | SOUTH | 76878 | | 5 | Accounting | NORTHWEST| 86767 | +----------+------------------+------------------+--------------+
Now, we will update the budget of all the departments and then check what all rows got affected using the implicit cursor attribute. With that, we will use other attributes as well:
DECLARE rows_count number(2); BEGIN UPDATE deptbudget SET budget = budget + 3000; IF sql%notfound THEN dbms_output.put_line('Nothing selected!'); ELSIF sql%found THEN rows_count := sql%rowcount; dbms_output.put_line( rows_count || ' rows affected! '); END IF; END; /
The output:
5 rows affected!
Since we updated the budget for all the departments above, our table rows would look like this:
<DEPTBUDGET> +-----------+-----------------+---------------+------------------+ | DEPTID | DEPTNAME | DEPTLOC | BUDGET| +----------+------------------+---------------+------------------+ | 1 | Finance | NORTH | 59678 | | 2 | Marketing | EAST | 90687 | | 3 | Operations | WEST | 98979 | | 4 | Technical | SOUTH | 79878 | | 5 | Accounting | NORTHWEST| 89767 | +----------+------------------+------------------+--------------+
Explicit cursors are custom defined i.e. programmer-defined cursors that give more control over the context area. Oracle forms a memory area when an SQL statement is processed, which is called a context area.
The following is the syntax to create an explicit cursor:
CURSOR name_of_cursor IS select_statement;
Here,
“name_of_cursor” is the cursor name
As you can see above, an explicit cursor is created on a SELECT statement. Let us now see how to work with Explicit Cursors,
Let us declare a cursor and set a cursor name with the SELECT statement:
CURSOR d_deptbudget IS SELECT deptid, deptname, deptloc, budget FROM deptbudget;
Above, “d_deptbudget” is our cursor.
The memory gets allocated for the cursor when it is opened in PL/SQL. Opening the cursor allocates the memory for the cursor and makes it ready for fetching the rows returned by the SQL statement into it.
OPEN d_deptbudget;
Access one row at a time when a cursor is fetched in PL/SQL. The following is when you fetch a cursor that is already opened:
FETCH d_deptbudget INTO d_deptid, d_deptname, d_deptloc, d_budget;
When a cursor is closed, then the allocated memory is released. To close the above-created cursor:
CLOSE d_deptbudget;
Let us now see an example of working with explicit cursors in PL/SQL:
DECLARE d_deptid deptbudget.deptid%type; d_deptname deptbudget.deptname%type; CURSOR d_deptbudget IS SELECT deptid, deptname FROM deptbudget; BEGIN OPEN d_deptbudget; LOOP FETCH d_deptbudget into d_deptid, d_deptname; EXIT WHEN d_deptbudget%notfound; dbms_output.put_line(d_deptid || ' ' || d_deptname); END LOOP; CLOSE d_deptbudget; END; /
The output display department id and name:
1 Finance 2 Marketing 3 Operations 4 Technical 5 Accounting
The EXIT WHEN statement has a condition under the WHEN clause. The statement acts like NULL before the condition is TRUE.
The following is the syntax:
EXIT WHEN condition;
Here is an example:
DECLARE val number(2) := 2; BEGIN WHILE val < 10 LOOP dbms_output.put_line ('Value = ' || val); val := val + 1; EXIT WHEN val = 5; END LOOP; END; /
The output:
Value = 2 Value = 3 Value = 4
A code with a comment helps the developers in reading the source code properly. Like other programming languages, in PL/SQL, you can easily add comment to the code. Compiler ignores the content under the comment.
The single-line comments under PL/SQL begin with the delimiter:
-- (double hyphen)
The multi-line comments are enclosed by:
/* and */
Let us see both the comments in the following code:
DECLARE -- Variable declaration and initialization car varchar2(15) := 'Bentley'; BEGIN /* * UPPER() method in PL/SQL */ dbms_output.put_line(UPPER(car)); END; /
Above, we have used both the single line and multi-line comment. The output:
BENTLEY
Triggers are stored in a database and fired when some event occurs. The event here can be a Database Manipulation statement i.e. DELETE, INSERT, or UPDATE, or a Database Definition statement (CREATE, ALTER, or DROP). It can even be a database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).
Triggers are used to enforce referential integrity, auditing, or even applying security authorizations. It prevents invalid transactions and gather statistics on table access.
Syntax
The CREATE TRIGGER statement is used in PL/SQL to create a trigger. The syntax:
CREATE [OR REPLACE ] TRIGGER name_of_trigger {BEFORE | AFTER | INSTEAD OF } {INSERT [OR] | UPDATE [OR] | DELETE} [OF col_name] ON table_name [REFERENCING OLD AS o NEW AS n] [FOR EACH ROW] WHEN (condition) DECLARE Declaration-statements BEGIN Executable-statements EXCEPTION Exception-handling-statements END;
Here,
Let us now take an example wherein we will create a trigger, trigger it, and even delete it.
For that, we are considering the following table <DEPTBUDGET>:
<DEPTBUDGET> +-----------+-----------------+---------------+------------------+ | DEPTID | DEPTNAME | DEPTLOC | BUDGET| +----------+------------------+---------------+------------------+ | 1 | Finance | NORTH | 56678 | | 2 | Marketing | EAST | 87687 | | 3 | Operations | WEST | 98979 | | 4 | Technical | SOUTH | 76878 | | 5 | Accounting | NORTHWEST| 86767 | +----------+------------------+------------------+--------------+
Create Trigger
Now we will create a row-level trigger that gets fired for INSERT or UPDATE or DELETE operations performed on the table. This is created to display the budget differences between new and old assigned budgets:
CREATE OR REPLACE TRIGGER budget_diff BEFORE DELETE OR INSERT OR UPDATE ON deptbudget FOR EACH ROW WHEN (NEW.ID > 0) DECLARE budget_diff number; BEGIN budget_diff := :NEW.budget - :OLD.budget; dbms_output.put_line('Old Budget Assigned =' || :OLD.budget); dbms_output.put_line('New Budget Assigned = ' || :NEW.budget); dbms_output.put_line('Differences in Budget = ' || budget); END; /
The above trigger it will fire before any DELETE or INSERT or UPDATE operation on the table, since we added the following above:
BEFORE DELETE OR INSERT OR UPDATE ON deptbudget
We will now perform a DML operation on the DEPTBUDGET table i.e. the UPDATE statement.
The statement would update an existing record in the table:
UPDATE budget SET = budget + 4500 WHERE id = 4;
We updated the record in the DEPTBUDGET table. When we will fire the “budget_diff”, it displays the following:
Old Budget Assigned = 76878 New Budget Assigned = 81378 Differences in Budget = 4500
PL/SQL is an extension for SQL and Oracle. It enhances the capabilities of SQL. Therefore, with every release of the Oracle Database, the feature and functionalities of PL/SQL is enhanced. It is strongly integrated with SQL and supports both static and dynamic SQL.
The following are the advantages of PL/SQL:
PL/SQL has both static and dynamic SQL. Static SQL with DML operations and transaction control from PL/SQL block. SQL allows embedding DDL statements in PL/SQL blocks in Dynamic SQL.
PL/SQL has exception handling that helps in easier debugging.
PL/SQL allows faster update of database. Therefore, beneficial for programmers.
With PL/SQL, we can easily perform faster execution of queries.
PL/SQL executes as a whole block i.e. sends entire block of statements to the database at once.
Since PL/SQL is an extension of SQL, you get access to predefined SQL packages.
PL/SQL also comes with the functionality of Triggers. Triggers are stored in a database and fired when some event occurs. The event here can be a Database Manipulation statement i.e. DELETE, INSERT, or UPDATE, or a Database Definition statement (CREATE, ALTER, or DROP).
PL/SQL and SQL brings optimal efficiency since it runs within the same server process.
Error Management in PL/SQL brings in internally defined, predefined, and user-define exceptions and process to handle them with error codes and messages. With that, PL/SQL also has RAISE statement for your own defined exceptions.
A must-know for anyone heading into a PL SQL interview, this question is frequently asked in PL SQL developer interview questions.
The types of Datatypes are categorized as the following:
1. Scalar Data Type
The Data Types have single values with no internal components. Here are the predefined Scalar Data Types with the description of its data:
2. Composite Data Type
Data items that have internal components that can be accessed individually
3. Reference Data Type
Pointers to other data items
4. Large Object Data Types (LOB)
These Data Types are pointers to large objects that are stored separately from other data items, like text, graphic images, video clips, etc. The following are the data types:
If a PL/SQL program violate the rule of Oracle, then an exception is raised. The exceptions are handled by name, for example, DUP_VAL_ON_INDEX exceptions is raised when an attempt is made to store duplicate values in a database column constrained by a unique index.
Let us learn about the predefined exceptions in PL/SQL:
This exception is raised when an attempt is made to the attributes of an uninitialized object.
This exception is raised when none of the choices in the WHEN clause of a CASE statement is selected and there is no ELSE clause.
This exception is raised when an attempt is made to apply collection methods other than EXISTS to an uninitialized nested table or varray.
This exception is raised when duplicate values are attempted to be stored in a column with unique index.
This exception is raised when attempts are made to make a cursor operation that is not allowed, such as closing an unopened cursor.
This exception is raised when the conversion of a character string into a number fails because the string does not represent a valid number.
This exception is raised when a program attempts to log on to the database with an invalid username or password.
This exception is raised when a SELECT INTO statement returns no rows.
This exception is raised when a database call is issued without being connected to the database.
This exception is raised when there is an internal problem.
This exception is raised when a cursor fetches value in a variable having incompatible data type.
This exception is raised when a member method is invoked, but the instance of the object type was not initialized.
This exception is raised when PL/SQL ran out of memory.
This exception is raised when SELECT INTO statement returns more than one row.
This exception is raised when arithmetic, conversion, or truncation error occurs.
This exception is raised when an attempt is made to divide a number by zero.
Literals are syntactic representation of numeric, character, string, or Boolean value. The literals in PL/SQL are case-sensitive.
The following are the literals in PL/SQL:
'Mystring!' 'Web!'
Examples: TRUE, FALSE
DATE '2018-12-20'; TIMESTAMP '2018-12-15 11:10:30';
In PL/SQL, it is possible to create your own records i.e. user-defined. With that, you can set the different record structures.
Let us take an example of a Laptop’s record. Here is how you can declare:
DECLARE TYPE laptop IS RECORD (brand varchar(50), RAM number, SNO number, ); l1 laptop; l1 laptop;
You can now access the fields using the dot(.) operator.
The following is the example wherein we have created user-define records and accesses the fields:
DECLARE TYPE laptop IS RECORD (brand varchar(50), RAM number, SNO number ); lp1 laptop; lp2 laptop; lp3 laptop; BEGIN -- Laptop 1 specification lp1.brand:= 'Dell'; lp1.RAM:= 4; lp1.SNO:= 87667; -- Laptop 2 specification lp2.brand:= 'Lenevo'; lp2.RAM:= 4; lp2.SNO:= 47656; -- Laptop 3 specification lp3.brand:= 'HP'; lp3.RAM:= 8; lp3.SNO:= 98989; -- Laptop 1 record dbms_output.put_line('Laptop 1 Brand = '|| lp1.brand); dbms_output.put_line('Laptop 1 RAM = '|| lp1.RAM); dbms_output.put_line('Laptop 1 SNO = ' || lp1.SNO); -- Laptop 2 record dbms_output.put_line('Laptop 2 Brand = '|| lp2.brand); dbms_output.put_line('Laptop 2 RAM = '|| lp2.RAM); dbms_output.put_line('Laptop 2 SNO = ' || lp2.SNO); -- Laptop 3 record dbms_output.put_line('Laptop 3 Brand = '|| lp3.brand); dbms_output.put_line('Laptop 3 RAM = '|| lp3.RAM); dbms_output.put_line('Laptop 2 SNO = ' || lp3.SNO); END;
The output:
Laptop 1 Brand = Dell Laptop 1 RAM = 4 Laptop 1 SNO = 87667 Laptop 2 Brand = Lenevo Laptop 2 RAM = 4 Laptop 2 SNO = 47656 Laptop 3 Brand = HP Laptop 3 RAM = 8 Laptop 2 SNO = 98989
It's no surprise that this one pops up often in PLSQL interview questions.
There are two types of exceptions that exist in PL/SQL:
System Defined Exceptions
The predefined exceptions in PL/SQL are System Defined Exceptions. If a PL/SQL program violate the rule of Oracle, then an exception is raised. The exceptions are handled by name, for example, DUP_VAL_ON_INDEX exceptions are raised when an attempt is made to store duplicate values in a database column constrained by a unique index.
Let us learn about the predefined exceptions in PL/SQL:
User-Defined Exceptions
Like other programming languages, PL/SQL also allows users to create their own exceptions. Use the RAISE statement or the procedure DBMS_STANDARD.RAISE_APPLICATION_ERROR to declare and raise the user-defined exception explicitly.
To create your own exception, first consider the following table <DEPTBUDGET>:
<DEPTBUDGET> +-----------+-----------------+---------------+------------------+ | DEPTID | DEPTNAME | DEPTLOC | BUDGET| +----------+------------------+---------------+------------------+ | 1 | Finance | NORTH | 56678 | | 2 | Marketing | EAST | 87687 | | 3 | Operations | WEST | 98979 | | 4 | Technical | SOUTH | 76878 | | 5 | Accounting | NORTHWEST| 86767 | +----------+------------------+------------------+--------------+
Let’s say we created the following procedure and a user-defined exception in it:
DECLARE id deptbudget.id%type := &d_id; deptname deptbudget.deptname%type; deptloc deptbudget.deptloc%type; my_exception EXCEPTION; BEGIN IF d_id <= 0 THEN RAISE my_exception; ELSE SELECT deptname, deptloc INTO deptname, deptloc FROM deptbudget WHERE id = d_id; DBMS_OUTPUT.PUT_LINE ('Name: '|| deptname); DBMS_OUTPUT.PUT_LINE ('Address: ' || deptloc); END IF; EXCEPTION WHEN my_exception THEN dbms_output.put_line('ID is always greater than zero!'); WHEN no_data_found THEN dbms_output.put_line('No such department in the company!'); WHEN others THEN dbms_output.put_line('Error!'); END; /
On executing the above procedure, the following output is visible. Let’s say our input is department id as “-2”, yes, a negative number. When executed, it raises the following user-defined error:
ID is always greater than zero!
Implicit cursors in PL/SQL are created automatically by Oracle when an SQL statement is executed. This occurs when there is no explicit cursor for the statement.
An implicit cursor is associated, when INSERT, UPDATE and DELETE is issued. The attributes include %FOUND, %ISOPEN, %NOTFOUND, etc. With the attributes, you can check which all rows got affected by the operation.
The attributes:
To use any of the above attribute, use the following syntax:
sql%attribute_name
Above, set any attribute name under “attribute_name”.
Let us consider the following table for our example:
<DEPTBUDGET> +-----------+-----------------+---------------+------------------+ | DEPTID | DEPTNAME | DEPTLOC | BUDGET| +----------+------------------+---------------+------------------+ | 1 | Finance | NORTH | 56678 | | 2 | Marketing | EAST | 87687 | | 3 | Operations | WEST | 95979 | | 4 | Technical | SOUTH | 76878 | | 5 | Accounting | NORTHWEST| 86767 | +----------+------------------+------------------+--------------+
Now, we will update the budget of all the departments and then check what all rows got affected using the implicit cursor attribute. With that we will use other attributes as well:
DECLARE rows_count number(2); BEGIN UPDATE deptbudget SET budget = budget + 3000; IF sql%notfound THEN dbms_output.put_line('Nothing selected!'); ELSIF sql%found THEN rows_count := sql%rowcount; dbms_output.put_line( rows_count || ' rows affected! '); END IF; END; /
The output:
5 rows affected!
Since we updated the budget for all the departments above, therefore our table rows would look like this:
<DEPTBUDGET> +-----------+-----------------+---------------+------------------+ | DEPTID | DEPTNAME | DEPTLOC | BUDGET| +----------+------------------+---------------+------------------+ | 1 | Finance | NORTH | 59678 | | 2 | Marketing | EAST | 90687 | | 3 | Operations | WEST | 98979 | | 4 | Technical | SOUTH | 79878 | | 5 | Accounting | NORTHWEST| 89767 | +----------+------------------+------------------+--------------+
A transaction in PL/SQL is an atomic unit that may consist of one or more related SQL statements.
To commit a transaction in PL/SQL, use the COMMIT command:
COMMIT;
The COMMIT statement saves all the changes since the last COMMIT or ROLLBACK. An explicit or implicit request is made to commit a transaction.
Let’s say the following is the INSERT in the table. At the end, execute the COMMIT command if you want to commit the transaction:
INSERT INTO DEPTBUDGET (DEPTID,DEPTNAME,DEPTLOC) VALUES (1, 'Finance', 'NORTH'); INSERT INTO DEPTBUDGET (DEPTID,DEPTNAME,DEPTLOC) VALUES (2, 'Marketing', 'EAST'); INSERT INTO DEPTBUDGET (DEPTID,DEPTNAME,DEPTLOC) VALUES (3, 'Operations', 'WEST'); COMMIT;
In PL/SQL, use the %ROWTYPE attribute with an explicit cursor or cursor variable where each field corresponds to a column in the cursor SELECT statement.
The following is an example:
DECLARE CURSOR cur_dept is SELECT deptid, deptname, deptloc FROM deptbudget; rec_dept cur_dept %rowtype; BEGIN OPEN cur_dept ; LOOP FETCH cur_dept into rec_dept; EXIT WHEN cur_dept %notfound; DBMS_OUTPUT.put_line(rec_dept.deptid || ' ' || rec_dept.deptname || ' ' || rec_dept.deptloc); END LOOP; END; /
The output:
1 Finance NORTH 2 Marketing EAST 3 Operations WEST 4 Technical SOUTH 5 Accounting NORTHWEST PL/SQL procedure successfully completed.
The predefined numeric datatypes are subtypes of the Scalar Data Types.
The following are the subtypes of the numeric datatypes:
Signed integer in range -2,147,483,648 through 2,147,483,647, represented in 32 bits
Signed integer in range -2,147,483,648 through 2,147,483,647, represented in 32 bits
Single-precision IEEE 754-format floating-point number
Double-precision IEEE 754-format floating-point number
ANSI specific fixed-point type with maximum precision of 38 decimal digits
IBM specific fixed-point type with maximum precision of 38 decimal digits
Floating type with maximum precision of 38 decimal digits
ANSI specific floating-point type with maximum precision of 126 binary digits (approximately 38 decimal digits)
ANSI and IBM specific integer type with maximum precision of 38 decimal digits
Floating-point type with maximum precision of 63 binary digits (approximately 18 decimal digits)
A common question in PL/SQL interview questions, don't miss this one.
PL/SQL allocates memory for the variable's value when a variable is declared. The storage location is identified by the variable name.
Declaration
The following is the syntax:
variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value]
Here,
Example for a string variable:
name varchar2(15);
Let us see an example of a number precision limit. This is called constraint declaration that needs less memory:
a number(5, 2)
Initialization
To initialize a variable, you can use the DEFAULT keyword as well as the assignment operator. Let us see them one by one.
Initialization with DEFAULT keyword:
DECLARE msg varchar2(15) DEFAULT 'House'; BEGIN dbms_output.put_line(msg); END; /
The output:
House
Initialization with assignment operator:
DECLARE device varchar2(11) := 'Laptop'; BEGIN dbms_output.put_line(device); END; /
The output:
Laptop
The Datetime Interval data type stores and manipulate dates, times, and intervals.
A variable with date and time data type stores values called datetimes. A variable with interval data type stores values called intervals.
Here are the field names:
YEAR
MONTH
DAY
HOUR
MINUTE
SECOND
TIMEZONE_HOUR
TIMEZONE_MINUTE
TIMEZONE_REGION
TIMEZONE_ABBR
The data type TIMESTAMP stores the year, month, day, hour, minute, and second. TIMESTAMP extends the data type DATE.
The following is the syntax:
TIMESTAMP[(precision)
Here,
Precision: The number of digits in the fractional part of the seconds field. Optional.
The following is an example:
DECLARE mydate TIMESTAMP(2); BEGIN mydate := '20-Dec-2018 07:48:53.275'; DBMS_OUTPUT.PUT_LINE( TO_CHAR(mydate )); END; /
The output:
20-DEC-18 07.48.53.28 AM
In PL/SQL, you can also find a datatype:
TIMESTAMP WITH TIME ZONE
The above datatype includes a time-zone displacement. The time-zone displacement is the difference between local time and Coordinated Universal Time (UTC,) formerly Greenwich Mean Time (GMT). This difference is in hours and minutes.
The following is the syntax:
TIMESTAMP[(precision)] WITH TIME ZONE
Here,
Precision: The number of digits in the fractional part of the seconds field. Optional.
Let us see an example:
DECLARE mydate TIMESTAMP(2) WITH TIME ZONE; BEGIN mydate := '10-Dec-2018 11:28:55.114 AM +03:00'; DBMS_OUTPUT.PUT_LINE( TO_CHAR(mydate )); END; /
The output:
10-DEC-18 11.28.55.11 AM +03:00
A delimiter is a compound symbol with a special meaning to PL/SQL. For example, you use delimiters to represent arithmetic operations such division. For example, a delimiter symbol is also the assignment operator used in PL/SQL:
:=
An example would be:
DECLARE device varchar2(15) := 'Laptop'; BEGIN dbms_output.put_line(device); END; /
The following are the symbols of Delimiters:
Symbol | Meaning |
---|---|
+ | addition operator |
% | attribute indicator |
' | character string delimiter |
. | component selector |
/ | division operator |
( | expression or list delimiter |
) | expression or list delimiter |
: | host variable indicator |
, | item separator |
* | multiplication operator |
" | quoted identifier delimiter |
= | relational operator |
< | relational operator |
> | relational operator |
@ | remote access indicator |
; | statement terminator |
- | subtraction/negation operator |
:= | assignment operator |
=> | association operator |
|| | concatenation operator |
** | exponentiation operator |
<< | label delimiter (begin) |
>> | label delimiter (end) |
/* | multi-line comment delimiter (begin) |
*/ | multi-line comment delimiter (end) |
.. | range operator |
<> | relational operator |
!= | relational operator |
~= | relational operator |
^= | relational operator |
<= | relational operator |
>= | relational operator |
-- | single-line comment indicator |
Explicit cursors are custom defined i.e. programmer defined cursors that gives more control over the context area. Oracle forms a memory area, when an SQL statement is processed, which is called context area.
The following is the syntax to create explicit cursor:
CURSOR name_of_cursor IS select_statement;
Here,
“name_of_cursor” is the cursor name
As you can see above, an explicit cursor is created on a SELECT statement. Let us now see how to work with Explicit Cursors,
Let us declare a cursor and set a cursor name with the SELECT statement:
CURSOR d_deptbudget IS SELECT deptid, deptname, deptloc, budget FROM deptbudget;
Above, “d_deptbudget” is our cursor.
The memory gets allocated for the cursor when it is opened in PL/SQL.
Opening the cursor allocates the memory for the cursor and makes it ready for fetching the rows returned by the SQL statement into it
OPEN d_deptbudget;
Access one row at a time when a cursor is fetched in PL/SQL.
The following is when you fetch a cursor which is already opened:
FETCH d_deptbudget INTO d_deptid, d_deptname, d_deptloc, d_budget;
When a cursor is closed, then the allocate memory is released. To close the above created cursor:
CLOSE d_deptbudget;
Let us now see an example to work with explicit cursors in PL/SQL:
DECLARE d_deptid deptbudget.deptid%type; d_deptname deptbudget.deptname%type; CURSOR d_deptbudget IS SELECT deptid, deptname FROM deptbudget; BEGIN OPEN d_deptbudget; LOOP FETCH d_deptbudget into d_deptid, d_deptname; EXIT WHEN d_deptbudget%notfound; dbms_output.put_line(d_deptid || ' ' || d_deptname); END LOOP; CLOSE d_deptbudget; END; /
The output display department id and name:
1 Finance 2 Marketing 3 Operations 4 Technical 5 Accounting
The modes of parameters in a PL/SQL subprogram includes the following:
The IN parameter is a read-only parameter that allows you to pass a value to the subprogram.
It acts as a constant in the subprogram. A constant, literal, initialized variable, or expression can be passed as an IN parameter.
The OUT parameter acts like a variable in a subprogram. Its value can be changed unlike the IN parameter.The value can be reference after assignment.
The actual parameter is passed by value. The initial value is passed to a subprogram. The updated value is returned to the caller.
Let us see an example:
DECLARE val number; PROCEDURE display(n IN OUT number) IS BEGIN n := n * n; END; BEGIN val:= 42; display(val); dbms_output.put_line('Square of 42 = ' || val); END; /
The output:
Square of 42 = 1764
To join two or more strings i.e. concatenation, PL/SQL comes with concatenation operator (||). This operator returns a string value.
The following is the syntax:
str1 || str2 [ || str_n ]
Here,
The following is an example:
DECLARE one varchar2(20) := 'Website'; two varchar2(20) := ' World!'; BEGIN dbms_output.put_line(one || two); END; /
The output:
Website World!
A record is a data structure in PL/SQL to hold data items of different kinds.
For our examples on records in PL/SQL, we have the following sample table:
<DEPTBUDGET> +-----------+-----------------+---------------+------------------+ | DEPTID | DEPTNAME | DEPTLOC | BUDGET| +----------+------------------+---------------+------------------+ | 1 | Finance | NORTH | 56678 | | 2 | Marketing | EAST | 87687 | | 3 | Operations | WEST | 98979 | | 4 | Technical | SOUTH | 76878 | | 5 | Accounting | NORTHWEST| 86767 | +----------+------------------+------------------+--------------+
PL/SQL handles the following types of records:
To create table-based records, you need to use the %ROWTYPE attribute. Using the same we will fetch the records of a department with id = 3:
DECLARE rec_dept deptbudget%rowtype; BEGIN SELECT * into rec_dept FROM customerdeptbudgets WHERE deptid = 3; dbms_output.put_line('Department ID =' || rec_dept.deptd); dbms_output.put_line('Department Name = ' || rec_dept.deptname); dbms_output.put_line('Department Location = ' || rec_dept.deptloc); dbms_output.put_line('Department Budget = ' || rec_dept.budget); END; /
The output:
Department ID = 3 Department Name = Operations Department Location = WEST Department Budget = 98979 PL/SQL procedure successfully completed.
In PL/SQL, use the %ROWTYPE attribute with an explicit cursor or cursor variable where each field corresponds to a column in the cursor SELECT statement.
The following is an example:
DECLARE CURSOR cur_dept is SELECT deptid, deptname, deptloc FROM deptbudget; rec_dept cur_dept %rowtype; BEGIN OPEN cur_dept ; LOOP FETCH cur_dept into rec_dept; EXIT WHEN cur_dept %notfound; DBMS_OUTPUT.put_line(rec_dept.deptid || ' ' || rec_dept.deptname || ' ' || rec_dept.deptloc); END LOOP; END; /
The output:
1 Finance NORTH 2 Marketing EAST 3 Operations WEST 4 Technical SOUTH 5 Accounting NORTHWEST PL/SQL procedure successfully completed.
In PL/SQL, it is possible to create your own records i.e. user-defined. With that, you can set the different record structures.
Let us take an example of a Laptop’s record. Here is how you can declare:
DECLARE TYPE laptop IS RECORD (brand varchar(50), RAM number, SNO number, ); l1 laptop; l1 laptop;
You can now access the fields using the dot(.) operator.
The following is the example wherein we have created user-define records and accesses the fields:
DECLARE TYPE laptop IS RECORD (brand varchar(50), RAM number, SNO number ); lp1 laptop; lp2 laptop; lp3 laptop; BEGIN -- Laptop 1 specification lp1.brand:= 'Dell'; lp1.RAM:= 4; lp1.SNO:= 87667; -- Laptop 2 specification lp2.brand:= 'Lenevo'; lp2.RAM:= 4; lp2.SNO:= 47656; -- Laptop 3 specification lp3.brand:= 'HP'; lp3.RAM:= 8; lp3.SNO:= 98989; -- Laptop 1 record dbms_output.put_line('Laptop 1 Brand = '|| lp1.brand); dbms_output.put_line('Laptop 1 RAM = '|| lp1.RAM); dbms_output.put_line('Laptop 1 SNO = ' || lp1.SNO); -- Laptop 2 record dbms_output.put_line('Laptop 2 Brand = '|| lp2.brand); dbms_output.put_line('Laptop 2 RAM = '|| lp2.RAM); dbms_output.put_line('Laptop 2 SNO = ' || lp2.SNO); -- Laptop 3 record dbms_output.put_line('Laptop 3 Brand = '|| lp3.brand); dbms_output.put_line('Laptop 3 RAM = '|| lp3.RAM); dbms_output.put_line('Laptop 2 SNO = ' || lp3.SNO); END;
The output:
Laptop 1 Brand = Dell Laptop 1 RAM = 4 Laptop 1 SNO = 87667 Laptop 2 Brand = Lenevo Laptop 2 RAM = 4 Laptop 2 SNO = 47656 Laptop 3 Brand = HP Laptop 3 RAM = 8 Laptop 2 SNO = 98989
The valid values for seconds:
The INSTRB is a function used in PL/SQL to return the location of a string within another string. The value is returned in bytes.
An example:
INSTR('Jack Sparrow', 'c')
The above returns the output in bytes:
3
Like any other language, PL/SQL also provides a functionality to handle exceptions. This is done using the EXCEPTION block. To raise exceptions explicitly, use the RAISE command.
To handle exceptions in PL/SQL, here is the syntax:
DECLARE <declarations section> BEGIN <executable command(s)> EXCEPTION <exception handling > WHEN exception1 THEN exception1-handling-statements WHEN exception2 THEN exception2-handling-statements WHEN exception3 THEN exception3-handling-statements ........ WHEN others THEN exception-handling-statements END;
Above, WHEN is used for exceptions like:
WHEN no_data_found THEN
With that, the following is to be used for default exception:
WHEN others THEN
Before beginning our example, let’s say we have the following table:
<DEPTBUDGET> +-----------+-----------------+---------------+------------------+ | DEPTID | DEPTNAME | DEPTLOC | BUDGET| +----------+------------------+---------------+------------------+ | 1 | Finance | NORTH | 56678 | | 2 | Marketing | EAST | 87687 | | 3 | Operations | WEST | 98979 | | 4 | Technical | SOUTH | 76878 | | 5 | Accounting | NORTHWEST| 86767 | +----------+------------------+------------------+--------------+
The following is our example wherein we are handling exceptions. The dept
DECLARE d_id deptbudget.deptid%type := 20; d_ name deptbudget.deptname%type; d_loc deptbudget.deptloc%type; BEGIN SELECT deptname, deptloc INTO d_name, d_loc FROM deptbudget WHERE deptid = d_id; DBMS_OUTPUT.PUT_LINE ('Name: '|| d_name); DBMS_OUTPUT.PUT_LINE ('Address: ' || d_loc); END IF; EXCEPTION WHEN no_data_found THEN dbms_output.put_line('No such department in the company!'); WHEN others THEN dbms_output.put_line('Error!'); END; /
The output:
No such department in the company
Variable Scope defines that if you declare a variable within an inner bloc, it is not accessible for the outer. On the contrary, if a variable is declared in outer block, then it is accessible in the entire program whether its outer, inner, nested inner blocks.
The following are the scope of variables in PL/SQL:
Local variables
Global variables
Let us now see an example to learn the role of local and global variables in scope of variables in PL/SQL:
DECLARE -- Global variables a number := 5; BEGIN dbms_output.put_line('Outer Variable a = ' || a); DECLARE -- Local variables a number := 10; BEGIN dbms_output.put_line('Inner Variable a = ' || a); END; END; /
The output:
Outer Variable a = 5 Inner Variable a = 10
Nested tables are one of the collection types in PL/SQL. They are created either in PL/SQL block or at schema level. These are like a 1D array, but its size can get increased dynamically.
The following is the syntax:
TYPE type_name IS TABLE OF element_type [NOT NULL]; name_of_table type_name;
The following is an example:
DECLARE TYPE deptname IS TABLE OF VARCHAR2(10); TYPE budget IS TABLE OF INTEGER; names deptname; deptbudget budget; BEGIN names := deptname ('Finance', 'Sales', 'Marketing'); deptbudget := budget (89899, 67879, 98999); FOR i IN 1 .. names.count LOOP dbms_output.put_line('Department = '||names(i)||', Budget = ' || deptbudget(i)); end loop; END; /
The output:
Department = Finance, Budget = 89899 Department = Sales, Budget = 67879 Department = Marketing, Budget = 98999
No, this is not a correct way to implement assignment in PL/SQL. In PL/SQL, the assignment statement is the following:
:=
Let us see in the example:
sum := sum +10;
The following is another example:
DECLARE cost number := 3000; BEGIN dbms_output.put_line(cost); END; /
The output:
3000
This is a frequently asked question in PLSQL interview questions.
The Rollback undone all the changes since the last COMMIT or ROLLBACK.
To commit a transaction in PL/SQL, use the COMMIT command:
COMMIT;
Let’s say the following is the INSERT in the table. At the end, execute the COMMIT command if you want to commit the transaction:
INSERT INTO DEPTBUDGET (DEPTID,DEPTNAME,DEPTLOC) VALUES (1, 'Finance', 'NORTH'); INSERT INTO DEPTBUDGET (DEPTID,DEPTNAME,DEPTLOC) VALUES (2, 'Marketing', 'EAST'); INSERT INTO DEPTBUDGET (DEPTID,DEPTNAME,DEPTLOC) VALUES (3, 'Operations', 'WEST'); COMMIT;
To rollback the transactions, use the ROOLBACK command. The changes you made to the database without COMMIT can be undone with ROLLBACK.
The syntax:
ROLLBACK [TO SAVEPOINT < name_of_savepoint>];
Here, name_of_savepoint is the name of the savepoint.
If savepoint is not used, then simply use the “ROLLBACK” statement to rollback all the changes.
ROLLBACK;
The SAVEPOINT statement forms a savepoint. This allows in performing partial ROLLBACKs.
Savepoints splits a long transaction into smaller units. This is simply done using checkpoints. These checkpoints allow you to roll back to a checkpoint in a long transaction using the following command:
SAVEPOINT name_of_savepoint;
Let us see an example of SAVEPOINT:
INSERT INTO DEPTBUDGET (DEPTID,DEPTNAME,DEPTLOC, BUDGET) VALUES (1, 'Finance', 'NORTH', 20000); INSERT INTO DEPTBUDGET (DEPTID,DEPTNAME,DEPTLOC, BUDGET) VALUES (2, 'Marketing', 'EAST', 30000); SAVEPOINT s1; UPDATE DEPTBUDGET SET BUDGET = BUDGET - 4500; ROLLBACK TO s1; UPDATE DEPTBUDGET SET BUDGET = BUDGET - 4500; WHERE DEPTNAME = 'MARKETING'; COMMIT;
Above, we have created a SAVEPOINT s1, which will allow us to rollback.
The COMMIT statement is used to commit a transaction in PL/SQL, use the:
COMMIT;
The COMMIT statement saves all the changes since the last COMMIT or ROLLBACK. An explicit or implicit request is made to commit a transaction.
Let’s say the following is the INSERT in the table. At the end, execute the COMMIT command if you want to commit the transaction:
INSERT INTO DEPTBUDGET (DEPTID,DEPTNAME,DEPTLOC) VALUES (1, 'Finance', 'NORTH'); INSERT INTO DEPTBUDGET (DEPTID,DEPTNAME,DEPTLOC) VALUES (2, 'Marketing', 'EAST'); INSERT INTO DEPTBUDGET (DEPTID,DEPTNAME,DEPTLOC) VALUES (3, 'Operations', 'WEST'); COMMIT;
A staple in PL SQL interview questions, be prepared to answer this one.
The COLLECTION_IS_NULL exception is raised when a program attempts to apply collection methods other than EXISTS to an uninitialized nested table or varray, or the program attempts to assign values to the elements of an uninitialized nested table or varray.
The dot(.) operator is to be used in PL/SQL to access the fields of a record. This is between the record variable and the field.
Let us take an example of a record. This is how you can declare:
DECLARE TYPE laptop IS RECORD (brand varchar(50), RAM number, SNO number, ); l1 laptop; l1 laptop;
You can now access the fields using the dot(.) operator.
The following is the example wherein we have created user-define records and accesses the fields:
DECLARE TYPE laptop IS RECORD (brand varchar(50), RAM number, SNO number ); lp1 laptop; lp2 laptop; lp3 laptop; BEGIN -- Laptop 1 specification lp1.brand:= 'Dell'; lp1.RAM:= 4; lp1.SNO:= 87667; -- Laptop 2 specification lp2.brand:= 'Lenevo'; lp2.RAM:= 4; lp2.SNO:= 47656; -- Laptop 3 specification lp3.brand:= 'HP'; lp3.RAM:= 8; lp3.SNO:= 98989; -- Laptop 1 record dbms_output.put_line('Laptop 1 Brand = '|| lp1.brand); dbms_output.put_line('Laptop 1 RAM = '|| lp1.RAM); dbms_output.put_line('Laptop 1 SNO = ' || lp1.SNO); -- Laptop 2 record dbms_output.put_line('Laptop 2 Brand = '|| lp2.brand); dbms_output.put_line('Laptop 2 RAM = '|| lp2.RAM); dbms_output.put_line('Laptop 2 SNO = ' || lp2.SNO); -- Laptop 3 record dbms_output.put_line('Laptop 3 Brand = '|| lp3.brand); dbms_output.put_line('Laptop 3 RAM = '|| lp3.RAM); dbms_output.put_line('Laptop 2 SNO = ' || lp3.SNO); END;
The output:
Laptop 1 Brand = Dell Laptop 1 RAM = 4 Laptop 1 SNO = 87667 Laptop 2 Brand = Lenevo Laptop 2 RAM = 4 Laptop 2 SNO = 47656 Laptop 3 Brand = HP Laptop 3 RAM = 8 Laptop 2 SNO = 98989
This question is a regular feature in PL SQL interview questions, be ready to tackle it.
One of the Collection types in PL/SQL is Index-By table. These are also called Associative Arrays. It gets created only in the PL/SQL block. The subscript type for Associative Array is a string or integer.
Associative Array is a set of key-value pairs. The key here can be an Integer or String. The following is the syntax:
TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY subscript_type; name_of_table type_name;
Here, index-by table is the name_of_table. The keys of the subscript_type. The associated values will be of the element_type i.e. NUMBER, etc.
The following is an example:
DECLARE TYPE budget IS TABLE OF NUMBER INDEX BY VARCHAR2(15); dept_budget budget ; dept_name VARCHAR2(20); BEGIN dept_budget ('Operations') := 30000; dept_budget ('Finance') := 25000; dept_budget ('Accounts') := 45000; dept_name := dept_budget.FIRST; WHILE dept_name IS NOT null LOOP dbms_output.put_line ('Budget - ' || dept_name || ' Department = ' || TO_CHAR(dept_budget (dept_name ))); dept_name := dept_budget .NEXT(dept_name ); END LOOP; END; /
The output:
Budget - Accounts Department = 45000 Budget - Finance Department = 25000 Budget - Operations Department = 30000
This is a frequently asked question in PLSQL interview questions.
The TIMESTAMP WITH TIME ZONE datatype is used for date information across geographic regions.
It has a time zone region name or a time zone offset in its value. TIME ZONE offset = difference in hours and minutes between local time and UTC.
A must-know for anyone heading into a PL SQL interview, this question is frequently asked in PL SQL interview questions.
Yes, this is correct. Here, the WHEN clause is used for conditions, which are from top to bottom. The sequence of statements with the WHEN clause whose condition evaluates to TRUE is executed. Only the first statement executes if more than one condition evaluates to TRUE.
The following is the syntax:
CASE WHEN condition_1 THEN statements_1 WHEN condition_2 THEN statements_2 ... WHEN condition_n THEN statements_n [ ELSE else_statements ] END CASE;]
Let us see an example:
DECLARE points number := 100; BEGIN case when points = 20 then dbms_output.put_line('Rank 4'); when points = 50 then dbms_output.put_line('Rank 3'); when points = 75 then dbms_output.put_line('Rank 2'); when points = 100 then dbms_output.put_line('Rank1... Topper'); else dbms_output.put_line('No ranking!'); end case; END; /
The output:
Rank1... Topper
Yes, we can label PL/SQL loops. Here are some key points about labels in a loop:
Let us see an example:
DECLARE i number(1); j number(1); BEGIN << outer >> FOR i IN 1..5 LOOP << inner >> FOR j IN 1..2 LOOP dbms_output.put_line('i = '|| i || ', j = ' || j); END loop inner; END loop outer; END; /
The output:
i = 1, j = 1 i = 1, j = 2 i = 2, j = 1 i = 2, j = 2 i = 3, j = 1 i = 3, j = 2 i = 4, j = 1 i = 4, j = 2 i = 5, j = 1 i = 5, j = 2
The CASE statement in PL/SQL selects one sequence of statements to execute. The statement uses a selector instead multiple Boolean expression.
The following is the syntax:
CASE selector WHEN 'value1' THEN Statement1; WHEN 'value2' THEN Statement2; WHEN 'value3' THEN Statement3; ... ELSE Sn; -- default case END CASE;
The following is an example:
DECLARE points number := 100; BEGIN case points when 20 then dbms_output.put_line('Rank 4'); when 50 then dbms_output.put_line('Rank 3'); when 75 then dbms_output.put_line('Rank 2'); when 100 then dbms_output.put_line('Rank1... Topper'); else dbms_output.put_line('No ranking!'); end case; END; /
The output:
Rank1... Topper
The schema objects created with PL/SQL is Packages, Triggers, Cursors, etc.
Packages are the schema objects that groups logically related PL/SQL types and variables.
Triggers are used to enforce referential integrity, auditing, or even applying security authorizations. It prevents invalid transactions and gather statistics on table access.
Cursors has information about processing the statement. It holds the rows (one or more) returned by a SQL statement.
The %FOUND cursor attribute returns TRUE if record was fetched successfully, FALSE otherwise. Returns TRUE if an INSERT, UPDATE, or DELETE statement affected one or more rows or a SELECT INTO statement returned one or more rows. Otherwise, it returns FALSE.
The %NOTFOUND cursor attribute returns TRUE if record was not fetched successfully, FALSE otherwise. It returns TRUE if an INSERT, UPDATE, or DELETE statement affected no rows, or a SELECT INTO statement returned no rows. Otherwise, it returns FALSE.
Cursor attributes are used like this in PL/SQL:
sql%attribute_name
Above, set any attribute name under “attribute_name”. Here we are learning about %FOUND and %NOTFOUND attribute names.
Let us consider the following table for our example:
<DEPTBUDGET> +-----------+-----------------+---------------+------------------+ | DEPTID | DEPTNAME | DEPTLOC | BUDGET| +----------+------------------+---------------+------------------+ | 1 | Finance | NORTH | 56678 | | 2 | Marketing | EAST | 87687 | | 3 | Operations | WEST | 95979 | | 4 | Technical | SOUTH | 76878 | | 5 | Accounting | NORTHWEST| 86767 | +----------+------------------+------------------+--------------+
Now, we will update the budget of all the departments and then check what all rows got affected using the implicit cursor attribute. With that we will use other attributes as well:
DECLARE rows_count number(2); BEGIN UPDATE deptbudget SET budget = budget + 3000; IF sql%notfound THEN dbms_output.put_line('Nothing selected!'); ELSIF sql%found THEN rows_count := sql%rowcount; dbms_output.put_line( rows_count || ' rows affected! '); END IF; END; /
The output:
5 rows affected!
Since we updated the budget for all the departments above, therefore our table rows would look like this:
<DEPTBUDGET> +-----------+-----------------+---------------+------------------+ | DEPTID | DEPTNAME | DEPTLOC | BUDGET| +----------+------------------+---------------+------------------+ | 1 | Finance | NORTH | 59678 | | 2 | Marketing | EAST | 90687 | | 3 | Operations | WEST | 98979 | | 4 | Technical | SOUTH | 79878 | | 5 | Accounting | NORTHWEST| 89767 | +----------+------------------+------------------+--------------+
To display output, PL/SQL has an in-built package known as DBMS_OUTPUT. With this package, we can also display debugging information.
The following is an example:
DECLARE one varchar2(20) := 'Website'; two varchar2(20) := ' World!'; BEGIN dbms_output.put_line(one || two); END; /
The output:
Website World!
Let us see the subprograms of DBMS_OUTPUT:
Disables message output.
Enables message output. A NULL value of buffer_size represents unlimited buffer size.
Retrieves a single line of buffered information.
Retrieves an array of lines from the buffer.
Puts an end-of-line marker.
Places a partial line in the buffer.
Places a line in the buffer.
A common question in PL SQL interview questions, don't miss this one.
At each iteration in a basic loop in PL/SQL, a sequence of statements is executed. After the execution, the control resumes at the top of the loop. The EXIT statement is a must in the execution part to exit from the loop. The keyword LOOP begins the execution. It ends with the keyword END LOOP.
The following is an example:
DECLARE a number := 2; BEGIN LOOP dbms_output.put_line(a); a := a + 2; IF a > 10 THEN exit; END IF; END LOOP; dbms_output.put_line('After exit, the value of a = ' || a); END; /
The output:
2 4 6 8 10 After exit, the value of a = 12
A subprogram in PL/SQL is either a procedure or a function that can be invoked repeatedly. Each subprogram has a name and a list of parameters. It performs a specific task and the following two subprograms exist in PL/SQL:
The following are the parts of a subprogram:
It contains declarations of cursors, constants, variables, exceptions, etc.
Had statements that perform actions.
The code that handles run-time errors.
The following is the syntax to create a subprogram (procedure):
CREATE [OR REPLACE] PROCEDURE name_of_procedure [(parameter_name [IN | OUT | IN OUT] type [, ...])] {IS | AS} BEGIN < procedure_body > END name_of_procedure;
Here,
Here is an example of a subprogram (procedure):
CREATE OR REPLACE PROCEDURE one AS BEGIN dbms_output.put_line('Welcome!'); END; /
The output displays the procedure created successfully:
Procedure created.
The default value is NULL. PL/SQL assigns NULL as the default value of a variable. To assign another value, you can use the DEFAULT keyword or the assignment operator. Let us see them one by one.Initialization with DEFAULT keyword:
DECLARE msg varchar2(15) DEFAULT 'House'; BEGIN dbms_output.put_line(msg); END; /
The output:
House
Initialization with assignment operator:
DECLARE device varchar2(11) := 'Laptop'; BEGIN dbms_output.put_line(device); END; /
The output:
Laptop
PL/SQL has subtypes of datatypes. This means subtype is a subset of a datatype. The following are the types and subtypes of Numeric data type in PL/SQL
Signed integer in range -2,147,483,648 through 2,147,483,647, represented in 32 bits
Signed integer in range -2,147,483,648 through 2,147,483,647, represented in 32 bits
Single-precision IEEE 754-format floating-point number
Double-precision IEEE 754-format floating-point number
ANSI specific fixed-point type with maximum precision of 38 decimal digits
IBM specific fixed-point type with maximum precision of 38 decimal digits
Floating type with maximum precision of 38 decimal digits
ANSI specific floating-point type with maximum precision of 126 binary digits (approximately 38 decimal digits)
ANSI and IBM specific integer type with maximum precision of 38 decimal digits
Floating-point type with maximum precision of 63 binary digits (approximately 18 decimal digits)