231026 PL/SQL SQL문

Data Base/Oracle SQL

231026 PL/SQL SQL문

잇꼬 2023. 10. 26. 17:50

728x90

SMALL

■ PL/SQL 의 SQL문
1. SELECT 문을 이용해서 데이터베이스에 있는 데이터를 추출할 수 있다.
2. DML(INSERT, UPDATE, DELETE, MERGE) 문을 이용해서 DB에 있는 데이터를 입력, 삭제, 수정할 수 있다.
3. COMMIT, ROLLBACK, SAVEPOINT 문을 이용해서 트랜잭션을 처리할 수 있다.

# 오류발생: 변수에 로드하는 작업이 필요
-- PLS-00428: an INTO clause is expected in this SELECT statement

BEGIN
    SELECT last_name, salary
    FROM hr.employees
    WHERE employee_id = 100;
END;
/

# cursor : SQL문 실행 메모리 영역 (parse, bind, execute, fetch)
1) 암시적 커서 : 오라클이 SQL문을 처리하기 위해서 내부적으로 생성하고 관리한다.
2) 명시적 커서 : 프로그래머가 명시적으로 관리한다.(여러 개의 row를 fetch해야 할 때 사용)

# 암시적커서
1. SELECT ... INTO(fetch) 절
- 반드시 1개 row만 fetch 해야 한다.
- 0개 : NO_DATA_FOUND
- 2개 이상 : TOO_MANY_ROWS
- DML, COMMIT, ROLLBACK, SAVEPOINT

DECLARE
    v_name varchar2(30);
    v_sal number;
BEGIN
    SELECT last_name, salary
    INTO v_name, v_sal -- fetch절
    FROM hr.employees
    WHERE employee_id = 100;
    
    dbms_output.put_line(v_name);
    dbms_output.put_line(v_sal);
END;
/

# 1개의 row라 출력 가능

DECLARE
    v_name varchar2(30);
    v_sal number;
BEGIN
    SELECT last_name, salary
    INTO v_name, v_sal -- fetch절
    FROM hr.employees
    WHERE department_id = 10;
    
    dbms_output.put_line(v_name);
    dbms_output.put_line(v_sal);
END;
/

# 오류발생 ORA-01422: exact fetch returns more than requested number of rows
- 여러 row 출력되어서

DECLARE
    v_name varchar2(30);
    v_sal number;
BEGIN
    SELECT last_name, salary
    INTO v_name, v_sal -- fetch절
    FROM hr.employees
    WHERE department_id = 20;
    
    dbms_output.put_line(v_name);
    dbms_output.put_line(v_sal);
END;
/

# 바인드변수

var b_id number;
execute :b_id := 100;

DECLARE
    v_name varchar2(30);
    v_sal number;
BEGIN
    SELECT last_name, salary
    INTO v_name, v_sal -- fetch절
    FROM hr.employees
    WHERE employee_id = :b_id;
    
    dbms_output.put_line(v_name);
    dbms_output.put_line(v_sal);
END;
/

# TYPE, SIZE 확인이 어려울때 + 종속관계의 편의성, 유지관리 회수
- 유저.테이블.컬럼%TYPE : 컬럼의 TYPE, SIZE 을 가져다 사용한다.
- 후에 컬럼의 type, size가 변경되더라도 프로그램을 수정할 수 필요가 없다.

DECLARE
    v_name hr.employees.last_name%TYPE; 
    v_sal hr.employees.salary%TYPE;
BEGIN
    SELECT last_name, salary
    INTO v_name, v_sal -- fetch절
    FROM hr.employees
    WHERE employee_id = 100;
    
    dbms_output.put_line(v_name);
    dbms_output.put_line(v_sal);
END;
/

# 바인드 변수

var b_avg_sal number;

BEGIN
    SELECT ROUND(AVG(salary))
    INTO :b_avg_sal
    FROM hr.employees;
        
    dbms_output.put_line('전체 사원의 평균 값은 '||:b_avg_sal);
END;
/

SELECT * FROM hr.employees WHERE salary > :b_avg_sal;

# 입사일, 급여(공백제외) 출력

DECLARE
    v_hire_date hr.employees.hire_date%TYPE;
    v_sal hr.employees.salary%TYPE;
