Scribd
Upload
16 views

Oracle PLSQL Programming1

The document provides an overview of PL/SQL programming including PL/SQL block structure, declaring variables, scalar variable declarations, the %TYPE attribute, bind variables, referencing non-PL/SQL variables, DBMS_OUTPUT.PUT_LINE, and practice examples.

Uploaded by

gunarathneisuri11
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd
16 views

Oracle PLSQL Programming1

The document provides an overview of PL/SQL programming including PL/SQL block structure, declaring variables, scalar variable declarations, the %TYPE attribute, bind variables, referencing non-PL/SQL variables, DBMS_OUTPUT.PUT_LINE, and practice examples.

Uploaded by

gunarathneisuri11
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd
You are on page 1/ 22

Overview of PL/SQL

Programming
About PL/SQL

• PL/SQL (Procedural Language/Structured Query


Language ) is the procedural extension to SQL with
design features of programming languages.

• Data manipulation and query statements of SQL are


included within procedural units of code.
Declaring Variables
PL/SQL Block Structure

DECLARE (Optional)
Variables, Cursors, User-defined exceptions
BEGIN (Mandatory)
- SQL statements
- PL/SQL statements
EXCEPTION (Optional)
Actions to perform when errors occur
END; (Mandatory)
Cont…
Section Description Inclusion

Declarative Contains all variables, constants, Optional


cursors, and user-defined exception
that are referenced in the
executable and declarative sections

Executable Contains SQL statements to manipulate Mandatory


data in the database and PL/SQL
statements to manipulate data in block

Exception Specifies the actions to perform when Optional


Handling errors and abnormal conditions arise
in the executable section
Executing Statements and PL/SQL
Blocks
DECLARE
v_variable VARCHAR2 (5);
BEGIN
SELECT
column_name INTO
v_variable FROM
table_name;
EXCEPTION
WHEN
exception_name THEN
Block Types
Anonymous Procedure Functions

[DECLARE] PROCEDURE name FUNCTION name


IS | AS RETURN data type
IS

BEGIN BEGIN BEGIN


-- Statements -- Statements -- Statements
RETURN value;
[EXCEPTION] [EXCEPTION] [EXCEPTION]

END; END; END;


Declaring PL/SQL Variables

Syntax :
identifier [CONSTANT] datatype [NOT NULL] [:= | DEFAULT expr];

Examples :
DECLARE
v_hiredate DATE;
v_deptno NUMBER (2) NOT NULL := 10 ;
v_location VARCHAR2 (13) := ‘NIBM’ ;
c_comm CONSTANT NUMBER := 1400;
Scalar Variable Declarations

• Examples :

DECLARE
v_job VARCHAR2 (9) ;
v_count BINARY_INTEGER := 0 ;
v_total_sal NUMBER (9,2) := 0 ;
v_orderdate DATE := SYSDATE + 7 ;
c_tax_rate CONSTANT NUMBER (3,2) := 8.25;
v_valid BOOLEAN NOT NULL := TRUE;
Cont…
• v_job : Variable to store an employee job title.
• v_count : Variable to count the iterations of a loop
and initialized to 0.
• v_total_sal : Variable to accumulate the total
salary for a department and initialized to 0.
• v_orderdate : Variable to store the ship date of an
order and initialized to one week from today.
• c_tax_rate : A constant variable for the tax rate,
which never changes throughout the PL/SQL
block.
• v_valid : Flag to indicate whether a piece of data
is valid or invalid and initialized to TRUE.
The %TYPE
Attribute
• Declare a variable according to :
- A database column definition
- Another previously declared variable

• Prefix %TYPE with :


- The database table and column
- The previously declared variable name
Cont…
• Syntax
identifier Table.Column_Name%TYPE;

• Examples:
….
v_name Emp.last_name%TYPE;
v_balance NUMBER (7,2);
v_min_balance v_balance%TYPE :=
…. 10;
Bind Variables

• A bind variable is a variable that you declare


in a host environment.
• Used to pass run-time values, either number
or character, into or out of one or more
PL/SQL programs.
• To declare a bind variable in the iSQL*Plus
environment , use the command VARIABLE.
• Syntax
VARIABLE variable_name
data_type
Cont…
• Example

VARIABLE g_salary
NUMBER; BEGIN
SELECT salary
INTO :g_salary
FROM employee
WHERE emp_id = 178;
END;
/
PRINT g_salary;
Referencing Non-PL/SQL Variables

• To reference host variables, you must prefix the


references with a colon (:) to distinguish them from
declared PL/SQL variables.
• Store the monthly salary into a iSQL*/Plus
host variable.

:g_monthly_sal := v_sal / 12;


DBMS_OUTPUT.PUT_LINE

• An Oracle-supplied packaged procedure


• An alternative for displaying data from
a PL/SQL block
• Must be enabled in iSQL*Plus with

SET SERVEROUTPUT ON
Example :
SET SERVEROUTPUT ON
VARIABL g_monthly_sal
E DEFINE NUMBER; p_annual_sal
= 5000;
DECLARE
v_sal NUMBER (9,2) := &p_annual_sal;
BEGIN
:g_monthly_sal := v_sal / 12;

DBMS_OUTPUT.PUT_LINE (‘The monthly salary is ‘ ||


TO_CHAR (v_sal));

END;
/
Practice 01
1. Evaluate each of the following declarations.
Determine which of them are not legal and explain
why.
a) DECLARE v_id NUMBER (4);

b) DECLARE v_x, v_y, v_z VARCHAR2 (10);

c) DECLARE v_birthdate DATE NOT NULL;

d) DECLARE v_in_stock BOOLEAN := 1;


2. Create an anonymous block to output the phrase
“My PL/SQL Block Work” to the screen.
G_MESSAGE
My PL/SQL Block Work

3. Create a block that declares two variables as v_char


and v_num. Assign the value of these PL/SQL
variables to iSQL*Plus host variables and print the
result of the PL/SQL variables to the screen.
G_CHAR
42 is the answer

G_NUM
42
Answer - 02
VARIABLE g_message VARCHAR2 (30);
BEGIN
:g_message := ‘My PL/SQL Block Works’;
END;
/
PRINT g_message;
Alternate Solution
SET SERVEROUTPUT ON
BEGIN
DBMS_OUTPUT.PUT_LINE (‘My PL/SQL Block Works’);
END;
/
Answer – 03
VARIABLE g_char VARCHAR2 (30);
VARIABLE g_num NUMBER;
DECLARE
v_char VARCHAR2 (30);
v_num NUMBER (11,2);
BEGIN
v_char := ‘42 is the answer’;
v_num := TO_NUMBER (SUBSTR
(v_char,1,2));
:g_char := v_char;
:g_num := v_num;
END;
/
PRINT g_char;
PRINT
Answer – 03 (Method 2)
set serveroutput on

--Method 2

DECLARE
v_char VARCHAR2(30);
v_num NUMBER;

BEGIN
v_char := '42 is the Answer';
v_num := TO_NUMBER(substr(v_char,1,2));

DBMS_OUTPUT.PUT_LINE(v_char);
DBMS_OUTPUT.PUT_LINE(v_num);

END;

You might also like