PL/SQL学習三


8、参照変数   参照変数とは、数値ポインタを格納するための変数です.主にカーソル変数とオブジェクトタイプ変数の2種類があります.   8.1、REF CURSOR(カーソル変数)   カーソルを表示する場合は、カーソルを定義するときに、対応するSELECT文を指定する必要があります.このカーソルは静的カーソルとも呼ばれます.   カーソル変数を使用する場合、SELECT文を指定する必要はありません.カーソルを開くときに指定します.このカーソルを動的カーソルと呼びます.
-- ex:pl/sql_10  
DECLARE
  TYPE TMP_CUR IS REF CURSOR; --        
  CUR01  TMP_CUR; --      
  V_NAME EMP.ENAME%TYPE;
  V_SAL  EMP.SAL%TYPE;
BEGIN
  OPEN CUR01 FOR --    
    SELECT ENAME, SAL FROM EMP WHERE ROWNUM = 1;
  LOOP
    FETCH CUR01
      INTO V_NAME, V_SAL;
    EXIT WHEN CUR01%NOTFOUND;
    --  
    DBMS_OUTPUT.PUT_LINE('name:' || V_NAME);
  END LOOP;
  CLOSE CUR01;
  --    
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('error');
END;

       8.2、REF obj_type(オブジェクトタイプ)   7.3にネストされたテーブルのEM_のようにTYPEと7.4のtmp_typeタイプはオブジェクトタイプです.   オブジェクトタイプからオブジェクトテーブルを作成できます.7.4のtmp_を作成するtypeのオブジェクトテーブルは次のとおりです.   
SQL> create table tmp_test of tmp_type;

    。

SQL> desc tmp_test
                                             ?   
 ----------------------------------------- -------- -------------

 TITLE                                              VARCHAR2(30)
 PDATE                                              DATE

   REFは実際にはオブジェクトタイプのポインタとして使用され,同じオブジェクトを共有するために役割を果たし,占有空間を低減する.   次のようになります.
--              ,             
SQL> drop table tmp_test;

    。
SQL> drop type tmp_array;

     。
SQL> --       
SQL> CREATE OR REPLACE TYPE TMP_TYPE AS OBJECT
  2  (
  3    STREET  VARCHAR2(60),
  4    CITY    VARCHAR2(30),
  5    STATE   VARCHAR2(30),
  6    ZIPCODE VARCHAR2(8),
  7    OWNER   VARCHAR2(20)
  8  );
  9  /   
     。

SQL> --     
SQL> CREATE TABLE TMP_TABLE OF TMP_TYPE;

    。
SQL> --    
SQL> INSERT INTO TMP_TABLE VALUES('  ', '   ', '   ', '010', '  ');

    1  。

SQL> INSERT INTO TMP_TABLE VALUES('  ', '   ', '    ', '020', '  ');


    1  。

SQL> COMMIT;

    。

 
SQL> --   person   tmp_type    
SQL> CREATE TABLE person(
  2  ID NUMBER(8) PRIMARY KEY,
  3  NAME VARCHAR2(20),
  4  addr REF tmp_type);

    。

 
--    ,'  '    '  '      SELECT        person 
--  REF()        ,     
--         ,  REF(t)        tmp_table            
SQL> select REF(tmp_table) from tmp_table;
select REF(tmp_table) from tmp_table
           *
  1      :
ORA-00904: "TMP_TABLE":      
SQL> select REF(t) from tmp_table t;--    2   ,    2      

REF(T)
--------------------------------------------------------------------------------------

0000280209EF4CBC59804040DD8AB78B9E3B01189A6AA8DCCD6A5942E4A119009E64908FD60440184E0000

000028020913F3BEB30AE840D08E32FC32415EFB826AA8DCCD6A5942E4A119009E64908FD60440184E0001

--   person     ,     tmp_table   
SQL> INSERT INTO person SELECT 1,'  ',REF(t)
  2  FROM tmp_table t WHERE owner='  ';

    1  。

