bing
Flat 10% & upto 50% off + Free additional Courses. Hurry up!

PL/SQL Language Elements

 

Assignment Statement

An assignment statement sets the current value of a variable, field, parameter, or element. The statement consists of an assignment target followed by the assignment operator and an expression. When the statement is executed, the expression is evaluated and the resulting value is stored in the target.

Syntax:-

assignement statement

 

AUTONOMOUS_TRANSACTION Pragma

The AUTONOMOUS_TRANSACTION pragma changes the way a subprogram works within a transaction. A subprogram marked with this pragma can do SQL operations and commit or roll back those operations, without committing or rolling back the data in the main transaction.

Syntax:-

autonomous transaction

 

Block Declaration

The basic program unit in PL/SQL is the block. A PL/SQL block is defined by the keywords DECLARE, BEGIN, EXCEPTION, and END. These keywords partition the block into a declarative part, an executable part, and an exception-handling part. Only the executable part is required. You can nest a block within another block wherever you can place an executable statement.

Syntax:-

block

 

CASE Statement

The CASE statement chooses from a sequence of conditions, and executes a corresponding statement. The CASE statement evaluates a single expression and compares it against several potential values, or evaluates multiple Boolean expressions and chooses the first one that is TRUE.

Syntax:-

case

 

CLOSE Statement

The CLOSE statement indicates that you are finished fetching from a cursor or cursor variable, and that the resources held by the cursor can be reused.

Syntax:-

close

 

Collection Definition

A collection is an ordered group of elements, all of the same type. For example, the grades for a class of students. Each element has a unique subscript that determines its position in the collection. PL/SQL offers three kinds of collections: associative arrays, nested tables, and varrays.

Syntax:-

collection

 

Collection Methods

A collection method is a built-in function or procedure that operates on collections and is called using dot notation. You can use the methods EXISTS, COUNT, LIMIT, FIRST, LAST, PRIOR, NEXT, EXTEND, TRIM, and DELETE to manage collections whose size is unknown or varies.

Syntax:-

collection methods

 

Comments

Comments let you include arbitrary text within your code to explain what the code does. PL/SQL supports two comment styles: single-line and multi-line. A double hyphen (–) anywhere on a line (except within a character literal) turns the rest of the line into a comment. Multi-line comments begin with a slash-asterisk (/*) and end with an asterisk-slash (*/).

Syntax:-

comment

COMMIT Statement

The COMMIT statement makes permanent any changes made to the database during the current transaction. A commit also makes the changes visible to other users. The SQL COMMIT statement can be embedded as static SQL in PL/SQL.

 

Constant and Variable Declaration

You can declare constants and variables in the declarative part of any PL/SQL block, subprogram, or package. Declarations allocate storage for a value, specify its datatype, and specify a name that you can reference. Declarations can also assign an initial value and impose the NOT NULL constraint.

Syntax:-

constant and variable declaration

 

Cursor Attributes

Every explicit cursor and cursor variable has four attributes: %FOUND, %ISOPEN %NOTFOUND, and %ROWCOUNT. When appended to the cursor or cursor variable, these attributes return useful information about the execution of a data manipulation statement.

The implicit cursor SQL has additional attributes, %BULK_ROWCOUNT and %BULK_EXCEPTIONS.

Syntax:-

cursor attribute

 

Cursor Variables

To execute a multi-row query, Oracle opens an unnamed work area that stores processing information. You can access this area through an explicit cursor, which names the work area, or through a cursor variable, which points to the work area. To create cursor variables, you define a REF CURSOR type, then declare cursor variables of that type.

Syntax:-

cursor variable

 

Cursor Declaration

To execute a multi-row query, Oracle opens an unnamed work area that stores processing information.

Syntax:-

cursor declaration

DELETE Statement

The DELETE statement removes entire rows of data from a specified table or view.

Syntax:-

delete

 

EXCEPTION_INIT Pragma

The pragma EXCEPTION_INIT associates an exception name with an Oracle error number. You can intercept any ORA- error and write a specific handler for it instead of using the OTHERS handler.

Syntax:-

exception init

 

Exception Definition

An exception is a runtime error or warning condition, which can be predefined or user-defined. Predefined exceptions are raised implicitly (automatically) by the runtime system. User-defined exceptions must be raised explicitly by RAISE statements. To handle raised exceptions, you write separate routines called exception handlers.

Syntax:-

exception definition

 

EXECUTE IMMEDIATE Statement

The EXECUTE IMMEDIATE statement executes a dynamic SQL statement or anonymous PL/SQL block. You can use it to issue SQL statements that cannot be represented directly in PL/SQL, or to build up statements where you do not know all the table names, WHERE clauses, and so on in advance.

Syntax:-

 

execute immediate

 

EXIT Statement

The EXIT statement breaks out of a loop. The EXIT statement has two forms: the unconditional EXIT and the conditional EXIT WHEN.

Syntax:-

