Разница объявляемых переменных заключается в месте их применения и соответственно в месте хранения значений этих переменных.
Подстановочные переменные определённого типа данных объявляются оператором var . Инициализировать эти переменные возможно только в PL/SQL блоке. Могут использоваться для подстановки как входных так и выходных значений полей в DML запросах. Подстановочные переменные могут так же быть в анонимных PL/SQL блоках как для передачи так и для чтения. Подстановка переменных осуществляется на этапе подстановки (bind ) для входных значений, или определения результата (define output) для выходных значений, непосредственно перед выполнением (execute ).
Важно: Подстановочные переменные не могут быть использованы для имён полей, таблиц, представлений и других объектов БД, так как они необходимы на этапе подготовки к выполнению (parse ).Переменные замещения объявляются и сразу же инициализируются символьным значением с помощью оператора def . Все встретившиеся имена переменных с префиксом & будут замещены символьным значением этих переменных. Замещение произойдёт в клиенте ешё до отправки DML/DDL/DCL выражения или PL/SQL блока на выполнение серверу БД, поэтому каких либо ограничений, какая их часть может быть замещена, не существует.
Переменные языка PL/SQL обьявляются в програмном блоке. Они не зависят от клиентской программы, так как в клиенте это только текст программы, который должен быть отправлен на сервер БД. Инициализируются переменные этого типа при выполнении програмного блока на сервере БД. Хранятся эти переменные в UGA (user global area ) так же на сервере БД.
- PL/SQL переменные обьявляются в аннонимном блоке после ключевого слова declare . В именных блоках переменные могут быть объявлены сразу после заголовка create|alter ... is|as объявления/изменения именного объекта. PL/SQL блоки могут содержать вложенные блоки. Зона видимости переменных ограничена блоком, в котором они объявлены. Время жизни переменных объявленых в пакетах (packaged variables ) - сессия, во всех остальных случаях - время выполнения именного объекта или анонимного блока.
Подстановочные переменные хоста (host or bind variables
) и переменные замещения (substitute variable
) объявляются и хранятся в клиентской программе.
Обявление переменных в клиенте зависит от клинтской программы и может несколько отличаться или вообще отсутствовать. Объявления def и var присходят от SQL*Plus, но поддерживаются также многими другими программными продуктами для работы с БД Oracle
, например: Sql Developer, TOAD.
Пример исполъзующий все виды вышеописаных переменных для динамического выполнения скриптов в SQL*Plus - есть некое клолличество скриптов, но зарение неизвестно какой из них вызывать, т.е. его надо определить динамически и тут же вызвать. Все виды переменных и алиас колонки умышлено используют одно и то же имя sqlfile:
Define sqlfile="default";
variable sqlfile varchar2(100);
declare
sqlfile varchar2(100);
begin
<
# my_sqlscript_01 running ...
привет. В чем разница между обявлениями?
1.Инструкция var:
Var id number; exec:id:= 1; SELECT * FROM table_a WHERE id= :id ;
2 и 3.DEFINE и DECLARE
DEFINE id =1; SELECT * FROM table_a WHERE id= &id; DECLARE v_text VARCHAR2(10); -- declare BEGIN v_text:= "Hello"; --assign dbms_output.Put_line(v_text); --display END;
В чем разница между обявлениями переменных?
1. var - это способ объявления переменных в SQL*Plus, которые должны иметь какой-либо тип из указанных в справке. Их можно использовать в sql и в pl/sql как для подстановки каких-либо значений так и для сохранения значений, например:
Variable value varchar2(10); begin select "a" into:value from dual; end; / select:value from dual /
2. def - это способ объявления переменных в SQL*Plus, в которых можно указать текст, который будет подставлен вместо них в те места, где они используются. Так же с помощью этой команды можно получить список всех существующих переменных которые можно использовать для подстановки.
Если объявить переменную заранее и присвоить ей значение, то оно просто будет подставлено в тексте запроса:
Define value = dual select * from &value /
Этот запрос выдаст такой результат:
Old 1: select * from &value new 1: select * from dual D - X
В переменной можно указать почти любой текст:
Define value ="23 from dual" select 1&value / old 1: select 1&value new 1: select 123 from dual 123 ---------- 123
Если переменной заранее не присвоить какое-либо текстовое значение, а просто использовать в тексте запроса, то SQL*Plus предложит ввести ее значение:
Select * from &another_value /
После выполнения этого текста SQL*Plus отобразит на экране просьбу указать значения для переменной:
Enter value for another_value:
Указав которое (в нашем случае dual) и нажав Enter мы увидим такой результат:
Old 1: select * from &another_value new 1: select * from dual D - X
При вызове просто команды
Выведется примерно такой список уже существующих переменных:
DEFINE _DATE = "03-AUG-15" (CHAR) DEFINE _CONNECT_IDENTIFIER = "orcl" (CHAR) DEFINE _USER = "SYS" (CHAR) DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR) DEFINE _SQLPLUS_RELEASE = "1102000200" (CHAR) DEFINE _EDITOR = "Notepad" (CHAR) DEFINE _O_VERSION = "Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR) DEFINE _O_RELEASE = "1102000200" (CHAR) DEFINE VALUE = "dual" (CHAR)
3. declare - это часть объявления pl/sql блока кода define ... begin ... end после которой и до begin идет объявление переменных , которые можно использовать внутри блока begin ... end Например:
Declare val1 number; val2 varchar2(10); val3 date; begin select 1, "a" into val1, val2 from dual; val3:= sysdate; dbms_output.put_line(val1); dbms_output.put_line(val2); dbms_output.put_line(val3); end; /
Разница объявляемых переменных заключается в месте их применения и соответственно в месте хранения значений этих переменных.
Подстановочные переменные определённого типа данных объявляются оператором var . Инициализировать эти переменные возможно только в PL/SQL блоке. Могут использоваться для подстановки как входных так и выходных значений полей в DML запросах. Подстановочные переменные могут так же быть в анонимных PL/SQL блоках как для передачи так и для чтения. Подстановка переменных осуществляется на этапе подстановки (bind ) для входных значений, или определения результата (define output) для выходных значений, непосредственно перед выполнением (execute ). Важно: Подстановочные переменные не могут быть использованы для имён полей, таблиц, представлений и других объектов БД, так как они необходимы на этапе подготовки к выполнению (parse ).
Переменные замещения объявляются и сразу же инициализируются символьным значением с помощью оператора def . Все встретившиеся имена переменных с префиксом & будут замещены символьным значением этих переменных. Замещение произойдёт в клиенте ешё до отправки DML/DDL/DCL выражения или PL/SQL блока на выполнение серверу БД, поэтому каких либо ограничений, какая их часть может быть замещена, не существует.
Переменные языка PL/SQL обьявляются в програмном блоке. Они не зависят от клиентской программы, так как в клиенте это только текст программы, который должен быть отправлен на сервер БД. Инициализируются переменные этого типа при выполнении програмного блока на сервере БД. Хранятся эти переменные в UGA (user global area ) так же на сервере БД.
- PL/SQL переменные обьявляются в аннонимном блоке после ключевого слова declare . В именных блоках переменные могут быть объявлены сразу после заголовка create|alter ... is|as объявления/изменения именного объекта. PL/SQL блоки могут содержать вложенные блоки. Зона видимости переменных ограничена блоком, в котором они объявлены. Время жизни переменных объявленых в пакетах (packaged variables ) - сессия, во всех остальных случаях - время выполнения именного объекта или анонимного блока.
Подстановочные переменные хоста (host or bind variables ) и переменные замещения (substitute variable ) объявляются и хранятся в клиентской программе. Обявление переменных в клиенте зависит от клинтской программы и может несколько отличаться или вообще отсутствовать. Объявления def и var присходят от SQL*Plus, но поддерживаются также многими другими программными продуктами для работы с БД Oracle , например: Sql Developer, TOAD.
Пример исполъзующий все виды вышеописаных переменных для динамического выполнения скриптов в SQL*Plus - есть некое клолличество скриптов, но зарение неизвестно какой из них вызывать, т.е. его надо определить динамически и тут же вызвать. Все виды переменных и алиас колонки умышлено используют одно и то же имя sqlfile:
Define sqlfile="default";
variable sqlfile varchar2(100);
declare
sqlfile varchar2(100);
begin
<
Программные единицы в составе БД Oracle - хранимые процедуры, функции и пакеты.
Хранимая процедура (stored procedure) — это программа, которая выполняет некоторые действия с информацией в и при этом сама хранится в базе данных. В Oracle хранимые процедуры можно можно писать на языках PL/SQL и Java.
Хранимые процедуры могут входные параметры и возвращать результаты. В отличие от триггеров, которые принадлежат определенной таблице или представлению, хранимые процедуры принадлежат базе данных в целом. Они могут вызываться любым процессом, использующим базу данных, при условии, что у этого процесса есть достаточные права доступа.
Хранимые процедуры используются для многих целей. Хотя администраторы баз данных используют их для выполнения рутинных задач администрирования, главной областью их применения являются все же приложения баз данных. Эти процедуры могут вызываться из прикладных программ, написанных на таких языках, как Java, С#, С++ или, а также из веб-сценариев, написанных на VBScript или JavaScript. Кроме того, эти процедуры можно вызывать в интерактивном режиме из командной оболочки SQL*Plus.
Можно выделить следующие преимущества хранимых процедур:
В отличие от кода приложений, хранимые процедуры никогда не передаются на клиентские компьютеры. Она всегда находятся в базе данных и выполняются СУБД на том компьютере, где располагается сервер базы данных. Таким образом, они более безопасны, чем распространяемый код приложения, а кроме того, снижают сетевой трафик. Хранимые процедуры постепенно становятся предпочтительным режимом реализации логики приложения в сети Интернет и корпоративных интрасетях. Еще одно преимущество хранимых процедур заключается в том, что SQL-операторы в них могут быть оптимизированы компилятором СУБД.
CREATE OR REPLACE PROCEDURE TESTPRM(NUM IN NUMBER)
in_COMP VARCHAR2(50);
SELECT COMPANY INTO in_COMP FROM customers
WHERE customers. CUST_NUM = NUM;
DBMS_OUTPUT. enable;
DBMS_OUTPUT. put_line(in_COMP);
SET SERVEROUTPUT ON
Функция – это подпрограмма, которая вычисляет значение.
CREATE OR REPLACE Function FindCourse
(name_in IN varchar2)
SELECT course_number
FROM courses_tbl
WHERE course_name = name_in;
fetch c1 into cnumber;
if c1%notfound then
WHEN OTHERS THEN
raise_application_error(-20001,"An error was encountered - "||SQLCODE||" - ERROR - "||SQLERRM);
CREATE OR REPLACE PACKAGE имя_модуля {IS AS}
описание_процедуры |
описание_функции |
объявление_переменной |
определение_типа |
объявление_исключительной_ситуации |
объявление_курсора |
END [имя_модуля];
Тело модуля определяется так:
CREATE OR REPLACE PACKAGE BODY имя_модуля {IS AS}
код_инициализации_процедуры |
код_инициализации_функции |
END [имя_модуля];
По большому счету тело модуля не является обязательной частью. Если заголовок модуля содержит описание, скажем нескольких переменных, типов и курсоров, то создавать тело модуля нет необходимости. Такой способ целесообразен при объявлении глобальных переменных, поскольку все объекты модуля видимы вне его пределов.
процедуры
Синонимы в БД Oracle
Синоним (Synonym) – это альтернативное имя (псевдоним) для объекта схемы. Если для какого либо объекта базы данных Oracle существует синоним, то к объекту из SQL запроса можно обращаться либо по его настоящему имени, либо по синониму. Так же они обеспечивают некоторый уровень безопасности, поскольку скрывают имя объекта и его , а так же делают прозрачным местоположение удаленных объектов распределенных баз данных.
Синонимы позволяют переименовывать и перемещать базовые объекты. При том переопределяется только синоним, а приложение не требует никаких модификаций.
Различают два типа синонимов:
Частный (PRIVATE)- синонимы содержаться в схеме конкретного пользователя и доступны только самому пользователю, и тем, кому он предоставил соответствующие права доступа.
Общий (PUBLIC)- этими синонимами владеет специальная группа пользователей – PUBLIC, в результате чего эти синонимы доступны всем пользователям базы данных.
CREATE SYNONYM [имя_синонима] FOR[имя_объекта]
CREATE PUBLIC SYNONYM [имя_синонима] FOR[имя_объекта]
Словарь данных - назначение, основные представления
Oracle поддерживает исчерпывающий словарь метаданных. Этот словарь описывает структуру таблиц, последовательностей, представлений, индексов, ограничений, хранимых процедур и многое другое. Он также содержит исходные тексты процедур, функций и триггеров.
В таблице DICT словаря метаданных содержатся данные, описывающие сам словарь. Можно запрашивать данные из этой таблицы, чтобы узнать больше о содержимом словаря данных, но нужно иметь в виду, что она имеет большие размеры. Например, если запросить имена всех таблиц словаря данных, будет возвращено более 800 строк.
Предположим, нужно узнать, какие таблицы с информацией о пользовательских и системных таблицах имеются в словаре данных. В этом может помочь следующий запрос:
SELECT Table_Name, Contents
WHERE Table_Name LIKE (" %TABLES%");
Будет возвращено около двадцати пяти строк. Одна из таблиц будет называться USER_TABLES. Чтобы увидеть столбцы этой таблицы, нужно ввести:
DESC USER_TABLES;
Можно использовать эту стратегию для получения из словаря метаданных информации об интересующих объектах и структурах. В табл. 4.1 перечислены многие из представлений и указано их назначение. Таблицы USER_SOURCE и USER_TRIGGERS полезны, когда требуется узнать, исходные тексты каких процедур и триггеров хранятся в настоящий момент в базе данных.
Таблица 4.1. Метаданные в СУБД Oracle
Имя таблицы
Содержимое
Метаданные, описывающие словарь данных
Список таблиц, представлений, последовательностей и других структур, принадлежащих пользователю
Структуры таблиц пользователя
USER_TAB_COLUMNS
Потомок таблицы USER TABLES. Содержит данные о столбцах таблиц. Синонимом является COLS
Пользовательские представления
USER_ CONSTRAINTS
Пользовательские ограничения
USER_CONS_COLUMNS
Потомок таблицы USER_CONSTRAINTS. Содержит столбцы, на которые наложены ограничения
Метаданные, описывающие триггеры. Есть смысл запрашивать столбцы Trigger Name, Trigger Type и Trigger Event. Предупреждение: Trigger Body в действительности не содержит исходного кода триггера
Исходные тексты. Например, для получения текста процедуры MYTRIGGER: SELECT Text FROM USER_SOURCE WHERE Name = "MYTRIGGER" AND Type = "PROCEDURE"
Dynamic performance views в словаре данных Oracle - назначение и основные представления
Locks currently held/requested on the instance
Sessions/processes holding a latch
Cursors opened by sessions on the instance
Sessions currently connected to the instance
Different resources sessions are currently waiting for
