Or15PLSQL

/*****************************

파일명 : Or15PLSQL.sql

PL/SQL

설명 : 오라클에서 제공하는 프로그래밍 언어

*****************************/

/*

PL/SQL(Procedural Langage)

:일반 프로그래밍 언어에서 가지고 있는 요소를 모두 가지고 있으며 DB업무를

처리하기 위해 최적화된 언어이다.

기본구조

-선언부(Declare) : 모든 변수나 상수를 선언하는 부분

-실행부 (Executable) : Begin - end /제어문, 반복문, 함수정의 등의 로직을

기술하는 부분

- 예외처리부(Exception) : 실행도중에 에러발생시 해결하기위한 명령들을

기술하는 부분

Declare, Begin, Exception 등과 같은 키워드들은 ;(세미콜론)을 붙이지 않는다.

나머지 문장들은 ; 으로 마무리한다.

-익명블록(Anonymous Black) : 주로 일회성으로 사용할 경우 사용된다.

-저장블록(Stared Block) : 서버에 저장해 놓고 주기적으로 반복해서 사용할

경우에 많이 사용된다.

*/

/

set serveroutput on;

--화면상에 내용을 출력할 때는 on으로 설정한다.

--set serveroutput off; --내용을 출력하지 않을때는 off로 설정한다.

--선언부는 declare로 시작함.

declare

    cnt integer;

--실행부는 begin --end; 사이임.

begin

    cnt := cnt +1; 

    -- 대입연산자로 PL/SQL에서는 = 대신 :=를 사용함.

    if cnt is null then 

    -- Java에서 System.out.println()과 동일한 역할로 화면에 문자열을 출력

        dbms_output.put_line('결과:cnt는 넓이다');

    else

        dbms_output.put_line('결과:cnt는 넓이 아니다');

    end if;

end;

--실행부의 종료부분

/

/

declare

    empNo number(20);

    empName varchar2(30);

begin

    -- select절에서 가져온 필드를 into절에서 변수에 할당한다.

    select employee_id, first_name

    into empNo, empName

    from employees

    where employee_id=120;

    

    dbms_output.put_line(empNo ||' '|| empName);

end

;

/

/*

변수

-변수의 생성규칙

1. 반드시 문자로 시작해야 한다.

2. 문자나 숫자, 특수문자를 포함할수 있다.

3. 변수명은 30byte이하여야 한다.

4. 예약어(키워드)를 사용할 수 없다.

-변수의 선언은 선언부(declare)에서 선언되고, 값으로 초기화가 가능하다.

-실행부에서 실행될경우 값이 할당된다.

-서브프로그램의 파라미터로 전달되기도 하며, 서브프로그램의 출력결과를

저장하기도 한다.

변수의선언예>

emp_no number(6,3) : 숫자를 저장하는 변수로 총 6자리, 소수점이하

3자리를 의미한다.

emp_name varchar2(5) : 문자를 저장하는 변수로 총 5바이트를 

저장할수있다.

emp_date date : 날자를 저장하는 변수이다.

변수의 데이터타입>

-char : 고정길이의 문자를 저장. 최소1 ~ 최대32,767바이트 저장.

기본최소값은 1

-varchar2 : 가변길이의 문자를 저장. 최대 32,767바이트 저장.

기본최소값 없음

-number(전체자리수, 소수점이하 자리수) : 전체자리수와 소수점이하

자리수를 가진 숫자를 저장. 전체자리수 범위는 1~38까지 가능함.

소수점 자리수의 범위는 -84~127까지 가능함.

-binary_double : 부동소수점 숫자를 저장. 9바이트 필요함.

-date : 날짜 및 시간을 저장. 초단위로 저장함.

날자의 범위는 4712 B.C ~ 9999 A.D

-timestamp : date타입의 확장. 연,월,일,시,분,초 및 소수로 표시되는

초단위를 저장. 

*참조변수*

형식 : 테이블명.필드명%Type

empNo employees.employee_id%Type

:employees 테이블의 employee_id와 동일한 데이터타입으로 선언

empRow employees%ROWTYPE

:employees 테이블의 모든 컬럼을 한꺼번에 저장하기 위한 변수로 선언

*/

--employees -> employees_copy로 스키마와 레코드까지 복사하기

create table employees_copy

