주:

Oracle Database 23ai의 SQL 기능 살펴보기

소개

이 자습서에서는 알아야 할 10가지 기능과 기존 기능과 비교하는 방법을 배웁니다. 이러한 기능은 다음과 같습니다.

목표

필요 조건

기능 1: FROM 절 사용

Oracle Database 23ai에 도입된 흥미로운 기능은 SELECT 문에서 FROM 절을 선택할 수 있는 옵션입니다. 이 버전까지 FROM 절이 필요합니다.

다음은 Oracle Database 23ai에서 FROM 기능이 없는 SELECT의 몇 가지 잠재적 이점입니다.

기능 2: BOOLEAN 데이터 유형 사용

Oracle Database 23ai는 새로운 BOOLEAN 데이터 유형을 도입했습니다. 이렇게 하면 숫자 값 또는 Varchar로 시뮬레이션하는 대신 실제 부울 열/변수를 사용할 수 있습니다. 부울 술어를 작성하는 기능은 SQL 문의 구문을 단순화합니다.

  1. TEST_BOOLEAN이라는 테이블을 생성합니다.

    CREATE TABLE IF NOT EXISTS TEST_BOOLEAN (name VARCHAR2(100), IS_SLEEPING BOOLEAN);

Table TEST_BOOLEAN created.

Elapsed: 00:00:00.004

  2. 새 테이블에 데이터를 입력합니다. IS_SLEEPING 값은 기본값으로 FALSE로 설정된 NOT NULL입니다.

    ALTER TABLE TEST_BOOLEAN modify (IS_SLEEPING boolean NOT NULL);

    SQL에 부울을 사용하는 방법을 보여주는 이미지 

    ALTER TABLE TEST_BOOLEAN modify (IS_SLEEPING default FALSE);

Table TEST_BOOLEAN altered.

Elapsed: 00:00:00.014

    여기에서는 Mick, Keith 및 Ron에 대한 다양한 유형의 부울 입력을 볼 수 있습니다. 모두 유효합니다. Mick의 경우 기본 FALSE 값이 사용됩니다. Mick은 일시 정지 상태가 아닙니다.

    1 row inserted.

Elapsed: 00:00:00.006

    Keith의 경우 NO 값을 사용합니다. Keith는 일시 정지 상태가 아닙니다.

    INSERT INTO TEST_BOOLEAN (name, is_sleeping) values ('Keith','NO');

1 row inserted.

Elapsed: 00:00:00.002

    Ron의 경우 1 값을 사용합니다. Ron이 절전 상태입니다.

  3. 우리의 부울 값을 기반으로 몇 가지 결과를 참조하십시오.

    SELECT * FROM test_boolean;

    어떤 종류의 부울 시스템을 배치했는지 더 이상 기억할 필요가 없습니다. 앞에서 설명한 것처럼 0/1, True/False, Yes/No 또는 기타 공통 입력을 사용하면 정확한 테이블 값이 반환됩니다.

기능 3: IF NOT EXISTS DDL 절 사용

Oracle Database 23ai부터 새 IF NOT EXISTS DDL 절을 통해 DDL 오류 처리 방법을 결정할 수 있습니다. 이렇게 하면 객체가 존재하거나 존재하지 않기 때문에 발생할 수 있는 오류를 스크립팅에 숨길 수 있으므로 DDL 스크립팅이 간소화됩니다.

  1. 먼저 이 새 기능을 사용하지 않고 테스트합니다. 다음 명령문을 실행합니다.

    DROP TABLE DEPT;

    삭제할 기존 DEPT 테이블이 없으므로 ORA-00942: table or view does not exist 오류가 표시됩니다.

  2. 그러나 Oracle Database 23ai에서는 오류 없이 DROP IF EXISTS를 사용할 수 있습니다. 이것은 우리에게 오류를 피하면서 마음의 평화를줍니다. 이제 동일한 명령문을 실행하지만 이 새 IF EXISTS 기능을 포함합니다.

    DROP TABLE IF EXISTS DEPT;

  3. 마찬가지로, 이 기능을 사용하여 테이블이 없는 경우 테이블을 생성할 수 있습니다. 해당 DEPT 테이블을 생성합니다.

    CREATE TABLE IF NOT EXISTS DEPT
	(DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
	DNAME VARCHAR2(14) ,
	LOC VARCHAR2(13) ) ;

    IF [NOT] EXISTS DDL 절을 사용하는 방법을 보여주는 이미지

  4. 이 기능을 사용하여 이 자습서 전체에서 더 많은 샘플 테이블을 생성할 수 있습니다. 여기서는 EMP이라는 직원 테이블을 만듭니다.

    CREATE TABLE IF NOT EXISTS EMP
(EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);

Table EMP created.

Elapsed: 00:00:00.006

기능 4: 다중 행에 INSERT 사용

다른 자주 사용되는 데이터베이스 관리 시스템과의 공존 및 호환성을 향상시키는 또 다른 흥미로운 기능은 다중 값 INSERT 문입니다.

  1. 이전 버전의 Oracle 데이터베이스에서 예를 들어, 여러 행을 삽입하려면 각 행에 대해 별도의 insert 문이 필요했습니다.

    INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');
INSERT INTO DEPT VALUES (50,'HR','LOS ANGELES');
INSERT INTO DEPT VALUES (60,'IT','SAN FRANCISCO');
INSERT INTO DEPT VALUES (70,'MANUFACTURING','DETROIT');

    Oracle Database 23ai는 이러한 모든 행을 하나의 INSERT 문으로 삽입할 수 있는 새로운 구문을 도입했습니다. 따라서 하나의 DML에 여러 튜플을 삽입할 수 있습니다. 다음 명령문을 실행합니다.

    INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK'),
(20,'RESEARCH','DALLAS'),
(30,'SALES','CHICAGO'),
(40,'OPERATIONS','BOSTON'),
(50,'HR','LOS ANGELES'),
(60,'IT','SAN FRANCISCO'),
(70,'MANUFACTURING','DETROIT');

    다중 값 INSERT 사용 방법을 보여주는 이미지

    다른 데이터베이스와의 호환성 향상 외에도 이 명령문을 사용하여 자동 커밋 모드에서 일부 삽입 작업의 일관성을 보장할 수 있습니다. 예를 들어, 이 모드를 사용하는 Oracle APEX 애플리케이션의 경우 데이터 작업을 수행하는 것이 중요할 수 있습니다.

  2. 이 기능을 사용하여 EMP 테이블에 대한 값을 채우려면 다음 명령문을 실행합니다.

    INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20),
