Oracle

PL/SQL

PL/SQL stands for “Procedural Language extensions to the Structured Query Language”. 
SQL is a popular language for both querying and updating data in the relational database management systems (RDBMS). 
PL/SQL adds many procedural constructs to SQL language to overcome some limitations of SQL. 
Besides, PL/SQL provides a more comprehensive programming language solution for building mission-critical applications on Oracle Databases.

What is PL/SQL

PL/SQL is an embedded language. PL/SQL only can execute in an Oracle Database.
Platform Independence

Oracle VM VirtualBox

Database Virtual Box Appliance / Virtual Machine

- Oracle Forms

PL/SQL ENGINE
Procedural Statment Executor

Architecture
- Cooperates with SQL Engine
- Enables Subprograms
- Dynamic Queries
- Case Insensitivity
- Oracle Style
- Optimizer
- Enables Object-Orinted Programming(OO Programmierung mit abstrakten Datentypen)
- Web Development
  - PL/SQL gateway
  - PL/SQL Web Toolkit

SQL ENGINE
SQL Statement Executor
3 Schritte:
Parsing
Fetching
Executing
Bei der Ausführung optimiert der SQL-Optimierer

Database-Management-Tools

- TOAD(Tools for Oracle application Development)
- Oracle SQL Developer
- SQLplus(command line tool)

Oracle SQL DEVELOPER
ANSICHT/DATA MODELER/BROWSER

Schema vs Tablespace

A schema is a namespace - a logical thing. It is used to organize the names of database objects. It has nothing to do with the way the data is stored.

A tablespace is a physical thing. It's a container for data and has nothing to do with the logical organization of the database objects. 
A database schema vs a database tablespace?

SQL | DDL, DQL, DML, DCL and TCL Commands

DDL – Data Definition Language
DQl – Data Query Language
DML – Data Manipulation Language
DCL – Data Control Language

DDL (Data Definition Language): 
- CREATE: This command is used to create the database or its objects (like table, index, function, views, store procedure, and triggers).
   - Table begin with a letter
   - 1-30 character long
   - A-Z,0-9,-,$,#
   - no duplicate names
   - no reserver words(select,from,etc.)
- DROP: This command is used to delete objects from the database.
- ALTER: This is used to alter the structure of the database.
- TRUNCATE: This is used to remove all records from a table, including all spaces allocated for the records are removed.
- COMMENT: This is used to add comments to the data dictionary.
- RENAME: This is used to rename an object existing in the database.

DQL (Data Query Language):
- SELECT: It is used to retrieve data from the database.

DML(Data Manipulation Language): 
- INSERT : It is used to insert data into a table.
- UPDATE: It is used to update existing data within a table.
- DELETE : It is used to delete records from a database table.
- LOCK: Table control concurrency.
- CALL: Call a PL/SQL or JAVA subprogram.
- EXPLAIN PLAN: It describes the access path to data.

SELECT
FROM
JOIN
WHERE
GROUP BY
HAVING
ORDER BY

DCL (Data Control Language): 
- GRANT: This command gives users access privileges to the database.
- REVOKE: This command withdraws the user’s access privileges given by using the GRANT command.

TCL – Transaction Control Language 
- COMMIT: Commits a Transaction.
- ROLLBACK: Rollbacks a transaction in case of any error occurs.
- SAVEPOINT:Sets a savepoint within a transaction.
- SET TRANSACTION: Specify characteristics for the transaction.

Character functions

lower       | converts
upper       | converts
length      | returns
substring   | select characters from string to display

concat    | join 2 strings with ||
initcap   | first letter to uppercase
trim      | trims text from specified x
ltrim     | trims text from left
rtrim     | trims text from right
replace   | replace and remove strings
translate | replace set of characters

convert functions

to_char   = numeric and date to string
to_date   = numeric & string to date
to_number = value to numeric

date functions

add_months     | add and return
months_between | return between x and y
next_day       | next day of day
last_day       | days left in month from date

numeric functions

round     | 
trunc     | 
floor     | 
ceil      | 
mod       |

Oracle data constrains

NULL         | not null
UNIQUE       | each row unique
PRIMARY KEY  | references pk in other table
FOREIGN KEY  | references fk in other table
CHECK        | meets condition
DEFAULT      |

ALIAS

shorter query
more than one table
table or column name too long or not reader friendly
two or more columns combined together
when functions used in query

Limiting and sorting data

retrieve a only a part of a big data and sort the result
use select clause and operators

order by

filter or sort data
required by some analytic functions
function () over ( order by column_name)

JOIN

equi join
non equi join
catesian join
outer join
self join

