Day29

1.変換を理解し実現する.
2.lockの概念が理解できる.
3.Oracleのプロセスを設計および説明できます.
4.PL/SQL文法を理解し、基本文法と設計を学ぶ.

質問する

Q1.≪トランザクション|Transaction|emdw≫:データの一貫性を維持するための論理的関連タスクのセット.

関連DML構文

DDL構文

トランザクション開始:最初のDML構文を実行すると

が開始します.

トランザクション終了:

1)COMMIT/ROLLBACKコマンド実行時のタイプ
2)DDL構文実行時終了->AUTO COMMIT
3)SQL*PLUS OR DBSERVER異常終了時->AUTO ROLLBACK(バックアップなし)

トランザクション制御
COMMIT(保存):変更されたデータ(挿入、削除、更新)を保存し、トランザクションを終了するコマンド
ROLLBACK(キャンセル):トランザクションの変更をキャンセルして終了するコマンド
デフォルトでは、データ・ステータスはトランザクションの開始点に復元されます.
SAVEPOINT savepoint name:トランザクションの特定の時点を記録するコマンド
ROLLBACK TO savepoint name:指定された時点でデータ状態を回復できる

commit :
変更をデータベース(ファイル)に保存して反映
すべてのユーザーに同じ結果が表示されます.
同じ行に対して異なる変更を行うことができます.
これまでに設定したすべてのsavepointが消えます
rollback :
データ移行ステータスに復元します.
同じ行に対して他の変更を加えることができます
これまでに設定したsavepointはすべて消えます.

トランザクションデータ
1)他のユーザは、動作中のDMLの結果を表示できない.
2)処理中のローに対して他の変更はできません
Q2.トランザクションとデータのステータスを確認します.

ALTER TABLE EMPLOYEE
DISABLE CONSTRAINTS FK_MGRID;

SAVEPOINT S0;

ALTER TABLE DEPARTMENT
DROP COLUMN MGR_ID;

ALTER TABLE DEPARTMENT
DROP COLUMN MGR_ID02;

INSERT INTO DEPARTMENT VALUES('40','기획전략팀','A1');

SAVEPOINT S1;

UPDATE EMPLOYEE SET DEPT_ID ='40'
WHERE DEPT_ID IS NULL;

SAVEPOINT S2;
DELETE FROM EMPLOYEE;

ROLLBACK TO S2; -- SAVEPONT S2 를 롤백시킨다.

SELECT COUNT(*) FROM EMPLOYEE; --22개

SELECT COUNT(*) FROM EMPLOYEE
WHERE DEPT_ID = '40';

ROLLBACK TO S1;

SELECT COUNT(*) FROM DEPARTMENT
WHERE DEPT_ID ='40'; --1

40번 사원 테이블도 확인

ROLLBACK TO S0;
1,2번 확인

ROLLBACK

Q3.同期LOCK:複数のユーザが同じデータに同時にアクセスして変更を試みることを許可する
整合性を確保するために同期を制御する必要があります
データ同期制御技術の特徴
1)異なるユーザー(異なるトランザクション)が同時に同じローを変更しないようにする
2)他のユーザー(他の取引)がCOMMITを経ていない変更を書き換えることを防止する
3)取引実行中の自動実行/メンテナンス/管理
セッション1

SELECT EMP_NAME,
MARRIAGE
FROM EMPLOYEE
WHERE EMP_ID = '143';

UPDATE EMPLOYEE
SET MARRIAGE = 'N'
WHERE EMP_ID ='143';

MARRIAGEをNに変更
セッション2

SELECT EMP_NAME,
MARRIAGE
FROM EMPLOYEE
WHERE EMP_ID = '143';

UPDATE EMPLOYEE
SET MARRIAGE ='N'
WHERE EMP_ID='143';

セッション1はMARRIAGEをNに変更しますが、COMMIT OR ROLLBACKはありません.
LOCKが必要で、COMMIT ORROLLBACKはLOCKを解放してUPDATEを実行することができます.
✨
データ型/変数と定数/演算子/標準I/O/制御文の繰り返し、選択、分岐文/関数、プロシージャ(Xを返す)メソッド(OOP)ファイルI/O
IF条件THENコマンド
ELSIF THEN
ELSE
END IF;
Q3.点数の単位をA~FでIF文と表す.

DECLARE
 score01 int := 85;
 grade01 varchar2(2);
BEGIN
 IF score01 >= 90  THEN grade01 :='A';
 ELSIF score01 >=80 THEN grade01 :='B';
 ELSIF score01 >=70 THEN grade01 :='C';
 ELSIF score01 >=60 THEN grade01 :='D';
 ELSE grade01 :='F';
END IF;
 DBMS_OUTPUT.PUT_LINE('SCORE '||score01||',GRADE ='||grade01);
END;

Q4.'洪吉童1'3回出力

DECLARE
 NAME VARCHAR2(10) :='홍길동';
 I   INT :=0;
