**인덱스
1)생성된 인덱스 조회
select a.table_name, b.column_name, a.index_name, a.index_type, a.uniqueness
from user_indexes a, user_ind_columns b
where a.index_name = b.index_name and a.table_name in ('EMP','DEPT');
save ind
** primary key, unique제약이 설정된 컬럼에 대해서는 자동으로 unique 인덱스를 오라클이 생성함
// 실행계획서를 확인하기 위한 환경설정
1.
conn /as sysdba
2.
@C:\oraclexe\app\oracle\product\10.2.0\server\sqlplus\admin\plustrce.sql
=>실행계획서를 확인할 수 있도록 하는 plustrace 권한을 생성하는 script를 실행
3. grant plustrace to scott; => scott에게 plustrace 권한 부여(실행계획서 참조가능하게하는 권한)
4.
conn scott/tiger
실행계획서를 참조할 수 있는 set autotrace 기능을 활성화
SQL> @C:\oraclexe\app\oracle\product\10.2.0\server\RDBMS\ADMIN\utlxplan.sql
=>plan_table을 생성하는 스크립트
SQL> set autotrace traceonly explain =>이제부터 쿼리의 실행계획서만을 보여줌
set autotrace off =>원상태로
set autotrace on => 실행계획서 및 실행 성능, 결과값을 보여줌
SQL> select * from emp
where empno = 7788;
SQL> analyze table emp compute statistics;
=>emp 테이블의 현재상황을 통계분석하라
**시퀀스(sequence)
1. 역할 : 새로운 정수값을 생성하는 객체임. 이 정수값은 흔히 pk값으로 사용하면 좋다.
2. 시퀀스 생성
create sequence emp_seq
start with 1000
increment by 1
maxvalue 2000
nocycle
nocache;
=>1000번 부터 2000번까지 1식 증가하는 emp_seq라는 시퀀스를 생성하라
3. 생성된 시퀀스 확인
select * from user_sequences;
4.시퀀스 사용
insert into emp(empno, ename)
values(emp_seq.nextval,'test');
/
select * from emp; => test의 사번?
select emp_seq.currval
from dual;
*** 사용자 접근제어 ***
1.계정 생성
conn /as sysdba =>sys계정으로 접속
show user
select * from all_users; => DB에 접속할 수 있는 모든 계정명 확인
create user cjedu
identified by cjedu; => 새로운 cjedu 계정 생성
conn cjedu/cjedu => 최소한의 권한인 접근 권한이 없는 관계로 접속이 안됨
2. 시스템 권한
conn /as sysdba
select * from system_privilege_map; => 계정들에게 부여할 수 있는 시스템 권한의 모든 정보 확인
grant create session to cjedu; => cjedu에게 create session(접속권한)부여
conn cjedu/cjedu => ??
select * from user_users; => 접속자 본인정보 확인
select * from user_sys_privs; => 부여받은 시스템 권한 확인
select * from role_sys_privs; => 역시부여받은 시스템권한 확인(role채로..)
conn /as sysdba
select * from role_sys_privs; =>부여할 수 있는 모든 role의 정보 확인
grant connect,resource to cjedu; => 일반적으로 생성된 계정들에게는 Connect, resource라는 role을 부여함
conn cjedu/cjedu
select * from role_sys_privs;
3. 객체 권한
show user => cjedu
select * from tab;
select * from scott.dept; => scott계정 소유의 emp 테이블을 조회하려 함.
conn scott/tiger
grant select on dept to cjedu; => cjedu계정에게 dept 테이블에 대한 select권한을 부여함
conn cjedu/cjedu
select * from scott.dept =>조회 가능
delete scott.dept; => 삭제 불가능
////////////////////////////////////////////////////////////
중간에 서버 설정하는 필기등 생략..(채워넣을 것)
select * from user_tab_privs_recd; =>부여받은 객체권한정보 조회
conn scott/tiger
select * from user_tab_privs_made; => 부여한 객체권한정보 조회
///////////////////////////////////////////////////////////////////
1.오라클 네트워크 서버측 환경설정
1)listener.ora 파일 확인
C:\oraclexe\app\oracle\product\10.2.0\server\NETWORK\ADMIN\listener.ora
2)listener 프로세스 활성화 시키기
3)client
////////////////////////////////////////////////////////////
create or replace procedure emp_info( i_empno in emp.empno%type)
is
v_empno emp.empno%type;
v_ename emp.ename%type;
v_job emp.job%type;
v_mgr emp.mgr%type;
v_sal emp.sal%type;
v_deptno emp.deptno%type;
v_mgrname varchar2(10);
begin
select e.empno, e.ename, e.job, e.mgr, e.sal, e.deptno, m.ename
into v_empno, v_ename, v_job, v_mgr, v_sal, v_deptno, v_mgrname
from emp e, emp m
where (e.empno = i_empno) and (e.mgr = m.empno);
dbms_output.put_line ( v_empno||' '||v_ename ||' '|| v_mgrname);
end;
/
select object_name from user_objects
where object_type = 'PROCEDURE';
select text from user_source
where name ='EMP_INFO';
**프로시저를 실행하는 방법
1)sql*plus툴이라면
set serveroutput on
execute 프로시저명(값..);
2)다른 프로시저내에서 호출
begin
프로시저명(값....);
end;
/
3)자바등의 host언어에서 호출
**pl/sql stored procedure
1.이름을 입력받아 그 사원의 정보 중 부서명과 급여를 out변수에 저장하여 return 하는 stored프로시저를 생성하여라.
create or replace procedure out_test(
i_ename in emp.ename%type,
o_dname out dept.dname%type,
o_sal out emp.sal%type)
is
v_deptno emp.deptno%type;
begin
--그 사원의 급여 및 부서코드를 조회해서 변수에 치환
select sal, deptno
into o_sal, v_deptno
from emp
where ename=upper(i_ename);
--그 부서코드에 해당하는 부서명을 조회해서 변수에 치환
select dname
into o_dname
from dept
where deptno= v_deptno;
end;
/
declare
v_dname dept.dname%type;
v_sal emp.sal%type;
begin
out_test('scott',v_dname,v_sal);
dbms_output.put_line('scott의 부서명 :'||v_dname);
dbms_output.put_line('scott의 급여 :'||v_sal);
end;
/
2.이름을 입력받아 그 사원의 정보 중 부서명과 급여를 return 하는 stored function을 생성하여라.
create or replace function function_test(
i_ename in emp.ename%type)
return varchar2
is
v_deptno emp.deptno%type;
v_dname dept.dname%type;
v_sal emp.sal%type;
begin
--그 사원의 급여 및 부서코드를 조회해서 변수에 치환
select sal, deptno
into v_sal, v_deptno
from emp
where ename=upper(i_ename);
--그 부서코드에 해당하는 부서명을 조회해서 변수에 치환
select dname
into v_dname
from dept
where deptno= v_deptno;
return v_sal||v_dname;
end;
/
select function_test('scott') from dual;
1)emp 테이블의 모든 사원들의 사번, 이름, 급여, 부서코드를 화면에 출력하는 stored procedure를 생성
create or replace procedure all_empinfo
is
v_empno emp.empno%type;
v_ename emp.ename%type;
v_sal emp.sal%type;
v_deptno emp.deptno%type;
--커서의 선언
cursor c1 is
select empno,ename, sal, deptno
from emp;
begin
--커서의 open(커서공간에 데이터를 올리는 작업)
open c1;
--커서에 올려진 데이터를 한행씩 변수에 치환(fetch)
loop
fetch c1 into v_empno, v_ename, v_sal, v_deptno;
exit when c1%notfound;
dbms_output.put_line('사번 : '||v_empno);
dbms_output.put_line('이름 : '||v_ename);
dbms_output.put_line('급여 : '||v_sal);
dbms_output.put_line('부서코드 : '||v_deptno);
dbms_output.put_line(chr(13));
end loop;
--커서의 close(메모리공간을 해제)
close c1;
end;
/
2)동일한 급여를 받는 사원들의 이름, 업무, 급여를 조회하여 출력하는 stored procedure를 생성하시오.
create or replace procedure same_sal_procedure
is
v_ename emp.ename%type;
v_job emp.job%type;
v_sal emp.sal%type;
--커서의 선언
cursor c1 is
select ename, job, sal
from emp
where sal in (select sal from emp group by sal having count(*) >1);
begin
--커서의 open(커서공간에 데이터를 올리는 작업)
open c1;
--커서에 올려진 데이터를 한행씩 변수에 치환(fetch)
loop
fetch c1 into v_ename, v_job, v_sal;
exit when c1%notfound;
dbms_output.put_line('이름 : '||v_ename);
dbms_output.put_line('업무 : '||v_job);
dbms_output.put_line('급여 : '||v_sal);
dbms_output.put_line(chr(13));
end loop;
--커서의 close(메모리공간을 해제)
close c1;
end;
/
1.이름을 입력받아 그 사원의 정보 중 부서명과 급여를 out변수에 저장하여 return 하는 stored프로시저를 생성하여라. 예외처리
create or replace procedure out_test(
i_ename in emp.ename%type,
o_dname out dept.dname%type,
o_sal out emp.sal%type)
is
v_deptno emp.deptno%type;
begin
--그 사원의 급여 및 부서코드를 조회해서 변수에 치환
select sal, deptno
into o_sal, v_deptno
from emp
where ename=upper(i_ename);
--그 부서코드에 해당하는 부서명을 조회해서 변수에 치환
select dname
into o_dname
from dept
where deptno= v_deptno;
exception
when no_data_found then
dbms_output.put_line(i_ename||'님은 저희회사 사원이 아닙니다.');
dbms_output.put_line('에러번호 : '||sqlcode||' 에러메시지 : '||sqlerrm);
end;
/
declare
v_dname dept.dname%type;
v_sal emp.sal%type;
begin
out_test('test',v_dname,v_sal);
dbms_output.put_line('test의 부서명 :'||v_dname);
dbms_output.put_line('test의 급여 :'||v_sal);
end;
/
invalid-file
