[새싹] 현대IT&E 231117 기록 - PL/SQL

최정윤·2023년 11월 17일
PL/SQLsql
0

새싹

목록 보기
24/67

개요

  • oracle 안쓰고 mysql 쓰는 이유?
    • oracle이 비싸기 때문이다.
  • view란 무엇일까?
    • 이름이 있는 Select!

PL/SQL Datatypes

https://docs.oracle.com/cd/B10501_01/appdev.920/a96624/03_types.htm#10546

  • Scalar Types : number, character, date, boolean
  • Composite Types : plsql record, plsql table, ...
  • Reference Types : ref cursor, ...
  • LOB : http://me2.do/F3hYsYX7

변수

  • variable ≒ 그릇
  • constant ≒ 그릇 : CONSTANT Constrains the variable so that its value cannot change (Constants must be initialized)
  • parameter ≒ 그릇
  • argument ≒ 그릇

컴퓨터에게 0101이라는 값을 주면?
-> 못알아듣는다.
-> 항상 타입을 지정해 주어야 한다.

변수와 상수

-- 6가지 유형의 SELECT 문장과 적절한 변수 설정
-- 6가지 유형의 SELECT 문장과 적절한 변수 설정

-- [1] 

create or replace procedure p1 (
p_employee_id in  employees.employee_id%type,
p_salary      out employees.salary%type      
)
is
v_salary employees.salary %type;
begin
select salary into v_salary
from employees
where employee_id = p_employee_id;

p_salary := v_salary;
end;
/

show errors

desc p1

set serveroutput on

declare
v_id  number := 100;
v_ret number;
begin
p1(v_id, v_ret);
dbms_output.put_line(v_id||'의 급여는 '||v_ret);
end;
/

---

-- [2] 행 하나를 리턴하는 SELECT 문

create or replace procedure p1 (
p_employee_id in  employees.employee_id%type,
p_last_name   out employees.last_name%type,
p_salary      out employees.salary%type,
p_job_id      out employees.job_id%type  
)
is
employee_row employees%rowtype;
begin
select * into employee_row
from employees
where employee_id = p_employee_id;

p_last_name := employee_row.last_name;
p_salary    := employee_row.salary;
p_job_id    := employee_row.job_id;
end;
/

declare
v_employee_id employees.employee_id%type := 100;
v_last_name   employees.last_name%type;
v_salary      employees.salary%type;
v_job_id      employees.job_id%type; 
begin
p1(v_employee_id, v_last_name, v_salary, v_job_id);
dbms_output.put_line(v_employee_id);
dbms_output.put_line(v_last_name);
dbms_output.put_line(v_salary);
dbms_output.put_line(v_job_id);
end;
/


create or replace procedure p1 (
p_employee_id  in  employees.employee_id%type,
p_employee_row out employees%rowtype  
)
is
begin
select * into p_employee_row
from employees
where employee_id = p_employee_id;
end;
/

declare
v_employee_id employees.employee_id%type := 100;
employee_row  employees%rowtype ;
begin
p1(v_employee_id, employee_row);
dbms_output.put_line(v_employee_id);
dbms_output.put_line(employee_row.last_name);
dbms_output.put_line(employee_row.salary);
dbms_output.put_line(employee_row.job_id);
end;
/


-- [3] 행 하나의 몇몇 컬럼을 리턴하는 SELECT 문

create or replace procedure p1(
p_employee_id  in  employees.employee_id%type
)
is
TYPE employee_record_type IS RECORD (
  last_name employees.last_name%type, 
  salary    employees.salary%type,
  job_id    employees.job_id%type);

employee_row employee_record_type;
begin
select last_name, salary, job_id into employee_row
from employees
where employee_id = p_employee_id;

dbms_output.put_line(employee_row.last_name);
dbms_output.put_line(employee_row.salary);
dbms_output.put_line(employee_row.job_id);
end;
/

exec p1(100)
exec p1(101)

↓↓

create or replace package pack_datatypes
is

TYPE employee_record_type IS RECORD (
  last_name employees.last_name%type, 
  salary    employees.salary%type,
  job_id    employees.job_id%type);

end;
/

create or replace procedure p1(
p_employee_id  in  employees.employee_id%type
)
is
employee_row pack_datatypes.employee_record_type;
begin
select last_name, salary, job_id into employee_row
from employees
where employee_id = p_employee_id;

dbms_output.put_line(employee_row.last_name);
dbms_output.put_line(employee_row.salary);
dbms_output.put_line(employee_row.job_id);
end;
/

exec p1(100)
exec p1(101)

↓↓

create or replace procedure p1 (
p_employee_id  in  employees.employee_id%type,
p_employee_row out pack_datatypes.employee_record_type
)
is
begin
select last_name, salary, job_id into p_employee_row
from employees
where employee_id = p_employee_id;
end;
/

