Clever Geek Handbook

PL / SQLの良い習慣

次に、Oracle Database 11gについて、時間の経過とともに開発してきた習慣について説明します。



標準



チーム内では、独自の標準を開発する必要があります（そして当然クライアントの標準も考慮に入れます）。

標準に含まれる最小値：



必要なものをここに含めることができます。 誰もが標準に同意し、サブスクライブすることが重要です（指を切り落とす苦痛の下で）。



コード管理



データベースの移行とバージョン管理のトピックに関する記事がいくつかありました。 記事は間違いなく優れていますが、タグが多数ある場合のみです。 PL / SQLコードは20万行あり（他のオブジェクトの数はこれに匹敵します）、10人のチームです。

最初に、彼らはバンドルRequesitePro + ClearQuest + ClearCaseを使用してすべてを制御しようとしましたが、しばらくするとSSは放棄され、ローカルストレージを備えたgitiousに置き換えられました。

アプリケーションのさまざまな部分がさまざまなリポジトリに分割されました。 個々のクライアントの変更用に個別のリポジトリを作成しました。

ファイルへのアプローチは次のとおりです。ベースラインがあり、それに対するパッチがあり、時間が経つにつれて両方が更新されます。



コード記述



簡単な手順を検討してください（これは単なる例です）：

CREATE OR REPLACE PROCEDURE upd_for_dept ( dept_in IN employee.department_id%TYPE ,newsal_in IN employee.salary%TYPE) IS CURSOR emp_cur IS SELECT employee_id,salary,hire_date FROM employee WHERE department_id = dept_in; BEGIN FOR rec IN emp_cur LOOP UPDATE employee SET salary = NVL(newsal_in, 1000) WHERE employee_id = rec.employee_id; END LOOP; END upd_for_dept; * This source code was highlighted with Source Code Highlighter .



  1. CREATE OR REPLACE PROCEDURE upd_for_dept ( dept_in IN employee.department_id%TYPE ,newsal_in IN employee.salary%TYPE) IS CURSOR emp_cur IS SELECT employee_id,salary,hire_date FROM employee WHERE department_id = dept_in; BEGIN FOR rec IN emp_cur LOOP UPDATE employee SET salary = NVL(newsal_in, 1000) WHERE employee_id = rec.employee_id; END LOOP; END upd_for_dept; * This source code was highlighted with Source Code Highlighter .



  2. CREATE OR REPLACE PROCEDURE upd_for_dept ( dept_in IN employee.department_id%TYPE ,newsal_in IN employee.salary%TYPE) IS CURSOR emp_cur IS SELECT employee_id,salary,hire_date FROM employee WHERE department_id = dept_in; BEGIN FOR rec IN emp_cur LOOP UPDATE employee SET salary = NVL(newsal_in, 1000) WHERE employee_id = rec.employee_id; END LOOP; END upd_for_dept; * This source code was highlighted with Source Code Highlighter .



  3. CREATE OR REPLACE PROCEDURE upd_for_dept ( dept_in IN employee.department_id%TYPE ,newsal_in IN employee.salary%TYPE) IS CURSOR emp_cur IS SELECT employee_id,salary,hire_date FROM employee WHERE department_id = dept_in; BEGIN FOR rec IN emp_cur LOOP UPDATE employee SET salary = NVL(newsal_in, 1000) WHERE employee_id = rec.employee_id; END LOOP; END upd_for_dept; * This source code was highlighted with Source Code Highlighter .



  4. CREATE OR REPLACE PROCEDURE upd_for_dept ( dept_in IN employee.department_id%TYPE ,newsal_in IN employee.salary%TYPE) IS CURSOR emp_cur IS SELECT employee_id,salary,hire_date FROM employee WHERE department_id = dept_in; BEGIN FOR rec IN emp_cur LOOP UPDATE employee SET salary = NVL(newsal_in, 1000) WHERE employee_id = rec.employee_id; END LOOP; END upd_for_dept; * This source code was highlighted with Source Code Highlighter .



  5. CREATE OR REPLACE PROCEDURE upd_for_dept ( dept_in IN employee.department_id%TYPE ,newsal_in IN employee.salary%TYPE) IS CURSOR emp_cur IS SELECT employee_id,salary,hire_date FROM employee WHERE department_id = dept_in; BEGIN FOR rec IN emp_cur LOOP UPDATE employee SET salary = NVL(newsal_in, 1000) WHERE employee_id = rec.employee_id; END LOOP; END upd_for_dept; * This source code was highlighted with Source Code Highlighter .



  6. CREATE OR REPLACE PROCEDURE upd_for_dept ( dept_in IN employee.department_id%TYPE ,newsal_in IN employee.salary%TYPE) IS CURSOR emp_cur IS SELECT employee_id,salary,hire_date FROM employee WHERE department_id = dept_in; BEGIN FOR rec IN emp_cur LOOP UPDATE employee SET salary = NVL(newsal_in, 1000) WHERE employee_id = rec.employee_id; END LOOP; END upd_for_dept; * This source code was highlighted with Source Code Highlighter .



  7. CREATE OR REPLACE PROCEDURE upd_for_dept ( dept_in IN employee.department_id%TYPE ,newsal_in IN employee.salary%TYPE) IS CURSOR emp_cur IS SELECT employee_id,salary,hire_date FROM employee WHERE department_id = dept_in; BEGIN FOR rec IN emp_cur LOOP UPDATE employee SET salary = NVL(newsal_in, 1000) WHERE employee_id = rec.employee_id; END LOOP; END upd_for_dept; * This source code was highlighted with Source Code Highlighter .



  8. CREATE OR REPLACE PROCEDURE upd_for_dept ( dept_in IN employee.department_id%TYPE ,newsal_in IN employee.salary%TYPE) IS CURSOR emp_cur IS SELECT employee_id,salary,hire_date FROM employee WHERE department_id = dept_in; BEGIN FOR rec IN emp_cur LOOP UPDATE employee SET salary = NVL(newsal_in, 1000) WHERE employee_id = rec.employee_id; END LOOP; END upd_for_dept; * This source code was highlighted with Source Code Highlighter .



  9. CREATE OR REPLACE PROCEDURE upd_for_dept ( dept_in IN employee.department_id%TYPE ,newsal_in IN employee.salary%TYPE) IS CURSOR emp_cur IS SELECT employee_id,salary,hire_date FROM employee WHERE department_id = dept_in; BEGIN FOR rec IN emp_cur LOOP UPDATE employee SET salary = NVL(newsal_in, 1000) WHERE employee_id = rec.employee_id; END LOOP; END upd_for_dept; * This source code was highlighted with Source Code Highlighter .



  10. CREATE OR REPLACE PROCEDURE upd_for_dept ( dept_in IN employee.department_id%TYPE ,newsal_in IN employee.salary%TYPE) IS CURSOR emp_cur IS SELECT employee_id,salary,hire_date FROM employee WHERE department_id = dept_in; BEGIN FOR rec IN emp_cur LOOP UPDATE employee SET salary = NVL(newsal_in, 1000) WHERE employee_id = rec.employee_id; END LOOP; END upd_for_dept; * This source code was highlighted with Source Code Highlighter .



  11. CREATE OR REPLACE PROCEDURE upd_for_dept ( dept_in IN employee.department_id%TYPE ,newsal_in IN employee.salary%TYPE) IS CURSOR emp_cur IS SELECT employee_id,salary,hire_date FROM employee WHERE department_id = dept_in; BEGIN FOR rec IN emp_cur LOOP UPDATE employee SET salary = NVL(newsal_in, 1000) WHERE employee_id = rec.employee_id; END LOOP; END upd_for_dept; * This source code was highlighted with Source Code Highlighter .



  12. CREATE OR REPLACE PROCEDURE upd_for_dept ( dept_in IN employee.department_id%TYPE ,newsal_in IN employee.salary%TYPE) IS CURSOR emp_cur IS SELECT employee_id,salary,hire_date FROM employee WHERE department_id = dept_in; BEGIN FOR rec IN emp_cur LOOP UPDATE employee SET salary = NVL(newsal_in, 1000) WHERE employee_id = rec.employee_id; END LOOP; END upd_for_dept; * This source code was highlighted with Source Code Highlighter .



  13. CREATE OR REPLACE PROCEDURE upd_for_dept ( dept_in IN employee.department_id%TYPE ,newsal_in IN employee.salary%TYPE) IS CURSOR emp_cur IS SELECT employee_id,salary,hire_date FROM employee WHERE department_id = dept_in; BEGIN FOR rec IN emp_cur LOOP UPDATE employee SET salary = NVL(newsal_in, 1000) WHERE employee_id = rec.employee_id; END LOOP; END upd_for_dept; * This source code was highlighted with Source Code Highlighter .



