07_23 오라클 sql문

create or replace view v_emp  as

           select empno, ename, deptno from emp;

 

insert into v_emp values( 1000, '홍길동', 20 );

insert into v_emp values( 1000, '홍길순', 30 ); 

insert into v_emp values( 1009, '홍길순', 50 );

select * from v_emp;

 create or replace view v_emp  as

           select empno, ename, deptno from emp

           with read only;
          
delete from v_emp where empno = 800;

  create or replace view v_emp_info as
                     select e.empno empno, e.ename ename, d.dname dname
                     from emp e, dept d
                     where e.deptno=d.deptno;

 

select * from v_emp_info;

create sequence seq_tname_colname;
insert into emp(empno, ename,sal, deptno)
values(seq_tname_colname.nextval,'쟈나',15000,30);
select * from emp;

select seq_tname_colname.currval from dual;

select rownum, empno, ename, rowid
from emp where empno =7788;

select rownum, empno, ename, rowid
from emp where ename ='SCOTT';
--------------------------------------------
set serveroutput on;

declare
--변수선언
v_empno NUMBER;
v_ename varchar2(30);
v_hiredate DATE;

BEGIN
--변수값 지정, 값 출력
v_empno:=2000;
v_ename:='이무기';
v_hiredate:='14/07/23';

dbms_output.put_line('사번'||v_empno);
--나머지도
end;
----------------------------------------------
declare
--변수선언
v_empno NUMBER;
v_ename varchar2(30);
v_hiredate DATE;

BEGIN
--sql
select empno, ename, hiredate
into v_empno, v_ename, v_hiredate
from emp
where ename='SCOTT';

dbms_output.put_line('사번'||v_empno);
--나머지도
end;

accept p_ename prompt '조회할 사원이름을 입력'

declare
  v_ename emp.ename% TYPE := '&p_ename';
  /*
  --레코드 선언
  type emp_record_type is RECORD (
  v_empno emp.empno% TYPE,
  v_ename emp.ename% TYPE,
  v_job emp.job% TYPE);
 
  --레코드 변수 선언
  emp_record emp_record_type;*/
    emp_ record emp%ROWTYPE;
BEGIN
  SELECT empno, ename, job
  into emp_record
  from emp
  where ename = v_ename;
  /*
  dbms_output.put_line('사원번호 : '|| emp_record.v_empno);
  dbms_output.put_line('사원이름 : '|| emp_record.v_ename);
  dbms_output.put_line('사원직무 : '|| emp_record.v_job);
*/
dbms_output.put_line('사원번호 : '|| emp_record.empno);
  dbms_output.put_line('사원이름 : '|| emp_record.ename);
  dbms_output.put_line('사원직무 : '|| emp_record.job);
EXCEPTION
  when no_data_found then dbms_output.put_line('자료가 없습니다.');
  when too_many_rows then dbms_output.put_line('자료가 두건 이상 입니다.');
  when others then dbms_output.put_line('오류가 발생했습니다.');
end;

set serveroutput on
DECLARE
  --배열 선언
  TYPE varrar_type is varray(3) of integer;
 
  --배열변수 선언
  varray_var varrar_type;
BEGIN
  varray_var := varrar_type(100,200,300);
  dbms_output.put_line(varray_var(1));
END;
set serveroutput off

set serveroutput on

DECLARE

  TYPE nested_type IS TABLE OF VARCHAR2(20);   -- 테이블 선언

  nested_var nested_type;                       -- 테이블 변수 선언

BEGIN

  nested_var := nested_type('안녕하세요','감사합니다','열심히살자');

  FOR i IN 1..3 LOOP

    dbms_output.put_line( nested_var(i));

  END LOOP;

END;

set serveroutput off

set serveroutput on

DECLARE

  TYPE ename_table_type IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER;

  ename_table ename_table_type; 

  i BINARY_INTEGER := 1;

BEGIN

  FOR r IN ( SELECT ename FROM emp ) LOOP

    ename_table(i) := r.ename;

    i := i + 1;

  END LOOP;

 

  FOR j IN 1..i LOOP

    dbms_output.put_line( ename_table(j) );

  END LOOP;

END;

/

set serveroutput off
 

 --1. 부서번호를 입력 받아 급여의 합을 출력
set serveroutput on
ACCEPT p_deptno PROMPT '조회할 부서번호는?'
DECLARE
  v_sal NUMBER; 
BEGIN
  SELECT sum(sal) INTO v_sal FROM emp
  WHERE deptno = '&p_deptno';
  dbms_output.put_line(&p_deptno || '부서의 급여의 합은 ' || v_sal);
