PLSQL Fundamentals: Difference between revisions

Revision as of 13:36, 21 May 2021

title: PL/SQL Fundamentals
author: Lukasz Sokolowski

</slideshow>

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.

Copyright Notice

This publication is protected by copyright, and permission must be obtained from the publisher prior to any prohibited reproduction, storage in a retrieval system, or transmission in any form or by any means, electronic, mechanical, photocopying, recording, or likewise.

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 ⌘

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 ⌘

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;	SQL%ROWCOUNT = 1 SQL%ISOPEN = FALSE SQL%FOUND = FALSE 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 ⌘

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

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