カーソルと動的SQL

カーソル カテゴリ: 静的カーソル (コンパイル中に選択ステートメントに静的にバインドされるカーソルを指します。このカーソルは 1 つのクエリ ステートメントにのみ作用します) および動的カーソル (つまり、クエリ ステートメントが実行されていることを望みます。バインドされているだけです)動的カーソルを使用するには、カーソル変数を宣言する必要があります)。

動的カーソルは、強いタイプと弱いタイプの 2 つのタイプに分類されます。強く型指定された動的カーソルは、クエリ結果がその型と一致するクエリ ステートメントのみをサポートしますが、弱く型指定された動的カーソルは任意のクエリ ステートメントをサポートできます。

静的カーソルは、暗黙的カーソルと明示的カーソルの 2 つのタイプに分類されます。表示カーソルは、ユーザーが宣言して操作するカーソルです。暗黙的カーソルは、すべてのデータ操作ステートメントに対して Oracle によって自動的に宣言されるカーソルです。

各ユーザーのセッションでは、同時に複数のカーソルを開くことができます。この数は、データベース初期化パラメータ ファイルの OPEN CURSORS パラメータによって定義されます。

カーソルを表示する手順:

1. 明示的なカーソルを宣言します。構文: CURSOR<カーソル名>ISSELECT;

カーソルを宣言するときは、通常、クエリを保存するための変数も宣言する必要があります。ステートメント。カーソルと変数はdeclareで宣言します。通常は変数が最初に宣言され、次にカーソルが宣言されます。

2. カーソルを開くことから始まり、開始と終了で次の手順が実行されます。構文: open; カーソルがオープンされると、クエリ ステートメントの実行が開始され、クエリ結果が Oracle バッファに配置され、カーソルはバッファ内のクエリ結果の最初の行を指します。

3. カーソルを抽出します。カーソルを抽出すると、カーソルはクエリ結果の各行を順番に指します。構文: FETCH<カーソル名>INTO<変数リスト>

4. カーソルを閉じます。構文: CLOSE<カーソル名>;

例:

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;

カーソル属性: %ISOPEN、カーソルが開いているかどうか。カーソルが有効な行を指しているかどうか、%NOTFOUND、カーソルが有効な行を指していないかどうか、%ROWCOUNT、カーソルによって抽出された行の数。

構文: カーソル名%属性名。

例: 会社が上場し、従業員の給与を増額することを決定しました。従業員が 1 年以上雇用されている場合、給与は 100 人民元増加し、給与の上限は 1,000 人民元となります。

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

ループ カーソルを使用して読み取ります。構文: FOR IN LOOP – データの各行を操作します。END LOOP;

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;

暗黙的カーソル

暗黙的カーソルと明示的カーソルの違い: 1.カーソルを宣言する必要はありません。 2. カーソルを開いたり閉じたりする必要はありません。 3. INTO 句を使用する必要があり、結果は 1 つだけです。

暗黙的カーソルは明示的カーソルと同じです。同じ属性を持ちます。暗黙的カーソルの属性を使用するには、属性名の前に SQL% を追加します (SQL%FOUND、SQL%ISOPEN など)。

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;

暗黙的なカーソル クエリの結果には 1 行しかないため、カウントに使用してもあまり意味がありません。そのため、%ROECOUNT 属性は挿入、削除、更新が成功したかどうかを判断するためによく使用されますが、COMMIT ステートメントの前に使用されます。 。 COMMIT 後、%ROECOUNT が 0 のみになる場合、

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 動的カーソル

ref 動的カーソルは、実行時にさまざまなステートメントに関連付けることができます。 ref ダイナミック カーソルは、複数行のクエリ結果セットを処理するために使用されます。ref ダイナミック カーソルは、ポインタに似た ref 型の変数です。

参照動的カーソルのタイプを定義します: type<タイプ名> は参照カーソル return <戻り値のタイプ>;

参照動的カーソルを宣言します: <カーソル名>;

参照動的カーソルを開きます: OPEN<カーソル名> FOR ;

例:

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;

Strong型ref動的カーソル: RETURN文を使用したREF動的カーソル。

弱い型指定の ref 動的カーソル: RETURN ステートメントのない REF 動的カーソル。

例:

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;

情報を印刷する

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;

ユーザー入力 (従業員、部門) に基づいて動的 SQL ステートメントを作成します。

静的 SQL。コンパイル時に決定されます。

コンパイルされない動的 SQL は、実行中に動的に決定されます。SQL ステートメントは、ユーザー入力パラメーターなどに基づいて決定できます。これにより、PL/SQL で DDL ステートメントがサポートされないという問題が解決されます。

動的 DML.DDL を作成するための SQL ステートメント構文:

EXECUTEIMMEDIATE 'DML, DDL ステートメント'; [INTO<変数シーケンス>] [USING <パラメータ シーケンス>]; は 1 行または 0 行を返すステートメントのみを実行できます。

次のステートメントが select ステートメントの場合、into 句を使用して、select ステートメントによって選択されたレコード値を受け取ることができます。一連の変数、またはレコード タイプ変数であるレコード タイプ変数を指定できます。 SQL ステートメントに動的に決定する必要があるパラメーターがある場合は、入力パラメーター変数をバインドするために使用される USING 句を使用します。 SQL ステートメントにパラメーターがある場合は、「:パラメーター名」を使用します

例: テーブルを動的に作成する

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;

例: レコードを動的に挿入する

DECLARE
      Sql_stmt varchar2(200);
      emp_id NUMBER(10) := ‘&emp_id’;
      emp_rec employees%ROWTYPE;
BEGIN
      sql_stmt := ‘INSERT INTO employees(id)values(:id)’;
      EXECUTE IMMEDIATE sql_stmt USING emp_id;
      Dbms_output.put_line(emp.rec.phone);
END;

EXECUTEIMMEDIATE ステートメントは戻り値のみを返します1 行でも何もなくても、複数の行を返す SQL ステートメントを作成する場合は、ref 動的カーソルを使用できます。 OPEN カーソル名 FOR 例: 動的に一定金額以上の給与を出力 従業員情報

DECLARE
      e_id NUMBER(10);
      e_name VARCHAR2(50);
      s_salary NUMBER(8);
      TYPE c_type is REF CURSOR;
      cur c_type;
      p_salary NUMBER := ‘&p_id’;
BEGIN
      OPEN cur FOR ‘selecte.id,e.name,e.salaryvalue from employees e,salary s where e.id=s.employeeid ands.salaryvalue >:sal ORDER BY id ASC’;
      USING p_salary;
      dbms_output.put_line(‘薪水大于’||p_salary||’的员工有：’);
      LOOP
             FETCH cur INTOe_id,e_name,e_salary;
             EXIT WHEN cur%NOTFOUND;
             dbms_output.put_line(‘编号：’||e_id||’姓名：’||e_name||’薪水：’||e_salary);
END LOOP
      CLOSE cur;
END;