분류 전체보기 (657) 썸네일형 리스트형 065 - [Oracle PL/SQL] Design - Standardizing 에러처리를 고급스럽게 처리하는 방법, 샘플코드, 사용자 정의 오류와 오라클 표준 에러를 동시에 처리하는 방법 delete from DEPARTMENTS /* Error report - ORA-02292: integrity constraint (HR.EMP_DEPT_FK) violated - child record found */ declare e_fk_err exception; pragma EXCEPTION_INIT (e_fk_err, -02292); begin delete from DEPARTMENTS; exception when e_fk_err then RAISE_APPLICATION_ERROR (-20001, 'error'); end; /* Error report - ORA-20001: error .. 064 - [Oracle PL/SQL] Dynamic SQL - DBMS_SQL Package VS Execute immediate 다이나믹 SQL을 구현하는 방법은 다양하다, 아래 방법중에 편한 방법을 사용하면 된다. 테스트용 테이블을 아래와 같이 준비 drop table emp1; create table emp1 ( emp_id number ); drop table emp2; create table emp2 ( emp_id number ); begin insert into emp1 values (1); insert into emp1 values (2); insert into emp1 values (3); insert into emp2 values (1); insert into emp2 values (2); insert into emp2 values (3); commit; end; select * from emp1; select *.. 063 - [Oracle PL/SQL] Dynamic SQL - to compile packages Execute immediate 를 이용한 다양한 샘플 코드 # we can also use dynamic sql to execute anonymous-block declare v_code varchar2(100):= 'begin dbms_output.put_line(''welcome''); end; '; begin execute immediate v_code; end; ---------------------------------------------- # procedure 컴파일 alter procedure ADD_ROWS compile; ---------------------------------------------- Procedure ADD_ROWS altered. # function 컴파일 al.. 061 - [Oracle PL/SQL] Dynamic SQL - INTO Clause 간단한 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 emplo.. 060 - [Oracle PL/SQL] Dynamic SQL - USING Clause # EXECUTE IMMEDIATE with USING # 우선 테이블 데이터를 모두 삭제한다 delete from emp1; select * from emp1; -- this procedure can work with any table contain 1 coulmn and -- this column should be number create or replace procedure add_rows ( p_table_name varchar2, p_value number ) is begin --EXECUTE IMMEDIATE 'insert into '||p_table_name ||' values('||p_value||') '; EXECUTE IMMEDIATE 'insert into '||p_table_name.. 059 - [Oracle PL/SQL] Dynamic SQL 테스트 환경 준비 # 새로운 테이블을 생성 drop table emp1; create table emp1 ( emp_id number ); drop table emp2; create table emp2 ( emp_id number ); # 데이터 입력 begin insert into emp1 values (1); insert into emp1 values (2); insert into emp1 values (3); insert into emp2 values (1); insert into emp2 values (2); insert into emp2 values (3); commit; end; # 입력결과 확인 select * from emp1; select * from emp2; --------------.. 058 - [Oracle PL/SQL] Oracle package - UTL_FILE 사전에 필요한 작업들, 서버 환경에 따라서 폴더 옵션은 다름 to read/write file , we need to create directory create directory only for sys and system user 1- open sqlplus 2- conn as sysdba 3- alter session set container=orclpdb ( the plug db name ) 4- create directory mydir as '/home/oracle' 5- grant READ, WRITE on DIRECTORY MYDIR to public; 6- CREATE THE DIRECTORY ON your computer ( the server ) 7- put empty file sample.. 057 - [Oracle PL/SQL] Oracle package - DBMS_OUTPUT •PUT appends text from the procedure to the current line of the line output buffer. •NEW_LINE places an end-of-line marker in the output buffer. •PUT_LINE combines the action of PUT and NEW_LINE (to trim leading spaces). •GET_LINE retrieves the current line from the buffer into a procedure variable. •GET_LINES retrieves an array of lines into a procedure-array variable. •ENABLE/DISABLE enables a.. 이전 1 ··· 5 6 7 8 9 10 11 ··· 83 다음
