본문 바로가기

빅데이터과정/PL/SQL

#16_140704_PL_SQL_종속성 # 종속성 l 참조한 객체에 따라서 프로시져, 뷰 등등이 종속성에 영향을 받는다l 객제를 변형한 경우에 유형에 따라서 동작할 수도 있고 동작안할 수도 있다 l failure 를 최소화 하는 방법1. %rowtype 속성으로 레코드 선언2. %type 속성으로 변수 선언3. select * 로 query를 해라 4. insert 문을 사용하여 컬럼 리스트를 입력해라 문제198. 직업이 SALESMAN, ANALYST 인 사원들의 이름과 월급과 직업과 부서번호를 출력하는 VIEW를 생성하고 이름을 입력하면 이름과 월급을 출력하는 프로시져를 만드시오 CREATE VIEW emp934ASSELECT ename, sal, job, deptnoFROM EMPWHERE job IN ('SALESMAN','ANALYS.. 더보기
#15_140703_PL_SQL_암호화 # 암호화 emp_info.txt # 위 텍스트 파일에 패키지와 패키지 바디를 수행한다 1. EMP_INFO 패키지의 명세 코드를 아래의 파일 이름으로 저장ed emp_info_package.sql 2. EMP_INFO 패키지의 명세 코드를 아래의 파일 이름으로 저장ed emp_info_package_body.sql 3. emp_info_package_body.sql 를 emp_info_package.plb 로 암호화 시킴C:\Users\실습실> wrap iname=emp_info_package_body.sql oname= emp_info_package.plb 4. scott 계정으로 다시 접속C:\Users\실습실> sqlplus scott/tiger 5. 암호와된 body 스크립트를 열어보시오ed e.. 더보기
#15_140703_PACKAGE_OVERLOAD # OVERLOAD 4장. 패키지의 오버로딩- 아래의 구현을 가능하게 하려면 패키지의 오버로딩 기능을 이용해야한다- exec sal_rank(1,3); -- 대기업 연봉순위 1위부터 3위까지 나오는 프로시져- exec sal_rank(1); -- 매개변수 하나만 입력하면 1위만 나오는 프로시져 - 같은 이름의 프로시져 이름을 가지면서 동시에 다른 매개변수를 갖도록 하는 것을 의미한다 문제185. 아래의 명령어가 가능하도록 아래의 소스를 고치시오exec sal_pack.sal_rank(1,5);exec sal_pack.sal_rank(1); CREATE OR REPLACE PACKAGE sal_pack AS PROCEDURE SAL_RANK(p_rank NUMBER, p_rank2 number);END s.. 더보기
#15_140703_PL_SQL_PACKAGE # PACKAGEl 패키지를 사용하는 이유1. 정보(코드)를 숨길 수 있다2. 쉬운 유지보수 관리 – 비슷한 업무의 프로시져들을 하나의 소스로 묶어서 관리3. 전페 패키지가 메모리에 로드되어 응용프로그램 성능 향상4. 오버로드 : 동일한 이름의 다중 서브 프로그램 l 패키지 구조- 명세 : body에서 구현할 프로시져나 전역변수의 이름을 선언(책의 목차)- 몸체 : 실제 구현 코드(프로시져, 함수)(책의 내용) l Data Dictionalray 이용해서 패키지 보기- SELECT text FROM user_source WHERE name='패키지 명칭' AND TYPE='패키지 타입';- SELECT text FROM user_source WHERE name='SAL_PACK' AND TYPE='PAC.. 더보기
#14_140702_PL_SQL_TRIGGER # TRIGGER l trigger(반응이나 사건을 유발한 계기) : 오라클 데이터베이스는 지정된 조건이 발생할 때 트리거를 자동으로 실행l 트리거의 종류1. DML 트리거(delete, insert, update)2. DDL 트리거(create, alter, drop)3. DB 작업에 대한 트리거(log on, log off, start up. shutdown) l 트리거 확인 : SELECT * FROM user_triggers; 사원170. 사원 테이블에 토요일과 일요일에 데이터 입력작업을 못하게 하도록 트리거를 생성하시오 create or replace trigger secure_empbefore insert on empbegin if (to_char(sysdate,'DY') in ('토','일'.. 더보기
#14_140702_PL_SQL_RETURNING #RETURNING - returning 은 DML문(Iinsert, update, delete)을 이용하게 될 경우 select 절을 이용해서 변수에 넣어주는 과정이 필요한데 이런 과정을 간소화시키기 위해 DML문 끝에 returning 구문을 추가한다. 문제169. 사원번호를 입력하고 프로시져를 수행하면 해당 사원의 월급이 2배로 갱신되는 프로시져를 생성하시오. 그런데 프로시져가 수행될 때 갱신된 그 사원의 이름과 월급이 출력되게 하시오(p7-26쪽에 나오는 returning 절로 구현하시오) CREATE OR REPLACE PROCEDURE pro1(p_empno emp.empno%type)IS v_ename emp.ename%TYPE; v_sal EMP.sal%TYPE; BEGIN UPDATE E.. 더보기
#14_140702_PL_SQL_DETERMINISTIC # DETERMINISTIC 문제168. 함수 생성시 deterministic 절을 사용하는 이유 SELECT mpno, ename, sal, func12(deptno)FROM EMPwher deptno = 20; l func12 : 부서번호를 입력받고 해당 부서번호의 평균 월급이 출력되는 함수l deterministic을 이용하면 func12(20) 의 결과가 2934 라면 함수를 수행하지 않고 무조건2934 값을 출력하자l deterministic을 이용하는 이유는 DB에 수억건의 데이터가 있게 되면 단순 부서번호를 받고 평균월급을 출력하는 단순 함수라도 엄청난 시간이 소요되므로 위의 예와 같이 20번 부서번호의 결과가 2934로 고정되 있으면 하나하나 수행하지 말고 고정 출력으로 간주하는 것이다 더보기
#14_140702_PL_SQL_PARALLEL ENABLE # PARALLEL ENABLE 문제167. 함수 생성시 parallel enable 힌트를 사용하는 이유가 무엇인가 SELECT /*+ full(emp) parallel(emp,8) */ ename, sal, deptno, func12(empno)FROM EMPWHERE ename='SCOTT'; l func12 사용자 정의 함수 : 사원번호를 입력하면 해당 사원의 부서위치를 출력되게 하는 함수l parallel enable 은 함수도 병렬도 처리하게 하고 싶다면 꼭 함수 생성시 써야한다 더보기
#14_140702_PL_SQL_PRAGMA AUTOMONOUS TRANSACTION # PRAGMA AUTOMONOUS TRANSACTION l transaction 이란- 하나의 일련의 DML 문의 집합(insert, update…)- DDL이나 DCL은 하나하나가 transaction l pragma automonous_transaction- 프로시져내에 DML 문들의 집합인 트랜잭션을 별도의 하나의 트랜잭선으로 구분하겠다는 뜻 - 주 트랜잭션과 독립트랜잭션은 commit과 rollback 이 동시에 적용 되지 않는다 l 독립 트랜잭션에서 5번의 commit 을 하게 되면 본래는 1번의 insert문은 rollback 이 불가능하다l 이를 해결하기 위해 pragma autonomous_transaction을 이용해서 독립된 세션으로 권한을 주면 가능하다 문제166. pragma au.. 더보기
#14_140702_PL_SQL_AUTHID CURRETN USER # AUTHID CURRETN USER 문제165. 아래의 사원번호를 입력받고 이름을 출력하는 프로시져를 authid current user 옵션을 써서 다시 생성하시오 DROP USER jack CASCADE; CREATE USER jackIDENTIFIED BY tiger; GRANT CONNECT, RESOURCE TO jack; CREATE OR REPLACE PROCEDURE proc_grant(p_empno number)IS v_ename emp.ename%TYPE;BEGIN SELECT ename INTO v_ename FROM scott.EMP WHERE empno = p_empno; dbms_output.put_line(v_ename);END;/ # 각각의 유저 창에서 수행soctt 창에.. 더보기
#13_140701_PL_SQL_REF CURSOR # REF CURSOR l ref cursor : 동적 SQL에서 커서를 사용하려면 ref cursor를 사용해야한다.l 일반 커서와 ref cursor 의 차이점- 우리가 알고있는 커서선언은 declare 절에서 수행하는데 ref cursor 는 커서선언이 실행절(begin)에서 이루어진다l 일반커서- 수행 순서 : cursor 선언 >> cursor open >> cursor에 있는 내용 fetch >> cursor close- 일반적인 커서 선언방법cursor emp_cursor isselect empno,ename,salfrom emp;- 아래의 출력결과를 emp_cursor 라는 메모리에 올려놓고 cursor를 메모리에 오픈하고 하나씩 하나씩 fetch 함 - for loop 문은 open, f.. 더보기
#13_140701_PL_SQL_동적 SQL # 동적 SQLl 동적 SQL을 사용하면 할 수 있는 작업- 프로시져 생성할 떄 select 문의 into 절 없이 사용가능하다- 프로시져 생성시 DDL 문이나 DCL 문을 포함시킬 수 있다.(DDL : crteate, alter, drop., truncate, rename. DCL : grant, revoke)l 동적 SQL을 사용하는 방법 2가지- execute inmmediate 절 사용(가장 많이 활용) - DBMS_SQL 패키지를 이용하는 방법 문제147. 부서번를 입력하여 토탈월급, 최대월급, 최소월급을 출력하는 아래의 프로시져를 동적SQL로 수정해서 생성하고 실행하시오 CREATE OR REPLACE PROCEDURE pro146(p_deptno IN number)IS v_sumsal emp.. 더보기
#12_140630_PL_SQL_FUNCTION # FUNCTION함수 수행하는 방법 - execute dbms_output.put_line(get_mgr(7788)); 문제127. 부서번호를 입력하고 프로시져를 실행하면 해당 부서번호의 평균월급이 출력되는 프로시져를 함수로 변경하시오 CREATE OR REPLACE PROCEDURE avgsal(p_deptno emp.deptno%type)IS v_avgsal emp.sal%TYPE;BEGIN SELECT AVG(sal) INTO v_avgsal FROM EMP WHERE deptno = p_deptno; dbms_output.put_line('평균월급 ' || v_avgsal);END;/ CREATE OR REPLACE FUNCTION GET_AVGSAL(p_deptno IN emp.deptno%t.. 더보기
#10_140627_PL_SQL_WHERE CURRENT OF # WHERE CURRENT OFl where current of emp_cursor;내가 방금 fetch 해온 그 행을 가리킨다l 실행절에서 where current of 절을 사용하려면 커서 선언시 for update 절을 사용해야 한다l select … for update 를 하면서 select 해오는 테이블의 그 행에 lock이 걸림declarecursor emp_cursor isselect ename, rank() over(order by sal desc) as "순위"from empfor update; update empset rnk=emp_record.순위where current of emp_cursor l 수행한 후에 commit; 을 해줘야 한다 문제76. where current of .. 더보기
#11_140627_PL_SQL_예외처리 # 예외처리l 예외처리가 필요한 이유- 오라클 에러메세지를 사용자의 눈높이에 맞춰서 출력할 수 있다.- 프로그램을 수행되게 할 떄 몇가지 오류가 있는 data 때문에 전체 프로그래밍이 실패하는 현상을 방지하기 위해서 필요하다 l 예외의 종류- 미리 정의 해놓은 예외처리- 미리 정의하지 않은 예외처리- 사용자정의 예외처리 l 미리 정의하지 않은 예외처리 - 우리회사만의 특별한 오라클 에러에 대한 예외처리를 내가 프로그램상에서 직접 구현 문제86. 부서번호를 입력하면 해당 부서번호에 속한 사원들의 토탈월급이 출력되게 하는 PL/SQL 을 작성하는데 없는 부서번호를 넣으면 “해당 부서번호는 없습니다” 라는 메시지가 출력되게 하시오. set serveroutput onaccept p_deptno prompt '.. 더보기
#10_140626_PL_SQL_CURSOR # CURSORl 커서 : 수행하는 SQL 문의 결과를 처리하기 위한 메모리 영역 - 암시적 커서 : SQL%rowcount. 메모리에 미리 올려놓고 사용자가 데이터를 업데이트하면 알아서 바꿔줌 명시적 커서 : 커서선언 -> 커서오픈 -> 커서패치 커서 close 문제61. 직업을 물어보게 하고 직업을 입력하면 해당 사원의 사원이름, 월급과 부서위치가 출력되게하시오 set serveroutput onaccept p_job prompt '직업를 입력하시오! 'DECLARE CURSOR emp_cursor IS SELECT e.ename, e.sal, d.loc FROM EMP e, DEPT d WHERE e.job = UPPER('&p_job') AND e.deptno = d.deptno; v_ename .. 더보기
#10_140626_PL_SQL_중첩 테이블 # 중첩 테이블 문제57. (p6-23) 중첩 테이블을 사용해서 dept 테이블에서 모든 부서번호와 부서위치를 출력될 수 있도록 하시오 SET serveroutput ONDECLARE TYPE dept_table_type IS TABLE OF dept%ROWTYPE INDEX BY PLS_INTEGER; dept_table dept_table_type; v_count NUMBER(10) := 10; BEGIN FOR i IN 1..4 LOOP SELECT * INTO dept_table(i) FROM DEPT WHERE deptno = v_count; v_count := v_count + 10; END LOOP; FOR i IN DEPT_TABLE.FIRST .. DEPT_TABLE.LAST LOOP d.. 더보기
#10_140626_PL_SQL_RECORD # RECORD l 조합 데이터 타입의 2가지 종류- 레코드(record)- 컬렉션(collection) 문제51. 사원번호를 물어보게 하고 사원번호를 입력하면 해당 사원의 모든 컬럼의 정보가 emp745 테이블에 입력되게 하는 PL/SQL을 작성하시오 accept p_empno prompt '사원번호를 입력하시오! 'declare v_empno emp.empno%TYPE := &p_empno; v_emp emp%ROWTYPE;BEGIN SELECT * INTO v_emp FROM EMP WHERE empno=v_empno;INSERT INTO emp745 VALUES v_emp;end; / 문제55. 책 6-16쪽을 보고 사원 번호를 물어보게 하고 사원번호를 입력하면 해당 사원에 대한 정보를emp 테이.. 더보기
#10_140626_PL_SQL_LABEL # LABEL 문제45. 위의 소스를 프로시져(pro2)로 만들어서 디버깅을 하시오 CREATE OR REPLACE PROCEDURE pro2isv_count NUMBER(10):=1;v_count2 NUMBER(10):=0; BEGIN WHILE v_count v_count := v_count + 1; v_count2 := 0; WHILE v_count2 v_count2 := v_count2 + 1; dbms_output.put_line(v_count || '*' || v_count2 || '=' || v_count*v_count2); END LOOP; END LOOP;END;/ 문제46. 5-31쪽을 보고 문제45번 소스에 레이블을 붙이시오 SET serveroutput ONDECLAREv_count.. 더보기
#12_140630_PL_SQL_PROCEDURE # PROCEDUREl PLSQL의 종류1. anonymous PL/SQL- 기본(fund)2. procedure3. function4. trigger5. packagen anonymous PL/SQL은 DB에 코드를 저장하지 않고 나머지는 코드를 저장한다 l 프로시져를 생성했을 때 장점1. 반복된 코드를 단순화 할 수 있다.똑같은 코드를 여러 번 사용해야할 때 코딩 양을 줄일 수 있다2. 프로그램 코드를 다른 프로그램에 이식하기가 편하다 l 매개변수- 입력용 : in- 출력용 : out - 입출력용 : inout l exec pro2 : 프로시져 수행l show err : 에러 출력 l 프로시져를 만들기 위해서는 set 구문삭제, declare 문장 삭제해야함 l 화살표를 써주면 서로 위치가 바뀌어도 .. 더보기
#10_140626_PL_SQL_FOR 문 # FOR 문 문제41. 아래의 테이블에 for loop 문을 이용해서 data를 입력하시오. empno 에는 1~1000까지 입력. ename 에는 scott1 ~ scott1000까지 입력 SET serveroutput ONDECLAREBEGIN FOR i IN 1..1000 LOOP INSERT INTO emp867 VALUES ( i , 'scott' || i); END LOOP;END;/ 문제43. for loop 문을 중첩해서 구구단 2단에서 4단까지 출력하시오 SET serveroutput ONBEGIN FOR i IN 2..4 LOOP FOR j IN 1..9 LOOP dbms_output.put_line(i || '*' || j || '=' || i*j); END LOOP; END LOO.. 더보기
#9_140625_PL_SQL_CASE 문 # CASE 문 문제34. 부서번호를 물어보게 하고 부서번호를 입력했을 때 10번이면 “인사팀입니” 20번이면 “개발팀입니다” 30번이면 “영업팀입니다” 라는 메시지가 출력되게 하시오 ACCEPT p_deptno PROMPT '부서번호를 입력하시오 ' DECLARE v_deptno emp.deptno%TYPE := &p_deptno; v_tmp VARCHAR2(20); BEGIN v_tmp := CASE v_deptno WHEN 10 THEN '인사팀입니다' WHEN 20 THEN '개발팀입니다' ELSE '영업팀입니다' END; dbms_output.put_line (v_tmp);END; / 더보기
#9_140625_PL_SQL_LOOP문 # LOOP문 l LOOP 문을 사용해야 하는 이유 – 특정 실행문을 반복해서 수행하고자 할 때 사용l LOOP 문의 종류 3가지- basic loop- while loop : 조건이 true인 경우만 반복 - for loop 문제36. 아래의 테이블을 생성하고 empno에 1~50000 만번까지 숫자를 입력하고 ename에는 scott를 입력하시오 CREATE TABLE emp440(empno NUMBER(10),ename VARCHAR2(20)); SET serveroutput ON DECLARE v_count NUMBER(10) := 1; BEGIN LOOP v_count := v_count+1; INSERT INTO emp440 VALUES (v_count,'SCOTT'); EXIT WHEN v_.. 더보기
#9_140625_PL_SQL_IF 문 # IF문 문제31. 위의 소스를 수정해서 사원 이름을 물어보게하고 사원 이름을 입력 했을 떄 해당 사원의 월급이 3000 이상이면 고소득자 입니다. 1000이상이고 3000보다 작으면 적당합니다. 1000보다 작으면 저 소득자입니다. 라고 출력되게 PL/SQL 을 작성하시오 ACCEPT p_ename PROMPT '이름을 입력하시오 ' DECLARE v_ename emp.ename%TYPE := UPPER('&p_ename'); v_sal EMP.sal%TYPE; BEGIN SELECT sal INTO v_sal FROM EMP WHERE ename = v_ename; IF v_sal >= 3000 THEN dbms_output.put_line('고소득자입니다'); ELSIF v_sal >= 1000 .. 더보기
#9_140625_PL_SQL_진리연산표 # 진리연산표 l 진리 연산표- T and T = T- T and F = F- and 진리연산표 TFnullTTFnullFFFFnullnullFnull - or 진리연산표 TFnullTTTTFTFnullnullTnullnull 더보기
#9_140625_PL_SQL_변수의 데이터 유형 # 변수의 데이터 유형 l 변수의 데이터 유형(scalar datatype)1. 문자 : varchar2, blob, clob2. 숫자 : number3. 날짜 : date4. 부울(Boolean) : boolean – true, false, null l composite datetype1. 레코드(Record)2. 컬렉션(Collection) l declare 와 end 사이에 쓴 것은 내부변수이고 나머지는 외부변수 l 오라클 함수 2가지1. 단일행 함수 : 문자, 숫자, 날짜, 변환, 일반2. 복수행 함수(=GROUP 함수) : max, min, sum, avg, count l PL/SQL내에서 쓸 수 있는 함수- 단일행 함수 : upper, month_betweenl PL/SQL내에서 쓸 수 없는 .. 더보기
#9_140625_PL_SQL_BIND 변수 # BIND 변수 문제11. 부서번호를 물어보게 하고 부서번호를 입력하면 해당 부서번호의 토탈월급을 출력하시오 set serveroutput onset verify offaccept p_deptno prompt '부서번호를 입력하시오! ' declarev_deptno emp.deptno%type := &p_deptno;v_sumsal emp.sal%type; beginselect SUM(sal) into v_sumsalfrom empwhere deptno=v_deptno; dbms_output.put_line ('토탈월급은 ' || v_sumsal); end;/ 문제12. 문제11번 소스를 bind 변수를 사용해서 결과가 출력되게 하시오 accept p_deptno prompt '부서번호를 입력하시오! .. 더보기
#9_140625_AUTOPRINT # AUTOPRINT l set autoprint on : print 문 입력없어도 됨 l show autoprint : autoprint 상태 확인 더보기
#8_140624_PL_SQL_ 기본 #8_140624_PL_SQL_ 기본 l PL/SQL(Procedure Language, 절차적 언어)- SQL + C언어 l 오류가 나서 부르기전에 2번 확인- 오류를 스스로 찾아보고- 짝궁의 도움 or notepad++ 의 비교기능(내가 작성 vs 정답소스) l 금융권에 쓰는 금융 패키지 – 오라클 PL/SQL(프랑스) l PL/SQL의 종류- 익명(1번)블록과 그 외 블록(2,3,4,5)의 차이는 DB에 이름을 가지고 저장 여부이다1. 익명 블록(anonymous PL/SQL block)2. procedure3. function4. trigger5. package 문제1. 사원번호를 입력하면 해당 사원의 월급이 출력되는 PL/SQL 프로그램을 작성하시오 set serveroutput onaccept.. 더보기

티스토리툴바