join
ON
USING(mergeinto a single col)

aggregate function

avg
count
max
min
sum

set_operator

union all
  returns result from both queries after eliminating doubles
intersect set
 rows that are common to both queries
minnus set
 rows in the first query that are not in the second query

set operators summary
  use order by only at the end of statement

SQL logical operators

- and
- or
- in
- between
- like
- not
- all
- any
- is null

VIEW and DUAL TABLE

VIEW
SQL SELECT QUERY, stored in db, treated like a table but no data stored in.

DUAL TABLE
A special dummy one-row, one-column table, present by default in all oracle db installations
varchar2(1), column value is x

Describe

Description relating to the db obj like tables and view

Synonyms

alias for an obj in the db
can be used to hide the identity and location

PRIVATE SYNONYM

PUBLIC SYNONYM

SQL INDEXES

Used to speed up the performance of sql-querys or statements
Pointer to data

ERD - Entity Relationship Diagram

Cardinality

Crow's foot notation

Pluggable Database

Pluggable Database = Multitenant Architecture

sys-user rolle SYSDBA or SYSOPER

Methoden wie "Bulk Insert"

About DML Statements and Transactions

PL/SQL - Arrays

BLOCKS

DECLARE     | optional
BEGIN       | mandatory
EXCEPTION   | optional
END;        | mandatory

3 TYPES OF BLOCKS
- Anonymous Blocks
- Procedures
- Functions

PL/SQL Outputs

Not an output language
SET SERVEROUTPUT ON
SET SERVEROUTPUT ON SIZE UNLIMITED
SET SERVEROUTPUT ON SIZE 5000
DBMS_OUTPUT | Package
DBMS_OUTPUT 
106 DBMS_OUTPUT

Ein Paket ist ein PL/SQL Objekt das Prozeduren oder Funktionen enthält um Operationen auszuführen.

SET SERVEROUTPUT ON; 
begin 
dbms_output.put_line ('Hello'); 
end; 

 SET SERVEROUTPUT ON; 
 begin 
 dbms_output.put_line ('Anonymous Block'); 
    begin 
        dbms_output.put_line ('Nested Block'); 
    end; 
 end; 

PL/SQL VARIABLE

TYPES:

SCALAR
 CHAR (max_length) | 32767 bytes
 VARCHAR2(max_length)
NUMBER[precision,sale]
BINARY_INTEGER = PLSINTEGER | (LOWER MEMORY, FASTER FOR ARITHMETIC)
BINARY_FLOAT
BINARY_DOUBLE
BOOLEAN | TRUE, FALSE, NULL (DEFAULT NULL)
DATE
TIMESTAMP 
TIMESTAMP(p) WITH TIME ZONE
TIMESTAMP(p) WITH LOCAL TIME ZONE
INTERVAL(p) YEAR TO MONTH
INTERVAL(p) DAY TO SECOND(p)

- LARGE OBJECTS
- REFERENCE
- COMPOSITE

Variable naming rules

start with letter
can contain special chars
max 30 chars
no oracle reserved names

name conventions:
v_variable
cur_cursor
e_exception
p_procedure
p_function
b_bind

variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value] 

sales number(10, 2); 
pi CONSTANT double precision := 3.1415; 
name varchar2(25); 
address varchar2(100);

DECLARE
   V_TEXT_1 VARCHAR2(50) := 'STRING';
   V_TEXT_2 VARCHAR2(50) NOT NULL DEFAULT 'DEFAULT';
   V_TEXT_3 VARCHAR2(50) := 'STRING';    
   V_TEXT_4 VARCHAR2(50) := 'STRING';
   
   V_NUMBER_1 NUMBER NOT NULL := 50;
   V_NUMBER_2 NUMBER NOT NULL := 100.02;
   V_NUMBER_3 NUMBER(4) NOT NULL := 111.22;
   V_NUMBER_4 NUMBER(5,2) NOT NULL := 111.22;
   V_NUMBER_5 NUMBER(5,1) NOT NULL := 111.22;
   
   V_PLS_INTEGER_1 PLS_INTEGER := 111.22;
   V_BINARY_INTEGER_1 BINARY_INTEGER := 111.22;
   V_BINARY_FLOAT_1 PLS_INTEGER := 111.22f;
       
   V_DATE_1 DATE NOT NULL := SYSDATE;
   V_DATE_2 DATE NOT NULL := '6 JUN-22 08:01:22';
   
   V_TIMESTAMP_1 TIMESTAMP NOT NULL := SYSTIMESTAMP;
   V_TIMESTAMP_2 TIMESTAMP(3) WITH TIME ZONE NOT NULL := SYSTIMESTAMP;

