오라클 PL/SQL에서 SQL문장(SELECT, UPDATE, INSERT, DELETE) 사용

오라클 PL/SQL에서 SQL문장(SELECT, UPDATE, INSERT, DELETE) 사용

http://ojc.asia/bbs/board.php?bo_table=LecSQLnPlSql&wr_id=570

 

오라클 PL/SQL에서 SQL문장(SELECT, UPDATE, INSERT, DELETE) 사용

오라클 PL/SQL에서 SQL문장(SELECT, UPDATE, INSERT, DELETE) 사용3.2 PL/SQL에서 SQL문장 사용n PL/SQL은 SELECT, INSERT, UPDATE, DELETE와 같은 DML(Data Manipulation Language) 및 COMMIT, ROLLBACK과 같은 TCL(Transaction Control La…

ojc.asia

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 개의 행이 선택되었습니다.