웹 개발과 관련된 모든 것들

CHR(9) => DBMS.output에서 탭값 출력하기

엑셀에 바로 붙여넣기 귀찮아서 ... ㅋㅋ;

값을 쉽게 구하자..

 또한 & 표시는 CHR(38) 번.

PL/SQL 구문은 & 표시는 값을 입력받도록 되어 있어

REPLACE 구문으로 입력받은 값을 치환할 때 컴파일이 정상적으로 되지 않을수 있음...

1. pkg_backup.sh 파일내용

###  오라클 환경 설정(.profile 참조)

###

sqlplus -SILENT 아뒤/패스워드

@/oracle/backup_pkg/get_pkg_name.sql > names.txt

yd=`date +%Y%m%d`
mkdir -p /oracle/backup_pkg/pkgs/$yd
for get_name in `cat names.txt`
do
        echo "$get_name Package Header Backup"
        echo "set heading off ;" > get_pkg.sql

        echo "set linesize 10000;" >> get_pkg.sql
        echo "set pagesize 50000; " >> get_pkg.sql

        echo "set feedback off; " >> get_pkg.sql
        echo "set verify off;" >> get_pkg.sql

        echo "SELECT TEXT FROM ALL_SOURCE " >> get_pkg.sql
        echo "WHERE TYPE='PACKAGE'" >> get_pkg.sql
        echo "AND   OWNER=''"    >> get_pkg.sql
        echo "AND   NAME='$get_name';" >> get_pkg.sql

        echo "exit; " >> get_pkg.sql

        chmod +x get_pkg.sql

        echo "CREATE OR REPLACE " > /oracle/backup_pkg/pkgs/$yd/$get_name.pks

        sqlplus -SILENT 아뒤/패스워드 @get_pkg.sql >> /oracle/backup_pkg/pkgs/$yd/$get_name.pks

        echo "$get_name Package Body Backup"
        echo "set heading off ;" > get_pkg.sql

        echo "set linesize 10000;" >> get_pkg.sql
        echo "set pagesize 50000; " >> get_pkg.sql

        echo "set feedback off; " >> get_pkg.sql
        echo "set verify off;" >> get_pkg.sql

        echo "SELECT TEXT FROM ALL_SOURCE " >> get_pkg.sql
        echo "WHERE TYPE='PACKAGE BODY'" >> get_pkg.sql
        echo "AND   OWNER=''"    >> get_pkg.sql
        echo "AND   NAME='$get_name';" >> get_pkg.sql

        echo "exit; " >> get_pkg.sql

        chmod +x get_pkg.sql

        echo "CREATE OR REPLACE " > /oracle/backup_pkg/pkgs/$yd/$get_name.pkb

        sqlplus -SILENT 아뒤/패스워드 @get_pkg.sql >> /oracle/backup_pkg/pkgs/$yd/$get_name.pkb

done
cd -

2. get_pkg_name.sql  파일내용

-- 헤더 정보 출력되지 않게 설정
set heading off;

-- 페이지 사이즈를 최대한으로
set pagesize 10000;

-- 출력되는 행수 삭제
set feedback off;

set verify off;

-- 패키지의 이름을 가지고 옴
SELECT NAME FROM ALL_SOURCE
WHERE TYPE='PACKAGE'
AND   OWNER=''
GROUP BY NAME;

-- 종료
exit;

프로시져백업.zip

레코드가 있으면 UPDATE하고 없으면 INSERT해라 였는데,
오라클9i에서 새로 추가된 MERGE INTO 구문을 사용하면

대략 구문은 아래와 같습니다.
pl/sql환경에서는 위와 같이 사용해도 되지만,
sql을 꼭 사용해야한다면 이 방법이 낫겠네요.

[SYNTAX]

