約 838,522 件
https://w.atwiki.jp/ora_tips/pages/31.html
PL/SQLの特徴 ・手続き型構造とSQLの統合 非手続言語のSQLにIF文やLOOP文を使用して制御構造をもたせることができる。 PL/SQLでは、すべてのSQLコマンドが実行可能 ・高いパフォーマンス 複数のSQLを一つのブロックにまとめてOracleに投げることができるので効率がよく ネットワーク通信量を抑えることができる。 ・高い移植性 PL/SQLで作成されたアプリは、OSやプラットフォームに依存しない。 他のプラットホームでも利用可能。 PL/SQLを利用して作成可能なオブジェクト ・プロシジャ 特定のアクションを実行するサブプログラム ・ファンクション 値を計算するサブプログラム ・パッケージ サブプログラムをグループ化したオブジェクト Oracle提供のパッケージはビルトインパッケージ 例）DBMS_OUTPUT ・・・ テキストメッセージをDBバッファに送信 DBMS_OUTPUT.PUT_LINE( OK ); ※利用前に以下を実施 set serveroutput on DBMS_STATS ・・・ 統計情報の取得（表示、変更） ・トリガー 表、ビュー、スキーマ、データベースに対応ずけられ特定の操作、動作をしたときに暗黙的に 実行されるプログラム。 起動されるイベント ①表、ビューに対するDML ②DDL ③ユーザのログイン、ログオフ ④データベース起動、停止 ⑤データベースに発生したエラー 構文 DECLARE ---宣言部 Oracleのデータ型を利用 %TYPE（列の属性取得） var dept.deptno%TYPE %ROWTYPE（行取得） d_row dept.%ROWTYPE BEGIN ---処理部 EXCEPTION ---例外処理部 END; 例） set serveroutput on DECLARE var NUMBER; BEGIN var = 10; DBMS_OUTPUT.PUT_LINE(var); var = var + 12; DBMS_OUTPUT.PUT_LINE(var); END; /
https://w.atwiki.jp/lookworld/pages/52.html
PL/SQL バッチ
https://w.atwiki.jp/honeybe/pages/9.html
PL/SQL 雑記 独立トランザクション PL/SQLの宣言部(ISからBEGINの間)に次のように記述する。 PRAGMA AUTONOMOUS_TRANSACTION; メインのトランザクションから独立したトランザクションとなる。 プロシージャ(orファンクション)を抜けた時点でトランザクションは開放される。commitしてなければrollbackされると言うこと。 デッドロックに注意。 CURSORの中身を配列に保持。 LOOOP内でCURSORのOPEN,CLOSEを繰り返さないために。 --カーソル定義 CURSOR C01 IS SELECT ID, NAME FROM HOGE_TABLE; --ROWTYPE定義 R01 C01%ROWTYPE; --コレクション型の定義 TYPE ARRAY IS TABLE OF C01%ROWTYPE; LIST ARRAY = ARRAY(); --コレクションの初期化 I NUMBER = 0; --添え字の定義 OPEN C01; LOOP FETCH C01 INTO R01; EXIT WHEN C01%NOTFOUND; --フェッチ終了したら抜ける。 I = I+1; --添え字をインクリメント LIST.EXTEND(I); --Rowを保持するために配列を拡張する。 LIST(I) = R01; --拡張した領域にRow情報を代入。 END LOOP; CLOSE C01; FOR X IN 1 .. I --こんな感じにカーソルを閉じた後もアクセス可能。 DBMS_OUTPUT.PUT_LINE(LIST(X).ID || LIST(X).NAME); END LOOP; 他にもっといいやり方知っている方、教えてください。 ストアド実行 引数、戻り値無しの場合。 exec hoge 引数有り、戻り値無しの場合。 exec hoge(1,2,3); 引数有り、戻り値有りの場合。 declare x integer; begin x = hoge; end; / 無名プロシージャ宣言と実行。 他のやり方がわかりません。 何かあればどうぞ 名前 コメント
https://w.atwiki.jp/kittyc/pages/6.html
SQLとPL／SQLの違い SQL データの集合の先頭から順番に処理を行うのではない。 1回に1種類のSQL文を発行することしか出来ない 複数の操作を順番に処理していく PL/SQL SQLをベースとしてオラクル社が独自に開発したもの SQLにプログラミング手法である「手続き」の機能を持ち込んで、手続き型言語とした。 PL／SQLの３つの特徴 +SQLのサポート 手続きの中に、直接SQLを記述できる プログラミング言語に用意されているデータ型とSQLが規定しているデータ型が異なる場合、 それを変換してやる必要がある。 しかし、PL／SQLはデータ型もOacleと同じデータ型であるため、型変換を行う必要がない +ブロック構造化言語 構造化プログラミングを言語としてサポートしている。 １つのサブルーチンの中をさらにブロック化することができる。 +データベースと同一のメモリ空間で動作 データベースと同一のメモリ空間で動作する DBにSQL文を渡す⇒解釈し実行⇒結果を何らかの手続きで処理 といった一連のなかでプロセス間通信を行う必要がない。 ↓ パフォーマンス的に有利 PL/SQLが使われる環境 製品の名前でわなく、言語の名前 +ストアドプログラムとして利用 頻繁に利用される処理をストアドプログラムとしてOracleに格納。 ストアドプログラムには、ストアドプロシージャ、ストアドファンクションがある。 Developer製品の言語として利用
https://w.atwiki.jp/cz8686/pages/12.html
[Oracle]PL／SQL 処理時間計測 DECLARE TIME_BEFORE BINARY_INTEGER; TIME_AFTER BINARY_INTEGER; BEGIN TIME_BEFORE = DBMS_UTILITY.GET_TIME; --//処理実行 TIME_AFTER = DBMS_UTILITY.GET_TIME; DBMS_OUTPUT.PUT_LINE(TIME_AFTER - TIME_BEFORE); END; / --//テストプロシージャ CREATE OR REPLACE PROCEDURE HATASYS1.TEST_SHORI_JIKAN IS TIME_BEFORE BINARY_INTEGER; TIME_AFTER BINARY_INTEGER; BEGIN --//100分の1ミリ秒単位で取得 TIME_BEFORE = DBMS_UTILITY.GET_TIME; FOR i IN 1..100 LOOP DBMS_OUTPUT.PUT_LINE(i); END LOOP; TIME_AFTER = DBMS_UTILITY.GET_TIME; DBMS_OUTPUT.PUT_LINE(TO_CHAR( (TIME_AFTER - TIME_BEFORE)/100) || sec ); END; [Oracle]PL/SQL 日付(YYYY,MM,DD)妥当性チェック /*********************************************************** このFUNCTIONでは、引数の年､月、日より その日付が 妥当であるかを判定します。 返り値：BOOLEAN ***********************************************************/ FUNCTION DATE_CHECK ( W_YEAR VARCHAR2 = NULL, W_MONTH VARCHAR2 = NULL, W_DATE VARCHAR2 = NULL ) RETURN BOOLEAN IS MONTH1 CONSTANT NUMBER = 31; MONTH2 CONSTANT NUMBER = 28; --//通常の場合の2月の日数 MONTH2_LEAP CONSTANT NUMBER = 29; --//閏年の場合の2月の日数 MONTH3 CONSTANT NUMBER = 31; MONTH4 CONSTANT NUMBER = 30; MONTH5 CONSTANT NUMBER = 31; MONTH6 CONSTANT NUMBER = 30; MONTH7 CONSTANT NUMBER = 31; MONTH8 CONSTANT NUMBER = 31; MONTH9 CONSTANT NUMBER = 30; MONTH10 CONSTANT NUMBER = 31; MONTH11 CONSTANT NUMBER = 30; MONTH12 CONSTANT NUMBER = 31; BEGIN IF W_MONTH = 01 THEN IF TO_NUMBER(W_DATE) = MONTH1 THEN RETURN TRUE; ELSE RETURN FALSE; END IF; ELSIF W_MONTH = 02 THEN --( ( (strYear%4 == 0) (strYear%100 != 0) ) || (strYear%400 == 0) ) ) --//■うるう年のチェック IF ( ( MOD(TO_NUMBER(W_YEAR),4) = 0) AND (MOD(TO_NUMBER(W_YEAR),100) != 0) ) OR (MOD(TO_NUMBER(W_YEAR),400) = 0) THEN IF TO_NUMBER(W_DATE) = MONTH2_LEAP THEN RETURN TRUE; ELSE RETURN FALSE; END IF; ELSE IF TO_NUMBER(W_DATE) = MONTH2 THEN RETURN TRUE; ELSE RETURN FALSE; END IF; END IF; ELSIF W_MONTH = 03 THEN IF TO_NUMBER(W_DATE) = MONTH3 THEN RETURN TRUE; ELSE RETURN FALSE; END IF; ELSIF W_MONTH = 04 THEN IF TO_NUMBER(W_DATE) = MONTH4 THEN RETURN TRUE; ELSE RETURN FALSE; END IF; ELSIF W_MONTH = 05 THEN IF TO_NUMBER(W_DATE) = MONTH5 THEN RETURN TRUE; ELSE RETURN FALSE; END IF; ELSIF W_MONTH = 06 THEN IF TO_NUMBER(W_DATE) = MONTH6 THEN RETURN TRUE; ELSE RETURN FALSE; END IF; ELSIF W_MONTH = 07 THEN IF TO_NUMBER(W_DATE) = MONTH7 THEN RETURN TRUE; ELSE RETURN FALSE; END IF; ELSIF W_MONTH = 08 THEN IF TO_NUMBER(W_DATE) = MONTH8 THEN RETURN TRUE; ELSE RETURN FALSE; END IF; ELSIF W_MONTH = 09 THEN IF TO_NUMBER(W_DATE) = MONTH9 THEN RETURN TRUE; ELSE RETURN FALSE; END IF; ELSIF W_MONTH = 10 THEN IF TO_NUMBER(W_DATE) = MONTH10 THEN RETURN TRUE; ELSE RETURN FALSE; END IF; ELSIF W_MONTH = 11 THEN IF TO_NUMBER(W_DATE) = MONTH11 THEN RETURN TRUE; ELSE RETURN FALSE; END IF; ELSIF W_MONTH = 12 THEN IF TO_NUMBER(W_DATE) = MONTH12 THEN RETURN TRUE; ELSE RETURN FALSE; END IF; ELSE --//どの場合にも当てはまらない、入力値がおかしい時 RETURN FALSE; END IF; END; [Oracle]PL/SQL 例外処理 ■主キー制約違反 EXCEPTION --//一意制約違反 WHEN DUP_VAL_ON_INDEX THEN DBMS_OUTPUT.PUT_LINE( データの重複有り ); ROLLBACK; ■その他例外発生時 EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM); DBMS_OUTPUT.PUT_LINE(SQLCODE); ROLLBACK; ■ユーザ定義例外 ここでは、例として、「REQUIRED_ITEM_EXCEPTION」を 定義するものとする。 ①宣言部 /*■独自例外定義■*/ --//新規ユーザ追加用のデータの必須項目がNULLだった場合 REQUIRED_ITEM_EXCEPTION EXCEPTION; ②実行部 --//例外を投げる。 RAISE REQUIRED_ITEM_EXCEPTION; ③例外処理部 EXCEPTION --//新規ユーザ登録に必要な項目のNULLチェック WHEN REQUIRED_ITEM_EXCEPTION THEN DBMS_OUTPUT.PUT_LINE( SQLERRM || SQLERRM); DBMS_OUTPUT.PUT_LINE( SQLCODE ||SQLCODE); ROLLBACK;
https://w.atwiki.jp/honeybe/pages/4.html
Contents Contents SQL/DDL SQLJOININNER JOIN OUTER JOIN 設定されている制約を参照する。 SEQUENCEを参照する。 PL/SQL パッケージの一覧を参照する。 PL/SQLパッケージソースの復元 View のソースを復元 INSERT...SELECT文の構文 トランザクション管理セーブポイント 月末日を求める。 欠番探索 その他shellからSQLを実行する。SQLファイルを実行 SQL文を実行 なんかあればどうぞ SQL/DDL SQL JOIN INNER JOIN select * from hoge h inner join fuga f on (h.id = f.id) 以下と等価。 select * from hoge h, fuga f where h.id = f.id OUTER JOIN select * from hoge h left outer join fuga f on (h.id = f.id) 以下と等価。 select * from fuga f right outer join hoge h on (f.id = h.id) select * from hoge h, fuga f where h.id = f.id(+) [left|right]は省略可能。(省略時はleft) 設定されている制約を参照する。 select a.table_name, b.column_name, a.constraint_name, a.constraint_type from user_constraints a, user_cons_columns b where a.table_name = b.table_name (+) and a.constraint_name = b.constraint_name (+) SEQUENCEを参照する。 SELECT * FROM USER_SEQUENCES PL/SQL パッケージの一覧を参照する。 select object_name from user_objects where object_type = PACKAGE object_typeには INDEX, LOB, PACKAGE, PACKAGE BODY, PROCEDURE, SEQUENCE, TABLE, SYNONYM がある。 PL/SQLパッケージソースの復元 select text from user_source where name = 必要なパッケージ名 and type = PACKAGE BODY order by line spoolに出力するがよい。 typeには INDEX, LOB, PACKAGE, PACKAGE BODY, PROCEDURE, SEQUENCE, TABLEがある。 View のソースを復元 select text from user_views where view_name= ビュー名 ; set long 4000; などして、longの表示可能サイズを拡大する。 INSERT...SELECT文の構文 insert into table_name1 (col_name1, col_name2...) select col_mame1, col_name2... from table_name2 where id = 1; values句は不要。 カラムの型は同じでないといけない。 トランザクション管理 セーブポイント savepoint セーブポイント名 commitはトランザクション全てコミットされる。 rollbackはトランザクション全て破棄される。 同一トランザクション内で同じ名前のセーブポイントを指定した場合、上書きされる。 rollback to savepoint セーブポイント名; セーブポイント名を設定した以後のトランザクションが破棄される。 セーブポイント以前のトランザクションは生き。 セーブポイント名が未設定のセーブポイントの場合、エラーとなる。 月末日を求める。 TO_DATE(TO_CHAR(ADD_MONTH(TO_DATE( 適当な日付 ), 1), YYYY/MM ) || /01 ) -1 適当な日付 に1月足して、月初日(01)を算出し、1日引く。 欠番探索 select NEW_CODE = min(t1.CODE + 1) from ( select CODE from [TABLE] union all select CODE from [RESERVE] (READUNCOMMITTED) ) t1 left outer join ( select CODE from [TABLE] union all select CODE from [RESERVE] (READUNCOMMITTED) ) t2 on (t1.CODE + 1) = t2.CODE where t2.CODE is null via @IT掲示板 http //www.atmarkit.co.jp/bbs/phpBB/viewtopic.php?topic=15927 forum=26 5 その他 shellからSQLを実行する。 SQLファイルを実行 sqlplus -S uid/pass@sid @hoge.sql SQL文を実行 sqlplus -S uid/pass@sid END select sysdate from dual; exit sql.sqlcode END なんかあればどうぞ 名前 コメント
https://w.atwiki.jp/guma/pages/8.html
【Oracle】-SQL-PL/SQLソース出力 set define on set verify off accept pg_name prompt プログラム名を入力してください： col owner format a20 select owner from dba_source where name = upper( pg_name ) group by owner ; accept owner_name prompt オーナー名を入力してください： set pagesize 0 set feedback off set trimspool on set heading off col text format a4000 set line 4000 set escape on spool pg_name\.sql select case when upper(text) like PACKAG% || upper( pg_name ) || % then CREATE OR REPLACE || text when upper(text) like END% || upper( pg_name ) || % then text || / else text end as text from dba_source where name = upper( pg_name ) and owner = upper( owner_name ) order by owner, name, type, line ; spool off set pagesize 24 set feedback on set heading on set line 100 prompt create file name - || pg_name\.sql set escape off
https://w.atwiki.jp/sfrontier/pages/51.html
Books 作成日 2007/11/01 H.Naito 更新日 2007/11/05 T.Kodama Programing Command Reference Programing No 名前 版数 出版年月日 著者 出版社 値段 (+tax) 保持者 貸出先 作成日 更新日 購入 ブックレビュー 001 プロとしての Oracle PL/SQL 入門 初版 2006/08/09 アシスト教育センター SoftBank クリエイティブ 2400 内藤 NULL 2007/09/18 2007/09/18 amazon NULL Command Reference No 名前 版数 出版年月日 著者 出版社 値段 (+tax) 保持者 貸出先 作成日 更新日 購入 ブックレビュー 001 改訂新版SQLポケットリファレンス 第2版 2006/01/15 浅井 淳 技術評論社 1980
https://w.atwiki.jp/ttsa/pages/14.html
PL/SQL勉強メモ Oracle Database 11g Express Editionインストール 途中で聞かれるパスワードはSYS,SYSTEMユーザに適用される。 デフォルトのセットアップ情報 宛先フォルダ C \oraclexe\ Oracleホーム C \oraclexe\app\oracle\product\11.2.0\server\ Oracleベース C \oraclexe\ Oracle Databaseリスナー のポート 1521 Oracle Services for Microsoft Transaction Server のポート 2030 Oracle HTTPリスナー のポート 8080 参考 http //www.atmarkit.co.jp/fdb/ref/ref_oracle.html http //www.shift-the-oracle.com/sqlplus/ SQL*Plusのコンソールからデータベースの起動 DOSコマンドプロンプトからSQL*Plus起動 sqlplus /NOLOG SQL CONNECT system/zxcvbnm AS SYSDBA SQL STARTUP systemはユーザ名(最初から登録されている) zxcvbnmはパスワード(インストールする時に入力しているはず) ユーザを追加する とりあえず新しくユーザを追加して作業をする。 ユーザ作成構文 CREATE USER my_name IDENTIFIED BY "my_password" [DEFAULT TABLESPACE my_tablespace] [TEMPORARY TABLESPACE my_temp_tablespace] [PROFILE my_profile] OS認証で SYSDBA に接続。NETのサンプル通り、深くは追求しない… CONNECT / AS SYSDBA TEST_USER追加 CREATE USER TEST_USER IDENTIFIED BY "TEST_USER" テスト用なので権限拡大と使用容量を無限にしておく。 GRANT DBA TO TEST_USER; GRANT UNLIMITED TABLESPACE TO TEST_USER; ユーザを確認する SELECT * FROM all_users; 表を作成 CREATE TABLE working ( id NUMBER NOT NULL PRIMARY KEY, dt DATE, name VARCHAR2(24) ); CREATE TABLE log ( -- id NUMBER NOT NULL IDENTITY, logtime TIMESTAMP NOT NULL, msg VARCHAR2(48) ); 表を削除する DROP TABLE working; 表定義の確認 DESCRIBE working レコードを追加 INSERT INTO working (id, dt, name)VALUES(1, 2000/01/01 , AAA ); INSERT INTO log (logtime, msg)VALUES(CURRENT_TIMESTAMP, AAA ); ループしてみる DECLARE cnt NUMBER; BEGIN cnt = 0; LOOP EXIT WHEN cnt = 10; cnt = cnt + 1; END LOOP; END; 1レコードを抽出して変数に読み込む ※DBMS_OUTPUTパッケージを使用して変数の内容を表示している。 デフォルトでは表示がOFFになっているため以下でONにする必要がある。 SET SERVEROUTPUT ON DECLARE row working%ROWTYPE; row0 VARCHAR2(24); row1 VARCHAR2(24); row2 VARCHAR2(24); BEGIN SELECT * INTO row FROM working WHERE id=1; SELECT * INTO row0,row1,row2 FROM working WHERE id=1; DBMS_OUTPUT.PUT_LINE(row0 || , || row1 || , || row2); DBMS_OUTPUT.PUT_LINE(row.id || , || row.dt || , || row.name); END; カーソルを使って複数レコードを抽出する DECLARE CURSOR cur IS SELECT * FROM working; row cur%ROWTYPE; BEGIN OPEN cur; LOOP FETCH cur INTO row; EXIT WHEN cur%NOTFOUND; DBMS_OUTPUT.PUT_LINE(row.id || , || row.dt || , || row.name); END LOOP; CLOSE cur; END; ファイルに出力してみる オラクルが提供するPL/SQLパッケージ「UTL_FILE」を使用してファイル出力を行う。 --TEST_USERに実行権限を与える CONNECT / AS SYSDBA GRANT EXECUTE ON UTL_FILE TO TEST_USER; --ディレクトリオブジェクト一覧を確認 SELECT * FROM ALL_DIRECTORIES; --ディレクトリオブジェクト作成 CREATE DIRECTORY temp AS C \temp\ ; --ディレクトリオブジェクトのアクセス権限を付与 GRANT READ,WRITE ON DIRECTORY temp TO TEST_USER; --ファイル出力 DECLARE fh UTL_FILE.FILE_TYPE; BEGIN --ファイルオープン ※ディレクトリオブジェクト名は大文字じゃないとエラーになる fh = UTL_FILE.FOPEN( TEMP , test2.txt , w ); UTL_FILE.PUT_LINE(fh, testtest ); UTL_FILE.FCLOSE(fh); END; 2015-02-27 17 58 08 (Fri);
https://w.atwiki.jp/ora_tips/pages/32.html
①実行ファイルの内容を画面に表示する set echo on 例） SQL set echo on SQL @a.sql SQL begin SQL dbms_output.put_line( OK ); SQL end; SQL / PL/SQLプロシジャが正常に完了しました