2009 | 0528 Vocabulary List 분류 구문 설명 본문 출력 출력 문 켜 기 set serveroutput on 프로 시 져 procedure procedure 만들 기 create or replace procedure 반복 for loop 구구단 출력 ( 값 하나 입력 받 아서 출력 하 는 구문 ) sav gugu 2 create or replace procedure gugu 2 ( su number ) asbegin 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 '); 선언 declarebeginend ; asbeginend ; 변수 선언 anonymous 블럭 에서 는 declare 와 begin 사이 에 선언 . procedure 에서 는 as 와 begin 사이 에 선언 . sav p 2 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 p 2 ( su number ) as x number ( 4 ) : = 10 ; begin dbms _ output . put _ line ( x + su ) ; end ;/ function create or replace function 함수 생성 sav f 1 create or replace function f 1 ( s 1 number , s 2 number ) return numberasbegin return s 1 + s 2 ; end ;/ function create or replace function 세금 구하 는 function 세금 3 . 3 % sav taxcreate or replace function tax ( sal number ) return numberasbegin if sal > 1000 then return sal * 0 . 033 ; else return 0 ; end if ; end ;/ select sal , tax ( sal ) TAX from emp ; 매개변수 default 매개변수 넣 지 않 을시 default 값 사용 sav p 3 create or replace procedure p 3 ( su number default 0 ) asbegin dbms _ output . put _ line ( su ) ; end ;/ procedure 테이블 생성 대용량 테이블 생성 sav testSet drop table tm / drop sequence tm _ s / CREATE TABLE TM ( ID NUMBER ( 10 ) , DATA VARCHAR 2 ( 30 ) , CONSTRAINT TM _ PK PRIMARY KEY ( ID ) ) / create sequence tm _ s / create or replace procedure tm _ i ( su number , data varchar 2 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 p 4 ( su in out number ) asbegin su : = 0 ; end ;/ var x number ; exec : x : = 40 ; exec p 4 ( x ) print x ; 선언 type , rowtype 데이터 유형 을 찾 기 귀찮 아서 . 향후 alter table 문 에 의해 column 정의 가 바뀌 었 을 경우 대비 declare enamex emp . ename % type ; deptx dept % rowtype ; begin enamex : =' Blake '; deptx . deptno : = 52 ; deptx . dname : = ' sales 2 '; deptx . loc : = ' seoul '; dbms _ output . put _ line ( enamex || deptx . dname ) ; end ;/ procedurerowtypeselect into select into 가 row 가 한 줄 인 것 또는 vector 값 만 되 나 rowtype 를 사용 해서 vector 값 을 넣 어서 사용 가능 sav deptscreate 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 ;/ procedurecursor execute immediate scott . emp . enme 로 user 만들 기 conn / as sysdba ; create or replace procedure createUas 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 dropUas 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 varchar 2 default ' a ', su number default null ) asbegin if su >= 0 then for i in 0 . . su loop execute immediate ' create table '|| tname || i ||'( id number ( 10 ) , data varchar 2 ( 20 ) ) '; end loop ; elsif su = 0 then execute immediate ' create table '|| tname || su ||'( id number ( 10 ) , data varchar 2 ( 20 ) ) '; else execute immediate ' create table '|| tname ||'( id number ( 10 ) , data varchar 2 ( 20 ) ) '; end if ; end ;/ procedure execute immediate 테이블 삭제 create or replace procedure dropT ( tname varchar 2 , su number default null ) asbegin 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 cursorfetchopen % ISOPEN % NOTFOUND % ROWCOUNT cursor 의 기본 형 sav empdcreate 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 gugu 2 ( su number ) ; procedure print ( st varchar 2 ) ; end ; / packagemyPackage package body exec myPackage . print ( ' aaaa ') create or replace package body myPackage is procedure gugu 2 ( 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 varchar 2 ) as begin dbms _ output . put _ line ( st ) ; end ; end ;/ DML hr 계정 에 emp 만들 기 conn / as sysdbacreate table hr . emp as select * from scott . emp ; create table hr . dept as select * from scott . dept ; conn hr / hralter 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 % typeas 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 numberas 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 ;/ procedureempdelete emp delete package create or replace procedure empDelete ( pEmpno emp . empno % type ) asbegin delete from emp where empno = pEmpno ; commit ; end ;/ DML truncateemp truncate empcommit ; drop table emp ; drop table dept ; drop sequence emps ; drop sequence depts ;@@ empCreate . sql ; truncate table emp ; delete from dept ; commit ; DML dropemppackage 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 > 15000 WHILE sal <= 15000 LOOPSELECT salary , manager _ id , last _ name INTO sal , mgr _ id , lnameFROM employees WHERE employee _ id = mgr _ id ; END LOOP ; 반복 while boolean 으로 도 사용 가능 If counter > 500 , DONE = TRUE DECLAREdone BOOLEAN ; counter NUMBER : = 0 ; BEGINdone : = FALSE ; WHILE done ! = TRUELOOPcounter : = counter + 1 ; done : = ( counter > 500 ) ; END LOOP ; END ;/ 반복 while IS NOT NULL WHILE counter IS NOT NULLLOOP ~~~~~~ END LOOP ;