분류구문설명본문
출력  출력문 켜기 set serveroutput on
 프로시져 procedure procedure 만들기 create or replace procedure
 반복 for loop

 구구단 출력

(값 하나 입력 받아서 출력하는 구문)

sav gugu2

create or replace procedure gugu2(su number)
as
begin
        for i in 1..9 loop
                dbms_output.put_line(su || 'X' || i || '=' || su*i);
        end loop;
end;
/
출력 dbms_output.put_line exec dbms_output.put_line('test');
 선언

declare

begin

end;

as

begin

end;

 변수 선언

anonymous 블럭에서는

declare 와 begin 사이에 선언.

procedure 에서는 as와 begin 사이에 선언.

sav p2

 declare
       x number(4);
       y number(4);
    begin
       x:=3;
       y:=10;
       dbms_output.put_line(x+y);
    end;
    /

 선언, 정의 한번에 가능

declare
        x number(4) :=3;
        y number(4) :=10;
begin
--      x:=3;
--      y:=10;
        dbms_output.put_line(x+y);
end;
/

create or replace procedure p2(su number)
as
        x number(4) :=10;
begin
        dbms_output.put_line(x+su);
end;
/



function  create or replace function

 함수생성

sav f1

create or replace function f1(s1 number, s2 number)
return number
as
begin
        return s1+s2;
end;
/
 function create or replace function

세금 구하는 function

세금 3.3%

sav tax

create or replace function tax(sal number)
return number
as
begin
        if sal > 1000 then
                return sal*0.033;
        else
                return 0;
        end if;
end;
/

select sal, tax(sal) TAX from emp;

 매개변수 default

 매개변수 넣지 않을시 default 값 사용

sav p3

create or replace procedure p3(su number default 0)
as
begin
        dbms_output.put_line(su);
end;
/
procedure 테이블 생성 

대용량 테이블 생성

sav testSet

 drop table tm
/
drop sequence tm_s
/
CREATE TABLE TM(ID NUMBER(10),DATA VARCHAR2(30),
           CONSTRAINT TM_PK PRIMARY KEY(ID))
/
create sequence tm_s
/
create or replace procedure tm_i(su number,data varchar2 default 'abcedfg')as
 begin
    for i in 1..su loop
            insert into tm values(tm_s.nextval,data);
            commit;
    end loop;
 end;
/
exec tm_i(100)
/
select count(*) from tm
/
  execute immediate DDL 문 사용 begin
   execute immediate 'create table xxxxx(x date)';
   end;
   /
 argument outargument 로 값 받기

create or replace procedure p4(su in out number) as
begin
    su:=0;
end;
/
var x number;

exec :x  := 40;

exec p4(x)

print x;

 선언 type, rowtype
  1. 데이터 유형을 찾기 귀찮아서.
  2. 향후 alter table문에 의해 column 정의가 바뀌었을 경우 대비

declare
        enamex emp.ename%type;
        deptx dept%rowtype;
begin
        enamex :='Blake';
        deptx.deptno := 52;
        deptx.dname := 'sales2';
        deptx.loc := 'seoul';
        dbms_output.put_line(enamex || deptx.dname);
end;
/

procedure

rowtype

select into

 select into가 row가 한줄인 것 또는 vector값만 되나 rowtype를 사용해서 vector 값을 넣어서 사용 가능

sav depts

create or replace procedure depts(su dept.deptno%type) as
        depts dept%rowtype;
begin
        select dname,loc into depts.dname, depts.loc
        from dept where deptno=su;
        dbms_output.put_line('dname= ' || depts.dname);
        dbms_output.put_line('loc= ' || depts.loc);
end;
/
procedure

cursor 

execute immediate

 scott.emp.enme로 user 만들기

conn / as sysdba;
create or replace procedure createU
as
        cursor createU_cur is select * from scott.emp where ename <> 'SCOTT';
begin
        for urow in createU_cur loop
                  execute immediate 'create user '||urow.ename||' identified by "1"';
                        dbms_output.put_line('create '||urow.ename);
                  execute immediate 'grant create session to '||urow.ename;
        end loop;
end;
/

