アカデミー45日目-Oracle

ex29_plsql.sql

PL/SQL

select->結果セット->PL/SQL変数

selection toセクション使用

結果セットが1レコードのみの場合に利用可能(★★)

の結果、3つの列の1つまたは複数が

に達することを示した.

declare
    변수선언;
begin
    select 컬럼 into 변수 from 테이블;
end;

cursor

を使用

結果セットのレコードが0個を超える場合に利用可能(N個)

.

の結果、3つの列の1つまたは複数が

に達することを示した.

は、通常、結果セットに2つ以上のレコードがある場合に

を使用することを推奨する.

Javaと同様の奇形腫構造(ブラウズ(アクセス)データセット-個々のアクセス)

declare
    커서선언;
begin
    커서열기;
    	loop
        	레코드 접근 -> 커서 사용
    	end loop;
    커서닫기;
end;    

set serveroutput on;

declare
    vname tblInsa.name%type;
begin
-- select name into vname from tblInsa where num = 1000; -- 데이터가 0개면 에러(into절은 무조건 1개일때 사용)
-- select name into vname from tblInsa -- 레코드의 갯수가 여러개면 에러
    select name into vname from tblInsa where num = 1001; -- PK
    dbms_output.put_line(vname); -- 홍길동
end;    

-- 여러 레코드를 가져오는 방법 : cursor사용
declare
    vname tblInsa.name%type; 
    
    -- 커서 선언
    -- 자료형 변수명 is select 컬럼 from 테이블명;
    -- 1. select하고 싶은 문장을 cursor로 만든다. 
    cursor vcursor is select name from tblInsa; -- 결과셋 참조 객체
begin
    -- 2. 커서 열기 : 커서가 가지고 있는 select문이 실행된다. > 결과셋 > 커서가 참조
    open vcursor;
    
    -- 커서 조작 -> 결과셋 탐색
    -- 3. loop: 결과셋의 레코드들을 탐색 루프(한줄씩 접근)
    loop
        -- 커서를 전진한다. + 커서가 가르키는 레코드의 컬럼 접근(읽기)
        -- 4. vcursor가 읽어온 컬럼리스트의 값을 vname변수에 넣는다.(루프반복) 
        fetch vcursor into vname; -- select name into vname과 유사(일회성)
        
        -- 5. 탈출 -> 더이상 읽어올 레코드가 없을 때
        exit when vcursor%notfound; -- 커서 프로퍼티, notfound : 더이상 읽어올 레코드가 없으면 true를 반환
        
        -- 6. 사용
        dbms_output.put_line(vname);
        
    end loop;
    
    -- 커서 닫기
    close vcursor;
end; 

select * from tblCounty;

declare
    cursor vcursor
        is select name, capital, population from tblCountry order by name asc;
    vname tblCountry.name%type;
    vcapital tblCountry.capital%type;
    vpopulation tblCountry.population%type;
begin
    open vcursor;
        loop
            -- select name, capital, population into vname, vcapital, vpopulation
            fetch vcursor into vname, vcapital, vpopulation;
            exit when vcursor%notfound;
            
            dbms_output.put_line(vname || '-' || vcapital || '-' || vpopulation);
        end loop;
    close vcursor;    
end;

select * from tblBonus;

-- 개발부 직원 -> 모두 보너스 지급
declare
    
    cursor vcursor is select num, basicpay from tblInsa where buseo = '개발부';
    vnum tblInsa.num%type;
    vbasicpay tblInsa.basicpay%type;
begin
    
    open vcursor;
        loop
        
            fetch vcursor into vnum, vbasicpay;
            exit when vcursor%notfound;
            
            -- 여기서부터 업무에 따라 고민..
            insert into tblBonus(seq, pnum, bonus, regdate)
                values (seqBonus.nextVal, vnum, round(vbasicpay * 0.7), default);
            
        end loop;
    close vcursor;    
end;

-- 기본버전
-- tblInsa.개발부 직원 + 모든 컬럼 
declare
    cursor vcursor is select * from tblInsa where buseo = '개발부';
    vrow tblInsa%rowtype; -- 레코드 참조 변수 (컬럼 10개짜리)
