Convert Seconds to Hours and Minutes and Seconds

Данная функция позволяет преобразовать количество секунд в количество часов : минут : секунд.

CREATE FUNCTION [SEC2HMS] (@SEC FLOAT)
RETURNS VARCHAR(30)
with schemabinding
AS
/* Переводит секунды в часы:минуты:секунды */
BEGIN
declare @Hours FLOAT, @Minutes FLOAT, @Seconds FLOAT
declare @vHours VARCHAR(20), @vMinutes VARCHAR(2), @vSeconds VARCHAR(2)

set @SEC=@SEC;
set @Hours = FLOOR(@SEC/60/60);
set @Minutes = FLOOR((@SEC/60/60 - FLOOR(@SEC/60/60))*60);
set @Seconds = FLOOR((((@SEC/60/60 - FLOOR(@SEC/60/60))*60) - FLOOR((@SEC/60/60 - FLOOR(@SEC/60/60))*60))*60);

set @vHours = case when len(@Hours) = 1 then '0'+cast(@Hours as varchar(20))
				   else cast(@Hours as varchar(20)) end;
set @vMinutes = case when len(@Minutes) = 1 then '0'+cast(@Minutes as varchar(20))
				   else cast(@Minutes as varchar(20)) end;
set @vSeconds = case when len(@Seconds) = 1 then '0'+cast(@Seconds as varchar(20))
				   else cast(@Seconds as varchar(20)) end;

 RETURN @vHours + ':' + @vMinutes + ':' + @vSeconds;
end

Пример вызова функции, если переводим секунды в часы, минуты и секунды:

select SEC2HMS(3715412) as hms;
hms
--
1032:03:32
Реклама

COUNT WORKING DAYS IN MONTH (количество рабочих дней в месяце)

Данная функция возвращает количество рабочих дней в месяце, который берется из указанной даты.
Выходными днями функция считает СУББОТУ (7) и ВОСКРЕСЕНЬЕ (1).

Пример вызова функции:

select WORKDAYS ('2014-02-10');
--
20
CREATE FUNCTION WORKDAYS (@DAT DATETIME)
RETURNS INT
with schemabinding
AS
/* Возвращает количество рабочих дней в месяце по указанной дате */
BEGIN
DECLARE @RET INT;
set @RET = null;

select @RET = count(*) from (
select cast(convert(varchar(4),year(@DAT)) + '-' + convert(varchar(2),month(@DAT)) + '-' + convert(varchar(2),calendar.dd) as date) dt
from (
select 1 dd union
select 2 dd union
select 3 dd  union
select 4 dd  union
select 5 dd  union
select 6 dd  union
select 7 dd  union
select 8 dd  union
select 9 dd  union
select 10 dd  union
select 11 dd  union
select 12 dd  union
select 13 dd  union
select 14 dd  union
select 15 dd  union
select 16 dd  union
select 17 dd  union
select 18 dd  union
select 19 dd  union
select 20 dd  union
select 21 dd  union
select 22 dd  union
select 23 dd  union
select 24 dd  union
select 25 dd  union
select 26 dd  union
select 27 dd  union
select 28 dd  union
select 29 dd  union
select 30 dd  union
select 31 dd ) calendar
where calendar.dd <= DAY(convert(date, convert(varchar, dateadd(month,1,dateadd(day,1-day(@DAT),@DAT))-1)))
and datepart(w,cast(convert(varchar(4),year(@DAT)) + '-' + convert(varchar(2),month(@DAT)) + '-' + convert(varchar(2),calendar.dd) as date)) NOT IN (1,7)) ap;
RETURN @RET;
END
GO

Start the process when it is not running (by vbscript)

Иногда требуется проверить запущен ли процесс (например, notepad.exe) под пользователем (user1), чтобы случайно не запустить его дважды, или вообще запустить его, если он отсутствует в списке процессов. Я описал пример запуска java.exe для компиляции apex.war (запуска Oracle Apex listener 2.0.3), при условии, что база данных APEX (Oracle) работает и процесс не запущен. Для проверки запущен ли процесс, написал функцию SearchProcess, которая возвращает 1, если процесс запущен.

'(с) 19.05.2014
Dim ObjShell, Shell
Dim ObjProc
Dim StrORA
Dim dblist
Dim chekdir
Dim procname
Dim ownername
Dim Res
Dim JavaHome

