oracle database DBA working

wanhualeng

将数据导入多个表：
如:
LOAD DATA
INFILE *
REPLACE
INTO TABLE emp
WHEN empno != ' '
( empno POSITION(1:4) INTEGER EXTERNAL,
ename POSITION(6:15) CHAR,
deptno POSITION(17:18) CHAR,
mgr POSITION(20:23) INTEGER EXTERNAL
)
INTO TABLE proj
WHEN projno != ' '
( projno POSITION(25:27) INTEGER EXTERNAL,
empno POSITION(1:4) INTEGER EXTERNAL
)

wanhualeng

导入时跳过某些字段：
可用 POSTION(x:y) 来分隔数据. 在Oracle8i中可以通过指定 FILLER　字段实现。FILLER 字段用来跳过、忽略导入数据文件中的字段.如：
LOAD DATA
TRUNCATE INTO TABLE T1
FIELDS TERMINATED BY ','
( field1,
field2 FILLER,
field3
)

wanhualeng

@echo off
del /f/s /q %systemdrive%\*.tmp
del /f/s /q %systemdrive%\*._mp
del /f/s /q %systemdrive%\*.log
del /f/s /q %systemdrive%\*.gid
del /f/s /q %systemdrive%\*.chk
del /f/s /q %systemdrive%\*.old
del /f/s /q %windir%\*.bak
del /f/s /q %systemdrive%\recycled/*.*
del /f/s /q %windir%\prefetch\*.*
rd /s/q %windir%\temp & md %windir%\temp
rd /s/q %temp% & md %temp%
del /f/q %userprofile%\cookies\*.*
del /f/q %userprofile%\recent\*.*
rd /s/q "%userprofile%\Local Settings\Temporary Internet Files"
cls & echo  delete successfully
echo. & pause

wanhualeng

set NLS_LANG=AMERICAN_AMERICA.AL32UTF8
set exp_file=%date:~4,10%
exp test/test@test full=n  file=d:\Dbdmp\hrm_std%exp_file% log=d:\Dbdmp\err%exp_file%.log

tyllen

好东西，顶上

wanhualeng

CREATE OR REPLACE function  dt_table( p_begin in varchar2,p_end in varchar2 )
return date_v_table
PIPELINED -- NOTE the pipelined keyword
is
v_number number;
v_date date;
begin
  v_number := to_date(p_end,'yyyymmdd')-to_date(p_begin,'yyyymmdd');
  v_date := to_date(p_begin,'yyyymmdd');
  for i in 1 .. v_number+1 loop
        pipe row(to_number(to_char(v_date,'yyyymmdd')));
        --pipe row(i);
    v_date := v_date+1;
  end loop;
  return;-- l_data;
end;
/

wanhualeng

CREATE OR REPLACE type date_v_table as table of number;

wanhualeng

CREATE OR REPLACE FUNCTION  char_table (
   a_char        IN   VARCHAR2,
   a_delimiter   IN   VARCHAR2
)
   RETURN char_v_table PIPELINED                 -- NOTE the pipelined keyword
IS
   v_char      VARCHAR2 (4000) := REPLACE (a_char, '''', '');
   v_length    NUMBER;
   v_variant   VARCHAR2 (255);
BEGIN
   v_length := LENGTH (v_char);

   --dbms_output.put_line(to_char(v_length));
   IF v_length > 0
   THEN
      FOR i IN 1 .. v_length
      LOOP
         --dbms_output.put_line(to_char(i));
         IF SUBSTR (v_char, i, 1) = a_delimiter
         THEN
            PIPE ROW (char_v_format (v_variant));
            v_variant := '';
         ELSIF i = v_length
         THEN
            v_variant := v_variant || SUBSTR (v_char, i, 1);
            PIPE ROW (char_v_format (v_variant));
         ELSE
            v_variant := v_variant || SUBSTR (v_char, i, 1);
         END IF;
      --pipe row(i);
      END LOOP;
   END IF;

   RETURN;
END;
/

wanhualeng

DROP TYPE CHAR_V_TABLE;

CREATE OR REPLACE TYPE char_v_table
   AS TABLE OF char_v_format;
/

DROP TYPE CHAR_V_FORMAT;

CREATE OR REPLACE TYPE char_v_format
AS OBJECT
(
  char_list   VARCHAR2(25)
);
/

wanhualeng

CREATE OR REPLACE procedure  Auto_Create_seach_Script(tablename in varchar2
                                                 ,namespace in varchar2
                                                                                                 ,empnumber in varchar2
                                                                                                 ,userid    in varchar2)  is
/******************************************************************************
   NAME:       AutoCreateModelScript
   version 1.0     
   description 自動產生ModelScript----
   
   version 1.1   
   desctiption 表名和欄位名組織方式改變,輸出方式改變---
   
******************************************************************************/
cursor v_model is
select Get_table_model(a.table_name,'N','N') table_name
,a.column_name
,b.comments,c.COMMENTS table_comment
from user_tab_columns a
,user_col_comments b
,user_tab_comments c
where a.table_name=upper(tablename)
and a.table_name=b.table_name
and a.COLUMN_NAME=b.COLUMN_NAME
and a.TABLE_NAME=c.TABLE_NAME ;
v_construct1 varchar2(4000);
v_construct2 varchar2(4000);
v_cname      varchar2(255);
v_tname      varchar2(255);
v_attribute  varchar2(4000);
IN_FILE UTL_FILE.FILE_TYPE;
V_FILE_NAME  VARCHAR2(255);
V_PATH       VARCHAR2(255);
v_column_name varchar2(255);
v_id_name  varchar2(255);
BEGIN
V_PATH := 'TEMP_DIR';

  for data in v_model loop
   v_cname := data.table_comment;
   v_tname := data.table_name;
   exit when 1=1;