BEGIN
 FOR I IN 1..3 LOOP
 DBMS_OUTPUT.PUT_LINE(NAME||I);
END LOOP;
END;

Q5.テーブルを作成し、クエリーを繰り返し、資料を入れます.

CREATE TABLE TEST01(
NO NUMBER(3),
IRUM VARCHAR2(10));

DECLARE

BEGIN
 FOR I IN 1..10 LOOP
 INSERT INTO TEST01 VALUES(I,SYSDATE);
END LOOP;
END;

Q6.ググダンは嫌いだけどやってみよう奇数段だけ出力しよう

DECLARE
 TOTAL NUMBER := 0;
BEGIN
 FOR I IN 2..9 LOOP
   IF MOD(1,2) != 0 THEN DBMS_OUTPUT.PUT_LINE(I || '단');
    FOR J IN 1..9 LOOP
             TOTAL := I*J;
            DBMS_OUTPUT.PUT_LINE(I||'*'||J||'='||TOTAL);
       END LOOP;
    END IF;
  END LOOP;
END;

Q7.EMPテーブルでは、匿名のブロックを作成し、10番の従業員の平均賃金を出力します.
EX)OOO部門の平均賃金は00元です.

DECLARE
    MY_AVG NUMBER(7,2) :=0;
    MY_DEPTNO NUMBER(7) :=10; --MY_DEPTNO EMP.DEPTNO%TYPE :=10;
BEGIN
  SELECT AVG(SAL) INTO MY_AVG
  FROM EMP
  WHERE DEPTNO = MY_DEPTNO
  GROUP BY DEPTNO;
  DBMS_OUTPUT.PUT_LINE(MY_DEPTNO ||'부서의 평균 급여는['||MY_AVG||']원 입니다');
END;

Q8.EMP表では、20番部門の個数、平均賃金を見つけます.
EX)OOO部門の平均賃金はOOで、従業員数はOOです.

DECLARE 
  MY_AVG NUMBER(7,2) :=0;
  MY_DEPTNO NUMBER(7) :=20;
  MY_CNT NUMBER(7) :=0;
BEGIN
SELECT COUNT(*),AVG(SAL) INTO MY_CNT,MY_AVG
FROM EMP
WHERE DEPTNO = MY_DEPTNO
GROUP BY DEPTNO;
DBMS_OUTPUT.PUT_LINE(MY_DEPTNO ||'부서의 평균 급여는['||MY_AVG||']원이고,사원의 수는 '||MY_CNT||'명 입니다');
END;

Q9.ACCEPT:ユーザ入力値を読み出して変数に格納するためのキーワード
4番の従業員の名前を印刷します.

ACCEPT inputVal prompt'사번입력:'

DECLARE 
    NAME1 EMP.ENAME%TYPE;
BEGIN
    SELECT ENAME INTO NAME1 FROM EMP
    WHERE EMPNO=&inputVal;
    DBMS_OUTPUT.PUT_LINE('현재의 결과 : '||NAME1)
END;

Q10.4回入力して100パーセントの給料を増やす

CREATE OR REPLACE PROCEDURE UPDATE_SAL(V_empno IN NUMBER)
AS 
BEGIN
 UPDATE EMP
 SET SAL  = SAL+100
 WHERE EMPNO = V_EMPNO;
 COMMIT;
END UPDATE_SAL;

Q11.従業員番号を入力し、従業員番号、従業員名、従業員給与を印刷します.

CREATE OR REPLACE PROCEDURE EXAM01 (RES_EMPNO IN EMP.EMPNO%TYPE)
AS 
    V_EMPNO  EMP.EMPNO%TYPE;
    V_ENAME EMP.ENAME%TYPE;
    V_SAL EMP.SAL%TYPE;
    
BEGIN
    DBMS_OUTPUT.ENABLE;
    SELECT EMPNO,ENAME,SAL INTO V_EMPNO,V_ENAME,V_SAL
    FROM EMP
    WHERE EMPNO = RES_EMPNO;
    DBMS_OUTPUT.PUT_LINE('사원의 번호 : '||V_EMPNO);
    DBMS_OUTPUT.PUT_LINE('사원의 이름 : '||V_ENAME);
    DBMS_OUTPUT.PUT_LINE('사원의 봉급 : '||V_SAL);

END EXAM01;

Q12.関数をバインドします.

CREATE FUNCTION GET_MYAVG(V_ID IN EMP.EMPNO%TYPE)
RETURN NUMBER
AS
  V_SAL EMP.SAL%TYPE :=0;
  V_TOT NUMBER :=0;
  V_COMM EMP.COMM%TYPE;

BEGIN
    SELECT SAL,COMM INTO V_SAL,V_COMM
    FROM EMP
    WHERE EMPNO = V_ID;
    V_TOT := V_SAL*12 +NVL(V_COMM,0);
    RETURN V_TOT;
END;
/

SELECT EMPNO,GET_MYAVG(7934)
FROM EMP;