Cursor und dynamisches SQL

Cursorkategorien: statische Cursor (bezieht sich auf Cursor, die während der Kompilierung statisch an eine SELECT-Anweisung gebunden sind. Dieser Cursor kann nur auf eine Abfrageanweisung reagieren) und dynamische Cursor (das heißt, wir möchten, dass unsere Abfrageanweisung so ist). Zur Laufzeit an den Cursor gebunden. Um einen dynamischen Cursor zu verwenden, muss die Cursorvariable deklariert werden.

Es gibt zwei Arten von dynamischen Cursorn, nämlich den starken Typ und den schwachen Typ. Ein stark typisierter dynamischer Cursor kann nur Abfrageanweisungen unterstützen, deren Abfrageergebnisse mit seinem Typ übereinstimmen, während ein schwach typisierter dynamischer Cursor jede Abfrageanweisung unterstützen kann.

Statische Cursor werden in zwei Typen unterteilt: implizite Cursor und explizite Cursor. Ein Anzeigecursor ist ein Cursor, der vom Benutzer deklariert und bedient wird. Ein impliziter Cursor ist ein Cursor, der von Oracle automatisch für alle Datenmanipulationsanweisungen deklariert wird.

In jeder Benutzersitzung können wir mehrere Cursor gleichzeitig öffnen. Diese Anzahl wird durch den Parameter OPEN CURSORS in der Datenbankinitialisierungsparameterdatei definiert.

Schritte zum Anzeigen eines Cursors:

1. Deklarieren Sie einen expliziten Cursor, Syntax: CURSORISSELECT;

Beim Deklarieren eines Cursors Normalerweise Einige Variablen werden auch deklariert, um die von der Abfrageanweisung generierten Abfrageergebnisse zu speichern. Deklarieren Sie Cursor und Variablen in der Deklaration. Normalerweise werden zuerst Variablen und dann Cursor deklariert.

2. Öffnen Sie den Cursor. Beginnend mit dem Öffnen des Cursors werden die folgenden Schritte ausgeführt. Syntax: open; Wenn der Cursor geöffnet wird, wird die Abfrageanweisung ausgeführt. Die Abfrageergebnisse werden im Oracle-Puffer abgelegt und der Cursor zeigt dann auf die erste Zeile der Abfrageergebnisse im Puffer.

3. Durch Extrahieren des Cursors zeigt der Cursor nacheinander auf jede Zeile des Abfrageergebnisses. Syntax: FETCHINTO;

4. Schließen Sie den Cursor, Syntax: CLOSE;

Beispiel:

declare
 name varchar2(50);                               --定义变量存储employees表中的内容。
 department_name varchar2(20);               --定义变量存储departments表中的内容；
 cursor emp_cur IS                               --定义游标emp_cur
select name,department_name               --选出所有员工的姓名和所做部门。
from employees e,departments d
  where e.department_id=d.department_id;
begin
 open emp_cur;                                      --打开游标
 LOOP
 FETCH emp_cur INTO name,depart_name;      --将第一行数据放入变量中，游标后移。
      EXIT WHEN emp_cur%NOTFOUND;
      dbms_output.put_line(name||’在’||department_name);
 END LOOP;
 CLOSE emp_cur;
END;

Cursorattribute: %ISOPEN, ob der Cursor auf eine gültige Zeile zeigt; %NOTFOUND, ob der Cursor nicht auf eine gültige Zeile zeigt; %ROWCOUNT, die Anzahl der vom Cursor extrahierten Zeilen.

Syntax: Cursorname% Attributname.

Zum Beispiel: Das Unternehmen geht an die Börse und beschließt, das Gehalt der Mitarbeiter zu erhöhen. Die Gehaltserhöhung beträgt 100 Yuan für mehr als ein Jahr Beschäftigung, und das Gehalt ist auf 1.000 Yuan begrenzt.

declare
 hire_date date;              --存放员工入职日期
 e_id number;                   --存放员工id
 cursor emp_cur is            --定义游标
      select id,hire_date from employees;