END;
--2. 초기값이 8000부터 9999까지 1씩 증가하는 시퀀스(emp_empno_seq)를 생성하여 EMP 테이블에 등록하되, 이름은 JONG, 업무는 MANAGER, 부서번호는 10인 경우
          -- 결과는 select로 데이터 입력 확인
         -- 익명블록을 몇 번 실행하여 시퀀스값 확인

create sequence emp_empno_seq start with 8000 maxvalue 9999;
DECLARE
BEGIN
  INSERT INTO EMP(EMPNO, ENAME, JOB, DEPTNO)  VALUES (emp_empno_seq.NEXTVAL, 'JONG','MANAGER',10);
END;

--3. 사원번호가 8000인 사원의 급여에 1000를 더하려면
set serveroutput on;
declare
v_empno emp.empno%TYPE := 8000;
v_sal emp.sal%type;
begin
SELECT empno, nvl(sal,0)
into v_empno, v_sal
FROM emp
where empno = v_empno;
if(v_empno=8000) then v_sal:= v_sal+1000;
end if;
update emp set sal=v_sal
where empno=v_empno;
commit;
end;

--4. 입력한 사원번호의 정보를 삭제
ACCEPT p_empno PROMPT '삭제할 사원 번호를 입력하세요: '

DECLARE
  v_sal NUMBER; 
BEGIN
  DELETE FROM emp WHERE empno = '&p_empno';
END;

 

--1. 이름, 급여, 부서번호를 입력 받아 사원 테이블에 입력시 부서번호가 20이면 급여의 30%를 추가하고, 사번은 시퀀스를 이용한다.

accept p_name prompt '이름은'
accept p_sal prompt '급여는'
accept p_deptno prompt '부서번호는'

DECLARE
    v_name emp.ename%type := upper('&p_name');
    v_sal emp.sal%type := &p_sal;
    v_deptno emp.deptno%type := &p_deptno;
BEGIN
if v_deptno = 20 then v_sal := v_sal*1.3;
end if;
  insert into emp(empno, ename, sal, deptno)values(seq_tname_colname.nextval, v_name, v_sal, v_deptno);
END;

--2. 이름을 입력받아 그 사람의 업무가 MANAGER이면 10% , ANALYST이면 20%, SALESMAN이면 30%, PRESIDENT이면 40%, CLERK이면 50% 급여를 증가한다.
 
 
 
 accept p_name prompt '이름은'

DECLARE
    v_name emp.ename%type := upper('&p_name');
   v_empno    emp.empno%type;
    v_sal emp.sal%type ;
    v_job emp.job%type ;
   
BEGIN
 select empno,job,sal
        into v_empno,v_job,v_sal
        from emp
        where ename=v_name;
if v_job = 'MANAGER' then v_sal := v_sal*1.1;
elsif v_job = 'ANALYST' then v_sal := v_sal*1.2;
elsif v_job = 'SALESMAN' then v_sal := v_sal*1.3;
elsif v_job = 'PRESIDENT' then v_sal := v_sal*1.4;
elsif v_job = 'CLERK' then v_sal := v_sal*1.5;
end if;
  update emp set sal = v_sal where  empno=v_empno;

END;
 
select *from emp;
-------------------------------------------------------

 accept p_name prompt '이름은'

DECLARE
    v_name emp.ename%type := upper('&p_name');
   v_empno    emp.empno%type;
    v_sal emp.sal%type ;
    v_job emp.job%type ;
   
BEGIN
 select empno,job,sal
        into v_empno,v_job,v_sal
        from emp
        where ename=v_name;

case v_job
'MANAGER' then v_sal := v_sal*1.1;
when 'ANALYST' then v_sal := v_sal*1.2;
when 'SALESMAN' then v_sal := v_sal*1.3;
when 'PRESIDENT' then v_sal := v_sal*1.4;
when 'CLERK' then v_sal := v_sal*1.5;
end case;
  update emp set sal = v_sal where  empno=v_empno;

END;
-- 1부터 9까지의 숫자를 입력 받아 해당하는 구구단을 출력

set serveroutput on;
accept p_su prompt '몇단'
declare
  v_su integer := &p_su;
  i integer;
  cnt integer;
begin
for i in 1..9 loop  --reverse
 cnt := v_su * i;
 dbms_output.put_line(v_su ||'*' ||i||'='||cnt);