exit

Expression Definition

An expression is an arbitrarily complex combination of variables, constants, literals, operators, and function calls. The simplest expression is a single variable.

Syntax:-

expression definition

FETCH Statement

The FETCH statement retrieves rows of data from the result set of a multi-row query.

Syntax:- 

fetch

 

FORALL Statement

The FORALL statement issues a series of static or dynamic DML statements, usually much faster than an equivalent FOR loop. It requires some setup code, because each iteration of the loop must use values from one or more collections in its VALUES or WHERE clauses.

 

Function Declaration

A function is a subprogram that can take parameters and return a single value. A function has two parts: the specification and the body. The specification begins with the keyword FUNCTION and ends with the RETURN clause, which specifies the datatype of the return value. Parameter declarations are optional.

Functions that take no parameters are written without parentheses. The function body begins with the keyword IS (or AS) and ends with the keyword END followed by an optional function name. The function body has three parts: an optional declarative part, an executable part, and an optional exception-handling part.

Syntax:- 

function declaration

 

GOTO Statement

The GOTO statement branches unconditionally to a statement label or block label. The label must be unique within its scope and must precede an executable statement or a PL/SQL block. The GOTO statement transfers control to the labelled statement or block.

 

IF Statement

The IF statement executes or skips a sequence of statements, depending on the value of a Boolean expression.

 

INSERT Statement

The INSERT statement adds one or more new rows of data to a database table.

 

Literal Declaration

A literal is an explicit numeric, character, string, or Boolean value not represented by an identifier. The numeric literal 135 and the string literal ‘hello world’ are examples.

Syntax:-

literal

LOCK TABLE Statement

The LOCK TABLE statement locks entire database tables in a specified lock mode. That enables you to share or deny access to tables while maintaining their integrity.

 

LOOP Statements

LOOP statements execute a sequence of statements multiple times. The LOOP and END LOOP keywords enclose the statements. PL/SQL provides four kinds of loop statements: basic loop, WHILE loop, FOR loop, and cursor FOR loop.

Syntax:-

loop

 

MERGE Statement

The MERGE statement inserts some rows and updates others in a single operation. The decision about whether to update or insert into the target table is based upon a join condition: rows already in the target table that match the join condition are updated; otherwise, a row is inserted using values from a separate sub query.

 

NULL Statement

The NULL statement is a no-op (no operation); it passes control to the next statement without doing anything. In the body of an IF-THEN clause, a loop, or a procedure, the NULL statement serves as a placeholder.

 

Object Type Declaration

An object type is a user-defined composite datatype that encapsulates a data structure along with the functions and procedures needed to manipulate the data. The variables that form the data structure are called attributes. The functions and procedures that characterize the behavior of the object type are called methods. A special kind of method called the constructor creates a new instance of the object type and fills in its attributes.

Object types must be created through SQL and stored in an Oracle database, where they can be shared by many programs. When you define an object type using the CREATE TYPE statement, you create an abstract template for some real-world object. The template specifies the attributes and behaviors the object needs in the application environment.

 

OPEN Statement

The OPEN statement executes the query associated with a cursor. It allocates database resources to process the query and identifies the result set — the rows that match the query conditions. The cursor is positioned before the first row in the result set.

Syntax:-

open statement

 

OPEN-FOR Statement

The OPEN-FOR statement executes the query associated with a cursor variable. It allocates database resources to process the query and identifies the result set – the rows that meet the query conditions. The cursor variable is positioned before the first row in the result set.

 

Package Declaration

A package is a schema object that groups logically related PL/SQL types, items, and subprograms. Use packages when writing a set of related subprograms that form an application programming interface (API) that you or others might reuse. Packages have two parts: a specification and a body.

 

Procedure Declaration

A procedure is a subprogram that can take parameters and be called. Generally, you use a procedure to perform an action. A procedure has two parts: the specification and the body. The specification (spec for short) begins with the keyword PROCEDURE and ends with the procedure name or a parameter list. Parameter declarations are optional.

Procedures that take no parameters are written without parentheses. The procedure body begins with the keyword IS (or AS) and ends with the keyword END followed by an optional procedure name. The procedure body has three parts: an optional declarative part, an executable part, and an optional exception-handling part.

The declarative part contains declarations of types, cursors, constants, variables, exceptions, and subprograms. These items are local and cease to exist when you exit the procedure. The executable part contains

statements that assign values, control execution, and manipulate Oracle data. The exception-handling part contains handlers that deal with exceptions raised during execution.

 

RAISE Statement

The RAISE statement stops normal execution of a PL/SQL block or subprogram and transfers control to an exception handler. RAISE statements can raise predefined exceptions, such as ZERO_DIVIDE or NO_DATA_FOUND, or user-defined exceptions whose names you decide.

Syntax:-

raise

Record Definition

