Als PDF Downloaden!Aufruf von PL/SQL aus Visual Basic
Aufrufen einer PL/SQL Prozedur aus Visual Basic über OLE DB
Hinweis:
Mit den aktuellen Data Providern wird die Oracle Version 8i nicht mehr unterstützt.
Bei der Entwicklung von Datenbank-Applikationen, die explizit mit einer Oracle Datenbank zusammenarbeiten sollen, trifft man häufig auf das Problem, dass man gerne direkt aus der Anwendung heraus PL/SQL Code (Packages, Prozeduren oder Funktionen) nutzen möchte.
Viele Entwickler verwenden innerhalb ihrer Applikation jedoch oft nur direkte SQL Aufrufe, also direkt im Source-Code geschriebene Select und DML Anweisungen.
Ein Datenbankzugriff über PL/SQL Routinen hat jedoch u. a. den Vorteil der Kapselung von direkten Zugriffen auf interne Tabellenstrukturen. Ein weiterer Vorteil ist die bessere Ausnutzung von Ressourcen auf oft groß ausgelegten Datenbankservern. Jedoch haben sie auch den Nachteil der schlechteren Portierbarkeit auf andere Datenbanken.
Wer sich aber zielgerichtet auf eine Oracle Datenbank konzentrieren muss, kann mit der Verwendung von PL/SQL Routinen obige Vorteile ausnutzen.
Beispielhaft soll hier nun das zugehörige Vorgehen anhand eines Visual Basic Programmausschnittes in Zusammenarbeit mit der OLE DB Schnittstelle aufgezeigt werden.
Aufrufen der Funktion EMP_SAL aus dem Package PKG_EMP
Als Beispiel rufen wir aus Visual Basic über OLEDB eine Funktion EMP_SAL aus dem Package PKG_EMP auf. Diese Funktion hat als Übergabeparameter P_EMPNO NUMBER, P_JOB VARCHAR2 und als Returnwert NUMBER.
Die PL/SQL Funktion selektiert aus der Mitarbeitertabelle, anhand der übergebenen Werte Mitarbeiternummer und Job, das Gehalt und gibt dieses an die VB Applikation zurück.
Die VB Applikation ruft die PL/SQL Funktion mit den richtigen Werten auf und bekommt als Rückgabewert das Gehalt des ausgewählten Mitarbeiters. Das Gehalt kann nun weiterverarbeitet werden, in unserem Beispiel geben wir es nur im Direktfenster aus.
Beispielcode Visual Basic / VB.NET:
Im Vergleich zu VB 6.0 haben sich nur der Connection String und die Parameteraufrufe geändert. Um dies hervorzuheben wurden die VB 6.0 spezifischen Anweisungen auskommentiert.
Private Sub Package_Aufruf()
’Als erstes kommt die Deklaration
Dim v_conn As New ADODB.Connection
' => das Verbindungsobjekt zur Datenbank
Dim v_connectString As String
' => die Parameterangabe für die Verbindung
Dim v_cmd As New ADODB.Command
' => das Objekt das die Ausführung steuert
Dim v_ret As ADODB.Parameter
' => ist der Returnwert
Dim v_empno As ADODB.Parameter
' => der NUMBER Parameter
Dim v_job As ADODB.Parameter
' => der VARCHAR2 Parameter
'Die Zusammensetzung des Connectstring mit dem OLEDB
Treiber von Oracle(distribtx=0 Ausschalten von
Distributed Transaction)
'v_connectString="Provider=OraOLEDB.Oracle;distribtx=0;
Data Source= TNS-Alias;User Id=user;Password=pwd;"
v_connectString = "Provider=OraOLEDB.Oracle;Data Source=TNS-Alias;
User Id=user;Password=pwd;"
'Das öffnen der Verbindung
v_conn.ConnectionString = v_connectString
v_conn.Open
'Nun geht's richtig los ;-)
With v_cmd
.ActiveConnection = v_conn
.CommandType = adCmdStoredProc
.CommandText = "PKG_EMP.EMP_SAL"
'die Parameter werden gesetzt, als erstes der Returnwert
'("Parametername", Datentyp, Parameterart, erwartete Größe,
[Wert])
'Set v_ret = .CreateParameter("v_ret", adBigInt,
adParamReturnValue,100000)
'Set v_empno = .CreateParameter("v_empno", adBigInt,
adParamInput, 10000, 7499)
'Set v_job = .CreateParameter("v_job", adVarChar,
adParamInput, 100, "SALESMAN")
v_ret = .CreateParameter("v_ret", ADODB.DataTypeEnum.adBigInt,
ADODB.ParameterDirectionEnum.adParamReturnValue, 100000)
v_empno = .CreateParameter("v_empno",
DODB.DataTypeEnum.adBigInt,
ADODB.ParameterDirectionEnum.adParamInput, 10000, 7499)
v_job = .CreateParameter("v_job",
ADODB.DataTypeEnum.adVarChar,
ADODB.ParameterDirectionEnum.adParamInput, 100, "SALESMAN")
'als nächstes werden die Parameter angefügt.
'Achtung hier ist die Reihenfolge, die in der Funktion
erwartet werden entscheidend!
'Der Returnwert muß als erstes angefügt werden!
.Parameters.Append v_ret
.Parameters.Append v_empno
.Parameters.Append v_job
'die letzte Aktion ist das Ausführen der Funktion
.Execute
End With
'Jetzt kann man aus v_ret den Returnwert auslesen.
Debug.Print v_ret.value
'Und am Ende räumen wir die Verbindung noch auf
v_conn.Close
Set v_conn = Nothing
End Sub
Beispielcode PL/SQL
CREATE OR REPLACE PACKAGE pkg_emp IS
FUNCTION emp_Sal(p_empno IN NUMBER, p_job IN VARCHAR2) RETURN NUMBER;
END pkg_emp;
/
CREATE OR REPLACE PACKAGE BODY pkg_emp IS
FUNCTION emp_Sal(p_empno IN NUMBER, p_job IN VARCHAR2) RETURN NUMBER IS
v_sal NUMBER;
BEGIN
SELECT e.sal
INTO v_sal
FROM emp e
WHERE e.empno = p_empno AND e.job = p_job;
RETURN v_sal;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 0;
WHEN OTHERS THEN
RETURN - 1;
END;
END pkg_emp;
/
Zusammenfassung
Mit der Kapselung von Funktionalität innerhalb der Datenbank muß man bei eventuellen Änderungen an der Tabellenstruktur nur noch an der PL/SQL Funktion Hand anlegen. Der VB Code bleibt davon unberührt und es ist keine neue Version der VB Applikation notwendig.
Ausführlich werden diese Themen in unserem Kurs Visual Basic Datenbankprogrammierung MS 2011 behandelt.
E-Mail Verteiler Monatstipps
Bitte nehmen Sie mich in den Verteiler der monatlichen Tipps & Tricks auf.
