[0327] PLSQL

환경 설정

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

SQL> grant create  procedure, create trigger
     to ace;

SQL> exit

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

SQL을 이용해서 Hello World! 출력

• 

PLSQL - 헬로 월드

---

Anonymous Block으로 Hello World! 출력

• 

set serveroutput on

ㄴ 설정해주어야 커맨드창에 나타남

• 

---

Named Block : procedure, function, package, trigger, type, ... 확인용 쿼리

• 

ㄴ p1 프로시저 생성, execute p1으로 실행

ㄴ 타입을 프로시저로 변경

---

예시

• 
• 

예시

drop table sales purge;

create table sales
as
select empno as sale_id, sal as amount, deptno as prod_id
from emp;

create or replace function sales_amount(p_prod_id sales.prod_id%type) return varchar2
is 
	v_amount number;
begin
	select sum(amount) into v_amount
	from sales
	where prod_id = p_prod_id;

	return '판매 총액 : '||to_char(v_amount);
end;
/


execute dbms_output.put_line(sales_amount(10))

---

function으로 입력 길이 확인

drop table t1 purge;

 create table t1 (no number, name varchar2(30));

 create or replace function uf_name_length(p_name t1.name%type) return boolean
 is 
   v_length number;
 begin
   select lengthb(p_name) into v_length
   from dual;

   if v_length <= 30 then
     return TRUE;
   else
     return false;
   end if;
 end;
 /

 /* 익명 블록으로 함수 테스트 */
 declare
   v_bool boolean;
 begin 
   v_bool := uf_name_length('Korea');

   if v_bool then
     dbms_output.put_line('True');
   else
     dbms_output.put_line('False');
   end if;
 end;
 /   

입력 길이 확인한 뒤 procedure로 데이터 입력

create or replace procedure t1_insert(p_no number, p_name varchar2)
is 
begin
  if uf_name_length(p_name) then
    insert into t1 (no, name)
    values(p_no, p_name);
  else 
    dbms_output.put_line('입력하신 name의 길이가 너무 길어요!');
  end if;

end;
/

show errors

truncate table t1;

exec t1_insert(1001, 'Alice');
exec t1_insert(1002, 'Alice is Missing is a silent role-playing ...')

select * from t1;

function으로 상품별 판매 총액 계산

drop table sales purge;

 create table sales
 as 
 select empno as sale_id, sal as amount, deptno as prod_id
 from emp;

 select * from sales;

 create or replace function sales_amount(p_prod_id sales.prod_id%type) return varchar2
 is
   v_amount number;
 begin
   select sum(amount) into v_amount
   from sales
   where prod_id = p_prod_id;

   return '판매총액 : '||to_char(v_amount);
 end;
 /

 execute dbms_output.put_line(round(100.5))
 execute dbms_output.put_line(sales_amount(10))
 execute dbms_output.put_line(sales_amount(30))

 drop table products purge;

 create table products
 as
 select deptno as prod_id, dname as prod_name
 from dept;

 col total format a30

 select prod_id, sales_amount(prod_id) as total
 from products;

trigger를 활용해서 값 수정 전후 모으기

drop table t1 purge;
 drop table t1_monitoring purge;

 create table t1 as select * from emp;
 create table t1_monitoring (workdate date, workperson varchar2(30), t1_empno number, t1_old_sal number, t1_new_sal number);

 create or replace trigger t1_sal_monitoring
 before update of sal on t1
 for each row
 begin
   insert into t1_monitoring
   values(sysdate, user, :new.empno, :old.sal, :new.sal);
 end;
 /

 update t1
 set sal = sal + 200
 where deptno = 10;

 alter session set nls_date_format = 'YYYY/MM/DD HH24:MI:SS';

 col workperson format a10

 select * from t1_monitoring;