SQL

SQL mit ORACLE 10.2

ORACLE Stored Procedures

Native Dynamic SQL

SELECT * FROM <tableName>;

DELETE FROM <tableName>;

UPDATE <tableName>
SET <columnName> 
WHERE <columnName> = <value>;

SELECT * FROM ALL_TABLES;
SELECT * FROM ALL_ALL_TABLES;

SELECT * FROM TABS;
SELECT * FROM USER_TABLES;
SELECT * FROM DBA_TABLES;

SELECT * FROM ALL_INDEXES;
SELECT * FROM USER_INDEXES;
SELECT * FROM DBA_INDEXES;

SELECT * FROM ALL_PROCEDURES;
SELECT * FROM USER_PROCEDURES;
SELECT * FROM DBA_PROCEDURES;

SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'LINK';
-- table name has to be written in uppercase for constraint queries
SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'MULTILAYERSUBNETWORK' AND CONSTRAINT_TYPE = 'R'; 

DROP TABLE <tableName>;
DROP TABLE <tableName> CASCADE CONSTRAINTS;

CREATE OR REPLACE
PROCEDURE helloworld AS
BEGIN
    DBMS_OUTPUT.PUT('Hello ');
	DBMS_OUTPUT.PUT_LINE('World!');
END;
/

create or replace
FUNCTION getMEAtt RETURN types.meatt_array 
AS
...

create or replace
PROCEDURE getMEAtt
AS
...

create or replace
FUNCTION getMEAtt(startIndex in INTEGER) RETURN meattarray 
AS

OracleCallableStatement stmt 
	= (OracleCallableStatement)conn.prepareCall(
		"begin ? := GETMEATT(?); end;" );

stmt.registerOutParameter(1, OracleTypes.ARRAY, "MEATTARRAY");
stmt.setInt(2, 30);
stmt.executeUpdate();

CREATE OR REPLACE TYPE meattarray AS VARRAY(100) OF MANAGEDELEMENTATTRIBUTES%ROWTYPE;
/

create or replace
PACKAGE types
AS
    TYPE meatt_array IS VARRAY(100) OF MANAGEDELEMENTATTRIBUTES%ROWTYPE;
END;
/

create or replace
FUNCTION getMEAtt RETURN types.meatt_array 
AS
  attArr types.meatt_array := types.meatt_array();  
  CURSOR c_att IS SELECT * FROM MANAGEDELEMENTATTRIBUTES;
  --r1 MANAGEDELEMENTATTRIBUTES%ROWTYPE;
  i INT := 0;
BEGIN
  --FOR r1 IN c_att LOOP
  --  meatt_array.extend;
  --  meatt_array(meatt_array.count) := r1;
  --END LOOP;
  
  OPEN c_att;

  loop

    --FETCH c_att into r1;
    attArr.extend;
    FETCH c_att into attArr(attArr.count);

    --exit when c_auftrag_pos%NOTFOUND;
    i := i + 1;
    
    exit when (i >= 100);

  end loop;

  CLOSE c_att;
  
  RETURN attArr;
END;
/

create or replace
TYPE meattrowtype AS OBJECT 
(
  MANAGEDELEMENT NUMBER(8,0),
  ATTRIBUTETYPE VARCHAR2(30),
  CONFIGURATION NUMBER(8,0),
  ATTRIBUTEVALUE VARCHAR2(30)
);
/

CREATE OR REPLACE TYPE meattarray AS VARRAY(100) OF meattrowtype;
/

create or replace
FUNCTION getMEAtt RETURN meattarray 
AS
  attArr meattarray := meattarray();  
  CURSOR c_att IS SELECT * FROM MANAGEDELEMENTATTRIBUTES ORDER BY MANAGEDELEMENT;
  r1 MANAGEDELEMENTATTRIBUTES%ROWTYPE;
  i INT := 0;
BEGIN
  
  OPEN c_att;

  loop

    attArr.extend;

    FETCH c_att into r1;

    attArr(attArr.count).MANAGEDELEMENT := r1.MANAGEDELEMENT;
    attArr(attArr.count).ATTRIBUTETYPE := r1.ATTRIBUTETYPE;
    attArr(attArr.count).CONFIGURATION := r1.CONFIGURATION;
    attArr(attArr.count).ATTRIBUTEVALUE := r1.ATTRIBUTEVALUE;
    
    i := i + 1;
    
    exit when (i >= 100);

  end loop;

  CLOSE c_att;
  
  RETURN attArr;
END;
/

import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;
import oracle.sql.ARRAY;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import org.eclipse.swt.widgets.*;

public class Main {
	
	Connection conn = null;
	
