210413

9261 ワード


  • 制約(**)
    -無効な値の入力/変更/削除は、テーブルの該当する列には行われません.
    設定された条件(整合性制約)

  • 欠陥のないデータの管理
    1)タイプ
    <1>PRIMARY KEY(マスタキー)
  • は1つのテーブルにのみ存在し、各行を識別するための
  • である.
  • NULL値は許可されず、重複値は許可されない(NOT NULL+UNIQUE KEY)
    <2>FOREGN KEY(外部キー、参照キー)
  • 親テーブルの特定のカラム(プライマリ・キー)を参照するカラム
  • 親テーブルに参照値がない場合、エラーが発生します.
    <3>UNIQUE KEY(一意鍵)
  • 列のすべての値が一意である必要がある場合に使用します.重複除外
  • NULLを許可
    <4>CHECK制約
  • 条件制約、
  • の条件を満たすデータのみ入力
    <5>NOT NULL条件
  • NULL値が許可されていない列に
    예1) 제약조건에 이름을 부여하지 않은 경우
    CREATE TABLE DEPT 1を設定します.
    (
    DEPTNO NUMBER(2) PRIMARY KEY,
    DNNAME VARCHAR 2(15)DEFAULT「人事部」;
    LOC CHAR(9)CHECK(「ソウル」「釜山」)
    );
  • INSERT INTO DEPT 1 VALES(1、「部門1」、「ソウル」)エラー
    ->PK列に重複値は使用できません
    INSERT INTO DEPT 1(DEPTNO,LOC)VALES(2,「釜山」);--DNNAMEはDEFAULT値を記憶する(人事部)
    INSERT INTO DEPT 1(DEPTNO,LOC)VALES(3,「対偶」);--検査制約dp違反
    #USER CONTRAINTS:作成した制約情報を含むデータリスト
    DESC USER_CONSTRAINTS; -- USER CONTRAINTS列構造の表示
    SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME FROM USER_CONSTRAINTS;
    예2) 제약조건에 이름을 부여하기 - 컬럼레벨과 테이블레벨이 있다.
    --列レベル
    CREATE TABLE DEPT2
    (
    DEPTNO NUMBER(2) CONSTRAINT PK_DEPT2_DEPTNO PRIMARY KEY,
    DNAME VARCHAR2(15),
    LOC VARCHAR2(15)
    );
    INSERT INTO DEPT 2 VALES(1、「部門1」、「鐘路」);
    COMMIT;
    SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME FROM USER_CONSTRAINTS;
    --テーブル・レベル
    CREATE TABLE EMP2
    (
    EMPNO NUMBER(4),
    ENAME VARCHAR2(15) CONSTRAINT NNNAME NOT NULL,
    DEPTNO NUMBER(2),
    CONSTRAINT PK_EMP2_EMPNO PRIMARY KEY(EMPNO),
    CONSTRAINT FK_EMP2_DEPTNO FOREIGN KEY(DEPTNO REFERENCES DEPT2
    );
    -->EMP 2テーブルに2人の従業員情報を追加します.
    INSERT INTO EMP 2 VALuES(100,「洪吉童」,1);
    INSERT INTO EMP 2 VALUES(101,「洪順子」,2);親テーブルに2番目の部門が存在しないため、エラーが発生しました.
    INSERT INTO EMP 2 VALUES(101,「洪順子」,1);
    예3) 두 개이상의 컬럼이 PK로 설정된다면 테이블레벨로 설정해야 한다.
    CREATE TABLE MYSTUDENT
    (
    SNUM NUMBER(5)--学号
    SUBJECT VARCHAR 2(10)--カリキュラム名
    SCORE NUMBER(3)--得点
    CONTRAINT PK STUDENT PRIMARY KEY(SNUM,SUBJECT)--2つの列をPKとして指定
    );
    INSERT INTO MYSTUDENT VALUES(1,英語,100);
    INSERT INTO MYSTUDENT VALUES(1,「数学」,100);
    INSERT INTO MYSTUDENT VALUES(2,「英語」,100);
    INSERT INTO MYSTUDENT VALUES(2,「英語」,60);(エラー)学生番号+科目名欄に重複データがあり、PK制限条件違反!
    COMMIT;
    例4)
    CREATE TABLE DEPT3
    (
    DEPTNO NUMBER(3) CONSTRAINT PK_DEPT3 PRIMARY KEY,
    DNAME VARCHAR2(10) NOT NULL,
    LOC VARCHAR2(10),
    CONSTRAINT UK_DEPT3 UNIQUE(DNAME),
    CONTRANT CK DEPT 3 CHECK(「ソウル」「釜山」)
    );
    INSERT INTO DEPT 3 VALES(10、「部門1」、「ソウル」)
    INSERT INTO DEPT 3 VALES(10、「部門1」、「大邱」)検査制約違反
    INSERT INTO DEPT 3 VALES(11、「部門1」、「ソウル」)ユニークコンストレイントに反する
    2)制約の削除/追加
    # 제약조건 삭제
    - ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건명;
    예1) ALTER TABLE DEPT3 DROP CONSTRAINT PK_DEPT3;
    # 제약조건 추가
    - ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건명 제약조건(컬럼명);
    예2) ALTER TABLE DEPT3 ADD CONSTRAINT PK_DEPT3 PRIMARY KEY(DEPTNO);

  • シーケンス#シーケンス#
  • オブジェクト
  • は、自動的に増加する数値のシリアル番号を生成します.
  • 形式)
    CREATE SEQUENCEシーケンス名
    [ INCREMENT BY N ][ START WITH N ]
    [ MAXVALUE N| NOMAXVALUE N ][ MINVALUE N| NOMINVALUE N ]
    [ CYCLE | NOCYCLE ][ CACHE | NOCACHE ]
  • 削除

  • DROP SEQUENCEシーケンス名;
  • 関数
    nextval:次の値を取得
    Currval:現在の値
  • を取得
    例1)
    CREATE DEQUENCE MY_SEQ;
    SELECT MY_SEQ.NEXTVAL FROM DUAL; -- 現在の励起値
    例2)
    CREATE TABLE BOARD
    (
    NUM NUMBER(5) PRIMARY KEY,
    TITLE VARCHAR2(100),
    CONTENT VARCHAR2(100)
    );
    CREATE SEQUENCE BOARD1_SEQ;
    INSERT INTO BOARD VALUES(BOARD 1 SEQ.NEXTBAL、Oracle、テスト1);
    INSERT INTO BOARD VALUES(BOARD 1 SEQ.NEXTBAL、「Java」、「テスト2」);
    -シーケンスの削除
    DROP SEQUENCE BOARD1_SEQ;
    CREATE SEQUENCE MY_SEQ1
    INCREMENT BY 2
    START WITH !100;
    SELECT MY_SEQ1.NEXTVAL FROM DUAL;

  • 表示

  • ビューはテーブルの仮想テーブルであり、selectでクエリーされたデータの仮想テーブルです.
    1)フォーマット
    CREATE[ORREPLACE]VIEWビュー名
    AS
    サブクエリ
    [ WITH CHECK OPTION ][ WITH READ ONLY ]
    2)使用理由
    -セキュリティ設定
    -複雑で一般的なクエリーをビューで簡単に使用できるようにします.
  • 例1)
    CREATE VIEW EMP1
    AS
    SELECT EMPNO, ENAME, SAL, DEPTNO
    FROM EMP;

    scottユーザーにビューを作成する権限を付与


    CONN system/java1234
    GRANT CREATE VIEW TO scott;
    SELECT*FROM EMP1;

    ビューでは基本的にDMLを使用できます。


    INSERT INTO EMP 1 VALES(100、「金ジャワ」、1000、10);
    例2)
    #WITH READ ONLY=>ビューでDMLを使用できない
    CREATE OR REPLACE VIEW EMP1
    AS
    SELECT EMPNO, ENAME, SAL, DEPTNO
    FROM EMP
    WITH READ ONLY;
    INSERT INTO EMP 1 VALES(101、「金ジャワ」、1000、10);エラー
    例3)
    #WITH CHECK OPTION->DMLのみビュー条件に適合
    CREATE OR REPLACE VIEW VEMP
    AS
    SELECT EMPNO, ENAME, SAL, DEPTNO
    FROM EMP
    WHERE DEPTNO=10
    WITH CHECK OPTION;
    INSERT INTO VEMP VALES(200,「金チョルス」,2000,10);
    INSERT INTO VEMP VALUES(201,「金英熙」,2000,20);エラー
    ===>ビューの条件(部門番号10)が正しくないため、エラーが発生しました.
    3)ビューの種類
    <1>統合ビュー:テーブルとして作成されたビュー(デフォルトはDML操作)
    <2>複合ビュー:2つのテーブルに作成されたビュー(コネクタ付きビュー).DMLを使用できません
    4)ビューの削除
    フォーマット)DROP VIEWビュー名;
    DROP VIEW VEMP;
    SELECT VIEW_NAME FROM USER_VIEWS;
    Q 1)EMPテーブルおよびDEPTテーブルを用いて名称、職業、部門番号および部門名を照会する場合
    ビューを作成および使用/削除します.
    CREATE VIEW VEMP
    AS
    SELECT ENAME, JOB.E.DEPTNO, DNAME
    FROM EMP E, DEPT D
    WHERE E.DEPTNO=D.DEPTNO;
    SELECT*FROM VEMP;
    SELECT VIEW_NAME FROM USER_VIEWS;
    [2] PL/SQL
    - 구조적인 SQL언어와 달리 SQL를 절차적인 방식에 의해 사용하도록 설계한 언어.
    - PL/SQL의 단위는 블록이며 하나의 블록은 아래와 같은 세부분으로 나뉜다.
    
    (1) 선언부 : 변수나 상수를 선언하는 부분 
    (2) 실행부 : 실제 처리할 로직을 담당하는 부분
    (3) 예외처리부 : 실행부에서 처리하던 중 발생하는 각종 오류에 대한 처리 부분
    
    1) PL/SQL의 구조
    
       DECLARE            -- 선언부
        변수선언
       BEGIN              -- 실행부
        실행구문
       EXCEPTION          -- 예외처리
    예외처리구문	
       END;
       /  -- PL/SQL실행
    
       예1) 
    SET SERVEROUTPUT ON -- 출력모드 설정
    DECLARE
    	MSG VARCHAR2(20); -- 변수선언
    BEGIN
    	MSG:='안녕하세요';	
    	DBMS_OUTPUT.PUT_LINE('메시지:'||MSG); -- DBMS_OUTPUT.PUT_LINE는 System.out.println 같은 역할
    END;
    /	
    
       예2)
       DECLARE
    NUM1 NUMBER(5);
    NUM2 NUMBER(5);
    NUM3 NUMBER(5);
       BEGIN
    NUM1:=&NUM1;
    NUM2:=&NUM2;
    NUM3:=NUM1+NUM2;
    DBMS_OUTPUT.PUT_LINE(NUM1||'+'||NUM2||'='||NUM3);
      END;
      /
    
       2) IF ~ ELSE 문
           형식1)
    IF 조건식 THEN
      실행문;
    ELSE
      실행문;
    END IF;
    
    예1)
    DECLARE
    	NUM1 NUMBER(3);
    	NUM2 NUMBER(3);
    	NUM3 NUMBER(3);
    BEGIN
    	NUM1:=&NUM1;
    	NUM2:=&NUM2;
    	IF NUM1>NUM2 THEN
    		NUM3:=NUm1;
    	ELSE
    		NUM3:=NUM2;
    	END IF;
    	DBMS_OUTPUT.PUT_LINE('큰 수'||NUM3);
    END;
    /
    Enter value for num1: 1
    old 6: NUM1:=&NUM1;
    new 6: NUM1:=2;
    Enter value for num2: 2
    old 7: NUM2:=&NUM2;
    new 7: NUM2:=2;
    큰 수 2
    
    Q1) 임의의 정수를 입력 받아 짝수인지 홀수인지 출력해보세요. (나머지값 MOD연산자 사용)
    DECLARE
    	NUM1 NUMBER(3);
    BEGIN
    	NUM1:=&NUM1;
    	IF NUM1 MOD 2=0 THEN
    		DBMS_OUTPUT.PUT_LINE(NUM1||' 은 짝수');
    	ELSE
    		DBMS_OUTPUT.PUT_LINE(NUM1||' 은 홀수');
    	END IF;
    END;
    /
    
    Q2) 두 정수를 입력 받아 평균이 80 이상이면 합격 아니면 불합격 출력하기
    DECLARE
    	NUM1 NUMBER(3);
    	NUM2 NUMBER(3);
    	AVER NUMBER(5,2);
    BEGIN
    	NUM1:=&NUM1;
    	NUM2:=&NUM2;
    	AVER:=(NUM1+NUM2)/2;
    	DBMS_OUTPUT.PUT_LINE('평균:'||AVER);
    	IF AVER>=80 THEN
    		DBMS_OUTPUT.PUT_LINE('합격');
    	ELSE
    		DBMS_OUTPUT.PUT_LINE('불합격');
    	END IF;
    END;
    /
    
          형식2)
    IF 조건식 THEN 
      실행문;
    ELSIF 조건식 THEN
      실행문;
    ELSIF 조건식 THEN
      실행문;
      ..
    END IF;
    
      3) FOR문
        
    -형식
    FOR 변수 IN 초기값..마지막값 LOOP
    	반복수행할 문장;
    
    END LOOP;
    
    예1) 1부터 100까지 합 구하기
    DECLARE
    	TOT NUMBER(5):=0;
    BEGIN
    	FOR I IN 1..100 LOOP
    		TOT:=TOT+I;
    		DBMS_OUTPUT.PUT(I||' ');
    	END LOOP;
    	DMBS_OUTPUT.put_line(' ');
    	dbms_output.put_line('1부터 100까지 합:'||TOT);
    END;
    /
    
            Q1) 1부터 100까지 수 중 3의 배수출력하고 3의 배수합 구해서 출력하기
    DECLARE
    	TOT NUMBER(5):=0;
    BEGIN
    	FOR I IN 1..100 LOOP
    	IF I MOD 3=0 THEN
    		TOT:=TOT+I;
    		DBMS_OUTPUT.PUT(I||' ');
    	END IF;
    	END LOOP;
    	DBMS_OUTPUT.PUT_LINE('1부터 100까지 3의 배수 합:'||TOT);
    END;
    /
    
    Q2) 단 입력받아 구구단 출력하기
    DECLARE
    	NUM1 NUMBER(5):=&NUM1;
    BEGIN
    	DBMS_OUTPUT.PUT_LINE('['||NUM1||'단]');
    	FOR I IN 1..9 LOOP
    		DBMS_OUTPUT.PUT_LINE(NUM1||'*'||I||'='||NUM1*I||' ');
    	END LOOP;
    END:
    /
    
    Q3) 구구단 출력하기(2단부터 9단까지)
    BEGIN
    	FOR I IN 2..9 LOOP
    		DBMS_OUTPUT.PUT('['||I||'단]');
    		FOR J IN 1..9 LOOP
    			DBMS_OUTPUT.PUT(I||'*'||J||'='||I*J||' ');
    		END LOOP;
    		DBMS_OUTPUT.PUT_LINE('');
    	END LOOP;
    END;
    
       - EXIT : FOR문을 강제로 빠져나가기
        
       예2) 정수입력받아 소수 판별하기
    DECLARE
    	NUM1 NUMBER(5);
    BEGIN
    	NUM1:=&NUM1;
    	IF NUM1=2 THEN
    		DBMS_OUTPUT.PUT_LINE(NUM1||'은 소수입니다.');
    	END IF;
    	FOR I IN 2..NUM1-1 LOOP
    		IF NUM1 MOD I=0 THEN
    			DBMS_OUTPUT.PUT_LINE(NUM1||'은 소수가 아닙니다.');
    			EXIT:
    		END IF;
    		IF I =NUM1-1 THEN
    			DBMS_OUTPUT.PUT_LINE(NUM1||'은 소수입니다.');
    			EXIT;
    		END IF;
    	END LOOP;
    END;
    /