Block Types Database Management System

Block Types Database Management System

A PL/SOL program comprises one or more blocks. These blocks can be entirely separate or nested one within another. The basic units (procedures and functions, also known as subprograms, and anonymous blocks) that make up a PL/SOL program are logical blocks, which can contain any number of nested Subblocks. Therefore, one block can represent a small part of another block, which in turn can be part of the whole unit of Code.

Anonymous Blocks

Anonymous blocks are unnamed blocks. They are declared at the point in an application where they are to be executed and are passed to the PL/SQL engine for execution at run time. You can embed an anonymous block within a precompiler program and within SQL*Plus or Server Manager. Triggers in Oracle Developer Components consist of Such blocks.

Subprograms

Subprograms are named PL/SQL blocks that can accept parameters and can be invoked. You can declare them either as procedures or as functions. Generally use a procedure to performance action and a function to Compute a value. Stored procedures and functions have many benefits in addition to modularizing application development:

Easy maintenance that enables you to modify:

  • Routines online without interfering with other users
  • One routine to affect multiple applications
  • One routine to eliminate duplicate testing

Improved data security and integrity by doing the following:

  • Control indirect access to database objects from nonprivileged users with security privileges.
  • Ensure that related actions are performed together, or not at all, by funneling activity for related tables through a single path

Improved performance that allows you to do the following:

  • Avoid reparsing for multiple users by exploiting the shared SOL area
  • Avoid PL/SOL parsing at run time by parsing at compile time
  • Reduce the number of calls to the database and decrease network traffic by bundling Commands

Improved code clarity : Using appropriate identifier names to describe the action of the routines reduces the need for comments and enhances the clarity of the code.

Types of Variables

All PL/SQL variables have a data type, which specifies a storage format, constraints, and valid range of values. PL/SOL supports four data type categories-scalar, composite, reference, and LOB (large object)-that you can use for declaring variables, Constants, and pointers.

  • Scalar data types hold a single value. The main data types are those that Correspond to Column types in Oracle server tables; PL/SQL also supports Boolean variables.
  • Composite data types, such as records, allow groups of fields to be defined and manipulated in PL/SOL blocks.

PL/SQL Block Structure

PL/SOL is a block-structured language, meaning that programs can be divided into logical blocks. A PL/SQL block consists of up to three sections: declarative (optional), executable (required), and exception handling (optional). The following table describes the three sections:
Executing Statements and PL/SQL Blocks
DECLARE
var variable VARCHAR2(5);
BEGIN
SELECT column name
INTO var variable
FROM table name;
EXCEPTION
WHEN exception name THEN

END;
Executing Statements and PL/SQL Blocks

  • Place a semicolon (;) at the end of a SOL statement or PL/SQL control statement.
  • When the block is executed successfully, without unhandled errors or compile errors, the message output should be as follows:

PL/SQL procedure successfully completed.

Block Types

1. Anonymous
(DECLARE)
BEGIN
-statements
EXCEPTION
END;

2. Procedure PROCEDURE name
IS
BEGIN
-statements
(EXCEPTION)
END;

3. Function
FUNCTION name
RETURN datatype
IS
BEGIN
-statements
RETURN value;
(EXCEPTION)
END;

Declaring PL/SQL Variables

You must declare all PL/SQL identifiers in the declaration section before referencing them in the PL/SQL block. You have the option to assign an initial value to a variable. You do not need to assign a value to a variable in order to declare it. If you refer to other variables in a declaration, you must be sure to declare them separately in a previous Statement.

In the syntax:

identifier is the name of the variable.
CONSTANT Constrains the variable so that its value cannot change, Constants must be initialized.

data type is a Scalar, composite, reference, or LOB data type. (This Course Covers only Scalar, composite, and LOB data types.)

NOT NULL Constrains the variable so that it must contain a value. (NOT NULL variables must be initialized.)

expr is any PL/SQL expression that can be a literal expression, another variable, or an expression involving operators and functions.