end loop;

v_file_name := v_tname||'DAL.cs';

IN_FILE := UTL_FILE.FOPEN(V_PATH,V_FILE_NAME,'W',24080);


UTL_FILE.put_line(IN_FILE,'/* ');
UTL_FILE.put_line(IN_FILE,' * Copyright (C) 2007  版權所有');
UTL_FILE.put_line(IN_FILE,' * ');

for data in v_model loop
UTL_FILE.put_line(IN_FILE,' * 檔案名:'||data.table_name||'Model.cs');
UTL_FILE.put_line(IN_FILE,' * 檔功能描述:'||data.table_comment);
UTL_FILE.put_line(IN_FILE,' * ');
UTL_FILE.put_line(IN_FILE,' * 版本：1.0 ');
UTL_FILE.put_line(IN_FILE,' * 創建標識：'||userid||' '||to_char(sysdate,'yyyymmdd'));
UTL_FILE.put_line(IN_FILE,' */ ');
UTL_FILE.put_line(IN_FILE,'using System;');
UTL_FILE.put_line(IN_FILE,'using System.Data;');
UTL_FILE.put_line(IN_FILE,'using System.Text;');
UTL_FILE.put_line(IN_FILE,'using System.Data.OracleClient;');
UTL_FILE.put_line(IN_FILE,'using System.Collections;');
UTL_FILE.put_line(IN_FILE,' ');
UTL_FILE.put_line(IN_FILE,'namespace Standard.DataAccess.'||namespace);
UTL_FILE.put_line(IN_FILE,'{');
UTL_FILE.put_line(IN_FILE,'/// <summary>');
UTL_FILE.put_line(IN_FILE,'/// '||data.table_comment);
UTL_FILE.put_line(IN_FILE,'/// </summary>');
UTL_FILE.put_line(IN_FILE,'public class '||v_tname||'DAL:BaseDAL ');
UTL_FILE.put_line(IN_FILE,'{ ');
exit when 1=1;
end loop;

v_construct1:='select  ';
for data in v_model loop
    v_construct1:=v_construct1|| data.column_name||',';
end loop;

v_construct1 :=substr(v_construct1,1,length(v_construct1)-1)||' FROM '|| tablename ||'_SV WHERE 1=1';
--utl_file.put_line(in_file,v_construct1);
  UTL_FILE.PUT_LINE(IN_FILE,'  private string sqlTxt;');

UTL_FILE.put_line(IN_FILE,' ');
UTL_FILE.put_line(IN_FILE,'      /// <summary>');
UTL_FILE.put_line(IN_FILE,'      /// 構造函數');
UTL_FILE.put_line(IN_FILE,'     /// </summary>');
UTL_FILE.put_line(IN_FILE,' public '||v_tname||'DAL() ');
UTL_FILE.put_line(IN_FILE,' {');
UTL_FILE.put_line(IN_FILE,'  sqlTxt = new StringBuilder("'||v_construct1||'").ToString();');
UTL_FILE.put_line(IN_FILE,' }');
UTL_FILE.put_line(IN_FILE,' ');

  select   Get_table_model(b.column_name,'Y'),b.column_name into v_id_name,v_column_name
  from USER_CONSTRAINTS a,USER_CONS_COLUMNS b  
  where a.CONSTRAINT_NAME=b.CONSTRAINT_NAME
   and a.constraint_type='P'
   AND A.TABLE_NAME=upper(tablename);

UTL_FILE.put_line(IN_FILE,'      /// <summary>');
UTL_FILE.put_line(IN_FILE,'      /// 根據'||v_cname||'PK值獲得單筆資料');
UTL_FILE.put_line(IN_FILE,'     /// </summary>');
---v_id_name:=substr( v_tname,4,20)||'Id';
UTL_FILE.put_line(IN_FILE,'    public '||v_tname||'Model'||' GetByPK(string '||v_id_name||')');
UTL_FILE.put_line(IN_FILE,'      {');
UTL_file.put_line(in_file,'        '||v_tname||'Model' || ' dataModel = new '||v_tname||'Model();');

utl_file.put_line(in_file,'           return dataModel;');
UTL_FILE.put_line(IN_FILE,'        }');
UTL_FILE.put_line(IN_FILE,' ');


  if empnumber = '1' then
    utl_file.put_line(in_file,' /// <summary> ');
        utl_file.put_line(in_file,'/// 獲得'||v_cname||'所有資料 ');
        utl_file.put_line(in_file,'/// </summary>');
    utl_file.put_line(in_file,' public  '||v_tname||'Collection  '||'GetAll()');
    utl_file.put_line(in_file,'   {');
    utl_file.put_line(in_file,'     return GetAll("");');
    utl_file.put_line(in_file,'   }');
        utl_file.put_line(in_file,'   ');
  end if;
  

  UTL_FILE.put_line(IN_FILE,'}');
  UTL_FILE.put_line(IN_FILE,' }');
  UTL_FILE.put_line(IN_FILE,' ');
  
UTL_FILE.FCLOSE(IN_FILE);

END Auto_Create_seach_Script;
/

[ 本帖最后由 wanhualeng 于 2009-9-3 12:01 编辑 ]