Erste Schritte mit Oracle

Mit Materialien vonUlfLeser undSebastianWandelt

13. November2017PatrickSchäfer

patrick.schaefer@hu-berlin.de

Vorlesung: https://hu.berlin/vl_dwhdm17Übung: https://hu.berlin/ue_dwhdm17

Arbeiten mit Oracle• WirwolleneinSchemaerstellen:

CREATE TABLE coworkers(c_id int PRIMARY KEY, name varchar(25) NOT NULL

)

• Daten einfügen:INSERT INTO coworkers

VALUES (1, 'Herbert')

• UndAnfragen stellen:SELECT count(c_id) as count

FROM coworkers

Überblick

• VerbindenmitOracle• JDBC• PL/SQL• OracleSQLDeveloper

Verbindungsdaten

• Server:alibaba.informatik.hu-berlin.de• Port:1521• Datenbankinstanz(OracleSID):orcl• Version:OracleDatabase12cEnterpriseEdition• Wichtig:– NurausdemHU-Netz(VPN+WLAN)erreichbar– JedeGruppeerhälteigenenAccountundTablespace.MireineMailschreiben!

OracleDatenbankzugriff• BeimOracleTechnologyNetworkregistrieren

http://www.oracle.com/technology/

a) AllesinJAVAmitJDBCmachen– JDBCTreiberfürOracleDB12cherunterladen

http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html

– Javaprogrammieren

b) GUI-Tool verwenden- Empfohlen:OracleSQLDeveloper

http://www.oracle.com/technetwork/developer-tools/sql-developer/overview/index.html

- Alternative:GUI-Tool(sieheWebseite)undOracleInstantClientLibrariesherunterladenhttp://www.oracle.com/technetwork/database/features/instant-client/index.html

ZumEntwicklen /Testen

• LokaleInstallationvonOracle11gExpressEdition(Windows+Linux)„OracleDatabase 11g ExpressEdition(OracleDatabaseXE)is anentry-level,small-footprint database based onthe OracleDatabase11g Release2codebase. It's free to develop,deploy,and distribute;fastto download;and simpletoadminister.“http://www.oracle.com/technetwork/database/database-technologies/express-edition/overview/index.html

OracleSQLDeveloper

Verbindungsdaten

Skript ausführen (F5)

JDBC

JavaDatabaseConnectivity(JDBC)- Basics

• EinheitlicheSchnittstellefürZugriffaufRDBMSmittelsSQL-basiertenAnfragen

• GenerischeAPIfüreineVielzahlvonSystemen– Treibersindinunterschiedlichen

Leveln(1bis4)verfügbar• InLevel3werdenAPI-Befehlein

generischeDBMS-BefehleübersetztundanMiddlewareübertragen

• InLevel4werdenAPI-BefehledirektinnativeDBMS-Befehleübersetzt(keineMiddleware) Level4 Level3

JDBC- Konzepte

• RDBMS-spezifischerJDBC-Treiber:DriverManager• VerbindungzurRDBMS:InterfaceConnection• Anfragenformulieren:

– Statement (einzelne Anfragen), – PreparedStatement (mehrere gleichartige Anfragen),

– CallableStatement (PL/SQL)

• Ergebnisseauswerten:ResultSet• Metadatenauslesen

