Инспектор
Спонсор: Microsoft
Вы можете этот курс.
Опубликован: 11.03.2009 | Уровень: специалист | Доступ: платный
Лекция 9:
Внутренний язык СУБД. Сравнительные характеристики T-SQL и PL/SQL
Неявное использование переменной цикла
declare
n number := 100;
begin
for n in 1..10 loop
dbms_output.put_line(n);
end loop;
dbms_output.put_line('after ' || n);
end;
declare
n number;
begin
for i in 1..10 loop
n := i;
dbms_output.put_line(n || ' ' || i);
-- error
-- i := 20;
end loop;
dbms_output.put_line(n);
end;Динамическое изменение границ
declare
n number := 3;
begin
for i in 1..n loop
if n < 10 then
n := n+1;
end if;
dbms_output.put_line(i || ' ' || n);
end loop;
end;Использование меток
begin
<<l1>>
for i in 1..3 loop
for i in 5..6 loop
dbms_output.put_line(l1.i || ' ' || i);
end loop;
end loop;
end;
begin
<<l1>>
for i in 1..3 loop
for i in 5..6 loop
dbms_output.put_line(l1.i || ' ' || i);
exit l1 when i = 5;
end loop;
end loop;
end;
-- goto
declare
n number;
begin
if n = null then
dbms_output.put_line('in if');
else
goto met1;
end if;
dbms_output.put_line('after if');
<<met1>>
dbms_output.put_line('after met1');
end;
declare
n number := 1;
begin
for i in 1..10 loop
if i = 5 then
goto cont;
end if;
n := n+1;
dbms_output.put_line(n);
<<cont>>
null;
end loop;
end;Основные объекты. Хранимые процедуры и функции
Хранимая процедура или функция - это подпрограмма, состоящая из SQL операторов и команд процедурного языка. Хранимая процедура и функция может:
- Содержать параметры (аргументы);
- Вызывать другие процедуры;
- Возвращать свой статус вызывающей процедуре или пакету, указывающий на успешное окончание или ошибку, и в случае ошибки на ее причину;
- Возвращать значения параметров вызывающей процедуре или пакету;
- Выполняется всегда на стороне сервера.
- Функция кроме того возвращает результат через свое имя.
Хранимые процедуры и функции значительно увеличивают мощность, эффективность и гибкость языка SQL и значительно ускоряют выполнение SQL-операторов и пакетов.
Хранимые процедуры создаются с помощью команды create procedure. Функции создаются с помощью команды - create function. Для выполнения хранимой процедуры, как системной, так и определенной пользователем, используется команда execute (выполнить). Можно также просто указать название хранимой процедуры, если оно является первым словом в операторе или пакете.
Хранимая процедура или функция имеет две части:
- Спецификация, которая объявляет процедуру или функцию и состоит из следующей информации:
- Тело, которое определяет процедуру или функцию. Тело процедуры состоит из блока PL/SQL или T-SQL (который содержит предложения SQL и процедурного расширения).
Основные объекты. Триггеры
Триггер - это хранимая процедура специального вида, которая запускается при возникновении какого-либо события, обычно изменения данных в таблице. В частности, триггеры помогают сохранить ссылочную целостность данных пользователя, проверяя их согласованность в логически связанных таблицах. Основным достоинством триггеров является то, что они вызываются автоматически. Они будут работать независимо от причины, которая вызвала модификацию данных, как, например, после ввода данных клерком, так и при выполнении некоторой прикладной процедуры. Триггер может быть связан с одним или несколькими операторами модификации, такими как update, insert или delete.
Практические занятия
Постановка задачи. Проектирование данных на концептуальном и логическом уровнях. Нормализация отношений.
Презентация по ER-моделированию
Подготовка SQL скриптов генерации схемы отношений БД в ERwin. Разработка скрипта для ввода тестовой информации.
Видео-презентация (Для проигрывания требуется Windows Media Player)
Архитектура MS SQL Server 2005. Настройка и использование основных компонент среды. Создание учебной базы данных.
Видео-презентация (Для проигрывания требуется Windows Media Player)
Работа с СУБД MS SQL Server 2005, ORACLE 10g. Примеры соединений с БД, технологии разработки клиенского приложения
Использование технологии Java Database Connectivity (JDBC) для работы с базами данных
Примеры к презентации
SQL-скрипты, проект и исходные коды
package org.mai806.jdbcsample;
import java.sql.*;
public class QuerySample {
public static void main(String[] args) throws Exception {
/* ======== Подключение к MS SQL Server ===== */
// Загрузка драйвера
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
// Соединение с базой данных
Connection connection = DriverManager.getConnection(
"jdbc:sqlserver://localhost:1433;databaseName=o01;",
// localhost - сервер СУБД, o01 - имя базы данных
"sa", "123"); // пользователь, пароль
/* ======== Подключение к Oracle ============
// Загрузка драйвера
Class.forName("oracle.jdbc.OracleDriver");
// Соединение с базой данных
Connection connection = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:orcl",
// localhost - сервер СУБД, orcl - SID базы оракла
"o01", "o01"); // пользователь, пароль
// Создание Statement
PreparedStatement stmt = connection.prepareStatement
("select ID, NAME from PERSON where NAME like ?");
stmt.setString(1, "%S%");
// Выполнение запроса
ResultSet rs = stmt.executeQuery();
// Перебор результата выполнения запроса
while(rs.next()) {
// Пример выбора параметра по номеру или по имени
System.out.println("ID: " +
rs.getLong(1) + "; NAME="+
rs.getString("NAME"));
}
// закрытие использованных ресурсов БД
rs.close();
stmt.close();
connection.close();
}
}
Листинг
P.1.
Выполнение запроса: QuerySample.java
package org.mai806.jdbcsample;
import java.sql.*;
import java.util.ResourceBundle;
public class StoredProcedureSample {
private static Connection connection = null;
public static void main(String[] args) throws Exception {
// Получение соединения из значений параметров в файле properties
ResourceBundle properties = ResourceBundle.getBundle("database");
Class.forName(properties.getString("driver"));
connection = DriverManager.getConnection(
properties.getString("url"),
properties.getString("user"),
properties.getString("password"));
transferAmount(1,2,100.0);
connection.close();
}
/**
* Переводит указанную сумму с одного счета на другой
* @param from счет плательщика
* @param to счет получателя
* @param amount сумма
*/
public static void transferAmount(long from, long to, double amount)
throws Exception {
// Создание Statement
CallableStatement stmt
= connection.prepareCall("{call TransferAmount(?,?,?)}");
// Установка параметров
stmt.setLong(1, from);
stmt.setLong(2, to);
stmt.setDouble(3, amount);
// Выполнение процедуры
stmt.execute();
}
}
Листинг
P.2.
Выполнение хранимой процедуры: StoredProcedureSample.java
package org.mai806.jdbcsample;
import java.sql.*;
import java.util.ResourceBundle;
public class TransactionalSample {
private static Connection connection = null;
public static void main(String[] args) throws Exception {
// Получение соединения из значений параметров в файле properties
ResourceBundle properties = ResourceBundle.getBundle("database");
Class.forName(properties.getString("driver"));
connection = DriverManager.getConnection(
properties.getString("url"),
properties.getString("user"),
properties.getString("password"));
// Ручное управление транзакциями
connection.setAutoCommit(false);
try {
transferAmount(2, 1, 10.0);
} finally {
connection.close();
}
}
/**
* Переводит указанную сумму с одного счета на другой
* @param from счет плательщика
* @param to счет получателя
* @param amount сумма
*/
public static void transferAmount(long from, long to,
double amount) throws Exception {
PreparedStatement stmt = null;
Statement query = null;
try {
stmt = connection.prepareStatement
("update ACCOUNT set AMOUNT=AMOUNT+? where ID=?");
// Забираем сумму со счета плательщика
stmt.setDouble(1, -amount);
stmt.setLong(2, from);
stmt.execute();
// Добавляем сумму на счет получателя
stmt.setDouble(1, amount);
stmt.setLong(2, to);
stmt.execute();
// Пост-проверка: отрицательность счета плательщика
query = connection.createStatement();
ResultSet rs = query.executeQuery(
"select AMOUNT from ACCOUNT where ID="+from+" and AMOUNT<0");
if (rs.next()) {
throw new Exception("На счете №"+from+"
недосточно средств ["+(amount+rs.getDouble(1))+"]
для снятия суммы ["+amount+"]");
}
connection.commit();
System.out.println("Перечисление средств успешно выполнено");
} catch(Exception e) {
e.printStackTrace();
connection.rollback();
} finally {
if (stmt!=null)
stmt.close();
if (query!=null)
query.close();
}
}
}
Листинг
P.3.
Работа с транзакциями: TransactionalSample.java
