061 - [Oracle PL/SQL] Dynamic SQL - INTO Clause

<샘플코드에서 사용한 데이터는 HR 스키마이고, 오라클 설치시 생성할 수 있는 기본 스키마 입니다>

 

 

 

• 간단한 into 샘플 코드

# dynamic sql with single row query

# 익명블록, into 절을 사용한 경우
declare
    v_ename varchar2(100);
begin
    execute immediate 'select first_name from employees where employee_id=100'
    into v_ename;
    DBMS_OUTPUT.put_line(v_ename);
end;


# 익명블록, into/using 절을 동시에 사용한 경우
# into 부분이 using 앞에 위치해야 한다
declare
    v_ename varchar2(100);
    vno number:=200;
begin
    execute immediate 'select first_name from employees where employee_id=:1' 
    into v_ename using vno; --note: the into should be first then the using
    DBMS_OUTPUT.put_line(v_ename);
end;
--------------------------------------------------
Jennifer
PL/SQL procedure successfully completed.

 

 

 

 

• 함수 리턴이 레코드 타입인 샘플, ':=' 으로 값을 전달하는 경우

# 레코드 전체를 리턴하는 함수
create or replace function get_emp
    (p_id number)
    return employees%rowtype
is
    emp_rec employees%rowtype;
begin
    select * into emp_rec
    from employees
    where employee_id=p_id;
    
    return emp_rec;
end;


# you can not use this function in select, it retrive %rowtype
select get_emp(100) from dual;
--------------------------------------------------------------
ORA-06553: PLS-801: internal error [55018]
06553. 00000 -  "PLS-%s: %s"
*Cause:    SQL compilation failed because of an illegal reference to a
           PL/SQL unit.
*Action:   If the PL/SQL error message does not provide a workaround, then
           try the SQL statement as a top-level call as the appropriate user
           with the appropriate privileges.
Error at Line: 50 Column: 8



# 함수의 리턴타입이 rowtype 이므로, 변수도 rowtype 변수로 함수결과를 받아야한다
declare
    emp_rec employees%rowtype;
begin
    emp_rec:=get_emp(100);
    dbms_output.put_line(emp_rec.employee_id||' '||emp_rec.first_name );
end;
--------------------------------------------------------------
100 Steven

 

 

 

• 함수 리턴이 레코드 타입인 샘플, into , ':=' 코딩 비교

--dynamic sql with single row query
create or replace function get_emp2
    (p_id number)
    return employees%rowtype
is
    emp_rec employees%rowtype;
    v_query varchar2(1000);
begin
    v_query:='select * from employees where employee_id=:1';
    execute immediate v_query into emp_rec using p_id;
    return emp_rec;
end;


declare
    emp_rec employees%rowtype;
begin
    emp_rec:=get_emp2(105);
    dbms_output.put_line(emp_rec.employee_id||' '||emp_rec.first_name );
end;
--------------------------------------------------------------
105 David