Records are composite variables that can store data values of different types, similar to a struct type in C, C++, or Java. In PL/SQL records are useful for holding data from table rows, or certain columns from table rows. For ease of maintenance, you can declare variables as table%ROWTYPE or cursor%ROWTYPE instead of creating new record types.

 

RETURN Statement

The RETURN statement immediately completes the execution of a subprogram and returns control to the caller. Execution resumes with the statement following the subprogram call. In a function, the RETURN statement also sets the function identifier to the return value.

Syntax:-

return

RETURNING INTO Clause

The returning clause specifies the values return from DELETE, EXECUTE IMMEDIATE, INSERT, and UPDATE statements. You can retrieve the column values into individual variables or into collections. You cannot use the RETURNING clause for remote or parallel deletes. If the statement does not affect any rows, the values of the variables specified in the RETURNING clause are undefined.

 

ROLLBACK Statement

The ROLLBACK statement is the inverse of the COMMIT statement. It undoes some or all database changes made during the current transaction.

 

%ROWTYPE Attribute

The %ROWTYPE attribute provides a record type that represents a row in a database table. The record can store an entire row of data selected from the table or fetched from a cursor or cursor variable. Variables declared using %ROWTYPE are treated like those declared using a datatype name. You can use the %ROWTYPE attribute in variable declarations as a datatype specifier.

 

SAVEPOINT Statement

The SAVEPOINT statement names and marks the current point in the processing of a transaction. With the ROLLBACK TO statement, savepoints undo parts of a transaction instead of the whole transaction.

 

SELECT INTO Statement

The SELECT INTO statement retrieves data from one or more database tables, and assigns the selected values to variables or collections.

In its default usage (SELECT … INTO), this statement retrieves one or more columns from a single row. In its bulk usage (SELECT … BULK COLLECT INTO), this statement retrieves an entire result set at once.

Syntax:-

select into statement

 

SERIALLY_REUSABLE Pragma

The pragma SERIALLY_REUSABLE indicates that the package state is needed only for the duration of one call to the server. An example could be an OCI call to the database or a stored procedure call through a database link. After this call, the storage for the package variables can be reused, reducing the memory overhead for long-running sessions.

 

SET TRANSACTION Statement

The SET TRANSACTION statement begins a read-only or read-write transaction, establishes an isolation level, or assigns the current transaction to a specified rollback segment. Read-only transactions are useful for running multiple queries against one or more tables while other users update the same tables.

 

SQL Cursor

Oracle implicitly opens a cursor to process each SQL statement not associated with an explicit cursor. In PL/SQL, you can refer to the most recent implicit cursor as the SQL cursor, which always has the attributes %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT. They provide information about the execution of data manipulation

statements. The SQL cursor has additional attributes, %BULK_ROWCOUNT and %BULK_EXCEPTIONS, designed for use with the FORALL statement.

 

SQLCODE Function

The function SQLCODE returns the number code of the most recent exception. For internal exceptions, SQLCODE returns the number of the associated Oracle error.

The number that SQLCODE returns is negative unless the Oracle error is no data found, in which case SQLCODE returns +100. For user-defined exceptions, SQLCODE returns +1, or a value you assign if the exception is associated with an Oracle error number through pragma EXCEPTION_INIT.

 

SQLERRM Function

The function SQLERRM returns the error message associated with its error-number argument. If the argument is omitted, it returns the error message associated with the current value of SQLCODE. SQLERRM with no argument is useful only in an exception handler. Outside a handler, SQLERRM with no argument always returns the normal, successful completion message. For internal exceptions, SQLERRM returns the message associated with the Oracle error that occurred. The message begins with the Oracle error code.

For user-defined exceptions, SQLERRM returns the message user-defined exception, unless you used the pragma EXCEPTION_INIT to associate the exception with an Oracle error number, in which case SQLERRM returns the corresponding error message.

Syntax:-

sqlerrm

 

%TYPE Attribute

The %TYPE attribute lets use the datatype of a field, record, nested table, database column, or variable in your own declarations, rather than hardcoding the type names.

You can use the %TYPE attribute as a datatype specifier when declaring constants, variables, fields, and parameters. If the types that you reference change, your declarations are automatically updated. This technique saves you from making code changes when, for example, the length of a VARCHAR2 column is increased.

 

UPDATE Statement

The UPDATE statement changes the values of specified columns in one or more rows in a table or view.

Syntax:-

update

"0 Responses on PL/SQL Language Elements"

Training in Cities

Bangalore, Hyderabad, Chennai, Delhi, Kolkata, UK, London, Chicago, San Francisco, Dallas, Washington, New York, Orlando, Boston

100% Secure Payments. All major credit & debit cards accepted Or Pay by Paypal.

top

Sales Offer

  • To avail this offer, enroll before 05th December 2016.
  • This offer cannot be combined with any other offer.
  • This offer is valid on selected courses only.
  • Please use coupon codes mentioned below to avail the offer
offer-june

Sign Up or Login to view the Free PL/SQL Language Elements.