PLSQL Fundamentals

From Training Material
Jump to navigation Jump to search


title
PL/SQL Fundamentals
author
Lukasz Sokolowski

PL/SQL Fundamentals

PL/SQL Fundamentals Training Materials 

IMPORTANT NOTE for trainers only: 
Please DO NOT change these materials, especially the EXERCISES, without direct permission from: GRZEGORZ PRUSZCZYNSKI or LUKASZ SOKOLOWSKI.

Introduction ⌘

  • Aims and Objectives
  • Course Schedule
  • Introductions
  • Pre-requisites
  • Responsibilities

What is PL/SQL? ⌘

  • What is PL/SQL?
  • Why Use PL/SQL?
  • Block Structure
  • Displaying a Message
  • Sample Code
  • Setting SERVEROUTPUT
  • Update Example, Style Guide

What is PL/SQL? ⌘

  • Developed in 1991 by Oracle Corporation as a procedural language extension to SQL
  • Integrates seamlessly with SQL
  • Powerful language to construct the data access layer
  • Rich procedural extensions help in translating business logic within the Oracle Database

Why Use PL/SQL? ⌘

  • Procedural Language-Structured Query Language (PL/SQL)
  • Part of the Oracle Database product, no separate installation is required
  • Translates business logic in the db and exposes the program interface layer to the application
  • SQL is purely a data access language that directly interacts with the db
  • PL/SQL is a programming language
    • Multiple SQLs and procedural statements can be grouped in a program unit
    • Portable between Oracle Databases
  • The built-in db optimizer refactors the code to improve the execution performance

The advantages of PL/SQL as a language ⌘

  • Supports all types of SQL statements, data types, static SQL, and dynamic SQL
  • Runs on all platforms supported by the Oracle Database
  • Performance can be improved by the use of bind variables in direct SQL queries
  • Supports the object-oriented model of the Oracle Database
  • Apps increase scalability by allowing multiple users to invoke the same program unit

PL/SQL accomplishments ⌘

  • Not used to build user interfaces
  • For robust, secure, and portable interface layers
    • Can be exposed to a high-level programming language
  • Procedural: list of operations that can execute sequentially
    • SQL just declares, PL/SQL adds selective and iterative constructs
  • Db programming: server side programs run faster than the middle-tier
    • Easy code maintenance, needs to be re-written less frequently
  • Integral: apps developers can easily integrate it with other high-level programming interfaces (Java, C++, or .NET)
    • Procedures or subprograms can be invoked from client programs as executable statements

PL/SQL program fundamental questions ⌘

  • How do we handle an SQL execution in the program?
  • How do we handle the procedural execution flow in the program?
  • Does the program handle the exceptions?
  • How do we trace and debug the program code?

Block structure ⌘

PlsqlBlockStr.jpg

Block structure Expl ⌘

PL/SQL program has 4 sections

  • Each section carries a specific objective
  • Must exist in the same sequence in a program

Header: optional, required for named blocks (procedures, functions, and triggers)

  • Contains the program name, the program's owner, and the parameter specification

Declaration: optional, declares local variables, cursors, and local subprograms

  • Keywords - DECLARE; can be skipped if no variables

Execution: procedural, comprises the main program body and an exception

  • Keywords - BEGIN, END; must contain at least one executable statement
  • Statements are parsed and sequentially executed by the engine

Exception: optional, for various errors, occurable in the program leading to abnormal termination

  • Keywords - EXCEPTION; control lands into it and the appropriate exception handler is executed

PL/SQL program categories ⌘

Block is the elementary unit of a program

  • Groups a set of procedural statements

Number of sections defines type of the unit

  • Anonymous: simplest; has no name, has DECLARE-BEGIN-END skeleton
    • Current execution as standalone block or embedded locally within another unit
    • Cannot be stored in the db
  • Named: routine stored persistently in db as a schema object
    • Invoked from db session or by another unit
    • Can be a function, procedure, trigger, or package
  • Nested: block within another block, forms a nested block structure

Anonymous unit, example ⌘

/* Enable the Serveroutput to display block messages */
-- SET SERVEROUTPUT ON;