MERGE INTO 테이블 A                        -- update 또는 insert할 테이블
USING (테이블명 | SUBQUERY | 뷰)  B  -- 비교 대상
ON(A와 B의 조인식)                             -- 대상테이블과 비교테이블의 조인식
WHEN MATCHED THEN                       -- on절에서 일치하는 로우가 있다면
    UPDATE SET = , , ....                       -- 업데이트하고,
WHEN NOT MATCHED THEN                -- 일치하는 로우가 없다면
    INSERT INTO(컬럼명)                       -- insert를
    VALUES(값, 값, ...)                           -- 이 값으로 넣어라.

f_u1:
   merge into tb_result a
        using dual             b
           on ( a.plt_no = '$in_plt_no' and
                a.srt_id = '$in_srt_id' )
   when matched then
      update set srt_qty     = srt_qty + to_number('$in_srt_qty'),
                 fin_chgr_id = '$in_user_id',
                 fin_chg_dtm = to_char(sysdate, 'yyyymmddhh24miss')
   when not matched then
      insert ( plt_no       , srt_id            , proj_no         , blk_no            ,
               sass_no      , prcd_sass_no      , ptlst_no        , pcs_gb            ,
               stype        , srt_qty           , uom_wgt         , bom_org_cmpo      ,
               io_gb        , req_gb            , ptlst_stat      , req_ser           ,
               lug_gb       , mpart_plt_no      , newprod_qty     , reprod_qty        ,
               imprv_yn     , plt_incd_yn       , fs_srt_usr_id   , fs_srt_dtm        ,
               fs_inpr_id )
      values ( '$in_plt_no' , '$in_srt_id'      , '$in_proj_no'   , '$in_blk_no'      ,
               '$in_sass_no', '$in_prcd_sass_no', '$in_ptlst_no'  , '$in_pcs_gb'      ,
               '$in_stype'  , to_number('$in_srt_qty'), to_number('$in_uom_wgt'), '$in_bom_org_cmpo',
               '$in_io_gb'  , '$in_req_gb'      , 'C'             , to_number('$in_req_ser'),
               '$in_lug_gb' , '$in_mpart_plt_no', to_number('$in_new_srt_qty'), to_number('$in_re_srt_qty'),
               'N'          , 'Y'               , '$in_user_id'   , to_char(sysdate, 'yyyymmddhh24miss'),
               '$in_user_id' )
   ;

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

f_u2:
   declare cursor csr1 is
      select a.lug_gb       as lug_gb  ,
             a.ptlst_no     as lug_type,
             '$in_bizcls'   as bizcls  ,
             sum(a.srt_qty) as srt_qty
      from   tb_result a
      where  a.plt_no = '$in_plt_no'
      group  by a.lug_gb, a.ptlst_no
      $in_semicolon

      begin
         for rec1 in csr1 loop
            update tb_result
            set    stk_qty     = stk_qty - rec1.srt_qty * decode('$in_out_gb', 'O', 1, -1),
                   fin_chgr_id = '$in_user_id',
                   fin_chg_dtm = to_char(sysdate, 'yyyymmddhh24miss')
            where  lug_gb      = rec1.lug_gb
            and    lug_type    = rec1.lug_type
            and    bizcls      = rec1.bizcls
            $in_semicolon

            if sql%notfound then
               insert into tb_stk
                         ( lug_gb    ,
                           lug_type  ,
                           bizcls    ,
                           stk_qty   ,
                           fs_inpr_id    )
                  values ( rec1.lug_gb  ,
                           rec1.lug_type,
                           rec1.bizcls  ,
                           rec1.srt_qty * decode('$in_out_gb', 'O', -1, 1),
                           '$in_user_id' )
               $in_semicolon
            end if $in_semicolon
         end loop $in_semicolon
      end $in_semicolon
   ;

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

테이터를 넣어야할 테이블에

값이 이미 있으면 Update

값이 없으면 Insert

