오라클 데이터 타입(이미지 쓸라면 아니면 파일 추가하려면)
| Data Type | 특징 | 크기 |
| BLOB | 이미지, 동영상 | 128TB |
| CLOB | 대용량 문자 | 128TB |
| NCLOB | 유니코드 대용량 문자 | 128TB |
| BFILE | 대용량 이진파일, 운영체제 파일로저장 | os파일사이즈 |
over(partition by)를 통해서 해결가능
select empno, ename, job, sum(sal) over(partition by job)
--job을 그룹한 것에 맞춰서 집계함 결국 여러번씩 출력됌
from emp
order by job;
plsql
트리거, 함수, 프로시저, 등등 4가지 잘못건드리면 DB 다운됌
+표시를 눌러서 사용자를 누른다
프린트 결과창을 볼수 있다
oracle에서는 =이 같다라는 표현이기 때문에 대입은 :=로한다
declare -begin - end를 프로시저로 묶어서 실행되도록하는 편리한 방법도 있다.
pl-sql에서는 into구문에들어가는 변수에 항상 한가지의 row만 대입한 다 그래서 탄생한 게 cusor!
pl-sql에서 입력받기 &empno임 "&"
선언할때 테이블에서 값가져올때 값을 동일하게 해줘야하는데
그때 쓰는데 테이블.컬럼명%TYPE
테이블의 컬럼을 전부다 가져올수 있음 테이블명%ROWTYPE
DECLARE
v_emprow emp%ROWTYPE;
BEGIN
select *
into v_emprow -- empno , ename , ,..... deptno
from emp
where empno=7788;
DBMS_OUTPUT.PUT_LINE(v_emprow.empno || '-' || v_emprow.ename || '-' || v_emprow.sal);
END;if~ then~elsif ~then~end if;
"이엘에스이프" 로 읽자
고급
cursor
커서도 변수 개념 declare에 선언해서
open 커서가 갖고 있는 sql문 실행해서 메모리에 실행 데이터를 올림
loop를 돌릴꺼다
fetch 순간 커서가 첫번째 row를 가리킴 그전까진 그냥 시작부 가리키고
언제탈출??? 커서가 리턴하는 시스템 변수 사용
exit when c1%NOTFOUND;--더이상 row 없으면 탈출
close ->커서에 대한 메모리 할당 해제
for loop 사용시에는 open/fetch/close를 사용하지 않아도 된다
사용자 정의 예외
RAISE_APPLICATION_ERROR(-20002,'my no data found'); --사용자가 예외 만들기 (강제 예외 던지기)
프로시저생성후
create or replace procedure usp_emplist --create or replace (생성 가능 , 수정 가능)
is
BEGIN
update emp
set job = 'TTT'
where deptno=30;
END;execute로 호출
execute usp_emplist;
프로시저 장점
자바코드에 select 코드를 적어서 보내는게 좋은가?
패킷을 도청당했을때 정보 유출가능
sql문의 길이 때문에 트래픽 증가
실제로 코드는 db에 있고 자바에서 부르는게 좋은가?(프로시저)
네트워크 트래픽감소(프로시저 이름만 부르면 돼)
도청당해도 정확하게 무슨일을 하는 건지 모름
이미 DB서버에 컴파일 되어있어서 사용만 하면 됨
DECLARE
v_ename emp.ename%TYPE := '&p_ename';
v_err_code NUMBER;
v_err_msg VARCHAR2(255);
BEGIN
DELETE emp WHERE ename = v_ename;
IF SQL%NOTFOUND THEN
RAISE_APPLICATION_ERROR(-20002,'my no data found'); --사용자가 예외 만들기 (강제 예외 던지기)
--SQLCODE , SQLERRM
END IF;
EXCEPTION
WHEN OTHERS THEN --sQL%~로 정의된 예외 외에는 others에 해당한다
ROLLBACK;
v_err_code := SQLCODE;
v_err_msg := SQLERRM;
DBMS_OUTPUT.PUT_LINE('에러 번호 : ' || TO_CHAR(v_err_code));
DBMS_OUTPUT.PUT_LINE('에러 내용 : ' || v_err_msg);
END;
프로시저 반환 값 cursor 가 있는 애
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import kr.or.kosa.utils.ConnectionHelper;
import oracle.jdbc.OracleTypes;
/*
create or replace procedure usp_EmpList
(
p_sal IN number,
p_cursor OUT SYS_REFCURSOR -- 한건이상이면 메모리에 올려서 커서처럼 갖고 감
--java, c#언어에서 여러건의 데이터를 처리하려면 (커서타입을 사용해야한다.)
)
is
begin
open p_cursor
for select empno, ename , sal from emp where sal>p_sal;
end;
--데이터가 여러건일때
var out_cursor REFCURSOR
exec usp_EmpList(2000,:out_cursor);
print out_cursor;--developer의 print기능
* */
public class Ex10_Oracle_ProcedureCall {
public static void main(String[] args) {
Connection conn = null;
CallableStatement cstmt = null; //프로시저 사용할 때
//PreparedStatement
ResultSet rs = null;
try {
conn = ConnectionHelper.getConnection("oracle");
// String sql = "select..";
String sql = "{call usp_EmpList(?,?)}";
cstmt = conn.prepareCall(sql);//DB서버에 미리 컴파일 시켜놓음
// p_sal IN number,
// p_cursor OUT SYS_REFCURSOR
cstmt.setInt(1, 2000);
cstmt.registerOutParameter(2, OracleTypes.CURSOR);//p_cursor OUT SYS_REFCURSOR
boolean result = cstmt.execute();
rs = (ResultSet)cstmt.getObject(2);//Object타입으로 가져와서 캐스팅
while(rs.next()) {
System.out.println(rs.getInt(1)+" / "+rs.getString(2)+" / "+rs.getInt(3));
}
}catch(Exception e) {
System.out.println(e.getMessage());
}finally {
ConnectionHelper.close(conn);
ConnectionHelper.close(rs);
ConnectionHelper.close(cstmt);
}
}
}dB에서 오류메세지를 받아와서 출력할 수 있는애
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Types;
import kr.or.kosa.utils.ConnectionHelper;
/*
create or replace procedure usp_Insert_Emp
(
vempno IN emp.empno%TYPE,
vename IN emp.ename%TYPE,
vjob IN emp.job%TYPE,
p_outmsg OUT varchar2
)
is
begin
insert into emp(empno, ename, job) values(vempno,vename,vjob);
commit;
p_outmsg :='SUCCESS';
Exception when others then
p_outmsg := SQLERRM;
rollback;
end;
alter table emp
add constraint pk_emp_empno primary key(empno);
*
* */
public class Ex11_Oracle_ProcedureCall_DML {
public static void main(String[] args) {
Connection conn = null;
CallableStatement cstmt = null;
try {
conn = ConnectionHelper.getConnection("oracle");
String sql = "{call usp_Insert_Emp(?,?,?,?)}";//insert,update,delete하는 프로시져 동일
cstmt = conn.prepareCall(sql);
//3개 input , 1개 output 일반타입
cstmt.setInt(1, 9999);
cstmt.setString(2, "홍길동");
cstmt.setString(3,"IT");
cstmt.registerOutParameter(4, Types.VARCHAR);
cstmt.execute();
String oracle_msg = (String)cstmt.getObject(4);
System.out.println("oracle server message : "+oracle_msg);
}catch(Exception e) {
System.out.println(e.getMessage());
}finally {
ConnectionHelper.close(conn);
ConnectionHelper.close(cstmt);
}
}
}
0719수업
show user;
--제 15 장 PL/SQL 개요
--page 224
--PL-SQL
--PL/SQL 은 Oracle's Procedural Language extension to SQL. 의 약자 입니다.
--SQL문장에서 변수정의, 조건처리(IF), 반복처리(LOOP, WHILE, FOR)등을 지원하며,
--오라클 자체에 내장되어 있는Procedure Language입니다
--DECLARE문을 이용하여 정의되며, 선언문의 사용은 선택 사항입니다.
--PL/SQL 문은 블록 구조로 되어 있고PL/SQL 자신이 컴파일 엔진을 가지고 있습니다.
--Tool > 보기 > DBMS 출력창 > + 버튼 클릭 > 사용자 접속(개발자)
--DBMS 출력창 : 이클립스 console 창
--pl-sql (java : System.out.println()) 결과 확인
--DBMS 출력 창에서
--1.pl-sql 블럭 단위 실행
BEGIN
DBMS_OUTPUT.PUT_LINE('HELLO WORLD');
END;
--pl-sql
--선언부(변수)
--실행부(변수 값을 할당 , 제어구문)
--예외부(Exception)
DECLARE --선언
vno number(4);
vname varchar2(20);
BEGIN
vno := 100; -- 할당 > String s; s = "홍길동"
vname := 'kglim';
DBMS_OUTPUT.PUT_LINE(vno); --화면 출력
DBMS_OUTPUT.PUT_LINE(vname || '입니다');
END;
--변수 선언 방법 (타입)
--DECLARE
--v_job varchar2(10);
--v_count number := 10; --초기값 설정
--v_date date := sysdate + 7; --초기값 설정
--v_valid boolean not null := true
--------------------------------------------------------------------------------
DECLARE
vno number(4);
vname varchar2(20);
BEGIN
select empno ,ename
into vno , vname --pl-sql 사용하는 구분 (into) . 실행결과 [ 변수에 담기 ]
from emp
where empno=&empno; --& 자바 scanner 역할 (입력값 받기)
DBMS_OUTPUT.PUT_LINE('변수값 : ' || vno || '/' || vname);
END;
--실습 테이블 만들기
create table pl_test(
no number , name varchar2(20) , addr varchar2(50));
DECLARE
v_no number := '&NO';
v_name varchar2(20) := '&NAME';
v_addr varchar2(50) := '&ADDR';
BEGIN
insert into pl_test(no,name,addr)
values(v_no , v_name , v_addr);
commit;
END;
desc emp;
select * from pl_test;
--변수 제어하기(타입)
--1.1 타입 : v_empno number(10)
--1.2 타입 : v_empno emp.empno%TYPE (emp 테이블에 있는 empno 컬럼의 타입 사용)
--1.3 타입 : v_row emp%ROWTYPE (v_row 변수는 emp 테이블 모든 컬럼 타입 정보)
-- v_row.empno , v_row.ename
--QUIZ
--두개의 정수를 입력받아서 그 합을 변수에 담아서 출력
DECLARE
v_no1 number := '&no1';
v_no2 number := '&no2';
result number;
BEGIN
result := v_no1 + v_no2;
DBMS_OUTPUT.PUT_LINE('result : ' || result);
END;
--------------------------------------------------------------------------------
DECLARE
v_emprow emp%ROWTYPE;
BEGIN
select *
into v_emprow -- empno , ename , ,..... deptno
from emp
where empno=7788;
DBMS_OUTPUT.PUT_LINE(v_emprow.empno || '-' || v_emprow.ename || '-' || v_emprow.sal);
END;
--------------------------------------------------------------------------------
create sequence empno_seq
increment by 1
start with 8000
maxvalue 9999
nocycle
nocache;
DECLARE
v_empno emp.empno%TYPE;
BEGIN
select empno_seq.nextval
into v_empno
from dual;
insert into empdml(empno ,ename)
values(v_empno/*empno_seq.nextval*/,'홍길동');
commit;
END;
select * from empdml;
create table empdml
as
select * from emp where 1=2;
--여기까지 변수 선언 , 타입 , 값 할당
--------------------------------------------------------------------------------
--pl-sql 제어문
DECLARE
vempno emp.empno%TYPE;
vename emp.ename%TYPE;
vdeptno emp.deptno%TYPE;
vname varchar2(20) := null;
BEGIN
select empno , ename , deptno
into vempno , vename , vdeptno --변수
from emp
where empno=7788;
--제어문 if(조건문){실행문}
IF(vdeptno = 10) THEN vname := 'ACC'; -- if(vdeptno==10) { vname = "ACC"}
ELSIF(vdeptno=20) THEN vname := 'IT'; --
ELSIF(vdeptno=30) THEN vname := 'SALES';
END IF;
DBMS_OUTPUT.PUT_LINE('당신의 직종은 : ' || vname);
END;
--IF() THEN 실행문
--ELSIF() THEN 실행문
--ELSIF() THEN 실행문
--ELSE 실행문 (선택)
--END IF;
--사번이 7788번인 사원의 사번 , 이름 , 급여를 변수에 담고
--변수에 담긴 급여가 2000 이상이면 '당신의 급여는 BIG' 출력하고
--그렇지 않으면(ELSE) '당신의 급여는 SMALL' 이라고 출력하세요
DECLARE
vempno emp.empno%TYPE;
vename emp.ename%TYPE;
vsal emp.sal%TYPE;
BEGIN
select empno , ename , sal
into vempno , vename , vsal
from emp
where empno=7788;
--제어문 if(조건문){실행문}
IF(vsal >= 2000) THEN
DBMS_OUTPUT.PUT_LINE('당신의 급여는 BIG ' || vsal);
ELSE
DBMS_OUTPUT.PUT_LINE('당신의 급여는 SMALL ' || vsal);
END IF;
END;
-------------------------------------------------------------------------------
--CASE
DECLARE
vempno emp.empno%TYPE;
vename emp.ename%TYPE;
vdeptno emp.deptno%TYPE;
v_name varchar2(20);
BEGIN
select empno, ename , deptno
into vempno, vename , vdeptno
from emp
where empno=7788;
-- v_name := CASE vdeptno
-- WHEN 10 THEN 'AA'
-- WHEN 20 THEN 'BB'
-- WHEN 30 THEN 'CC'
-- WHEN 40 THEN 'DD'
-- END;
v_name := CASE
WHEN vdeptno=10 THEN 'AA'
WHEN vdeptno in(20,30) THEN 'BB'
WHEN vdeptno=40 THEN 'CC'
ELSE 'NOT'
END;
DBMS_OUTPUT.PUT_LINE('당신의 부서명:' || v_name);
END;
--------------------------------------------------------------------------------
--pl-sql (반복문)
--Basic loop
/*
LOOP
문자;
EXIT WHEN (조건식)
END LOOP
*/
DECLARE
n number :=0;
BEGIN
LOOP
n := n + 1;
DBMS_OUTPUT.PUT_LINE('n value : ' || n);
EXIT WHEN n > 5;
END LOOP;
END;
/*
WHILE(n < 6)
LOOP
실행문;
END LOOP
*/
DECLARE
num number := 0;
BEGIN
WHILE(num < 6)
LOOP
DBMS_OUTPUT.PUT_LINE('num 값 : ' || num);
num := num +1;
END LOOP;
END;
--for
--java for(int i = 0 ; i <= 10 ; i++) {}
BEGIN
FOR i IN 0..10 LOOP
DBMS_OUTPUT.PUT_LINE(i);
END LOOP;
END;
--위 FOR 문을 사용해서 (1~100까지 합) 구하세요
DECLARE
total number :=0;
BEGIN
FOR i IN 1..100 LOOP
total := total + i;
END LOOP;
DBMS_OUTPUT.PUT_LINE('1~100 총합 : ' || total); --5050
END;
--11g 이전 (continue (x))
--11g (continue 추가)
DECLARE
total number := 0;
BEGIN
FOR i IN 1..100 LOOP
DBMS_OUTPUT.PUT_LINE('변수 : ' || i);
CONTINUE WHEN i > 5; --skip
total := total + i; -- 1 , 2 , 3 , 4, 5
END LOOP;
DBMS_OUTPUT.PUT_LINE('합계 : ' || total);
END;
--------------------------------------------------------------------------------
--활용
DECLARE
v_empno emp.empno%TYPE;
v_name emp.ename%TYPE := UPPER('&name');
v_sal emp.sal%TYPE;
v_job emp.job%TYPE;
v_deptno emp.deptno%TYPE;
BEGIN
select empno , job ,sal , deptno
into v_empno, v_job , v_sal , v_deptno
from emp
where ename = v_name;
IF v_job IN('MANAGER','ANALYST') THEN
v_sal := v_sal * 1.5;
ELSE
v_sal := v_sal * 1.2;
END IF;
update emp
set sal = v_sal
where deptno=v_deptno;
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || '개의 행이 갱신 되었습니다');
--예외처리
EXCEPTION
WHEN NO_DATA_FOUND THEN --오라클내부에서 예외로 던져주는 값 자체를 작성해준것임
DBMS_OUTPUT.PUT_LINE(v_name || '는 자료가 없습니다');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE(v_name || '는 동명 이인입니다');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('기타 에러가 발생했습니다');
END;
insert into emp(empno,ename) values(5555,'SMITH');
rollback;
SELECT * FROM EMP;
/*
질의는 하나의 행만 RETURN 해야 합니다. PL/SQL 블록 내의 SELECT 문장은 다음 규칙을
적용하는 Embedded SQL 의 ANSI 범주에 속합니다. 질의의 결과는 하나의 행만을 RETURN 해
야 하고 하나의 행 이상 또는 행이 없는 것은 에러를 생성합니다. PL/SQL 은
NO_DATA_FOUND 와 TOO_MANY_ROWS 를 예외로 블록의 예외 섹션에서 추적할 수 있는 표준 예
외를 조성하여 처리 합니다.
*/
select * from emp where ename='SMITH';
rollback;
-- pl-sql 기본 구문 END
--------------------------------------------------------------------------------
-- cursor , procedure , function , Trigger 고급자원
--[ 커서 ]
--지금까지 집합 단위의 데이터 처리 (전체 row를 대상으로)
--[CURSOR]
--1. [행단위]로 데이터를 처리하는 방법을 제공
--2. 여러건의 데이터를 처리하는 처리하는 방법을 제공 (한 건이상의 row가지고 놀기)
--사원급여테이블(건설회사)
--정규직 , 일용일 ,시간직
--사번 , 이름 , 직종명 , 월급 , 시간 , 시간급 , 식대
-- 10 홍길동 정규직 120 null null null
-- 11 김유신 시간직 null 10 100 null
-- 12 이순신 일용일 null null 120 10
--최종 출력 (판단의 기준이 : 직종 조건 .. row 단위)
--사번 , 이름 , 이번달 급여총액
--10 홍길동 (월급 : 120 )
--11 김유신 (시간*시간급 : 10 *100)
--12 이순신 (시간급 +식대 : 120+10)
--정규직
--월급
--
--일용직
--시간 * 시간급
--
--시간직
--시간급 + 식대
--한 행씩 접근해서 직종을 기준으로 계산방법
--if 정규직 > 월급 (총급여)
--elsif 시간직 > 시간 * 시간급 (총급여)
--elsif 일용직 > 시간급 + 식대 (총급여)
--SQL CURSOR 의 속성을 사용하여 SQL 문장의 결과를 테스트할 수 있다.
--[종 류 설 명]
--SQL%ROWCOUNT 가장 최근의 SQL 문장에 의해 영향을 받은 행의 수
--SQL%FOUND 가장 최근의 SQL 문장이 하나 또는 그 이상의 행에 영향을 미친다면 TRUE 로 평가한다.
--SQL%NOTFOUND 가장 최근의 SQL 문장이 어떤 행에도 영향을 미치지 않았다면 TRUE 로 평가한다.
--SQL%ISOPEN PL/SQL 이 실행된 후에 즉시 암시적 커서를 닫기 때문에 항상 FALSE 로 평가된다.
/*
DECLARE
CURSOR 커서이름 IS 문장(커서가 실행할 쿼리)
BEGIN
OPEN 커서이름 (커서가 가지고 있는 쿼리를 실행)
FETCH 커서이름 INTO 변수명들...
--커서로 부터 데이터를 읽어서 원하는 변수에 저장
CLOSE 커서이름 (커서닫기)
END
*/
DECLARE
vempno emp.empno%TYPE;
vename emp.ename%TYPE;
vsal emp.sal%TYPE;
CURSOR c1 IS select empno,ename,sal from emp where deptno=30;
BEGIN
OPEN c1; --커서가 가지고 있는 문장 실행
LOOP --데이터 row 건수 만큼 반복
--Memory
/*
7499 ALLEN 1600
7521 WARD 1250
7654 MARTIN 1250
7698 BLAKE 2850
7844 TURNER 1500
7900 JAMES 950
*/
FETCH c1 INTO vempno , vename, vsal;
--if...then..
--교수님꺼 복사;;;---=저레적ㅎ거ㅔㅓㄷ
EXIT WHEN c1%NOTFOUND; --더이상 row 가 없으면 탈출
DBMS_OUTPUT.PUT_LINE(vempno || '-' || vename || '-'|| vsal);
END LOOP;
CLOSE c1;
END;
-------------------------------------------------------
--위 표현을 좀 더 간단하게
--java (for(emp e : emplist){}
DECLARE
CURSOR emp_curr IS select empno ,ename from emp;
BEGIN
FOR emp_record IN emp_curr --row 단위로 emp_record변수 할당
LOOP
EXIT WHEN emp_curr%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(emp_record.empno || '-' || emp_record.ename);
END LOOP;
END;
--------------------------------------------------------------------------------
--java (for(emp e : emplist){}
DECLARE
vemp emp%ROWTYPE; --Type 정의
CURSOR emp_curr IS select empno ,ename from emp;
BEGIN
FOR vemp IN emp_curr --row 단위로 emp_record변수 할당
LOOP
EXIT WHEN emp_curr%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(vemp.empno || '-' || vemp.ename);
END LOOP;
END;
-------------------------------------------------
DECLARE
v_sal_total NUMBER(10,2) := 0;
CURSOR emp_cursor
IS SELECT empno,ename,sal FROM emp
WHERE deptno = 20 AND job = 'CLERK'
ORDER BY empno;
BEGIN
DBMS_OUTPUT.PUT_LINE('사번 이 름 급 여');
DBMS_OUTPUT.PUT_LINE('---- ---------- ----------------');
FOR emp_record IN emp_cursor
LOOP
v_sal_total := v_sal_total + emp_record.sal;
DBMS_OUTPUT.PUT_LINE(RPAD(emp_record.empno,6) || RPAD(emp_record.ename,12) ||
LPAD(TO_CHAR(emp_record.sal,'$99,999,990.00'),16));
END LOOP;
DBMS_OUTPUT.PUT_LINE('----------------------------------');
DBMS_OUTPUT.PUT_LINE(RPAD(TO_CHAR(20),2) || '번 부서의 합 ' ||
LPAD(TO_CHAR(v_sal_total,'$99,999,990.00'),16));
END;
--------------------------------------------------------------------------------
select * from emp where deptno=20 and job='CLERK';
create table cursor_table
as
select * from emp where 1=2;
select* from cursor_table;
alter table cursor_table
add totalsum number;
--문제
--emp 테이블에서 사원들의 사번 , 이름 , 급여를 가지고
--와서 cursor_table insert 를 하는데 totalsum 은 급여 + comm 통해서
--부서번호가 10인 사원은 totalsum에 급여 정보만 넣으세요
--데이터 처리
--
insert into CURSOR_TABLE(empno,ename,sal,totalsum)
select empno , ename , sal , sal+nvl(comm,0)
from emp where deptno=20;
select * from CURSOR_TABLE;
commit;
delete from cursor_table;
commit;
select * from cursor_table;
DECLARE
result number := 0;
CURSOR emp_curr IS select empno, ename, sal,deptno,comm from emp;
BEGIN
FOR vemp IN emp_curr --row 단위로 emp_record 변수에 할당
LOOP
EXIT WHEN emp_curr%NOTFOUND;
IF(vemp.deptno = 20) THEN
result := vemp.sal+nvl(vemp.comm,0);
insert into cursor_table(empno, ename, sal,deptno,comm,totalsum)
values (vemp.empno,vemp.ename, vemp.sal,vemp.deptno,vemp.comm,result);
ELSIF(vemp.deptno = 10) THEN
result := vemp.sal;
insert into cursor_table(empno, ename, sal,deptno,comm,totalsum)
values (vemp.empno,vemp.ename, vemp.sal,vemp.deptno,vemp.comm,result);
ELSE
DBMS_OUTPUT.PUT_LINE('ETC');
END IF;
END LOOP;
END;
rollback;
commit;
select * from cursor_table order by deptno;
--PL-SQL 트랜잭션 및 예외 처리하기
DECLARE
v_ename emp.ename%TYPE := '&p_ename';
v_err_code NUMBER;
v_err_msg VARCHAR2(255);
BEGIN
DELETE emp WHERE ename = v_ename;
IF SQL%NOTFOUND THEN
RAISE_APPLICATION_ERROR(-20002,'my no data found'); --사용자가 예외 만들기 (강제 예외 던지기)
END IF;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
v_err_code := SQLCODE;
v_err_msg := SQLERRM;
DBMS_OUTPUT.PUT_LINE('에러 번호 : ' || TO_CHAR(v_err_code));
DBMS_OUTPUT.PUT_LINE('에러 내용 : ' || v_err_msg);
END;
rollback;
select* from emp where ename ='JONES';
select * from c_dept;
select *from SYS.USER_CONSTRAINTS where TABLE_NAME ='C_DEPT';
select * from c_emp;
select *from SYS.USER_CONSTRAINTS where TABLE_NAME ='C_EMP';
select * from emp where ename ='KING';
delete from c_dept where deptno=300;
commit;
delete from emp where ename='aaa';
--------------------------------------------------------------------------------
--지금까지 만들었는 작업이 영속적으로 저장 되지 않았다
--crerate table , create view
--내가 위에서 만든 [커서]를 영속적으로 저장 (객체)
--객체 형태로 저장 해놓으면 그 다음번에 코딩하지 않고 [불러 사용]
--Oracle : subprogram(procedure)
--Ms-sql : procedure
--자주 사용되는 쿼리를 모듈화 시켜서 객체로 저장하고
--필요한 시점에 불러(호출) 해서 사용하겠다
--sp
--usp
create or replace procedure usp_emplist --create or replace (생성 가능 , 수정 가능)
is
BEGIN
update emp
set job = 'TTT'
where deptno=30;
END;
--실행방법
execute usp_emplist; //호출한다
select * from emp where deptno=30;
rollback;
--procedure 장점
--기존 : APP(emp.java > select .... 구문) ->네트워크 > DB연결 > selet... > DB에
--지금 : APP(emp.java > usp_emplist 구문) ->네트워크 > DB연결 > usp_emplist > DB에
--1.장점 : 네트워크 트래픽 감소(시간 단축)
--2.장점 : 보안 (네트워크 상에서 ...보안 요소)해결
--procedure
--parameter 사용가능
--종류 : INPUT , OUTPUT
create or replace procedure usp_update_emp
(vempno emp.empno%TYPE)--input파라미터자리
is
BEGIN
update emp
set sal = 0
where empno = vempno;
END;
--실행방법
--줄여쓸수 있다
exec usp_update_emp(7788); --파라미터 받을 수있다.
select * from emp where empno = 7788;
rollback;
--------------------------------------------------------------------------------
create or replace procedure usp_getemplist
(vempno emp.empno%TYPE)
is
--내부에서 사용하는 변수
vname emp.ename%TYPE;
vsal emp.sal%TYPE;
BEGIN
select ename, sal
into vname , vsal
from emp
where empno=vempno;
DBMS_OUTPUT.put_line('이름은 : ' || vname);
DBMS_OUTPUT.put_line('급여는 : ' || vsal);
END;
exec usp_getemplist(7902);
exec usp_getemplist(7788);
--------------------------------------------------------------------------------
-- procedure 는 parameter 종류 2가지
--1. input paramter : 사용시 반드시 입력 (IN : 생략하는 default)
--2. output parmater : 사용시 입력값을 받지 않아요 (OUT)
create or replace procedure app_get_emplist
(
vempno IN emp.empno%TYPE,
vename OUT emp.ename%TYPE,
vsal OUT emp.sal%TYPE
)
is
BEGIN
select ename, sal
into vename , vsal
from emp
where empno=vempno;
END;
--오라클 실행 테스트
DECLARE
out_ename emp.ename%TYPE;
out_sal emp.sal%TYPE;
BEGIN
app_get_emplist(7902,out_ename,out_sal);
--output파라미터자리의 변수들은 반환된 값들을 받아옴
DBMS_OUTPUT.put_line('출력값 : ' || out_ename || '-' || out_sal);
END;
CREATE OR REPLACE PROCEDURE usp_EmpList
(
p_sal IN number,
p_cursor OUT SYS_REFCURSOR --APP 사용하기 위한 타입 (한건이상의 데이터 select 내부적으로 cursor 사용
)
IS
BEGIN
OPEN p_cursor
FOR SELECT empno, ename, sal FROM EMP WHERE sal > p_sal;
END;
create table usp_emp
as
select * from emp;
alter table usp_emp
add constraint pk_usp_emp_empno primary key(empno);
select * from SYS.USER_CONSTRAINTS where table_name='USP_EMP';
CREATE OR REPLACE PROCEDURE usp_insert_emp
(
vempno IN emp.empno%TYPE,
vename IN emp.ename%TYPE,
vjob IN emp.job%TYPE,
p_outmsg OUT VARCHAR2
)
IS
BEGIN
INSERT INTO USP_EMP(empno , ename, job) VALUES(vempno ,vename , vjob);
COMMIT;
p_outmsg := 'success';
EXCEPTION WHEN OTHERS THEN
p_outmsg := SQLERRM;
ROLLBACK;
END;
DECLARE
out_msg varchar2(200);
BEGIN
usp_insert_emp(9999,'홍길동','IT',out_msg);
DBMS_OUTPUT.put_line('출력값 : ' || out_msg);
END;0719grouping sets, with, partition by
--GROUPING SETS 는 여러 그룹핑 쿼리를 UNION ALL 한 것과 같은 결과를 만들수 있어
--조금 더 유연하게 소계, 합계를 집계할 수있다.
select job ,deptno, count(*) cnt
from emp
group by grouping sets(job,deptno);
--job별로 몇명있는지 일단 출력,deptno별로 몇명있는지 출력 각각 그룹지어서 나타냄
select job ,deptno, count(*) cnt
from emp
group by grouping sets((job,deptno));
--이렇게 묶어 쓰면 걍 group by 에 job,deptno를 쓴것과 같음
select job ,deptno, count(*) cnt
from emp
group by job,deptno
order by job;
--p203까지함
--------------------------------------------------------------------
--with 절
/*
WITH 절은 임시 테이블 또는 가상 테이블이라고 생각하면 된다.
반복되는 서브쿼리 블록을 하나의 WITH 절 블록으로 만들어서 사용하거나,
긴 문장의 서브 쿼리를 WITH 절로 만들어서 SELECT 문의 가독성을 높일 수 있다.
*/
select *
from (
SELECT deptno , SUM(sal) AS sal
FROM emp
GROUP BY deptno
) a ;--inline view
with emp_w as
(
SELECT deptno , SUM(sal) AS sal
FROM emp
GROUP BY deptno
)
select *
from emp_w a join dept d
on a.deptno=d.deptno;
WITH emp AS (
SELECT '7698' empno, 'BLAKE' ename, 'MANAGER' job, '30' deptno FROM dual UNION ALL
SELECT '7782' empno, 'CLARK' ename, 'MANAGER' job, '10' deptno FROM dual UNION ALL
SELECT '7566' empno, 'JONES' ename, 'MANAGER' job, '20' deptno FROM dual
),
dept_history as (
SELECT '7566' empno, '1981-04-02' sdate, '1991-04-01' edate, '10' deptno FROM dual UNION ALL
SELECT '7566' empno, '1991-04-02' sdate, '2001-04-01' edate, '30' deptno FROM dual UNION ALL
SELECT '7782' empno, '1981-06-09' sdate, '1991-06-08' edate, '40' deptno FROM dual
)
SELECT a.empno
, a.ename
, a.deptno
FROM emp a
WHERE a.job = 'MANAGER'
AND EXISTS (SELECT 1
FROM dept_history aa
WHERE aa.empno = a.empno);--상관 서브쿼리로써 from절의 테이블과 join해서들어감
WITH emp AS (
SELECT '7698' empno, 'BLAKE' ename, 'MANAGER' job, '30' deptno FROM dual UNION ALL
SELECT '7782' empno, 'CLARK' ename, 'MANAGER' job, '10' deptno FROM dual UNION ALL
SELECT '7566' empno, 'JONES' ename, 'MANAGER' job, '20' deptno FROM dual
),
dept_history as (
SELECT '7566' empno, '1981-04-02' sdate, '1991-04-01' edate, '10' deptno FROM dual UNION ALL
SELECT '7566' empno, '1991-04-02' sdate, '2001-04-01' edate, '30' deptno FROM dual UNION ALL
SELECT '7782' empno, '1981-06-09' sdate, '1991-06-08' edate, '40' deptno FROM dual
)
SELECT a.empno , a.ename , a.deptno
FROM emp a join dept_history b
on a.empno = b.empno
WHERE a.job = 'MANAGER';
--테이블이 없을 때
--1.인라인뷰를 사용
--2.create view로 생성해서 사용
--3. with절을 사용
-------------------------------------------------------------------
--소계, 통계
--rollup,cube,grouping sets
--가상테이블(실제 물리적인 테이블이 없는 경우 :join 사용시)
--서브 쿼리(inlineview), create view 객체 등록, with 절
---------------------------------------------------------------------
--순위 함수
--rownum(select 한 결과에 순번)
--rank
--dense rank
select * from emp;
--rank()함수는 기준이 필요하다 -> over()에는정렬이들어간다
select ename, sal, rank() over(order by sal desc) as 순위,
dense_rank() over(order by sal desc) as 순위2
from emp
order by sal desc;
/*
--같은 점수 rank dense_rank
KING 5000 1 1
FORD 3000 2 2
SCOTT 3000 2 2
JONES 2975 4 3 --차이점
rank는 동률 다음에 그 숫자들 포함해서 숫자가 나옴
dense_rank 동률다음에 그다음숫자가 바로 나옴
--만약 동률이 나올때를 대비하여 동률을 줄일 수 있는 기준을 만들어야한다.
--회사) 포인트 부여 -> 3사람에게만 -> 6명이 동률 -> 가입순으로 하자! -> 5명이 동률, 나이순, 중복값을 줄여감
*/
select ename, sal, comm, rank() over(order by sal desc, comm desc) as 순위
from emp
order by sal desc;
--조건 (그룹안에서 순위정하기)
--A조(1,2,3), B조 (1,2,3) 등등
--모아 놓은 것 (그룹을 잡는다)partition by
select job ,ename,sal, comm,
rank() over(partition by job order by sal desc, comm desc) as 그룹순위
from emp
order by job asc, sal desc , comm desc;
---------------------------------------------------------------------
--집계함수의 단점
--select 절에 집계함수 이외에 나머지 컬럼은 반드시 group by 명시
--select job, sum(sal)
--사번, 사원이름 보고 싶다 select empno, ename, job, sum(sal)
--해결 : in line view(조인)
-- create view (조인)
-- with 절 (조인)
select job, sum(sal)
from emp
group by job
order by job;
--사번, 이름
--over(partition by)를 통해서 해결가능
--장점 : group by 하지 않아도 그룹을 만들수 있다.
select empno, ename, job, sum(sal) over(partition by job)
--job을 그룹한 것에 맞춰서 집계함 결국 여러번씩 출력됌
from emp
order by job;실습문제
--문제)
--부서번호를 입력값으로 받아서 부서별 평균급여와, 인원을 출력하는 프로시져를 만드세요
--그리고 JDBC 를 사용해서 출력하세요
drop procedure munje;
create or replace procedure munje
(
vdeptno IN emp.deptno%TYPE,
v_cursor OUT SYS_REFCURSOR
)
is
begin
open v_cursor
for select deptno, avg(sal) avgsal, count(*) cnt
from emp
where deptno = vdeptno
group by deptno;
end;
-------------------------------------------------------------
drop table tb1;
create table tb1
(
empno number,
ename varchar(50),
job varchar(50),
sal number,
time number,
timesal number,
eat number,
total number
);
insert into tb1 values(10,'홍길동','정규직',120,null,null,null,null);
insert into tb1 values(11,'김유신','시간직',null,10,100,null,null);
insert into tb1 values(12,'이순신','일용직',null,null,120,10,null);
commit;
select * from tb1;
drop procedure munje2;
create or replace procedure munje2 is
vtotal number;
CURSor c_Array is select * from tb1;
begin
for c in c_Array
LOOP
EXIT WHEN c_Array%NOTFOUND; --더이상 row 가 없으면 탈출
if(c.job='정규직')then
vtotal:= c.sal;
update tb1 set total=vtotal where empno= c.empno;
elsif(c.job='시간직')then
vtotal:= c.time*c.timesal;
update tb1 set total=vtotal where empno= c.empno;
elsif(c.job='일용직') then
vtotal:= c.timesal+c.eat;
update tb1 set total=vtotal where empno= c.empno;
end if;
END LOOP;
end;
새로운 접속창하나 다띄우기
ALT + F10
'오라클 데이터베이스' 카테고리의 다른 글
| SQL 10일차(0721) servlet 하네 (0) | 2023.07.21 |
|---|---|
| SQL 9일차(0720) (0) | 2023.07.20 |
| sQL7일차(0718) (0) | 2023.07.18 |
| sQL 6일차(0717) (0) | 2023.07.17 |
| SQL 2일차(0711) (0) | 2023.07.16 |
