Here’s a simple PL/SQL program

• This program adds two variables and prints the total
• –Three sections are demonstrated in this program with indentations for clarity
•
• DECLARE
• Var1 number := 35;
• Var2 number := 60;
• –Var3 will be used to save the result
• var3 number;
• BEGIN
• Var3 := var1 + var2;
• DBMS_OUTPUT.PUT_LINE(‘ The result of addition is ‘);
• dbms_output.put_line( var3 );
• End;

Arithmetic Operators Used in Statements

Let us discuss about Running PL/SQL Blocks in SQL*PLUS

• In PL/SQL mode semicolon (;) can be used multiple times in single program
• Editing of instruction same as in the SQL statements
• End; appears as the termination of the program
• / can initiate execution of the program

Comment Statements

• Block of comments are delimited with /* */
• /* < comment that spans more than one line of code > */
• Single comment line starts with 2 hyphens
– comment on a single line

Displaying PL/SQL Output in SQL*Plus

• Normally PL/SQL is used with other Oracle utilities such as forms or reports
• PL/SQL in SQL*Plus
• Command to activate memory buffer in SQL*Plus to enable output from PL/SQL programs:
SQL > SET SERVEROUTPUT ON

Here’s the brief about PL/SQL Data Types in Declare Section

• Scalar
• References a single value
• Composite
• References a data structure
• Reference
• References a specific database item

Scalar Data Types

• Database scalar data types:
• VARCHAR2
• CHAR
• DATE
• LONG
• NUMBER
• Non-database scalar data types:
• Integers: BINARY_INTEGER, INTEGER, INT, SMALLINT
• Decimal numbers: DEC, DECIMAL, DOUBLE, PRECISION, NUMERIC, REAL
• BOOLEAN

Composite Data Types

• Reference multiple data elements, such as a record
• Types:
• RECORD
• TABLE
• VARRAY

Reference Data Types

(Refers to data in tables)
• Reference a database item
• Assume data type of item
• %TYPE: assumes data type of field
• %ROWTYPE: assumes data type of entire row

Let’s take look at of PL/SQL Block Structure

• Block is the most basic unit in PL/SQL
• Usually, PL/SQL blocks combine statements that represent a single logical task
• Different tasks are within a single program can be separated into blocks
• This makes easier to understand and maintain the logic of the program

PL/SQL Program Structure

DECLARE
Variable declarations
BEGIN
Program statements
EXCEPTION
Error-handling statements
END;

Declaring PL/SQL Variables

• PL/SQL is a strongly-typed language (it contains definitions of variables and constants)
• All variables must be declared prior to use
• Syntax for declaring a variable:
• variable_name data_type_declaration;
• Example:
• current_s_id NUMBER(6);