Отчёт по практике в Kaspi Bank

OPEN {{[GLOBAL]имя_курсора }

     |@имя_переменной_курсора}

После открытия курсора происходит выполнение связанного с ним оператора SELECT, выходные данные которого сохраняются в многоуровневой памяти.

Выборка данных из курсора

Сразу после открытия курсора можно выбрать его содержимое (результат выполнения соответствующего запроса) посредством следующей команды:

FETCH [[NEXT | PRIOR | FIRST | LAST

  | ABSOLUTE {номер_строки

  | @переменная_номера_строки}

  | RELATIVE {номер_строки |

    @переменная_номера_строки}]

  FROM ]{{[GLOBAL ]имя_курсора }|

    @имя_переменной_курсора }

  [INTO @имя_переменной [,...n]]

При указании FIRST будет возвращена самая первая строка полного результирующего набора курсора, которая становится текущей строкой.

При указании LAST возвращается самая последняя строка курсора. Она же становится текущей строкой.

При указании NEXT возвращается строка, находящаяся в полном результирующем наборе сразу же после текущей. Теперь она становится текущей. По умолчанию команда FETCH использует именно этот способ выборки строк.

Ключевое слово PRIOR возвращает строку, находящуюся перед текущей. Она и становится текущей.

 

Изменение и удаление данных

Для выполнения изменений с помощью курсора необходимо выполнить команду UPDATE в следующем формате:

UPDATE имя_таблицы SET {имя_столбца={

  DEFAULT | NULL | выражение}}[,...n]

  WHERE CURRENT OF {{[GLOBAL] имя_курсора}

  |@имя_переменной курсора}

За одну операцию могут быть изменены несколько столбцов текущей строки курсора, но все они должны принадлежать одной таблице.

Для удаления данных посредством курсора используется команда DELETE в следующем формате:

DELETE имя_таблицы

  WHERE CURRENT OF {{[GLOBAL] имя_курсора}

  |@имя_переменной курсора}

В результате будет удалена строка, установленная текущей в курсоре.

Закрытие курсора

CLOSE {имя_курсора | @имя_переменной_курсора}

После закрытия курсор становится недоступным для пользователей программы. При закрытии снимаются все блокировки, установленные в процессе его работы. Закрытие может применяться только к открытым курсорам. Закрытый, но не освобожденный курсор может быть повторно открыт. Не допускается закрывать неоткрытый курсор.

 

Пример: Разработать курсор для вывода списка приобретенных клиентами из Москвы товаров и их общей стоимости. В один курсор заносятся все московские клиенты, затем для каждой строки курсора, т.е. для каждого клиента, определяется и распечатывается другой курсор – его покупки. Подсчитывается общая стоимость покупок клиента.

DECLARE @id_kl    INT,

        @firm     VARCHAR(50),

        @fam      VARCHAR(50),      

        @message  VARCHAR(80),

        @nam      VARCHAR(50),

        @d        DATETIME,

        @p        INT,

        @s        INT

SET @s=0

PRINT '  Список покупок'

DECLARE klient_cursor CURSOR LOCAL FOR

    SELECT КодКлиента, Фирма, Фамилия

    FROM Клиент

    WHERE Город='Москва'

    ORDER BY Фирма, Фамилия

 

 

OPEN klient_cursor

FETCH NEXT FROM klient_cursor

INTO @id_kl, @firm, @fam

WHILE @@FETCH_STATUS=0

