ORACLE PL/SQL Basic 1

ORACLE PL/SQL

a PL/SQL block has three basic parts: a declarative part (DECLARE), an executable part (BEGIN .. END), and an exception-handling(EXCEPTION) part that handles error conditions.

Oracle Database PL/SQL User's Guide and Reference

Using DBMS_OUTPUT to Display Output

-- enable SERVEROUTPUT in SQL*Plus to display with DBMS_OUTPUT.PUT_LINE

-- this enables SERVEROUTPUT for this SQL*Plus session only

SET SERVEROUTPUT ON

DECLARE

answer VARCHAR2(20); -- declare a variable

BEGIN

-- assign a value to a variable

answer := 'Maybe';

-- use PUT_LINE to display data from the PL/SQL block

DBMS_OUTPUT.PUT_LINE( 'The answer is: ' || answer );

END;

/

Simple PL/SQL Block

-- the following is an optional declarative part

DECLARE

monthly_salary NUMBER(6);

number_of_days_worked NUMBER(2);

pay_per_day NUMBER(6,2);

-- the following is the executable part, from BEGIN to END

BEGIN

monthly_salary := 2290;

number_of_days_worked := 21;

pay_per_day := monthly_salary/number_of_days_worked;

-- the following displays output from the PL/SQL block

DBMS_OUTPUT.PUT_LINE('The pay per day is ' || TO_CHAR(pay_per_day));

-- the following is an optional exception part that handles errors

EXCEPTION

WHEN ZERO_DIVIDE THEN

pay_per_day := 0;

END;

/

Using Comments

DECLARE -- Declare variables here.

monthly_salary NUMBER(6); -- This is the monthly salary.

number_of_days_worked NUMBER(2); -- This is the days in one month.

pay_per_day NUMBER(6,2); -- Calculate this value.

BEGIN

-- First assign values to the variables.

monthly_salary := 2290;

number_of_days_worked := 21;

-- Now calculate the value on the following line.

pay_per_day := monthly_salary/number_of_days_worked;

-- the following displays output from the PL/SQL block

DBMS_OUTPUT.PUT_LINE('The pay per day is ' || TO_CHAR(pay_per_day));

EXCEPTION

/* This is a simple example of an exeception handler to trap division by zero.

In actual practice, it would be best to check whether a variable is

zero before using it as a divisor. */

WHEN ZERO_DIVIDE THEN

pay_per_day := 0; -- set to 0 if divisor equals 0

END;

/

Declaring Variables in PL/SQL

DECLARE -- declare the variables in this section

last_name VARCHAR2(30);

first_name VARCHAR2(25);

employee_id NUMBER(6);

active_employee BOOLEAN;

monthly_salary NUMBER(6);

number_of_days_worked NUMBER(2);

pay_per_day NUMBER(6,2);

avg_days_worked_month CONSTANT NUMBER(2) := 21; -- a constant variable

BEGIN

NULL; -- NULL statement does nothing, allows this block to executed and tested

END;

/

Assigning Values to Variables With the Assignment Operator

DECLARE -- declare and assiging variables

wages NUMBER(6,2);

hours_worked NUMBER := 40;

hourly_salary NUMBER := 22.50;

bonus NUMBER := 150;

country VARCHAR2(128);

counter NUMBER := 0;

done BOOLEAN := FALSE;

valid_id BOOLEAN;

BEGIN

wages := (hours_worked * hourly_salary) + bonus; -- compute wages

country := 'France'; -- assign a string literal

country := UPPER('Canada'); -- assign an uppercase string literal

done := (counter > 100); -- assign a BOOLEAN, in this case FALSE

valid_id := TRUE; -- assign a BOOLEAN

END;

/

Using Literals

DECLARE -- declare and assign variables

number1 PLS_INTEGER := 32000; -- numeric literal

number2 NUMBER(8,3);

char1 VARCHAR2(1) := 'x'; -- character literal

char2 VARCHAR2(1000);

boolean BOOLEAN := TRUE; -- BOOLEAN literal

date1 DATE := '11-AUG-2005'; -- DATE literal

time1 TIMESTAMP;

time2 TIMESTAMP WITH TIME ZONE;

BEGIN

number2 := 3.125346e3; -- numeric literal

number2 := -8300.00; -- numeric literal

number2 := -14; -- numeric literal

char2 := q'!I'm writing an example string.!'; -- string literal

char2 := 'I''m writing an example string.'; -- need two single quotes here

time1 := '11-AUG-2005 11:01:01 PM'; -- TIMESTAMP literal

time2 := '11-AUG-2005 09:26:56.66 PM +02:00';

END;

/

Using DEFAULT and NOT NULL

DECLARE -- declare and assign variables

last_name VARCHAR2(30);

first_name VARCHAR2(25);

employee_id NUMBER(6);

active_employee BOOLEAN NOT NULL := TRUE; -- value cannot be NULL

monthly_salary NUMBER(6) NOT NULL := 2000; -- value cannot be NULL

number_of_days_worked NUMBER(2);

pay_per_day NUMBER(6,2);

employee_count NUMBER(6) := 0;

avg_days_worked_month NUMBER(2) DEFAULT 21; -- assign a default value

BEGIN

NULL; -- NULL statement does nothing, allows this block to executed and tested

END;

/