end loop;
end;
-- 1부터 100까지의 홀수의 합과 짝수의 합을 출력
declare
  i integer;
  h_sum integer :=0;
  j_sum integer:=0;
 
begin
for i in 1..100 loop  --reverse
if i mod 2 = 0 then j_sum := j_sum+i;
 else h_sum := h_sum+i;
end if;
end loop;
 dbms_output.put_line('홀수의 합은'||h_sum);
 dbms_output.put_line('짝수의 합은'||j_sum);
end;

declare
  i integer:=0;
  h_sum integer :=0;
  j_sum integer:=0;
 
begin
while i <= 100 loop  --reverse
if i mod 2 = 0 then j_sum := j_sum+i;
 else h_sum := h_sum+i;
end if;

i := i+1;
end loop;
 dbms_output.put_line('홀수의 합은'||h_sum);
 dbms_output.put_line('짝수의 합은'||j_sum);
end;
--1@ 부서번호를 입력 받아 사원번호, 이름, 급여를 출력
Accept p_deptno prompt '부서번호는'
declare
  v_deptno emp.deptno%type := &p_deptno;
  v_empno emp.empno%TYPE;
  v_ename emp.ename%TYPE;
  v_sal emp.sal%TYPE;
  cursor e_csr is select empno, ename, sal from emp where deptno = v_deptno;
begin
open e_csr;
loop
fetch e_csr into v_empno, v_ename, v_sal;
exit when e_csr%notfound;
dbms_output.put_line(lpad(v_empno, 10)||lpad (v_ename,20)|| lpad(v_sal,10));
end loop;
close e_csr;
end;
--사번과 급여를 넘겨받아 수정하는 프로시져

 
create or replace PROCEDURE emp_sal_update(
  p_empno in emp.empno%type,
  p_sal in emp.sal%type
  )is
begin
update emp set sal = p_sal where empno = p_empno;
if SQL%notfound then
  dbms_output.put_line('존재하지 않는 사번입니다');
  else dbms_output.put_line('성공');
end if;
commit;
end;

exec emp_sal_update(7788,15000);

select object_name, object_type from user_bjects
where object_type = 'PROCEDURE';

 --사원명, 업무, 매니저, 급여를 넘겨받아 등록하는 프로시져를 생성 단, 부서번호는 매니저의 부서 번호와 동일하고 사원번호는 시퀀스를 이용.
CREATE OR REPLACE PROCEDURE emp_input (
        v_name    IN  emp.ename%TYPE,
        v_job     IN  emp.job%TYPE,
        v_mgr     IN  emp.mgr%TYPE,
        v_sal     IN  emp.sal%TYPE )
IS
    -- 변수선언
    v_deptno  emp.deptno%TYPE;
BEGIN
    SELECT deptno INTO v_deptno  FROM emp WHERE empno=v_mgr;
    INSERT INTO emp(empno, ename, job, mgr, sal, deptno )
        VALUES( emp_empno_seq.nextval, upper(v_name), upper(v_job), v_mgr, v_sal, v_deptno );
EXCEPTION
    WHEN NO_DATA_FOUND THEN dbms_output.put_line('입력한 매니저 번호가 없습니다');
    WHEN OTHERS THEN dbms_output.put_line('에러발생');
END;

-- 넘겨 받은 사원의 정보 중 부서명과 급여를 검색하는 프로시져
CREATE OR REPLACE PROCEDURE dname_sal_display (
        v_ename   IN  emp.ename%TYPE,
        v_dname   OUT dept.dname%TYPE,
        v_sal     OUT emp.sal%TYPE  )
IS v_deptno  emp.deptno%TYPE;
BEGIN
    SELECT deptno, sal INTO v_deptno, v_sal  FROM emp WHERE ename=upper(v_ename);
    SELECT dname INTO v_dname FROM dept WHERE deptno=v_deptno;
EXCEPTION
    WHEN NO_DATA_FOUND THEN dbms_output.put_line('해당 데이터가 없습니다');
    WHEN TOO_MANY_ROWS THEN dbms_output.put_line('검색 데이터가 2건 이상입니다.');
    WHEN OTHERS THEN dbms_output.put_line('에러발생');
END;

VAR g_dname VARCHAR2(15)
VAR g_sal NUMBER
set serveroutput on
EXECUTE dname_sal_display('SCOTT', :g_dname, :g_sal );
PRINT g_dname
PRINT g_sal

