Erste Schritte mit Oracle
Mit Materialien vonUlfLeser undSebastianWandelt
13. November2017PatrickSchäfer
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?