Тригери. Функції. Процедури.

 

Збережена підпрограми являє собою процедуру або функцію. Збережені підпрограми створюються за допомогою виразів CREATE PROCEDURE або CREATE FUNCTION. Збережена підпрограма викликається, використовуючи вираз CALL, причому тільки повертають значення змінні використовуються в якості вихідних. Функція може бути викликана подібно будь-який інший функції і може повертати скалярну величину. Збережені підпрограми можуть викликати інші збережені підпрограми.

 

Починаючи з MySQL 5.0.1, завантажена процедура або функція пов'язана з конкретною базою даних. Це має кілька смислів:

 

Коли підпрограма викликається, то мається на увазі, що треба провести виклик USE db_name (і скасувати використання бази, коли підпрограма завершилася, і база більше не знадобиться)

 

Ви можете кваліфікувати звичайні імена з ім'ям бази даних. Це може бути використано, щоб посилатися на підпрограму, яка-не в поточній базі даних. Наприклад, для виконання процедури, що “p” або функції “f” які пов'язані з БД test, ви можете сказати інтерпретатору команд так: CALL test.p () або test.f ().

Коли база даних видалена, всі завантажені підпрограми пов'язані з нею теж видаляються. В MySQL 5.0.0, завантажені підпрограми - глобальні і не пов'язані з базою даних. Вони успадковують за замовчуванням базу даних з викликає оператора. Якщо USE db_name виконано в межах підпрограми, оригінальна поточна БД буде відновлена після виходу з підпрограми (Наприклад поточна БД db_11, робимо виклик підпрограми, що використовує db_22, після виходу з підпрограми залишається поточної db_11)

MySQL підтримує повністю розширення, які дозволяють юзати звичайні SELECT виразу (без використання курсорів або локальних змінних) всередині збережених процедур. Результуючий набір, повернений від запиту, а просто відправляється безпосередньо клієнтові. Множинний SELECT запит генерує безліч результуючих наборів, тому клієнт повинен використовувати бібліотеку, підтримуючу множинні результуючі набори.

 

CREATE PROCEDURE - створити збережену процедуру.

 

CREATE FUNCTION - створити збережену функцію.

 

синтаксис:

CREATE PROCEDURE імя_процедури ([ параметр_процедури [,...] ])

[ характерістеіка...] телі_подпрограмми

CREATE FUNCTION імя_функциі ([ параметр_функціі [,...] ])

RETURNS тип

[ характеристика... ] тело_подпрограмми

 

параметр_процедури:

[ IN | OUT | INOUT ] імя_параметра тип

параметр_функціі:

імя_параметра тип

 

тип:

Будь-який тип даних MySQL

 

характеристика:

LANGUAGE SQL

[ NOT ] DETERMINISTIC