/* Start the PL/SQL block */
DECLARE
/* Declare a local variable and initialize with a default value */
   MY_STR VARCHAR2(80) := 'Yes, I do like PL/SQL';
BEGIN
/* Print the result */
   DBMS_OUTPUT.PUT_LINE( 'Do you like it? ' || MY_STR );
END;
/
-----------------------------------
-- Do you like it? Yes, I do like PL/SQL

-- PL/SQL procedure successfully completed.

SQL Tools ⌘

  • Objectives
  • SQL Developer
  • SQL Developer - Connection
  • Viewing Table Information
  • Using SQL, SQL Developer - Query
  • SQL*Plus Login
  • Direct Connection
  • Using SQL*Plus
  • Ending the Session
  • SQL*Plus Commands
  • SQL*Plus Environment
  • SQL*Plus Prompt
  • Finding Information about Tables
  • Getting Help
  • Using SQL Files
  • iSQL*Plus, Entity Models
  • Course Tables Handout
  • SQL Statement Syntax
  • SQL*Plus Commands

Variables ⌘

  • Variables
  • Datatypes
  • Setting Variables
  • Constants
  • Local and Global Variables
  • %Type Variables

Variables, Datatypes, Setting ⌘

DECLARE
  part_number       NUMBER(6);     -- SQL data type, NULL
  part_name         VARCHAR2(20) := 'Gauss Rifle';  -- SQL data type, initialized with value
  in_stock          BOOLEAN;       -- PL/SQL-only data type, NULL(unknown)
  part_price        NUMBER(6,2);   -- SQL data type
  part_description  VARCHAR2(50);  -- SQL data type
BEGIN
  NULL;
END;
/

Constants ⌘

DECLARE
  credit_limit     CONSTANT REAL    := 5000.00;  -- SQL data type
  max_days_in_year CONSTANT INTEGER := 366;      -- SQL data type
  urban_legend     CONSTANT BOOLEAN := FALSE;    -- PL/SQL-only data type
BEGIN
  NULL;
END;
/

%TYPE Attribute ⌘

  • Syntax - referencing_item referenced_item%TYPE;
  • Examples
DECLARE
  ---- Variable of Same Type as Column
  emp_name  emp.ename%TYPE;
  name     VARCHAR(25) NOT NULL := 'Bond';
  ---- Variable of Same Type as Another Variable
  surname  name%TYPE := 'Dick';
BEGIN
  DBMS_OUTPUT.PUT_LINE('Full name =' || emp_name);
  DBMS_OUTPUT.PUT_LINE('name=' || name);
  DBMS_OUTPUT.PUT_LINE('surname=' || surname);
END;
/

Variables Scope ⌘

-- https://docs.oracle.com/en/database/oracle/oracle-database/18/lnpls/plsql-language-fundamentals.html#GUID-2FC17012-FC99-4614-90DD-ADC99F2EDBE9

Exercise ⌘