BEGIN

    SELECT @message='Клиент '+@fam+

        ' Фирма '+ @firm

    PRINT @message

    SELECT @message='Наименование товара Дата

        покупки  Стоимость'

    PRINT @message

    DECLARE tovar_cursor CURSOR FOR

        SELECT Товар.Название, Сделка.Дата,

            Товар.Цена*Сделка.Количество AS

    Стоимость

        FROM Товар INNER JOIN Сделка ON Товар.

    КодТовара=Сделка.КодТовара

        WHERE Сделка.КодКлиента=@id_kl

 

    OPEN tovar_cursor

    FETCH NEXT FROM tovar_cursor

      INTO @nam, @d, @p

    IF @@FETCH_STATUS<>0

        PRINT ' Нет покупок'

    WHILE @@FETCH_STATUS=0

    BEGIN

        SELECT @message='   '+@nam+'   '+

           CAST(@d AS CHAR(12))+'  '+

           CAST(@p AS CHAR(6))

        PRINT @message

        SET @s=@s+@p

        FETCH NEXT FROM tovar_cursor

        INTO @nam, @d, @p 

    END

    CLOSE tovar_cursor

    DEALLOCATE tovar_cursor

 

    SELECT @message='Общая стоимость '+

       CAST(@s AS CHAR(6))

    PRINT @message   

 

-- переход к следующему  клиенту--

 

    FETCH NEXT FROM klient_cursor

    INTO @id_kl, @firm, @fam

END

CLOSE klient_cursor

DEALLOCATE klient_cursor

 

Задание 3. Создание функции и процедур. Использование встроенных функции. Создание пакетов.

 

1. Функции

Синтаксис создания (обновления) функции:

CREATE [OR REPLACE] FUNCTION function_name

   [ (parameter [,parameter]) ]

   RETURN return_datatype

IS | AS

   [declaration_section]

BEGIN

   executable_section

[EXCEPTION

   exception_section]

END [function_name];

 

При создании функции или процедуры, вы можете определять ее параметры. Существует три типа параметров, которые вы можете определить:

• IN – На этот параметр можно ссылаться внутри процедуры или функции. Значение параметра не может быть изменено процедурой или функцией.

• OUT – На параметр нельзя сослаться внутри процедуры или функции, но значение параметра может быть изменено процедурой или функцией.

• IN OUT – На параметр можно сослаться внутри процедуры или функции и его значение может быть изменено процедурой или функцией.

 

Простой пример создания (обновлении) функции:

CREATE OR REPLACE FUNCTION FindCourse(name_in IN VARCHAR2) RETURN NUMBER IS

   cnumber NUMBER; 

 

   CURSOR c1 IS

      SELECT course_number FROM courses_tbl WHERE course_name = name_in; 

 

BEGIN

   OPEN c1;

   FETCH c1

      INTO cnumber; 

 

   IF c1%notfound THEN

      cnumber := 9999;

   END IF; 

 

   CLOSE c1;

   RETURN cnumber; 

 

EXCEPTION

   WHEN OTHERS THEN

      raise_application_error(-20001,'An error was encountered - ' || SQLCODE || ' -ERROR- ' || SQLERRM);

END;

 

Эта функция называется FindCourse. Она имеет один входной параметр name_in и возвращает число. Функция может вернуть количество курсов если найдет их по имени курса. В противном случае она вернет 99999. 
Вы можете сослаться на вашу функцию из SQL выражения следующим образом:

 

SELECT course_name, FindCourse(course_name) AS course_id

  FROM courses

 WHERE subject = 'Mathematics';

 

2. Процедуры

Синтаксис создания (обновления) функции:

CREATE [OR REPLACE] PROCEDURE proc_name

   [ (parameter [,parameter]) ]

IS | AS

   [declaration_section]

BEGIN

   executable_section

[EXCEPTION

   exception_section]

END [proc_name];

 

 

Пример:

CREATE OR REPLACE PROCEDURE TESTINOUT(NUM IN OUT NUMBER(3,2), DT OUT VARCHAR2(100))

IS

 

BEGIN

 

SELECT COMPANY INTO DT FROM customers

WHERE customers.CUST_NUM = NUM;

 

SELECT CUST_REP INTO NUM FROM customers

WHERE customers.CUST_NUM = NUM;

 

END TESTINOUT;

 

 

3. Встроенные функции

Функция LOWER(str)

Функция LOWER преобразует все символы строки str в строчные.

Пример: 

 

