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 

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

USING SQL INSIDE PL/SQL

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

Gleiche Reihenfolge|Typen der Variablen im STATEMENT

DML OPERATIONS

- INSERT
- UPDATE
- DELETE
- MERGE

SEQUENCES

SELECT sequence_name.nextval|currval INTO variable|column
FROM table_name|dual;

Variable|column :=sequence_name.nextval|currval

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

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

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