CREATE OR REPLACE PROCEDURE upd_for_dept ( dept_in IN employee.department_id%TYPE ,newsal_in IN employee.salary%TYPE) IS CURSOR emp_cur IS SELECT employee_id,salary,hire_date FROM employee WHERE department_id = dept_in; BEGIN FOR rec IN emp_cur LOOP UPDATE employee SET salary = NVL(newsal_in, 1000) WHERE employee_id = rec.employee_id; END LOOP; END upd_for_dept; * This source code was highlighted with Source Code Highlighter .





ここでは、％TYPEに注目する価値があります。 変数の型が列またはフィールドの型である場合は常に、％TYPEを使用します。

FORループでは、多くのUPDATEステートメントが実行されます（もちろん、プロシージャ全体を1つのUPDATEに詰め込むことができます）。 Oracle DB pl / sqlおよびsqlでは、異なるエンジンがコードを実行するため、これらのエンジン間で多くのコンテキストの切り替えが行われ、時間がかかります。

したがって、手順を次のように書き直します。





  1. プロシージャの作成または置換upd_for_dept（
  2. dept_in IN employee.department_id％TYPE
  3. 、newsal_in IN employee.salary％TYPE）
  4. IS
  5. deptlist id_list;
  6. 開始
  7. SELECT employee_id
  8. deptlistに一括収集
  9. FROM従業員WHERE department_id = dept_in;
  11. FORPT indx IN deptlist。 最初の ..deptlist。 最後
  12. 従業員の更新
  13. SET給与= NVL（newsal_in、1000）
  14. WHERE employee_id = deptlist（indx）;
  15. END upd_for_dept;