Prepare necessary declarations for the draft or real invoice, print all of them to the screen(like below)

  • variables, initialised (item price, item name)
  • vars, not initialised (invoice's unique number, invoice type(draft or real), final cost, items total, date of issue, amount, VAT rate, Items)
  • constants (tax rate, item number)
  • inheriting type od data (Items from Final Cost)
/*
---- HEADERS ----
Date of Issue:      present_year-present_month-present_day
Invoice number:     INV201
Invoice type:       draft

---- INVOICE ITEMS ----
Item Number     Item Name       Amount      Price       Tax Rate       Final Cost
190.228.09      Gaus Rifle      20          800         0,20           16000

SUMMARY:
VAT rate:       20%
Items:          16000
Items total:    19200
(...)
*/

SELECT INTO example ⌘

DECLARE
  bonus   NUMBER(8,2);
BEGIN
  SELECT sal * 0.10 INTO bonus
  FROM emp
  WHERE id = 7900;

  DBMS_OUTPUT.PUT_LINE('bonus = ' || TO_CHAR(bonus));
END;
/

%ROWTYPE attribute ⌘

---- Record variable that represents a row of the table "dept", assigns values to its fields, and prints them
DECLARE
  dept_rec dept%ROWTYPE;
BEGIN
  -- Assign values to fields:  
  dept_rec.deptno   := 50;
  dept_rec.dname    := 'Administration';
  dept_rec.loc      := 'HongKong';
 
  -- Print fields: 
  DBMS_OUTPUT.PUT_LINE('dept_id:   ' || dept_rec.deptno);
  DBMS_OUTPUT.PUT_LINE('dept_name: ' || dept_rec.dname);
  DBMS_OUTPUT.PUT_LINE('loc:    ' || dept_rec.loc);
END;
/

Exercise ⌘

When the TOOSK's subordinate was hired?

  • Use SELECT INTO
  • Show the result like below
/*
Subordinate name = THATCHER
Hiredate = 80/12/17
*/


Conditional Statement ⌘

  • IF Statement
  • Case Statement
  • More here
    • -- https://docs.oracle.com/en/database/oracle/oracle-database/18/lnpls/plsql-control-statements.html#GUID-B7DD4E62-3ED2-41E9-AAE5-90A78788BB31

IF Statement ⌘

  • IF THEN
  • IF THEN ELSE
  • IF THEN ELSIF

IF THEN ELSE example ⌘

-- IF statement example
DECLARE
  PROCEDURE p (
    sales  NUMBER,
    quota  NUMBER,
    emp_id NUMBER
  )
  IS
    bonus  NUMBER := 0;
  BEGIN
    IF sales > (quota + 200) THEN
      bonus := (sales - quota)/4;
    ELSE
      bonus := 50;
    END IF;
 
    DBMS_OUTPUT.PUT_LINE('bonus = ' || bonus);
 
    UPDATE emp
    SET sal = sal + bonus 
    WHERE id = emp_id;
  END p;
BEGIN
  p(10100, 10000, 7369);
  p(10500, 10000, 121);
END;
/

Exercise ⌘

Reuse variables exercise and apply this logic

  • When invoice is a draft set new variable 'Invoice Proforma Reference' with invoice number
  • Otherwise set new variable 'Invoice Reference'

Case Statement ⌘

  • Appropriate when a different action is to be taken for each alternative
  • Two forms
    • Simple, evaluates a single expression and compares it to several potential values
    • Searched, evaluates multiple conditions and chooses the first one that is true

Simple Case example ⌘

DECLARE
  sal_grade NUMBER;
BEGIN
  sal_grade := 2;

  CASE sal_grade
    WHEN 1 THEN DBMS_OUTPUT.PUT_LINE('Poor');
    WHEN 2 THEN DBMS_OUTPUT.PUT_LINE('Peanuts');
    WHEN 3 THEN DBMS_OUTPUT.PUT_LINE('Still Peanuts');
    WHEN 4 THEN DBMS_OUTPUT.PUT_LINE('Pineapples');
    WHEN 5 THEN DBMS_OUTPUT.PUT_LINE('Coconuts');
    ELSE DBMS_OUTPUT.PUT_LINE('No such grade');
  END CASE;
END;
/

Searched Case example ⌘

DECLARE
PROCEDURE print_bool (pb BOOLEAN)
AS
BEGIN
  DBMS_OUTPUT.PUT_LINE (
    CASE
      WHEN pb IS NULL THEN 'Unknown'
      WHEN pb THEN 'True'
      WHEN NOT pb THEN 'False'
    END
  );
END;

BEGIN
  print_bool(TRUE);
  print_bool(FALSE);
  print_bool(NULL);
END;

Exercise ⌘

Reuse invoice reference exercise and add this logic

  • One Franchisee can have 1-3 instances of invoicing tool
  • For each instance set 3 new variables
    • 'tool_key_var1' - set it with a string 'tool_key1_' concatenated with Franchsiee's Name(new var)
    • 'tool_secret_var1' - set it with 'tool_secret1_' with F's name
    • 'tool_acc_var1' - set 'tool_account1_' with F's name
  • Check which instance will be used to issue invoice (new var)
    • Default should say 'No such tool exists. Please contact the administrator.'

Trapping Errors ⌘

  • Exception
  • System-defined
  • User-defined
  • Propagation

Exception ⌘

  • Unusual and unexpected flow during runtime might result in abnormal termination
    • Such errors must be trapped and handled in the EXCEPTION section
  • Exceptions suppress the abnormal termination with an alternative and secured action
  • Important step in db programming
  • Unhandled exceptions
    • Can result in unplanned application outages
    • Impact business continuity
    • Frustrate end users
  • System-defined(implicitly raised) and user-defined(declared and raised in unit)
  • Utility functions - SQLCODE and SQLERRM
    • Retrieve the error code and message for the most recent exception

System-defined ⌘

  • Defined and maintained implicitly by the Oracle Database, STANDARD package
  • db picks up the appropriate exception from the available list
  • Associated with a negative error code (except 1 to 100) and short name (for handlers)

Using No Data Found ⌘

-- Example includes a SELECT statement to show details of employee 8265
-- Raises NO_DATA_FOUND - employee id 8265 doesn't exist
DECLARE
   V_ENAME VARCHAR2 (100);
   V_HIRED DATE;
   V_EMPNO NUMBER := 8265;
BEGIN
/*Write a SELECT statement */
   SELECT ENAME, HIREDATE
   INTO V_ENAME, V_HIRED
   FROM EMP
   WHERE ID = V_EMPNO;
END;
/
-----------------------------------
/*
DECLARE
(...)
ERROR at line 7:
ORA-01403: no data found
ORA-06512: at line 7
*/

Solution - EXCEPTION with handler ⌘

(...)
EXCEPTION
   /* Exception Handler */
   WHEN NO_DATA_FOUND THEN
   /* Display an informative message */
   DBMS_OUTPUT.PUT_LINE ('No Employee exists with the id ' || V_EMPNO);
END
/
-----------------------------------
/*
No Employee exists with the id 8265

PL/SQL procedure successfully completed.
*/

Exercise ⌘

When did Blake come to the company?

Commonly used internal exceptions ⌘

Error, ORA-_ Named exception Raised when
00001 DUP_VAL_ON_INDEX Duplicate value exists
01001 INVALID_CURSOR Cursor is invalid
01012 NOT_LOGGED_ON User is not logged in
01017 LOGIN_DENIED System error occurred
01403 NO_DATA_FOUND The query returns no data
01422 TOO_MANY_ROWS A single row query returns multiple rows
01476 ZERO_DIVIDE An attempt was made to divide a number by zero
01722 INVALID_NUMBER The number is invalid
06504 ROWTYPE_MISMATCH Mismatch occurred in row type
06511 CURSOR_ALREADY_OPEN Cursor is already open
06531 COLLECTION_IS_NULL Working with NULL collection
06532 SUBSCRIPT_OUTSIDE_LIMIT Collection index out of range
06533 SUBSCRIPT_BEYOND_COUNT Collection index out of count

User exceptions ⌘

  • Has specified name and associated error code
  • Raises statements in line with the implementation logic
  • Standardizes the exception handling to alter the program execution logic
  • RAISE statement in the BEGIN..END section

User exceptions, 3 ways of declaring ⌘

EXCEPTION type variable in the declaration section

  • Raise it explicitly in the program body (RAISE statement)
  • Handle it in the EXCEPTION section, no error code is involved

EXCEPTION variable associated with standard error number

  • using PRAGMA EXCEPTION_INIT
  • PRAGMA - directive to the compiler, manipulates the behavior during compilation (not at the time of execution)
  • Also mapping to non-predefined exception (standard errors not defined as PL/SQL exceptions)

RAISE_APPLICATION_ERROR - declares a dedicated error number and error message

User-defined exception, example ⌘

/* Declare a bind variable */
VARIABLE B_NO NUMBER;
/* Assign negative value to B_NO */
EXEC :B_NO := -4;
-- EXEC :B_NO := 9;

DECLARE
   I_NO NUMBER := :B_NO;
   I_SQR NUMBER;
   /* Declare an exception variable */
   NORESU EXCEPTION;
BEGIN
   /* Raise the exception if B_NO equals negative value */
   IF I_NO < 0 THEN
      RAISE NORESU;
   END IF;
   I_SQR := SQRT(I_NO);
   DBMS_OUTPUT.PUT_LINE('The result : ' || I_SQR);
EXCEPTION
   /* Exception handler for NORESU exception */
   WHEN NORESU THEN
      DBMS_OUTPUT.PUT_LINE('Number cannot be negative');
END;
/
---------------------------
/*
Number cannot be negative

PL/SQL procedure successfully completed.
*/
---------------------------
/*
The result : 3

PL/SQL procedure successfully completed.
*/

Exercise ⌘

For each employee calculate the payout to commission ratio (ver1).

Raise Application Error ⌘

RAISE_APPLICATION_ERROR - procedure, raises a user-defined exception with a custom exception message

  • Exception can be optionally pre-defined (DECLARE)
  • Syntax: RAISE_APPLICATION_ERROR (error_number, error_message[, {TRUE | FALSE}])
    • error_number - mandatory parameter, values range 20000-20999
    • error_message - user-defined, appears along with the exception
    • last parameter - optional, adding to the current error stack

Example ⌘

---- Example
-- Show employees who earn more than 2000 (excluding commision).
-- The program must raise an exception if monies are less than the given amount.
DECLARE
   G_MONEY NUMBER := 2000;
/* Declare a cursor to filter employees */
   CURSOR C_EMP IS
    SELECT id, ename, sal
    FROM emp;
BEGIN
   FOR E IN C_EMP
   LOOP
   /* Raise exception, if monies are less than 2000 */
      IF e.sal < g_money THEN
        /* Exception name is not used to create the exception handler */
        RAISE_APPLICATION_ERROR (-20008, 'Gets less, show the other one!');
      ELSE 
	DBMS_OUTPUT.PUT_LINE( e.ename || ' gets ' || e.sal);
      END IF;
   END LOOP;
END;
/
---------------
/*
Error starting at line : 171 in command -
ORA-20008: Gets less, show the other one!
ORA-06512: at line 12
*/

Exercise ⌘

Show employees who have joined the company after 1st of Nov, 1981.

  • Raise the exception if earlier

Example con't ⌘

-- Same example, but with named exception
(...)
/* Declare the exception var */
  INVAL_EMP_SAL EXCEPTION;
  PRAGMA EXCEPTION_INIT(INVAL_EMP_SAL, -20008);
(...)
    IF e.sal < g_money THEN
	   RAISE INVAL_EMP_SAL;
    ELSE
(...)
  END LOOP;
EXCEPTION
  WHEN INVAL_EMP_SAL THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM || 'Gets less, show the other one!');