PL/SQL DELIMETER | COMMENTS

-- CODE --
/* CODE */

PL/SQL VARIABLE

- Declaring
- Initializing
- Attribute
- Scope
- Bind

PL/SQL BIND VARIABLE

SQL-Bindings können helfen, die Performance von dynamischem SQL ganz erheblich zu verbessern.

Die meisten Datenbankanbieter verfügen heutzutage über einen Ausführungsplan-Cache (Oracle nennt ihn Cursor-Cache), 
in dem zuvor analysierte SQL-Anweisungen gespeichert sind und deren Ausführungsplan (und -pläne) zur Wiederverwendung zwischengespeichert werden. 
Dies ist der Hauptgrund, warum Bind-Variablen so wichtig sind

PL/SQL CONTROL STRUCTURES

- IF
- CASE EXPRESSIONS
- LOOPS
  - Basic LOOP
  - WHILE LOOP
  - FOR LOOP
  - NESTED LOOP
  - LOOP LABELING
- CONTINUE STATEMENT
- GOTO

IF | IF-ELSE | IF-ELSE IF

The IF statement allows you to either execute or skip a sequence of statements, depending on a condition. 
The IF statement has the three forms:

– IF THEN
– IF THEN ELSE
– IF THEN ELSIF

IF condition THEN
statements;
END IF;

LOOP

The CASE statement chooses one sequence of statements to execute out of many possible sequences.
The CASE statement has two types: simple CASE statement and searched CASE statement. 
Both types of the CASE statements support an optional ELSE clause.

Simple CASE statement
Searched CASE statement

This structure is the most basic of all the loop constructs including FOR LOOP and WHILE LOOP. 
This basic LOOP statement consists of a LOOP keyword, a body of executable code, and the END LOOP keywords.

The LOOP statement executes the statements in its body and returns control to the top of the loop. 
Typically, the body of the loop contains at least one EXIT or EXIT WHEN statement for terminating the loop. Otherwise, the loop becomes an infinite loop.

The LOOP statement can have an optional label that appears at the beginning and the end of the statement.

<<label>> LOOP 
 statements; 
END LOOP loop_label; 

 LOOP 
  EXIT; 
 END LOOP;  

 LOOP 
  IF condition THEN 
   EXIT; 
  END IF; 
 END LOOP; 

EXIT WHEN statement
The EXIT WHEN statement has the following syntax:
EXIT WHEN condition;

Nested loops
It is possible to nest a LOOP statement within another LOOP statement as shown in the following example:

USING SQL INSIDE PL/SQL

SELECT columns|expressions
INTO variables|records
FROM table|tables
[WHERE condition];

Gleiche Reihenfolge|Typen der Variablen im STATEMENT

PL/SQL Static SQL 

DML OPERATIONS

A Transaction start's with a DML and end with a commit,rollback or system error

- INSERT
- UPDATE
- DELETE
- MERGE

CREATE TABLE testcopy as
       select * from test;

There is no create or replace table in Oracle

DROP TABLE test;
CREATE TABLE test ...;

SEQUENCES

Use of CURRVAL and NEXTVAL
A sequence is a schema object that can generate unique sequential values. 
These values are often used for primary and unique keys. 
You can refer to sequence values in SQL statements with these pseudocolumns:

CURRVAL: Returns the current value of a sequence
NEXTVAL: Increments the sequence and returns the next value
In same statement increments only 1 time
select 1.NEXTVAL 1.NEXTVAL 1.NEXTVAL ....

You must qualify CURRVAL and NEXTVAL with the name of the sequence:
SELECT sequence_name.nextval|currval INTO variable|column
FROM table_name|dual;

Variable|column :=sequence_name.nextval|currval

How do I reset a sequence in Oracle?
How to reset sequences?
Dynamic SQL to reset sequence value

COMPOSITE DATA TYPES

Records

Ein Datensatz mit Einem oder mehrere Werte
Gleiche oder unterschiedliche Typen
Datensatz innerhalb eines Datensatzes
NULL by Default

1. Weg
r_name TABLE_NAME%rowtype;

Vorteil:
- Dynamische Typen-Zuweisung aus Tabelle
- Durchführung einfacher DML Operationen

2. Weg
- Einzelne Spalten Auswahl
- Eigene Typen definition

type NAME is record ( NAME TYPE,
                      NAME TYPE, 
                      []);

Alle typen ausser ref-cursors

Einen Datensatz

Collections

List of the same type
Vorsicht vor Speicherfehler 

3 Types

- Nested tables
    Keys only Numbers, begin fom 1
    No Max Values

