Встроенный SQL

Для того, чтобы облегчить разработку прикладных систем, позволяет встраивать SQL в методы и тексты программ. В таком случае говорят о встроенном (Embedded) SQL. Встроенный SQL отличается от остального текста тем, что он ограничен директивой & sql(). В нем могут использоваться в качестве параметров сроки (берутся в одинарные кавычки), числа, даты (внутреннее представление).

Есть два вида доступа к полученным в результате запроса данных:

- простые SQL выражения;

- курсоры.

Простые SQL выражения можно использовать для следующих операций:

1. Insert, Update, Delete

2. DDL - создание метаданных

3. Grant и Revoke – предоставление и снятие прав на работу с объектами

4. Select выражения, которые возвращают одну строку (если возвращается более одной строки, то заносятся данные из первого)

Следующий запрос всегда возвращает только одну строку:

& sql(SELECT Name INTO: name

FROM Patient

WHERE %ID = 43)

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

& sql(SELECT Name INTO: name

FROM Patient

WHERE Age = 43)

Хорошей практикой является использование переменной SQLCODE и проверка ее значение до того, как будут использованы данные полученные в результате работы запроса:

- если SQLCODE = 0, то запрос выполнен успешно,

- если SQLCODE = 100, то запрос выполнен успешно, но не найдено ни одной записи, удовлетворяющей условие,

- если SQLCODE < 0, то под время выполнения запроса возникла ошибка.

NEW SQLCODE

& sql(SELECT Name, Home_State

INTO: CName,: CAddr

FROM Sample.Person)

IF SQLCODE=0 {

WRITE!, " Name is: ", CName

WRITE!, " State is: ", CAddr

}

ELSE {

WRITE!, " SQL error ", SQLCODE

}

При использовании простых SQL выражений, полученные данные можно присваивать как переменным, так и свойствам объекта:

Method CountStudents() As %Integer

{

& sql(SELECT COUNT(*) INTO: count

FROM MyApp.Student)

Quit count

}

SET minval = 10000

SET maxval = 50000

NEW SQLCODE

& sql(SELECT Name, Salary INTO: outname,: outsalary

FROM MyApp.Employee

WHERE Salary >: minval AND Salary <: maxval)

IF SQLCODE=0 {

WRITE!, " Name is: ", outname

WRITE!, " Salary is: ", outsalary

}

ELSE {

WRITE!, " SQL error ", SQLCODE

}

& sql(SELECT Name, Title INTO: obj.Name,: obj.Title

FROM MyApp.Employee

WHERE %ID =: id)

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

- определение курсора;

- открытие курсора;

- проведение серии операций чтения данных из курсора;

- закрытие курсора.

Для определения курсора используется команда Declare, в которой указывается как имя курсора, так и SQL выражение. Имя курсора должно быть уникальным в пределах класса или программы. Курсор не может находиться в теле рекурсивной процедуры.

Например:

& sql(DECLARE MyCursor CURSOR FOR

SELECT Name, DOB

FROM Sample.Person

WHERE Home_State =: state

ORDER BY Name

)

& sql(DECLARE MyCursor CURSOR FOR

SELECT Name, DOB

INTO: name,: dob

FROM Sample.Person

WHERE Home_State =: state

ORDER BY Name

)

После определения курсора и перед началом работы с ним необходимо его открыть. Для этого используется команда Open:

& sql(OPEN MyCursor)

Если открытие курсора прошло без ошибок, переменная SQLCODE будет равной 0.

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

& sql(FETCH MyCursor)

& sql(FETCH MyCursor INTO: a,: b)

В конце работы с курсором его обязательно необходимо закрыть:

& sql(CLOSE MyCursor)

3. Динамический SQL

Динамический SQL используется для подготовки и выполнения SQL-операций во время работы программы. Отличие от встроенного динамического SQL:

- Динамические запросы формируются во время работы программы, а не во время компиляции. Это означает, что компилятор не может проверить запрос на ошибки.

- Динамические запросы могут создавать и изменять таблицы и представления в пределах одной программы.

- Динамические запросы менее эффективны, поскольку в отличие от встроенного SQL не генерируют внутренний код для ускорения процесса выполнения запроса.

