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

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).
- 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.

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.

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

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

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

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

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