Oracle SQL and PL/SQL

Introduction to Oracle SQL and PL/SQL

10 Sep 2023
Agenda

Overview
Configure Oracle SQL Developer
Oracle SQL Basics
Data Definition Language(DDL)
Data Manipulation Language(DML)
Oracle Built-In Functions
Oracle PL/SQL Introduction
Practice
Q&A
 Oracle SQL Overview
Structured Query Language (SQL) is the set of statements with which all programs and users access data
in an Oracle Database. The strengths of SQL provide benefits for all types of users, including application
programmers, database administrators, managers, and end users. The purpose of SQL is to provide an
interface to a relational database such as Oracle Database, and all SQL statements are instructions to the
database.
Among the features of SQL are the following:
• It processes sets of data as groups rather than as individual units.
• It provides automatic navigation to the data.
• It uses statements that are complex and powerful individually, and that therefore stand alone. Flow-
control statements, such as begin-end, if-then-else, loops, and exception condition handling, were
initially not part of SQL and the SQL standard, but they can now be found in ISO/IEC 9075-4 -
Persistent Stored Modules (SQL/PSM). The PL/SQL extension to Oracle SQL is similar to PSM.
SQL provides statements for a variety of tasks, including:
• Querying data
• Inserting, updating, and deleting rows in a table
• Creating, replacing, altering, and dropping objects
• Controlling access to the database and its objects
• Guaranteeing database consistency and integrity
 Oracle Built-In Functions
There are two types of functions in Oracle.
• Single Row Functions: Single row or Scalar functions return a value for every row that is processed in a query.
There are four types of single row functions. They are:
1) Numeric Functions: These are functions that accept numeric input and return numeric values.
Commonly used are ABS(x), CEIL(x), FLOOR(x),ROUND(x, y)
2) Character or Text Functions: These are functions that accept character input and can return both character
and number values.
Commonly used Text functions: LOWER (string_value), UPPER (string_value), INITCAP
(string_value), LTRIM (string_value, trim_text), RTRIM (string_value, trim_text), SUBSTR
(string_value, m, n), LENGTH (string_value), LPAD (string_value, n, pad_value), RPAD (string_value,
n, pad_value)
3) Date Functions: These are functions that take values that are of datatype DATE as input and return values
of datatype DATE, except for the MONTHS_BETWEEN function, which returns a number.
Commonly used Date Functions: ADD_MONTHS (date, n), MONTHS_BETWEEN (x1, x2),
NEXT_DAY (x, week_day), LAST_DAY (x), SYSDATE
4) Conversion Functions: These are functions that help us to convert a value in one form to another form. For
Example: a null value into an actual value, or a value from one datatype to another datatype like NVL,
TO_CHAR, TO_NUMBER, TO_DATE etc.
Commonly used Conversion Functions: TO_CHAR (x [,y]), TO_DATE (x [, date_format]), NVL (x, y),
DECODE (a, b, c, d, e, default_value)
 Oracle Built-In Functions
• Group Functions (Aggregate functions): Aggregate functions return a single result row based on groups of
rows, rather than on single rows. Aggregate functions can appear in select lists and in ORDER BY and
HAVING clauses. They are commonly used with the GROUP BY clause in a SELECT statement, where
Oracle Database divides the rows of a queried table or view into groups. In a query containing a GROUP BY
clause, the elements of the select list can be aggregate functions, GROUP BY expressions, constants, or
expressions involving one of these. Oracle applies the aggregate functions to each group of rows and returns a
single result row for each group.

DISTINCT and UNIQUE, which are synonymous, cause an aggregate function to consider only distinct
values of the argument expression. The syntax diagrams for aggregate functions in this chapter use the
keyword DISTINCT for simplicity.

Other commonly used aggregate functions MIN, MAX, SUM, AVG, COUNT, VARIANCE, and STDDEV
 Oracle PL/SQL Introduction
• PL/SQL is Oracle's procedural language extension to SQL, the non-procedural relational database
language.

• With PL/SQL, you can use SQL statements to manipulate ORACLE data and the flow of control
statements to process the data. Moreover, you can declare constants and variables, define subprograms
(procedures and functions), and trap runtime errors. Thus, PL/SQL combines the data manipulating
power of SQL with the data processing power of procedural languages.

• PL/SQL is just like any other programming language, it has syntax and rules that determine how
programming statements work together. PL/SQL is not a stand-alone programming language.PL/SQL is
a part of the Oracle RDBMS, and it can reside in two environments, the client and the server.

• PL/SQL blocks can be divided into two groups:

Named and Anonymous.
Named blocks are used when creating subroutines. These subroutines are procedures, functions, and
packages. The subroutines can be stored in the database and referenced by their names later on.
In addition, subroutines can be defined within the anonymous PL/SQL block. Anonymous PL/SQL
blocks do not have names. As a result, they cannot be stored in the database and referenced later.
 Oracle PL/SQL Introduction
• PL/SQL Block Structure
PL/SQL blocks contain three sections

Declare section
Executable section and
Exception-handling section.

The executable section is the only mandatory section of the block.

Both the declaration and exception-handling sections are optional.
PL/SQL anonymous block has the following structure:

DECLARE
Declaration statements
BEGIN
Executable statements
EXCETION
Exception-handling statements
END ;
 Oracle PL/SQL Introduction
• Declare section
The declaration section is the first section of the PL/SQL block.
It contains definitions of PL/SQL identifiers such as variables, constants, cursors and so on.
Example
DECLARE
v_first_name VARCHAR2(35) ;
v_last_name VARCHAR2(35) ;
v_counter NUMBER := 0 ;
• Executable section
The executable section is the next section of the PL/SQL block.
This section contains executable statements that allow you to manipulate the variables that have been
declared in the declaration section.
BEGIN
SELECT first_name, last_name
INTO v_first_name, v_last_name
FROM student
WHERE student_id = 123 ;
DBMS_OUTPUT.PUT_LINE
(‘Student name :’ || v_first_name ||‘ ’|| v_last_name);
END;
 Oracle PL/SQL Introduction
• Exception-handling section
The exception-handling section is the last section of the PL/SQL block.
This section contains statements that are executed when a runtime error occurs within a block.
Runtime errors occur while the program is running and cannot be detected by the PL/SQL compiler.

EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE
(‘ There is no student with student id 123 ’);
END;
• Built-In Exceptions
NO_DATA_FOUND - This exception is raised when a SELECT INTO statement does not return any
rows.
TOO_MANY_ROWS - This exception is raised when a SELECT INTO statement returns more than one
row since a SELECT INTO can return only a single row.
VALUE_ERROR - This exception is raised when a data conversion is not possible between the variables
and values being assigned. If there is a mismatch of data-type size the exception is raised.
OTHERS - The OTHERS clause is used to trap all remaining exceptions that have not been handled by
Named System Exceptions or Named Programmer-Defined Exceptions.
 Oracle PL/SQL Introduction
• Named programmer-defined exception
Sometimes, it is necessary for programmers to name and trap their own exceptions - ones that aren't
defined already by PL/SQL. These are called Named Programmer-Defined Exceptions.

Syntax for the Named Programmer-Defined Exception

DECLARE
[declaration_section]
exception_name EXCEPTION;

BEGIN
executable_section
RAISE exception_name;

EXCEPTION
WHEN exception_name THEN
[statements]
WHEN OTHERS THEN
[statements]
END;
Q&A
Thank you