{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

SQL SECURITY { DEFINER | INVOKER }

COMMENT ' string'

тело_подпрограмми:

Правильне SQL вираз.

 

Розглянемо все на практиці.

Спочатку створимо збережену процедуру наступним запитом:

CREATE PROCEDURE ` my_proc ` (OUT t INTEGER (11))

NOT DETERMINISTIC

SQL SECURITY INVOKER

COMMENT ''

BEGIN

select val1 + val2 into ' t ' from ` my ` LIMIT 0,1;

END;

 

Застосування вирази LIMIT в цьому запиті зроблено з міркувань того, що не будь-який клієнт здатний прийняти багато строковий результуючий набір.

 

Після цього викличемо її:

CALL my_proc (@ a);

SELECT @ a;

 

Для відділення внутрішнього запиту від зовнішнього завжди використовують роздільник відмінний від звичайно (для завдання використовують команду DELIMITER < рядок / символ >)

 

Ось ще один приклад з урахуванням всіх вимог.

mysql > delimiter / /

mysql > CREATE PROCEDURE simpleproc (OUT param1 INT)

-> BEGIN

-> SELECT COUNT (*) INTO param1 FROM t;

-> END;

-> / /

Query OK, 0 rows affected (0.00 sec)

mysql > delimiter;

mysql > CALL simpleproc (@ a);

Query OK, 0 rows affected (0.00 sec)

mysql > SELECT @ a;

+ ------ +

| @ A |

+ ------ +

| 3 |

+ ------ +

1 row in set (0.00 sec)

 

Підтримка тригерів з'явилася в MySQL починаючи з версії 5.0.2.

Тригер - поіменований об'єкт БД, який асоційований з таблицею і активується при настанні певної події, події пов'язаного з цією таблицею.

Наприклад, нижченаведений код створює таблицю і INSERT тригер. Тригер підсумовує значення, що вставляються в один з стовпців таблиці.

mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
Query OK, 0 rows affected (0.03 sec)
mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
-> FOR EACH ROW SET @sum = @sum + NEW.amount;
Query OK, 0 rows affected (0.06 sec)

Оголосимо змінну sum і присвоїмо їй значення 1. Після цього при кожній вставці в таблицю account значення цієї змінної буде збільшувати згідно встановленої частини.

 

Синтаксис створення тригераCREATE
[DEFINER = { имя_ пользователя | CURRENT_USER }]
TRIGGER имя_триггера время_триггера событие_срабатывания_триггера
ON имя_таблицы FOR EACH ROW выражение_выполняемое_при_срабатывании_триггера

 

Якщо з ім'ям тригера і ім'ям користувача все зрозуміло відразу, то про «час тригера» і «подію» поговоримо окремо.

 

час_тригера

 

Визначає час звершення дії тригера. BEFORE означає, що тригер виконається до завершення події спрацьовування тригера, а AFTER означає, що після. Наприклад, при вставці записів (див. приклад вище) наш тригер спрацьовував до фактичної вставки запису і обчислював суму. Такий варіант доречний при попередньому обчисленні якихось додаткових полів у таблиці або паралельної вставці в іншу таблицю.

Додаткові можливості.

 

Збережені процедури – це об’єкти бази даних, які зазвичай містять велику кіль­кість директив SQL. Такі процедури можуть містити сукупність команд (складних запитів, операцій оновлення, додавання та видалення), що часто використовуються як єдине ціле. Збережена процедура дає змогу звернутися до неї як до функції, замість того, щоб послідовно записувати команди SQL, які вона містить. Значне зниження навантаження на канали зв’язку під час роботи користувачів із сервером теж є важливою перевагою збережених процедур, адже замість окремих багаторазових звернень до бази даних виконується єдине звернення до збереженої процедури.

Розширення SQL

 

Будь-яка СКБД розширює стандарт мови SQL, причому в багатьох випадках SQL трансформується в мову програмування, що надає усі можливості SQL. Наприклад, у Microsoft SQL Server таким розширенням є мова Transact-SQL, в Oracle — мова PL/SQL. Кожна з них має переваги звичайних мов програмування, підтримує всі можливості стандарту ANSI SQL, а також розширює його.

Крім того, багато СКБД надають засоби для застосування SQL у традиційних мовах програмування. Існують два методи такого використання мови.

СтатичнаSQL припускає вкладання безпосередньо в програмний код речення, що не може бути змінене під час виконання програми. Зазвичай статична SQL вимагає використання предкомпілятора. Наприклад, Oracle має предкомпілятори для використання SQL у мовах С, Pascal, Ada, COBOL та FORTRAN.

ДинамічнаSQ L дає змогу програмувати побудову SQL -запитів, що створюю­ться під час виконання програми і передаються СКБД, яка, в свою чергу, передає знайдені дані змінним програми.

Ця радіостанція 50-70 років, тут ви почуєте артистів цієї епохи, і насолодитеся улюбленими і добрими піснями сайт Радіо “СРСР 50-70″ онлайн – ретро радіо, яке слухають всі, іменно ця радіохвиля подарує вам гарну атмосферу повернуться в минуле, і насолодиться ейфорією 70-х років.

Самсотійна №10

Індекси в MongoDB

 

Індекси в MongoDB, так само як і в РСУБД використовуються для прискорення роботи запитів. Індекси реалізовані у вигляді B- дерев.

MongoDB завжди разом з колекцією створює індекс по полю _id.

Для створення індексу використовується команда:

db.things.ensureIndex ({ name: 1 })

Things - колекція, name - ім'я поля. 1 означає, що індекс вибудовується за зростанням, -1 за спаданням.

Можна створювати складені індекси вказуючи кілька полів:

db.things.ensureIndex ({ name: 1, y: -1 })

При створенні складових індексів потрібно пам'ятати, що MongoDB буде використовувати їх при обробці запитів, тільки в тому випадку, коли в запиті поля вказані в тому ж порядку, що і в індексі.

Наприклад: складовою індекс по полях a, b, c.

MongoDB буде використовувати цей індекс якщо в запиті зазначено:

- a

- A, b

- A, b, c

 

Якщо поле є масивом значень, то при його індексуванні індексуються всі елементи масиву.

 

При створенні індексів можна вказувати додаткові параметри:

1) background (true / false). Створення індексу в фоні.

2) unique (true / false). Створюється індекс з унікальними значеннями. За наявності не унікальних значень у полі поверне помилку;

3) dropDubs (true / false). Використовується якщо необхідно створити унікальний індекс по полю з дублюючими значеннями. Документи з дублюючими значеннями просто видаляються. Використовується в парі з unique;

