하로의 공부방

CREATE FUNCTION F_RPAD
(
	@TEXT VARCHAR(200),
	@MAX_COUNT INT,
	@PAD_CHAR CHAR(1)
)
RETURNS VARCHAR(200)
AS
BEGIN
	DECLARE @RTNVAL VARCHAR(200)
	SELECT @RTNVAL = RIGHT(REPLICATE(@PAD_CHAR, @MAX_COUNT) + @TEXT , @MAX_COUNT)
	RETURN @RTNVAL
END

• FUNCTION의RETURNS형을 변수로 지정해주지 않고 리턴해버릴경우 0를 반환하게된다.

SELECT dbo.F_RPAD('AA', 6, '0')

結果
0000AA

• dense_rank() over (partition by 그룹1, 그룹2)
• 그룹단위의 순위를 번호로 표시
• dense_rank는 같은그룹은 전부 같은 순위로 하며, 다음그룹은 현재 순위의 다음 순위로 시작
• 그룹단위의 표시 수를 제어할 때 사용할 수 있다

예제 SQL>
select CA1, CA2, danse_rank() over (partition by CA1, CA2)
from
(
select 'A' CA1, 'B' CA2 from dual
union all
select 'A' CA1, 'B' CA2 from dual
union all
select 'A' CA1, 'C' CA2 from dual
union all
select 'A' CA1, 'C' CA2 from dual
union all
select 'A' CA1, 'D' CA2 from dual
)
->
1: A B 1
2: A B 1
3: A C 2
4: A C 2
5: A D 3

• rpad(문자열 , 표지수, 매꿔줄문자)
• 문자열을 오른쪽에서 6번째문자까지 표현
• 문자열이 표지수 보다 적을 경우 매꿔줄문자를 대신 체워서 표지수까지 표현
• 문자열의 표지수위치가 멀티바이트 문자일 경우 대상 멀티바이트문자를 제외하고, 빈자리를 매꿔줄 문자로 체워서 표시
• 바이트단위로 문자수를 계산
• 멀티바이트에서의 문자끈김으로인한 에러처리를 안해줘도 됨
  