procedure  execute immediate

 scott.emp.enme로 만든 user 삭제

 

 

 

 

conn / as sysdba;
create or replace procedure dropU
as
        cursor dropU_cur is select * from scott.emp where ename <> 'SCOTT';
begin
        for urow in dropU_cur loop
              execute immediate 'drop user '||urow.ename;
                      dbms_output.put_line('drop '||urow.ename);
        end loop;
end;
/

 procedure execute immediate 테이블 생성

create or replace procedure createT(tname varchar2 default 'a',su number default null)
as
begin
        if su >=0  then
                for i in 0..su loop
                         execute immediate 'create table '||tname||i||'(id number(10),data varchar2(20))';

                end loop;

        elsif su = 0 then
                execute immediate 'create table '||tname||su||'(id number(10),data varchar2(20))';

        else
                execute immediate 'create table '||tname||'(id number(10),data varchar2(20))';
        end if;

end;
/

procedure  execute immediate 테이블 삭제

create or replace procedure dropT(tname varchar2,su number default null)
as
begin

        if su >=0  then
                for i in 0..su loop
                        execute immediate 'drop table '||tname||i;
                end loop;

        elsif su = 0 then

                execute immediate 'drop table '||tname||su;
        else
                execute immediate 'drop table '||tname;
        end if;
end;
/

cursor 

cursor

fetch

open

%ISOPEN

%NOTFOUND

%ROWCOUNT

 cursor의 기본형

 

 

 

sav empd

create or replace procedure empd(dn emp.deptno%type)
as
        cursor ec is select * from emp where deptno=dn;
        e emp%rowtype;
begin
        if ec%ISOPEN = FALSE then
                open ec;
        end if;
        loop
                fetch ec into e;
                exit when ec%NOTFOUND;
                dbms_output.put_line(e.ename ||' '||e.sal);
        end loop;
                dbms_output.put_line('count = '||ec%ROWCOUNT);
end;
/
cursor  close 값을 변환해서 사용할 수 있다. declare
        dn emp.deptno%type;
        cursor ec is select * from emp where deptno=dn;
        e emp%rowtype;
begin
        dn := 10;
                open ec;
        loop
                fetch ec into e;
                exit when ec%NOTFOUND;
                dbms_output.put_line(e.ename ||' '||e.sal);
        end loop;
                dbms_output.put_line('count = '||ec%ROWCOUNT);
        close ec;
                dn := 20;
                open ec;
        loop
                fetch ec into e;
                exit when ec%NOTFOUND;
                dbms_output.put_line(e.ename ||' '||e.sal);
        end loop;
                dbms_output.put_line('count = '||ec%ROWCOUNT);
end;
/
 exception RAISE

 user_define_error

 

sal을 올리는데 500 이하면 rollback

 create or replace procedure raiseSal(
        empnox emp.empno%type,
        raiseSal number)
as
        user_define_error EXCEPTION;

begin
        update emp set sal=sal+raiseSal where empno=empnox;

        for e in (select sal from emp where empno=empnox) loop
                if e.sal <=500 then
                        RAISE user_define_error;
                else
                        commit;
                end if;
        end loop;

exception
        when user_define_error then
                ROLLBACK;
                dbms_output.put_line('rollback');


end;
/

package myPackage package

 package와 package body 같이 붙여서 만들 것.

 

exec myPackage.gugu(3)

 create or replace package myPackage
 is
    procedure gugu2(su number);
    procedure print(st varchar2);
 end;
 /
packagemyPackage  package body

 

 

 

exec myPackage.print('aaaa')

  create or replace package body myPackage
  is
     procedure gugu2(su number)
     as
     begin
             for i in 1..9 loop
                     dbms_output.put_line(su || 'X' || i || '=' || su*i);
             end loop;
     end;
     procedure print(st varchar2)
     as
     begin
             dbms_output.put_line(st);
     end;
 end;
/
 DML  hr 계정에 emp 만들기

 conn / as sysdba
create table hr.emp as select * from scott.emp;
create table hr.dept as select * from scott.dept;