SQL> INSERT INTO person SELECT 2,'  ',REF(t)
  2  FROM tmp_table t WHERE t.owner='  ';

    1  。
--sqlplus    
SQL> col id for 999
SQL> col name format a10
SQL> col addr format a40
SQL> select * from person;
--     tmp_table ref(t)      ,        
  ID NAME       ADDR
---- ---------- ----------------------------------------
   1            0000220208EF4CBC59804040DD8AB78B9E3B0118
                9A6AA8DCCD6A5942E4A119009E64908FD6

   2            000022020813F3BEB30AE840D08E32FC32415EFB
                826AA8DCCD6A5942E4A119009E64908FD6

                9、LOBタイプ   LOBタイプは、大量のデータを格納するための変数です.主に2つに分けられます.   内部LOB:CLOB,BLOB(メモリバイナリデータ),NCLOB   内部LOBのデータはすべてデータベースに格納され、トランザクション操作をサポートします.   外部LOB:BFILE(OSファイルへのポインタを格納)   外部LOBのデータはOSファイルに格納されており、トランザクション操作はサポートされていません.   10、非PL/SQL変数   10.1 SQL*PLUS変数の使用   PL/SQLでSQL*PLUS変数を使用するには、まずvariableで変数定義を行う必要があります.次のようになります.
   SQL> var t_name varchar2(20);
   SQL> BEGIN
     2    SELECT ename
     3    INTO :T_NAME
     4    FROM emp
     5    WHERE empno=7788;
     6* END;
   SQL> /
   PL/SQL        。
   SQL> print t_name
T_NAME
--------------------------------
SCOTT

   10.2 Pro*C/C++変数の使用   PL/SQLでPro*C/C++ホスト変数を使用する場合は、まずホスト変数を定義する必要があります.次のようになります.
CHAR NAME[10];
EXEC SQL EXECUTE
 BEGIN
  SELECT ename INTO :NAME FROM emp
  WHERE empno=7788;
 END;