END;
/

Nested Blocks Structure ⌘

/*Parent block*/
DECLARE
...
BEGIN
   /* Outer block executable statements */
...
   /* Child Block */
   DECLARE
  ...
   BEGIN
      ...
      /* Inner block executable statements */
      ...
   EXCEPTION
      /* Inner block exception handlers */
   END;
   ...
   /* Outer block executable statements */
EXCEPTION
/* Outer block exception handlers */
END;

Nested Blocks Expl ⌘

  • Exception raised in inner block ---> program control flows down to exception section of it
  • Inner block handles the exception, it's executed ---> control returns to the next executable statement in the outer block
  • Inner block doesn't handle the exception ---> control searches further and propagates to the exception section of the outer block
    • The execution of the outer block is skipped and the program control lands straight in to the exception section
  • Control will continue to propagate the unhandled exception in the outer blocks
    • Until the appropriate one is found and handled


Cursors ⌘

  • Overview
  • Execution cycle
  • Attributes
  • FOR loop

Overview ⌘

  • SQL is one of the critical parts of PL/SQL
    • Statements embedded within a PL/SQL block are executed as a cursor
  • Cursor - private memory area, temporarily allocated in the session's User Global Area (UGA)
    • Stores the result set retrieved from the SQL execution and cursor attributes
  • Cursors can be implicit or explicit
  • Implicit - is always created for all the included SQL statements
    • Its lifecycle is maintained by db
  • Explicit - execution cycle can be controlled by the user
    • We can explicitly declare an implicit cursor under the DECLARE section in SELECT query