declare
v_employee_id employees.employee_id%type := 100;
employee_row  pack_datatypes.employee_record_type; 
begin
p1(v_employee_id, employee_row);
dbms_output.put_line(v_employee_id);
dbms_output.put_line(employee_row.last_name);
dbms_output.put_line(employee_row.salary);
dbms_output.put_line(employee_row.job_id);
end;
/

↓↓

create or replace view view_for_row_type
as
select last_name, salary, job_id 
from employees;

create or replace procedure p1 (
p_employee_id  in  employees.employee_id%type,
p_employee_row out view_for_row_type%rowtype
)
is
employee_row view_for_row_type%rowtype;
begin
select last_name, salary, job_id into p_employee_row
from employees
where employee_id = p_employee_id;
end;
/

declare
v_employee_id employees.employee_id%type := 100;
employee_row  view_for_row_type%rowtype; 
begin
p1(v_employee_id, employee_row);
dbms_output.put_line(v_employee_id);
dbms_output.put_line(employee_row.last_name);
dbms_output.put_line(employee_row.salary);
dbms_output.put_line(employee_row.job_id);
end;
/


-- [4] 같은 유형의 값 여러 개를 리턴하는 SELECT 문

create or replace procedure p1 (
p_department_id in employees.department_id%type
)
is
TYPE employees_salary_tab_type IS TABLE OF employees.salary%type
 INDEX BY pls_integer;

emp_sal_tab employees_salary_tab_type;
begin
select salary BULK COLLECT INTO emp_sal_tab
from employees
where department_id = p_department_id;

for i in emp_sal_tab.first .. emp_sal_tab.last loop
  dbms_output.put_line(emp_sal_tab(i));
end loop;
end;
/

exec p1(20)
exec p1(50)

↓↓

create or replace package pack_datatypes
is

TYPE employee_record_type IS RECORD (
  last_name employees.last_name%type, 
  salary    employees.salary%type,
  job_id    employees.job_id%type);

TYPE employees_salary_tab_type IS TABLE OF employees.salary%type
 INDEX BY pls_integer;

end;
/

create or replace procedure p1 (
p_department_id in  employees.department_id%type,
p_emp_sal_tab   out pack_datatypes.employees_salary_tab_type
)
is
begin
select salary BULK COLLECT INTO p_emp_sal_tab
from employees
where department_id = p_department_id;
end;
/


create or replace procedure p1_print (
p_department_id in employees.department_id%type    
)
is
emp_sal_tab pack_datatypes.employees_salary_tab_type;
begin
p1(p_department_id, emp_sal_tab);

for i in emp_sal_tab.first .. emp_sal_tab.last loop
  dbms_output.put_line(emp_sal_tab(i));
end loop;
end;
/

exec p1_print(20)
exec p1_print(50)


-- [5] 행 여러 개를 리턴하는 SELECT 문 

create or replace procedure p1(
p_department_id in employees.department_id%type
)
is
TYPE employees_table_type IS TABLE OF employees%rowtype
  INDEX BY pls_integer;

employees_tab employees_table_type;
begin
select * BULK COLLECT INTO employees_tab
from employees
where department_id = p_department_id;

for i in employees_tab.first .. employees_tab.last loop
  dbms_output.put_line(employees_tab(i).last_name||', '||employees_tab(i).salary);
end loop;
end;
/

exec p1(20)
exec p1(50)


[6] 몇몇 컬럼을 포함하는 행 여러 개를 리턴하는 SELECT 문

create or replace procedure p1(
p_department_id in employees.department_id%type
)
is

TYPE employee_record_type IS RECORD (
  last_name employees.last_name%type, 
  salary    employees.salary%type,
  job_id    employees.job_id%type);

TYPE employees_table_type IS TABLE OF employee_record_type
  INDEX BY pls_integer;

employees_tab employees_table_type;
begin
select last_name, salary, job_id BULK COLLECT INTO employees_tab
from employees
where department_id = p_department_id;

for i in employees_tab.first .. employees_tab.last loop
  dbms_output.put_line(employees_tab(i).last_name||', '||employees_tab(i).salary);
end loop;
end;
/

exec p1(20)
exec p1(50)

프로시져 vs 함수

  • SQL에 포함할 수 있는 Subprogram을 만들고자 할 경우만 함수를 만드세요.
  • 다른 경우는 프로시져를 만드세요.
  • SQL에 포함할 수 있는 함수가 되려면 아래 Requirements를 준수하셔야 합니다.

Requirements for Calling PL/SQL Functions from SQL Expressions

