Образовательный проект «SnakeProject» Михаила Козлова

⇒ SQL\T-SQL ⇐

CISCO

Voice(Asterisk\Cisco)

Microsoft

Powershell

Python

FreeBSD and Nix

1С

Общая

WEB Разработка

ORACLE SQL \ JAVA

Мото

Стрельба, пневматика, оружие

Саморазвитие и психология

T-SQL: Получение адреса сети по ip адресу и маске

В продолжении цикла статей о преобразовании десятичных чисел в двоичные и  обратно, подведем итоговую задачу.

Необходимо вычислять адрес сети по ip и маске некоторого хоста.

Как написать функции преобразования разобрано тут, в данной статье я не буду дублировать их код:

http://snakeproject.ru/rubric/article.php?art=binaryToInt

http://snakeproject.ru/rubric/article.php?art=intToHex2

Напишем нашу фунцию, принимающую адрес и маску:

USE Sbase
GO
CREATE FUNCTION getNetwork (@ip VARCHAR(MAX), @mask VARCHAR(MAX))
    RETURNS VARCHAR(MAX)
    BEGIN

--Разделитель между октетами
DECLARE @delimeter CHAR(1) = '.'

--Счетчик
DECLARE @i INT = 1

--Позиции по разделителю
DECLARE @pos INT = CHARINDEX(@delimeter,@ip)
DECLARE @posMask INT = CHARINDEX(@delimeter,@mask)
--//Позиции по разделителю

--Переменные для обработки частей ip и маски
DECLARE @id NVARCHAR(MAX)
DECLARE @idMask NVARCHAR(MAX)
--//Переменные для обработки частей ip и маски

DECLARE @table TABLE (
    ip1 INT
    ,ip2 INT
    ,ip3 INT
    ,ip4 INT
    ,mask1 INT
    ,mask2 INT
    ,mask3 INT
    ,mask4 INT
    ,iph VARCHAR(MAX)
    ,mh VARCHAR(MAX)
)

--Пока есть точки в ip адресе переводим все части в HEX
WHILE(@pos != 0)
BEGIN
    SET @id = SUBSTRING(@ip, 1, @pos-1)
    SET @idMask = SUBSTRING(@mask, 1, @posMask-1)
    
    IF @i = 1
    BEGIN
        DECLARE @ip1 INT = @id
        DECLARE @m1 INT = @idMask
        
        DECLARE @iph1 VARCHAR(MAX) = (SELECT Sbase.dbo.ConvertToBase(@ip1, 2))
        DECLARE @mh1 VARCHAR(MAX) = (SELECT Sbase.dbo.ConvertToBase(@m1, 2))
        
        WHILE LEN(@iph1) < 8
        BEGIN
            SET @iph1 = '0' + @iph1
        END
        
        WHILE LEN(@mh1) < 8
        BEGIN
            SET @mh1 = @mh1 + '0'
        END
        
    END
    
    ELSE IF @i = 2
    BEGIN
        DECLARE @ip2 INT = @id
        DECLARE @m2 INT = @idMask
        
        DECLARE @iph2 VARCHAR(MAX) = (SELECT Sbase.dbo.ConvertToBase(@ip2, 2))
        DECLARE @mh2 VARCHAR(MAX) = (SELECT Sbase.dbo.ConvertToBase(@m2, 2))
        
        WHILE LEN(@iph2) < 8
        BEGIN
            SET @iph2 = '0' + @iph2
        END
        
        WHILE LEN(@mh2) < 8
        BEGIN
            SET @mh2 = @mh2 + '0'
        END
    END    
    
    ELSE IF @i = 3
    BEGIN
        DECLARE @ip3 INT = @id
        DECLARE @m3 INT = @idMask
        
        DECLARE @iph3 VARCHAR(MAX) = (SELECT Sbase.dbo.ConvertToBase(@ip3, 2))
        DECLARE @mh3 VARCHAR(MAX) = (SELECT Sbase.dbo.ConvertToBase(@m3, 2))
        
        WHILE LEN(@iph3) < 8
        BEGIN
            SET @iph3 = '0' + @iph3
        END
        
        WHILE LEN(@mh3) < 8
        BEGIN
            SET @mh3 = @mh3 + '0'
        END
    END
    
    SET @i = @i + 1
    
    SET @ip = SUBSTRING(@ip, @pos+1, LEN(@ip))
    SET @mask = SUBSTRING(@mask, @posMask+1, LEN(@mask))
    SET @pos = CHARINDEX(@delimeter,@ip)
    SET @posMask = CHARINDEX(@delimeter,@mask)
    
    --Заносим итоговые данные после окончания точек в ip и маске
    IF @pos IN (0) AND @posMask IN (0)
    BEGIN
        
        DECLARE @ip4 INT = @ip
        DECLARE @m4 INT = @mask
        
        DECLARE @iph4 VARCHAR(MAX) = (SELECT Sbase.dbo.ConvertToBase(@ip4, 2))
        DECLARE @mh4 VARCHAR(MAX) = (SELECT Sbase.dbo.ConvertToBase(@m4, 2))
        
        WHILE LEN(@iph4) < 8
        BEGIN
            SET @iph4 = '0' + @iph4
        END
        
        WHILE LEN(@mh4) < 8
        BEGIN
            SET @mh4 = @mh4 + '0'
        END
                
        INSERT INTO @table (ip1,ip2,ip3,ip4,
                            mask1,mask2,mask3,mask4,
                            iph, mh) 
                VALUES(@ip1,@ip2,@ip3,@ip4,
                        @m1,@m2,@m3,@m4,
                        @iph1+@iph2+@iph3+@iph4,
                        @mh1+@mh2+@mh3+@mh4
                        )
    END
    --//Заносим итоговые данные после окончания точек в ip и маске    
END

--Количество нулей в маске
DECLARE @maskPathZero INT = (SELECT PATINDEX('%0%',mh) FROM @table)

DECLARE @maskHexNetwork INT = 32 - @maskPathZero + 1

--Преобразование ip в сеть
DECLARE @ipHex VARCHAR(MAX) = (SELECT iph FROM @table) 

DECLARE @ipHexNetwork VARCHAR(MAX) = SUBSTRING(@ipHex, 1, 32 - @maskHexNetwork)
WHILE LEN(@ipHexNetwork) < 32
BEGIN
    SET @ipHexNetwork = @ipHexNetwork + '0'
END

--Получаем сеть в десятичном формате.Преобразуем двоичные октеты в десятичные.
DECLARE @netwokIP1 VARCHAR(8) = (SELECT Sbase.dbo.ToInt(SUBSTRING(@ipHexNetwork, 1, 8)))
DECLARE @netwokIP2 VARCHAR(8) = (SELECT Sbase.dbo.ToInt(SUBSTRING(@ipHexNetwork, 9, 8)))
DECLARE @netwokIP3 VARCHAR(8) = (SELECT Sbase.dbo.ToInt(SUBSTRING(@ipHexNetwork, 17, 8)))
DECLARE @netwokIP4 VARCHAR(8) = (SELECT Sbase.dbo.ToInt(SUBSTRING(@ipHexNetwork, 25, 8)))

RETURN (@netwokIP1+'.'+@netwokIP2+'.'+@netwokIP3+'.'+@netwokIP4)
END;

Теперь проверим ее, например введем: SELECT Sbase.dbo.getNetwork('91.184.236.25', '255.255.255.248')

Возможно это не самое элегантное и практичное решение, но оно работает.

Видео на YouTube: https://www.youtube.com/watch?v=7Ar8Ft2NJwU

Комментарии пользователей