Врезка 1

Листинг 1. Сценарий создания функции cxValid.

CREATE FUNCTION dbo.cxValid(
   @cx AS complex)
 RETURNS bit
 AS 
 BEGIN
   SET @cx = RTRIM(LTRIM(@cx))
   IF RIGHT(@cx, 1) <> `i`
     RETURN 0
   SET @cx = LEFT(@cx, LEN(@cx) - 1)
   DECLARE @signpos AS int
   SET @signpos = PATINDEX(`%_[-+]%`, @cx) + 1
   IF @signpos = 0
     RETURN 0
   IF IsNumeric(LEFT(@cx, @signpos - 1)) = 0
     RETURN 0
   IF IsNumeric(RIGHT(@cx, LEN(@cx) - @signpos + 1)) = 0
     RETURN 0
   RETURN 1
 END
 GO

Листинг 2. Cоздание правила complex_valid и связывание его с типом Complex.

CREATE rule complex_valid
 AS
   - Check whether last character is `i`.
   RIGHT(RTRIM(@cx), 1) = `i` AND
   IsNumeric(LEFT(LTRIM(@cx),
               PATINDEX(`%_[-+]%`, LTRIM(@cx)))) = 1 AND
   IsNumeric(SUBSTRING(LTRIM(@cx),
               PATINDEX(`%_[-+]%`, LTRIM(@cx)) + 1,
               LEN(RTRIM(LTRIM(@cx))) -
                 PATINDEX(`%_[-+]%`, LTRIM(@cx)) - 1)) = 1
 GO
 EXEC sp_bindrule `complex_valid`, `complex`
 GO

Листинг 3. Сценарий создания функции cxStandardize.

 CREATE FUNCTION dbo.cxStandardize(
   @cx AS complex)
 RETURNS complex
 AS
  BEGIN
   SET @cx = REPLACE(@cx, ` `, ``)
   DECLARE @real AS varchar(25)
   SET @real = LEFT(@cx, PATINDEX(`%_[-+]%`, @cx))
   IF CHARINDEX(`.`, @real) > 0 AND PATINDEX
                                               (`%.%[1-9]%`, @real) = 0
     SET @real = LEFT(@real, CHARINDEX(`.`, @real) - 1)
   IF PATINDEX(`%.%0`, @real) > 0
     SET @real = LEFT(@real, LEN(@real) - PATINDEX
                           (`%[^0]%.%`, REVERSE(@real)) + 1)
    DECLARE @imaginary AS varchar(25)
   SET @imaginary = SUBSTRING(@cx, PATINDEX
            (`%_[-+]%`, @cx) + 1,CHARINDEX(`i`, @cx) - 
                       PATINDEX(`%_[-+]%`, @cx) - 1)
    IF CHARINDEX(`.`, @imaginary) > 0
      AND PATINDEX(`%.%[1-9]%`, @imaginary) = 0
     SET @imaginary = LEFT(@imaginary, CHARINDEX(`.`, 
                                                      @imaginary) - 1)
   IF PATINDEX(`%.%0`, @imaginary) > 0
     SET @imaginary = LEFT(@imaginary,
                     LEN(@imaginary) - PATINDEX(`%[^0]%.%`,
                                         REVERSE(@imaginary)) + 1)
    SET @cx = @real + @imaginary + `i`
   SET @cx = STUFF(@cx, PATINDEX(`%_[-+]%`, 
                                                @cx) + 1, 0, ` `)
   SET @cx = STUFF(@cx, PATINDEX(`%_[-+]%`, 
                                                @cx) + 2, 0, ` `)
   IF LEFT(@cx, 1) = `+`
     SET @cx = STUFF(@cx, 1, 1, ``)
   RETURN @cx
 END
 GO

Листинг 4. Сценарий создания функций cxGetReal и cxGetImaginary.

