Oracleデータベース・カーソルの使用方法の迅速な把握

10148 ワード

oracle sql C++ c 同前

明示的カーソル
クエリが結果を1行以上返すと、明示的なカーソルが必要になります.select into文は使用できません.PL/SQLは暗黙カーソルを管理し、クエリー開始時に暗黙カーソルが開き、クエリー終了時に暗黙カーソルが自動的に閉じる.明示的なカーソルは、PL/SQLブロックの宣言部分で宣言され、実行部分または例外処理部分で開き、データを取り出し、閉じます.
カーソルの使用
ここで宣言します.カーソルは通常、明示的なカーソルを指します.したがって、これからは特に指定されていない場合、カーソルは明示的なカーソルを指します.プログラムでカーソルを使用するには、まずカーソルを宣言する必要があります.
カーソルの宣言
構文:

　　CURSOR cursor_name IS select_statement;

PL/SQLでのカーソル名は宣言されていない変数であり、カーソル名に値を付けたり式で使用したりすることはできません.
例:


　　DELCARE

　　CURSOR C_EMP IS SELECT empno,ename,salary

　　FROM emp

　　WHERE salary>2000

　　ORDER BY ename;

　　........

　　BEGIN

　　
カーソル定義のSELECT文では、必ずしもテーブルをビューにする必要はありません.また、複数のテーブルまたはビューから選択したカラム、または*を使用してすべてのカラムを選択することもできます.
カーソルを開く
カーソルの値を使用する前に、カーソルを開き、カーソルを開いてクエリー処理を初期化する必要があります.カーソルを開く構文は次のとおりです.
　　

OPEN cursor_name。

　　cursor_nameは、宣言セクションで定義されたカーソル名です.
例:
　　OPEN C_EMP; カーソルを閉じます.
構文:
　　

CLOSE cursor_name

例:
　　CLOSE C_EMP; カーソルからデータを抽出します.カーソルから1行のデータを取得するには、FETCHコマンドを使用します.データを抽出するたびに、カーソルは結果セットの次のローを指します.構文は次のとおりです.
　　

FETCH cursor_name INTO variable[,variable,...]

SELECTで定義されたカーソルの各列について、FETCH変数リストには変数が対応している必要があります.変数のタイプも同じです.
例:


　　SET SERVERIUTPUT ON

　　DECLARE

　　v_ename EMP.ENAME%TYPE;

　　v_salary EMP.SALARY%TYPE;

　　CURSOR c_emp IS SELECT ename,salary FROM emp;

　　BEGIN

　　OPEN c_emp;

　　FETCH c_emp INTO v_ename,v_salary;

　　DBMS_OUTPUT.PUT_LINE('Salary of Employee'|| v_ename ||'is'|| v_salary);

　　FETCH c_emp INTO v_ename,v_salary;

　　DBMS_OUTPUT.PUT_LINE('Salary of Employee'|| v_ename ||'is'|| v_salary);

　　FETCH c_emp INTO v_ename,v_salary;

　　DBMS_OUTPUT.PUT_LINE('Salary of Employee'|| v_ename ||'is'|| v_salary);

　　CLOSE c_emp;

　　END

　
このコードは間違いなく面倒です.複数のローが結果を返す場合は、ループを使用してカーソル属性をループを終了する条件として使用できます.この方法でデータを抽出すると、プログラムの可読性と簡潔性が大幅に向上します.次に、ループを使用して上記のプログラムを書き直します.


　　SET SERVERIUTPUT ON

　　DECLARE

　　v_ename EMP.ENAME%TYPE;

　　v_salary EMP.SALARY%TYPE;

　　CURSOR c_emp IS SELECT ename,salary FROM emp;

　　BEGIN

　　OPEN c_emp;

　　LOOP

　　FETCH c_emp INTO v_ename,v_salary;

　　EXIT WHEN c_emp%NOTFOUND;

　　DBMS_OUTPUT.PUT_LINE('Salary of Employee'|| v_ename ||'is'|| v_salary);

　　END

　　
レコード変数
TYPEコマンドと%ROWTYPEを使用するレコード変数を定義します.%ROWsTYPEの詳細は、関連資料を参照してください.
レコード変数は、カーソルからデータ行を抽出するために使用され、カーソルが複数のカラムを選択した場合、カラムごとに1つの変数を宣言するよりも、レコードを使用する方が便利です.
テーブルで%ROWTYPEを使用し、カーソルから取り出した値をレコードに挿入する場合、テーブル内のすべてのカラムを選択する場合は、すべてのカラムを列挙するよりもSELECT句で*を使用します.
例:


　　SET SERVERIUTPUT ON

　　DECLARE

