[Oracle] View,Trigger 실습

이번에 해야할게 좀 많다

1. trigger 만들기
2. updateable view 생성
3. check option view 생성
4. subqueries
5. ranking queries .. 

열심히 해보자

 

1. sql 접속

일단 sql에 접속한다. 

 

 

터미널 창에 sqlplus 입력 후 저번시간에 만들었던 사용자명,비밀번호에 접속하면 된다.

 

 

2. 테이블 생성

 

왼쪽 상단 + 누르고 

 

 

저번에 했던것처럼 적고 접속 하면 가볍게 만들 수 있습니다.

 

sql문을 작성하고, 새로고침 버튼을 누르면 왼쪽에 Customer랑 Orders 생긴것을 확인 할 수 있다.

계속 명령문을 실행해야 하기 때문에 맨위에 drop명령문을 추가했다.

 

Drop table Orders;
Drop table Customers;

CREATE TABLE Customers (
  CustomerID INT PRIMARY KEY,
  CustomerName VARCHAR(255),
  City VARCHAR(255)
);

CREATE TABLE Orders (
  OrderID INT PRIMARY KEY,
  CustomerID INT,
  OrderDate DATE,
  TotalAmount DECIMAL(10, 2),
  FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

 

3. Insert

만든 테이블에 값을 넣어준다. 

INSERT INTO Customers values(1, 'John Smith', 'New York');
INSERT INTO Customers values(2, 'Jane Doe', 'Los Angeles');
INSERT INTO Customers values(3, 'Michael Johnson', 'Chicago');

       
INSERT INTO Orders values(1,1,TO_DATE('2023-01-10', 'YYYY-MM-DD'), 100.50);
INSERT INTO Orders values(2,1,TO_DATE('2023-02-15', 'YYYY-MM-DD'), 200.75);
INSERT INTO Orders values(3,2,TO_DATE('2023-02-20', 'YYYY-MM-DD'), 150.25);

 

데이터 눌러보면 값을 확인 할 수 있다.

 

 

4. update view생성

 

create view myorder as 
select OrderID,OrderDate,TotalAmount
from Orders
where Totalamount>150;

 

그냥 간단한 뷰 하나 만들어줬는데

 

 

권한이 불충분하다며 자꾸 오류가 뜬다.

해결방법은 

2023.06.05 - [DB] - [Oracle] ORA-01031 : 권한이 불충분합니다.

[Oracle] ORA-01031 : 권한이 불충분합니다.

보고 해결하면 됩니다 ㅎㅎ

 

잘 생성된것을 확인 할 수 있다.

 

5. trigger 생성

:trigger란 무결성 제약 관리를 위해 지원하는 기능이다.

 

--triger(with referencing and when)
CREATE OR REPLACE TRIGGER OrdersTrigger
BEFORE INSERT ON Orders
REFERENCING NEW AS NewOrder
FOR EACH ROW
WHEN (NewOrder.TotalAmount > 500)
BEGIN
  DBMS_OUTPUT.PUT_LINE('Order amount exceeds $500. Please contact the customer for verification.');
END;
/

 

꼭 마지막에 "/" 를 입력하자..

삽질을 한시간 한 것 같다.

 

DBMS_OUTPUT.PUT_LINE 명령문을 사용하기 위해 코드 상단에

SET SERVEROUTPUT ON FORMAT WRAP;

추가해준다.

INSERT INTO Orders values(10,2,TO_DATE('2023-02-20', 'YYYY-MM-DD'), 550.25);

조건에 맞는 쿼리를 돌려보면

 

트리거가 잘 작동되는 것을 볼 수 있다.

 

 

6. check with option

: check with option은 갱신된 뷰를 통하여 갱신 효과를 사용자가 볼 수 있을 경우에만 뷰 갱신을 허용해준다

 

--with check option view
create view Customer1Order as 
select OrderId,CustomerID,TotalAmount
from Orders
where CustomerID=1
with check option;

 

성공 ~ 

잘 작동하는지 확인해보자

Insert into Customer1Order values (11,1,100.12);
Insert into Customer1Order values (12,2,100.12);

아래 코드를 실행하면 오류가 발생해야 한다.

 

 

첫번째 데이터는 잘 들어갔고, 두번째 실행문을 실행하면 오류가 발생하는 것을 확인 할 수 있다.

 

 

나머지는 간단히 코드만 소개하겠다~

7.correlated subqueries

SELECT CustomerName
FROM Customers c
WHERE EXISTS (
  SELECT *
  FROM Orders o
  WHERE o.CustomerID = c.CustomerID
    AND o.TotalAmount > 200
);

 

 

8. nested subqueries

SELECT OrderID, OrderDate, TotalAmount
FROM Orders
WHERE TotalAmount >= SOME (SELECT TotalAmount 
                    FROM Orders 
                    WHERE TotalAmount > 200)
ORDER BY OrderDate DESC
FETCH FIRST 1 ROW ONLY;

 

 

9. ranking

SELECT CustomerName, TotalAmount
FROM (SELECT CustomerName, TotalAmount, RANK() OVER (ORDER BY TotalAmount DESC) AS Rank
      FROM Customers
      JOIN Orders ON Customers.CustomerID = Orders.CustomerID) RankedCustomers
WHERE Rank <= 3;

 

 

끝 !