begin
    open vcursor;
        loop
            fetch vcursor into vrow; -- 컬럼 10개 -> 변수 10개 복사
            exit when vcursor%notfound;
            
            dbms_output.put_line(vrow.name);
            dbms_output.put_line(vrow.buseo);
            dbms_output.put_line('---');
        end loop;
    close vcursor;    
end;

-- 간단버전
declare
    cursor vcursor is select * from tblInsa where buseo = '개발부'; -- 그대로
    -- vrow tblInsa%rowtype; -- 생략 -> 아래에서 만든다.
begin
    -- open vcursor;
        for vrow in vcursor loop
            -- fetch vcursor into vrow;
            -- exit when vcursor%notfound;
            
            dbms_output.put_line(vrow.name);
            dbms_output.put_line(vrow.buseo);
            dbms_output.put_line('---');
        end loop;
    -- close vcursor;    
end;

declare
    cursor vcursor is select * from tblInsa where buseo = '개발부';
begin
        for vrow in vcursor loop -- for loop로 커서탐색, vrow + loop + fetch ++ exit when
        
            dbms_output.put_line(vrow.name);
            dbms_output.put_line(vrow.buseo);
            dbms_output.put_line('---');
            
        end loop;  
end; 

create view vwDev
as
select * from tblInsa where buseo = '개발부';

select * from vwDev;
select * from (select * from tblInsa where buseo = '개발부'); -- 인라인뷰
--------------------------------------------------------------------

-- 더 간단버전(권장x)
-- 인라인터서 -> 가독성이 낮음
-- 단순한 쿼리에 한해서 사용
begin
        for vrow in (select * from tblInsa where buseo = '개발부') loop
        
            dbms_output.put_line(vrow.name);
            dbms_output.put_line(vrow.buseo);
            dbms_output.put_line('---');
            
        end loop;  
end; 

プログラム

PL/SQLブロック(declare、begin、exception、endを含む)

匿名プログラム

使い捨て(データベースに保存せず、ハードディスク*.sqlに保存)

オブジェクトx

速度が遅い

はテスト用、

は一時開発用

実名プログラム

データベースに格納(格納)

オブジェクトO

は再利用可能であり、

は他のユーザと共有可能である.

より高速

商用

実名プロシージャのタイプ(ストアド・プロシージャ)

ストレージプロセス

構成

パラメータ構成または戻り値->フリー

記憶機能

要求

パラメータ、要求戻り値->固定

匿名プロシージャvsストレージプロシージャ

匿名プロシージャ宣言

[declare
    변수 선언;
    커서 선언;]
begin
    구현부;
[exception 
    예외처리;]    
end;

ストアド・プロシージャの宣言

create [or replace] procedure 프로시저명
is(as)
    [변수 선언;
    커서 선언;]
begin
    구현부;
[exception 
    예외처리;]    
end [프로시저명];

-- 익명
declare
    vnum number;
begin
    vnum := 100;
    dbms_output.put_line(vnum);
end;


-- 실명 
-- Procedure PROCTEST이(가) 컴파일되었습니다. > 프로시저가 만들어짐. 보려면 호출을 해야한다. 
create or replace procedure procTest
is 
    vnum number;
begin
    vnum := 100;
    dbms_output.put_line(vnum);
end procTest;

ストアド・プロシージャを呼び出します。

スクリプト環境で(ANSI-SQL環境で)

を呼び出す.

PL/SQL BLOCK呼び出し

-- 2. PL/SQL 블럭에서 호출하기
-- PL/SQL 환경을 만들어줘야지만 호출 가능
begin
    procTest; -- 인자값이 없는 메소드 호출, ()소괄호사용x
end;    

-- ANSI-SQL환경이기때문에 실행 x
-- ANSI-SQL에서는 create, insert, delete, alter, select.. 등만 사용가능
-- procTest;


-- 1. 스크립트 환경에서 호출하기(ANSI-SQL 환경에서 호출)
execute procTest;
exec procTest;
call procTest(); -- call 사용시 () 필수!