Execution cycle ⌘

PlsqlCursorCycle.jpg

Execution cycle Expl ⌘

Cursor moves through the following stages during execution

  • implicit - all steps are carried out by db

OPEN - allocates the context area in the session's UGA

  • Starts with parsing and binding, followed by statement execution
  • In SELECT the record pointer points to the first record in the result set

FETCH - pulls the data from the query result set

  • In multi-record set, the record pointer moves incrementally with every fetch
  • Is alive until the last record is reached

CLOSE - closes the cursor

  • Flushes the context area
  • Releases the memory back to the UGA

Attributes ⌘

Hold the information about the cursor processing at each stage of its execution

  • %ROWCOUNT: number of rows, fetched until the last fetch
    • or impacted by the last DML operation (SELECT, DML)
  • %ISOPEN: TRUE/FALSE, cursor open or not, implicit is always FALSE
    • The only one accessible outside the cycle (!)
  • %FOUND: TRUE/FALSE, fetch operation switches and points to a record or not
  • %NOTFOUND: FALSE/TRUE, cursor pointer switches but does not point to a record

Attributes, example ⌘

SET SERVEROUTPUT ON

DECLARE

/* Declare a cursor to select employees info */
   CURSOR MY_C_EMP IS
      SELECT ID,ENAME
       FROM EMP;
   L_EMPNO EMP.ID%TYPE;
   L_ENAME EMP.ENAME%TYPE;