--사원명을 입력받아 부서번호, 부서명, 급여를 검색하는 FUNCTION을 작성하되 부서번호를 리턴한다
CREATE OR REPLACE FUNCTION emp_dis_func ( v_ename   IN  emp.ename%TYPE )
RETURN NUMBER
IS
    v_deptno  emp.deptno%TYPE;
    v_dname   dept.dname%TYPE;
    v_sal     emp.sal%TYPE;
BEGIN
    SELECT deptno, sal INTO v_deptno, v_sal  FROM emp WHERE ename=upper(v_ename);
    SELECT dname INTO v_dname FROM dept WHERE deptno=v_deptno;
    dbms_output.put_line('사 원 명 : ' || v_ename );
    dbms_output.put_line('부서번호 : ' || v_deptno );
    dbms_output.put_line('부 서 명 : ' || v_dname );
    dbms_output.put_line('급   여  : ' || v_sal );
    RETURN v_deptno;
EXCEPTION
    WHEN NO_DATA_FOUND THEN dbms_output.put_line('해당 데이터가 없습니다');
    WHEN TOO_MANY_ROWS THEN dbms_output.put_line('검색 데이터가 2건 이상입니다.');
    WHEN OTHERS THEN dbms_output.put_line('에러발생');
END;

 

--------------------------------------------------------------

--1. 최고의 월급을 받는 사원의 사번을 구하여 출력하는 프로시져
 CREATE OR REPLACE PROCEDURE pro_ex1(
  v_empno OUT emp.empno%type
)
IS
  v_max_sal int;
BEGIN
  select max(sal) into v_max_sal
  from emp;
 
  select empno into v_empno
  from emp where sal=v_max_sal;

  dbms_output.put_line('사번 : ' || v_empno);

EXCEPTION
    WHEN NO_DATA_FOUND THEN
      dbms_output.put_line('사원이 없습니다');
    WHEN OTHERS THEN
      dbms_output.put_line('에러발생'); 
END;
/
var empno number      --@실행
set serveroutput on
EXECUTE pro_ex1(:empno)
print empno

--2. 이름을 넘겨받아 부서명을 구하여 출력하는 프로시져
CREATE OR REPLACE PROCEDURE pro_ex2(
  v_ename in emp.ename%type,
  v_dname OUT dept.dname%type
)
IS
  v_deptno number;
BEGIN
  select dname into v_dname
  from emp ee, dept dd
  where ee.deptno=dd.deptno and ee.ename=v_ename;   
 

  dbms_output.put_line('부서명 : ' || v_dname);

EXCEPTION
    WHEN NO_DATA_FOUND THEN
      dbms_output.put_line('그 이름의 사원이 없습니다');
    WHEN OTHERS THEN
      dbms_output.put_line('에러발생'); 
END;
/
var ename VARCHAR2(10)     --@실행
var dname VARCHAR2(10)
set serveroutput on
EXECUTE pro_ex2('SCOTT', :dname)
print dname

--3. 부서번호를 넘겨받아 그 부서의 최고 급여를 구하여 출력하는 함수
CREATE OR REPLACE FUNCTION fun_ex3(
  v_deptno in emp.deptno%type
)
RETURN NUMBER                   -- number타입을 리턴
IS 
  v_max_sal emp.sal%type;
BEGIN
  select max(sal) into v_max_sal
  from emp
  where deptno=v_deptno;
 
  dbms_output.put_line('최고급여 : ' || v_max_sal); 

  return v_max_sal;
EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line('에러발생');
END;
/
--@실행
VAR g_max_sal NUMBER;
set serveroutput on
exec :g_max_sal :=fun_ex3(20)
PRINT g_max_sal

 
--4. 사번과 이동할 부서를 넘겨받아 수정하는 프로시져
--단, 연봉은 기존 연봉의 30% 증가를 하되 이동할 부서의 최고연봉보다 높으면 최고연봉으로 지정하고, 최소연봉보다 낮으면 최소연봉으로 지정한다.
CREATE OR REPLACE PROCEDURE pro_ex4(
  v_empno in emp.empno%type,
  v_dname in dept.dname%type
)
IS
  v_deptno number;
BEGIN

  select deptno into v_deptno
  from dept where dname=v_dname;
 
  update emp
  set deptno=v_deptno
  where empno=v_empno;

  dbms_output.put_line('이동부서 : ' || v_dname);

EXCEPTION
    WHEN NO_DATA_FOUND THEN
      dbms_output.put_line('그 부서가 없습니다');
    WHEN OTHERS THEN
      dbms_output.put_line('에러발생'); 
END;
/
set serveroutput on
EXECUTE pro_ex4(7369, 'SALES')

select * from emp;
select * from dept;