https://www.oraclechennai.com/Calling-Stored-Functions-from-SQL-Expressions.html
To be callable from SQL expressions, a user-defined PL/SQL function must meet the following basic requirements:

  • It must be a stored function, not a function defined within a PL/SQL block or subprogram.
  • It must be a row function, not a column (group) function; in other words, it cannot take an entire column of data as its argument.
  • All its formal parameters must be IN parameters; none can be an OUT or IN OUT parameter.
  • The datatypes of its formal parameters must be SQL built-in types, such as CHAR, DATE, or NUMBER, not PL/SQL types, such as BOOLEAN, RECORD, or TABLE.
  • Its return type (the datatype of its result value) must be an SQL built-in type.
  /* SQL에 포함할 수 있는 함수 예제 하나 */
  create or replace function number_of_days_worked(
    p_employee_id in employees.employee_id%type
  )
    return number
  is 
    v_days number;
  begin
    select ceil(sysdate - hire_date) into v_days
    from employees
    where employee_id = p_employee_id;   

    return v_days;
  end;
  /

  select employee_id, number_of_days_worked(employee_id) "근속일수"
  from employees;

  exec dbms_output.put_line(number_of_days_worked(101))
  
  /* SQL에 포함할 수 없는 함수 예제 하나 */
  create or replace function findEmployeeById_func(
    p_employee_id in employees.employee_id%type
  )
    return employees%rowtype
  is 
    emp_row employees%rowtype;
  begin
    select * into emp_row
    from employees
    where employee_id = p_employee_id;   

    return emp_row;
  end;
  /

  -- 에러임
  select findEmployeeById_func(100)
  from dual;

  set serveroutput on

  -- 성공함
  declare
    ret employees%rowtype;
  begin
    ret := findEmployeeById_func(100);
    dbms_output.put_line(ret.last_name);
    dbms_output.put_line(ret.salary);
  end;
  /

  /* SQL에 포함할 수 없는 함수 예제 하나 더 */
  create or replace function employees_salary_func(
    p_employee_id in  employees.employee_id%type,
    p_salary      out employees.salary%type
  )
    return employees.salary%type
  is 
  begin
    select salary into p_salary
    from employees
    where employee_id = p_employee_id;  

    return 0; 
  end;
  /

  -- 변수명에 변수를 넣을 수 있는가??
  select employee_id, employees_salary_func(employee_id, 변수명)
  from employees;

  -- 성공함
  declare
    v_ret employees.salary%type;
    v_sal employees.salary%type;
  begin
    v_ret := employees_salary_func(100, v_sal);
    dbms_output.put_line(v_sal);
  end;
  /

트리거

Database Triggers Overview

트리거 종류

  • DML statements (INSERT, UPDATE, DELETE, MERGE) on a particular table or view, issued by any user
  • DDL statements (CREATE or ALTER primarily) issued either by a particular schema/user or by any schema/user in the database
  • Database events, such as logon/logoff, errors, or startup/shutdown, also issued either by a particular schema/user or by any schema/user in the database
  • DML Triggers -> Statement Trigger -> Row Trigger
  • Instead of 뷰이름
  • 트리거에서 에러가 발생하면 트리거를 유발한 문장이 실패한다!
# DML Triggers

    /* 문장 트리거 */
    CREATE OR REPLACE TRIGGER secure_emp
    BEFORE INSERT or UPDATE or DELETE ON emp
    BEGIN
      IF (TO_CHAR(SYSDATE,'DY') IN ('SAT','SUN')) OR (TO_CHAR(SYSDATE,'HH24:MI') BETWEEN '08:00' AND '12:00') THEN
        RAISE_APPLICATION_ERROR(-20500, 'You may insert into EMP table only during business hours.');
      END IF;
    END;
    /

    UPDATE emp
    SET sal = sal + 100
    WHERE rownum = 1;

    DROP TRIGGER secure_emp;

      ------

    /* 행 트리거 */
    CREATE OR REPLACE  TRIGGER restrict_salary
    BEFORE INSERT OR UPDATE OF sal ON emp
    FOR EACH ROW 
    BEGIN
      IF NOT (:NEW.job IN ('PRESIDENT', 'MANAGER')) AND :NEW.sal > 5000 THEN
        RAISE_APPLICATION_ERROR (-20202, 'Employee cannot earn more than $5,000.');
      END IF;
    END;
    /

    UPDATE emp
    SET sal = sal + 4500
    WHERE deptno = 10;

    set long 1000

    select trigger_name, trigger_body  
    from user_triggers;


# DDL Triggers

  CREATE OR REPLACE TRIGGER grant_connect 
  AFTER CREATE ON DATABASE
  WHEN (dictionary_obj_type = 'USER')
  begin
    execute immediate 'grant connect,resource to '||dictionary_obj_name;
  end;
  / 
 

  CREATE OR REPLACE TRIGGER grant_select 
  AFTER CREATE ON schema
  WHEN (sys.dictionary_obj_type = 'TABLE')
  begin
    execute immediate 'grant select on '|| sys.dictionary_obj_name || ' to joo' ;
  end;
  /