BEGIN

/* Check if the cursor is already open */
   IF NOT MY_C_EMP%ISOPEN THEN
     DBMS_OUTPUT.PUT_LINE('--- Employee Info ---');
   END IF;

/* Open the cursor and iterate in a loop */
   OPEN MY_C_EMP;
   LOOP

/* Fetch the cursor data into local variables */
   FETCH MY_C_EMP INTO L_EMPNO, L_ENAME;
   EXIT WHEN MY_C_EMP%NOTFOUND;

/* Display the employee info */
      DBMS_OUTPUT.PUT_LINE(chr(10) || 'Show Info for employee: ' || MY_C_EMP%ROWCOUNT);
      DBMS_OUTPUT.PUT_LINE('Id: ' || L_EMPNO);
      DBMS_OUTPUT.PUT_LINE('Name: ' || L_ENAME);
   END LOOP;
END;
/

/*
--- Employee Info ---

Show Info for employee:1
Id:7839
Name:BUSH

Show Info for employee:2
Id:7566
Name:PUTIN
(...)

PL/SQL procedure successfully completed.
*/

FOR loop ⌘

CURSOR FOR loop

  • Looping through all the records of a cursor object
  • Reduces the overhead of manually specifying the OPEN, FETCH, and CLOSE stages
  • Compacts the code when working with multi-row explicit cursors
  • No need to declare the block variables to capture the cursor columns
    • Index implicitly acts as a record of the cursor type
    • No need to explicitly open or close the cursor

FOR loop, example ⌘

DECLARE
/* Declare an explicit cursor to select employee info */
   CURSOR C_EMP IS
      SELECT ename, sal
      FROM emp;
BEGIN
/* Using the cursor C_EMP directly */
   FOR EMP IN C_EMP
   LOOP
/* Display result */
   DBMS_OUTPUT.PUT_LINE(EMP.ename || ' gains ' || EMP.sal || ' monthly');
   END LOOP;
END;
/

/*
BUSH gains 5000 monthly
PUTIN gains 2975 monthly
CARNEGIE gains 3000 monthly
FORD gains 1100 monthly
(...)
*/

Exercise ⌘

Pick the attribute with the wrong value

Query Cursor attributes 
  BEGIN
  (...)
  SELECT ENAME, DEPTNO
  INTO V_ENAME, V_DEPTNO
  FROM EMP
  WHERE ID = 7900;
  (...)
  END;
  1. SQL%ROWCOUNT = 1
  2. SQL%ISOPEN = FALSE
  3. SQL%FOUND = FALSE
  4. SQL%NOTFOUND = FALSE