as

select * from employees where 1=1;

desc employees_copy;

select * from employees_copy where department_id=10;

/*

하나의 컬럼을 지정하여 기존테이블의 동일한 자료형으로 변수 선언

예시] 부서번호 10인 사원의 사원번호, 급여, 부서번호를 가져와서

아래 변수에 대입후 화면상에 출력하는 PL/SQL문을 작성하시오.

단, 변수는 기존테이블의 자료형을 참조하는 참조변수로 선언하시오.

*/

--테이블명 컬럼%type : 특정테이블의 컬럼과 동일한 자료형으로 선언

declare

    empNo       employees_copy.employee_id%type;

    empSalary   employees_copy.salary%type;

    deptId      employees_copy.department_id%type;

begin

    select employee_id, salary, department_id

        into empNo, empSalary, deptId

        from employees_copy

        where department_id = 10;

        

        dbms_output.put_line(empNo ||' '|| empSalary || ' '|| deptId);

end;

/

/*

테이블의 전체 컬럼을 한꺼번에 가져와서 emp_row변수에 지정한 후 출력

예시] 사원번호가 100인 사원의 레코드를 가져와서 emp_row변수에 지정한 후

화면에 출력하시오.

단, emp_row는 employees_copy 테이블의 전체컬럼을 저장할 수 있는 형태의

참조변수로 선언해야 한다.

*/

--테이블명%rowtype : 특졍테이블의 전체컬럼을 대체하는 참조변수 선언

declare

    emp_row employees_copy%rowtype;

begin

    select * into emp_row

    from employees_copy

    where employee_id =100;

    

    dbms_output.put_line(

        emp_row.employee_id ||' '|| 

        emp_row.first_name ||' '||

        emp_row.email ||' '||

        emp_row.salary ||' '||

        emp_row.department_id);

end;

/

/*

PL/SQL에서 Select문장 사용시 주의사항

1. select문은 반드시 하나의 행만 return해야 한다. 하나이상 이거나 없을 경우

PL/SQL은 Too_many-rows 혹은 No_data_found 예외를 발생시킨다.

2. 만약 여러행을 Return해야 할 경우에는 Cursor를 사용해야 한다.

3. Select문장을 PL/SQL에서 사용시에는 into절을 사용해야 한다.

4. Into절에서는 테이블의 열과 Into절에서의 출력변수의 수가 일치해야 한다.

(1:1매칭되어 할당되어야 함)

*/

/*

-바인드변수

1. 호스트환경에서 선언된 변수로서 바 PL/SQL변수이다.

2. PL/SQL 프로그램의 내부나 외부에서 값을 전달하기 위해서 사용한다.

3. PL/SQL 문이 프로시저나 함수안에 있지 않는다면 호스트에서 선언된 변수

(바인드변수)를 PL/SQL문장에서 참조할 수 있다.

4. 바인드변수와 PL/SQL내에서의 변수를 구분하기 위해 바인드변수 앞에 :(콜론)을

붙인다.

5. 키워드는 VAR[IBLE]을 이용한다.

6. PL/SQL 블럭이 실행된 후에도 엑세스가 가능하다.

7. print명령을 이용하여 출력이 가능하다.

사용법 :

    VAR 변수명 자료형;

바인드변수가 1개인 경우

    PRINT 변수명

    or

    SELECT : 변수명 FROM 테이블명;

바인드변수가 2개이상인 경우

    PRINT 변수명1 변수명2 -> 스페이스바로 구분함

    or

    SELECT : 변수명1, :변수명2 FROM 테이블명;    

*/

--바인드(호스트)변수 변수선언

set serveroutput on;

var return_var number;

declare

begin

    :return_var := 300;

end;

/

print return_var;

-바인드변수 잠시 보류

/************************************************************/

--PL/SQL을 이용한 데이터 입력

--1.tb_member에 레코드 입력

insert into tb_member values (100,'gildong1','1234','홍길동','gildong@kosmo.co.kr');

insert into tb_member values (101,'gildong2','1234','최길동','choi@kosmo.co.kr');--1.tb_member에 레코드 입력

insert into tb_member values (102,'gildong3','1234','박길동','park@kosmo.co.kr');

--2.tb_member테이블을 스키마만 복사

create table tb_member_copy

as

