Folien Czarski

Moderne Anwendungsentwicklung in der Praxis
Oracle Database 12c.
Carsten Czarski
Business Unit Database
Oracle Deutschland B.V. & Co KG
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Bewährtes wird noch besser: SQL und PL/SQL
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
2
Identity Columns
Bisher
create table tab_kunden (
id
number(10) primary key
name varchar2(200)
:
);
create sequence seq_kunden
start with 1 increment by 1;
Oracle12c
create table kunden_tab(
id
number(10) primary key
generated always as identity
start with 1 increment by 1,
name varchar2(200)
:
);
create or replace trigger tr_pk_kunden
before insert on tab_kunden
for each row
begin
:new.id := seq_kunden.nextval;
end;
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Eine Query – Keine Function – Keine Statistik-Daten ...
http://twitter.com/cczarski
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
4
PL/SQL WITH Klausel
• Die Funktion existiert nur für diese SQL-Abfrage
• Keine Persistenz im Data Dictionary
WITH
FUNCTION get_domain(url VARCHAR2) RETURN VARCHAR2 IS
pos BINARY_INTEGER;
len BINARY_INTEGER;
BEGIN
pos := INSTR(url, 'www.');
len := INSTR(SUBSTR(url, pos + 4), '.') - 1;
RETURN SUBSTR(url, pos + 4, len);
END;
SELECT
DISTINCT get_domain(catalog_url)
FROM
orders;
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
SQL Pattern Matching
• Muster finden - über mehrere Zeilen hinweg
• SQL-Syntax für deklaratives Pattern Matching
• Keine Programmierung: Die Datenbank löst die Aufgabe!
CLIENT_IP
--------------10.165.251.50
129.157.43.110
10.175.6.99
10.175.6.99
10.165.251.50
10.165.251.50
:
ZEITSTEMPEL
-------------------2011-04-20 14:30:10
2011-04-20 14:30:34
2011-04-20 14:30:46
2011-04-20 14:30:46
2011-04-20 14:30:59
2011-04-20 14:30:59
:
REQUEST
HTTP_CODE
-------------------- --------POST /pls/htmld ...
200
POST /pls/htmld ...
200
GET /pls/htmldb ...
301
GET /pls/htmldb ...
200
GET /pls/htmldb ...
301
GET /pls/htmldb ...
200
:
:
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
SQL Pattern Matching: Ein Beispiel
select p.client_ip, p.session_id, p.start_tstamp, p.cnt_clicks
from exttab_webserver_log
MATCH_RECOGNIZE (
PARTITION BY client_ip
ORDER BY zeitstempel asc
MEASURES
strt.zeitstempel
AS start_tstamp,
MATCH_NUMBER()
AS session_id,
FINAL COUNT(zeitstempel) AS cnt_clicks
ONE ROW PER MATCH
AFTER MATCH SKIP PAST LAST ROW
PATTERN (strt nxt+)
DEFINE
nxt AS nxt.zeitstempel < (PREV(nxt.zeitstempel) + interval '300' second)
) p
order by client_ip, start_tstamp;
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Zugriff auf PL/SQL Objekte in Oracle12c
• Einem PL/SQL-Objekt können nun Rollen zugewiesen werden
– Erlaubt wesentlich bessere Rechte / Rollenkonzepte
grant {role} to function meine_funktion;
• ACCESSIBLE BY-Klausel verhindert direkten Aufruf eines Objektes
– Verhindern des direkten Aufrufs von "Helper Packages"
create or replace package pkg_helper
accessible by {other plsql object} is
:
end pkg_helper;
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Daten maskieren: Data Redaction
• Maskierung der Ergebnisse(!) einer SQL-Abfrage
– Abfragen funktionieren wie gewohnt
– Alle WHERE-Bedingungen sind möglich – Indizes werden genutzt
– Ergebnisausgabe teilweise oder vollständig maskiert
• Hilfreich für den Anwendungsentwickler
– Maskierung sensibler Daten in der Anwendungsmasken
Kreditkartennummern, Geburtsdatum, etc.
– Sicherstellung einheitlicher Maskierungen durch Hinterlegen
in der Datenbank  Bspw. "Immer die letzten 4 Stellen"
• Lizenz der Advanced Security Option erforderlich
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Data Redaction
SQL> select * from emp where ename like 'M%' ;
EMPNO
----7654
7934
ENAME
---------***TIN
***LER
JOB
MGR HIREDATE
SAL COMM DEPTNO
--------- ----- ------------------- ----- ----- -----SALESMAN
7698 28.09.1981 00:00:00 1250 1400
30
CLERK
7782 23.01.1982 00:00:00 1300
10
2 Zeilen ausgewählt.
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Der Weg zu Oracle12c: Wichtige Änderungen ...
• Änderungen an Standard-Privilegien
– RESOURCE-Rolle enthält nicht länger das UNLIMITED TABLESPACE Privileg
– Neues Privileg INHERIT PRIVILEGES für AUTHID CURRENT_USER Prozeduren
http://sql-plsql-de.blogspot.co.uk/2015/01/inherit-privileges-in-oracle12c-was-ist.html
• Multitenant-Architektur: Auswirkungen auf Connection-Strings
– SID führt nur zur Container Database; Service-Name ist für PDB nötig
• Die XML DB ist ab Oracle12c mandatory
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
REST
Microservices JSON
Feeds OAuth Javascript
Modern Application
Development
Spatial Data Text Data
Web API
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Oracle12c. Tabellen.
Oracle12c. JSON
Document Store.
Oracle REST Data Services
(ORDS)
Oracle NoSQL DB
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Daten in Oracle12c – Zugriff mit REST ...?
SQL> desc emp
Name
Null?
-------------------------------- -------EMPNO
NOT NULL
ENAME
JOB
MGR
HIREDATE
SAL
COMM
DEPTNO
Typ
------------NUMBER(4)
VARCHAR2(10)
VARCHAR2(9)
NUMBER(4)
DATE
NUMBER(7,2)
NUMBER(7,2)
NUMBER(2)
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
REST-API für Datenbanktabellen. 15 Minuten.
1. ORDS herunterladen und auspacken
5 Minuten
2. ORDS starten und konfigurieren
8 Minuten
3. Datenbankschema für REST freigeben
1 Minute
4. Tabelle für REST freigeben
1 Minute
begin
ords.enable_schema(
p_enabled
p_schema
p_url_mapping_pattern
p_auto_rest_auth
);
end;
begin
ords.enable_object(
p_enabled
=>
p_schema
=>
p_object
=>
p_object_type
=>
p_object_alias
=>
p_auto_rest_auth =>
);
end;
=>
=>
=>
=>
true,
'SCOTT',
'scott',
false
true,
'SCOTT',
'EMP',
'TABLE',
'the-emp-table',
false
http://json-rest-oracledb.blogspot.ro/2015/07/rest-enabling-einer-tabelle-mit.html
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
REST-API für Datenbanktabellen. 15 Minuten.
SQL> select * from emp;
EMPNO
----4711
7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
7900
7902
7934
ENAME
---------CZARSKI
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
JOB
MGR HIREDATE
SAL COMM DEPTNO
--------- ----- ------------------- ----- ----- -----ORACLEGUY
15.10.2015 00:00:00
20
CLERK
7902 17.12.1980 00:00:00
800
20
SALESMAN
7698 20.02.1981 00:00:00 1600
300
30
SALESMAN
7698 22.02.1981 00:00:00 1250
500
30
MANAGER
7839 02.04.1981 00:00:00 2975
20
SALESMAN
7698 28.09.1981 00:00:00 1250 1400
30
MANAGER
7839 01.05.1981 00:00:00 2850
30
MANAGER
7839 09.06.1981 00:00:00 2450
10
ANALYST
7566 09.12.1982 00:00:00 3000
20
PRESIDENT
17.11.1981 00:00:00 5000
10
SALESMAN
7698 08.09.1981 00:00:00 1500
0
30
CLERK
7788 12.01.1983 00:00:00 1100
20
CLERK
7698 03.12.1981 00:00:00
950
30
ANALYST
7566 03.12.1981 00:00:00 3000
20
CLERK
7782 23.01.1982 00:00:00 1300
10
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Flexibel JSON
Feeds Javascript Web API
JSON
Schemaless REST
Modern Application
Development
Document Store
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
JSON and SQL Duality.
Oracle Database 12c
JSON
Datenzugriff per
REST oder
Native API
SQL
JSON wird in der
Datenbank gespeichert
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Analysen / Queries per SQL
Twitter Daten als JSON-Feed ...
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
... in Oracle12c.
SQL> select tweet from apextweets where rownum <= 3;
TWEET
-------------------------------------------------------------------------------{"retweeted_status":{"contributors":null,"text":"#countdown naar #Apexworld @OGh
_nl #iadvise_live @Yvke1983 geeft met #Robeco een client case over #twitterboots
trap #formsmigratie #orclapex","geo":null,"retweeted":false,"in_reply_to_screen_
name":null,"truncated":false,"lang":"nl","entities":{"symbols":[],"urls":[],"has
htags":[{"text":"countdown","indices":[0
{"retweeted_status":{"contributors":null,"text":"#countdown naar #Apexworld @OGh
_nl #iadvise_live @Yvke1983 geeft met #Robeco een client case over #twitterboots
trap #formsmigratie #orclapex","geo":null,"retweeted":false,"in_reply_to_screen_
name":null,"truncated":false,"lang":"nl","entities":{"symbols":[],"urls":[],"has
htags":[{"text":"countdown","indices":[0
{"contributors":null,"text":"#countdown naar #Apexworld @OGh_nl #iadvise_live @Y
vke1983 geeft met #Robeco een client case over #twitterbootstrap #formsmigratie
#orclapex","geo":null,"retweeted":false,"in_reply_to_screen_name":null,"truncate
d":false,"lang":"nl","entities":{"symbols":[],"urls":[],"hashtags":[{"text":"cou
ntdown","indices":[0,10]},{"text":"Apexw
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
SQL/JSON Funktionen.
• JSON-Daten in Tabellen mit SQL/JSON-Funktionen abfragen
– Externe Tabellen ebenso möglich
– JSON-Daten in Tabellen, Dateien, NoSQL-Datenbanken, Hadoop-Cluster
• Vorhandene SQL/JSON Funktionen
– JSON_VALUE selektiert skalare Werte
– JSON_QUERY selektiert "JSON-Fragmente"
– JSON_EXISTS prüft die Existenz von JSON-Attributen
– JSON_TABLE projiziert JSON-Attribute als relationale Ergebnismenge
– JSON_TEXTCONTAINS dient zur Volltextsuche in JSON-Daten
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
SQL/JSON am Beispiel.
select distinct json_value(
tweet,
'$.user.screen_name' RETURNING VARCHAR2(35)
) from apextweets;
JSON_VALUE
-----------------------------------------------------------------AdKosigan
Enkitec
berkleyp
delrez_stephane
:
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Ein weiteres Beispiel -I-
SQL/JSON Part
zur Selektion der
JSON-Attribute
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Ein weiteres Beispiel -II"Klassische"
SQL-Aggregation
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
JSON
IO.js
Javascript
Modern Application
Development
Oracle12c
+ Node.js = node-oracledb
Node.js REST
Web API
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Javascript auf dem Server
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Node Package Manager (npm).
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Node.js und die Oracle-Datenbank – ein Beispiel.
var oracledb = require('oracledb');
oracledb.getConnection(
{
user
: "scott",
password
: "tiger",
connectString : "sccloud033:1521/orcl"
},
function(err, connection) {
if (err) {console.error(err.message); return;}
connection.execute(
"SELECT * from EMP where EMPNO=7839",
[],
function(err, result) {
if (err) {console.log('%s', err.message); return;}
console.log(result.rows);
}
);
}
);
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Modern Application Development. Node.js.
• Oracle12c. Plattform für alle Unternehmensdaten
– JSON, XML, Tabellen
– Anbindung via node-oracledb oder ORDS
N
• Express. Webserver-Plattform für Node.js
– Einfache, schnelle, flexible Entwicklung
E
• Node.js. Javascript auf dem Server
– Leicht erlernbar
– Große Funktionsbibliothek - Große Community
O
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
http://tinyurl.com/ModernAppDev12c
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |
Fazit: Entwickler profitieren deutlich mit Oracle12c.
• Bewährtes wird noch besser: SQL und PL/SQL in Oracle12c
– Identity Columns, 32k VARCHAR2, PL/SQL WITH-Klausel und vieles mehr
– SQL Pattern Matching: Völlig neue Möglichkeiten
– Verbesserte Rechte, Rollen und Security für PL/SQL
• Neue Horizonte: Moderne Anwendungsentwicklung mit Oracle12c
– Native JSON-Unterstützung in der Datenbank
– REST-Schnittstellen ganz einfach: Oracle REST Data Services
– Node.js und Oracle12c – moderne Technologie im Praxiseinsatz!
Copyright © 2014 Oracle and/or its affiliates. All rights reserved. |