PL/SQL – Program Structure

PL/SQL is a block-structured language and programs are divided and written in logical blocks of code. The  basic parts of PL/ SQL blocks are Declarative block ( optional) Executable block  (Mandatory ) Exception Handling block (optional)  Declarative block It is used to define user defined types, variable which can be used in the executable part … Continue reading PL/SQL – Program Structure


PL/SQL – Literals

PL/SQL Literals. A Literal is an explicit numeric, string or Boolean values not represented by an identifiers. For examples, 999,  TRUE, NULL, 'learndba' are literal types number, Boolean or string. PL/SQL literals are case sensitive. PL/SQL supports following literals. S.No Literal Type & Example 1 Numeric Literals 050 78 -14 0 +32767 6.6667 0.0 -12.0 … Continue reading PL/SQL – Literals

PL/SQL – Operators

An operator is reserved word that manipulates individual data items and returns a result. The data items are called operands or arguments. Operators are represented by special characters. For example, the Addition operator is represented by plus (+) and subtraction operator represented by minus (-) etc... There are two general classes of operators: Unary operators … Continue reading PL/SQL – Operators

PL/SQL – Packages

A package is a schema object that groups logically related PL/SQL types, variables, and subprograms. Package is compiled and stored in database that can be used later. PL/SQL package basically have two components. Package Specification Package Body Package Specification : Specification is the interface to the package. It consists of a declaration of all the … Continue reading PL/SQL – Packages

PL/SQL Records

A PL/SQL record is a composite data structure that is a group of related data stored in fields. Each field in the PL/SQL record has its own name and data type. A record is a group of related data items stored in fields, each with its own name and datatype. Records are composed of a … Continue reading PL/SQL Records

PL/SQL Collections

A collection is an ordered group of elements, all of the same data type and are called elements. Each element is addressed by a unique subscript that represents its position in the collection. You access each element by its unique subscript. PL/SQL has three collection types, Associative array (or index-by table) Nested table Variable-size array … Continue reading PL/SQL Collections

PL/SQL Cursors

A cursor is a pointer to a private SQL area that stores information about processing a specific SELECT or DML statement. A cursor holds the rows (one or more) returned by a SQL statement. The set of rows the cursor holds is referred to as the active set. There are two types of cursors: Implicit … Continue reading PL/SQL Cursors

PL/SQL Exceptions

An exception is a PL/SQL error that is raised during program execution. PL/SQL supports programmers to catch errors using Exception block in the program and appropriate action is taken against the error condition. Exception types There are three types of exceptions: Predefined exceptions are error conditions that are defined by PL/SQL. Non-predefined exceptions include any … Continue reading PL/SQL Exceptions

PL/SQL Triggers

A trigger is a named PL/SQL unit that is stored in the database and can be invoked repeatedly when some events occur.A trigger is a named PL/SQL unit that is stored in the database and can be invoked repeatedly when some events occur. You can enable and disable a trigger, but you cannot explicitly invoke … Continue reading PL/SQL Triggers

PL/SQL User defined Functions

A function is a set of PL/SQL statements you can call by name. Functions are very similar to procedures, except that a function returns a value to the environment in which it is called. User defined functions can be used as part of a SQL expression. The data type of the return values must be … Continue reading PL/SQL User defined Functions

PL/SQL Procedures

A procedure is a group of PL/SQL statements that you can call by name which performs one or more specific task. A procedure has a header and a body. The header consists of the name of the procedure and the parameters or variables passed to the procedure. The body consists or declaration section, execution section … Continue reading PL/SQL Procedures

PL/SQL – Loops

A LOOP statement is an iterative control statement that allows you to executes a sequence of statements multiple times. PL/SQL provides these loop statements. Basic loop FOR loop WHILE loop Cursor FOR loop Loop Control Statements To prevent an infinite loop, at least one statement must transfer control outside the loop. The statements that can … Continue reading PL/SQL – Loops

PL/SQL Conditional Statements

PL/SQL supports the conditional statements statements like other programming languages such as C++, java etc. The conditional selection statements, IF and CASE, run different statements for different data values. The IF statement either runs or skips a sequence of one or more statements, depending on a condition. The IF statement has these forms: IF THEN … Continue reading PL/SQL Conditional Statements

PL/SQL Arrays

Like other programming languages, PL/SQL provides a data structure called VARRAY, which can store collection of elements of the same type. Varray stands for variable-size array. When you create varrays you must provide the maximum size for them. All varrays consist of contiguous memory locations. The lowest address corresponds to the first element and the … Continue reading PL/SQL Arrays

PL/SQL – Strings

What is a String? A string, also referred to as character data, is actually a sequence of characters with an optional size specification. The characters could be numeric, letters, blank, special characters or a combination of all. There are three kinds of strings in PL/SQL: Fixed-length strings - The string is right-padded with spaces to … Continue reading PL/SQL – Strings