(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30),
(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30),
(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20),
(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30),
(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30),
(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10),
(7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87')-85,3000,NULL,20),
(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10),
(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30),
(7876,'ADAMS','CLERK',7788,to_date('13-JUL-87')-51,1100,NULL,20),
(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30),
(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20),
(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);

기능 5: 새 VALUE 생성자 사용

Oracle 데이터베이스 23ai부터 테이블 값 생성자가 확장되었습니다. 이제 INSERT 문에서 단일 명령으로 여러 행을 생성하는 데 사용할 수 있습니다. 또한 SELECT 문 및 뷰 계수화 구문에서 사용할 수 있습니다. 이 마지막 경우 명령문의 구문을 단순화하고 DUAL 테이블 사용을 피합니다.

다음 명령문은 일종의 테이블 함수처럼 보입니다.

SELECT * FROM (VALUES (50,'HR'), (60,'DEV'), (70,'AI')) virt_dept (deptno, dname);

새 테이블 값 생성자 사용 방법을 보여주는 이미지

기능 6: GROUP BY 절에서 별칭 사용

Oracle Database 23ai는 SELECT 문의 GROUP BY 절에서 별칭을 사용할 수 있는 기능을 도입했습니다. 이 기능은 복잡한 표현식으로 쿼리 작성을 단순화하고 Teradata, MySQL 및 PostgreSQL와 같은 다른 관계형 데이터베이스와의 호환성을 개선합니다.

예:

SELECT to_char(hiredate,'YYYY') "Year", count(*)
FROM emp
GROUP BY to_char(hiredate,'YYYY');

Oracle Database 23ai에서는 다음 이미지와 같이 간단한 방법으로 작성할 수 있습니다.

GROUP BY 절에서 별칭을 사용하는 방법을 보여주는 이미지

기능 7: UPDATE 및 MERGE 문의 RETURNING 절 사용

이 절은 EXECUTE IMMEDIATE 문의 일부로 이전에 구현되었습니다. 그러나 Oracle Database 23ai에서는 기존의 정적 DML 문의 일부로 이를 찾을 수 있습니다.

  1. 이 경우 처리된 행에서 열의 이전 값과 새 값을 가져올 수 있습니다. 먼저 왕의 현재 월급을 보자.

    SELECT ename, sal FROM emp WHERE ename = 'KING';

  2. LiveSQL에서 변수를 사용하기 위해 PL/SQL에서 명령문을 래핑합니다. 이 스크립트를 실행합니다. 먼저 이전 급여와 새 급여에 대한 변수를 생성한 다음 RETURNING 절을 사용하여 King의 급여를 갱신하여 변수를 설정합니다. 그런 다음 결과를 둘러봅니다.

    BEGIN
	DECLARE
		old_salary NUMBER;
		new_salary NUMBER;
	BEGIN
		UPDATE emp
		SET sal = sal + 1000
		WHERE ename = 'KING'
		RETURNING OLD sal, NEW sal INTO old_salary, new_salary;
		DBMS_OUTPUT.PUT_LINE('Old Salary: ' || old_salary);
		DBMS_OUTPUT.PUT_LINE('New Salary: ' || new_salary);
	END;
END;

    UPDATE 및 MERGE 문의 RETURNING 절을 사용하는 방법을 보여주는 이미지 

    Old Salary: 6000
New Salary: 7000

    이 예제에서는 UPDATE 문을 사용했지만 RETURNING 절은 MERGE 문과 유사하게 사용할 수 있습니다.

기능 8: UPDATE 및 DELETE에서 조인 사용

외래 테이블 조건에 준하여 조인을 사용하여 테이블 데이터를 갱신할 수 있습니다. 하위 선택 또는 IN 절이 필요하지 않습니다.

  1. 다음 명령문을 실행하여 연구 부서의 사원 급여 정보를 확인합니다.

    select e.sal, e.empno from emp e, dept d where e.deptno=d.deptno and d.dname='RESEARCH';

  2. 급여 정보를 갱신하려면 Oracle Database 23ai 이전에 중첩된 명령문을 사용해야 합니다.

    UPDATE emp e set e.sal=e.sal*2
WHERE e.deptno in
(SELECT d.deptno FROM dept d WHERE e.deptno=d.deptno
and d.dname='RESEARCH');

    Oracle Database 23ai를 사용하면 다음과 같이 사용할 수 있습니다.

    UPDATE emp e set e.sal=e.sal*2  FROM dept d  WHERE e.deptno=d.deptno  and d.dname='RESEARCH';

    UPDATE 및 DELETE에서 조인을 사용하는 방법을 보여주는 이미지

  3. 급여가 성공적으로 갱신된 것을 확인할 수 있습니다.

    select e.sal, e.empno from emp e, dept d where e.deptno=d.deptno and d.dname='RESEARCH';

    UPDATE 및 DELETE에서 조인을 사용하는 방법을 보여주는 이미지

피쳐 9: 주석 사용

주석은 데이터베이스 객체에 대한 선택적 메타 데이터입니다. 주석은 이름-값 쌍이거나 이름 자체입니다. 이름 및 선택적 값은 자유 형식 텍스트 필드입니다. 주석은 주석이 추가된 데이터베이스 객체에 대한 하위 요소로 표시됩니다. 지원되는 스키마 객체에는 테이블, 뷰, Materialized View 및 인덱스가 포함됩니다. annotation을 사용하면 데이터베이스 객체에 대한 메타 데이터를 저장하고 검색할 수 있습니다. 이를 사용하여 비즈니스 논리, 사용자 인터페이스를 사용자정의하거나 메타데이터 저장소에 메타데이터를 제공할 수 있습니다. 테이블 또는 열 레벨에서 CREATE 또는 ALTER 문을 사용하여 추가할 수 있습니다.

annotation을 사용하면 데이터베이스 객체에 대한 메타 데이터를 저장하고 검색할 수 있습니다. 이를 사용하여 비즈니스 논리, 사용자 인터페이스를 사용자정의하거나 메타데이터 저장소에 메타데이터를 제공할 수 있습니다.

  1. 열 및 테이블 주석이 포함된 주석이 달린 테이블 EMP_ANNOTATED_NEW를 생성합니다.

    CREATE TABLE emp_annotated_new
	(empno number annotations(identity, display 'person_identity', details 'person_info'),
	ename varchar2(50),
	salary number annotations (display 'person_salary', col_hidden))
	annotations (display 'employee_table');

    주석 사용 방법, 데이터베이스 객체에 대한 새 메타데이터를 보여주는 이미지

  2. USER_ANNOTATIONS 및 USER_ANNOTATIONS_USAGE와 같은 데이터 딕셔너리 뷰는 사용을 모니터하는 데 도움이 될 수 있습니다.

    SELECT object_name, object_type, column_name, annotation_name, annotation_value FROM user_annotations_usage;

    주석 사용 방법, 데이터베이스 객체에 대한 새 메타데이터를 보여주는 이미지

기능 10: SQL 도메인 사용

SQL 도메인은 스키마에 속하는 딕셔너리 객체로, 선택적 속성 및 제약 조건 집합을 캡슐화하고 CREATE DOMAIN 문으로 생성됩니다. 도메인은 제약 조건, 표시, 순서 및 주석 속성을 제공합니다. SQL 도메인을 정의한 후에는 해당 도메인과 연관될 테이블 열을 정의하여 해당 열에 도메인의 선택적 속성 및 제약 조건을 명시적으로 적용할 수 있습니다.

SQL 도메인을 사용하면 사용자가 의도한 열 사용을 선언할 수 있습니다. 추상적 도메인 관련 지식을 쉽게 재사용할 수 있도록 하는 데이터 딕셔너리 객체입니다.

  1. yearbirth라는 도메인과 person라는 테이블을 생성합니다.

    CREATE DOMAIN yearbirth as number(4)
constraint check ((trunc(yearbirth) = yearbirth) and (yearbirth >= 1900))
display (case when yearbirth < 2000 then '19-' ELSE '20-' end)||mod(yearbirth, 100)
order (yearbirth -1900)
annotations (title 'yearformat');

    SQL 도메인에서 경량 객체 유형을 사용하는 방법을 보여주는 이미지

    CREATE TABLE person
(id number(5),
name varchar2(50),
salary number,
person_birth number(4) DOMAIN yearbirth
)
annotations (display 'person_table');

    SQL 도메인에서 경량 객체 유형 사용 방법을 보여주는 이미지

    "Desc person;

    Name Null? 유형

ID 번호(5) 이름 VARCHAR2(50) 급여 번호 PERSON_BIRTH 번호(4) 도메인 생년월일

INSERT INTO 개인 값(1,'MARTIN',3000, 1988);

2. With the new function `DOMAIN_DISPLAY` you can display the property.

SELECT DOMAIN_DISPLAY(person_birth) FROM person;


   ![Image showing how to use Light weight object types" with SQL Domains ](./images/Picture14.png "Light weight object types with SQL Domains")

3. Domain usage and annotations can be monitored with data dictionary views. Let us view `user_annotations_usage`.

user_annotations_usage에서 *를 선택하십시오;


   Define reusable domain types (lightweight objects).

EmailAddress 도메인을 VARCHAR2(100)으로 생성합니다. (REGEXP_LIKE(VALUE, '^[^@]+@[^@]+.[^@]+$'));

CREATE TABLE users ( user_id NUMBER, email EmailAddress ); ```

SQL 도메인에서 경량 객체 유형 사용 방법을 보여주는 이미지

Oracle Database 23ai 기능의 제한 사항 및 제한 사항

일반 제한 사항

승인

추가 학습 자원

docs.oracle.com/learn에서 다른 랩을 탐색하거나 Oracle Learning YouTube 채널에서 더 많은 무료 학습 콘텐츠에 액세스하세요. 또한 education.oracle.com/learning-explorer를 방문하여 Oracle Learning Explorer가 되십시오.

제품 설명서는 Oracle Help Center를 참조하십시오.