begin
 open emp_cur;                 --打开游标
 loop
      fetch emp_cur into e_id,hire_date       --将数据逐条存入变量
      exit when emp_cur%NOTFOUND;
      if 100*(2014-to_char(hire_date,’yyyy’))<1000 then
             update salary setsalaryvalue=salaryvalue+100*(2010-to_char(hire_date,’yyyy’)) where employee.id=e_id;
      else
             update salary setsalaryvalue=salaryvalue+1000 where employee.id=e_id;
      end if;
 end loop;
      close emp_cur;
end

Schleifencursor zum Lesen verwenden, Syntax: FOR

DECLARE
      CURSOR emp_cur IS
      SELECT name,department_name
      FROM employees e,departments d;
      WHERE e.department_id=d.department_id;
BEGIN
      FOR employ_record IN emp_cur LOOP
             dnms_output.put_line(employ_record.name||’在’||employee_record.department_name);
      END LOOP;
END;

Der Unterschied zwischen implizitem Cursor und explizitem Cursor: 1. Es ist nicht erforderlich, einen Cursor zu deklarieren. 2. Der Cursor muss nicht geöffnet und geschlossen werden. 3. Die INTO-Klausel muss verwendet werden und das Ergebnis kann nur eins sein.

Implizite Cursor sind die gleichen wie explizite Cursor: Sie haben die gleichen Attribute für implizite Cursor, indem man SQL% vor dem Attributnamen hinzufügt, also SQL%FOUND, SQL%. ISOPEN usw.

Da das Ergebnis der impliziten Cursorabfrage nur eine Zeile enthält, macht es beim Zählen wenig Sinn. Daher wird das %ROECOUNT-Attribut häufig verwendet, um zu bestimmen, ob das Einfügen, Löschen und Aktualisieren erfolgreich ist , muss aber vorher in der COMMIT-Anweisung verwendet werden. Wenn %ROECOUNT nach COMMIT nur 0 sein kann;

DECLARE
      name VARCHAR2(50);
      department_name varchar(20);
BEGIN
      SELECT name,department_name
      INTO name,deprtment_name
      FROM employees e,departments d;
      WHERE e.department_id=d.department_id and e.id=1;
      dbms_output.put_line(name||’在’||department_name);
END;

REF dynamischer Cursor

begin
      update employees set name=name||’A’
      where id=7;
      if sql%rowcount=1 then
             dbms_output.put_line(‘表已更新！’);
      else
             dbms_output.put_line(‘编号未找到’);
      end if;
end;

ref dynamischer Cursor kann zur Laufzeit verschiedenen Anweisungen zugeordnet werden und ist dynamisch. Der dynamische Ref-Cursor wird zum Verarbeiten mehrzeiliger Abfrageergebnismengen verwendet. Der dynamische Ref-Cursor ist eine Variable vom Typ Ref, ähnlich einem Zeiger.

Dynamischen Ref-Cursortyp definieren: Typ ist Ref-Cursor-Rückgabe ;

Dynamischer Ref-Cursor öffnen: OPEN FOR ; RETURN-Anweisung.

Schwach typisierter dynamischer Ref-Cursor: Dynamischer REF-Cursor ohne RETURN-Anweisung.

Zum Beispiel:

Drucken Sie Informationen basierend auf Benutzereingaben (Mitarbeiter, Abteilung)

DECLARE
      TYPE refcur_t IS REF CURSOR
      RETURN employess%ROWTYPE;
      refcur refcur_t;
      v_emp employees%ROWTYPE;
BEGIN
      OPEN refcur FOR
      SELECT * FROM employees;
      LOOP
             FETCH refcur INTO v_emp;
             EXIT WHEN refcur%NOTFOUND;
             dbms_output.put_line(refcur%ROWCOUNT||’‘||v_emp.name);
      END LOOP;
      CLOSE refcur;
END;

Erstellen Sie dynamische SQL-Anweisungen.

