일 | 월 | 화 | 수 | 목 | 금 | 토 |
---|---|---|---|---|---|---|
1 | 2 | |||||
3 | 4 | 5 | 6 | 7 | 8 | 9 |
10 | 11 | 12 | 13 | 14 | 15 | 16 |
17 | 18 | 19 | 20 | 21 | 22 | 23 |
24 | 25 | 26 | 27 | 28 | 29 | 30 |
- Oracle DB
- github
- Java
- tomcat
- 모조칼럼
- git
- Oracle SQL
- HTML
- 제약조건
- JavaScript
- 과정평가형
- 이클립스
- 부트스트랩
- JSP
- 답변형 게시판
- group by
- sql developer
- distinct
- oracle
- HTTP Status 404
- ||
- alias
- CRUD
- 한글 인코딩
- jQuery
- SQL
- rownum
- HTTP Status 500
- 성적프로그램
- Bootstrap
초급의 끄적거림
[SQL] PL/SQL 프로시저 (형식/조건문/반복문), %rowtype, %type 본문
[앞으로의 진도]
PL/SQL 프로시저, 함수
트리거, 백업과 복원
[PL/SQL 프로시저]
⊙ Procedural Language extension to SQL
⊙ 프로그래밍 언어의 특성을 수용한, SQL의 확장기능
⊙ SQL문장에서 변수 정의, 조건처리(if), 반복처리 (loop, while, for) 등을 지원하며 오라클 자체에 내장되어 있는 Procedure Language 이다
[PL/SQL 형식]
create or repalce procedure 프로시저명 (매개변수)
is
내부변수선언;
begin
명령어; (세미콜론으로 종결필요)
end;
/
[PL/SQK 기본문법]
⊙ 콘솔창 출력하기 위한 명령어
set serveroutput on;
1) 변수선언 및 대입, 결합연산자 사용 |
declare a number := 3; b number := 5; begin dbms_output.put_line('*실행결과*'); dbms_output.put_line(a); dbms_output.put_line(b); dbms_output.put_line(a+b);
-- 결합연산자 || dbms_output.put_line(a || '+' || b || '=' || (a+b)); end; / |
2) 조건문
2-1) 조건문1 | 2-2) 조건문2 |
declare uname varchar2(50) := '무궁화'; begin if aver>=90 then grade := 'A';
-- 출력 dbms_output.put_line('*실행결과*'); |
declare --성적프로그램 (입력자료는 테이블에서 가져옴) uname varchar2(50) := '무궁화'; kor number := 100; eng number := 95; mat number := 80; aver number := (kor+eng+mat)/3; grade varchar2(50) := NULL; begin if aver between 90 and 100 then grade := 'A'; elsif aver between 80 and 89 then grade := 'B'; elsif aver between 70 and 79 then grade := 'C'; elsif aver between 60 and 69 then grade := 'D'; else grade := 'F'; end if; dbms_output.put_line('*실행결과*'); dbms_output.put_line('국어 :' || kor); dbms_output.put_line('영어 :' || eng); dbms_output.put_line('수학 :' || mat); dbms_output.put_line('평균 :' || aver); dbms_output.put_line('학점 :' || grade); end; / |
3) 반복문 (결과동일)
3-1) 반복문 |
declare dan number :=4; i number default 0; begin loop i := i+1; dbms_output.put_line(dan || '*' || i || '=' || (dan*i)); exit when i=9; -- i값이 9이면 반복문 빠져나옴 end loop; end; / |
3-2) 반복문2 | 3-3) 반복문3 |
declare dan number := 4; i number default 0; begin for i in 1..9 loop --for 변수 in 시작값..종료값 dbms_output.put_line(dan || '*' || i || '=' || (dan*i)); end loop; end; / |
declare dan number :=4; i number default 0; begin while i <10 loop i :=i+1; exit when i=10; dbms_output.put_line(dan || '*' || i || '=' || (dan*i)); end loop; end; / |
[성적테이블 생성]
1) sno=3 인 행을 가져오기
- where 문 사용 |
select * from sungjuk where sno=3; |
- 프로시저 사용 |
declare v_sno number; 면 헷갈릴 수 있기 때문에 차별화를 둠 (v_) begin --SQL문 작성 v_kor, v_eng, v_mat, v_addr, v_wdate --출력 end; / |
문2) 주소를 한글로 출력하시오
declare
if v_addr='Seoul' then v_juso:='서울'; --출력 end; / |
[PL/SQL 참조변수]
⊙ %type
- 테이블에서 한 개 칼럼의 데이터타입 및 사이즈를 참조한다
- 형식) 변수명 테이블명.칼럼명%type
⊙ %rowtype
- 테이블의 row 타입과 같다는 의미
- 하나 이상의 데이터값을 갖는 데이터 타입으로 배열과 비슷한 역할을 함 (= 테이블에 있는 전체 칼럼의 자료형을 모두 사용)
- 형식) 변수명 테이블명%rowtype
⊙ 데이터베이스 칼럼들의 수나 데이터타입을 알지 못할 때 편리
⊙ 테이블의 데이터칼럼의 데이터 타입이 변경 될 경우 프로그램을 재수정할 필요가 없다
1) %type형
declare v_sno sungjuk.sno%type; --굳이 테이블에서 자료명을 확인해서 적을 수고가 적어짐 v_uname sungjuk.uname%type; v_kor sungjuk.kor%type; v_eng sungjuk.eng%type; v_mat sungjuk.mat%type; v_addr sungjuk.addr%type; v_wdate sungjuk.wdate%type; begin select sno, uname, kor, eng, mat, addr, wdate into v_sno, v_uname, v_kor, v_eng, v_mat, v_addr, v_wdate from sungjuk where sno=3; dbms_output.put_line('*실행결과*'); dbms_output.put_line('번호 :' || v_sno); dbms_output.put_line('이름 :' || v_uname); dbms_output.put_line('국어 :' || v_kor); dbms_output.put_line('영어 :' || v_eng); dbms_output.put_line('수학 :' || v_mat); dbms_output.put_line('주소 :' || v_addr); dbms_output.put_line('작성일 :' || v_wdate); end; / |
2) %rowtype형 (1번과 동일한 결과)
declare sj sungjuk%rowtype; --sj 라는 변수에 sungjuk%rowtype begin select * into sj from sungjuk where sno=3; dbms_output.put_line('*실행결과*'); end; / |
[커서를 이용해서 복수행 처리]
⊙ fetch문
- 오픈된 커서로부터 한 행을 인출한다
- 형식) fetch 커서명 into 변수명
1) sno>=5 레코드 조회 |
select * from sungjuk where sno>=5; |
2) 프로시저를 이용해서 sno>=5 레코드 조회 |
declare v_cursor sys_refcursor; --커서를 담는 변수선언 및 자료형 rec sungjuk%rowtype; --한 행을 담을 수 있는 변수선언 begin open v_cursor for --커서는 아래 있는 select select * from sungjuk where sno>=5; loop fetch v_cursor into rec; --한 행 인출해서 rec에 대입 exit when v_cursor%notfound; --자료가 없으면 빠져나감 dbms_output.put_line(rec.sno); end; / |
[PL/SQL 생성]
1) test 프로시저 생성 |
create or replace procedure test is begin dbms_output.put_line('테스트'); end; |
2) test 프로시저 호출
execute test; |
3) test 프로시저 삭제3) test 프로시저 삭제
drop procedure test; |
[성적프로시저]
⊙ Create/ Read / Update / Delete 관련한 프로시저 생성
1) 행추가 프로시저 (Create)
- 매개변수 (parameter) - in 입력변수 (무언가를 받아들이겠다는 의미) |
create or replace procedure sungjukInsert ( v_uname in sungjuk.uname%type ) is begin insert into sungjuk(sno,uname,kor,eng,mat,aver,addr)
|
1-1) 행추가 테스트
execute sungjukInsert('손흥민', 100, 100, 100, 'Seoul'); |
⊙ 결과 확인
2) 행수정 프로시저 (Update)
create or replace rprcedure sungjukUpdate( v_uname in sungjuk.uname%type ) is begin update sungjuk set uname=v_uname, kor=v_kor, eng=v_eng, mat=v_mat, aver=(v_kor+v_eng+v_mat)/3, addr=v_addr where sno=v_sno; commit; end;
execute sungjukUpdate('김연아', 100, 100, 100, 'Suwon', 3); --sno가 3인 것을 수정하기 위해서 3을 설정한 것 |
3) 행삭제 프로시저 (Delete)
create or replace procedure sungjukDelete( v_sno in sungjuk.sno%type ) is begin delete from sungjuk where sno=v_sno; commit; end;
execute sungjukDelete(3);
select * from sungjuk; |
4) 상세보기 (Read)
- 자바에서 확인하기 위해서 컴파일만 해두기
create or replace procedure sungjukRead ( --out 출력매개변수 v_cursor out sys_refcursor --가지고 나오기 위해서 out 그동안 써오던 in말고 out 사용, 굳이 없어도 문제는 없을 것 ,v_sno sungjuk.sno%type ) is begin open v_curesor for select * from sungjuk where sno=v_sno; --close v_cursor; 커서반납, 지금 여기서 close 하면 자바에서 움직일 수 없기 때문에 주석처리 |
5) 목록 (Read)
- 자바에서 확인하기 위해서 컴파일만 해두기
create or replace procedure sungjukList ( v_cursor out sys_refcursor ) is begin open v_cursor for select * from sungjuk order by sno desc; commit; end; |
6) 레코드 개수 프로시저
- 자바에서 확인하기 위해서 컴파일만 해두기
create or replace procedure sungjukCount ( v_cursor out sys_refcursor ) is begin open v_curesor for select nvl(count(*), 0) cnt from sungjuk; --clos v_cursor commit; end; |
7) 검색 프로시저
- 자바에서 확인하기 위해서 컴파일만 해두기
- 이름에 '나' 라는 글자가 들어가 있는 사람 찾기 where uname like '%나%';
- 이름 또는 주소에 알파벳 'o'가 있는 검색 where uname like '%o%' or addr like '%o%';
- 1이면 이름에서 검색 2이면 이름 또는 주소에서 검색 나머지는 검색하지 않음 |
create or replace porcedure sungjukSearch ( v_cursor out sys_refcursor ,v_code number , v_keyword varchar2 ) is begin if v_code=1 hen open v_cursor for select * from sungjuk where uname like '%' || v_keyworde || '%'; elsif v_code=2 then open v_cursor for select * from sungjuk where uname like '%' || v_keyword || '%' or addr like '%' || v_keyworde || '%' order by sno desc; else open v_cursor for select * from sungjuk order by sno desc; end if; commit; end;
|
8) 페이징 프로시저
- 자바에서 확인하기 위해서 컴파일만 해두기
<rownum이 6이상 10이하인 것을 골라냄> select sno, uname, addr, rownum from sungjuk order by sno desc; → 서브쿼리 형태를 한번 만들어서 사용할 것 |
<6과 10을 변수처리 해서 넣기 : v_sno> create or replace procedure sungjukPaging |
'DB > Oracle' 카테고리의 다른 글
[SQL] 백업 및 복원 (0) | 2019.08.07 |
---|---|
[SQL] Function, Trigger (펑션, 트리거) (0) | 2019.08.06 |
[SQL] Oracle XE 11g 설치, cmd에서 사용자 계정 생성/권한부여 (0) | 2019.08.01 |
[SQL] CSV 변환 (외부 데이터를 이용하여 테이블 생성) (0) | 2019.07.31 |
[SQL] 트랜잭션 (Transaction) (0) | 2019.07.30 |