*このソースコードは、 ソースコードハイライターで強調表示されました。


FORALLステートメントは、コンテキストの切り替えを引き起こしません。 しかし、他に何が悪いのでしょう。 正しくこれはNVLです。 NVLはPL / SQL関数であるため、コンテキストスイッチが再び存在します。 したがって、COALESCEまたはCASEステートメントに置き換える必要があります。

ここでは、SQLエンジンに組み込まれた関数の使用に関するルールが自然に発生します。

コレクションを使用する場合、プロシージャと関数の設計は自然に変わります。 最後のオプションは、次のような手順です。





  1. プロシージャの作成または置換upd_for_dept（
  2. id_listのdeptlist、
  3. 、newsal_in IN employee.salary％TYPE）
  4. IS
  6. 開始
  7. FORPT indx IN deptlist。 最初の ..deptlist。 最後
  8. 従業員の更新
  9. SET給与= COALESCE （newsal_in、1000）
  10. WHERE employee_id = deptlist（indx）;
  11. END upd_for_dept;
*このソースコードは、 ソースコードハイライターで強調表示されました。


コレクションは次のように作成されます。





  1. CREATE TYPE id_listはTABLE OF INTEGERです。
*このソースコードは、 ソースコードハイライターで強調表示されました。


PL / SQLのSQLはハードコードであるため、SQLステートメントを別のプロシージャと関数に配置するルールにすることも価値があります。

型付きコレクションを使用する場合、次の機能を考慮する必要があります。



ほとんど常に（これが間違っているとは見たことがない）コレクションを使用したコード実行の速度は、通常の方法の6倍です。

これで私は終了したい。

エラー処理やログ、CBOクエリの最適化などのトピックは意図的にカバーされていません。 これらのポイントをカバーすることが興味深い場合。


More articles:


All Articles