	public static void main(String[] args) throws SQLException {
		
		Main m = new Main();
		
		m.establishDBConnection();
		
		OracleCallableStatement stmt 
			= (OracleCallableStatement)m.conn.prepareCall(
				"begin ? := GETMEATT; end;" );

		// The name we use below, EMPARRAY, has to match the name of the 
		// type defined in the PL/SQL Stored Function
		stmt.registerOutParameter(1, OracleTypes.ARRAY, "MEATTARRAY");
		stmt.executeUpdate();
		
		// Get the ARRAY object and print the meta data assosiated with it
		ARRAY simpleArray = stmt.getARRAY(1);
		
		System.out.println("Array is of type " + simpleArray.getSQLTypeName());
		
		System.out.println("Array element is of type code " + simpleArray.getBaseType());
		
		System.out.println("Array is of length " + simpleArray.length());
		
		// Print the contents of the array
		Object[] values = (Object[])simpleArray.getArray();
		
		for (int i = 0; i < values.length; i++) {
			oracle.sql.STRUCT object = (oracle.sql.STRUCT)values[i];
			//System.out.println(object.debugString());
			Object[] obs = object.getAttributes();
			int meid = Integer.parseInt(obs[0].toString());
			System.out.println("ME ID: " + meid);
		}
	}
	
	/**
	 * To retrieve the thin driver visit:
	 * http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/index.html
	 * 
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public final void establishDBConnection() {
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
		} catch (ClassNotFoundException e) {
			System.out.println("Nms.establishDBConnection() - Could not load jdbc driver class");
			conn = null;
			return;
		}

		try {
			conn = DriverManager.getConnection(
					"jdbc:oracle:thin:@192.168.4.4:3332:wie", "gehts", "dir");
		} catch (SQLException e) {
			System.out.println("Nms.establishDBConnection() - Could not establish connection");
			conn = null;
			return;
		}
	}

Einschränkungen von Paketen

• Object Types können nicht in Paketen definiert werden.
• Aus einem Java JDBC Programm kann man mittels Returnwerten von Functions nicht auf Datentypen zugreifen, die innerhalb eines Pakets definiert sind! Man muss diese Datentypen außerhalb eines Pakets definieren. Möchte man einen Returnwert mit einem Typ deklarieren, der in einem Paket definiert ist, und diese Function aus einem Java JDBC Program heraus aufrufen, dann erhält man eine Ungültiges Namensmuster: Exception

CREATE OR REPLACE TYPE merowtype AS OBJECT 
(
  ID NUMBER(8,0),
  CONFIGURATION NUMBER(8,0),
  NAME VARCHAR2(50),
  LOCATION NUMBER(8,0),
  PARENTMANAGEDELEMENT NUMBER(8,0),
  TYPE VARCHAR2(20),
  STATE NUMBER(8,0),
  MANUFACTURER VARCHAR2(50),
  MANAGEDELEMENTVERSION VARCHAR2(50)
);
/

-- bodies dürfen nicht leer sein
--CREATE OR REPLACE TYPE BODY merowtype AS
--END merowtype;
--/

CREATE OR REPLACE PACKAGE NmsEms
AS
  TYPE mearray IS VARRAY(100) OF merowtype;
  
  FUNCTION getME(startIndex in INTEGER, config in INTEGER) RETURN mearray;  
END NmsEms;
/

CREATE OR REPLACE PACKAGE BODY NmsEms
AS
    FUNCTION getME(startIndex in INTEGER, config in INTEGER) RETURN mearray 
    IS
      arr mearray := mearray();  
      CURSOR cur IS SELECT * FROM MANAGEDELEMENT WHERE configuration = config ORDER BY ID;
      r1 MANAGEDELEMENT%ROWTYPE;
      moveToStart INT := 1;
      i INT := 0;
    BEGIN
      
      OPEN cur;
      
      -- navigate to start
      loop
        exit when ((moveToStart = startIndex) OR cur%NOTFOUND);
        FETCH cur into r1;
        exit when cur%NOTFOUND;
        moveToStart := moveToStart + 1;
      end loop;
    
      -- store into array
      loop
        arr.extend;
        arr(arr.count) := merowtype(0, 0, '', 0, 0, '', 0, '', '');
        FETCH cur into r1;
    
        arr(arr.count).MANAGEDELEMENT := r1.MANAGEDELEMENT;
        arr(arr.count).ATTRIBUTETTYPE := r1.ATTRIBUTETYPE;
        arr(arr.count).CONFIGURATION := r1.CONFIGURATION;
        arr(arr.count).ATTRIBUTETVALUE := r1.ATTRIBUTEVALUE;
        
        i := i + 1;
        
        exit when ((i >= 100) OR cur%NOTFOUND);
    
      end loop;
    
      CLOSE cur;
      
      RETURN arr;
    END getME;
END NmsEms;
/

DECLARE
  a NmsEms.mearray;
BEGIN
  a := NmsEms.getME(1, 1);
END;
/

Einleitung

CREATE PROCEDURE drop_table () AS
BEGIN
  EXECUTE IMMEDIATE 'DROP TABLE employees';
END;
/

Bind Variables

CREATE PROCEDURE fire_employee (emp_id NUMBER) AS
BEGIN
   EXECUTE IMMEDIATE
      'DELETE FROM employees WHERE employee_id = ' || TO_CHAR(emp_id);
END;
/

CREATE PROCEDURE fire_employee (emp_id NUMBER) AS
BEGIN
   EXECUTE IMMEDIATE
      'DELETE FROM employees WHERE employee_id = :id' USING emp_id;
END;
/

Cursor in NativeDynamic PL/SQL