Database/PLSQL (119) 썸네일형 리스트형 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.. 056 - [Oracle PL/SQL] Package - index by tables in packages 아래 샘플은 index by table을 이용한 커서 결과를 저장하는 코드입니다. create or replace package emp_pkg is type emp_table_type is table of employees%rowtype index by binary_integer; procedure get_employees(p_emps out emp_table_type ); end; / create or replace package body emp_pkg is procedure get_employees(p_emps out emp_table_type ) is begin for emp_record in (select * from employees) loop p_emps(emp_record.employee_i.. 이전 1 ··· 5 6 7 8 9 10 11 ··· 15 다음
