oracle常用sql文(更新中)

14553 ワード

管理者ログイン:

C:\>sqlplus "system/manager@orcl"

SQL*Plus: Release 10.2.0.1.0 - Production on     1  5 19:30:54 2014

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


   :
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

現在の接続ユーザーを表示します.

SQL> show user
USER   "SYSTEM"

システムが所有するユーザーを表示します.

SQL> select * from all_users;

USERNAME                          USER_ID CREATED
------------------------------ ---------- --------------
USER1                                  61 01-1  -14
BI                                     60 01-1  -14
PM                                     59 01-1  -14
SH                                     58 01-1  -14
IX                                     57 01-1  -14
OE                                     56 01-1  -14
HR                                     55 01-1  -14
SCOTT                                  54 30-8  -05
MGMT_VIEW                              53 30-8  -05
MDDATA                                 50 30-8  -05
SYSMAN                                 51 30-8  -05

USERNAME                          USER_ID CREATED
------------------------------ ---------- --------------
MDSYS                                  46 30-8  -05
SI_INFORMTN_SCHEMA                     45 30-8  -05
ORDPLUGINS                             44 30-8  -05
ORDSYS                                 43 30-8  -05
OLAPSYS                                47 30-8  -05
ANONYMOUS                              39 30-8  -05
XDB                                    38 30-8  -05
CTXSYS                                 36 30-8  -05
EXFSYS                                 34 30-8  -05
WMSYS                                  25 30-8  -05
DBSNMP                                 24 30-8  -05

USERNAME                          USER_ID CREATED
------------------------------ ---------- --------------
TSMSYS                                 21 30-8  -05
DMSYS                                  35 30-8  -05
DIP                                    19 30-8  -05
OUTLN                                  11 30-8  -05
SYSTEM                                  5 30-8  -05
SYS                                     0 30-8  -05

   28 。

新規ユーザーおよび承認:

SQL> CREATE USER USER1
  2  IDENTIFIED BY USER1
  3  DEFAULT TABLESPACE USERS
  4  TEMPORARY TABLESPACE TEMP
  5  PROFILE DEFAULT;

     。

SQL> GRANT CONNECT,RESOURCE TO USER1;

    。

ユーザーuser 1に接続するには、次の手順に従います.

SQL> CONN USER1/USER1;
   。

テーブルの作成:

SQL> CREATE TABLE TB1(ID INTEGER PRIMARY KEY);

    。

クエリー・テーブル構造:

SQL> DESC TB1;
                                             ?   
 ----------------------------------------- -------- ----------------------------

 ID                                        NOT NULL NUMBER(38)

ユーザーパスワードの変更:

SQL> ALTER USER USER1 IDENTIFIED BY USER1;

     。

フィールドを追加:

SQL> ALTER TABLE TB1 ADD (USERNAME VARCHAR2(10) NULL);

    。

SQL> DESC TB1;
                                             ?   
 ----------------------------------------- -------- ----------------------------

 ID                                        NOT NULL NUMBER(38)
 USERNAME                                           VARCHAR2(10)

フィールドの変更:

SQL> ALTER TABLE TB1 MODIFY (USERNAME VARCHAR2(20));

    。

索引の作成:

SQL> CREATE INDEX TB1_USERNAME_INDEX ON TB1 (USERNAME);

     。

索引の削除:

SQL> DROP INDEX TB1_USERNAME_INDEX;

     。

フィールドを削除:

SQL> ALTER TABLE TB1 DROP (USERNAME);

    。

テーブルを削除するには

SQL> DROP TABLE TB1;

    。

ユーザーの削除:

SQL> CONN SYSTEM/MANAGER
   。
SQL> DROP USER USER1;

     。

プライマリ・キーの自己成長テーブルを作成するには、次の手順に従います.

CREATE TABLE TB_STUDENT (
    ID INTEGER PRIMARY KEY,
    STUDENT_NAME VARCHAR2(20) NULL
);

CREATE SEQUENCE SE_STUDENT
START WITH 1
INCREMENT BY 1
CACHE 20
NOMAXVALUE;

CREATE OR REPLACE TRIGGER TRI_STUDENT
BEFORE INSERT ON TB_STUDENT FOR EACH ROW
BEGIN
    SELECT SE_STUDENT.NEXTVAL INTO:NEW.ID FROM DUAL;
END;

ストアド・プロシージャ(パラメータなし):

CREATE OR REPLACE PROCEDURE PRO_PRINT AS
STUDENT_COUNT INTEGER;
BEGIN
    SELECT COUNT(1) INTO STUDENT_COUNT FROM TB_STUDENT;
    IF STUDENT_COUNT>1 THEN
        DBMS_OUTPUT.PUT_LINE(STUDENT_COUNT);
    ELSE
        DBMS_OUTPUT.PUT_LINE('no student found');
    END IF;