CREATE FUNCTION dbo.cxGetReal(
   @cx AS complex)
 RETURNS decimal(19, 9)
 AS
  BEGIN
   SET @cx = REPLACE(@cx, ` `, ``)
   RETURN CAST(LEFT(@cx, 
          PATINDEX(`%_[-+]%`, @cx)) AS decimal(19,9))
 END
 GO
 CREATE FUNCTION dbo.cxGetImaginary(
   @cx AS complex)
 RETURNS decimal(19,9)
 AS
  BEGIN
   SET @cx = REPLACE(@cx, ` `, ``)
   RETURN CAST(
     SUBSTRING(
       @cx,
       PATINDEX(`%_[-+]%`, @cx) + 1,
       CHARINDEX(`i`, @cx) - 
                       PATINDEX(`%_[-+]%`, @cx) - 1)
       AS decimal(19,9))
 END
 GO

Листинг 5. Сценарий создания функции cxStrForm.

CREATE FUNCTION dbo.cxStrForm(@real AS decimal(19,9),
                                         @imaginary AS decimal(19,9))
 RETURNS complex
 AS
  BEGIN
   RETURN dbo.cxStandardize(
     CAST(@real AS varchar(21)) +
     CASE SIGN(@imaginary)
       WHEN -1 THEN `-`
       ELSE `+`
     END +
     CAST(ABS(@imaginary) AS varchar(21)) + `i`)
 END
 GO

Листинг 6. Сценарий создания функции cxAdd.

 CREATE FUNCTION dbo.cxAdd(@cx1 as complex,
                                                 @cx2 as complex)
 RETURNS complex
 AS
 BEGIN
   IF dbo.cxValid(@cx1) = 0 OR dbo.cxValid(@cx2) = 0
     RETURN NULL
    DECLARE @a1        AS decimal(19,9),
           @b1        AS decimal(19,9),
           @a2        AS decimal(19,9),
           @b2        AS decimal(19,9)
    DECLARE @real      AS decimal(19,9),
           @imaginary AS decimal(19,9)
    SET @a1 = dbo.cxGetReal(@cx1)
   SET @a2 = dbo.cxGetReal(@cx2)
   SET @b1 = dbo.cxGetImaginary(@cx1)
   SET @b2 = dbo.cxGetImaginary(@cx2)
    - z1 + z2 = (a1 + a2) + (b1 + b2)i
   SET @real =      @a1 + @a2
   SET @imaginary = @b1 + @b2
    RETURN dbo.cxStrForm(@real, @imaginary)
  END
 GO

Листинг 7. Сценарий создания функции cxSubstract.

 CREATE FUNCTION dbo.cxSubtract(@cx1 as complex,
                                                        @cx2 as complex)
 RETURNS complex
 AS
  BEGIN
    IF dbo.cxValid(@cx1) = 0 OR dbo.cxValid(@cx2) = 0
     RETURN NULL
    DECLARE @a1        AS decimal(19,9),
           @b1        AS decimal(19,9),
           @a2        AS decimal(19,9),
           @b2        AS decimal(19,9)
    DECLARE @real      AS decimal(19,9),
           @imaginary AS decimal(19,9)
    SET @a1 = dbo.cxGetReal(@cx1)
   SET @a2 = dbo.cxGetReal(@cx2)
   SET @b1 = dbo.cxGetImaginary(@cx1)
   SET @b2 = dbo.cxGetImaginary(@cx2)
   SET @real =      @a1 - @a2
   SET @imaginary = @b1 - @b2
    RETURN dbo.cxStrForm(@real, @imaginary)
  END
 GO

Листинг 8. Сценарий создания функции cxMult.

CREATE FUNCTION dbo.cxMult(
   @cx1 AS complex,
   @cx2 AS complex)
 RETURNS complex
 AS
  BEGIN
    IF dbo.cxValid(@cx1) = 0 OR dbo.cxValid(@cx2) = 0
     RETURN NULL
    DECLARE @a1        AS decimal(19,9),
           @b1        AS decimal(19,9),
           @a2        AS decimal(19,9),
           @b2        AS decimal(19,9)
    DECLARE @real      AS decimal(19,9),
           @imaginary AS decimal(19,9)
    SET @a1 = dbo.cxGetReal(@cx1)
   SET @a2 = dbo.cxGetReal(@cx2)
   SET @b1 = dbo.cxGetImaginary(@cx1)
   SET @b2 = dbo.cxGetImaginary(@cx2)
   SET @real =      @a1*@a2 - @b1*@b2
   SET @imaginary = @a1*@b2 + @a2*@b1
    RETURN dbo.cxStrForm(@real, @imaginary)
  END
 GO