MERGE INTO table_name alias                               --테이블 명만 올수 있음(셀렉트 쿼리 No)
          USING (table|view|subquery) alias                --일반적으로 쓰는 쿼리 Ok
          ON (join condition)                                        --where 절이라고 생각하면됨(조건에 서브 OK)
          WHEN MATCHED THEN                              --데이터 중복건이 있는경우 Update
                       UPDATE SET col1 = val1[, col2 = val2…]
          WHEN NOT MATCHED THEN                      -- 중복건이 없는 경우 처리 Insert
                       INSERT (컬럼리스트) VALUES (값들....);

ex)

   MERGE INTO CRCD_WF_CARD A            
   USING DUAL                           
      ON ( A.WF_NO = ? AND A.CRD_NO =? )
    WHEN MATCHED    THEN                
         UPDATE  SET                    
                 A.CRD_TYPE       =?    
               , A.FML_SSN        =?    
               , A.FMLHG_NM       =?    
               , A.FMLENG_NM      =?    
               , A.OCRD_NO        =?    
               , A.DSN_CD         =?    
               , A.DSN_NM         =?    
               , A.BRD_CD         =?    
               , A.CRD_GRD        =?    
               , A.INTGLMT_AMT    =?    
               , A.FML_STLACCT_NO =?    
               , A.CRD_ISSUE_DT   =?    
    WHEN NOT MATCHED THEN               
         INSERT (                       
                 A.WF_NO                
               , A.CRD_NO               
               , A.CRD_TYPE             
               , A.FML_SSN              
               , A.FMLHG_NM             
               , A.FMLENG_NM            
               , A.OCRD_NO              
               , A.DSN_CD               
               , A.DSN_NM               
               , A.BRD_CD               
               , A.CRD_GRD              
               , A.INTGLMT_AMT          
               , A.FML_STLACCT_NO       
               , A.CRD_ISSUE_DT         
                                        
       ) VALUES ( ?, ?, ?, ?, ?, ?, ?,  
                  ?, ?, ?, ?, ?, ?, ?  )

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

ex2)

    MERGE INTO TC_CUSTAGENT A
   USING (SELECT BUSI_CODE, CUST_CODE, CHPE_NAME, CUST_PHNB, CUST_FANU
                FROM TB_ESTMAST
                WHERE BUSI_CODE = '___parameter___'

                     AND CUST_CODE = '____parameter___') B
    ON (A.BUSI_CODE = B.BUSI_CODE
           AND A.AFON_COMP = B.CUST_CODE
           AND A.SEQU_NUMB = (SELECT MAX(SEQU_NUMB)
                                            FROM TC_CUSTAGENT
                                            WHERE BUSI_CODE = A.BUSI_CODE
                                                  AND AFON_COMP = A.AFON_COMP))
   WHEN MATCHED THEN
         UPDATE
         SET  A.CHPE_NAME = B.CHPE_NAME
                ,A.CABL_PHNB = B.CUST_PHNB
                ,A.FAXM_NUMB = B.CUST_FANU
    WHEN NOT MATCHED THEN
         INSERT (  A.AFON_COMP
                       ,A.CHPE_NAME
                       ,A.CABL_PHNB
                       ,A.FAXM_NUMB     
                     )
         VALUES (  B.CUST_CODE
                        ,B.CHPE_NAME      
                        ,B.CUST_PHNB
                        ,B.CUST_FANU    
                      );

>>>>>>>>>>>>>>>>
 
 MERGE INTO
실제 적용하면서 발견한 문제점 (ORACLE 9.2.0.3.0 에서)

9.2.0.4.0 버젼에선 아래와 같은 문법으로도 MERGE INTO 가 정상적으로 동작하였다.

MERGE INTO POPULAR_KEYWORD
USING DUAL B
ON (FIX_YN = 'Y' AND CATEGORY = 'ALL' AND RANK = '1')
WHEN MATCHED THEN
  UPDATE SET REG_DATE = SYSDATE
