Objects of type tables are called PL/SQL tables that are modeled as database table. We can also say that PL/SQL tables are a way to providing arrays. Arrays are like temporary tables in memory that are processed very quickly. PL/SQL tables are used to move bulk data. They simplifies moving collections of data.
A WHEN clause specifies the condition that must be true for the trigger to be triggered.
Following conditions are true for the Commit statement:
- Other users can see the data changes made by the transaction.
- The locks acquired by the transaction are released.
- The work done by the transaction becomes permanent.
No. PL/SQL doesn’t support the data definition commands like CREATE.
Oracle uses workspaces to execute the SQL commands. When Oracle processes a SQL command, it opens an area in the memory called Private SQL Area. This area is identified by the cursor. It allows programmers to name this area and access it?s information.
There are two types of cursors in PL/SQL.
- Implicit cursor, and
- explicit cursor
A list of predefined exceptions in PL/SQL:
- TIMEOUT _ON_RESOURCE
PL/SQL packages have the following two parts:
Specification part: It specifies the part where the interface to the application is defined.
Body part: This part specifies where the implementation of the specification is defined.
There are two types of datatypes in PL/SQL:
- Scalar datatypes Example are NUMBER, VARCHAR2, DATE, CHAR, LONG, BOOLEAN etc.
- Composite datatypes Example are RECORD, TABLE etc.
ALTER TRIGGER update_salary DISABLE;
The Rollback statement is issued when the transaction ends. Following conditions are true for a Rollback statement:
- The work done in a transition is undone as if it was never issued.
- All locks acquired by transaction are released.
A function returns a value while a stored procedure doesn?t return a value.
Implicit cursor is implicitly declared by Oracle. This is a cursor to all the DDL and DML commands that return only one row.
Explicit cursor is created for queries returning multiple rows.
A trigger is a PL/SQL program which is stored in the database. It is executed immediately before or after the execution of INSERT, UPDATE, and DELETE commands.
The DROP PACKAGE command is used to delete a package.
There are two way to execute a stored procedure.
From the SQL prompt, write EXECUTE or EXEC followed by procedure_name.
EXECUTE or [EXEC] procedure_name;
Simply use the procedure name
PL/SQL uses BLOCK structure as its basic structure. Each PL/SQL program consists of SQL and PL/SQL statement which form a PL/SQL block.
PL/SQL block contains 3 sections.
- The Declaration Section (optional)
- The Execution Section (mandatory)
- The Exception handling Section (optional)
DROP TRIGGER command.
With SAVEPOINT, only part of transaction can be undone.
Whenever an Error occurs Exception arises. Error is a bug whereas exception is a warning or error condition.
The cursor attribute SQL%ROWCOUNT will return the number of rows that are processed by a SQL statement.
There are 12 types of triggers in PL/SQL that contains the combination of BEFORE, AFTER, ROW, TABLE, INSERT, UPDATE, DELETE and ALL keywords.
- BEFORE ALL ROW INSERT
- AFTER ALL ROW INSERT
- BEFORE INSERT
- AFTER INSERT etc.
Modularity, extensibility, reusability, Maintainability and one time compilation.
Function: The main purpose of a PL/SQL function is generally to compute and return a single value. A function has a return type in its specification and must return a value specified in that type.
Procedure: A procedure does not have a return type and should not return any value but it can have a return statement that simply stops its execution and returns to the caller. A procedure is used to return multiple values otherwise it is generally similar to a function.
Package: A package is schema object which groups logically related PL/SQL types , items and subprograms. You can also say that it is a group of functions, procedure, variables and record type statement. It provides modularity, due to this facility it aids application development. It is used to hide information from unauthorized users.
Table columns are referred as THEN.column_name and NOW.column_name.
For INSERT related triggers, NOW.column_name values are available only.
For DELETE related triggers, THEN.column_name values are available only.
For UPDATE related triggers, both Table columns are available.
Mutating table error is occurred when a trigger tries to update a row that it is currently using. It is fixed by using views or temporary tables.
Faster access of data blocks in the table.
It returns the Boolean value TRUE if at least one row was processed.
PL/SQL stands for procedural language extension to SQL. It supports procedural features of programming language and SQL both. It was developed by Oracle Corporation in early of 90’s to enhance the capabilities of SQL.
A trigger is automatically executed without any action required by the user, while, a stored procedure is explicitly invoked by the user.
%ISOPEN: it checks whether the cursor is open or not.
%ROWCOUNT: returns the number of rows affected by DML operations: INSERT,DELETE,UPDATE,SELECT.
%FOUND: it checks whether cursor has fetched any row. If yes – TRUE.
%NOTFOUND: it checks whether cursor has fetched any row. If no – TRUE.
Exception is an error handling part of PL/SQL. There are two type of exceptions: pre_defined exception and user_defined exception.
A stored procedure is a sequence of statement or a named PL/SQL block which performs one or more specific functions. It is similar to a procedure in other programming languages. It is stored in the database and can be repeatedly executed. It is stored as schema object. It can be nested, invoked and parameterized.
Consistency simply means that each user sees the consistent view of the data.
Consider an example: there are two users A and B. A transfers money to B’s account. Here the changes are updated in A’s account (debit) but until it will be updated to B’s account (credit), till then other users can’t see the debit of A’s account. After the debit of A and credit of B, one can see the updates. That?s consistency.
- Zero_error etc.
It returns the Boolean value TRUE if no rows were processed.
PL/SQL is an extension of SQL. While SQL is non-procedural, PL/SQL is a procedural language designed by Oracle. It is invented to overcome the limitations of SQL.
A list of some notable characteristics:
- PL/SQL is a block-structured language.
- It is portable to all environments that support Oracle.
- PL/SQL is integrated with the Oracle data dictionary.
- Stored procedures help better sharing of application
When a trigger is associated to a view, the base table triggers are normally enabled.
A syntax error can be easily detected by a PL/SQL compiler. For example: incorrect spelling etc. while, a runtime error is handled with the help of exception-handling section in a PL/SQL block. For example: SELECT INTO statement, which does not return any rows.
Greeting := ‘Hello’ || ‘World’;
- Stored procedures and functions
A cursor is a temporary work area created in a system memory when an SQL statement is executed.
A cursor contains information on a select statement and the row of data accessed by it. This temporary work area stores the data retrieved from the database and manipulate this data. A cursor can hold more than one row, but can process only one row at a time. Cursor are required to process rows individually for queries.
You can declare the User defined exceptions under the DECLARE section, with the keyword EXCEPTION.
A PL/SQL package can be specified as a file that groups functions, cursors, stored procedures, and variables in one place.