4) sparce (true / false). З цією опцією індекс може бути створений по полю, відсутньому в деяких документах. При цьому ці документи не потрапляють в індекс.

 

Видалення всіх індексів:

db.things.dropIndexes ()

Видалення одного індексу:

db.things.dropIndex ({ name: 1 })

переіндексація:

db.things.reIndex ()

 

Реплікації

Коли ми говоримо про реплікації, ми маємо на увазі, що у нас є кілька баз. Для локальних експериментів можна запустити кілька демонів вказавши їм різні порти і директорії для баз.

 

Створимо кілька директорій для експериментальних баз:

 

$ Mkdir db1

$ Mkdir db2

$ Mkdir db3

Запуск демона MongoDb (mongod) виконується наступною командою $ mongod якщо хочемо вказати шлях до бази і порт, тоді так:

 

$ Mongod - dbpath db1 - port 27001

плюс додамо ще параметр вказує на те що це реплікасет:

 

$ Mongod - dbpath db1 - port 27001 - replSet myreplica

myreplica - в даному випадку ім'я для нашого реплікасета.

 

Тепер таким же чином запускаємо ще 2 демона:

 

$ Mongod - dbpath db2 - port 27002 - replSet myreplica

$ Mongod - dbpath db3 - port 27003 - replSet myreplica

У результаті у нас підготовлені 3 бази і нам слід тепер розподілити між ними ролі: хто буде Первинним (Primary) ланкою, до якого будуть додані інші.

 

Припустимо ми вирішили зробити db1 головним. Для цього заходимо на нього використовуючи mongoshell:

 

$ Mongo - port 27001 і робимо

mongo > rs.initiate ()

для ініціалізації конфігурації.

 

Всі маніпуляції з реплікасетом виконуються за допомогою методів об'єкта rs. Весь список методів пожно отримати виконавши команду rs.help ().

 

Перевіримо статус нашого реплікасета:

 

mongo > rs.status ()

як бачимо у властивості members присутсвует тільки поточний сервер, для того щоб додати інші робимо:

 

mongo > rs.add (" myhost: 27002 ")

mongo > rs.add (" myhost: 27003 ")

 

Самостійна №11

Клієнтські інтерфейси

Приклад №1

#include <stdio.h>

#include <stdlib.h>

#include <string.h>

#include <sqlite3.h>

 

static int res=0;

static int callback(void *NotUsed, int argc, char **argv, char

**azColName)

{

int i;

if (res==0)

{

for(i=0; i<argc; i++)

{

printf("%s", azColName[i]);

if (i!=argc-1)

{

printf(" | ");

}

}

res=1;

}

printf("\n");

for(i=0; i<argc; i++)

{

printf("%s", argv[i]? argv[i]: "NULL");

if (i!=argc-1)

{

printf(" | ");

}

}

printf("\n");

return 0;

}

 

const char manual[]=

"\nSimple SQLite WC Design Manual\n"

"Version 1.0\n"

"\nSystem command:\n"

"help - open Design Manual.\n"

 

"quit - close program.\n"

"exit - close program.\n"

"restart - close old database and open new database.\n"

"\n";

 

static int sys_command_analysis(sqlite3 *db, char database[],

char command[])