'Указываем путь к java.exe нужно нам версии
JavaHome="c:\Program Files\Java\jdk1.6.0_18\bin\java.exe"

'Подключение к базе (Oracle)
Set ObjShell = CreateObject("WScript.Shell")
WScript.Sleep 600
StrORA = "sqlplus /@APEX as sysdba @x:\apexlistener\connect.sql" 'файл connect.sql содержит следующий код: select sysdate from dual; exit
Set ObjProc = ObjShell.Exec(StrORA)

input = ""
strOutput = ""
'Читаем output нашего соединения к базе
Do While Not ObjProc.StdOut.AtEndOfStream
input = input &amp; vbCrLf &amp; ObjProc.StdOut.ReadLine
Loop

'Если соединение завершилось успешно
If InStr(input, "SYSDATE") Then
'Проверяем запущен ли процесс под этим владельцем
Res = SearchProcess("java.exe", "orauser")
If Res 1 Then
StrORA = JavaHome &amp; " -Dapex.home=x:\apex\8090 -Dapex.port=8090 -Dapex.images=x:\apex\images -jar apex.war"
Set ObjProc = ObjShell.Exec(StrORA)
End IF
Else
'WScript.Echo "ORACLE is DISABLE!"
End If

Function SearchProcess(procname, ownername)
'(c) 19.05.2014
'What's: проверяет запущен ли процесс на компьютере и кто его владелец
' procname - имя процесса
' ownername - имя пользователя, под кем запущен процесс

Dim strComputer ' Имя компьютера
Dim objService ' Объект SWbemServices
Dim colProcesses ' Коллекция экземпляров класса WMI
Dim objProcess ' Элемент коллекции
Dim WshShell ' Объект WshShell
Dim cnt 'счетчик
Dim Return
Dim strNameOfUser

Set WshShell = WScript.CreateObject("WScript.Shell")

' "." - локальный компьютер
strComputer = "."

' Подключаемся к пространству имен WMI
Set colProcesses = GetObject("winmgmts:" &amp; "{impersonationLevel=impersonate}!\\" &amp; strComputer &amp; "\root\cimv2").ExecQuery("SELECT * FROM Win32_Process")

if colProcesses.Count &gt; 0 Then

'Перебираем коллекцию
For Each objProcess in colProcesses

'Проверяем искомый процесс
If objProcess.Name = procname Then
' Проверяем владельца процесса
Return = objProcess.GetOwner(strNameOfUser)
If Return = 0 Then
If strNameOfUser = ownername Then
SearchProcess = 1
'WScript.Echo "Process " &amp; objProcess.Name &amp; " is owned by " &amp; "\" &amp; strNameOfUser &amp; "."
End If
End If
End If
Next
End If

End Function 

Load data from file OS (txt, csv) using UTL_FILE

Написал процедуру LOADFROMFILE используя функционал пакета UTL_FILE, позволялющий быстро и практично загружать данные из текстового файла ОС (txt, csv) с разделителями (delimeters) в указанную таблицу. Процедура имеет несколько важных проверок, которые сократят ваше время, например, она сама считает количество столбцов в файле и сравнивает с количеством столбцов в таблице; конвертирует записи в тип DATE, где это нужно; возвращает сообщения об ошибках связанных с неверностью входных параметров, отсутствия файла и т.п.

 

CREATE OR REPLACE PROCEDURE LOADFROMFILE(dir_name VARCHAR2, file_name VARCHAR2, owner_name VARCHAR2, tab_name VARCHAR2, delim CHAR, exception_msg out VARCHAR2)
AS
/*
dir_name - Oracle директория расположения файлов
file_name - имя текстового файла
owner_name - имя владельца таблицы БД Oracle
tab_name - имя таблицы БД Oracle
delim - разделитель (delimeter) данных в файле (например, ';')
exception_msg - это возвращаемое сообщение о состоянии выполнения процедуры, позволяющзая понять, где мы допустили ошибку
*/
V_LINE CLOB;
F1 UTL_FILE.FILE_TYPE;
i INT;
colmax INT;
DATA_TYPE VARCHAR2(50);
TYPE arr_table IS TABLE OF CLOB INDEX BY BINARY_INTEGER;
arr arr_table;
STRCOM CLOB;
str CLOB;
tabnamechek INT;
cnt INT DEFAULT 0;
csvfields INT;