# Database Event Triggers

  create table log_trig_table 
  (user_id  varchar2(30),
   log_date date,
   action   varchar2(30));

  CREATE OR REPLACE TRIGGER logon_trig
  AFTER LOGON ON SCHEMA
  BEGIN
    INSERT INTO log_trig_table(user_id,log_date,action)
    VALUES (USER, SYSDATE, 'Logging on');
  END;
  /

    ------

  CREATE TABLE up_time
  (action_date DATE,
   action       VARCHAR2(20));
   
  CREATE OR REPLACE TRIGGER db_start 
  AFTER STARTUP ON DATABASE
  BEGIN
    insert into system.up_time values(sysdate,'STARTUP');
  END;
  /
 


# 트리거 구현 예제들

  - 보안 제어

    GRANT SELECT, INSERT, UPDATE, DELETE
    ON employees
    TO clerk;    -- database role

    GRANT clerk TO scott;
  
      ------

    CREATE OR REPLACE TRIGGER secure_emp
    BEFORE INSERT OR UPDATE OR DELETE ON employees
    DECLARE
      dummy PLS_INTEGER;
    BEGIN
      IF (TO_CHAR (SYSDATE, 'DY') IN ('SAT','SUN')) THEN
        RAISE_APPLICATION_ERROR(-20506,'You may only change data during normal business hours.');
      END IF;

      SELECT COUNT(*) INTO dummy FROM holiday
      WHERE holiday_date = TRUNC (SYSDATE);

      IF dummy > 0 THEN
        RAISE_APPLICATION_ERROR(-20507, 'You may not change data on a holiday.');
      END IF;
    END;
    /

  - 데이터 무결성

    ALTER TABLE employees ADD
    CONSTRAINT ck_salary CHECK (salary >= 500);

      ------

    CREATE OR REPLACE TRIGGER check_salary
    BEFORE UPDATE OF salary ON employees
    FOR EACH ROW
    WHEN (NEW.salary < OLD.salary)
    BEGIN
      RAISE_APPLICATION_ERROR (-20508, 'Do not decrease salary.');
    END;
    /

  - 참조 무결성

    ALTER TABLE employees
      ADD CONSTRAINT emp_deptno_fk
      FOREIGN KEY (department_id)
      REFERENCES departments(department_id)
      ON DELETE CASCADE;

      ------

    CREATE OR REPLACE TRIGGER cascade_updates
    AFTER UPDATE OF department_id ON departments
    FOR EACH ROW
    BEGIN
      UPDATE employees
      SET employees.department_id=:NEW.department_id
      WHERE employees.department_id=:OLD.department_id;

      UPDATE job_history
      SET department_id=:NEW.department_id
      WHERE department_id=:OLD.department_id;
    END;
    /

  - 테이블 복제(replication)

    CREATE MATERIALIZED VIEW emp_copy
    NEXT sysdate + 7 
    AS SELECT * FROM employees@ny;

      ------

    CREATE OR REPLACE TRIGGER emp_replica
    BEFORE INSERT OR UPDATE ON employees
    FOR EACH ROW
    BEGIN
      IF INSERTING THEN
        IF :NEW.flag IS NULL THEN
          INSERT INTO employees@sf 
          VALUES(:new.employee_id,...,'B');
          :NEW.flag := 'A';
        END IF;
      ELSE   /* Updating. */
        IF :NEW.flag = :OLD.flag THEN
          UPDATE employees@sf 
          SET ename=:NEW.last_name,...,flag=:NEW.flag
          WHERE employee_id = :NEW.employee_id;
        END IF;
        IF :OLD.flag = 'A' THEN
          :NEW.flag := 'B';
        ELSE
          :NEW.flag := 'A'; 
        END IF;
      END IF;
    END;
    /

  - 파생된 데이터 계산

    UPDATE departments d
    SET total_sal=(SELECT SUM(salary)
                   FROM employees d
                   WHERE e.department_id = d.department_id);

      ------

    CREATE PROCEDURE increment_salary
    (id NUMBER, new_sal NUMBER) 
    IS
    BEGIN
      UPDATE departments
         SET total_sal = NVL (total_sal, 0)+ new_sal
       WHERE department_id = id;
    END increment_salary;
    /
 
    CREATE OR REPLACE TRIGGER compute_salary
    AFTER INSERT OR UPDATE OF salary OR DELETE ON employees
    FOR EACH ROW
    BEGIN
      IF DELETING THEN
        increment_salary(:OLD.department_id,(-1*:OLD.salary));
      ELSIF UPDATING THEN
        increment_salary(:NEW.department_id,(:NEW.salary-:OLD.salary));
      ELSE
        increment_salary(:NEW.department_id,:NEW.salary); --INSERT
      END IF;
    END;
    /

  - 이벤트 로깅

    CREATE OR REPLACE TRIGGER notify_reorder_rep
    BEFORE UPDATE OF quantity_on_hand, reorder_point 
    ON inventories FOR EACH ROW
    DECLARE
      dsc product_descriptions.product_description%TYPE;
      msg_text VARCHAR2(2000);
    BEGIN
      IF :NEW.quantity_on_hand <= :NEW.reorder_point THEN 
        SELECT product_description INTO dsc
        FROM product_descriptions 
        WHERE product_id = :NEW.product_id;
        msg_text := 'ALERT: INVENTORY LOW ORDER:'||'Yours,' ||CHR(10) ||user || '.'|| CHR(10);
      ELSIF :OLD.quantity_on_hand >= :NEW.quantity_on_hand THEN
        msg_text := 'Product #'||... CHR(10);    
      END IF;

      UTL_MAIL.SEND('inv@oracle.com','ord@oracle.com', message=>msg_text, subject=>'Inventory Notice');
    END;
    /