Procedures, Functions and Packages ⌘

  • Procedures
  • Functions
  • Packages

Procedure ⌘

Derivative of a PL/SQL block

  • Has a name and is stored persistently within the db
  • Schema object, primarily used to implement business logic on the server side
  • Promotes a modular programming, breaks down complex logic into simple routines

Procedure con't ⌘

  • Must be invoked from the executable section of a PL/SQL block
  • Executable directly from SQLPLUS (EXECUTE)
  • Can not be called from a SELECT statement
  • Can optionally accept parameters - IN, OUT, or IN OUT mode
  • Can't return a value
    • OUT params - is the only way (not via RETURN [value])
  • RETURN - skips the further execution of the program and exits control

Procedure parameter modes ⌘

IN OUT IN OUT
Default Has to be explicitly defined Has to be explicitly defined
Value passed to the program from the calling environment Value returned back to the calling environment Passes a value from the calling env to the program or returns value to the calling env
Passed by reference Passed by value Passed by value
Constant, literal, initialized variable Uninitialized variable Initialized variable
Can hold default value No default value No default value

Procedure syntax ⌘

CREATE [OR REPLACE] PROCEDURE [Procedure Name] [Parameter List]
[AUTHID DEFINER | CURRENT_USER]
IS
  [Declaration Statements]
BEGIN
 [Executable Statements]
EXCEPTION
 [Exception handlers]
END [Procedure Name];

Procedure example ⌘

---- Converts GeoJSON points into WKT(Well-known text)
-- GeoJSON is a standard format for encoding various of geographic data structures
-- WKT is a text markup language for representing vector geometry objects on a map
CREATE OR REPLACE PROCEDURE P_GJSON2WKT (POINTS_STR VARCHAR2, COORDINATES_STR VARCHAR2)
IS
/*Declare the local variables*/
   RESULT_POINTS_STR VARCHAR2(100);
   RESULT_COORDS_STR VARCHAR2(100);
BEGIN
   -- TRANSLATE( '[127.8, 75.6]', '[,]', '( )' )
   RESULT_POINTS_STR := TRANSLATE( POINTS_STR, '[,]', '( )' );
   DBMS_OUTPUT.PUT_LINE('Points : ' || RESULT_POINTS_STR);
   -- TRANSLATE( '(127.8 75.6)', '( )', '[,]' )
   RESULT_COORDS_STR := TRANSLATE( COORDINATES_STR, '( )', '[,]' );
   DBMS_OUTPUT.PUT_LINE('Coordinates : ' || RESULT_COORDS_STR);
END;
/
---------------
/*
Procedure P_GJSON2WKT compiled
*/

Calling Procedures ⌘

  • From SQL*Plus
  • Inside of PL/SQL block

SQLPlus ⌘

  • Initial setup
  • Calling

Initial setup ⌘

sys as sysdba
asdf
SQL> conn mike/mike@localhost/xepdb1
SQL> show user

Calling ⌘

---- Calling procedure from PL*SQL
SQL> SET SERVEROUTPUT ON
/* Session variable for the input */
SQL> VARIABLE P_STR VARCHAR2(100);
/* Test value */
SQL> EXECUTE :P_STR := '[127.8, 75.6]';
-- PL/SQL procedure successfully completed.
SQL> VARIABLE C_STR VARCHAR2(100);
SQL> EXECUTE :C_STR := '(127.8 75.6)';
-- PL/SQL procedure successfully completed.
/* Call the procedure P_GJSON2WKT */
SQL> EXECUTE P_GJSON2WKT(:P_STR, :C_STR);
/*
Points : (127.8  75.6)
Coordinates : [127.8,75.6]

PL/SQL procedure successfully completed.
*/

Calling procedure from anonymous unit ⌘

BEGIN
   /* Call the P_GJSON2WKT procedure */
   P_GJSON2WKT('[127.8, 75.6]', '(271.3 56.4)');
END;
/

Exercise ⌘