Guidelines for Declaring PL/SQL Variables

  • Follow naming conventions.
  • Initialize variables designated as NOT NULL and CONSTANT.
  • Declare One identifier per line.
  • Initialize identifiers by using the assignment

operator (:=) or the DEFAULT reserved word.
identifier:= expr;

Guidelines for Declaring PL/SQL Variables

Here are some guidelines to follow while declaring PL/SQL variables:

  • Name the identifier according to the same rules used for SOL objects.
  • You can use naming conventions-for example, Varname to represent a variable and c name to representa Constant variable.
  • If you use the NOT NULL constraint, you must assign a value.
  • Declaring only one identifier per line makes code easier to read and maintain.
  • In Constant declarations, the keyword CONSTANT must precede the type specifier. The following declaration names a constant of NUMBER subtype REAL and assigns the value of 50000 to the Constant. A constant must be initialized in its declaration; otherwise, you get a compilation error when the declaration is elaborated (compiled). var sal CONSTANT REAL := 50000.00;
  • Initialize the variable to an expression with the assignment operator (:=) or, equivalently, with the DEFAULT reserved word. If you do not assign an initial value, the new variable contains NULL by default until you assign a value later. To assign or reassign a value to a variable, you write a PL/SQL assignment statement. You must explicitly name the variable to receive the new value to the left of the assignment operator (:=). It is good programming practice to initialize all variables.

Naming Rules

Two variables can have the same name, provided they are in different blocks.

The variable name (identifier) should not be the same as the name of table columns used in the block.

DECLARE
emp_id NUMBER(6);
BEGIN SELECT emp_id
INTO emp_id
FROM emp
WHERE last_name = “Kapadia’;
END;
Note: The names of the variables must not be longer than 30 characters. The first character must be a letter; the remaining characters can be letters, numbers, or special Symbols.

Variable initialization and Keywords
Assignment operator (:=)
DEFAULT keyword
NOT NULL constraint
Syntax:
Examples:
Identifer = expr;
var hiredate := 01-JAN-2003′;
var_ ename := “Pankaj;

Base Scalar Data Types

 

Variable initialization and Keywords (continued)

The following example computes a 10% bonus for the emp with the EMP ID 176 and assigns the computed value to the war bonus variable. This is done using the INTO clause.

DECLARE

var bonus NUMBER(8,2);

BEGIN

SELECT Sal * 0.10

INTO var bonus

FROM emp

WHERE emp_id= 176;

END;

/

Then you can use the variable war bonus in another Computation or insert its value into a database table.

PL/SQL BlockSyntax and Guidelines

Because PL/SQL is an extension of SQL, the general Syntax rules that apply to SOL also apply to the PL/SQL language.

>> A line of PL/SQL text contains groups of characters known as lexical units, which can be classified as follows:

  • Delimiters (simple and compound symbols)
  • Identifiers, which include reserved words
  • Literals
  • Comments

>> To improve readability, you can separate lexical units by spaces. In fact, you must separate adjacent identifiers by a space or punctuation.

>> You cannot embed spaces in lexical units except for string literals and comments.

>> Statements can be split across lines, but keywords must not be split.

Delimiters

Delimiters are simple or compound symbols that have special meaning to PL/SOL.

Symbol Meaning

+ Addition operator

– Subtraction/negation operator

* Multiplication operator

/ Division operator

— Relational operator

@ Remote access indicator

; Statement terminator

Symbol Meaning

<> Relational operator

= Relational operator

|| Concatenation operator

– Single line comment indicator

/* Beginning comment delimiter

*/ Ending Comment delimiter

:- Assignment operator

Identifiers

Identifiers are used to name PL/SQL program items and units, which include Constants, variables, exceptions, cursors, Cursor variables, Subprograms, and packages.