예외처리

Exception 개요

Error -> Logic Error
-> Syntax Error
-> Runtime Error -> Oracle-defined Exception - Predefined excetion -> [1] when name then
(Exception) - Non-predefined excetion -> [2] put the name to exception then handling
-> [3] when others then
-> User-defined Exception -> [4] declare, raise, handling
-> [5] raise_application_error procedure

  • Predefined excetion

    C:\Users\COM> cd C:\app\user\product\21c\dbhomeXE\rdbms\admin
    C:\Users\COM> dir cat /W
    C:\Users\COM> dir utl     /W
    C:\Users\COM> dir dbms /W
    C:\Users\COM> dir prvt     /W

    C:\Users\COM> notepad stdspec.sql

Every Oracle error has a number,

but exceptions must be handled by name.

  • 출처 : 9i documentation

http://scidb.tistory.com/entry/PLSQL-면접문제

Exception Propagation

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/lnpls/plsql-error-handling.html#GUID-A99B76C3-44DF-40E1-AB7A-454C6805B9BB

-> An exception raised in a declaration propagates immediately to the enclosing block.
-> An exception raised inside a handler propagates immediately to the enclosing block.

# Exception 처리하지 않을 경우 vs Exception 처리할 경우

  (1) Exception 처리하지 않을 경우

    drop table t1 purge;
    create table t1 (no number);

    begin
      insert into t1 values (1000);
      dbms_output.put_line(100/0);
      insert into t1 values (2000);
    end;
    /

    select * from t1;

  (2) Exception 처리할 경우

    begin
      insert into t1 values (1000);
      dbms_output.put_line(100/0);
      insert into t1 values (2000);
    exception
      when zero_divide then
        null;
    end;
    /

    select * from t1;

    rollback;

  (3) Exception 발생과 상관없이 두번째 insert 문장 무조건 수행해야 한다면?

    begin
      begin
        insert into t1 values (1000);
        dbms_output.put_line(100/0);
      exception
        when zero_divide then
          null;
      end;

      insert into t1 values (2000);
    end;
    /

    select * from t1;

    rollback;