{

int rc;

if (strcmp(command, "")==0)

{

printf("Interface message: Empty command.\n");

return 0;

}

if ((strcmp(command, "quit")==0)|(strcmp(command,

"exit")==0))

{

printf("Interface message: Program closed.\n");

return 1;

}

if (strcmp(command, "help")==0)

{

printf("%s", manual);

return 0;

}

if (strcmp(command, "restart")==0)

{

if (db!=NULL)

{

sqlite3_close(db);

fprintf(stderr, "Interface message: %s saved and

closed.\n", database);

}

fprintf(stderr, "Enter database file: ");

gets(database);

rc=sys_command_analysis(db,database,database);

if (rc==1)

{

return 1;

}

return 0;

}

return -1;

}

 

int main(int argc, char **argv)

{

sqlite3 *db=NULL;

sqlite3_stmt *stmt=NULL;

char *zErrMsg = 0;

int rc;

int i;

char database[256];

char sql[256];

fprintf(stderr, "Simple SQLite WC Design 1.0\nDesign file:

%s\n\n", argv[0]);

fprintf(stderr, "Enter database file: ");

gets(database);

rc=sys_command_analysis(db,database,database);

if (rc==1)

{

return 0;

}

for (;;)

{

fprintf(stderr, "SQLite> ");

gets(sql);

rc=sys_command_analysis(db,database,sql);

if (rc==1)

{

return 0;

}

else if (rc==0)

{

continue;

}

rc = sqlite3_open_v2(&database, &db,

SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL);

if(rc!=SQLITE_OK)

{

fprintf(stderr, "Interface message: Can't open

database: %s\n", sqlite3_errmsg(db));

sqlite3_close(db);

return(1);

}

rc = sqlite3_prepare_v2(db, &sql, -1, &stmt, NULL);

if(rc!=SQLITE_OK)

{

printf("Interface message: Syntax error.\n");

continue;

}

rc = sqlite3_exec(db, &sql, callback, 0, &zErrMsg);

if(rc!=SQLITE_OK)

{

fprintf(stderr, "Interface message: SQL error:

%s\n", zErrMsg);

sqlite3_free(zErrMsg);

continue;

}

}

sqlite3_close(db);

return 0;

}

 

Приклад 2

#include <stdio.h>5

 

#include <stdlib.h>

#include <windows.h>

#include <mysql.h>

 

int main(int argc, char** argv)

{

MYSQL *conn;

MYSQL_RES *result;

MYSQL_ROW row;

int Nfields;

int i;

int j;

 

char *mysql_host;

char *mysql_usr;

char *mysql_passw;

char *mysql_db;

 

char command[256];

char var[100];

char passw[256];

 

printf("MySQL client version: %s\n",

mysql_get_client_info());

 

mysql_host="localhost";

mysql_usr="root";

mysql_passw="";

mysql_db="testdb";

 

printf("Password: ");

gets(passw);

mysql_passw=passw;

 

for (;;)

{

printf("SQL> ");

gets(command);

if (strcmp(command, "quit")==0)

{

return 0;

}

conn=mysql_init(NULL);

if (conn == NULL)

{

printf("Error %u: %s\n", mysql_errno(conn),

mysql_error(conn));

continue;

}

 

if (mysql_real_connect(conn, mysql_host, mysql_usr,

mysql_passw, mysql_db, 0, NULL, 0) == NULL)

printf("Error %u: %s\n", mysql_errno(conn),

mysql_error(conn));

continue;

}

 

if (mysql_query(conn, command))

{

printf("Error %u: %s\n", mysql_errno(conn),

mysql_error(conn));

continue;

}

else

{

printf("SQL query OK!\n");

}

 

for (j = 0; j < 6; j++)

{

var[j] = command[j];

}

 

if (strcmp(var, "select")==0)

{

result = mysql_store_result(conn);

 

Nfields = mysql_num_fields(result);

printf("\n\n Results: \n");

while (row = mysql_fetch_row(result))

{

for (i = 0; i < Nfields; i++)

{

printf("%s ", row[i]);

}

printf("\n");

}

mysql_free_result(result);

}

mysql_close(conn);

}

return 0;

}

Висновок: важливою частиною роботи з базами даних є здійснення їхнього

адміністрування – створення, впорядкування і структуризації. Ці прості інтерфейси дозволяють спростити роботу з базами даних, а також показують основні принципи взаємодії мови SQL із мовами програмування.

 

Розширений SQL

PostgreSQL є розширюваної системою, тому що її робота управляєти каталогом. Якщо ви добре знайомі зі стандартними РСУБД, ви знаєте, що вони зберігають інформацію про бази даних, таблицях, стовпцях і т.д. в так званих системних каталогах. (Деякі системи називають їх словником даних). Ці каталоги виглядають для користувача як звичайні таблиці, але DBMS зберігає в них свою внутрішню інформацію. Одне з ключових відмінностей між PostgreSQL і стандартними РСУБД полягає в тому, що PostgreSQL зберігає в своїх каталогах набагато більше інформації: не тільки інформацію про таблиці і шпальтах, але також інформацію про типи даних, функціях, методах доступу і т.д. Таблиці каталогу можуть бути змінені користувачем і в силу цих операцій PostgreSQL може бути розширений користувачами. Для порівняння, звичайні СУБД можуть розширюватися тільки через зміну процедур, які жорстко вкомпільовані в код, тобто через зміну вихідного коду або шляхом завантаження модулів, спеціально написаних виробником СУБД.

 

Крім того, сервер PostgreSQL за допомогою динамічного завантаження може включати в себе написаний користувачем код. Таким чином, користувач може вказати файл з об'єктами кодом (наприклад, що розділяється бібліотеку) який реалізує новий тип або функцію і PostgreSQL завантажить його як тільки він буде потрібен. Ще більш просто додати до сервера код, написаний на мові SQL. Це зробить можливим змінювати операції " на льоту", що робить PostgreSQL унікальним інструментом для швидкого створення прототипів нових додатків і структур зберігання.