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

Liste:
- Name conventions