select * from tb_member where 1=0;

--3.레코드확인

select * from tb_member;        --3개의 레코드 연출

select * from tb_member_copy;   --0개의 레코드 연출

--4.commit해서 영구저장

commit;

--update tb_member set member_idx=102

--where userid = 'gildong3'; 레코드에 아이디가 모두 같게 들어가서 변경하였음.

--5.데이터를 복사하여 저장하는 PL/SQL문 작성

declare

    c_rec tb_member%rowtype;

begin

    select * into c_rec

    from tb_member

    where member_idx=100;

    

    insert into tb_member_copy values c_rec;

end;

/

--6. 레코드 확인

select * from tb_member_copy;

--PL/SQL을 이용한 데이터 수정

/*

예시] tb_member테이블의 idx=101을 가져와서

      tb_member_copy 테이블의 100번 레코드 수정

*/

-- 하나의 레코드를 가져온 후 특정 컬럼만 다른값으로 수정하고 싶을 때

declare

    --테이블의 전체 행 참조한 참조변수 생성

    copy_row tb_member_copy%rowtype;

begin

    --member_idx가 101인 레코드를 가져와서 copy_row 변수에 할당

    select *

        into copy_row

    from tb_member

    where member_idx=101;

    

    

    --update문에서는 * 를 사용할 수 없으므로 전체컬럼을 대체할 수 있는

    --'row'라는 키워드를 제공하고 있다.

    --즉, 전체행을 tb_member_copy 테이블에 업데이트한다.

    update tb_member_copy

    set row=copy_row

    where member_idx =100;

end;

/

select * from tb_member_copy;

-- 하나의 레코드를 가져온 후 특정 컬럼만 다른값으로 수정하고 싶을 때

declare

    --테이블의 전체 행 참조한 참조변수 생성

    copy_row tb_member_copy%rowtype;

begin

    --member_idx가 101인 레코드를 가져와서 copy_row 변수에 할당

    select *

        into copy_row

    from tb_member

    where member_idx=102;

    

    --할당한 값중에 패스워드만 다른 값으로 변경하기

    copy_row.passward := '9999';

    

    update tb_member_copy

    set row=copy_row

    where member_idx =102;

end;

/

select * from tb_member_copy;

    

/*

    치환연산자

    : 사용자로부터 값을 입력받을 때 사용한다.

*/

declare

    /* 사용자로부터 입력을 받을 때 변수 앞에 엠퍼센트를 사용한다. */

    no1 number := #

    no2 number := #

    total number;

begin

    total := no1 + no2;

    dbms_output.put_line(

    '첫번째수:' || no1 ||', 두번째수:' || no2 ||', 합은:'|| total || '입니다.');

end;

/

/*

복합변수

    : class를 정의하듯 필요한 자료형을 묶어서 생성하는 변수

    사용법

    1. 복합변수 생성을 위한 자료형 정의

        Type 타입명 Is Record (

            자료형1,

            자료형2....

        );

    2. 자로형을 통한 복합변수 생성

    변수명 타입명;

*/

declare

    -- emp_type이라는 3개의 자료형을 가진 복합변수를 생성함

    type emp_type is record

    (

        emp_id employees.employee_id%type,

        emp_name employees.first_name%type,

        emp_job employees.job_id%type

    );

    --위에서 정의한 복합변수 emp_type을 이용하여 변수생성

   

    rec1 emp_type;

begin

    --select절에서 가져온 3개의 컬럼을 변수 rec1에 할당함.

    select employee_id, first_name, job_id

    into rec1

    from employees

    where department_id =10;

    

    --할당한 변수를 출력 -> 변수.컬럼명

    dbms_output.put_line('사번    이름    직무아이디');

    dbms_output.put_line('--------------------------');

    dbms_output.put_line(rec1.emp_id ||' '|| rec1.emp_name ||' '||

    rec1.emp_job);

end;

/

/*

문제] 아래 절차에 따라 PL/SQL 코드를 작성하시오.

아래 조건에 맞는 '복합변수'를 생성한다.

참조테이블 : employees의 각 컬럼을 참조하여 생성

복합변수 자료형 이름 : employees

    멤버1 : emp_id

    멤버2 : emp_name

    멤버3 : emp_email

    멤버4 : emp_salary

    멤버5 : emp_percent

생성한 복합변수 자료형으로 복합변수 rec2를 생성하고 사원번호 100번을

가져와서 할당한다

그리고 사번, 이름, 이메일, 급여, 보너스율을 출력한다.

단, 보너스율이 null이라면 0으로 표시한다.

*/

