Beispiele für die Erstellung und Ausführung gespeicherter Prozeduren in Oracle

Oracle ist ein sehr leistungsfähiges Datenbankverwaltungssystem mit vielen erweiterten Funktionen und Features, zu denen auch gespeicherte Prozeduren gehören. Bei einer gespeicherten Prozedur handelt es sich um eine Reihe vordefinierter SQL-Anweisungen für Datenbankoperationen, die zur späteren Aufrufverwendung in der Datenbank gespeichert werden können.

In Oracle werden gespeicherte Prozeduren in PL/SQL geschrieben, einer Sprache, die SQL und Programmierung kombiniert. PL/SQL verfügt über starke Datenbearbeitungs- und Prozesssteuerungsfunktionen und kann problemlos effiziente gespeicherte Prozeduren schreiben.

Vorteile gespeicherter Prozeduren

Der Hauptvorteil gespeicherter Prozeduren besteht darin, dass sie die Ausführungseffizienz der Datenbank erhöhen und den Netzwerkkommunikationsaufwand reduzieren können. Da die gespeicherte Prozedur vorkompiliert und optimiert wurde, muss sie während der Ausführung nicht wiederholt analysiert und optimiert werden und kann direkt zur Ausführung aufgerufen werden. Darüber hinaus können gespeicherte Prozeduren auch dynamische Operationen über Parameter implementieren, was nicht nur den Code vereinfacht, sondern auch Risiken wie SQL-Injection vermeidet.

Erstellung und Ausführung gespeicherter Prozeduren

Im Folgenden wird beschrieben, wie gespeicherte Prozeduren in Oracle erstellt und ausgeführt werden.

Eine gespeicherte Prozedur erstellen

In Oracle erfordert die Erstellung einer gespeicherten Prozedur die Verwendung der CREATE PROCEDURE-Anweisung. Die Syntax lautet wie folgt:

CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name [IN | OUT | IN OUT] parameter_type [, ...])]
[IS | AS]
BEGIN
      pl/sql_code_block;
END [procedure_name];

Unter ihnen:

• CREATE PROCEDURE: Die Anweisung zum Erstellen einer gespeicherten Prozedur.
• OR REPLACE: Optionaler Parameter. Wenn dieser Parameter angegeben wird, bedeutet dies, dass die erstellte gespeicherte Prozedur ersetzt wird, wenn sie bereits vorhanden ist.
• Prozedurname: Der Name der gespeicherten Prozedur.
• Parametername: Optionale Eingabe- und/oder Ausgabeparameter, die zur Angabe der Eingabe und Ausgabe der gespeicherten Prozedur verwendet werden.
• parameter_type: Der Typ des Parameters, der ein Datentyp wie VARCHAR2, NUMBER oder ein Cursortyp wie SYS_REFCURSOR sein kann.
• IS |. AS: Optionaler Parameter, der zur Angabe des Sprachtyps der gespeicherten Prozedur verwendet wird. IS repräsentiert den Anfang (PL/SQL-Block), AS repräsentiert das Ende (PL/SQL-Block).
• pl/sql_code_block: PL/SQL-Codeblock, der die spezifische Logikimplementierung der gespeicherten Prozedur enthält.

Der folgende Beispielcode zeigt, wie man eine einfache gespeicherte Prozedur erstellt, die zwei Parameter akzeptiert und deren Summe ausgibt:

CREATE OR REPLACE PROCEDURE add_nums(
    num1 IN NUMBER,
    num2 IN NUMBER,
    sum OUT NUMBER
)
IS
BEGIN
    sum := num1 + num2;
END add_nums;

Eine gespeicherte Prozedur ausführen

In Oracle erfordert die Ausführung einer gespeicherten Prozedur die Verwendung der EXECUTE- oder EXECUTE IMMEDIATE-Anweisung . Um beispielsweise das obige Beispielprogramm auszuführen, können Sie die folgende Anweisung verwenden:

DECLARE
    result NUMBER;
BEGIN
    add_nums(10, 20, result);
    DBMS_OUTPUT.PUT_LINE('The sum is: ' || result);
END;

Hier verwenden wir die DECLARE-Anweisung, um das zu verwendende Variablenergebnis zu deklarieren, rufen die gespeicherte Prozedur add_nums auf und geben das Ergebnis auf dem Bildschirm aus .

Parametertyp

In einer gespeicherten Prozedur können Parameter Eingabeparameter, Ausgabeparameter oder bidirektionale Parameter sein.

• Eingabeparameter: Geben Sie die Eingabe der gespeicherten Prozedur an.
• Ausgabeparameter: Geben Sie die Ausgabe der gespeicherten Prozedur an.
• Bidirektionale Parameter: können eingegeben oder ausgegeben werden.