Identifiers can contain up to 30 characters, but they must start with an alphabetic character.

  • Do not choose the same name for the identifier as the name of Columns in a table used in the block. If PL/SOL identifiers are in the same SQL Statements and have the same name as a Column, then Oracle assumes that it is the Column that is being referenced.
  • Reserved words should be written in uppercase to promote readability.
  • An identifier consists of a letter, optionally followed by more letters, numerals, dollar signs, underscores, and number signs. Other characters such as hyphens, slashes, and spaces are illegal, as the following examples show:

dots&dashes — ilegal ampersand
debit-amount — illegal hyphen
on/off-illegal slash
user id – illegal space
money$$$tree, SN## try again are examples that show that adjoining and trailing dollar signs, under scores, and number signs are allowed.

Literals

– Character and date literals must be enclosed in single quotation marks.

– Numbers can be simple values or scientific notation.

>> A slash (/) runs the PL/SOL block in a script file

A literal is an explicit numeric, character, String, or Boolean value that is not represented by an identifier.

>> Character literals include all the printable characters in the PL/SQL characterset: letters, numerals, spaces, and special symbols.

>> Numeric literals can be represented either by a simple value (for example, -32.5) or by a scientific notation (for example, 2E5, meaning 2 (10 to the power of 5) = 200000).

A PL/SOL program is terminated and executed by a slash (/) on a line by itself.

Commenting Code

Comment Code to document each phase and to assist debugging. Comment the PL/SOL code with two dashes (-) if the comment is on a single line, or enclose the comment between the symbols / and */if the comment spans several lines. Comments are strictly informational and do not enforce any conditions or behavior on behavioral logic or data. Well-placed comments are extremely valuable for code readability and future Code maintenance.

SQLFunctions in PL/SQL 

Most of the functions available in SOL are also valid in PL/SOL expressions: Single-row number functions

  • Single-row character functions
  • Data type conversion functions
  • Date functions
  • Timestamp functions
  • GREATEST, LEAST
  • Miscellaneous functions

The following functions are not available in procedural statements:

  • DECODE.
  • Group functions: AVG, MIN, MAX, COUNT, SUM, STDDEV, and VARIANCE. Group functions apply to groups of rows in a table and therefore are available only in SQL statements in a PL/SQL block.

SOL. Functions in PL/SQL: Examples

  • Build the mailing list for a company.
  • Convert the emp name to lowercase.

var_mailing_address := var_name || CHR(10) ||

var_address||CHR(10) || ||var_state || ||

CHR(10) || ||var_zip;

warename := LOWER(warename);

SQLFunctions in PL/SOL:

Most of the SQL functions can be used in PL/SQL. These built-in functions help you to manipulate data they fall into the following categories:

  • Number
  • Character
  • Conversion
  • Date
  • Miscellaneous

The function examples in the slide are defined as follows:

Build the mailing address for a company.

Convert the name to lowercase.

CHR is the SQL function that converts an ASCII code to its corresponding character; 10 is the code for line feed.

Data Type Conversion

Convert data to comparable data types.

Mixed data types can result in an error and affect performance. Conversion functions:

  • TO CHAR
  • TO DATE
  • TO NUMBER

DECLARE

var_date DATE := TO_DATE(“12-JAN-2004”, “DD-MON-YYYY”);
BEGIN

Data Type Conversion

PL/SQL attempts to convert data types dynamically if they are mixed in a statement. For example, if you assign a NUMBER value to a CHAR variable, then PL/SQL dynamically translates the number into a character representation, so that it can be stored in the CHAR variable. The reverse situation also applies, provided that the character expression represents a numeric value.

if they are compatible, you can also assign characters to DATE variables and vice versa.

Within an expression, you should make sure that data types are the same. If mixed data types occur in an expression, you should use the appropriate conversion function to convert the data.

Syntax

TO CHAR (value, fmt)

TODATE (value, fmt)

TO NUMBER (value, fmt)

where: value is a character string, number, or date.

fmt is the format model used to Converta Value.