BEGIN
    SELECT hire_date, salary
    INTO v_hire_date, v_sal
    FROM hr.employees
    WHERE employee_id = 100;
        
    dbms_output.put_line('입사일 : '||to_char(v_hire_date, 'yyyy"년" mm"월" dd"일"') );
    dbms_output.put_line('급여 :'||ltrim(to_char(v_sal, 'l999,999.00')));
    -- DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT); -- fetch한 row 수를 리턴한다. 의미없는 문장이다.
END;
/

# TEST TABLE 생성 및 DATA 로드후 확인

CREATE TABLE hr.test (
    id    NUMBER,
    name  VARCHAR2(30),
    day   DATE
);

DESC hr.test

INSERT INTO hr.test( id, name, day ) VALUES ( 1,'홍길동', sysdate);

SELECT * FROM hr.test;

ROLLBACK;

BEGIN
    INSERT INTO hr.test( id, name, day )
    VALUES ( 1,'홍길동', sysdate);
END;
/

SELECT * FROM hr.test;

# 변수처리 후 INSERT로 로드

DECLARE
    v_id hr.test.id%TYPE := 1;
    v_name hr.test.name%TYPE := '홍길동';
    v_day hr.test.day%TYPE := sysdate;
BEGIN
    INSERT INTO hr.test( id, name, day )
    VALUES ( v_id, v_name, v_day );
    COMMIT;
END;
/

SELECT * FROM HR.TEST;

# 바인드 변수

var b_id number;
var b_name varchar2(30);
var b_day varchar2(30);

execute :b_id := 2;
execute :b_name := '박찬호';
execute :b_day := '2001-01-01';

print :b_id;
print :b_name;
print :b_day

BEGIN
    INSERT INTO hr.test( id, name, day )
    VALUES ( :b_id, :b_name, to_date(:b_day, 'yyyy-mm-dd') );
END;
/

SELECT * FROM HR.TEST;

# 영문자를 대문자로만 출력해야 한다.

var b_id number;
var b_name varchar2(30);
var b_day varchar2(30);

execute :b_id := 3;
execute :b_name := 'james';
execute :b_day := '2002-01-01';

print :b_id;
print :b_name;
print :b_day

BEGIN
    INSERT INTO hr.test( id, name, day )
    VALUES ( :b_id, upper(:b_name), to_date(:b_day, 'yyyy-mm-dd') );
END;
/

SELECT * FROM HR.TEST;
COMMIT;

# SELECT문을 INSERT 에 로드함.

BEGIN
    INSERT INTO hr.test(id, name, day)
    SELECT employee_id, last_name, hire_date
    FROM hr.employees;
END;
/

SELECT * FROM hr.test;

# SQL%ROWCOUNT : 처리건수 확인.
- 암시적커서 속성, 영향을 입은 ROW 건수를 리턴한다.

BEGIN
    INSERT INTO hr.test(id, name, day)
    SELECT employee_id, last_name, hire_date
    FROM hr.employees;
    DBMS_OUTPUT.PUT_LINE('업데이트 된 ROW의 수 : '||SQL%ROWCOUNT);
END;
/

SELECT * FROM hr.test;

# UPDATE

UPDATE hr.test
SET name = '제임스'
WHERE id =3;

SELECT * FROM hr.test;
ROLLBACK;

BEGIN
    UPDATE hr.test
    SET name = '제임스'
    WHERE id =3;
    DBMS_OUTPUT.PUT_LINE('수정된 ROW의 수 : '||SQL%ROWCOUNT);
END;
/

SELECT * FROM hr.test;

# WHERE절 없이 진행

BEGIN
    UPDATE hr.test
    SET name = '제임스';
    DBMS_OUTPUT.PUT_LINE('수정된 ROW의 수 : '||SQL%ROWCOUNT);
END;
/

SELECT * FROM hr.test;

# 변수명 지정 후 UPDATE

DECLARE
    v_id hr.test.id%type := 3;
    v_name hr.test.name%type := '제임스';
BEGIN
    UPDATE hr.test
    SET name = v_name
    WHERE id = v_id;
    DBMS_OUTPUT.PUT_LINE('수정된 ROW의 수 : '||SQL%ROWCOUNT);
END;
/