Die Methode zum Deklarieren des Parametertyps ist wie folgt:

(param_name [IN | OUT | IN OUT] param_type [, ...])

In dieser Deklaration ist [IN | OUT |. Wenn der Parametertyp nicht angegeben ist, wird standardmäßig der IN-Typ verwendet, d. h. der Eingabeparameter.

Beispielcode:

CREATE OR REPLACE PROCEDURE my_proc (
    num IN NUMBER,
    str IN OUT VARCHAR2,
    cur OUT SYS_REFCURSOR
)
IS
BEGIN
    -- 逻辑实现
END my_proc;

Im obigen Code deklarieren wir eine gespeicherte Prozedur my_proc mit drei Parametern. Der erste Parameter num ist der Eingabeparameter, der zweite Parameter str ist der bidirektionale Parameter und der dritte Parameter cur sind Ausgabeparameter.

Datensatzverarbeitung

Bei der Verwendung gespeicherter Prozeduren zum Bearbeiten von Daten ist es häufig erforderlich, eine Abfrageergebnisliste zurückzugeben. Oracle bietet zwei Arten von Recordsets: Cursor und PL/SQL-Tabellen.

Cursor

Ein Cursor ist eine Datenstruktur, die eine Ergebnismenge zurückgibt, die Abfrageergebnisse durchlaufen kann. Cursor können explizit oder implizit sein. Für explizite Cursor muss eine Cursorvariable deklariert und im Code geöffnet und geschlossen werden.

Hier ist eine gespeicherte Prozedur, die die Verwendung eines Cursors demonstriert:

CREATE OR REPLACE PROCEDURE get_employee(
    id_list IN VARCHAR2,
    emp_cur OUT SYS_REFCURSOR
)
IS
BEGIN
    OPEN emp_cur FOR 'SELECT * FROM employees WHERE id IN (' || id_list || ')';
END get_employee;

In diesem Beispiel deklarieren wir eine gespeicherte Prozedur get_employee mit zwei Parametern, die eine durch Kommas getrennte Liste von Mitarbeiter-IDs als Eingabeparameter akzeptiert und einen Cursor emp_cur zurückgibt mit den ausgewählten Mitarbeiterinformationen.

PL/SQL-Tabelle

PL/SQL-Tabelle ist eine arrayartige Datenstruktur, die eine Reihe von Werten speichern kann. PL/SQL-Tabellen haben viele praktische Anwendungen in gespeicherten Prozeduren, z. B. die Übergabe eines Datensatzes an eine gespeicherte Prozedur usw.

In Oracle können PL/SQL-Tabellen deklariert und in gespeicherten Prozeduren verwendet werden, wie zum Beispiel im folgenden Code:

CREATE OR REPLACE PACKAGE my_package
IS
    TYPE num_list IS TABLE OF NUMBER INDEX BY PLS_INTEGER;

    PROCEDURE sum_nums(nums IN num_list, sum OUT NUMBER);
END my_package;

CREATE OR REPLACE PACKAGE BODY my_package
IS
    PROCEDURE sum_nums(nums IN num_list, sum OUT NUMBER)
    IS
        total NUMBER := 0;
    BEGIN
        FOR indx IN 1 .. nums.COUNT LOOP
            total := total + nums(indx);
        END LOOP;
        sum := total;
    END sum_nums;
END my_package;

Hier haben wir ein Paket namens my_package erstellt, das einen PL/SQL-Namen namens num_list deklariert. Der Tabellentyp und a gespeicherte Prozedur sum_nums, die diesen Typ verwendet. sum_nums akzeptiert ein Argument vom Typ num_list und berechnet deren Summe.

Fazit

In Oracle sind gespeicherte Prozeduren eines der wichtigen Werkzeuge zur Pflege von Datenbanken. Sie verfügen über effiziente Ausführungsfähigkeiten und Dynamik. Wir können gespeicherte Prozeduren auch verwenden, um die Ausführung einer Geschäftslogik zu ermöglichen, anstatt nur eine einzelne SQL-Anweisung auszuführen, was die Wiederverwendbarkeit und Wartbarkeit verbessern kann. Weil sie in einer Datenbank gespeichert und von mehreren Anwendungen oder Prozessen gemeinsam genutzt und abgerufen werden können. Die Verwendung gespeicherter Prozeduren bietet viele Vorteile und es ist schwierig, sie alle in einem kurzen Artikel abzudecken. Wir glauben jedoch, dass wir bei der tatsächlichen Arbeit viel davon profitieren werden, solange wir über ein umfassendes Verständnis und eine umfassende Anwendung verfügen.

Das obige ist der detaillierte Inhalt vonBeispiele für die Erstellung und Ausführung gespeicherter Prozeduren in Oracle. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!