declare

    type emp_type is record

    (

        emp_id employees.employee_id%type,

        emp_name employees.first_name%type,

        emp_email employees.email%type,

        emp_salary employees.salary%type,

        emp_percent employees.commission_pct%type

    );

begin

    select employee_id, first_name, email, salary, nvl(commission_pct, 0)

    into rec2

    from employees

    where department_id =100;

    dbms_output.put_line('사번:' || rec2.emp_id);

    dbms_output.put_line('이름:' || rec2.emp_name);

    dbms_output.put_line('이메일:' || rec2.emp_email);

    dbms_output.put_line('급여:' || rec2.emp_salary);

    dbms_output.put_line('보너스율:' || rec2.emp_percent);

end;

/

declare

    --복합변수 자료형을 선언

    Type employTypes Is Record(

        emp_id employees.employee_id%Type,

        emp_name employees.last_name%Type,

        emp_email employees.email%Type,

        emp_salary employees.salary%Type,

        emp_percent employees.commission_pct%Type

    );

    --자료형을 통한 복합변수 선언

    rec2 employTypes;

    --사용자로부터 사원번호를 입력받아 출력하기

    vemp_id employees.employee_id%Type := &vemp_id;

begin

    select employee_id, last_name, email, salary, nvl(commission_pct,0)

        into rec2

    from employees

    where employee_id = vemp_id;

    

    dbms_output.put_line('사번:'|| rec2.emp_id);

    dbms_output.put_line('이름:'|| rec2.emp_name);

    dbms_output.put_line('이메일:'|| rec2.emp_email);

    dbms_output.put_line('급여:'|| rec2.emp_salary);

    dbms_output.put_line('보너스율:'|| rec2.emp_percent ||'%');

end;

/

/*

제어문 : if문, case문과 같은 조건문

    if문

      사용법]

          if 조건1 then

                  실행문1

                elsif 조건2 then

                  실행문2

                else

                  실행문3

                end if;

                #else if가 아니라 s가 빠진 elsif 라는 것을 주의할 것

*/

--홀수 짝수를 판단하는 if문

declare

    vNum number;

begin

    vNum := 10;

    

    if mod(vNum,2) =0 then

        dbms_output.put_line(vnum||'은 짝수');

    else

        dbms_output.put_line(vNum||'은 홀수');

    end if;

    --if문의 끝은 반드시 end if; 로 마무리 할것.

end;

/

--단일 if문으로 사원의 부서를 출력하는 PL/SQL문

declare

    emp_id employees.employee_id%type;

    emp_name employees.last_name%type;

    emp_dept employees.department_id%type;

    dept_name varchar2(30) := '부서정보없음'; --선언과 동시에 초기화

begin

    select employee_id,last_name,department_id

    into emp_id,emp_name,emp_dept

    from employees

    where employee_id=110;

    

    if emp_dept=50 then

    dept_name:='Shipping';

    end if;

    if emp_dept=60 then

    dept_name:='IT';

    end if;

    if emp_dept=70 then

    dept_name:='Public Relations';

    end if;

    if emp_dept=80 then

    dept_name:='Sales';

    end if;

    if emp_dept=90 then

    dept_name:='Executlive';

    end if;

    

    dbms_output.put_line('사원번호'|| emp_id || '의 정보');

    dbms_output.put_line('이름'|| emp_name || '부서번호:'|| dept_name);

    

end;

/

--위에서 만든 PL/SQL문을 if-elsif문으로 변경 및 사원번호를 입력받은 후 조회

declare

    emp_id employees.employee_id%type := &emp_id; --사용자로부터 입력받음

    emp_name employees.last_name%type;

    emp_dept employees.department_id%type;

    dept_name varchar2(30) := '부서정보없음'; --선언과 동시에 초기화