Листинг 9. Сценарий создания функции cxDivide.

 CREATE FUNCTION dbo.cxDivide
       (@cx1 as complex,
       @cx2 as complex)
 RETURNS complex
 AS
  BEGIN
    IF dbo.cxValid(@cx1) = 0 OR dbo.cxValid(@cx2) = 0
     RETURN NULL
    DECLARE @a1        AS decimal(19,9),
           @b1        AS decimal(19,9),
           @a2        AS decimal(19,9),
           @b2        AS decimal(19,9)
    DECLARE @real      AS decimal(19,9),
           @imaginary AS decimal(19,9)
    SET @a1 = dbo.cxGetReal(@cx1)
   SET @a2 = dbo.cxGetReal(@cx2)
   SET @b1 = dbo.cxGetImaginary(@cx1)
   SET @b2 = dbo.cxGetImaginary(@cx2)
   SET @real =      (@a1*@a2 + @b1*@b2)/
                            (@a2*@a2 + @b2*@b2)
   SET @imaginary = (@a2*@b1 - @a1*@b2)/
                               (@a2*@a2 + @b2*@b2)
   RETURN dbo.cxStrForm(@real, @imaginary)
  END
 GO

Листинг 10. Сценарий создания функции cxVectorSize.

 CREATE FUNCTION dbo.cxVectorSize(
   @cx as complex)
 RETURNS decimal(19,9)
 AS
  BEGIN
    IF dbo.cxValid(@cx) = 0
     RETURN NULL
    DECLARE @real      AS decimal(19,9),
           @imaginary AS decimal(19,9)
    SET @real      = dbo.cxGetReal(@cx)
   SET @imaginary = dbo.cxGetImaginary(@cx)
   RETURN SQRT(@real*@real + @imaginary*
                            @imaginary)
  END
 GO

Листинг 11. Наполнение таблицы комплексными числами.

 CREATE TABLE ComplexNumbers
 (
   key_col int NOT NULL PRIMARY KEY,
   cx1 complex NOT NULL,
   cx2 complex NOT NULL
 )
 INSERT INTO ComplexNumbers VALUES(1, `5 + 2i`, `2 + 4i`)
 INSERT INTO ComplexNumbers VALUES(2, `2 + 9i`, `4 + 5i`)
 INSERT INTO ComplexNumbers VALUES(3, `7 + 4i`, `3 + 2i`)
 INSERT INTO ComplexNumbers VALUES(4, `3 + 2i`, `6 + 3i`)
 INSERT INTO ComplexNumbers VALUES(5, `4 + 3i`, `7 + 2i`)
 INSERT INTO ComplexNumbers VALUES(6, `1 + 4i`, `4 + 3i`)
 INSERT INTO ComplexNumbers VALUES(7, `7 + 2i`, `8 + 1i`)
 INSERT INTO ComplexNumbers VALUES(8, `2 + 3i`, `3 + 6i`)
 INSERT INTO ComplexNumbers VALUES(9, `3 + 6i`, `2 + 8i`)
 INSERT INTO ComplexNumbers VALUES(10, `2 + 1i`, `3 + 2i`)

Листинг 12. Использование комплексных функций в запросе.

  SELECT
   key_col,
   cx1,
   cx2,
   dbo.cxAdd(cx1, cx2)      AS cxAdd,
   dbo.cxSubtract(cx1, cx2) AS cxSubtract,
   dbo.cxMult(cx1, cx2)     AS cxMult,
   dbo.cxDivide(cx1, cx2)   AS cxDivide
 FROM
   ComplexNumbers

Листинг 13. Выполнение вычислений с помощью комплексных функций.

 DECLARE @sumproduct AS complex
 SET @sumproduct = `0 + 0i`
 SELECT @sumproduct = dbo.cxAdd
             (@sumproduct, dbo.cxMult(cx1, cx2))
 FROM
   ComplexNumbers
 PRINT `The sum product of the vectors is: ` 
              + @sumproduct
 The sum product of the vectors is: 8 + 252i.