END;

ストアド・プロシージャの実行:

BEGIN
    PRO_PRINT;
END;

あるいはCALLを使って、必ずかっこをつけます:

CALL PRO_STUDENT_INSERT();

乱数関数:
1、dbms_random.normal
この関数はパラメータを持たずnormal distributionのnumberタイプを返すことができるので、基本的に乱数は-1から1の間にあります.
2、dbms_random.random
これもパラメータがなく、-power(2,31)からpower(2,31)までの整数値を返します.
3、dbms_random.value
この関数は2つに分けられます.1つはパラメータがなければ、0-1の間の38ビットの小数を直接返します.
2つ目は、2つのパラメータa,bを加えると、戻り値がa,bの間の38ビットの小数点以下になる
4、dbms_random.string
この関数には、前の文字指定タイプ、後の数値指定ビット数(最大60)の2つのパラメータが必要です.
タイプの説明:
    'u','U' : upper case alpha characters only
    'l','L' : lower case alpha characters only
    'a','A' : alpha characters only (mixed case)
    'x','X' : any alpha-numeric characters (upper)
    'p','P' : any printable characters
乱数関数のテスト:

CREATE OR REPLACE PROCEDURE PRO_RANDOM(RANDOM_FROM IN INTEGER, RANDOM_TO IN INTEGER, RANDOM_STRING OUT VARCHAR2)
AS
RANDOM_RANDOM INTEGER;
RANDOM_NORMAL INTEGER;
RANDOM_VALUE INTEGER;
BEGIN
    SELECT DBMS_RANDOM.RANDOM INTO RANDOM_RANDOM FROM DUAL;
    SELECT DBMS_RANDOM.NORMAL INTO RANDOM_NORMAL FROM DUAL;
    IF RANDOM_FROM > RANDOM_TO THEN
        SELECT DBMS_RANDOM.VALUE(RANDOM_TO, RANDOM_FROM) INTO RANDOM_VALUE FROM DUAL;
    ELSE
        SELECT DBMS_RANDOM.VALUE(RANDOM_FROM, RANDOM_TO) INTO RANDOM_VALUE FROM DUAL;
    END IF;
    SELECT DBMS_RANDOM.STRING('U', 20) INTO RANDOM_STRING FROM DUAL;
    DBMS_OUTPUT.PUT_LINE('RANDOM_RANDOM='||RANDOM_RANDOM);
    DBMS_OUTPUT.PUT_LINE('RANDOM_NORMAL='||RANDOM_NORMAL);
    DBMS_OUTPUT.PUT_LINE('RANDOM_VALUE='||RANDOM_VALUE);
    DBMS_OUTPUT.PUT_LINE('RANDOM_STRING='||RANDOM_STRING);
END;

実行:

DECLARE
RANDOM_FROM INTEGER;
RANDOM_TO INTEGER;
RANDOM_STRING_OUT VARCHAR2(20);
BEGIN
    RANDOM_FROM:=1;
    RANDOM_TO:=100;
    PRO_RANDOM(RANDOM_FROM,RANDOM_TO, RANDOM_STRING_OUT);
    DBMS_OUTPUT.PUT_LINE('RANDOM_STRING_OUT='||RANDOM_STRING_OUT);
END;

結果(毎回異なる):

RANDOM_RANDOM=-33085305
RANDOM_NORMAL=-1
RANDOM_VALUE=17
RANDOM_STRING=MADGMQSZXJAHWPJNMWCS
RANDOM_STRING_OUT=MADGMQSZXJAHWPJNMWCS

システム取得時間:

SELECT SYSDATE FROM DUAL

出力:

2014-1-13 22:42:38

フォーマット時間:

SELECT TO_CHAR(SYSDATE, 'yyyy-MM-dd HH24:mi:ss') FROM DUAL

出力:

2014-01-13 22:43:29

SELECT TO_DATE('2013-01-04 12:12:12', 'yyyy-MM-dd HH24:mi:ss') FROM DUAL;

出力:

2013-1-4 12:12:12

ストアド・プロシージャ・ループ挿入データ:

CREATE OR REPLACE PROCEDURE PRO_STUDENT_INSERT AS
BEGIN
    FOR I IN 1..10000 LOOP
        INSERT INTO TB_STUDENT (STUDENT_NAME) VALUES ('aaa');
    END LOOP;
END;

実行:

BEGIN
    PRO_STUDENT_INSERT;
END;

カーソルでデータを巡回するには、次の手順に従います.