SELECT * FROM hr.test;
ROLLBACK;

# DELETE

DELETE FROM hr.test WHERE id = 1;
SELECT * FROM hr.test;
ROLLBACK;

BEGIN
    DELETE FROM hr.test WHERE id = 1;
    DBMS_OUTPUT.PUT_LINE('삭제된 ROW의 수 : '||SQL%ROWCOUNT);
END;
/

SELECT * FROM hr.test;

# DELETE + UPDATE ⓐ

BEGIN
    DELETE FROM hr.test 
    WHERE id = 1;
    DBMS_OUTPUT.PUT_LINE('삭제된 ROW의 수 : '||SQL%ROWCOUNT);
    
    UPDATE hr.test
    SET day = sysdate + 100;
    DBMS_OUTPUT.PUT_LINE('수정된 ROW의 수 : '||SQL%ROWCOUNT);
END;
/

SELECT * FROM hr.test;
ROLLBACK;

# DELETE + UPDATE ⓑ

CREATE TABLE hr.emp
AS
SELECT * FROM hr.employees;

BEGIN
    DELETE FROM hr.emp
    WHERE department_id = 20;
    DBMS_OUTPUT.PUT_LINE('삭제된 ROW의 수 : '||SQL%ROWCOUNT);
    
    UPDATE hr.emp
    SET salary = salary * 1.1
    WHERE department_id = 30;
    DBMS_OUTPUT.PUT_LINE('수정된 ROW의 수 : '||SQL%ROWCOUNT);
END;
/

SELECT * FROM hr.emp WHERE department_id in (20, 30);
ROLLBACK;

# UPDATE가 되지 않았는 것을 파악할 수 있다.

UPDATE hr.emp
SET salary = salary *1.1
WHERE employee_id = 0;

BEGIN
    UPDATE hr.emp
    SET salary = salary *1.1
    WHERE employee_id = 0;
    DBMS_OUTPUT.PUT_LINE('수정된 ROW의 수 : '||SQL%ROWCOUNT);
END;
/

DECLARE
    v_id hr.emp.employee_id%TYPE := 0;
BEGIN
    UPDATE hr.emp
    SET salary = salary *1.1
    WHERE employee_id = v_id;
    DBMS_OUTPUT.PUT_LINE('수정된 ROW의 수 : '||SQL%ROWCOUNT);
END;
/

# sql%found : 영향을 입은 ROW가 있으면 TRUE, 없으면 FLASE 를 리턴하는 속성

DECLARE
    v_id hr.emp.employee_id%TYPE := 0;
BEGIN
    UPDATE hr.emp
    SET salary = salary *1.1
    WHERE employee_id = v_id;
    
    IF sql%found THEN
        DBMS_OUTPUT.PUT_LINE(v_id ||' 사원으로 수정된 데이터가 있습니다.');
    ELSE
        DBMS_OUTPUT.PUT_LINE(v_id ||' 사원으로 수정된 데이터가 없습니다.');
    END IF;
END;
/

# sql%notfound : 영향을 입은 ROW가 없으면 TRUE, 있으면 FLASE 를 리턴하는 속성

DECLARE
    v_id hr.emp.employee_id%TYPE := 0;
BEGIN
    UPDATE hr.emp
    SET salary = salary *1.1
    WHERE employee_id = v_id;
    
    IF sql%notfound THEN
        DBMS_OUTPUT.PUT_LINE(v_id ||' 사원으로 수정된 데이터가 없습니다.');
    ELSE
        DBMS_OUTPUT.PUT_LINE(v_id ||' 사원으로 수정된 데이터가 있습니다.');
    END IF;
END;
/

728x90

LIST

저작자표시 비영리 동일조건

'Data Base > Oracle SQL' 카테고리의 다른 글

231024 Oracle SQL SAVEPOINT (0)	2023.10.24
231023 Oracle SQL TOP_N 분석 rownum, rank(), dense_rank() (0)	2023.10.23
231023 Oracle SQL 분석함수 (1)	2023.10.23
231023 Oracle SQL 날짜타입 (1)	2023.10.23
231023 Oracle SQL SYNONYM(동의어) (0)	2023.10.23

현재글231026 PL/SQL SQL문

250x250

안녕? 나야 :)