# Exception 처리 방법 패턴 5가지

  [1] when name then

    create or replace procedure p1(a number, b number)   
    is
    begin
      dbms_output.put_line(a/b);
    exception
      when zero_divide then
        dbms_output.put_line('0으로 나눌 수 없습니다!');
    end;
    /

    exec p1(100, 2)  -- 성공
    exec p1(100, 0)  -- 실패


  [2] put the name to exception then handling

    drop table t1 purge;
    create table t1 (no number not null);

    create or replace procedure  insert_t1(a number)
    is
    begin
      insert into t1 values(a);
    end;
    /

    exec insert_t1(1000);
    exec insert_t1(Null);

    select * from t1;

      ↓↓

    create or replace procedure insert_t1(a number)
    is
      e_null exception;
      pragma exception_init(e_null, -1400);
    begin
      insert into t1 values(a);
    exception
      when e_null then
        dbms_output.put_line('Null 값을 입력할 수 없습니다!');
    end;
    /

    exec insert_t1(1000);
    exec insert_t1(Null);

    select * from t1;

      ↓↓

    create or replace package pack_exceptions
    is
      e_null exception;
      pragma exception_init(e_null, -1400);
    end;
    /

    create or replace procedure insert_t1(a number)
    is
    begin
      insert into t1 values(a);
    exception
      when pack_exceptions.e_null then
        dbms_output.put_line('Null 값을 입력할 수 없습니다!');
    end;
    /

    exec insert_t1(1000);
    exec insert_t1(Null);

    select * from t1;


  [3] when others then

    drop table t1 purge;
    create table t1 (no number not null);

    create or replace procedure insert_t1(a number)
    is
    begin
      insert into t1 values(a);
    exception
      when others then
        dbms_output.put_line(sqlcode);
        dbms_output.put_line(sqlerrm);
    end;
    /

    exec insert_t1(1000);
    exec insert_t1(Null);

    select * from t1;

      ↓↓

    drop table t_errors purge;

    create table t_errors
    (error_date      date,
     subprogram_name varchar2(30),
     error_code      varchar2(60),
     error_message   varchar2(60));

    create or replace procedure insert_t1(a number)
    is
      v_error_code    varchar2(60);
      v_error_message varchar2(60);
    begin
      insert into t1 values(a);
    exception
      when others then
        v_error_code := substr(sqlcode, 1, 30);
        v_error_message := substr(sqlerrm, 1, 30);

        insert into t_errors
        values(sysdate, 'insert_t1', v_error_code, v_error_message );
    end;
    /

    exec insert_t1(1000);
    exec insert_t1(Null);

    select * from t1;
    select * from t_errors;

      ↓↓

    drop table t_errors purge;

    create table t_errors
    (error_date      date,
     subprogram_name varchar2(30),
     error_code      varchar2(60),
     error_message   varchar2(60));

    create or replace procedure insert_error_messages
     (error_date      in date,
      subprogram_name in varchar2,
      error_code      in varchar2,
      error_message   in varchar2)
    is
      v_error_code    varchar2(60);
      v_error_message varchar2(60);
    begin
        v_error_code := substr(error_code, 1, 30);
        v_error_message := substr(error_message, 1, 30);

        insert into t_errors
        values(sysdate, subprogram_name,  v_error_code, v_error_message );
    end;
    /

    create or replace procedure insert_t1(a number)
    is
    begin
      insert into t1 values(a);
    exception
      when others then
        insert_error_messages(sysdate, 'insert_t1', sqlcode, sqlerrm);
    end;
    /

    exec insert_t1(1000);
    exec insert_t1(Null);

    select * from t1;
    select * from t_errors;


  [4] declare, raise, handling 

    create or replace procedure p1(a number)
    is
      v_sal     employees.salary%type;
      e_too_low exception;
    begin
      select salary into v_sal
      from employees
      where employee_id = a;

      if v_sal < 10000 then
        raise e_too_low;
      end if;

      dbms_output.put_line(a||' 사원의 급여는 '||v_sal||'입니다.');

    exception
      when e_too_low then
        dbms_output.put_line(a||' 사원의 급여가 최저 급여미만입니다. 확인해보세요!');
    end;
    /

    exec p1(100)
    exec p1(141)


  [5] raise_application_error procedure

      - 사용자 정의 에러를 마치 Oracle의 Non-predefined excetion처럼 사용할 수 있게 하는 프로시져
      - https://docs.oracle.com/database/121/LNPLS/errors.htm#GUID-48F88C61-8CE9-4821-91CB-48A8F1BC09E1

    create or replace procedure p1(a number)
    is
      v_sal employees.salary%type;
    begin
      select salary into v_sal
      from employees
      where employee_id = a;

      if v_sal < 10000 then
        RAISE_APPLICATION_ERROR(-20111, a||' 사원의 급여가 최저 급여미만입니다. 확인해보세요!');
      end if;

      dbms_output.put_line(a||' 사원의 급여는 '||v_sal||'입니다.');
    end;
    /

    exec p1(100)
    exec p1(141)

      ↓↓

    create or replace procedure p1(a number)
    is
      v_sal emp.sal%type;
      e_too_null exception;
      pragma exception_init(e_too_null, -20111);
    begin
      select sal into v_sal
      from emp
      where empno = a;

      if v_sal < 1000 then
        RAISE_APPLICATION_ERROR(-20111, a||' 사원의 급여가 최저 급여미만입니다. 확인해보세요!');
      end if;

      dbms_output.put_line(a||' 사원의 급여는 '||v_sal||'입니다.');
    exception
      when  e_too_null then
        dbms_output.put_line('예외 발생');
    end;
    /

    exec p1(7369)


    cf.트리거(Trigger)를 이용해서 특정 IP 접속 제한하기
   
       http://orapybubu.blog.me/40025984303

       C:\Users\COM> sqlplus system/oracle@localhost:1521/xepdb1

       SQL> CREATE OR REPLACE TRIGGER LOGON_TRI
            after LOGON ON ACE.SCHEMA
            BEGIN
               if SUBSTR(sys_context('USERENV', 'IP_ADDRESS'), 1, 7) in ('127.0.0', '219.241')  then
                      RAISE_APPLICATION_ERROR ( -20002, 'IP '||ORA_CLIENT_IP_ADDRESS
                              || ' is not allowed to connect database as ACE!');
                END IF;
            END;
            /

       SQL> exit

       C:\Users\COM> sqlplus ace/me@localhost:1521/xepdb1

         ORA-04088: 트리거 'SYSTEM.LOGON_TRI'의 수행시 오류
         ORA-00604: 순환 SQL 레벨 1 에 오류가 발생했습니다
         ORA-20002: IP 127.0.0.1 is not allowed to connect database as ACE!

       C:\Users\COM> sqlplus system/oracle@localhost:1521/xepdb1

       SQL> drop trigger LOGON_TRI;

       SQL> exit

       C:\Users\COM> sqlplus ace/me@localhost:1521/xepdb1

명시적 커서