Wrap all the 'Invoice Reference' related code into procedure

Creating functions

Named derivative of a PL/SQL block, physically stored in db schema

  • Accepts parameters in all modes(IN, OUT, and IN OUT) and mandatorily returns a value
  • Can be called in SQL statements (SELECT and DMLs)
    • Must accept only IN parameters of valid SQL types
    • Function body must obey the database purity rules
    • Its return type should be a valid SQL data type
  • When invoked from PL/SQL, the return type should be a valid PL/SQL type

Function syntax ⌘

CREATE [OR REPLACE] FUNCTION [Function Name] [Parameter List]
RETURN [Data type]
[AUTHID DEFINER | CURRENT_USER]
[DETERMINISTIC | PARALLEL_ENABLED | PIPELINED]
[RESULT_CACHE [RELIES_ON (table name)]]
IS
  [Declaration Statements]
BEGIN
 [Executable Statements]
  RETURN [Value]
EXCEPTION
 [Exception handlers]
END [Function Name];

Function example ⌘

---- Get the date of birth from pesel
-- 82010901858
-- 820109
-- 82-01-09
--
CREATE OR REPLACE FUNCTION getDateOfBirthFromPesel (P_NUM NUMBER)
RETURN VARCHAR2
IS
  yearFromPesel     VARCHAR2(4); -- peselFromForm.substr(0, 2);
  monthFromPesel    yearFromPesel%TYPE; -- peselFromForm.substr(2, 2);
  dayFromPesel      yearFromPesel%TYPE; -- peselFromForm.substr(4, 2);
  D_BIRTH           VARCHAR2(100);
BEGIN
  /* On production we should validate pesel first */
  /* We should also consider the centuries bit first */
  yearFromPesel     := substr(to_char(P_NUM), 1, 2);
  monthFromPesel    := substr(to_char(P_NUM), 3, 2);
  dayFromPesel      := substr(to_char(P_NUM), 5, 2);

  D_BIRTH := yearFromPesel || '-' || monthFromPesel || '-' || dayFromPesel;
  
  RETURN D_BIRTH;
END;
/
-------------
-- Function GETDATEOFBIRTHFROMPESEL compiled

---------------------------------------------
---- MINI PROGRAM FOR EXECUTING OUR FUNC
---------------------------------------------
DECLARE
   DBIRTH VARCHAR2(100);
BEGIN
   DBIRTH := getDateOfBirthFromPesel(82010901858);
   DBMS_OUTPUT.PUT_LINE('Date of birth from pesel : ' || DBIRTH);
END;
/
---------------------------------------------

Exercise ⌘

  1. Wrap all the 'Franchisee' related code into function
    • HINT: redesign printing code (array?)
    • -- https://docs.oracle.com/en/database/oracle/oracle-database/18/lnpls/plsql-collections-and-records.html#GUID-8060F01F-B53B-48D4-9239-7EA8461C2170
  2. Validate PESEL, Polish social number

Packages ⌘

-- https://docs.oracle.com/en/database/oracle/oracle-database/18/lnpls/plsql-packages.html#GUID-C285EC5A-BE50-4192-A88E-48C0778B34E0

Triggers

-- https://docs.oracle.com/en/database/oracle/oracle-database/18/lnpls/plsql-triggers.html#GUID-217E8B13-29EF-45F3-8D0F-2384F9F1D231

Dynamic SQL

-- https://docs.oracle.com/en/database/oracle/oracle-database/18/lnpls/dynamic-sql.html#GUID-7E2F596F-9CA3-4DC8-8333-0C117962DB73

Collections

-- https://docs.oracle.com/en/database/oracle/oracle-database/18/lnpls/plsql-collections-and-records.html#GUID-7115C8B6-62F9-496D-BEC3-F7441DFE148A

Data Types

-- https://docs.oracle.com/en/database/oracle/oracle-database/18/lnpls/plsql-data-types.html#GUID-391C58FD-16AF-486C-AF28-173E309CDBA5
Retrieved from ‘https://training-course-material.com/index.php?title=PLSQL_Fundamentals&oldid=82440’