12/4일 오라클

/입력받는 프로시저
create or replace procedure rf_p(
p_emp_id in emps.emp_id%type,
p_name in emps.name%type,
p_depid in emps.dep_id%type)
is
begin
insert into emps(emp_id, name, dep_id)
values(p_emp_id, p_name, p_depid);

END;
/실행 execute rf_p(emp_id_nos.nextval,'gyuhyeon',3);
----------------------------------------------------------------------------
/* 입력받고 값 출력
CREATE OR REPLACE FUNCTION rf_f(v_empid number)
    RETURN VARCHAR
IS
   v_name VARCHAR2(100);
BEGIN
  select
  name into v_name
  from emps
  where emp_id=v_empid;
  RETURN v_name;
END;
*/실행 select rf_f(9) from dual;
----------------------------------------------------------------------------
create sequence emp_id_nos
start with 1
increment by 1
maxvalue 9999;
/시퀀스 제작
----------------------------------------------------------------------------
/뷰 만들기
create or replace view v_emps
AS
select emp_id, name, dep_id, job_id
from usr.emps;
----------------------------------------------------------------------------
/퍼블릭 시노님 만들기
create or replace public synonym sy_edj
for usr.emps;
----------------------------------------------------------------------------
/테이블 만들기 /제약/외래키 참조
create table emps
(
emp_id NUMBER (4)
    constraint emps_no_pk primary key,
name varchar2(15)
    constraint emps_name_nn not null,
dep_id NUMBER(4)
constraint emps_deptsid_fk references depts(dep_id),
job_id NUMBER(2)
constraint emps_jobid_fk references jobs(job_id)
);
----------------------------------------------------------------------------
/유저 만들기
create user test identified by test;
/권한 주기
grant connect, resource to test;
----------------------------------------------------------------------------
/구간 검색
select * from (
    select rownum rnum, p.* from(
        select * from person order by point desc) p
)
where rnum >=7 and rnum <= 10;
----------------------------------------------------------------------------