Explicit Cursor

  • Cursor

    A cursor is a name or handle to a specific private SQL area.

  • Oracle Memory Architecture

    https://docs.oracle.com/en/database/oracle/oracle-database/12.2/cncpt/memory-architecture.html#GUID-913335DF-050A-479A-A653-68A064DCCA41

  • Cursor의 종류

    -> implicit cursor : 한 건 리턴 select, insert, update, delete, merge
    -> explicit cursor : 두 건 이상 리턴 select

    create or replace procedure p1(a number)
    is
    v_salary number;
    begin
    select salary into v_salary
    from employees
    where department_id = a;
    end;
    /

    exec p1(10) <- 에러 : ORA-01422: 실제 인출은 요구된 것보다 많은 수의 행을 추출합니다
    exec p1(60) <- 에러 : ORA-01403: 데이터를 찾을 수 없습니다.

    create or replace procedure p1(a number)
    is
    CURSOR nemam
    IS
    select salary
    from employees
    where department_id = a;
    begin
    open nemam;
    end;
    /

    exec p1(10)
    exec p1(60)

  • Cursor 속성

    -> implicit cursor

    sql%isopen
    sql%found
    sql%notfoud
    sql%rowcount

    -> explicit cursor

    커서명%isopen
    커서명%found
    커서명%notfoud
    커서명%rowcount

# implicit cursor 예제
  
  예제> 암시적 커서 속성 사용 예제

  drop table sawon purge;

  create table sawon 
  as 
  select * from employees;

  begin
    update sawon
    set salary = salary * 1.3
    where department_id = 30;

    if sql%rowcount = 0 then
      p.p('수정된 행이 없습니다');
    else 
      p.p(sql%rowcount||'행이 수정되었습니다');
    end if;

    delete from sawon
    where department_id = 20;

    if sql%rowcount = 0 then
      p.p('삭제된 행이 없습니다');
    else 
      p.p(sql%rowcount||'행이 삭제되었습니다');
    end if;
  end;
  /


# Explicit cursor 예제 : declare -> open -> fetch -> close
  
  create or replace procedure p1(a emp.department_id%type)
  is
    CURSOR emp_cursor
    IS
    select empno, ename, salary, job, hiredate 
    from employees
    where department_id = a
    order by salary desc;

    r emp_cursor%rowtype;
  begin
    if not(emp_cursor%isopen) then
      OPEN emp_cursor;  /* 서버 내부에 active set이 생성 */ 
    end if;

    loop
      FETCH emp_cursor INTO r;
      exit when emp_cursor%notfound;
      p.p(r.empno||' '||r.salary||' '||r.job);
    end loop;

    CLOSE emp_cursor;
  end;
  /

  exec p1(10)
  exec p1(30)   

    ↓↓

  /* Cursor for loop */
  create or replace procedure p1(a emp.department_id%type)
  is
    CURSOR emp_cursor
    IS
    select empno, ename, salary, job, hiredate
    from employees
    where department_id = a
    order by salary desc;
  begin
    for r in emp_cursor loop  /* open, fetch */
      p.p(r.empno||' '||r.salary||' '||r.job);
    end loop;                 /* close */
  end;
  /

  exec p1(10)
  exec p1(30) 

    ↓↓

  /* 서브쿼리를 이용한 Cursor for loop */
  create or replace procedure p1(a emp.department_id%type)
  is
  begin
    for r in (select empno, ename, salary, job, hiredate
              from employees
              where department_id = a
              order by salary desc) loop
      p.p(r.empno||' '||r.salary||' '||r.job);
    end loop;
  end;
  /

  exec p1(10)
  exec p1(30) 


# Cursor for loop 예제 하나 더

  create or replace procedure p1
  is
  begin
    for d in (select * from dept order by department_id) loop
      p.p(d.department_id);
      for e in (select * from employees where department_id = d.department_id order by salary desc) loop
        p.p(e.empno||', '||e.ename);
      end loop;
      p.p('-----------');
    end loop;
  end;
  /

  exec p1


