Oracleストアド・プロシージャおよびカーソル
3906 ワード
一.きおくてつづき
1、ストアド・プロシージャ定義ストアド・プロシージャ(Stored Procedure)とは、特定のデータベース機能を完了するためのSQL文セットのグループであり、このSQL文セットはコンパイルされてデータベース・システムに格納される.使用時に、ユーザーは定義されたストレージ・プロシージャの名前を指定し、対応するストレージ・プロシージャ・パラメータを指定して呼び出し、実行することで、1つまたは複数のデータベース・オペレーションを完了します.
2.ストアド・プロシージャの作成Oracleストアド・プロシージャには、プロシージャ宣言、プロシージャ実行セクション、ストアド・プロシージャ例外の3つのセクションがあります.
1)非パラメトリック・ストアド・プロシージャ構文:
create or replace procedure NoParPro as//声明;begin//実行;Exception//ストレージ・プロシージャ異常; end;
2)パラメトリック・ストレージ・プロシージャの例
create or replace procedure queryempname(sfindno emp.empno%type) as sName emp.ename%type; sjob emp.job%type; begin .... exception .... end;
3)パラメータ付き記憶過程の付加価値方式
create or replace procedure runbyparmeters (isal in emp.sal%type, sname out varchar, sjob in out varchar) as icount number; begin select count(*) into icount from emp where sal>isal and job=sjob; if icount=1 then .... else .... end if; exception when too_many_rows then DBMS_OUTPUT.PUT_LINE('戻り値が1行より多い'); when others then DBMS_OUTPUT.PUT_LINE('RUNBAYPARMETERS中にエラー!'); end;
詳細:
ここで、パラメータINは入力パラメータを表し、パラメータのデフォルトモードである.OUTは、任意のOracleの正当なタイプを使用できる戻り値パラメータを表します.OUTモードで定義されたパラメータは、プロシージャ内でのみ付与されます.このパラメータは、ある値をコールバックに渡すことができることを示します.このパラメータは、プロシージャに値を渡すことができるか、ある値を渡すことができることを示します.
二.カーソル
1.カーソルの概念
カーソルは、実際には、複数のデータレコードを含む結果セットから1つのレコードを毎回抽出できるメカニズムです.カーソルはポインタとして機能します.カーソルは結果のすべてのローを巡回できますが、彼は一度に1つのローだけを指します.
2.カーソルの役割
要約すると、SQLのカーソルは、データベース・テーブルに格納されているデータ・ローのコピーや、データベースに格納されているデータ・ローへのポインタを指す一時的なデータベース・オブジェクトです.カーソルは、テーブル内のデータを行単位で操作する方法を提供します.カーソルの一般的な用途の1つは、後で使用するためにクエリー結果を保存することです.カーソルの結果セットはSELECT文によって生成され、プロセスでレコードセットを繰り返し使用する必要がある場合は、データベースの繰り返しクエリよりも1回のカーソルを作成して何回も繰り返し使用します.ほとんどのプログラムデータ設計言語では、カーソルを使用してSQLデータベースのデータを取得できます.プログラムに埋め込まれたカーソルは、プログラムに埋め込まれたSQL文と同じです.
1)ストアド・プロシージャ・カーソル定義使用:as//定義(カーソルの遍歴可能な結果セット)CURSOR cur_1 IS SELECT area_code,CMCODE,SUM(rmb_amt)/10000 rmb_amt_sn, SUM(usd_amt)/10000 usd_amt_sn FROM BGD_AREA_CM_M_BASE_T WHERE ym >= vs_ym_sn_beg AND ym <= vs_ym_sn_end GROUP BY area_code,CMCODE; begin//実行(常用For文遍歴カーソル)FOR rec IN cur_1 LOOP UPDATE xxxxxxxxxxx_T SET rmb_amt_sn = rec.rmb_amt_sn,usd_amt_sn = rec.usd_amt_sn WHERE area_code = rec.area_code AND CMCODE = rec.CMCODE AND ym = is_ym; END LOOP;
2)カーソル定義
--cursorの処理declareを表示---cursorを宣言し、sqlワークスペースcursor cursor_を作成し、名前を付けます.name is select real_name from account_hcz; v_realname varchar2(20); begin open cursor_name;---cursorを開き、sql文で生成された結果セットfetch cursor_を実行します.name into v_realname;--cursorを抽出し、結果セットのレコードdbms_を抽出するoutput.put_line(v_realname); close cursor_name;--cursor endを閉じます.
三.Oracleでのストアド・プロシージャの呼び出しの使用
1)呼び出し方式1
declare realsal emp.sal%type; realname varchar(40); realjob varchar(40); begin//プロシージャ呼び出し開始realsal:=1100; realname:=''; realjob:='CLERK'; runbyparmeters(realsal,realname,realjob);－－順番DBMS_が必要ですOUTPUT.PUT_LINE(REALNAME||' '||REALJOB); END;//プロシージャ呼び出し終了
2)呼び出し方式2
declare realsal emp.sal%type; realname varchar(40); realjob varchar(40); begin//プロシージャ呼び出し開始realsal:=1100; realname:=''; realjob:='CLERK'; -指定値は変数順序可変runbyparmeters(sname=>realname,isal=>realsal,sjob=>realjob)に対応します. DBMS_OUTPUT.PUT_LINE(REALNAME||' '||REALJOB); END;//プロシージャ呼び出し終了
3)呼び出し方式3(SQLコマンドライン下)
1、ストアド・プロシージャ定義ストアド・プロシージャ(Stored Procedure)とは、特定のデータベース機能を完了するためのSQL文セットのグループであり、このSQL文セットはコンパイルされてデータベース・システムに格納される.使用時に、ユーザーは定義されたストレージ・プロシージャの名前を指定し、対応するストレージ・プロシージャ・パラメータを指定して呼び出し、実行することで、1つまたは複数のデータベース・オペレーションを完了します.
2.ストアド・プロシージャの作成Oracleストアド・プロシージャには、プロシージャ宣言、プロシージャ実行セクション、ストアド・プロシージャ例外の3つのセクションがあります.
1)非パラメトリック・ストアド・プロシージャ構文:
create or replace procedure NoParPro as//声明;begin//実行;Exception//ストレージ・プロシージャ異常; end;
2)パラメトリック・ストレージ・プロシージャの例
create or replace procedure queryempname(sfindno emp.empno%type) as sName emp.ename%type; sjob emp.job%type; begin .... exception .... end;
3)パラメータ付き記憶過程の付加価値方式
create or replace procedure runbyparmeters (isal in emp.sal%type, sname out varchar, sjob in out varchar) as icount number; begin select count(*) into icount from emp where sal>isal and job=sjob; if icount=1 then .... else .... end if; exception when too_many_rows then DBMS_OUTPUT.PUT_LINE('戻り値が1行より多い'); when others then DBMS_OUTPUT.PUT_LINE('RUNBAYPARMETERS中にエラー!'); end;
詳細:
ここで、パラメータINは入力パラメータを表し、パラメータのデフォルトモードである.OUTは、任意のOracleの正当なタイプを使用できる戻り値パラメータを表します.OUTモードで定義されたパラメータは、プロシージャ内でのみ付与されます.このパラメータは、ある値をコールバックに渡すことができることを示します.このパラメータは、プロシージャに値を渡すことができるか、ある値を渡すことができることを示します.
二.カーソル
1.カーソルの概念
カーソルは、実際には、複数のデータレコードを含む結果セットから1つのレコードを毎回抽出できるメカニズムです.カーソルはポインタとして機能します.カーソルは結果のすべてのローを巡回できますが、彼は一度に1つのローだけを指します.
2.カーソルの役割
要約すると、SQLのカーソルは、データベース・テーブルに格納されているデータ・ローのコピーや、データベースに格納されているデータ・ローへのポインタを指す一時的なデータベース・オブジェクトです.カーソルは、テーブル内のデータを行単位で操作する方法を提供します.カーソルの一般的な用途の1つは、後で使用するためにクエリー結果を保存することです.カーソルの結果セットはSELECT文によって生成され、プロセスでレコードセットを繰り返し使用する必要がある場合は、データベースの繰り返しクエリよりも1回のカーソルを作成して何回も繰り返し使用します.ほとんどのプログラムデータ設計言語では、カーソルを使用してSQLデータベースのデータを取得できます.プログラムに埋め込まれたカーソルは、プログラムに埋め込まれたSQL文と同じです.
1)ストアド・プロシージャ・カーソル定義使用:as//定義(カーソルの遍歴可能な結果セット)CURSOR cur_1 IS SELECT area_code,CMCODE,SUM(rmb_amt)/10000 rmb_amt_sn, SUM(usd_amt)/10000 usd_amt_sn FROM BGD_AREA_CM_M_BASE_T WHERE ym >= vs_ym_sn_beg AND ym <= vs_ym_sn_end GROUP BY area_code,CMCODE; begin//実行(常用For文遍歴カーソル)FOR rec IN cur_1 LOOP UPDATE xxxxxxxxxxx_T SET rmb_amt_sn = rec.rmb_amt_sn,usd_amt_sn = rec.usd_amt_sn WHERE area_code = rec.area_code AND CMCODE = rec.CMCODE AND ym = is_ym; END LOOP;
2)カーソル定義
--cursorの処理declareを表示---cursorを宣言し、sqlワークスペースcursor cursor_を作成し、名前を付けます.name is select real_name from account_hcz; v_realname varchar2(20); begin open cursor_name;---cursorを開き、sql文で生成された結果セットfetch cursor_を実行します.name into v_realname;--cursorを抽出し、結果セットのレコードdbms_を抽出するoutput.put_line(v_realname); close cursor_name;--cursor endを閉じます.
三.Oracleでのストアド・プロシージャの呼び出しの使用
1)呼び出し方式1
declare realsal emp.sal%type; realname varchar(40); realjob varchar(40); begin//プロシージャ呼び出し開始realsal:=1100; realname:=''; realjob:='CLERK'; runbyparmeters(realsal,realname,realjob);－－順番DBMS_が必要ですOUTPUT.PUT_LINE(REALNAME||' '||REALJOB); END;//プロシージャ呼び出し終了
2)呼び出し方式2
declare realsal emp.sal%type; realname varchar(40); realjob varchar(40); begin//プロシージャ呼び出し開始realsal:=1100; realname:=''; realjob:='CLERK'; -指定値は変数順序可変runbyparmeters(sname=>realname,isal=>realsal,sjob=>realjob)に対応します. DBMS_OUTPUT.PUT_LINE(REALNAME||' '||REALJOB); END;//プロシージャ呼び出し終了
3)呼び出し方式3(SQLコマンドライン下)
1、SQL>exec proc_emp(' 1',' 2');//
2、SQL>var vsal number
SQL> exec proc_emp (' 1',:vsal);//
：call proc_emp (' 1',:vsal);//