DECLARE
CURSOR C1 IS SELECT STUDENT_NAME FROM TB_STUDENT;
BEGIN
    FOR STUDENT_ROW IN C1 LOOP
        DBMS_OUTPUT.PUT_LINE(STUDENT_ROW.STUDENT_NAME);
    END LOOP;
END;

INとEXISTSの性能比較:
100万個のデータを挿入:

CREATE OR REPLACE PROCEDURE PRO_STUDENT_INSERT AS
RANDOM_STRING VARCHAR2(20);
BEGIN
    FOR I IN 1..1000000 LOOP
        SELECT DBMS_RANDOM.STRING('U', 20) INTO RANDOM_STRING FROM DUAL;
        INSERT INTO TB_STUDENT (STUDENT_NAME) VALUES (RANDOM_STRING);
    END LOOP;
END;

BEGIN
    PRO_STUDENT_INSERT;
END;

実行時間を表示:

set timing on

さらに2つのテーブルを作成します.

CREATE TABLE TB_STU1 AS SELECT * FROM TB_STUDENT WHERE ID >= 32500 AND ID <= 102800;

CREATE TABLE TB_STU2 AS SELECT * FROM TB_STUDENT WHERE ID >= 32500 AND ID <= 102800;

インデックスを作成せずに実行:

SQL> SELECT COUNT(STUDENT_NAME) FROM TB_STUDENT A WHERE EXISTS (SELECT STUDENT_N
AME FROM TB_STU1 B WHERE A.STUDENT_NAME=B.STUDENT_NAME);

COUNT(STUDENT_NAME)
-------------------
              70301

    :  00: 00: 01.56
SQL> SELECT COUNT(STUDENT_NAME) FROM TB_STUDENT WHERE STUDENT_NAME IN (SELECT ST
UDENT_NAME FROM TB_STU2);

COUNT(STUDENT_NAME)
-------------------
              70301

    :  00: 00: 00.70
SQL>

ネット上でEXISTSはINより良いと言って、しかし結果INはもっと速くて、それから何度もテストしました:

CREATE TABLE TB_STU1 AS SELECT * FROM TB_STUDENT WHERE ID >= 150000 AND ID <= 260000;

CREATE TABLE TB_STU2 AS SELECT * FROM TB_STUDENT WHERE ID >= 150000 AND ID <= 260000;

結果:

SELECT STUDENT_NAME FROM TB_STUDENT A WHERE EXISTS (SELECT STUDENT_NAME FROM TB_STU1 B WHERE A.STUDENT_NAME=B.STUDENT_NAME);

    :  00: 00: 22.45

SELECT STUDENT_NAME FROM TB_STUDENT WHERE STUDENT_NAME IN (SELECT STUDENT_NAME FROM TB_STU2);

    :  00: 00: 15.64

何度かありますが、時間の差は多くありませんが、INの速い回数はもっと多いです.
インデックスを作成します.

CREATE INDEX IDX1 ON TB_STU1(STUDENT_NAME);

CREATE INDEX IDX2 ON TB_STU2(STUDENT_NAME);

CREATE INDEX IDX ON TB_STUDENT(STUDENT_NAME);

テスト結果:
外がTBだったらSTUDENTの場合、INはEXISTSより速い.

SELECT STUDENT_NAME FROM TB_STUDENT A WHERE EXISTS (SELECT STUDENT_NAME FROM TB_STU1 B WHERE A.STUDENT_NAME=B.STUDENT_NAME);

    :  00: 05: 11.90

SELECT STUDENT_NAME FROM TB_STUDENT WHERE STUDENT_NAME IN (SELECT STUDENT_NAME FROM TB_STU2);

    :  00: 02: 24.50

中がTBだったらSTUDENTの場合、EXISTSはINより速いです.

SELECT STUDENT_NAME FROM TB_STU1 A WHERE EXISTS (SELECT STUDENT_NAME FROM TB_STUDENT B WHERE A.STUDENT_NAME=B.STUDENT_NAME);

    :  00: 01: 43.53

SELECT STUDENT_NAME FROM TB_STU2 WHERE STUDENT_NAME IN (SELECT STUDENT_NAME FROM TB_STUDENT);

    :  00: 03: 56.59

TB_STUDENTはデータが多く、他のテーブルはデータが少ないが、複数回のテストを経て、毎回そうではない.
性能テスト:SELECT*とSELECTフィールド名
100万個のデータを挿入

CREATE TABLE TB_STUDENT (
    ID INTEGER PRIMARY KEY,
    STUDENT_NAME VARCHAR2(20) NOT NULL,
    SEX VARCHAR2(1) NOT NULL,
    AGE INTEGER NOT NULL
);