예제 SQL>
select rpad('abcd', 6, '_") from dual;
-> abcd__
select rpad('abcde한글', 6, '_") from dual;
-> abcde_
select rpad('abcd한글', 6, '_") from dual;
-> abcd한

• sum(대상열) over (partition by 그룹1, 그룹2 order by 누적순서)
• "누적순서"의 순서로 "대상열"을 순차적으로 합한 값을 표현
• partition by 를 넣는것으로 그룹단위의 누적합을 구할 수 있다..
  

예제 sql)
select CA1, CA2, sum(CLM1) over (partition by CA1, CA2 order by CLM2)
from (
select 1 CLM1, 1 CLM2, 'A' CA1, 'B' CA2 from dual;
union all
select 1 CLM1, 2 CLM2, 'A' CA1, 'B' CA2 from dual;
union all
select 1 CLM1, 3 CLM2, 'A' CA1, 'C' CA2 from dual;
union all
select 1 CLM1, 4 CLM2, 'A' CA1, 'C' CA2 from dual;
union all
select 1 CLM1, 5 CLM2, 'A' CA1, 'C' CA2 from dual;
)
->
1:  A B 1
2:  A B 2
3:  A C 1
4:  A C 2
5:  A C 3

CONNECT sys/password@w2k1 AS SYSDBA
CREATE OR REPLACE DIRECTORY EXTRACT_DIR AS 'c:\oracle\extract';
GRANT READ, WRITE ON DIRECTORY EXTRACT_DIR TO SCOTT;
GRANT EXECUTE ON UTL_FILE TO SCOTT;

CONNECT scott/tiger@w2k1
CREATE OR REPLACE PROCEDURE EMP_CSV AS
  CURSOR c_data IS
    SELECT empno,
           ename,
           job,
           mgr,
           TO_CHAR(hiredate,'DD-MON-YYYY') AS hiredate,
           sal,
           comm,
           deptno
    FROM   emp
    ORDER BY ename;
    
  v_file  UTL_FILE.FILE_TYPE;
BEGIN
  v_file := UTL_FILE.FOPEN(location     => 'EXTRACT_DIR',
                           filename     => 'emp_csv.txt',
                           open_mode    => 'w',
                           max_linesize => 32767);
  FOR cur_rec IN c_data LOOP
    UTL_FILE.PUT_LINE(v_file,
                      cur_rec.empno    || ',' ||
                      cur_rec.ename    || ',' ||
                      cur_rec.job      || ',' ||
                      cur_rec.mgr      || ',' ||
                      cur_rec.hiredate || ',' ||
                      cur_rec.empno    || ',' ||
                      cur_rec.sal      || ',' ||
                      cur_rec.comm     || ',' ||
                      cur_rec.deptno);
  END LOOP;
  UTL_FILE.FCLOSE(v_file);
 
EXCEPTION
  WHEN OTHERS THEN
    UTL_FILE.FCLOSE(v_file);
END;
/

EXCEPTION
  WHEN UTL_FILE.INVALID_PATH THEN
    UTL_FILE.FCLOSE(v_file);
    RAISE_APPLICATION_ERROR(-20000, 'File location is invalid.');
   
  WHEN UTL_FILE.INVALID_MODE THEN
    UTL_FILE.FCLOSE(v_file);
    RAISE_APPLICATION_ERROR(-20001, 'The open_mode parameter in FOPEN is invalid.');

  WHEN UTL_FILE.INVALID_FILEHANDLE THEN
    UTL_FILE.FCLOSE(v_file);
    RAISE_APPLICATION_ERROR(-20002, 'File handle is invalid.');

  WHEN UTL_FILE.INVALID_OPERATION THEN
    UTL_FILE.FCLOSE(v_file);
    RAISE_APPLICATION_ERROR(-20003, 'File could not be opened or operated on as requested.');

  WHEN UTL_FILE.READ_ERROR THEN
    UTL_FILE.FCLOSE(v_file);
    RAISE_APPLICATION_ERROR(-20004, 'Operating system error occurred during the read operation.');

  WHEN UTL_FILE.WRITE_ERROR THEN
    UTL_FILE.FCLOSE(v_file);
    RAISE_APPLICATION_ERROR(-20005, 'Operating system error occurred during the write operation.');

  WHEN UTL_FILE.INTERNAL_ERROR THEN
    UTL_FILE.FCLOSE(v_file);
    RAISE_APPLICATION_ERROR(-20006, 'Unspecified PL/SQL error.');

  WHEN UTL_FILE.CHARSETMISMATCH THEN
    UTL_FILE.FCLOSE(v_file);
    RAISE_APPLICATION_ERROR(-20007, 'A file is opened using FOPEN_NCHAR, but later I/O ' ||
                                    'operations use nonchar functions such as PUTF or GET_LINE.');

  WHEN UTL_FILE.FILE_OPEN THEN
    UTL_FILE.FCLOSE(v_file);
    RAISE_APPLICATION_ERROR(-20008, 'The requested operation failed because the file is open.');

  WHEN UTL_FILE.INVALID_MAXLINESIZE THEN
    UTL_FILE.FCLOSE(v_file);
    RAISE_APPLICATION_ERROR(-20009, 'The MAX_LINESIZE value for FOPEN() is invalid; it should ' ||
                                    'be within the range 1 to 32767.');

  WHEN UTL_FILE.INVALID_FILENAME THEN
    UTL_FILE.FCLOSE(v_file);
    RAISE_APPLICATION_ERROR(-20010, 'The filename parameter is invalid.');

  WHEN UTL_FILE.ACCESS_DENIED THEN
    UTL_FILE.FCLOSE(v_file);
    RAISE_APPLICATION_ERROR(-20011, 'Permission to access to the file location is denied.');

  WHEN UTL_FILE.INVALID_OFFSET THEN
    UTL_FILE.FCLOSE(v_file);
    RAISE_APPLICATION_ERROR(-20012, 'The ABSOLUTE_OFFSET parameter for FSEEK() is invalid; ' ||
                                    'it should be greater than 0 and less than the total ' ||
                                    'number of bytes in the file.');

  WHEN UTL_FILE.DELETE_FAILED THEN
    UTL_FILE.FCLOSE(v_file);
    RAISE_APPLICATION_ERROR(-20013, 'The requested file delete operation failed.');

  WHEN UTL_FILE.RENAME_FAILED THEN
    UTL_FILE.FCLOSE(v_file);
    RAISE_APPLICATION_ERROR(-20014, 'The requested file rename operation failed.');

  WHEN OTHERS THEN
    UTL_FILE.FCLOSE(v_file);
    RAISE;
END;

【형식】
    CREATE [OR REPLACE] DIRECTORY 객체명 AS 경로명

• object명에 schema는 기술할 수 없다.
• directory의 길이는 30byte를 넘을 수 없다.
• path명에 file이 위치해 있는 OS의 절대경로를 기술한다.
• CREATE ANY DIRECTORY 시스템 권한이 있아야 한다.

읽어올 데이터 파일이
/temp/data
폴더라면

1. 디렉토리를 생성
SQL> conn system/manager as sysdba

SQL> create directory test_dir
  2  AS '/temp/data';
 
2. 사용자에게 디렉도리 권한을 부여
SQL> grant read,write on directory test_dir to scott;

3. 사용자로 접속
SQL> conn scott/tiger
Connected.

create or replace directory foo_dir as '/tmp';

create directory some_dir;
grant read, write on directory some_dir to micky_mouse;

create or replace directory dir_temp as 'c:\temp';

declare
  f utl_file.file_type;
begin
  f := utl_file.fopen('DIR_TEMP', 'something.txt', 'w');
  utl_file.put_line(f, 'line one: some text');
  utl_file.put_line(f, 'line two: more text');
  utl_file.fclose(f);
end;
/

SQL> create or replace directory UTL_FILE_DIR as '/opt/oracle/utl_file';


Directory created.

SQL> declare
  2    fhandle utl_file.file_type;
  3  begin
  4    fhandle := utl_file.fopen('UTL_FILE_DIR', 'example.txt', 'w');
  5    utl_file.put_line(fhandle , 'eygle test write one');
  6    utl_file.put_line(fhandle , 'eygle test write two');
  7    utl_file.fclose(fhandle);
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> !
[oracle@jumper 9.2.0]$ more /opt/oracle/utl_file/example.txt
eygle test write one
eygle test write two
[oracle@jumper 9.2.0]$

SQL> declare
  2    fhandle   utl_file.file_type;
  3    fp_buffer varchar2(4000);
  4  begin
  5    fhandle := utl_file.fopen ('UTL_FILE_DIR','example.txt', 'R');
  6 
  7    utl_file.get_line (fhandle , fp_buffer );
  8    dbms_output.put_line(fp_buffer );
  9    utl_file.get_line (fhandle , fp_buffer );
 10    dbms_output.put_line(fp_buffer );
 11    utl_file.fclose(fhandle);
 12  end;
 13  /
eygle test write one
eygle test write two

PL/SQL procedure successfully completed.

SQL> select * from dba_directories;

OWNER                          DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ ------------------------------ ------------------------------
SYS                            UTL_FILE_DIR                   /opt/oracle/utl_file
SYS                            BDUMP_DIR                      /opt/oracle/admin/conner/bdump
SYS                            EXP_DIR                        /opt/oracle/utl_file

SQL> drop directory exp_dir;

Directory dropped

SQL> select * from dba_directories;

OWNER                          DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ ------------------------------ ------------------------------
SYS                            UTL_FILE_DIR                   /opt/oracle/utl_file
SYS                            BDUMP_DIR                      /opt/oracle/admin/conner/bdump