WHEN NOT MATCHED THEN
  INSERT (CATEGORY,KEYWORD,RANK, COUNT, REG_DATE)
      VALUES ('ALL','TEST22','1','100',SYSDATE)

즉, ON 절에서 값이 NULL 이더라도 WHEN NOT MATCHED THEN 절을 정상적으로 수행하였는데

이상하게 다른서버(9.2.0.3.0) 에선 ON절의 값이 NULL 이면 정상적인 루틴을 밟지 못하드라..

즉, INSERT 문을 수행하지 못했다.

[ 0 rows Merged ]

그래서 아래와 같이 USING절에서 NULL값이 리턴하지 않게 변경해주고

ON 절에서 A 와 B 를 JOIN 해주니까 정상적으로 동작한다.

MERGE INTO POPULAR_KEYWORD2 A
      USING (SELECT 'ALL' CATEGORY, '4' RANK, 'Y' FIX_YN FROM   DUAL) B
      ON (A.CATEGORY = B.CATEGORY AND A.RANK = B.RANK AND A.FIX_YN = B.FIX_YN)
      WHEN MATCHED THEN
        UPDATE SET REG_DATE = SYSDATE
      WHEN NOT MATCHED THEN
        INSERT (CATEGORY,KEYWORD,RANK, COUNT, REG_DATE)
            VALUES ('ALL','TEST22','4','100',SYSDATE)

[ 1 rows Merged ]

결론 ==> USING 다음의 SELECT 문의 결과 데이타는 반드시 존재하여야 한다 (NULL 불가).

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

9i이상에서는 데이타가 존재할 경우 갱신 없으면 등록하는 명령어로

MERGE INTO ~ USING ~ ON ~ WHEN ~ 명령어가 있습니다.

MERGE INTO oc_whslr_trd_rslt A
USING (
SELECT std_yymm, cust_nbr, whlsr_cust_nbr, sol_tp_cd, sol_cd,chnn_rt,

src_tp_cd,org_cd, emp_nbr,sell_amt, qty
FROM oc_temp_whslr_trd_rslt
) B
ON (

A.std_yymm = B.std_yymm AND

A.cust_nbr = B.cust_nbr AND

A.whlsr_cust_nbr = B.whlsr_cust_nbr AND

A.sol_cd = B.sol_cd AND

A.sol_tp_cd = B.sol_tp_cd AND

A.chnn_rt = B.chnn_rt
)
WHEN MATCHED THEN

UPDATE SET A.sell_amt = A.sell_amt + B.sell_amt,
A.qty = A.qty + B.qty
WHEN NOT MATCHED THEN
INSERT (
A.std_yymm,
A.cust_nbr,
A.whlsr_cust_nbr,
A.sol_cd,
A.sol_tp_cd,
A.chnn_rt,
A.src_tp_cd,
A.org_cd,
A.emp_nbr,
A.sell_amt,
A.qty,
A.drtn_amt,
A.drtn_qty,
A.last_chng_emp,
A.last_chng_dt )
VALUES (
B.std_yymm,
B.cust_nbr,
B.whlsr_cust_nbr,
B.sol_cd,
B.sol_tp_cd,
B.chnn_rt,
B.src_tp_cd,
B.org_cd,
B.emp_nbr,
B.sell_amt,
B.qty,
0,
0,
'CRMSP',
TO_CHAR(sysdate,'YYYYMMDD')
);

MERGE into KAC_PERSON a USING
(select user_id, OPER_FG, VARDATA, NUMDATA from KAC_PERSON
where user_id='57CCF7ADE63F3AC1' AND OPER_FG = 'MAIL_CAPACITY') b
ON (a.user_id=b.user_id and a.OPER_FG=b.OPER_FG)
WHEN MATCHED THEN UPDATE set a.NUMDATA = 15
WHEN NOT MATCHED THEN INSERT (a.user_id,a.OPER_FG,a.VARDATA,a.NUMDATA)
VALUES ('57CCF7ADE63F3AC1','MAIL_CAPACITY','mail',50)

