PLSQL에서 SQL 구문의 사용방법_%ROWTYPE, SELECT, INSERT, UPDATE, DELETE_SQL학원/SQ

PLSQL에서 SQL 구문의 사용방법_%ROWTYPE, SELECT, INSERT, UPDATE, DELETE_SQL학원/SQL교육/PLSQL교육/PLSQL학원

www.topcredu.co.kr

 

3.2 PL/SQL에서 SQL문장 사용

n  PL/SQL은 SELECT, INSERT, UPDATE, DELETE와 같은 DML(Data Manipulation Language) 및 COMMIT, ROLLBACK과 같은 TCL(Transaction Control Language)을 지원한다.

n  DDL(Data Definition Language)에 대해서는 지원하지 않는다(CREATE, DROP, ALTER). 만약 사용하려 한다면 Dynamic SQL(EXECUTE IMMEDIATE 구문, DBMS_SQL패키지 이용)을 사용해야 한다.

n  DCL(Data Control Language)도 지원하지 않는다.(GRANT, REVOKE)

3.2.1 SELECT문의 사용

n  SELECT문의 종료는 세미콜론(;)으로 표시한다.

n  SELECT절의 칼럼목록과 INTO절의 칼럼목록은 개수 및 데이터 타입이 동일해야 한다.

n  SELECT * 인 경우 %ROWTYPE을 사용하면 좀 더 효율적이다.

n  INTO절은 필수이며 BEGIN~END 사이에서 사용되는 SELECT는 반드시 한 건만 SELECT 되어야 한다. 한 건도 SELECT 되지 않는다면 ORA-01403(NO_DATA_FOUND) 오류가 발생하고 여러 건 SELECT 된다면 ORA-01422(TOO_MANY_ROWS) 오류가 발생한다.

[기본형식]

SELECT  select_list

INTO    변수명 or 레코드이름

FROM    table

WHERE   conditions

-- EMP 테이블에서 이름을 입력받아 해당 사원의 급여 및 입사일자를 표시

SQL> edit ojc14

set serveroutput on

create or replace procedure ojc14

(v_ename in emp.ename%type)

is

           v_sal emp.sal%type;

           v_hiredate emp.hiredate%type;

begin

           select sal, hiredate

           into v_sal, v_hiredate

           from emp

           where ename = v_ename;

           dbms_output.put_line('급여 : '||to_char(v_sal,'999,999'));

           dbms_output.put_line('입사일자 : '||to_char(v_hiredate, 'yyyy.mm.dd'));

end;

/

SQL> @ojc14

프로시저가 생성되었습니다.

SQL> exec ojc14('SMITH')

급여 :    4,500

입사일자 : 1980.12.17

-- SELECT예외 처리예제

SQL> set serveroutput on

SQL> create or replace procedure ojc15

    (v_sal in emp.sal%type)

    is

       v_ename emp.ename%type;

       v_hiredate emp.hiredate%type;

    begin

       select ename, hiredate

       into v_ename, v_hiredate

       from emp

      where sal = v_sal;

      dbms_output.put_line('이름 : '||v_ename);

      dbms_output.put_line('입사일자 : '||to_char(v_hiredate, 'yyyy.mm.dd'));

   end;

   /

프로시저가 생성되었습니다.

SQL> exec ojc15(950);

이름 : JAMES

입사일자 : 1981.12.03

SQL> exec ojc15(1250);

BEGIN ojc15(1250); END;

*

1행에 오류:

ORA-01422: 실제 인출은 요구된 것보다 많은 수의 행을 추출합니다

ORA-06512: "SCOTT.OJC15",  7행

ORA-06512:  1행

-- 예외처리 예문

SQL> set serveroutput on

SQL> create or replace procedure ojc15

    (v_sal in emp.sal%type)

    is

       v_ename emp.ename%type;

       v_hiredate emp.hiredate%type;

    begin

       select ename, hiredate

       into v_ename, v_hiredate

       from emp

      where sal = v_sal;

      dbms_output.put_line('이름 : '||v_ename);

      dbms_output.put_line('입사일자 : '||to_char(v_hiredate, 'yyyy.mm.dd'));

   exception

     when too_many_rows then

       dbms_output.put_line('데이터가 여러건 추출 되었습니다.');

     when no_data_found then

       dbms_output.put_line('데이터가 한건도 없습니다.');

     when others then

      dbms_output.put_line('SQL ERROR CODE : ' || SQLCODE);

      dbms_output.put_line('SQL ERROR MSG  : ' || SQLERRM);

   end;

   /

프로시저가 생성되었습니다.

SQL> exec ojc15(1250)

데이터가 여러건 추출 되었습니다.

SQL> exec ojc15(9999)

데이터가 한건도 없습니다.

3.2.2 INSERT/UPDATE/DELETE문의 사용

n  일반적인 DML 사용하는 방법과 같다.

n  WHERE절을 만족하는 레코드가 없더라도 오류가 발생하지 않고 WHERE절을 기술하지 않았다면 전체 레코드에 대해 DML이 처리된다.

-- EMP 테이블에서 모든 사원의 급여를 10% 상승시키세요.

SQL> select ename, sal from emp;

ENAME             SAL

---------- ----------

SMITH             800

ALLEN            1600

WARD             1250

……

14 개의 행이 선택되었습니다.

SQL> DECLARE

    BEGIN

      UPDATE EMP SET sal = sal + ROUND(10/100*sal);

      COMMIT;

    END;

    /

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> select ename, sal from emp;

ENAME             SAL

---------- ----------

SMITH             880

ALLEN            1760

WARD             1375

……

14 개의 행이 선택되었습니다.