# Cursor variable

  - Cursor Variables 

    https://docs.oracle.com/en/database/oracle/oracle-database/18/lnpls/static-sql.html#GUID-4A6E054A-4002-418D-A1CA-DE849CD7E6D5

    (1) 강한 타입의 커서 변수는 반환형만 일치하면 어떤 SELECT문도 OPEN 할 수 있다
    (2) 약한 타입의 커서 변수는 반환형이 서로 다른 쿼리에 대해서도 사용할 수 있다
    (3) 커서 변수는 서브프로그램의 매개변수로 사용할 수 있다

  - Enhancing the Application: Advanced JDBC Features

    https://docs.oracle.com/database/121/TDPJD/addfunc.htm#TDPJD206 


  REM 강한 타입의 커서 변수는 반환되는 칼럼의 개수와 타입만 일치하면
  REM 어떤 SELECT 문에 대해서도 OPEN 가능하다.

  create or replace procedure sp_strong_type_cursor_variable
  is
    type emp_record_type is record
    (empno emp.empno%type,
     ename emp.ename%type,
     salary   emp.salary  %type);

    type emp_cursor_type is ref cursor
      return emp_record_type;

    v_empcur emp_cursor_type;   /* 강한 타입의 커서 변수 */
    v_emprec emp_record_type;
  begin
    /* 첫번째 SQL문에 대해 커서 변수를 OPEN */
    open v_empcur
    for
      select empno, ename, salary 
      from employees 
      where department_id = 10;

    loop
      fetch v_empcur into v_emprec ;
      exit when v_empcur%notfound ;
      dbms_output.put_line('EMPNO='||v_emprec.empno||', ENAME='||v_emprec.ename||', salary='||v_emprec.salary);
    end loop ;

    close v_empcur ;
  
    dbms_output.put_line('--------------') ;
  
    /* 두번째 SQL문에 대해 커서 변수를 OPEN */
    open v_empcur
    for
      select empno, ename, salary+nvl(comm,0)
      from employees
      where department_id = 20;

    loop
      fetch v_empcur into v_emprec ;
      exit when v_empcur%notfound ;
      dbms_output.put_line('EMPNO='||v_emprec.empno||', ENAME='||v_emprec.ename||', salary='||v_emprec.salary);
    end loop ;
    close v_empcur ;
  end;
  /

  exec sp_strong_type_cursor_variable


  REM 커서 변수는 서브 프로그램의 매개 변수로 사용할 수 있다.
  create or replace procedure sp_cusor_variable_parameter
  is
    type emp_rec is record
    (empno emp.empno%type,
     ename emp.ename%type);

    type emp_cursor_type is ref cursor 
      return emp_rec; 

    v_empcur emp_cursor_type;  /* 강한 타입의 커서 변수 */
  
    procedure print_emp        /* 로컬 서브프로그램 */
    (a_empcur in emp_cursor_type)
    is   
      v_emprec emp_rec;
    begin
      loop
        fetch a_empcur into v_emprec ;
        exit when a_empcur%notfound;
        dbms_output.put_line('EMPNO=' ||v_emprec.empno||', ENAME=' || v_emprec.ename);
      end loop;
    end;
  begin
    open v_empcur 
    for
      select empno, ename
      from employees;

    print_emp(v_empcur);

    close v_empcur;
  end;
  /

  exec sp_cusor_variable_parameter

  
  REM 약한 타입의 커서 변수는 반환형이 서로 다른 쿼리에 대해서도 사용할 수 있다
  create or replace procedure sp_weak_type_cursor_variable
  is
    v_cursor   sys_refcursor;  /* 약한 타입의 커서 변수 */
    v_selector char;
    v_department_id   number;
  
    procedure open_cursor
    (a_cursor   in out sys_refcursor, 
     a_selector in     char,
     a_department_id   in     number)
    is
    begin
      if a_selector = 'E' then
        open a_cursor for select * from employees  where department_id = a_department_id;
      else
        open a_cursor for select * from dept where department_id = a_department_id;
      end if ;
    end;

    procedure print_cursor
    (a_cursor   in out sys_refcursor,
     a_selector in     char)
    is
      v_emprec  emp%rowtype;
      v_deptrec dept%rowtype;
    begin
      if a_selector = 'E' then
        loop
          fetch a_cursor into v_emprec;   -- emp 테이블의 모든 칼럼을 레코드에 담음
          exit when a_cursor%notfound;
          dbms_output.put_line('EMPNO='||v_emprec.empno||', ENAME='||v_emprec.ename||', JOB='||v_emprec.job  ||', salary='  ||v_emprec.salary);
        end loop;
      else
        loop
          fetch a_cursor into v_deptrec;  -- dept 테이블의 세 칼럼을 레코드에 담음
          exit when a_cursor%notfound;
          dbms_output.put_line('department_id='||v_deptrec.department_id||', DNAME='||v_deptrec.dname||', LOC=' ||v_deptrec.loc);
        end loop;
      end if;
    end;
  begin
    -- DEPT 테이블 출력
    v_selector := 'D';
    v_department_id   := 10;
    open_cursor (v_cursor, v_selector, v_department_id);  -- 커서 OPEN
    print_cursor(v_cursor, v_selector);            -- 커서 출력
    close v_cursor;
  
    dbms_output.put_line('----');

    -- EMP 테이블 출력
    v_selector := 'E';
    v_department_id   := 10;
    open_cursor (v_cursor, v_selector, v_department_id);  -- 커서 OPEN
    print_cursor(v_cursor, v_selector);            -- 커서 출력
    close v_cursor;
  end;
  /

  exec sp_weak_type_cursor_variable
profile
최정윤
개발 기록장
이전 포스트

[새싹] 현대IT&E 231116 기록 - PL/SQL

다음 포스트

[새싹] 현대IT&E 231120 기록 - React

0개의 댓글