Oracle: Unterschied zwischen den Versionen
Zur Navigation springen
Zur Suche springen
Geist (Diskussion | Beiträge) |
Geist (Diskussion | Beiträge) |
||
| Zeile 90: | Zeile 90: | ||
=== PL/SQL VARIABLE === | === PL/SQL VARIABLE === | ||
TYPES: | 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''' | - '''LARGE OBJECTS''' | ||
- '''REFERENCE''' | - '''REFERENCE''' | ||
- '''COMPOSITE''' | - '''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 | ||
Version vom 4. Juni 2022, 17:23 Uhr
Inhaltsverzeichnis
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.
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