- VARRAYs
    Begrenzung der Grösse
    Genau Angabe der Grösse
    Objekt für Prozedur oder Funktion
    Seltene Verwendung
Max 2 GB
Varray-Typ in der Datenbank
Can't go back
Bounded
Index start from 1
one dimensional array
null by default

- Associative Arrays
    Key can be strings

CURSORS

Bei normaler SQL-Abfrage, wird nur ein Teil der Ergebnismenge, Algorithemn, Ausführungspläne, etc. im KOntextbereich gespeichert.

Cursors are pointers to the Data, in Memory or Disc

2 Types of Cursors:

Implicit Cursor(Create and administration by Database)
  Bei jedem SELECT wird ein Impliziter Cursor erstellt

Explicit Cursor
We can control cursors
Explicit cursors are created by the programmers
Collection vs cursor
You cannot go back in cursors
Abruf- und Speicheralgorithmus
Gleichzeitiges laden von Ergebnismengen in den Speicher
Operationen sind so viel schneller

Anordnung vom Server anhand der Ergebnismenge und der Speichersituation.
Ein Select aus dem Speicher ist schneller als aus der Datenbank

Usage:
- Declare
- Open
- Fetch
- Check
- Close

loop
exit when NAME%notfound;

Cursor Parameters

Cursor Attributes
- %FOUND
- %NOTFOUND
- %ISOPEN
- %ROWCOUNT

FOR UPDATE CLAUSE
- for update(locks the selected rows)
- nowait
- wait(default)
- for update of(locks only selected tables)

EXCEPTIONS

- ORA CODE
 ORA-01403: no data found

Over 10.000 nonamed exceptions
Cause 
Action

A block has 3 sections
- Declare
- Begin-End
- Exception

Implicit
Explicit

- Predefined Oracle Server Error
- Nonpredefined Oracle Server Error
- User-defined Errors

Select "Step Into" on first run, with anonymous blocks
Handle by 3 ways:
1. Trap
2. Propagate
3. Trap & Propagate

SQLCODE & SQLERRM

FUNCTIONS & PROCEDURES

Stored in the Database with names
Compiled only once
Can be called by another block or application
Can return values
Can take parameters

Functions vs procedures in Oracle
What is the difference between function and procedure in PL/SQL?
Function-Based Indexes
Using Autotrace in SQL*Plus
Tuning SQL*Plus

Packages

Advantages of using packages
- Modularity
- Easy Maintenance
- Encapsulation & Security
- Performance
- Functionality
- Overloading

Triggers

- Security
- Auditing
- Data Integrity
- Table Logging
- Event Logging
- Derived Data

3 types of Triggers
- DML Triggers
- Compound Triggers
- Non-DML Triggers

Debugging

dbms_debug_package
JDWP Java Debug Wire Protocol

Never debug on production

Testing and Debugging Procedures with SQL Developer

CARE !! Execute the code on debug
Can't debug package

Connect with SYS or SYSTEM and GRAND SESSION and ANY PROCEDURE

On remote get port range from DBA

Breakpoints
- Run until Breakpointoccurs
- Step over
- Step into

Dynamic SQL & PL/SQL IN PL/SQL

Static SQL
- Written in advance
- parse at compile time

SQL EXECUTION STAGES
- Parse
- Bind
- Execute
- Fetch

Advantages of Dynamic SQL
- Constructed as string at runtime
- Bind variables can be used
- Allows us to use DDL, DCL or Session-Control statements in PL/SQL blocks

How to generate Dynamic SQL(2 Ways)
- Native Dynamic SQL Statements(more efficient and better performance as DBMS_SQL)
- DBMS_SQL Package

Can also generate dynamic PL/SQL blocks

Oracle-Supplied Packages

- StandardPackage
- DBMS_OUTPUT Package(debug subprograms, read-write text data)
- UTL_FILE Package(performing file operations)
- UTL_MAIL Package(performs mailing operations)

PL/SQL CODE

- Name conventions

SQL & PERFORMANCE TUNING

- What is SQL Tuning and why we need it?
- Oracle Database Architecture
- SQL Tuning Basics
- Execution Plans in Details
- Join Operations
- Basic Tuning Techniques
- Advanced Indexing Techniques
- Hints
- Subjects

Change SQL-Structure or Database-Structure
Need to know of the Database Architecture in Basic

