oracle sql

(1)

variable month_sal number;

declare

v_sal number(8) :=1800;

begin

:month_sal :=v_sal/12;

end;

/

print month_sal;

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

(2)

set serveroutput on

ed d:\test1.sql

declare 

v_sal number(8) :=3000;

month_sal number(8);

begin

month_sal :=v_sal/12;

dbms_output.put_line('내 급여는'||to_char(month_sal));

end;

/

set serveroutput on

@test1.sql

(3)

declare

v_deptno dept.deptno%type;

v_dname  dept.dname%type;

begin

select deptno, dname into v_deptno,v_dname

from dept

where loc='BOSTON';

dbms_output.put_line(v_deptno || ' -> '|| v_dname);

end;

/

set serveroutput on

(4)emp 테이블에서 부서번호 값이 10인 데이터들의 평균급여를 구하여라

set serveroutput on

declare

v_sal emp.sal%type;

begin

select avg(sal) into v_sal

from emp

where deptno='10';

dbms_output.put_line('평균급여:'||v_sal);

end;

/

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

set serveroutput on

declare 

v_empno emp.empno%type;

v_ename emp.ename%type;

v_sal   emp.sal%type;

v_p emp.sal%type;

v_cha emp.sal%type;

begin

select empno,ename,sal,avg(sal),(avg(sal)-sal)

into v_empno,v_ename,v_sal,v_p,v_cha

from emp

where deptno='10';

dbms_output.put_line(v_empno||':'||v_ename||':'||v_sal||':'||v_p||':'||p_cha);

end;

/

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

insert_dept.sql

declare

v_deptno dept.deptno%type :='90';

v_dname  dept.dname%type  :='총무부';

v_loc dept.loc :='부산';

begin

insert into dept value(v_deptno,v_dname,v_loc);

end;

/

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

(5)emp테이블에서 사원번호 ,사원이름을 사원번호별로 역순 정렬하여 2행~5행 출력

select empno,ename from (select emp no)

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

declare

v_comm emp.comm%type :=400;

begin

update emp set 

comm=NVL(comm,0)+v_comm

where deptno=10;

end;

/

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

외부데이터 받아서 처리(&기호)

select deptno from emp;

declare 

v_deptno dept.deptno%type :=&dept_no;

begin

delete from emp where deptno=v_deptno;

end;

/

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

if문

set serveroutput on

declare

v_deptno dept.deptno%type :=&dept_no;

v_cnt number(3);

begin

select count(*) into v_cnt from emp where deptno=v_deptno;

dbms_output.put_line('삭제할 개수:'||v_cnt);

if v_cnt = 0 then

dbms_output.put_line('삭제할 내용이 없습니다.');

else

delete from emp where deptno=v_deptno;

dbms_output.put_line('내용이 삭제되었습니다.');

end if;

end;

/

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

(9)부서번호를 입력하여 부서명을 출력(if ~els if~ else)

declare

v_deptno dept.deptno%type :=&dept_no;

v_cnt number(3);

v_dname dept.dname%type;

begin

select count(*) into v_cnt from dept where deptno=v_deptno;

if v_cnt=0 then

dbms_output.put_line(v_deptno||'는 없는 부서번호입니다.');

else

select dname into v_dname from dept where deptno=v_deptno;

dbms_output.put_line(v_deptno||'의 부서명은'||v_dname||'입니다.');

end if;

end;

/

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

(10)loop~exit where end loop

create table abc1(no number(9));

declare

v_counter number :=1;

begin

loop

insert into abc1(no) values(v_counter);

v_counter :=v_counter+1;

dbms_output.

exit when v_counter > 9;

end loop;

end;

/