begin

    select employee_id,last_name,department_id

    into emp_id,emp_name,emp_dept

    from employees

    where employee_id= emp_id;

    

    --자바에서 사용하는 else if에서 e가 빠진 형태임을 주의

    if emp_dept=50 then

    dept_name:='Shipping';

    elsif emp_dept=60 then

    dept_name:='IT';

    elsif emp_dept=70 then

    dept_name:='Public Relations';

    elsif emp_dept=80 then

    dept_name:='Sales';

    elsif emp_dept=90 then

    dept_name:='Executlive';

    end if;

    

    dbms_output.put_line('사원번호'|| emp_id || '의 정보');

    dbms_output.put_line('이름'|| emp_name || '부서번호:'|| dept_name);

    

end;

/

set serveroutput on;

--사원번호를 통해 조히한 사원의 보너스율에 따른 조건문

declare

    emp_id employees.employee_id%type := &emp_id;

    emp_name employees.first_name%type ;

    emp_comm employees.commission_pct%type ;

begin

    select first_name, commission_pct

        into emp_name, emp_comm

    from employees

    where employee_id = emp_id;

    

    if emp_comm > 0 then

        dbms_output.put_line(emp_name||'의 보너스 율은 '|| emp_comm ||'입니다.');

    else

        dbms_output.put_line(emp_name||'은 보너스가 없습니다.');

    end if;

end;

/

/*

case문 : switch문과 비슷한 조건절

사용법]

    Case 변수

        When 값1 then 실행문1

        When 값2 then 실행문2

        ............

        When 값n then 실행문n

    End ;

*/

declare

    e_id employees.employee_id%type;

    e_name employees.first_name%type;

    e_dept employees.department_id%type;

    dept_name varchar2(30) := '부서모름';