　　R_emp EMP%ROWTYPE;

　　CURSOR c_emp IS SELECT * FROM emp;

　　BEGIN

　　OPEN c_emp;

　　LOOP

　　FETCH c_emp INTO r_emp;

　　EXIT WHEN c_emp%NOTFOUND;

　　DBMS_OUT.PUT.PUT_LINE('Salary of Employee'||r_emp.ename||'is'|| r_emp.salary);

　　END LOOP;

　　CLOSE c_emp;

　　END;

　　%ROWTYPE          ，              ：

　　SET SERVERIUTPUT ON

　　DECLARE

　　CURSOR c_emp IS SELECT ename,salary FROM emp;

　　R_emp c_emp%ROWTYPE;

　　BEGIN

　　OPEN c_emp;

　　LOOP

　　FETCH c_emp INTO r_emp;

　　EXIT WHEN c_emp%NOTFOUND;

　　DBMS_OUT.PUT.PUT_LINE('Salary of Employee'

　　||r_emp.ename||'is'|| r_emp.salary);

　　END LOOP;

　　CLOSE c_emp;

　　END;

　
パラメータ付きカーソル
ストアド・プロシージャや関数と同様に、カーソルにパラメータを渡してクエリーで使用できます.これは、ある条件でカーソルを開く場合に便利です.構文は次のとおりです.


　　CURSOR cursor_name[(parameter[,parameter],...)] IS select_statement;

　　
パラメータを定義する構文は次のとおりです.
　　

Parameter_name [IN] data_type[{:=|DEFAULT} value]

ストアド・プロシージャとは異なり、カーソルは渡された値のみを受け入れ、値を返すことはできません.パラメータはデータ型のみを定義し、サイズはありません.
また、パラメータにデフォルト値を設定し、パラメータ値がカーソルに渡されない場合はデフォルト値を使用します.カーソルで定義されたパラメータはプレースホルダにすぎず、他の場所で参照するパラメータは必ずしも信頼性がありません.
カーソルを開くときにパラメータに値を割り当てます.構文は次のとおりです.
　

　OPEN cursor_name[value[,value]....];

パラメータ値は、テキストまたは変数です.
例:


　　DECALRE

　　CURSOR c_dept IS SELECT * FROM dept ORDER BY deptno;

　　CURSOR c_emp (p_dept VARACHAR2) IS

　　SELECT ename,salary

　　FROM emp

　　WHERE deptno=p_dept

　　ORDER BY ename

　　r_dept DEPT%ROWTYPE;

　　v_ename EMP.ENAME%TYPE;

　　v_salary EMP.SALARY%TYPE;

　　v_tot_salary EMP.SALARY%TYPE;

　　BEGIN

　　OPEN c_dept;

　　LOOP

　　FETCH c_dept INTO r_dept;

　　EXIT WHEN c_dept%NOTFOUND;

　　DBMS_OUTPUT.PUT_LINE

　　('Department:'|| r_dept.deptno||'-'||r_dept.dname);

　　v_tot_salary:=0;

　　OPEN c_emp(r_dept.deptno);

　　LOOP

　　FETCH c_emp INTO v_ename,v_salary;

　　EXIT WHEN c_emp%NOTFOUND;

　　DBMS_OUTPUT.PUT_LINE

　　('Name:'|| v_ename||' salary:'||v_salary);

　　v_tot_salary:=v_tot_salary+v_salary;

　　END LOOP;

　　CLOSE c_emp;

　　DBMS_OUTPUT.PUT_LINE

　　('Toltal Salary for dept:'|| v_tot_salary);

　　END LOOP;

　　CLOSE c_dept;

　　END;

　
カーソルFORループ
ほとんどの場合、プログラムを設計するときは次の手順に従います.
1、カーソルを開きます.
2、ループを開始します.
3、カーソルから値を取ります.
4、その行は戻される.
5、処理.
6、サイクルを閉じる.
7、カーソルを閉じます.
このクラスのコードを単純にカーソルと呼んでループに使用することができます.しかし、このタイプとは異なるループもあります.これはFORループです.FORループのカーソルは通常の宣言に従って宣言されます.その利点は、明示的に開く、閉じる、データを取る、データの存在をテストする、データを格納する変数を定義するなどする必要がないことです.カーソルFORループの構文は次のとおりです.


　　FOR record_name IN