Statisches SQL, zur Kompilierungszeit ermittelt.

Dynamisches SQL, nicht kompiliert, wird dynamisch während der Ausführung bestimmt; die SQL-Anweisung kann basierend auf Benutzereingabeparametern usw. bestimmt werden; löst das Problem, dass DDL-Anweisungen in PL/SQL nicht unterstützt werden.

DECLARE
      TYPE refcur_t IS REF CURSOR
       refcur refcur_t;
      e_id number;
      e_name varchar2(50);
BEGIN
      OPEN refcur FOR
      SELECT id,name FROM employees;
      FETCH refcur INTO e_id,e_name;
      WHILE refcur%FOUND LOOP
             dbms_output.put_line(‘#’||e_id||’:’||e_name);
             FETCH refcur INTO e_id,e_name;
      END LOOP;
      CLOSE refcur;
END;

SQL-Anweisungssyntax zum Erstellen dynamischer DML.DDL:

EXECUTEIMMEDIATE 'DML, DDL-Anweisung'; [INTO] [USING ]; Anweisung, die eine Zeile oder 0 Zeilen zurückgibt.

DECLARE
      TYPE refcur_t IS REF CURSOR;
      refcur refcur_t;
      p_id NUMBER;
      p_name VARCHAR2(50);
      selection VARCHAR2(1) :=UPPER(SUBSTR(‘&tab’,1,1));
BEGIN
      IF selection = ‘E’ THEN
             OPEN refcur FOR
                    SELECT id,name FROMemployees;
             dbms_output.put_line(‘===员工信息===’);
      ELSEIF selection = ‘D’ THEN
             OPEN refcur FOR
                    SELECTdepartment_id,department_name FROM departments;
             dbms_output.put_line(‘===部门信息===’);
      ELSE
             dbms_output.put_line(‘请输入员工信息E或部门信息D’);
             RETURN;
      END IF;
      FETCH refcur INTO p_id,p_name;
      WHILE refcur%FOUND LOOP
             dbms_output.put_line(‘#’||p_id||’:’||p_name);
             FETCH refcur INTO p_id,p_name;
      END LOOP;
      CLOSE refcur;
END;

Wenn die folgende Anweisung eine SELECT-Anweisung ist, können Sie die into-Klausel verwenden, um den von der SELECT-Anweisung ausgewählten Datensatzwert zu erhalten. Dabei kann es sich um eine Folge von Variablen oder um eine Datensatztypvariable handeln, bei der es sich um eine Datensatztypvariable handelt. Wenn die SQL-Anweisung Parameter enthält, die dynamisch ermittelt werden müssen, verwenden wir die USING-Klausel, mit der die Eingabeparametervariablen gebunden werden. Wenn die SQL-Anweisung Parameter enthält, verwenden Sie „:Parametername“

Beispiel: Tabelle dynamisch erstellen

Beispiel: Telefonnummer eines Mitarbeiters dynamisch abfragen

Beispiel: Datensätze dynamisch einfügen

Die EXECUTEIMMEDIATE-Anweisung kann nur eine Zeile oder nichts zurückgeben. Wenn Sie eine SQL-Anweisung schreiben, die mehrere Zeilen zurückgibt, können Sie den dynamischen Ref-Cursor verwenden: OPEN Cursor_Name FOR [USING ];

Beispiel: Mitarbeiterinformationen dynamisch ausgeben, deren Gehalt einen bestimmten Betrag überschreitet

BEGIN
      EXECUTE IMMEDIATE
             ‘CREATE TABLE bonus(id NUMBER,amtNUMBER)’;
END;

DECLARE
      sql_stmt VARCHAR2(200);
      emp_id NUMBER(10) :=’&emp_id’;
      emp_rec employees%ROWTYPE;
BEGIN
      sql_stmt :=’select * from employees WHEREid =:id’;
      EXECUTE IMMEDIATE sql_stmt INTO emp_recUSING emp_id;
END;