BEGIN

    -- получаем количество столбцов в таблице
    select nvl((SELECT count(*) 
    FROM all_tables t, all_tab_columns c 
    WHERE t.owner=upper(owner_name) AND T.TABLE_NAME = C.TABLE_NAME AND t.TABLE_NAME = upper(tab_name) 
    GROUP BY t.TABLE_NAME),0) INTO colmax from dual;

  IF colmax > 0 THEN
    -- открываем файл для чтения
    F1 := UTL_FILE.FOPEN(dir_name, file_name, 'R');

    -- проверяем открылся ли файл
    IF UTL_FILE.IS_OPEN(F1) THEN
       exception_msg:='File '|| file_name ||' is Open';
    ELSE
       exception_msg:='File '|| file_name ||' is not Open';
    END IF;
    -- считываем построчно записи из файла
    LOOP
        cnt:=cnt+1;

       BEGIN
            UTL_FILE.GET_LINE(F1,V_LINE);
            -- получаем количество столбцов в одной строке файла
            csvfields:=regexp_count(regexp_replace(V_LINE,'.*?'),''||delim||'') + 1;
            -- проверяем совпадает ли количество столбцов таблицы и файле
            IF csvfields = colmax THEN
                -- получаем запись для каждого столбца строки
               FOR i IN 1..csvfields
               LOOP
                    -- заносим записи N-строки в массив
                    arr(i):=trim(REGEXP_SUBSTR(V_LINE, '[^'||delim||']+', 1, i));
                    
                    SELECT C.DATA_TYPE INTO DATA_TYPE
                    FROM all_tables t, all_tab_columns c 
                    WHERE t.owner=upper(owner_name) AND T.TABLE_NAME = C.TABLE_NAME AND t.TABLE_NAME = upper(tab_name)
                    AND C.COLUMN_ID = i;
                        
                    -- составляем часть кода динамического sql
                    -- если тип столбца DATE, то конвертируем полученную запись в дату
                    IF DATA_TYPE = 'DATE' THEN
                        str:=str||'to_date('''||arr(i)||''',''dd.mm.yyyy hh24:mi:ss'')'||',';
                    ELSE 
                        str:=str||''''||arr(i)||''''||',';
                    END IF;

                    -- вставляем полученную строку в таблицу
                    IF i = csvfields THEN

                            BEGIN
                                STRCOM:='INSERT INTO '||owner_name||'.'||tab_name||' values('||regexp_replace(str,',$','')||')';

                                EXECUTE IMMEDIATE STRCOM;
                                COMMIT;

                                str:=NULL;
                                arr.DELETE;
                            END;
                                              
                    END IF;
                    
               END LOOP;
               
            ELSE
                exception_msg:='Wrong number of columns '||file_name||', because csv('||csvfields||') <> tab('||colmax||').';
                EXIT;
                UTL_FILE.FCLOSE(F1);
            END IF;
       EXCEPTION WHEN No_Data_Found THEN EXIT;
       END;
                    
    END LOOP;
    -- закрываем файл
    UTL_FILE.FCLOSE(F1);
   exception_msg:=exception_msg || chr(13) || 'Data load!'; 
 ELSE
   exception_msg:='Table name OR owner name is wrong!';
 END IF;     

    EXCEPTION 
    WHEN utl_file.invalid_operation THEN exception_msg:='File '|| file_name ||' is Not Open.';
    WHEN UTL_FILE.INVALID_PATH THEN exception_msg:='Invalid file '|| file_name ||' path.';
    WHEN UTL_FILE.READ_ERROR THEN exception_msg:='Read error file '|| file_name ||'.';
    WHEN UTL_FILE.INVALID_FILEHANDLE THEN exception_msg:='File ' || file_name || ' handle was Invalid.';
    WHEN UTL_FILE.INTERNAL_ERROR THEN exception_msg:='An unspecified error in PL/SQL.';
    WHEN OTHERS THEN exception_msg:=('ERROR: '||DBMS_UTILITY.FORMAT_ERROR_STACK||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    
END;

 

Вызов процедуры свыполняется следующим образом:

declare
    outrep varchar2(4000);
begin
    LOADFROMFILE('DBSTAT','apex_stat_sga_16052014_14.csv','SDUSER','STAT_SGA',';',outrep);
    dbms_output.put_line(outrep);
end;

--dbms_output--
File apex_stat_sga_16052014_14.csv is Open.
Data load!