CREATE SEQUENCE SE_STUDENT
START WITH 1
INCREMENT BY 1
CACHE 20
NOMAXVALUE;

CREATE OR REPLACE TRIGGER TRI_STUDENT
BEFORE INSERT ON TB_STUDENT FOR EACH ROW
BEGIN
    SELECT SE_STUDENT.NEXTVAL INTO:NEW.ID FROM DUAL;
END;

CREATE OR REPLACE PROCEDURE PRO_STUDENT_INSERT AS
BEGIN
    FOR I IN 1..1000000 LOOP
        IF I <= 10000 THEN
            INSERT INTO TB_STUDENT (STUDENT_NAME, SEX, AGE) VALUES ('aaaaa', 'B', 11);
        ELSIF I<=100000 THEN
            INSERT INTO TB_STUDENT (STUDENT_NAME, SEX, AGE) VALUES ('bbbbb', 'G', 12);
        ELSIF I<=500000 THEN
            INSERT INTO TB_STUDENT (STUDENT_NAME, SEX, AGE) VALUES ('ccccc', 'B', 13);
        ELSE
            INSERT INTO TB_STUDENT (STUDENT_NAME, SEX, AGE) VALUES ('ddddd', 'G', 14);
        END IF;
    END LOOP;
    COMMIT;
END;

CALL PRO_STUDENT_INSERT();

テスト結果:

SELECT * FROM TB_STUDENT WHERE ID <= 100000;

    :  00: 00: 32.92

SELECT ID, STUDENT_NAME, SEX, AGE FROM TB_STUDENT WHERE ID <= 100000;

    :  00: 00: 32.50

SELECT * FROM TB_STUDENT WHERE ID >= 100001 AND ID <=200000;

    :  00: 00: 25.12

SELECT ID, STUDENT_NAME, SEX, AGE FROM TB_STUDENT WHERE ID >= 100001 AND ID <=200000;

    :  00: 00: 24.45

SELECT * FROM TB_STUDENT;

    :  00: 03: 34.21

SELECT ID, STUDENT_NAME, SEX, AGE FROM TB_STUDENT;

    :  00: 01: 53.95

SELECT * FROM TB_STUDENT;

    :  00: 02: 14.85

SELECT ID, STUDENT_NAME, SEX, AGE FROM TB_STUDENT;

    :  00: 02: 04.62

100万件の記録を調べたところ、2回目の時間は1回目よりはるかに少ない.これはキャッシュの関係で、次にキャッシュを整理する.

ALTER SYSTEM FLUSH BUFFER_CACHE;

再テスト:

SELECT * FROM TB_STUDENT;

    :  00: 03: 22.29

SELECT ID, STUDENT_NAME, SEX, AGE FROM TB_STUDENT;

    :  00: 02: 47.64

フィールド名を直接書くのと*を使うのを見て、明らかに直接字を書くセグメント名が速いが、あまり速くない.
複数のテストを行い、毎回キャッシュをクリアします.次のようにします.

SELECT * FROM TB_STUDENT;

    :  00: 02: 15.59

SELECT ID, STUDENT_NAME, SEX, AGE FROM TB_STUDENT;

    :  00: 02: 14.17

SELECT * FROM TB_STUDENT;

    :  00: 02: 24.21

SELECT ID, STUDENT_NAME, SEX, AGE FROM TB_STUDENT;

    :  00: 02: 18.48

次に10列増やしてテストします.

ALTER TABLE TB_STUDENT ADD (
    COL1 VARCHAR2(10) NULL,
    COL2 VARCHAR2(10) NULL,
    COL3 VARCHAR2(10) NULL,
    COL4 VARCHAR2(10) NULL,
    COL5 VARCHAR2(10) NULL,
    COL6 VARCHAR2(10) NULL,
    COL7 VARCHAR2(10) NULL,
    COL8 VARCHAR2(10) NULL,
    COL9 VARCHAR2(10) NULL,
    COL10 VARCHAR2(10) NULL
);

テスト結果:

SELECT * FROM TB_STUDENT;

    :  00: 06: 16.53

SELECT ID, STUDENT_NAME, SEX, AGE, COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9, COL10 FROM TB_STUDENT;

    :  00: 06: 00.76

SELECT * FROM TB_STUDENT;

    :  00: 05: 52.68

SELECT ID, STUDENT_NAME, SEX, AGE, COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9, COL10 FROM TB_STUDENT;

    :  00: 06: 18.56

だから、2つのSQL文は、明らかな時間の差はなく、30秒程度の差があるような気がします.テストデータ量は100万件です.

REST APIとHTTPメソッドについてまとめてみた

新卒のトレーナーをするときの個人的まとめ