[Oracle] days14 - pl/sql

pl/sql   - 절차적인 언어 문법(기능 ) 

  PL/SQL은 블록 구조로 되어 있으며 블록은 
  선언부분, 
  실행부분, 
  예외처리부분의 3부분으로 구성되어 있으며 

선언 (declare) , 예외(exception)은 생략 가능하다  

★하지만 begin , end 은 꼭 써야 한다.★ 

【형식】
 
   [ DECLARE ]
      -- 선언문(declarations)
   BEGIN
      -- 실행문(statements)
   [ EXCEPTION ]
      -- 예외 처리문(handlers)
   END;

 

pl/sql 6가지종류

익명 프로시저	anonymous PL/SQL은 DECLARE ...로 시작되며, 사용자가 반복적으로 실행하려는 SQL문을 필요할 때마다 작성하여 실행하는 방법, 데이터베이스에 그 정보가 저장되지 않음
저장 프로시저	CREATE PROCEDURE name ...문에 의해서 생성된 후, 데이터베이스 내에 그 정보가 저장됨. stored procedure는 로직을 처리만 하고 끝남
저장함수	stored procedure와 같으며, stored procedure는 로직을 처리만 하고 끝나지만, stored function은 그 처리 결과를 사용자에게 반환함.
패키지	자주 사용되는 여러 procedure, function들을 하나의 package묶어서 관리에 편리토록 함
트리거	어떤 작업전, 또는 작업 후 trigger에 정의한 로직을 실행시키는 PL/SQL 블럭임.
객체 타입	객체에 데이터를 입력, 수정, 삭제, 조회하기 위해서는 반드시 PL/SQL 언어를 사용해야 함

 

익명 프로시저 

declare 
변수명 자료형 (크기)  -- 이 형식으로 선언 
begin 
exception 
begin;  이렇게 먼저 써놓고 시작 

이름과 나이 출력 

변수 대입할때 = 가 아닌 :=로 써야 오류가 안난다. 

-- 이름과 나이 출력
declare
    vname varchar2(20);
    vage number(3); --100살까지니까 3자리 
begin
    vname:='홍길동';
    vage:=20;
    dbms_output.put_line(vname || ',' ||vage); 
--exception
end ;

 

declare --선언문 변수선언, 상수선언 
--자바처럼 변수를 선언할 수 있다.
--변수를 담아놓은 이유는 begin 쿼리를 실행하여 이 변수에 담겠다는 것 
--변수명 자료형 (크기 
vename VARCHAR2(10) ;
vsal  NUMBER(7,2);
begin --실행문 statement

select ename, sal into vename, vsal
from emp
where empno=7369;
--출력
dbms_output.put_line(vename);
dbms_output.put_line(vsal);
--exception -예외처리 try~catch 문 
end; -- )

 

화면 출력 기능 함수 

put 또는 put_line	정의된 문자값을 화면에 출력하는 프로세서
NEW_LINE	GET_LINE에 의해 읽힌 행의 다음 라인을 읽을 때 사용
GET_LINE 또는 GET_LINES	현재 라인의 문자값을 읽는 프로세서
ENABLE	화면에 문자값을 출력하는 모드로 설정하며 문자값을 지정할 수 있는 버퍼크기를 정의함
DISABLE	화면에 문자값을 출력하는 모드로 해제함

 

 

10번 부서원주에 급여를 가장 많이 받는 사원의 정보를 출력하는 익명 프로시저 작성 

(empno, deptno , ename, job, mgr,hiredate, pay)

 

 

pl/sql 제어문 

 

if then

괄호는 생략가능 

IF 문은 END IF 문으로 끝난다. 

 
IF 조건1 THEN 
  수행문1; 
ELSE 
  IF 조건2 THEN 
    수행문2; 
  ELSE 
    IF 조건3 THEN 
      수행문3; 
    END IF; 
  END IF; 
END IF; 


IF 조건1 THEN 
  수행문1; 
  ELSIF 조건2 THEN 
    수행문2; 
    ELSIF 조건3 THEN 
      수행문3; 
END IF;

 

for문 

for counter 변수 in [reverse] 시작값, 끝값 
loop
반복처리할 코딩 
end loop; 

1부터 10까지 합 출력

declare
vi number;
vsum number:=0;
begin
    for vi in 1..10
    loop 
        vsum := vsum+vi; 
        if vi=10 then 
            dbms_output.put(vi);
        else
            dbms_output.put(vi ||'+');
        end if;
    end loop;
     dbms_output.put_line('=' || vsum);
end;

 

 while문 

1)loop end loop

declare
vi number;
vsum number:=0;
begin
    vi :=1;
    while(vi<=10)
    loop 
            dbms_output.put(vi||'+');
            vsum:=vsum+vi;
            vi:=vi+1;
    end loop;
     dbms_output.put_line('=' || vsum);
end;

구구단 

declare
--for 문에 사용하는 반복변수는 선언하지 않아도 된다. 
--vdan number(1);
--vi number(1);
begin
for vdan in 2..9
loop
    for vi in 1..9
    loop
    dbms_output.put(vdan || '*'||vi||'='|| rpad(vdan*vi, 4, ' '));
    end loop;

    dbms_output.put_line('');
 end loop;
 --exception 
 end;

 

 1.%type 변수를 선언함 

 declare
    vdeptno dept.deptno%type;
    vdname dept.dname%type;
    vempno emp.empno%type;
    vename emp.ename%type;
    vpay number;
begin
select d.deptno, dname, empno, ename, sal+nvl(comm,0) pay
into vdeptno, vdname, vempno, vename, vpay
 from emp e join dept d on e.deptno=d.deptno
 where empno=7369;

dbms_output.put_line(vdeptno ||',' ||vdname ||',' ||vempno|| ',' ||vename||','||vpay);
end;

 

record 형 변수

사용자정의  구조체  -편의를 위해 만들어짐 

 

 

【형식】 
    TYPE [type명] IS RECORD 
      ( field_name1  datatype [[NOT NULL] { := ¦ DEFAULT} expr] 
        field_name2  datatype [[NOT NULL] { := ¦ DEFAULT} expr] 
       .................. 
      );
    record명   type명;
 
필드 타입을 선언하기 위해서 %TYPE이나 %ROWTYPE을 사용할 수 있다.    

【예시】
  DECLARE 
   type dept_record_type IS RECORD
   (deptno	NUMBER(2),
    dname	VARCHAR2(13),
    loc		VARCHAR2(14);
   dept_record  dept_record_type;
  BEGIN
   dept_record.deptno := 10;
   dept_record.dname := '아리랑';
   dept_record.loc := 'Corea';
  END;

【예제】 
    DECLARE 
      TYPE DeptRecTyp IS RECORD 
      ( deptno  NUMBER(2) NOT NULL :=20, 
        dname   dept.dname%TYPE, 
        loc     dept.loc%TYPE 
      );

declare
   type empdepttype is record
   (
    vdeptno dept.deptno%type,
    vdname dept.dname%type,
    vempno emp.empno%type,
    vename emp.ename%type,
    vpay number
   );
   vrow empdepttype;
begin
select d.deptno, dname, empno, ename, sal+nvl(comm,0) pay
into vrow.vdeptno, vrow.vdname, vrow.vempno, vrow.vename, vrow.vpay
 from emp e join dept d on e.deptno=d.deptno
 where empno=7369;

dbms_output.put_line(vrow.vdeptno ||',' ||vrow.vdname ||',' ||vrow.vempno|| ',' ||vrow.vename||','||vrow.vpay);
end;