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

SET SERVEROUTPUT ON;

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;

BEGIN

   V_TEXT_2 := 'NEW DEFAULT';
   DBMS_OUTPUT.PUT_LINE(V_TEXT_1);
   DBMS_OUTPUT.PUT_LINE(V_TEXT_2);
   
   V_TEXT_3 := 'STRING 3';
   DBMS_OUTPUT.PUT_LINE(V_TEXT_3);
   
   V_TEXT_4 := 'STRING' || ' 4 ';
   DBMS_OUTPUT.PUT_LINE(V_TEXT_4 || 'NEW STRING');

   DBMS_OUTPUT.PUT_LINE( || V_NUMBER_1);
   DBMS_OUTPUT.PUT_LINE(V_NUMBER_2);
   DBMS_OUTPUT.PUT_LINE(V_NUMBER_3);
   DBMS_OUTPUT.PUT_LINE(V_NUMBER_4);
   DBMS_OUTPUT.PUT_LINE(V_NUMBER_5);    
   
   DBMS_OUTPUT.PUT_LINE(V_PLS_INTEGER_1);    

   DBMS_OUTPUT.PUT_LINE(V_BINARY_INTEGER_1);    

   DBMS_OUTPUT.PUT_LINE(V_BINARY_FLOAT_1);
   
   DBMS_OUTPUT.PUT_LINE(V_DATE_1);
   DBMS_OUTPUT.PUT_LINE(V_DATE_2);

   DBMS_OUTPUT.PUT_LINE(V_TIMESTAMP_1);
   DBMS_OUTPUT.PUT_LINE(V_TIMESTAMP_2);

END;

PL/SQL VARIABLE

- Declaring
- Initializing
- Attribute
- Scope
- Bind

PL/SQL CONTROL STRUCTURES

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

PL/SQL CODE

- Name conventions