conn hr/hr
alter table emp add constraint pk_emp primary key(empno);
alter table dept add constraint pk_dept primary key(deptno);
alter table emp add constraint fk_emp foreign key(deptno) references dept(deptno);
create sequence emps start with 7950;
create sequence depts start with 60 increment by 10;

function  empInsert insert package create or replace function empInsert(
            pEname emp.ename%type,
            pJob emp.job%type,
            pMgr emp.mgr%type,
            pHiredate emp.hiredate%type,
            Psal emp.sal%type,
            Pcomm emp.comm%type,
            PDeptno emp.deptno%type)
return emp.empno%type
as
        ret emp.empno%type;
begin
        insert into emp values(emps.nextval, pEname,pJob,pMgr,pHiredate,Psal,Pcomm,PDeptno);
        select emps.currval into ret from dual;
        commit;
        return ret;
exception
        when others then
                if SQLCODE=-2291 then
                                dbms_output.put_line('no FK...ERROR ');
                                return null;
                end if;
end;
/
function  empUpdate update package

 create or replace function empUpdate(
            pEmpno emp.empno%type,
            pEname emp.ename%type,
            pJob emp.job%type,
            pMgr emp.mgr%type,
            pHiredate emp.hiredate%type,
            Psal emp.sal%type,
            Pcomm emp.comm%type,
            PDeptno emp.deptno%type)
return number
as
        updateCount number(10):=0;
begin
        update emp set
                empno=pEmpno,
                ename=pEname,
                job=pJob,
                mgr=pMgr,
                hiredate=pHiredate,
                sal=Psal,
                comm=Pcomm,
                deptno=PDeptno
        where empno=pEmpno;

        select count(*) into updateCount from emp where empno=pEmpno;
        return updateCount;
end;
/

 procedure

empdelete

 

emp

 delete package create or replace procedure empDelete(pEmpno emp.empno%type)
as
begin
        delete from emp where empno= pEmpno;
        commit;
end;
/
 DML

 truncate

emp

 

 truncate empcommit;
drop table emp;
drop table dept;
drop sequence emps;
drop sequence depts;
@@empCreate.sql;
truncate table emp;
delete from dept;
commit;
DML 

 drop

emp

package

 drop emp

 rop table emp;
drop table dept;
drop sequence emps;
drop sequence depts;
drop package empDML;

 반복 casedecode 함수 대신해서 case 문 사용  create or replace procedure CaseTest(dn dept.deptno%type)
as
        empr emp%rowtype;
        cursor ec is select * from emp where deptno=dn;
begin
        open ec;
        loop
                fetch ec into empr;
                exit when ec%NOTFOUND;
                case dn
                        when 10 then
                                dbms_output.put_line(empr.ename||' '||'ACCOUNT');
                        when 20 then
                                dbms_output.put_line(empr.ename||' '||'RESEARCH');
                        when 30 then
                                dbms_output.put_line(empr.ename||' '||'SALES');
                        when 40 then
                                dbms_output.put_line(empr.ename||' '||'OPERATIONS');
                        else dbms_output.put_line('No such dname');
                end case;
        end loop;
        close ec;
end;
/
반복 while   loop until sal > 15000WHILE sal <= 15000 LOOP
SELECT salary, manager_id, last_name INTO sal, mgr_id, lname
FROM employees WHERE employee_id = mgr_id;
END LOOP;
 반복 while 

 boolean 으로도 사용가능

 

If counter > 500,

DONE = TRUE

 DECLARE
done BOOLEAN;
counter NUMBER := 0;
BEGIN
done := FALSE;
WHILE done != TRUE
LOOP
counter := counter + 1;
done := (counter > 500);
END LOOP;
END;
/
 반복 while IS NOT NULL

 WHILE counter IS NOT NULL
LOOP

     ~~~~~~
END LOOP;

저작자표시

'DataBase > Oracle' 카테고리의 다른 글

Admin 1. 04장 Managing the Oracle Instance  (0) 2012.10.18
Admin 1. 03장 Creating an Oracle Instance  (0) 2012.10.18
스크립트파일  (0) 2012.10.17
oracle user 생성  (0) 2012.10.17
oracle service 등록  (0) 2012.10.17

+ Recent posts

티스토리툴바