CREATE TABLE KAC_PERSON
(
  USER_ID    CHAR(16) NOT NULL,
  OPER_FG    VARCHAR2(20) NOT NULL,
  VARDATA    VARCHAR2(30),
  NUMDATA    NUMBER(4),
  CONSTRAINT PK_KAC_PERSON PRIMARY KEY (USER_ID, OPER_FG)
);

>>>>>>>>>>>>>>>>>>>>>>>>>

MERGE INTO tb_target a
USING (
   select proj_no, spl_no, tag_no, msch_actv, instl_plt_no, wk_ord_dt
   from   tb_source
   where  lot_no  = 'AJ'
   and    proj_no = '1697'
   ) b
ON (   a.proj_no = b.proj_no
   and a.spl_no  = b.spl_no
   and a.tag_no  = b.tag_no)
WHEN MATCHED THEN
   update
   set    a.mk_fndt   = b.wk_ord_dt,
          a.matdlv_dt = b.wk_ord_dt,
          a.gr_dt     = b.wk_ord_dt,
          a.stk_dt    = b.wk_ord_dt,
          a.gi_dt     = b.wk_ord_dt
WHEN NOT MATCHED THEN
   insert(a.proj_no, a.spl_no, a.tag_no, a.msch_actv, a.instl_plt_no,
          a.mk_fndt, a.matdlv_dt, a.gr_dt, a.stk_dt, a.gi_dt)
   values(b.proj_no, b.spl_no, b.tag_no, b.msch_actv, b.instl_plt_no,
          b.wk_ord_dt,b.wk_ord_dt,b.wk_ord_dt,b.wk_ord_dt,b.wk_ord_dt)

mom이라는 사용자의

A, B, C 라는 테이블을 사용하기 위해

다른 사용자들은 mom.A, mom.B 등으로 사용하는데

A, B 로 바로 쓸수 있게 하는

synonym을 일괄 생성하는 방법

================

sqlplus 로 접속해서

 spool make_synonyms.sql

SELECT 'create public synonym ' || table_name || ' for ' || table_name || ';'
  FROM user_tables;

spool off

@make_synonyms

================

다른 방법은 current schema를 쓰는 방법

alter session set current_schema=scott;

==================

USER_B에게 USER_A.sample의 select 권한 부여

GRANT SELECT ON USER_A.sample_table TO USER_B;

테이블 스페이스 생성

SQL> CREATE TABLESPACE XINET_DATA
    DATAFILE /경로/xinet_01.dbf'
    SIZE 100M
    AUTOEXTEND ON
    NEXT 30M
    SEGMENT SPACE MANAGEMENT AUTO;

SEGMENT SPACE MANAGEMENT AUTO 설정을 해야 SHRINK를 나중에 사용 가능 ~~

 

테이블 스페이스 변경

ALTER USER XINET DEFAULT TABLESPACE XINET_DATA;

사용 테이블 스페이스 조회

SELECT username, default_tablespace, temporary_tablespace FROM DBA_USERS;

### oracle

sqlplus -SILENT 아이디/패스워드 <<-EOF
 WHENEVER SQLERROR EXIT 1
  WHENEVER OSERROR EXIT 1
   SET ECHO ON
   EXECUTE 호출패키지명();

   EXIT 0
   EOF

   EXITCODE=$?
   export EXITCODE

   if [ "$EXITCODE" -ne 0 ]
   then
     echo "ERROR: SQL*Plus exit code: $EXITCODE"
     echo ""
     echo "**** PROCEDURE RUN ABORTED at `date` !!! ****"
   fi
### end

클론 등록 방법 (클론은 root로 등록)

매일 아침 6시에 실행한다면

0 6 * * * su - oracle -c /home/test.sh

주석문을 볼수있는 data dictionary

dictionary 상에 정보 조회를 통해 현재 comment(주석)이 등록 되어 있는지 확인 가능하다.