END-EXEC;
printf("   :%s
",NAME);--c

11、識別子が合法的である:   v_ename varchar2(10);    v$say   number(8,2);    v#error  exception;    “123456” varchar2(12);--二重引用符で始まる数字
-- ex:pl/sql_11
SQL>1  DECLARE
  2    "123"   VARCHAR2(20) := 'test';
  3    "  A" NUMBER(10, 2);
  4    T       VARCHAR2(20);
  5    N       NUMBER(10, 2);
  6  BEGIN
  7    T       := "123";
  8    "  A" := 12.22;
  9    N       := "  A";
 10    DBMS_OUTPUT.PUT_LINE(T);--  dbms       "  A" "123"
 11    DBMS_OUTPUT.PUT_LINE(N);
 12* END;
SQL> /
test
12.22

PL/SQL        。

不正な識別子:   特殊な記号をつけた、  例えば、v%enam varchar 2(10);   #で始まる、     例:#vl  exception;    数字で始まる、   例えば:2 say number(6,2);   v 1,v 2,v 3 varchar 2(10);   漢字で始まる、   例えば、変数B varchar 2(10);   キーワードで、     例:select number(6,2);   12、PL/SQLコード作成規則   変数を定義する場合は、V_を使用することをお勧めします.v_などの接頭辞としてename,v_salなど、   定数を定義する場合は、c_を推奨します.c_のような接頭辞としてrate    カーソルを定義するときは、_を使用することを推奨します.cursorは、emp_のような接尾辞として使用されます.cursor    例外を定義する場合は、e_を使用することを推奨します.e_のような接頭辞としてint_error    テーブル・タイプを定義する場合は、_table_typeを接尾辞として   テーブル変数を定義する場合は、_tableを接尾辞として   レコードタイプを定義する場合は、_record_typeを接尾辞として   レコード変数を定義する場合は、_recordを接尾辞として      キーワードやデータ型などは大文字で、変数名やパラメータ、データベース・オブジェクト、カラム名は小文字で書くことをお勧めします.  
13、簡単なクエリ文(比較的簡単で、詳しくは言わない)   SQL*PLUSコマンドdescribe(略記desc)を使用すると、次のようなテーブル構造を表示できます.
SQL> desc scott.emp
                                             ?   
 ----------------------------------------- -------- ------------

 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

   SELECT*FROM scott.empを使用します.テーブルempのすべてのデータをクエリーします.   SELECT列名(enameなど)from SCOTT.EMPを使用します.クエリーテーブルemp指定カラムのデータ(ここではenameカラム)   DISTINCTキーワードを使用して重複行を削除します.   ASエイリアスを使用します(エイリアスには特殊文字やスペース、中国語などが含まれているので、二重引用符で囲む必要があります).   SELECT ename AS「名前」、to_char(hiredate,'yyyyyy-mm-dd')AS"日付"FROM scott.emp;   またはスペースエイリアス   SELECT ename「名前」、to_char(hiredate,'yyyyyy-mm-dd')「日付」FROM scott.emp;   文字列または数値を接続するには、次の手順に従います.   select 'I''m '||123||' PL/SQL ' from dual;--I'm 123 PL/SQL出力(注意出力'の方法)   WHERE句を使用してクエリー結果を制限する   1.WHERE条件に使用する数字   SELECT ename,sal FROM scott.emp WHERE say>500;    2.WHERE条件で使用する文字   SELECT job,sal FROM scott.emp WHERE ename='SCOTT';    3.WHERE条件での使用日   SELECT ename,sal,hiredate FROM scott.emp WHERE hiredate>TO_DATE('2010-01-01','YYYY-MM-DD';    4.WHERE条件中BETWEEN…AND   SELECT ename,sal,hiredate,job FROM SCOTT.emp WHERE sal BETWEEN 1000 AND 2000;    5.WHERE条件でLIKEがファジイ問合せを行う   SELECT ename,sal FROM scott.emp WHERE ename LIKE 'S%';    SELECT ename,sal FROM scott.emp WHERE ename LIKE'_A%'--2番目の文字が大文字Aの従業員の従業員名と給料を照会します.   SELECT ename,sal FROM scott.emp WHERE ename LIKE'%a_%'ESCAPE'a';--従業員名に含まれる_の従業員情報を表示し、ESCAPEは文字'a'がエスケープ文字であることを示す.   6.WHERE条件でINを使用   SELECT ename,sal FROM scott.emp WHERE sal IN(800,1250);    7.WHERE条件でIS NULL/IS NOT NULLを使用   SELECT ename,sal FROM scott.emp WHERE mgr IS NULL;--NULL値がある場合は=、<、>などのオペレータで比較しないでください   SELECT ename,sal FROM scott.emp WHERE mgr IS NOT NULL;    8.WHERE条件で論理オペレータAND、OR、NOTを使用   SELECT ename,sal FROM scott.emp WHERE deptno=20 AND job='CLERK';    SELECT ename,sal FROM scott.emp WHERE sal>2500 OR job='MANAGER';    SELECT ename,sal FROM scott.emp WHERE sal NOT IN(800,1250);    9.WHERE条件でORDER BYを使用して該当フィールドを並べ替え   SELECT ename,sal FROM scott.emp WHERE deptno=30    ORDER BY sal;--salフィールドでソートします.デフォルトは小さいから大きいまで、NULLがある場合は一番前に表示されます.DESCキーを使用して降順ソートします.      
14、NULL値処理   NULLはスペースでも0でもありません.NULL加算減算乗数はNULLに等しいです.   14.1 NVL関数を使用してNULLを処理する.   NVL(exp 1,exp 2);---exp 1がNULL値である場合、exp 2を返し、そうでない場合はexp 1を返す.注意:exp 1とexp 2のデータ型は一致しなければならない.
SQL> select nvl(null,'aaa') from dual;

NVL
---
aaa

SQL> select nvl(null*0,6) from dual;

NVL(NULL*0,6)
-------------
            6

SQL> select nvl(null*0,'aaa') from dual;
select nvl(null*0,'aaa') from dual
                  *
  1      :
ORA-01722:     

       14.2 NVL 2関数を使用してNULLを処理する.   NVL 2はoracle 9 iに追加された関数です.   NVL 2(exp 1,exp 2,exp 3)--exp 1がNULLでない場合はexp 2を返し、exp 1がNULLである場合はexp 3を返します                       --exp 2、exp 3とexp 1のデータ型は一致しなければならない
SQL> select nvl2(null*0,9,'a') from dual;
select nvl2(null*0,9,'a') from dual
                     *
  1      :
ORA-01722:     


SQL> select nvl2(null*0,'b','a') from dual;

N
-
a

SQL> select nvl2(null*0,'a',9) from dual;

N
-
9

15、DML文   DELETE、INSERT、UPDATEはDML文に属し、それぞれデータの削除、挿入、更新に用いられる.   15.1、挿入データ(INSERT)   注意データを挿入する場合は、制約ルールを満たす必要があります.プライマリ・キー列とNOT NULL列にデータを指定する必要があります.
   SQL> INSERT INTO EMP VALUES --       
     2  (7876,'ADAMS','CLERK',7788,to_date('13-7-87','dd-mm-yyyy')-51,1100,NULL,20);
   
       1  。
   SQL> INSERT INTO EMP  --        
     2    (EMPNO, ENAME, JOB, HIREDATE)
     3  VALUES
     4    (1356, 'MARY', 'CLERK', TO_DATE('1988-10-20', 'yyyy-mm-dd'));
   
       1  。
   --  DEFAULT    ,     DEFAULT        ,    NULL
   SQL> INSERT INTO dept VALUES(60,'MARKET',DEFAULT);

       1  。

       サブクエリの結果を使用して挿入するには、次の手順に従います.
   SQL> INSERT /*+APPEND */INTO EMPLOYEES --  /*+APPEND*/          
     2    (EMPNO, ENAME, SAL, DEPTNO)
     3    SELECT EMPNO, ENAME, SAL, DEPTNO
     4    FROM EMP WHERE DEPTNO = 20;

      5 。

   大量のデータを挿入する必要がある場合は、/*+APPEND*/オプションを使用すると大幅に高速化されます.これは、オプションの使用によるものです.   APPEND後、テーブルの空きブロックを利用して挿入することなく、テーブルの一番後ろにデータが直接追加されます.      複数のテーブルを同時に挿入:
   --  ALL      ,               
SQL> create table clerk as select * from emp where 1=2;

    。

SQL> create table dept10 as select * from emp where 1=2;

    。

SQL> create table dept20 as select * from emp where 1=2;

    。

SQL> create table dept30 as select * from emp where 1=2;

    。

SQL> create table other_dept as select * from emp where 1=2;

    。   
SQL> INSERT ALL
  2  WHEN DEPTNO = 10 THEN INTO DEPT10
  3  WHEN DEPTNO = 20 THEN INTO DEPT20
  4  WHEN DEPTNO = 30 THEN INTO DEPT30
  5  WHEN job='CLERK' THEN INTO clerk
  6  ELSE INTO OTHER_DEPT
  7  SELECT * FROM emp;

   18 。
SQL> select count(*) from dept10;

  COUNT(*)
----------
         3

SQL> select count(*) from dept20;

  COUNT(*)
----------
         5

SQL> select count(*) from dept30;

  COUNT(*)
----------
         6
SQL> select count(*) from clerk;

  COUNT(*)
----------
         4

   --FIRSTによるマルチテーブル挿入
SQL> rollback;

     。
SQL> INSERT FIRST
  2  WHEN DEPTNO = 10 THEN INTO DEPT10
  3  WHEN DEPTNO = 20 THEN INTO DEPT20
  4  WHEN DEPTNO = 30 THEN INTO DEPT30
  5  WHEN job='CLERK' THEN INTO clerk
  6  ELSE INTO OTHER_DEPT
  7  SELECT * FROM emp;

   14 。
SQL> select count(*) from dept10;

  COUNT(*)
----------
         3

SQL> select count(*) from dept20;

  COUNT(*)
----------
         5

SQL> select count(*) from dept30;

  COUNT(*)
----------
         6

SQL> select count(*) from clerk;

  COUNT(*)
----------
         0

         FIRSTを使用してマルチテーブル挿入を行う場合、そのレコードが条件を満たして前のテーブルに挿入された場合、   その後、テーブルは挿入されません.
 
   15.2更新データ(UPDATE)   データを更新する場合は、データが制約を満たす必要があります.   データを更新する場合は、カラムのデータ型と一致する必要があります.   1.次のように、単一列のデータを更新します.
   SQL> UPDATE emp SET sal=2460 WHERE upper(ename)='SCOTT';

       1  。
   

   2.次のような複数列のデータを更新します.
   SQL> UPDATE emp SET sal=sal+100,comm=sal*0.5 WHERE deptno=20;

      5 。

       3.DEFAULTオプションを使用して、次のようにデータを更新します(9 i以降).   このカラムは、テーブル作成時に指定されたDEFAULT値がない場合、更新後はNULL、そうでない場合は設定されたDEFAULT値です.
   SQL> UPDATE emp SET job=DEFAULT WHERE lower(ename)='scott';

       1  。

   SQL> SELECT job FROM emp WHERE lower(ename)='scott';

   JOB
   ---------

   

   4.サブクエリによるデータの更新     次のような関連データを更新します.
     SQL> --    SCOTT   job、  sal、  comm SMITH   
     SQL> UPDATE EMP
       2     SET (JOB, SAL, COMM) = (SELECT JOB, SAL, COMM
       3                               FROM EMP
       4                              WHERE ENAME = 'SMITH')
       5   WHERE ENAME = 'SCOTT';
     
         1  。

          次のようなテーブル・データのコピーに使用します.
     SQL> --  emp  employee  JOB    ‘CLERK’(  EMPNO--7788) ,
     SQL> --   emp  deptno  employee DEPTNO
     SQL> UPDATE EMPLOYEE
       2     SET DEPTNO = (SELECT DEPTNO FROM EMP WHERE EMPNO = 7788)
       3   WHERE JOB = (SELECT JOB FROM EMP WHERE EMPNO = 7788);
       
         1  。

           15.3データの削除(DELETE、TRUNCATE)     注意DELETEを削除する場合、WHERE条件がない場合、テーブル全体のデータが削除されます.     関連データを削除するときは、そのテーブルデータが別のテーブルのプライマリテーブルであるかどうかにも注意します(つまり、サブテーブルに関連レコードが存在しない場合に削除できます).     ここでの主従関係とは、主外部キー制約関係のことである.あるテーブルの主キーが別のテーブルで外部キーを作る場合、このテーブルを主テーブルと呼び、主キーの存在(外部キーが存在するテーブル)に依存するテーブルを従テーブルと呼ぶ     1.DELETEを使用して、次のような指定されたデータを削除します.
     SQL> DELETE FROM emp WHERE lower(ename)='smith';

         1  。


           2.WHERE句を持たない場合は、テーブルのすべてのデータを削除し、ROLLBACKロールバック(後述)を使用します.
     SQL> DELETE FROM emp;

        13 。
     SQL> ROLLBACK;

          。

           3.TRUNCATEカットオフテーブルを使用する(TRUNCATEはDDL操作であり、カットオフ後にロールバックできないことに注意)     TRUNCATEを使用してテーブル全体のデータを削除するのはDELETEよりずっと速く、TRUNCATEテーブルの後、テーブルの高い水平線とインデックスが再設定されます(関連空間が解放されます).     したがって、TRUNCATE以降のテーブルの操作速度は、DELETE操作後のテーブルよりも速い.
     SQL> TRUNCATE TABLE employee;

         。


           4.サブクエリによるデータの削除
     SQL> DELETE FROM EMP
       2   WHERE DEPTNO = (SELECT DEPTNO FROM DEPT WHERE LOWER(DNAME) = 'sales');

        6 。