Tools
- SQL plus
- SQL Developer
- SQL Tuning Advisor
is internal diagnostic software that identifies problematic SQL statements and recommends how to improve statement performance.
- Automatic SQL Tuning Advisor
- SQL Access Advisor 
is internal diagnostic software that recommends which materialized views, indexes, and  materialized view logs to create, drop, or retain.
- Automatic Database Diagnostic Monitor (ADDM)
ADDM is self-diagnostic software built into Oracle Database.
- TOAD
- AWR
- TKPROF

- Determine the bottlenecks in low-performance Oracle databases
- Use Oracle database performance tuning tools, like: AWR, ASH, V$ views, SQL tracing, real-time monitoring, and EM Express
- Propose solutions to database performance issues
- Implement Oracle methodology in performance tuning

SQL Tuning is a continuous process
You need to tune your queris
- While creating
- After the creating

When to decide tuning?
- By checking the top consuming queries frequently
- After any complaints of bad performance

The reasons of performance loss
- Structural changes
- Changes on the data volume
- Application changes
- Aged statistics
- Database upgrades
- Database parameter changes
- Operating system changes
- hardware changes

Blocks

All data stored in blocks
smallest unit of database storage
2KB-32KB(default 8KB)
stores row data or index data

Eigenschaften:
- Block Header
  Block Type Information
  Table Information
  Row Directory
    ROWID(Like pointer?)
- Rows
Blocks in Blocks
Jeder Block wird in eine bestimmte Stelle geschrieben

PCTFREE or PCTUSE parameters for the space size in blocks

Wenn nicht genug Platz, wird bei jedem update wahrscheinlich die Position geändert
- Verringerte Leistung 
- IO-OPS werden langsamer
- Schlecht für das Tuning
- Verlangsamt die Indexes

Das lesen einer Zeile, von einer Tabelle aus einem Block, ist schneller als aus mehreren Blöcken

1. Row Header
2. Column Data

- Row Overhead
- Number of Columns
- Cluster Key ID(if clustered)
- ROWID of chained Row Pieces(if any)
- Column Length
- Column Value

Wenn die größe der Blocks in den Abfragen verringert wird, steigert man die Leistung

PGA | PROGRAM/PROCESS/PRIVATE GLOBAL AREA

Private for each user

4 Hauptspeicher Bereiche:
PGA
Session AREA
   - Session info for each user
   - Session Variables, login info, session status, etc.
Unnötige verbindungen vermeiden

PSA(Private SQL Area)
PERSISTENT AREA
  - Jede Abfrage wird zu einem Cursor
  - Verwendete Bindvariablen der Cursor werden hier gespeichert
RUNTIME AREA
  - Execution state info
CURSOR AREA
- Information of cursors

SQL WORK AREA
SORT AREA
HASH JOIN AREA
BITMAP MERGE AREA
BITMAP CREATE AREA

Weniger Speicher verringert die LEistung

Speichergröße festlegen
- User
- Oracle selber

SGA | SHARED/SYSTEM GLOBAL AREA

Database buffer cache
Redo log buffer
Shared pool
 - Data dictionary cache
 - Result Cache
 - other
Large pool
- I/O Buffer
- Free memory
- Response queue
- Request queue
JAVA pool
Streams pool

Context switching between pl/sql engine and sql engine

SHARED POOL

DATA DICTIONARY CACHE
- Stores the definitions of the database object and permissions
  - check if Data from query exists
  - check your privileges

RESULT CACHE
- stores result of common used queries
- stores result of functin
  - Bei wiederholter Abfrage wird aus dem RESULT CACHE gelesen
  - Speicher die Abfrageergebnisse
  - Speicher die Funktionsergebnisse

Erhöht die Leistung
Wird von der Datenbank durchgeführt
User kann anweisen Result Cache anweisen daten zu speichern

Library Cache
- Stores the execution plans
- Stores procedures, packages control structures
  Oracle erstellt Ausführungspläne
  Wie die Daten aus der Disc oder dem Puffer gelesen werden
  Die Erstellung eines Ausführungsplans ist ein kostspieliger Vorgang
  Die Verwendung eines Ausführungsplans für ähnliche Abfragen kann sehr effizient sein

OTHER AREAS

BUFFER CACHE

Largest memory area of sga
Stores the copies of the blocks read from the disc
Available for all the users
First check Buffer than Disc, for the data
Check Disc only if blocks are missing
Much faster than disc
Size of the memory is endless
Maintained with a complex algorithm
Can't save all data in buffer cache

IN-MEMORY-DATABASES
Stores the most recently used & most toched ones
Database writer process handles the write operations to the disc
Writes not one by one, writes all changed blocks in one step
dirty blocks
Stores Tabledata and Indexdata

Performance for same
- queries
- tables
- indexes

REDO LOG BUFFER

Redo-Log-Files