SQL쿼리

[Oracle] 오라클 - Top-N / 페이징 처리 / INVISIBLE column 오라클 12C부터 변경된 사항들이다! ✔️ Top-N 이란? : 반환되는 행수를 제한할 때 쓴다. : 데이터를 통해 페이징 할 때 매우 유용하다. 마리아DB 에는 없는 내용이라고 한다. 📓 FETCH : 리턴할 행수 또는 백분율 지정 📓 PERCENT : 백분율을 지정한다. -- 처음 레코드부터 3개만 출력하기 SELECT * FROM emp FETCH FIRST 3 ROWS ONLY; -- 급여 내림차순 정렬하여 처음 레코드부터 3개만 출력 SELECT * FROM emp ORDER BY sal DESC FETCH FIRST 3 ROWS ONLY; -- 급여 내림차순, 2개의 레코드는 건너뛰고 3개만 출력 SELEC..

[Oracle] 오라클 - 계정관리 (서버 연결, 패스워드 변경, 실행) ✔️ 오라클 서버 연결 사용법 : tnsping ✔️ SQLPLUS 실행 -- 관리자계정 : 오라클을 설치한 O/S 계정만 가능하다. cmd>sqlplus sys/"암호" AS SYSDBA ✔️ 사용자 계정 관리 -- 계정 생성 형식 CREATE USER 사용자명 IDENTIFIED BY 패스워드 [ DEFAULT TABLESPACE tablespace_name ] [ TEMPORARY TABLESPACE temp_tablespace_name ] [ QUOTA { size_clause | UNLIMITED } ON tablespace_name ] ; -- 계정 수정 형식 ALTER USER 사용자명 IDENTIFIED BY 패스워드..

[Oracle] 오라클 - 패키지(Package) ✔️ 패키지란? -- 서로 관련된 함수 등을 묶음하는 것을 패키지라고 한다. -- 선언부와 본문으로 나뉜다. -- 프로시저, 함수는 오버로딩(중복 정의) 가능하다. ⭐ 오버로딩 : 매개변수 타입, 개수가 다르면 동일한 이름으로 프로시저나 함수를 여러 개 정의할 수 있는데, 이를 오버로딩이라고 한다. ⭐ 패키지는 2번 작업한다. (선언/몸체) -- 실행 EXEC 피키지명.프로시저명(인수) -- 패키지 선언 CREATE OR REPLACE PACKAGE pEmp IS FUNCTION fnTax(p IN NUMBER) RETURN NUMBER; PROCEDURE empList(pName VARCHAR2); PROCEDURE empList; -- 중복 정의. 패..

[Oracle] 오라클 - PL/SQL (트리거 Trigger) ✔️ 트리거 Trigger : 미리 정해 놓은 특정 조건이 만족하거나 어떤 동작이 수행하면 자동으로 실행하도록 정의한 동작 : 예를 들어 DML(INSERT, UPDATE, DELETE) 문장이 실행되거나 DDL(CREATE, ALTER, DROP) 문장이 실행될 때 트리거가 실행될 수 있다. : 트리거는 CREATE TRIGGER 시스템 권한이 있어야 생성 가능하다. : 트리거 내에서는 자동 커밋 된다!! GRANT CREATE TRIGGER TO sky; 관리자에서 권한 주기 -- sky 계정에서 시스템 권한 확인 SELECT * FROM user_sys_privs; ✔️ 문장 트리거 Trigger : 하나의 DML 문에서 트리거는 한번..

[Oracle] 오라클 - PL/SQL (예외처리) ✔️ 예외처리 -- 실행되는 동안 발생하는 에러를 예외라고 한다. -- 컴파일 에러 / 런타임 에러 가 있다. -- 오라클도 예외를 정의할 수 있다. (사용자 정의 예외) -- 미리 정의 하지 않은 예외도 있다. -- 예외 블록 exception = when 절 안의 문장들을 수행하게 된다. -- exception_name EXCEPTION; -- 예외 발생 RAISE 예외 이름; RAISE_APPLICATION_ERROR; -- 시스템에서 제공하는 예외 DECLARE vName VARCHAR2(30), vSal NUMBER1 BEGIN SELECT name, sal INTO vName, vSal FROM emp WHERE empNo = '1001'; ..