SELECT LOWER('TeXt DATA') X

FROM dual 

 

X

text data

 

Функция UPPER(str)

Функция UPPER преобразует все символы строки str в прописные.

Пример: 

 

SELECT UPPER('TeXt DATA') X

FROM dual 

 

X

TEXT DATA

 

Функция INITCAP(str)

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

Пример: 

 

SELECT INITCAP('ИваноВ петр сиДорович') X1      

FROM dual 

 

X1

Иванов Петр Сидорович

 

 

Функция SUBSTR(str, m [,n])

Функция SUBSTR возвращает фрагмент строки str, начиная с символа m длиной n символов. Длину можно не указывать — в этом случае возвращается строка от символа m и до конца строки str. Нумерация символов идет с 1. Если указать m = 0, то копирование все равно начнется с первого символа. Задание отрицательного значения m приводит к тому, что символы отсчитываются от конца строки, а не от начала. Задание значений m, превышающих по абсолютному значению длину строки, приводит к тому, что функция возвращает NULL.

Пример: 

 

SELECT SUBSTR('У попа была собака', 13) x1,

SUBSTR('У попа была собака', -6) x2,

SUBSTR('Это тестовый текст', 5, 8) x3,

SUBSTR('У попа была собака', 150) x4

FROM dual

 

X1	X2	X3	X4
собака	собака	Текстовый	<NULL>

 

 

4. Пакеты

 

Объявление пакета имеет две части:

спецификация пакета	СПЕЦИФИКАЦИЯ ПАКЕТА содержит объявления процедур и функций, переменных, констант и исключений, которые доступны извне пакета.
тело пакета	ТЕЛО ПАКЕТА определяет процедуры и функции, курсоры и исключения, которые были объявлены в спецификации пакета. Тело пакета может также определять процедуры, функции, переменные, константы, курсоры и исключения, которые не были объявлены в спецификации пакета; однако эти объекты доступны лишь внутри сферы пакета.

 

Каждая часть пакета создается отдельной командой. Для создания спецификации пакета используйте команду CREATE PACKAGE. Для создания тела пакета используйте команду CREATE PACKAGE BODY.

Команда CREATE PACKAGE создает спецификацию пакета, содержащую объявления общих объектов этого пакета. Следующее предложение SQL создает спецификацию пакета EMP_MGMT:

 

CREATE PACKAGE emp_mgmt AS

  FUNCTION hire_emp (name VARCHAR2, job VARCHAR2,

    mgr NUMBER, hiredate DATE, sal NUMBER, comm NUMBER,

    deptno NUMBER) RETURN NUMBER;

  PROCEDURE fire_emp (emp_id NUMBER);

  PROCEDURE sal_raise (emp_id IN NUMBER, sal_incr NUMBER);END emp_mgmt;

Для создания тела пакета используйте команду CREATE PACKAGE BODY, чтобы специфицировать код для общих процедур и функций, объявленных в спецификации пакета. Вы можете также определить в теле пакета личные пакетированные процедуры, переменные и т.п.

Следующее предложение SQL создает тело пакета EMP_MGMT:

 

CREATE PACKAGE BODY emp_mgmt AS

  FUNCTION hire_emp (name VARCHAR2, job VARCHAR2,

    mgr NUMBER, hiredate DATE, sal NUMBER, comm NUMBER,

    deptno NUMBER) RETURN NUMBER

  IS

/* Эта функция принимает все  аргументы для полей в таблице

EMP, за исключением номера сотрудника.  Значение для этого 
поля поставляется последовательностью.  Функция возвращает 
номер последовательности, сгенерированный вызовом этой 
функции, в качестве номера нового сотрудника.  */

   new_empno NUMBER(10);   /* используется для возврата */

 BEGIN

    SELECT emp_sequence.NEXTVAL INTO new_empno FROM dual;

    INSERT INTO emp VALUES (new_empno, ename, job, mgr,

      hiredate, sal, comm, deptno);

    RETURN(new_empno);