Matura: Softwareentwicklung & Informationssysteme

9.3. Prozedurale Erweiterung von SQL-Datenbanken

Was versteht man unter „Prozedurale Erweiterung“ einer SQL-Datenbank?

Quelle:

Wer hat PL/SQL eingeführt?

Charakterisieren Sie die Syntax von PL/SQL

declare
    -- Deklarationsblock
begin
    -- Ausführungsteil
exception
    -- Ausnahmeverarbeitung
end;

Wo werden diese gespeichert und welche Vor- bzw. Nachteile hat PL/SQL?

Quelle: https://www.datenbank-plsql.de/packages.htm

Inwieweit ist es sinnvoll, die sogenannte „Business Logik“ – also Funktionalität von Geschäftsobjekten – in den PL/SQL-Raum zu verlagern?

Welche Konsequenzen ergeben sich damit auf den Software-Lifecycle?

Kann eine JDBC-Anwendung eine PL/pgSQL-Funktion nutzen; wenn ja: wie wird eine Nutzung ausschauen?

ResultSet rs = stmt.executeQuery("SELECT * FROM your_func()");

Was versteht man unter einer View?

Welche Operationen können darauf ausgeführt werden?

Welche Befehle stehen für das Einfügen/Updaten von Daten unter SQL zur Verfügung?

Grenzen Sie Trigger, Stored Procedures und Functions voneinander ab.

Stored Procedure

Create proc Proc_name  
@permater  
as begin  
----Query here  
end

Functions

create function funname(@parmeter datatype)  
returns Returntype   
as    
begin Returntype  
end

Trigger

create trigger trigger_name   
before | after    
{insert | update | delete}    
on table_name    
for each row    
---Query here

Quelle: https://www.c-sharpcorner.com/blogs/about-store-proc-function-trigger-in-brif

Welche unterschiedlichen Parameter gibt es und wie können diese verwendet werden.

DELIMITER $$

CREATE PROCEDURE GetOrderCountByStatus (
	IN  orderStatus VARCHAR(25),
	OUT total INT
)
BEGIN
	SELECT COUNT(orderNumber)
	INTO total
	FROM orders
	WHERE status = orderStatus;
END$$

DELIMITER ;

CALL GetOrderCountByStatus('Shipped', @total);
SELECT @total;

Quelle:

Was ist der Unterschied zwischen PreparedStatement, Stored Procedures und Stored Function?

Der Unterschied besteht darin, dass Sie Prepared Statements nicht speichern können. Sie müssen sie jedes Mal “vorbereiten”, wenn Sie eine ausführen müssen. Stored Procedures hingegen können gespeichert und einem Schema zugeordnet werden, aber Sie müssen PL/SQL beherrschen, um sie zu schreiben.

Quelle: https://stackoverflow.com/questions/7296417/difference-between-stored-procedures-and-prepared-statements

Wie kann aus einer objektorientierten Sprache wie Java PL/SQL genutzt werden (Beispiel)?

// connect to a local XE database as user HR
OracleDataSource ods = new OracleDataSource();
ods.setURL("jdbc:oracle:thin:hr/hr@localhost:1521/XE");
Connection conn = ods.getConnection();

// call the PL/SQL procedures with the three parameters
// the first two string parameters (1 and 2) are passed to the procedure
// as command-line arguments
// the REF CURSOR parameter (3) is returned from the procedure
String jobquery = "begin get_emp_info(?, ?, ?); end;";
CallableStatement callStmt = conn.prepareCall(jobquery);
callStmt.registerOutParameter(3, OracleTypes.CURSOR);
callStmt.setString(1, args[0]);
callStmt.setString(2, args[1]);
callStmt.execute();

// return the result set
ResultSet rset = (ResultSet)callStmt.getObject(3);

// determine the number of columns in each row of the result set
ResultSetMetaData rsetMeta = rset.getMetaData();
int count = rsetMeta.getColumnCount();

// print the results, all the columns in each row
while (rset.next()) {
    String rsetRow = "";
    for (int i=1; i<=count; i++){
            rsetRow = rsetRow + " " + rset.getString(i);
    }
    System.out.println(rsetRow);
}

Quelle: https://docs.oracle.com/cd/B25329_01/doc/appdev.102/b25108/xedev_jdbc.htm

Was ist ein Cursor (wofür wird dieser eingesetzt, wie schaut die Struktur aus)?

-- First declare a Cursor
DECLARE cursor_name CURSOR FOR SELECT_statement;

-- Declare Handler
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

-- Open the Cursor
OPEN cursor_name;

-- Fetch the Data
FETCH cursor_name INTO varables list;

-- Close Cursor
CLOSE cursor_name;


DELIMITER $$
CREATE PROCEDURE createEmailList (
	INOUT emailList varchar(4000)
)
BEGIN
	DECLARE finished INTEGER DEFAULT 0;
	DECLARE emailAddress varchar(100) DEFAULT "";

	-- declare cursor for employee email
	DEClARE curEmail 
		CURSOR FOR 
			SELECT email FROM employees;

	-- declare NOT FOUND handler
	DECLARE CONTINUE HANDLER 
        FOR NOT FOUND SET finished = 1;

	OPEN curEmail;

	getEmail: LOOP
		FETCH curEmail INTO emailAddress;
		IF finished = 1 THEN 
			LEAVE getEmail;
		END IF;
		-- build email list
		SET emailList = CONCAT(emailAddress,";",emailList);
	END LOOP getEmail;
	CLOSE curEmail;

END$$
DELIMITER ;

Quelle: StoredProcedures_MySQL (S. 24-25)

Welche einsetzbaren Tools gibt es?

Gibt es Debugger für PL/SQL-Systeme?

Kompatibilität von PL/SQL (Oracle/MySQL/Postgres/MariaDB/..).

Was versteht man unter SQL-Injection und wie kann man sich davor schützen?

Quelle: https://www.w3schools.com/sql/sql_injection.asp