JDBC- kompakttry (Connection con = DriverManager.getConnection(

"jdbc:oracle:thin:@alibaba:1521:orcl", “login", “password")) {

con.setAutoCommit(true);

try (Statement stms = con.createStatement()) {String sql = „...“;try (ResultSet rs = stmt.executeQuery(sql)) {while (rs.next()) {String name = rs.getString(“name");System.out.println(name);

}}

}}catch (SQLException e) {e.printStackTrace();

}

Verbindung

Statement

ResultSet

JDBC- Verbindung• ImClasspath:– OracleJDBCLibraryojdbc7.jar

• Danntry (Connection con = DriverManager.getConnection(

"jdbc:oracle:thin:@alibaba:1521:orcl","login", "password")

) {

JDBC– Statements

• SindgewöhnlicheSQL-Statements,dieJDBCandasRDBMSweiterleitet

• ErzeugtwirdeinStatementüberdasInterfaceConnection– InJava>7:mittry-with-resource:

try (Statement stmt = con.createStatement()) {stmt.executeQuery(SQL);// kein stmt.close() notwendig

}

– InJava<7:Statement stmt = con.createStatement(); stmt.execute(...);stmt.close(); // wichtig!

JDBC– Statements

• Ausführungmittels:– ResultSet executeQuery()– int executeUpdate()– boolean execute()

• Statement.executeQuery gibteinenIterator aufdieErgebnislistemitInterfaceResultSet zurückResultSet result = stmt.executeQuery(

"SELECT c_id, name FROM coworkers ORDER BY c_id“);

• Ergebnissekönnenelementweisedurchlaufenwerdenüberwhile (result.next()) { ... }

• OdereineinzelnesTupelabfragenif (result.next()) { ... }

JDBC– Statements– Beispiele

• Returnstrue if the first result is aResultSet or false if it is anupdatecountor there are no results,zBboolean result = stmt.execute(

"CREATE TABLE coworkers(c_id int, name varchar(25))”);

• ReturnseithertherowcountforSQLDMLstatementsor0 forSQLstatementsthatreturnnothing,zBint result = stmt.executeUpdate(

"INSERT INTO coworkers VALUES (1, 'Herbert')" );

• ReturnsaResultSet objectthatcontainsthedataproduced bythegivenquery;nevernull,zBResultSet result = stmt.executeQuery(

"SELECT count(c_id) as count FROM coworkers”);

JDBC– ResultSet

• Zugriff aufWerte über dieMethodenResultSet.getXXX("<attrib>")

• alsogetString(),getInt(),getObject()…ResultSet result = stmt.executeQuery("SELECT c_id ...”)while(result.next()) {

int c_id = result.getInt("c_id");String name = result.getString("name");

}

• Zugriff aufDatentyp ist auch über Metadaten möglichwhile (result.next()) { int numColumns = result.getMetaData().getColumnCount(); for ( int i = 1 ; i <= numColumns ; i++ ) { // 1 is first index System.out.println(result.getObject(i));

}}

JDBC– PreparedStatements• NormaleSELECTStatementswerdenimRDBMSjedesMalneugeparst,

optimiertundkompiliert• IneffektivbeimehrfacherAusführungmitgeändertenParametern• Besser:

try (PreparedStatement pstmt=con.prepareStatement("INSERT INTO coworkers (c_id, name) VALUES (?,?)“)) {

{LOOP}// prepare tuples:pstmt.setInt(1, anInt);pstmt.setString(2, aString);// add prepared statementpstmt.addBatch();

{ENDLOOP}// execute all operations at onceps.executeBatch();

}

JDBC- Quellen

• Tutorialhttp://www.jdbc-tutorial.com/

• OracleJDBCDriver(Oracle12c!)http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html

• GoogleJ

Wichtige Typen• Zeichenketten:– Fixed-length(padded):CHAR,NCHAR (Unicode)– Variable-length:VARCHAR2,NVARCHAR2 (Unicode)

• Zahlen:– INTEGER (32bit),LONGINTEGER(64bit),– Floating-Point:DECIMAL(precision,scale),NUMBER(precision,scale)

• Datum:– DATE (defaultformatis DD-MON-YY)

• RAWandLONGRAW– BLOB,CLOB,NCLOB,LONG (!!)

PL/SQL

PL/SQL- kompakt• Oracle-eigeneProgrammiersprache,engeIntegration

mitSQL• SämtlicheprozeduralenKonzeptesindverfügbar:

Funktionen/Prozeduren,Abfragen/bedingteAusführung,Schleifen,etc.

• WirdverwendetfürSQLFunktionen,Trigger,Stored-Procedures,etc.

• CodewirdinderDatenbank,u.U.währendderAusführungeinerSQL-Anfrageausgeführt– KeineBenutzerschnittstellen– KeineAusgabeaufBildschirmetc.– Logging istgarnichtsoeinfach(Transaktionen)

PL/SQL- Programme

DECLARE

.. /* Variablen-& Typdeklarationen */

BEGIN

.. /* Programmcode, Funktionen .. */

EXCEPTION

.. /* Optional: Exception-Handling */

END;

PL/SQL- Beispiel

• EinfachesBeispielset serveroutput on

DECLARE

cnt number := 0;

BEGIN

SELECT count(*) INTO cnt FROM USER_TABLES;

cnt := cnt*10;

dbms_output.put_line ('Einträge: ' || cnt);

END;

• ErlaubteSQL-Statements:– DML:select, insert, delete, update

• Nicht(direkt)erlaubt– DDL:create, drop, alter,etc.

PL/SQL- SQLDeveloper

PL/SQL- Variablen

• AllevonOracleunterstützenTypen+NUMBER,BOOLEAN• ErmittlungzurCompilezeit möglich

DECLARE

name VARCHAR(20);

gruppe NUMBER;

punktezahl table_name.column_name%TYPE;…

Kontrollflüsse• Bedingungen

– IF <B> THEN ... ELSIF <B2> THEN ...ELSE ... END IF;

– CASE selectorWHEN 'value1' THEN ...; ELSE ...;

END CASE;• Schleifen

– LOOP ...

EXIT WHEN <B> ...

END LOOP;– WHILE <B> LOOP

... END LOOP;

– FOR <V> IN <C1>..<C2> LOOP...

END LOOP;

PL/SQLStringFunktionen• CONCAT• || : Konkatenation• INSTR: Suche nach Teilsequenz• REPLACE• SUBSTR• LENGTH• TRIM• UPPER• LOWER• ...

PL/SQL- Procedure

• Gibt keinen Wertzurück (analog zu ’void’)

CREATE OR REPLACE PROCEDURE procedure_name(parameter [IN | OUT | IN OUT] type

) AS/* Variablen-& Typdeklarationen */

BEGIN /* Programmcode */

END procedure_name;

PL/SQL- Funktionen

• Gibt einen Wertzurück

CREATE OR REPLACE FUNCTION function_name( parameter_name [IN | OUT | IN OUT] type

) RETURN type AS

/* Variablen-& Typdeklarationen */BEGIN

/* Programmcode */

RETURN …;END function_name;

PL/SQL- Sonstiges

• BefehlausführenmittelsGUITool(z.B.OracleSQLDeveloper)CREATE OR REPLACE PROECUDURE myprocAS… // VariablenBEGIN…end;

• Entwedergehtesgut,oderesgibtFehler

PL/SQL- Konzepte

• Weitere Konzepte:– Arrays(VARARRAY),Collections– Cursor:Iteratoraller Tupel einer Anfrage– Trigger:Programme,dieautomatisch ausgeführt werden,wenn eine Bedingung erfüllt wird bei• z.B:“auto_increment”

– Exceptions– …

PL/SQL– EXPLAINPLAN

• EXPLAIN PLAN FORSELECT * FROM user_tables;

• ParstundoptimiertdieQuery,ohnesieauszuführen• ErfasstdieAbfolgesämtlichergewählterOperationen– ReferenzierteTabellen– VerwendeteZugriffsmethoden– Join-Methode– GeschätzteKosten– …

• PlänekönnendirektinSQLDeveloperangezeigtwerden

PL/SQL– EXPLAINPLAN

Weiterführende Links

• SQLDeveloper:– https://www.youtube.com/watch?v=U-Iigi2oBUo

• OracleDokumentation:– http://docs.oracle.com/database/122/index.htm

• PL/SQL– QuickGuidehttps://www.tutorialspoint.com/plsql/plsql_quick_guide.htm

– Oraclehttps://docs.oracle.com/database/121/LNPLS/

Fragen,Anregungen?