[Oracle] 오라클 - PL/SQL 커서(Cursor) / 동적쿼리 ✔️ 커서(Cursor) : 하나의 레코드가 아닌, 여러 레코드로 구성된 작업영역에서 SQL 문을 실행하고 그 과정에서 생긴 정보를 저장하기 위해 CURSOR 를 사용한다. : 오라클 서버에 의해 실행되는 모든 SQL 문은 각각의 커서를 소유한다. : 1)암시적 커서 (선언) 2)명시적 커서 (프로그래머가 선언하는 이름있는 커서) ✔️ 암시적 커서(Cursor) - SQL%ROWCOUNT : 해당 SQL 문에 영향을 받는 행의 수 - SQL%FOUND : 해당 SQL 영향을 받는 행의 수가 1개 이상일 경우 TRUE - SQL%NOTFOUND : 해당 SQL 문에 영향을 받는 행의 수가 없을 경우 TRUE - SQL%ISOPEN : ..

[Oracle] 오라클 - PL/SQL 프로시저 및 함수 문제 -- 테이블 작성 CREATE TABLE score1( hak VARCHAR2(20) PRIMARY KEY, name VARCHAR2(30) NOT NULL, kor NUMBER(3) NOT NULL, eng NUMBER(3) NOT NULL, mat NUMBER(3) NOT NULL ); CREATE TABLE score2( hak VARCHAR2(20) PRIMARY KEY, kor NUMBER(2,1) NOT NULL, eng NUMBER(2,1) NOT NULL, mat NUMBER(2,1) NOT NULL, FOREIGN KEY(hak) REFERENCES score1(hak) ); -- 평점 구하는 함수 작성 CREATE OR RE..

[Oracle] 오라클 - 함수 (FUNCTION) ✔️ 함수 (FUNCTION) : 사용자가 직접 로직을 구현하여 구현한 함수 : 내장 함수(빌트인 함수)처럼 쿼리에서 호출하거나 EXECUTE 문을 통해 실행 가능 -- 기본형식 CREATE OR REPLACE FUNCTION function_name ( 파라미터명 타입 ) RETURN 데이터 타입 IS 변수, 상수 선언 BEGIN RETURN 반환값; END; / 💡 함수 목록 확인 SELECT * FROM user_procedures; -- 함수 실행 SELECT fnSum(100), fnSum(20) FROM dual; -- 1에서 시작하는 합 구하기 CREATE OR REPLACE FUNCTION fnSum( pNum IN NUMBER ) RET..

[Oracle] 오라클 - PL/SQL 프로시저 (SELECT / IN OUT 파라미터) ✔️ PL/SQL 프로시저 (SELECT) -- 하나의 레코드를 출력하는 프로시저를 만들어보자 CREATE OR REPLACE PROCEDURE pSelectOneTest ( pNum NUMBER ) IS --rec test%ROWTYPE; 이렇게 해도 되지만 아래처럼 하기 -- 사용자 정의 레코드 타입 선언 TYPE mytype IS RECORD ( num test.num%TYPE, name test.name%TYPE, score test.score%TYPE, grade test.grade%TYPE ); rec MYTYPE; -- 변수선언 BEGIN SELECT num, name, score, grade INTO ..

[Oracle] 오라클 - PL/SQL 프로시저 (삽입/수정/삭제) ✔️ 프로시저란? ⭐ 일단 리턴 값이 없는 분이다. (void 메소드 같은 것) ⭐ : 자주 실행해야 하는 업무 흐름(SQL)을 미리 작성하여 데이터베이스 내에 저장해 두었다가 필요할 때마다 호출하여 실행 -- 테이블 삭제한다고 연관 프로시저가 삭제되는건 아니다. -- 특정 로직을 처리하고 결과를 반환하지 않는다. -- 모두 DB에 저장된 객체이므로 스토어드 프로시저라고 부른다. -- 필요할 때 가져다가 실행하는 것 -- 자동 롤백이 가능하다. (두번째 INSERT 실패시 첫번째 INSERT 도 자동 롤백) -- 프로시저에서 INSERT , UPDATE , DELETE 는 자동 커밋이 되지 않기 때문에 꼭! COMMIT 구문 추가해야한다..