begin

    select employee_id, first_name, department_id

        into e_id, e_name, e_dept

    from employees

    where employee_id = 100;

    

    --Case -When 문에 의한 분기

    dept_name := case e_dept

                    when 50 then 'Shipping'

                    when 60 then 'IT'

                    when 70 then 'Public Reations'

                    when 80 then 'Sales'

                    when 90 then 'Executive'

                end;

    dbms_output.put_line('사원번호'|| e_id || '의정보');

    dbms_output.put_line('부서아이디:'|| e_dept ||', 이름:'|| e_name ||',

    부서명:'|| dept_name);

end;

/

/*

반복문

- Basic Loop문 : do-while문과 비슷한 형태로 문장을 먼저 실행한 후

    조건을 검사한다. 즉, 무조건 한번은 실행이 되는 형태의 반복문.

- while문 : 반복문의 일종으로 조건이 맞는지 확인후 실행한다.

    Basic Loop문과 다른점은 조건에 따라 한번도 실행되지 않을 수도 있다.

- For문 : 반복의 횟수를 지정하여 사용할 수 있으며, 반복을 위한 변수는

따로 선언하지 않아도 된다.

*/

/*

1. Basic Loop문

    사용법]

    Loop

        실행문장 ;

        exit (Loop를 벗어날 조건);

    End Loop;

*/

declare

    num number := 0; --숫자타입으로 선언후 0으로 초기화

begin

    loop --조건검사없이 일단 loop 안으로 진입

        dbms_output.put_line(num);

        num := num + 1;

        exit when(num>10); -- num이 10을 초과할때 loop를 탈출

    end loop;

end;

/

/*

2. while문

    사용법]

     while 조건 Loop

        실행문;

     End Loop;

*/

declare

    num number := 0;

begin

    while num<11 loop

        dbms_output.put_line('이번숫자는: '||num);

        num := num +1;

    end loop;

end;

/

/*

3. For문

    사용법]

      For n[카운트 변수] In start[초기값] .. end[종료값] Loop

        실행문;

      End Loop;

*/

declare

    --반복을 위한 변수선언이 필요없다.

    --이 경우 declare는 생략 가능함.

begin

    for num1 in 0 .. 10 loop

        dbms_output.put_line('현재숫자는:'|| num1);

    end loop;

end;

/

--변수 선언이 필요 없으므로 declare 부분은 생략

begin

    for num2 in reverse 0 .. 10 loop

        dbms_output.put_line('거꾸로 반복되는 숫자는:'|| num2);

    end loop;

end;

/

/*

테이블 타입 변수

컬렉션 : 일반 프로그래밍 언어에서 사용하는 배열타입을 PL/SQL에서는

    컬렉션이라고 한다.

종류

1. 연관배열(Associative Array / Index-by Table)

2. VArray(Variable Array)

3. 중첩테이블(Nested Table)

* 연관배열 : 키와 갑싱 한쌍으로 구성된 컬렉션으로 Java의 해시테이블과

같은 개념이다.

    Key : 자료형은 주로 숫자를 사용하며 binary_integer, pis_integer가 사용된다.

        이 두가지 타입은 number보다 킉가 작고 산술연산에 빠른 자료형이다.

        단, 문자형(varchar2)으로 사용하는 것도 가능하다.

    Value : 자료형은 문자형이며 주로 varchar2가 사용된다.

사용법]

    Type 연관배열명 Is Table Of 연관배열_값_타입 Index by 연관배열_키_타입

*/

declare

    --변수명 : av_type

    --value(값)의 자료형 : varchar2(30)

    --key(키)의 자료형 : pls_integer

    type av_type is table of varchar2(30) index by pis_integer;

    

    -연관배열 변수를 생성

    var_test av_type;

begin

    var_test(10) := '10에 대한 할당값'; -- 키는 10, 값은 '10에 대한 할당값'

    var_test(20) := '20에 대한 할당값';

    

    dbms_output.put_line(var_test(10));

    dbms_output.put_line(var_test(20));

and;

/

/*

연관배열의 키를 문자형으로 선언한 경우

*/

declare

    tname varchar2(20);

    

    type t_emp_name

        is table of employees.last_name%type

        index by varchar2(4);

    v_name t_emp_name;

begin

    select last_name 

        into tname

    from employees

    where employee_id =100;

    

    v_name('CR01') := tname;

    

    dbms_output.put_line(v_name('CR01'));

end;

/

/*

VArray(Variable Array)

: 고정길이를 가진 배열로서 일반 프로그래밍 언어에서 사용하는 배열과

동일하다. 크기에 제한이 있어서 선언할 때 크기(요소의 갯수)지정하면

이보다 큰 배열로 만들 수 없다.

사용법]

    Type 배열명 Is Array(배열크기) Of 요소값_타입;

*/

declare

    --VArray의 이름 : va_type

    --값(value)의 자료형 : varchar2(20)

    

    type va_type is array(5) of varchar2(20);

    

    --VArray 변수 선언

    vArr va_type;

    

    --배열을 카운트하기 위한 변수선언

    vn_cnt number := 0;

begin

    --생성자를 통한 값의 초기화(총5개중 3개만 할당)

    vArr := va_type('First','Second','Third','','');

    

    --Basic Loop문을 통한 배열의 요소 출력(인덱스는 1부터 시작)

    loop

        vn_cnt := vn_cnt + 1;

        if vn_cnt>5 then

            exit; --5가 될때까지 반복하다가 6이 되었을때 탈출

        end if;

        --배열이 요소값 출력

        dbms_output.put_line(vArr(vn_cnt));

    end loop;

    

    --배열에 값을 재할당

    vArr(3) := '우리는';

    vArr(4) := 'JAVA';

    vArr(5) := '개발자다';

    

    vn_cnt := 0;

    loop

        vn_cnt := vn_cnt + 1;

        

        --5보다 커지면 Loop 탈출, if문과 동일한 조건문임.

        Exit when vn_cnt>5;

        dbms_output.put_line(vArr(vn_cnt));

    end loop;

end;

/

/*

중첩테이블(Nested Table)

    : VArray와 비슷한 구조의 배열로서 배열의 크기를 명시하지 않으므로

    동적으로 배열의 크기가 설정된다.

    여기서 말하는 테이블은 자료가 저장되는 실제테이블이 아니라

    컬렉션의 한 종류를 의미한다.

    

    사용법]

        Type 중첩테이블명 IS Type Of 값_타입;

*/

declare

    --중첩테이블 선언

    type nt_type is table of varchar2(30);

    --정의한 중첩테이블을 통해 변수 선언

    ntArray nt_type;

begin

    --생성자를통한 값 할당.(여기서는 크기가 4인 중첩테이블이 생성됨)

    ntArray := nt_type('첫번째', '두번째', '세번째', '');

    

    dbms_output.put_line(ntArray(1));

    dbms_output.put_line(ntArray(2));

    dbms_output.put_line(ntArray(3));

    ntArray(4) := '네번재값할당';

    dbms_output.put_line(ntArray(4));-- 4번째 값까지 저장할 수 있음.

    --ntArray(5) := '다섯번째값??할당??'; --5번째값을 저장할 경우 에러발생

    

    /* 오류내용 : 첨자가 개수를 넘었습니다. */

    

    --크기를 확정해야 할 때는 생성자를 통해 배열의 크기를 동적으로 확정한다.

    ntArray := nt_type('la','2b','3c','4d','5e','6f','7g');

    

    --변수 i가 1 ~ 7까지 증가하는 for문

    for i in 1 .. 7 loop

        dbms_output.put_line(ntArray(i));

    end loop;

end;

/

/*

반복문과 배열을 응용한 예제

예시] 다음 조건에 맞는 연관배열 자료형을 선언하시오.

    이름 : tbl_type

    값(value)의 자료형 : employees 의 last_name 컬럼 참조

    키(key)의 자료형 : binary_integer

위 자료형을 기반으로 v_tbl_array 변수를 생성한 후 사원테이블의

전체레코드 중 last_name을 가져와서 연관배열에 저장후 for문을 통해

출력하시오.

*/

declare

    --값의 자료형은 last_name 참조, 키의 자료형은 binary_integer로 선언

    type tbl_type

        is table of employees.last_name%type

        index by binary_integer;

    --배열변수선언

    v_tbl_array tbl_type;

    

    --배열의 인덱스로 사용할 변수 선언후 0으로 초기화

    cnt binary_integer := 0;

begin

    /*

    for문의 반복의 횟수를 정수의 구간이 아닌 조회되는 레코드의 갯수만큼

    반복하도록 설정함. 여기서는 조회된 last_name이 emp_name에 할당되면서

    레코드의 갯수만큼 반복됨.

    */

    for emp_name in (select last_name from employees) loop  

        cnt := cnt + 1;

        --반복의 요소에 접근할때는 변수 emp_name을 통해서 하게 된다.

        v_tbl_array(cnt) := emp_name.last_name;

    end loop;

    

    --위에서 저장된 배열의 값을 for문으로 반복하면서 출력형 (1~107까지 출력)

    for i in 1..cnt loop

        dbms_output.put_line(i || '>' || v_tbl_array(i));

    end loop;

end;

/

/*

continue 보조제어문 (11g부터 추가된 기능)

    : 해당 조건을 만족하면 반복문의 처음으로 이동하는 기능

    사용법]

        Continue When (조건);

*/

declare

    tot number := 0;

begin

    for i in 1..10 loop

    tot := tot +1;

    dbms_output.put_line('i='|| i ||', tot='|| tot);

    

    continue when(i>5);

    

    tot := tot + i;

    dbms_output.put_line('i='|| i ||', tot2->'|| tot);

  end loop;

end;

/

/*

커서(Cursor)

 : select 문장에 의해 여러행이 반환되는 경우 각 행에 접근하기 위한것.

 

    선언방법]

      Cursor 커서명 Is

        Select 문장(Into절이 없는 Select문)

Open Cursor

 : 질의를 수행하라는 의미, 즉 Open시에 Cursor 선언시의 Select문장이 실행되어

 결과셋을 얻게된다. Cursor는 그 결과셋의 첫번째 행의 의지하게 된다.

 

 Open 커서명

 

Fetch - Into -

 : 결과셋에서 하나의 행을 읽어들이는 작업, 결과셋의 인출(Fetch)후에

 Cursor는 다음 행으로 이동함.

 

 Fetch 커서명 Into (변수1, 변수2........)

 

Cursor닫기]

 : 결과셋의 자원을 반납. Select문장이 모두 처리된 후 Cursor을 Close해줌.

 

 Close 커서명;

 

Cursor의 속성]

 %Found : 가장 최근의 인출(Fetch)행을 Return하면 True, 아니면 False를

   반환

 %RowCount : 지금까지 Return된 행의 갯수

*/

declare

    --departments 테이블의 전체컬럼을 참조한 참조변수 선언

    v_dept departments%rowtype;

    

    --c1이라는 이름의 커서를 생성.

    cursor c1 is

        select department_id, department_name, location_id

        from departments;

begin

    dbms_output.put_line('부서번호 부서명 지역번호');

    dbms_output.put_line('------------------------');

    

    --커서Open : 질의를 수행하라는 의미

    open c1;

    

    loop

    --결과셋에서 하나의 행을 인출(Fetch)하여 변수에 할당

        fetch c1 into v_dept.department_id,

                                v_dept.department_name,

                                v_dept.location_id;

        --인출할 레코드가 더이상 없으면 loop 탈출 

        exit when c1%notfound;

        

        dbms_output.put_line(v_dept.department_id||' '||v_dept.department_name||' '||v_dept.location_id);

    end loop;

    

    --커서의 자원반납

    close c1;

end;

/

#PL/SQL

/*

1. 사원테이블에서 사원아이디를 통해 조회한 후 사원이름의 문자수만큼 #을 찍는 pl/sql문을 작성해보자.

*/

declare

--사원번호 입력받기

emp_id employees.employee_id%type:=&emp_id;

--사원의 이름을 저장하기 위한 변수

emp_name varchar2(40);

--이름의 길이를 저장할 변수

name_length number;

#을 줄바꿈없이 저장할변수

sharp_str varchar2(30);

begin

select last_name, length(last_name)

into emp_name, name_length

from employees

where employee_id = emp_id;

dbms_output.put_line('조회된 사원의 이름;'|| emp_name);

dbms_output.put_line('이름의 길이:'|| name_length);

for i in 1 .. name_length loop

sharp_str := sharp_str || '#';

end loop;

dbms_output.put_line(sharp_str);

end;

/*

2. Basic loop문으로 1부터 10까지의 정수의 합을 구하시오.

*/

declare

--1~10"까지증가시키기위한 변수

startNum number := 1;

--누적합을구하기 위한 변수

sumNum number := 0;

begin

loop

dbms_output.put_line('startNum'|| startNum);

--1씩 증가

--누적합 구함

sumNum := sumNum + startNum;

startNum :== startNum + 1;

exit when startNum>10;

end loop;

dbms_output.put.line('1~10까지의 합:' || sumNum);

end;

/*

3. For loop 문으로 구구단을 출력하시오.

*/

declare

    v_sum number(1) := 1; --초기화

begin

        for i in 1 .. 9 Loop       

         dbms_output.put_line('&곱셈*'|| I || '=' || &곱셈*I);

    end loop;

end;

/ 

begin

for i in 2 .. 9 loop --단에 해당하는 for문

for j in 1 .. 9 loop

dbms_output.put_linee(i || ' * ' || j || ' = ' || ((j*i));

end loop;

dbms_output.put_line('');

end loop;

end;

/*

4. While loop 문으로 다음과 같은 결과를 출력하시오.

*

**

***

****

*****

*/

declare

num1 number := 1; --while문 반복을 위한 변수

num2 number := 1;

starTxt varchar2(5);

begin

while num1 <= 5 loop

while num2 <= num1 loop --num2가 num1보다 작거나 같을때까지 루프 별을 찍어낸다.

starTxt := starTxt|| '*';

num2 := num2 + 1;

end loop;

num 1 := num1 + 1;

dbms_output.put_line(starTxt);

end loop;

end;

/*

5. Cursor를 사용하여 사원테이블에서 커미션이 null이 아닌 

사원의 사원번호, 이름, 급여를 오름차순(이름)으로 출력하시오. 

*/

declare

--사원테ㅐ이블의 전체컬럼을 대체할 수 있는 참조변수 생성

v_emp employees%rowtype;

--커서 생성

cursor cEmp is

select employee_id last_name, salary from employees

where commission_pct is not null

order by last_name asc;

begin

--커서 Open : 질의를 수행하라는 의미

open cEmp;

loop

fetch cEmp

into v_emp.employee_id, v_emp.last_name, v_emp salary;

exit when cEmp%notfound;

dbms_output.put_line(v_emp.employee_id ||' '|| v_emp.last_name ||' '|| v_emp.salary);

end loop;

close cEmp;

end;

declare

    v_emp employees%rowtype;

    cursor cEmp is

        select * from employees

        where commission_pct is not null

        order by first_name asc;

begin

    for v_emp in cEmp loop

        dbms_output.put_line(v_emp.employee_id||' '||v_emp.first_name||' '||v_emp.salary);

    end loop;

end;