Works > 逆引きSQL比較 > ORACLE > 手続き型言語
ORACLE
[ SQL ]
[ データベースの情報 ]
[ 手続き型言語 ]
[ その他 ]
■手続き型言語
この項で使用するサンプルテーブルについて ▲TOP
ORACLEとSQL Server系の大きな違いとは？ ▲TOP
SQL Server系（Sybase, MS）の経験者が初めてORACLEのストアドプロシージャを作った時に、おそらく面食らうであろうと思われるのが『BEGIN～ENDブロック内にSELECT文が書けない』と言う点です。
例えばTransact-SQLやWatcom-SQLでは、
create procedure sp_hogehoge @inum int as
begin
--何かの処理
--その処理結果に基づき変数やテーブルをSELECTする
select * from ksale where empno = @inum
end
と言った書き方が可能です。上記の sp_hogehoge を Access のパススルークエリーから実行する場合、
exec sp_hogehoge 7
とクエリーのSQLに書いておけば単純にテーブルやビューをSELECTした場合と同様にダイナセットを得る事ができ
ます。
しかし、ORACLE では、BEGIN～ENDブロック（PL/SQLブロック）の中に上記のようなSELECT文を書くことができま
せん。INTOを使用した値の代入、カーソル、サブクエリーにのみ使用できます。
そこでストアドを呼び出して複数行の結果を得るためには、PL/SQL表（配列）を使ったり、カーソルをバインドしたりする必要があります。
これをクライアントアプリケーションから実行するとなると、もうODBCではできないので、ミドルウェアにはネイティブなものを選択する必要があります。
（最近のODBCでは、カーソルについては、
API関数を呼ぶ事で実装できるようです）。
逆にORACLEのみをやっている人は、SQL Server系のストアドを初めて作る時に、自前配列を作ったりしないようにしましょう。
END の直前に SELECT文を書けばよいだけなので。
手続き型言語の書き方は？（BEGIN～ENDブロック） ▲TOP
ORACLE MSSQL SSA MySQL
基本的な制御の例を以下に示します。この文はストアドプログラムとしてコンパイルする必要はありません。
--PL/SQLサンプル
set serveroutput on size 1000000;
declare
--変数宣言
mystr varchar2(20);
mycnt number;
begin
--値の代入
mycnt := 0;
mystr := '文字';
--IF文
if mycnt = 0 then
mystr := '文字２';
dbms_output.put_line(mystr);
else
dbms_output.put_line('文字３');
end if;
--CASE文（ありません）
if to_char(sysdate,'mm') = '01' then
mystr := '1月';
elsif to_char(sysdate,'mm') = '02' then
mystr := '2月';
elsif to_char(sysdate,'mm') = '03' then
mystr := '3月';
elsif to_char(sysdate,'mm') = '04' then
mystr := '4月';
else
mystr := '1～4月以外';
end if;
dbms_output.put_line(mystr);
--WHILE-LOOP
while mycnt < 5 loop
mycnt := mycnt + 1;
dbms_output.put_line(mycnt);
end loop;
--FOR-LOOP
for i in 1..3 loop
dbms_output.put_line(i);
end loop;
--BEGIN～ENDブロック（PL/SQLブロック）内で、以下のようにSELECT文を発行する事はできません。
--SELECT文は、INTOを使用した値の代入、カーソル、サブクエリーでのみ使用できます。
-- × select sysdate from dual;
end;
/
ストアドプロシージャのサンプル（カーソルを使用するLOOP処理） ▲TOP
ORACLE MSSQL SSA MySQL
カーソルを使用するLOOP処理のサンプルです。
入力パラメータで指定した部署番号に該当する社員について、
役職が『スタッフ』である場合は、氏名の横に『営業スタッフ：』と表示します。
--PL/SQLサンプル（カーソルLOOPを使用するストアドプロシージャ）
set serveroutput on size 1000000;
create or replace procedure sp_hoge (inum in kemp.deptno%type)
is
--変数宣言
mystr varchar2(50);
--カーソル変数
type curref is ref cursor;
mycur curref;
--カーソル使用列
type recordset is record(
vempnm kemp.empnm%type,
vjob kemp.job%type
);
recset recordset;
begin
--カーソルオープン
open mycur for
select empnm,job from kemp where deptno = inum;
--LOOP
loop
--FETCH（行の取り出し）
fetch mycur into recset;
if mycur%NOTFOUND then
exit;
end if;
--EOFでなければ何か処理をする
if recset.vjob = 'スタッフ' then
mystr := '営業スタッフ：';
else
mystr := '';
end if;
dbms_output.put_line(mystr || recset.vempnm);
end loop;
--カーソルクローズ
close mycur;
end;
/
exec sp_hoge(1);
ストアドプロシージャのサンプル（アウトパラメータ） ▲TOP
ORACLE MSSQL SSA
プロシージャで更新を行った後、呼出し側に何らかの情報を返したい場合や、返す情報が複数ある場合、
アウトパラメータを使用します。
※因みにファンクションの中では更新をするべきではありません（RDBMSによっては可能だが好ましくない）。
--PL/SQLサンプル（OUTパラメータを使用するストアドプロシージャ）
set serveroutput on size 1000000;
create or replace procedure sp_hoge_outp (ovar out varchar2)
is
begin
--何やかやの処理
--何やかやの処理終わり
--OUTパラメータにセット
ovar := 'アウト！';
end;
/
--ストアドプロシージャ実行
var ovar varchar2(30);
exec sp_hoge_outp(:ovar);
print ovar;
--ストアドプロシージャ実行その２（無名PL/SQLブロックで確認）
declare
ovar varchar2(30);
begin
sp_hoge_outp(ovar);
dbms_output.put_line(ovar);
end;
/
ストアドプロシージャのサンプル（動的SQL） ▲TOP
ORACLE MSSQL SSA
プロシージャの中で、条件によって使用するテーブルを変えたい場合など、実行するSQLを動的に生成してSQLを実行します。
以下は、区分：１の時は部署番号に該当する社員名を返し、以外の時は区分：１の時は部署番号に該当する部署名を返す例です。
--PL/SQLサンプル（動的SQLを使用するストアドプロシージャ）
--DBMS_SQLパッケージを使用します
set serveroutput on size 1000000;
create or replace procedure sp_hoge_dyna
(kbn in varchar2,
inum in number)
is
cur number;
sqlstmt varchar2(1000);
vempno kemp.empno%type;
vname varchar2(60);
rows number;
begin
-- cursor id 取得
cur := dbms_sql.open_cursor;
--SQL文生成（区分：１の時は部署番号に該当する社員名、以外は部署名）
if kbn = '1' then
sqlstmt := 'select empnm from kemp where deptno = ' || to_char(inum);
else
sqlstmt := 'select deptnm from kdept where deptno = ' || to_char(inum);
end if;
--SQL文解析
dbms_sql.parse(cur, sqlstmt, dbms_sql.NATIVE);
--列の型定義
dbms_sql.define_column(cur, 1, vname, 40);
--カーソルオープン
rows := dbms_sql.execute(cur);
loop
--FETCH（行の取り出し）
if dbms_sql.fetch_rows(cur) > 0 then
dbms_sql.column_value(cur, 1, vname);
dbms_output.put_line(vname);
else
exit;
end if;
end loop;
--カーソルクローズ
dbms_sql.close_cursor(cur);
end;
/
--ストアドプロシージャ実行（部署番号：１の社員表示）
exec sp_hoge_dyna('1',1);
--ストアドプロシージャ実行（部署番号：１の部署名表示）
exec sp_hoge_dyna('2',1);
ストアドプロシージャのサンプル（一時テーブル） ▲TOP
ORACLE MSSQL SSA
ストアドプロシージャの中で一時的に使用するためのテーブルの使い方。
社員名の一覧をプロシージャ内で一時格納し、クライアント側に返す例です。
--PL/SQLサンプル（配列を使用するストアドプロシージャ）
--※R8.0以前では一時テーブルを作成できません（8i以降では可能です）。
-- 代替方法として、配列を使用する例を挙げますが、実際には、以下のような対処を行う場合もあります。
--
-- 配列を使用しない、一時表使用の代替方法
-- (1) 一時表に主キーとしてセッションIDを付加したイメージを持つ実表Aを作成する
-- (2) セッション単位でユニークとなるセッションIDを取得する
-- (3) 実表Aに対し、処理を行う。この時セッションIDをセットする事で、セッション間の競合を避ける
-- (4) ストアドプロシージャ完了後、別プロセスから実表Aを、セッションIDをキーに検索する
--
set serveroutput on size 1000000;
--Objectsオプション使用可能でない場合、クライアント側で配列を受け取るためには、通常のプロシージャ
--ではなく、パッケージとして定義する必要があります。
create or replace package pk_hoge
is
--PL/SQL表（ORACLEで配列を実装するためのレガシーな手法）
type recordset is record(vempnm kemp.empnm%type);
type tabarray is table of recordset index by binary_integer;
--プロシージャの定義
procedure sp_hoge_tmp(empnmt in out tabarray);
end pk_hoge;
/
create or replace package body pk_hoge is
procedure sp_hoge_tmp(empnmt in out tabarray)
is
--変数宣言
cnt number(3) := 0;
--カーソル変数
type curref is ref cursor;
mycur curref;
--カーソル使用列
recset recordset;
begin
--カーソルオープン
open mycur for select empnm from kemp;
--LOOP
loop
--FETCH（行の取り出し）
fetch mycur into recset;
if mycur%NOTFOUND then
exit;
end if;
cnt := cnt + 1;
--PL/SQL表に格納
empnmt(cnt).vempnm := recset.vempnm;
end loop;
--カーソルクローズ
close mycur;
end;
end pk_hoge;
/
--ストアドパッケージ実行
declare
empnmt pk_hoge.tabarray;
i number;
begin
pk_hoge.sp_hoge_tmp(empnmt);
-- PL/SQL表を表示
for i in 1 .. empnmt.last loop
dbms_output.put_line(empnmt(i).vempnm);
end loop;
end;
/
ストアドプロシージャのサンプル（外部コマンド・アプリを実行） ▲TOP
ORACLE MSSQL SSA
ストアドプロシージャから外部のコマンドやアプリケーションを実行します。
悪魔のストアドxp_cmdshellが標準で用意されているSQL Server（Sybase, Microsoft）
と異なり、ORACLEの場合、ストアドから外部コマンドを実行するには、
結構面倒な手順が必要です。
まず、.dll や .so などのライブラリを自前で作る必要があります。
（API関数の直読みは可能）
以下の例は、Windows版ORACLEのストアド側で文字列を引数として渡し、
.dll側でその文字列をOSコマンドとして実行する、言わば、ORACLE版xp_cmdshellの簡易版です。
。。。このサンプルだと、標準出力がSQL*Plusから見えないのが残念なところですが。(^^;
execstr.cpp
#include <windows.h>
extern "C"
{
int WINAPI _declspec(dllexport) doit(char* cmdstr);
};
int WINAPI _declspec(dllexport) doit(char* cmdstr)
{
//実際に使う上ではスレッドの制御とか色々あるでしょうが。。。
system(cmdstr);
}
これをコンパイルしてdllを作成（この例ではbcc32を使用しています）
↓
execstr.dll作成
ORACLE側では、まず、tnsnames.ora, listener.oraの設定が必要です。
この辺はマニュアルで「外部プロシージャ」のところを参照。
但し、8i以降でデフォルトインストールなら既設定されているはず（多分）。
--ライブラリの作成
create or replace library execstr is 'C:\bcc\execstr\execstr.dll';
/
--dllの関数を実行するストアドファンクション作成
--名前は、xp_cmdshellのパクリ。xfの意味は、eXtremeFunction（笑）
create or replace function xf_remoteshell(
cmdstr varchar2
) return binary_integer
as
external
library execstr
name "doit"
language C;
/
--dllの関数を実行するストアドファンクションを実行
declare
ret binary_integer;
begin
ret := xf_remoteshell('dir c:\*.txt > c:\xfremote.txt');
end;
/
ストアドプロシージャのサンプル（テーブルからテキストファイルへ出力） ▲TOP
ORACLE MSSQL
UTL_FILEパッケージを使用します。
declare
--ファイル関連変数
file utl_file.file_type;
filename varchar2(80);
vline varchar2(2000);
dl varchar2(1) := ','; -- delim:comma
--カーソル変数
type curref is ref cursor;
mycur curref;
--カーソル使用列
type recordset is record(
vcustno kcust.custno%type,
vcustnm kcust.custnm%type
);
recset recordset;
begin
--ファイルオープン
filename := 'cust.txt';
dbms_output.put_line(filename);
file := utl_file.fopen('/',filename,'w');
--カーソルオープン
open mycur for
select custno,custnm from kcust;
--LOOP
loop
--FETCH（行の取り出し）
fetch mycur into recset;
if mycur%NOTFOUND then
exit;
end if;
vline := recset.vcustno || dl || recset.vcustnm;
--ファイル出力
utl_file.put_line(file,vline);
end loop;
--カーソルクローズ
close mycur;
--ファイルクローズ
utl_file.fclose(file);
exception
when OTHERS then
dbms_output.put_line(SQLERRM);
end;
/
ニッチな方法としては、別項の
外部のデータベースに接続するには？（異機種間接続）
を利用するのも可（ハマりそうなので仕事ではやらない方が(笑)）。
ストアドプロシージャのサンプル（テキストファイルからテーブルへ入力） ▲TOP
ORACLE MSSQL
UTL_FILEパッケージを使用します。
以下のテキストファイルnewcust.txtを用意
newcust.txt
101,新規顧客１株式会社
102,新規顧客２株式会社
103,新規顧客３株式会社
104,新規顧客４株式会社
declare
--ファイル関連変数
file utl_file.file_type;
filename varchar2(80);
vline varchar2(2000);
--COMMA_TO_TABLE用変数
vtablen BINARY_INTEGER;
vtab DBMS_UTILITY.uncl_array;
--テーブル用変数
vcustno kcust.custno%type;
vcustnm kcust.custnm%type;
begin
--ファイルオープン
filename := 'newcust.txt';
dbms_output.put_line(filename);
file := utl_file.fopen('/',filename,'R');
--LOOP
begin
loop
--テキストファイルから行の取り出し
utl_file.get_line(file,vline);
--エラー回避のためのちょっとしたトリック※
vline := 'x' || vline;
--COMMA_TO_TABLEでCSV書式の変数をPL/SQL表に格納
vtablen := 0;
--dbms_utility.comma_to_table(vline,vtablen,vtab);
vcustno := substrb(vtab(1),2,60);
vcustnm := vtab(2);
--newcust.txtの内容をkcustにINSERTする
insert into kcust(custno,custnm) values (vcustno,vcustnm);
end loop;
exception
when NO_DATA_FOUND then
--ファイルEOFまで正常に読み込み完了
null;
when OTHERS then
dbms_output.put_line(SQLERRM);
end;
--ファイルクローズ
utl_file.fclose(file);
exception
when OTHERS then
dbms_output.put_line(SQLERRM);
end;
/
ここでは、CSV書式から配列変数への代入に、comma_to_tableを使用しています。
バージョンやパッチの有無によるようですが、このcomma_to_tableを使用する時、
列の書式が数値と判断される場合、エラーになる事があります。
この例では、先頭のみ数値なので、
ダミー的に'x'を付加する事によってエラーを回避していますが、
バージョンやパッチでの回避ができない
（もしくは、comma_to_tableがないバージョン）時は、
後述のsplit関数を使用すればよいでしょう。
ニッチな方法としては、別項の
外部のデータベースに接続するには？（異機種間接続）
を利用するのも可（ハマりそうなので仕事ではやらない方が(笑)）。
ストアドプロシージャのサンプル（メールを送信する） ▲TOP
ORACLE MSSQL
UTL_SMTPパッケージを使用します。
declare
vsubject varchar2(255) := 'mail title'; --タイトル
vbody varchar2(1000) := 'honbun'; --本文
vcrlf varchar2(2) := chr(13) || chr(10);
vsender varchar2(30) := 'email@uwaaaa.com'; --送信元
vrcpt varchar2(30) := 'email@uwaaaa.com'; --宛先
vmailhost varchar2(30) := 'smtp.uheeee.jp';
mail_conn utl_smtp.connection;
begin
mail_conn := utl_smtp.open_connection(vmailhost, 25);
utl_smtp.helo(mail_conn, vmailhost);
utl_smtp.mail(mail_conn, vsender);
utl_smtp.rcpt(mail_conn, vrcpt);
utl_smtp.open_data(mail_conn);
utl_smtp.write_data(mail_conn,'TO: ' || vrcpt || vcrlf);
utl_smtp.write_data(mail_conn,'SUBJECT: ' || vsubject || vcrlf);
utl_smtp.write_data(mail_conn,vcrlf);
utl_smtp.write_data(mail_conn,vbody);
utl_smtp.close_data(mail_conn);
utl_smtp.quit(mail_conn);
exception
when OTHERS then
dbms_output.put_line(SQLERRM);
end;
/
日本語の扱いについては、WRITE_RAW_DATAプロシージャ、UTL_ENCODEパッケージ、
UTL_RAWパッケージの合わせ技で比較的簡単にできますが、8.1.6以前だと、
これらがないため、ちょっと難しいですね。
※メールについては、UTL_TCPパッケージを使う方法もあります。
※前述の「外部プロシージャ」を使って、コマンドラインのSMTPメーラやsendmailするシェルをキックする、と言った方法もありでしょう。
ストアドプロシージャのサンプル（COM Automation 呼び出し） ▲TOP
ORACLE MSSQL
Windows版限定ですが、COM呼び出しが使えます。
CreateObject時のサーバーを指定すればリモートのDCOMもOKのはず。（未検証）
/* COM Automation（ORDCOM）サンプル */
/* Excelファイルは事前に消しておく */
/* ↓ */
ho del C:\test\ordcom.xls;
set serveroutput ON;
DECLARE
dummyToken binary_integer;
hr binary_integer;
servername varchar2(255) := '';
objXls binary_integer := -1;
workBooks binary_integer := -1;
workBook binary_integer := -1;
workSheet binary_integer:=-1;
workSheet1 binary_integer:=-1;
range binary_integer:=-1;
lines binary_integer:=-1;
filename varchar2(255) := 'c:\test\ordcom.xls';
error_src varchar2(255);
error_description varchar2(255);
error_helpfile varchar2(255);
error_helpID binary_integer;
BEGIN
/* オブジェクト生成 */
dbms_output.put_line('CreateObject');
hr := ORDCOM.CreateObject('Excel.Application', 0, servername,objXls);
/* エラーハンドリング割愛
IF hr != 0 THEN
ORDCOM.GetLastError(error_src, error_description, error_helpfile, error_helpID);
dbms_output.put_line(error_src);
dbms_output.put_line(error_description);
dbms_output.put_line(error_helpfile);
END IF;
*/
dbms_output.put_line('Add WorkBooks');
hr := ORDCOM.GetProperty(objXls, 'WorkBooks', 0, workBooks);
ORDCOM.InitArg();
ORDCOM.SetArg(-4167,'I4');
hr := ORDCOM.Invoke(workBooks, 'Add', 1, workBook);
dbms_output.put_line('Add WorkSheet');
ORDCOM.InitArg();
ORDCOM.SetArg('Sheet 1','BSTR');
hr := ORDCOM.GetProperty(objXls, 'WorkSheets', 0, workSheet1);
hr := ORDCOM.Invoke(workSheet1, 'Add', 0, workSheet);
/* セルに値をセット */
dbms_output.put_line('setValue');
ORDCOM.InitArg();
ORDCOM.SetArg('A1', 'BSTR');
hr := ORDCOM.GetProperty(workSheet, 'Range', 1, range);
hr := ORDCOM.SetProperty(range, 'Value', 'EmpCode', 'BSTR');
hr := ORDCOM.DestroyObject(range);
ORDCOM.InitArg();
ORDCOM.SetArg('B1', 'BSTR');
hr := ORDCOM.GetProperty(workSheet, 'Range', 1, range);
hr := ORDCOM.SetProperty(range, 'Value', 'EmpName', 'BSTR');
hr := ORDCOM.DestroyObject(range);
/* セルに値をセット */
dbms_output.put_line('setValue');
ORDCOM.InitArg();
ORDCOM.SetArg('A2', 'BSTR');
hr := ORDCOM.GetProperty(workSheet, 'Range', 1, range);
hr := ORDCOM.SetProperty(range, 'Value', 'E001', 'BSTR');
hr := ORDCOM.DestroyObject(range);
ORDCOM.InitArg();
ORDCOM.SetArg('B2', 'BSTR');
hr := ORDCOM.GetProperty(workSheet, 'Range', 1, range);
hr := ORDCOM.SetProperty(range, 'Value', '山田太郎', 'BSTR');
hr := ORDCOM.DestroyObject(range);
/* 書式変更 */
dbms_output.put_line('書式変更');
ORDCOM.InitArg();
ORDCOM.SetArg('A1:B1', 'BSTR');
hr := ORDCOM.GetProperty(workSheet, 'Range', 1, range);
hr := ordcom.GetProperty(range, 'Font', 0, lines);
hr := ordcom.SetProperty(lines, 'ColorIndex', 23, 'I2');
hr := ordcom.DestroyObject(lines);
hr := ordcom.GetProperty(range, 'Font', 0, lines);
hr := ordcom.SetProperty(lines, 'Bold', 1, 'I2');
hr := ordcom.DestroyObject(lines);
hr := ordcom.GetProperty(range, 'Font', 0, lines);
hr := ordcom.SetProperty(lines, 'Name', 'Century', 'BSTR');
hr := ordcom.DestroyObject(lines);
/* 書式変更終わり */
hr := ORDCOM.DestroyObject(range);
/* ファイル保存 */
dbms_output.put_line('SaveAs');
ORDCOM.InitArg();
ORDCOM.SetArg(filename,'BSTR');
hr := ORDCOM.Invoke(workBook, 'SaveAs', 1, DummyToken);
/* 後処理 */
dbms_output.put_line('DestroyObjects');
hr := ORDCOM.DestroyObject(WorkSheet);
hr := ORDCOM.DestroyObject(WorkSheet1);
ORDCOM.InitArg();
ORDCOM.SetArg(FALSE,'BOOL');
hr := ORDCOM.Invoke(workBook, 'Close', 0, DummyToken);
hr := ORDCOM.DestroyObject(workBook);
ORDCOM.InitArg();
hr := ORDCOM.Invoke(workBooks, 'Close', 0, DummyToken);
hr := ORDCOM.DestroyObject(workBooks);
hr := ORDCOM.Invoke(objXls, 'Quit', 0, DummyToken);
hr := ORDCOM.DestroyObject(objXls);
hr := ORDCOM.DestroyObject(dummyToken);
END;
/
オブジェクトがキレイに破棄されないと、実行時に、EXCEL.EXEのゾンビが残ります。
タスクマネージャなどでも殺せない場合は、リブートするか、
Process Walkerなどのプロセス制御ツールを使う必要があります。
CSVなどの任意のデリミタで区切られたテキストから任意のカラムを抽出する関数 ▲TOP
任意のデリミタで区切られたテキストから任意のカラムの内容を返す関数。
以前は Loopさせて作って
いたのですが、
掲示板でLoopしない方法を発見。関数にしてみました。
-- split.sql
-- intext : 入力文字列
-- itemno : カラム番号
-- delim : デリミタ
-- Usage :split(intext,itemno,delim)
--
-- SQL> select split('AA,BBB,CCC',2,',') from dual;
--
create or replace function split
(intext varchar2,
itemno integer,
delim varchar2) return varchar2
is
myitem varchar2(255);
begin
if intext is null or itemno is null or delim is null then
return 'Invalid parameters !';
end if;
if itemno = 1 then
myitem := SUBSTR( intext, 1, INSTR( intext || delim, delim, 1, 1 ) - 1 );
else
myitem := SUBSTR( intext, INSTR( intext, delim, 1, itemno-1 ) + 1,
INSTR( intext || delim, delim, 1, itemno ) - INSTR( intext, delim, 1, itemno-1 ) - 1);
end if;
return myitem;
end;
/
カレンダースクリプト ▲TOP
何か泥臭いけど一応動きます。
next_day と 日付+1 の組み合わせの方が低コストだったかな。
SQLAREAを圧迫するのであまり使わない方がいいかも（じゃ作るなって(^_^;）
まっ、使うならストアドにしてね。
-- cal.sql
-- Usage :
-- SQL> @cal
--
set linesize 200;
set ARRAYSIZE 10;
set verify off;
set feedback off
set serveroutput on size 24000;
declare
firstDate date;
today date;
today2 date;
currDate date;
currDay varchar2(10);
currYM varchar2(6);
todayYM varchar2(6);
todayDD varchar2(2);
mark1 varchar2(1);
mark2 varchar2(1);
i NUMBER;
lastDayNum NUMBER;
type ow is record (
sun varchar2(4) := ' ** ',
mon varchar2(4) := ' ** ',
tue varchar2(4) := ' ** ',
wed varchar2(4) := ' ** ',
thu varchar2(4) := ' ** ',
fri varchar2(4) := ' ** ',
sat varchar2(4) := ' ** ');
oneWeek ow;
oneWeekTitle1 varchar2(40) := ' 日 月 火 水 木 金 土';
oneWeekTitle2 varchar2(40) := ' --------------------------';
BEGIN
today := trunc(sysdate);
todayYM := substr(to_char(today,'YYYYMMDD'),1,6);
todayDD := to_char(today,'DD');
for cnt in 1 .. 4 loop
if cnt = 1 then
today2 := add_months(today,-1);
elsif cnt = 2 then
today2 := today;
elsif cnt = 3 then
today2 := add_months(today,1);
else
today2 := add_months(today,2);
end if;
currYM := substr(to_char(today2,'YYYYMMDD'),1,6);
lastDayNum := to_number(to_char(last_day(today2),'DD'));
dbms_output.put_line('**** ' || substr(currYM,1,4) || '年 ' || substr(currYM,5,2) || '月 ************************');
dbms_output.put_line(oneWeekTitle1);
dbms_output.put_line(oneWeekTitle2);
for i in 1 .. lastDayNum loop
currDate := to_date(currYM || to_char(i),'YYYYMMDD');
currDay := to_char(currDate,'DAY');
--当日の前後にカッコ "{}" を付ける
if todayDD = to_char(currDate,'DD') and todayYM = currYM then
mark1 := '{';
mark2 := '}';
else
mark1 := ' ';
mark2 := ' ';
end if;
if currDay = '日曜日' then
oneWeek.sun := mark1 || to_char(currDate,'DD') || mark2;
elsif currDay = '月曜日' then
oneWeek.mon := mark1 || to_char(currDate,'DD') || mark2;
elsif currDay = '火曜日' then
oneWeek.tue := mark1 || to_char(currDate,'DD') || mark2;
elsif currDay = '水曜日' then
oneWeek.wed := mark1 || to_char(currDate,'DD') || mark2;
elsif currDay = '木曜日' then
oneWeek.thu := mark1 || to_char(currDate,'DD') || mark2;
elsif currDay = '金曜日' then
oneWeek.fri := mark1 || to_char(currDate,'DD') || mark2;
else /* 土曜日 → 表示してクリア */
oneWeek.sat := mark1 || to_char(currDate,'DD') || mark2;
mark1 := ' ';
mark2 := ' ';
dbms_output.put_line(' ' || oneWeek.sun || oneWeek.mon || oneWeek.tue ||
oneWeek.wed || oneWeek.thu || oneWeek.fri || oneWeek.sat );
oneWeek.sun := ' ** ';
oneWeek.mon := ' ** ';
oneWeek.tue := ' ** ';
oneWeek.wed := ' ** ';
oneWeek.thu := ' ** ';
oneWeek.fri := ' ** ';
oneWeek.sat := ' ** ';
end if;
end loop;
if currDay <> '土曜日' then
dbms_output.put_line(' ' || oneWeek.sun || oneWeek.mon || oneWeek.tue ||
oneWeek.wed || oneWeek.thu || oneWeek.fri || oneWeek.sat );
end if;
dbms_output.put_line(chr(9));
oneWeek.sun := ' ** ';
oneWeek.mon := ' ** ';
oneWeek.tue := ' ** ';
oneWeek.wed := ' ** ';
oneWeek.thu := ' ** ';
oneWeek.fri := ' ** ';
oneWeek.sat := ' ** ';
end loop;
exception
when others then
dbms_output.put_line(substr(sqlerrm,1,55));
END;
/
拡張CEIL関数（任意の小数点で切り上げ） ▲TOP
任意の小数点を指定できるCEIL（切り上げ）関数
-- exceil.sql
-- innum : 入力数値
-- point : 切り上げ点
-- Usage :exceil(innum,point)
--
-- SQL> select exceil(innum,point) from dual;
--
create or replace function exceil
(innum in number,
point in number) return number
is
vnum1 number;
vnum2 number;
vnum3 number;
rslt number;
begin
vnum1 := trunc(innum,point);
vnum2 := trunc(innum,point + 1);
if vnum1 - vnum2 = 0 then
rslt := vnum1;
else
vnum3 := 1 * power(0.1 , point);
rslt := vnum1 + vnum3;
end if;
return rslt;
end;
/
結果はこうなります。
EXCEIL(10.123,0)
----------------
11
EXCEIL(10.123,1)
----------------
10.2
EXCEIL(10.123,2)
----------------
10.13
EXCEIL(10.123,3)
----------------
10.123
CSVの変数を配列に一発で入れる ▲TOP
SQL*Plus から
SQL> desc DBMS_UTILITY
を実行して、表示されるサブプログラム中にCOMMA_TO_TABLEがあれば、以下のサンプルが使えます。
-- csvtoarray.sql
-- Usage :
-- SQL> @csvtoarray
--
declare
vLIST VARCHAR2(227);
vTABLEN BINARY_INTEGER;
vTAB DBMS_UTILITY.uncl_array;
begin
vLIST := 'aaa,bbb,ccc';
vTABLEN := 0;
DBMS_UTILITY.COMMA_TO_TABLE(vLIST,vTABLEN,vTAB);
dbms_output.put_line(vLIST);
dbms_output.put_line(vTABLEN);
dbms_output.put_line(vTAB(1));
dbms_output.put_line(vTAB(2));
dbms_output.put_line(vTAB(3));
end;
/
２次元配列の例 ▲TOP
以下のサンプルはEMPNO,EMPNMを持つテーブルempを想定した例です。
実際にはテーブルを使ってないので、SQL*Plusから流してそのまま動きます。
Enterprise版であれば、TYPEオブジェクトを使用してもっと単純にできます。
-- nijigen.sql
-- Usage :
-- SQL> @nijigen
--
--■パッケージ仕様部
create or replace package pkg_nijigen is
--行イメージのTYPE宣言
TYPE rowimage is RECORD(
EMPNO number(1),
EMPNM varchar2(10)
);
--行イメージを２次元配列にする
TYPE myArray IS VARRAY(10) OF rowimage;
--行イメージを２次元配列にする（record型ではなく%rowtypeを使う場合）
--TYPE myArray IS VARRAY(10) OF emp%rowtype;
--２次元配列を引数にしたプロシージャの宣言
procedure test(p_varray in out myArray);
end pkg_nijigen;
/
--■パッケージ本体
create or replace package body pkg_nijigen is
--２次元配列を引数にしたプロシージャの実装
procedure test(p_varray in out myArray) is
begin
--２次元配列の参照
dbms_output.put_line(p_varray(1).EMPNO);
dbms_output.put_line(p_varray(1).EMPNM);
dbms_output.put_line(p_varray(2).EMPNO);
dbms_output.put_line(p_varray(2).EMPNM);
end test;
end pkg_nijigen;
/
--■動作確認テスト
set serveroutput on size 1000000;
declare
testArray pkg_nijigen.myArray;
begin
--配列初期化
testArray := pkg_nijigen.myArray();
--配列拡張
testArray.Extend(10);
--配列に代入
testArray(1).EMPNO := 1;
testArray(1).EMPNM := 'A';
testArray(2).EMPNO := 2;
testArray(2).EMPNM := 'B';
--パッケージプロシージャの実行（配列の参照）
pkg_nijigen.test(testArray);
end;
/
カーソル変数をアウトパラメータとして使用する例 ▲TOP
アプリケーションから使用する方法については、
データベース接続あれこれ
を参考にしてください。
-- outcur.sql
-- Usage :
-- SQL> @outcur
--
--■パッケージ仕様部
create or replace package pkg_cur is
TYPE curtype is ref cursor;
--カーソル変数をアウトパラメータを引数にしたプロシージャの宣言
procedure prc_getcur (ocur out curtype);
end pkg_cur;
/
--■パッケージ本体
create or replace package body pkg_cur is
--カーソル変数をアウトパラメータを引数にしたプロシージャの実装
procedure prc_getcur (ocur out curtype) is
cur1 curtype;
begin
open cur1 for select * from kemp;
ocur := cur1;
end prc_getcur;
end pkg_cur;
/
--■動作確認テスト
set serveroutput on size 1000000;
declare
cur1 pkg_cur.curtype;
recset kemp%rowtype;
begin
--パッケージプロシージャの実行
pkg_cur.prc_getcur(cur1);
fetch cur1 into recset;
dbms_output.put_line(recset.empnm);
end;
/
このページの先頭へ
ORACLE
[ SQL ]
[ データベースの情報 ]
[ 手続き型言語 ]
[ その他 ]
Works > 逆引きSQL比較 > ORACLE > 手続き型言語