- Динамические запросы используют символ «?» для ввода значений параметров.

- Динамические запросы получают результаты с помощью специальных функций в то время, как встроенные запросы сразу возвращают значения в указанные переменные.

- В динамических запросах гораздо легче получить метаданные.

Для работы с динамическими SQL запросами используются несколько пакетов и классов. Для подготовки и выполнения динамических запросов можно использовать объект класса %SQL.Statement. Результат выполнения запроса является объектом класса %SQL.StatementResult.

Например:

SET myquery = " SELECT TOP 5 Name, DOB FROM Sample.Person"

SET tStatement = ##class(%SQL.Statement).%New()

SET tStatus = tStatement.%Prepare(myquery)

SET rset = tStatement.%Execute()

DO rset.%Display()

WRITE!, " End of data"

Для начала работы с данным объектом, его нужно создать с помощью метода %New(%SelectMode, %SchemaPath), где %SelectMode – режим вывода данных (0 – логический, 1 – ODBC, 2 - экранный); %SchemaPath – предоставляет информацию о пакете и класс к которому выполняется запрос.

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

- %Prepare(< SQL>) – проверяет синтаксис SQL-выражения;

- %PrepareClassQuery(< Имя_пакета.Ім'я_класу>, < Имя метода-запроса>) – проверяет синтаксис метода-запроса в классе < Имя_пакета.Ім'я_класу>;

- %ExecDirect() – проверяет синтаксис SQL-выражения и сразу выполняет его.

SET myquery=" SELECT TOP? Name, Age FROM Sample.Person WHERE Age >? "

SET rstatus = tStatement.%Prepare(myquery)

SET rstatus = Statement.%PrepareClassQuery(" User.queryDocTest", " DocTest")

SET myquery=2

SET myquery(1)=" SELECT Name, Age FROM Sample.Person"

SET myquery(2)=" WHERE Age > 21 AND Age < 30 ORDER BY Age"

SET rset = ##class(%SQL.Statement).%ExecDirect(,.myquery)

После проверки SQL-выражения его нужно выполнить, для этого можно использовать метод %Execute(< параметры запроса>).

SET myquery=" SELECT Name, SSN, Age FROM Sample.Person WHERE Name %STARTSWITH? "

SET tStatement = ##class(%SQL.Statement).%New()

SET tStatus = tStatement.%Prepare(myquery)

SET rset = tStatement.%Execute(" A")

В результате выполнения запроса можно получить одиночное значение, набор срок, или объект. Для вывода полей полученного объекта используется метод %Display():

DO rset.%Display()

Если результатом выполнения запроса является набор строк, то для перехода между строками используют метод %Next():

rset.%Next()

После перехода на новую строку, для работы с данными можно использовать:

а) метод %Print(), который напечатает всю срока;

WHILE rset.%Next() {

DO rset.%Print() }

б) поле fieldname, в котором содержится значение данного поля;

WHILE rset.%Next() {

WRITE " Row count ", rset.%ROWCOUNT,!

WRITE rset.Name

WRITE " age ", rset.Age

WRITE " and birth date ", rset.bdate,!!

}

в) метод %GetData(< номер колонки>), который возвратит данные из указанной колонки.

SET myquery=" SELECT TOP 5 Name, SSN, Age FROM Sample.Person"

SET tStatement = ##class(%SQL.Statement).%New()

SET tStatus = tStatement.%Prepare(myquery)

SET rset = tStatement.%Execute()

WHILE rset.%Next() {

WRITE " Years: ", rset.%GetData(3), " Name: ", rset.%GetData(1),!

}

Также для работы с динамическими запросами можно использовать обьекты классов %результатов.

set rset = ##class(%ResultSet).%New(" %DynamicQuery: SQL")
Do rset.Prepare(" SELECT ID FROM SQLUser.Worker")
Do rset.Execute() //выполнениезапроса

set rset = ##class(%ResultSet).%New(" %DynamicQuery: SQL")
Do rset.Prepare(" SELECT ID FROM SQLUser.Manager")
Do rset.Execute() //выполнение запроса