　　(corsor_name[(parameter[,parameter]...)]

　　| (query_difinition)

　　LOOP

　　statements

　　END LOOP;

　　     for         ：

　　DECALRE

　　CURSOR c_dept IS SELECT deptno,dname FROM dept ORDER BY deptno;

　　CURSOR c_emp (p_dept VARACHAR2) IS

　　SELECT ename,salary

　　FROM emp

　　WHERE deptno=p_dept

　　ORDER BY ename

　　v_tot_salary EMP.SALARY%TYPE;

　　BEGIN

　　FOR r_dept IN c_dept LOOP

　　DBMS_OUTPUT.PUT_LINE

　　('Department:'|| r_dept.deptno||'-'||r_dept.dname);

　　v_tot_salary:=0;

　　FOR r_emp IN c_emp(r_dept.deptno) LOOP

　　DBMS_OUTPUT.PUT_LINE

　　('Name:' || v_ename || 'salary:' || v_salary);

　　v_tot_salary:=v_tot_salary+v_salary;

　　END LOOP;

　　DBMS_OUTPUT.PUT_LINE

　　('Toltal Salary for dept:'|| v_tot_salary);

　　END LOOP;

　　END;

　　
カーソルFORループでのクエリーの使用
カーソルFORループではクエリーを定義できます.明示的な宣言がないため、カーソルに名前がなく、レコード名はカーソルクエリーで定義されます.


　　DECALRE

　　v_tot_salary EMP.SALARY%TYPE;

　　BEGIN

　　FOR r_dept IN (SELECT deptno,dname FROM dept ORDER BY deptno) LOOP

　　DBMS_OUTPUT.PUT_LINE('Department:'|| r_dept.deptno||'-'||r_dept.dname);

　　v_tot_salary:=0;

　　FOR r_emp IN (SELECT ename,salary

　　FROM emp

　　WHERE deptno=p_dept

　　ORDER BY ename) LOOP

　　DBMS_OUTPUT.PUT_LINE('Name:'|| v_ename||' salary:'||v_salary);

　　v_tot_salary:=v_tot_salary+v_salary;

　　END LOOP;

　　DBMS_OUTPUT.PUT_LINE('Toltal Salary for dept:'|| v_tot_salary);

　　END LOOP;

　　END;

　　
カーソル内のサブクエリ
構文は次のとおりです.


　　CURSOR C1 IS SELECT * FROM emp

　　WHERE deptno NOT IN (SELECT deptno

　　FROM dept

　　WHERE dname!='ACCOUNTING');

　
SQLのサブクエリと変わらないことがわかります.
カーソルの更新と削除
UPDATE文とDELETE文を使用して、PL/SQLでもデータ行を更新または削除できます.明示的なカーソルは、複数行のデータを取得する必要がある場合にのみ使用されます.PL/SQLは、カーソルを使用するだけでレコードの削除や更新を行う方法を提供します.
UPDATE文またはDELETE文のWHERE CURRENT OFサブストリングは、UPDATEまたはDELETE操作を実行するテーブルから取り出した最近のデータを専門に処理します.この方法を使用するには、カーソルを宣言するときにFOR UPDATEサブストリングを使用する必要があります.ダイアログでFOR UPDATEサブストリングを使用してカーソルを開くと、すべての戻りセットのデータ行が行レベル(ROW-LEVEL)独占ロックされ、他のオブジェクトはこれらのデータ行のみをクエリーでき、UPDATE、DELETE、SELECTはできません.FOR UPDATE動作.
構文:


　　FOR UPDATE [OF [schema.]table.column[,[schema.]table.column]..

　　[nowait]

マルチテーブルクエリでは、OF句を使用して特定のテーブルをロックします.OF句を無視すると、すべてのテーブルで選択したデータ行がロックされます.これらのデータ行が他のセッションでロックされている場合、通常、ORACLEはデータ行がロック解除されるまで待機します.
UPDATEおよびDELETEでWHERE CURRENT OFサブストリングを使用する構文は次のとおりです.

　　WHERE{CURRENT OF cursor_name|search_condition}
　　 ：

　　DELCARE

　　CURSOR c1 IS SELECT empno,salary

　　FROM emp

　　WHERE comm IS NULL

　　FOR UPDATE OF comm;

　　v_comm NUMBER(10,2);

　　BEGIN

　　FOR r1 IN c1 LOOP

　　IF r1.salary<500 THEN

　　v_comm:=r1.salary*0.25;

　　ELSEIF r1.salary<1000 THEN

　　v_comm:=r1.salary*0.20;

　　ELSEIF r1.salary<3000 THEN

　　v_comm:=r1.salary*0.15;

　　ELSE

　　v_comm:=r1.salary*0.12;

　　END IF;

　　UPDATE emp;

　　SET comm=v_comm

　　WHERE CURRENT OF c1l;

　　END LOOP;

　　END

PHPで簡単にEXCELファイルの生成を実現

phpフィルタhtmlspecialchars()関数は、予め定義された文字をHTMLエンティティの用法分析に変換することを実現する