FAQ по PL/SQL Oracle

25 декабря 2018

SQL*Plus - программа для работы с СУБД Oracle посредством командной строки. Используя SQL*Plus можно выполнять команды SQL и PL/SQL в интерактивном режиме или используя сценарий.

Основное преимущество SLQ*Plus - доступность, т.к. инструмент доступен практически всегда, когда в системе установлен Oracle. При этом это достаточно мощный инструмент, используя который можно решать различные задачи на удаленных машинах.

Параметры, подключение к базе, запуск скриптов

sqlplus /nolog - запуск без интерактивного запроса логина/пароля для входа -S - молчаливый режим -L - только одна попытка входа в интерактиве -V - показать версию SQLplus -H - отобразить справку CONNECT gennick@db01 - присоединиться к базе, запросит пароль в интерактиве HOST - переключиться на командную строку операционной системы, не разрывая соединения с базой (exit - вернет в SQLplus) sqlplus user/pass@db @script.sql - присоединиться к базе и выполнить скрипт sqlplus user/pass@db @script.sql arg "arg" - выполнить скрипт с аргументами (будут доступны как &1, &2)

Выполнить несколько строк кода (не передавая отдельный файл со скриптом), unix:

sqlplus -s user/password@db_name <<+EOF update table set value = 'foo' where id=1; update table set value = 'bar' where id=2; commit; +EOF

Выполнение SQL запросов

Запрос может состоять из нескольких строк, содержать комментарии, но не может содержать внутри пустые строки.

SQL> SELECT * /* All columns */
2 FROM project;

Запрос может быть выполнен тремя способами:

точка с запятой в конце запроса

SQL> INSERT INTO project
2 /* All columns */
3 (project_id, project_name, project_budget)
4 VALUES (1006,'Mainframe Upgrade',456789)
5 ;

строка с слешем "/" после запроса

SQL> UPDATE project
2 SET project_budget = 1000000
3 WHERE project_id = 1006
4 /

пустая строка после запроса (будет помещен в буфер, но не выполнен немедленно)

SQL> DELETE
2 FROM project
3 WHERE project_id = 1006
4
SQL>
SQL> /
1 row deleted.

Выполнение PL/SQL блоков

Пример PL/SQL блока:

SQL> DECLARE 2 X VARCHAR2(12) := 'Hello World!'; 3 BEGIN 4 DBMS_OUTPUT.PUT_LINE(X); 5 EXCEPTION 6 WHEN OTHERS THEN 7 DBMS_OUTPUT.PUT_LINE('An error occurred.'); 8 END; 9 / PL/SQL procedure successfully completed.

Правила выполнения PL/SQL блоков:

Первое слово в PL/SQL блоке должно быть из списка: BEGIN, DECLARE, CREATE PROCEDURE,
CREATE FUNCTION, CREATE TRIGGER, CREATE PACKAGE, CREATE TYPE, CREATE TYPE BODY. Регистр не важен.
Блок может состоять из нескольких строк
Можно вставлять /* комментарии */, они также могут быть на несколько строк
Пустые строки не разрешены внутри блока

Сигнал к выполнению блока может быть подан двумя путями:

Строка, содержащая только слеш "/" после блока - выполнить сразу

SQL> BEGIN
2 NULL;
3 END;
4 /

Строка, содержащая точку "." после блока - поместить в буфер. Содержимое буфера может быть выведено командой LIST и выполнено, путем указания одиночного слеша "/" или команды RUN.

SQL> BEGIN
2 NULL;
3 END;
4 .

Одиночное выражение PL/SQL может быть выполнено, используя:

EXECUTE plsql_statement

Например, так:

SQL> EXECUTE DBMS_OUTPUT.ENABLE(10000)
PL/SQL procedure successfully completed.

В случае ошибок, при выполнении процедуры, можно отобразить их выполнив команду:

SHOW ERRORS

Работа с переменными

Переменные могут быть заданы двумя способами:

&variable  - переменная в скрипте, будет запрошена каждый раз когда встретится в ходе выполнения
&&variable - переменная в скрипте, будет запрошена один раз и сохранена на всю сессию работы с SQLplus

Если уже была определена &&variable, то значение будет подставлено во все дальнейшие переменные как &variable так и &&variable.

Если была определена &&variable, и скрипт запущен повтороно в ходе той же сессии работы с SQLplus - будет использовано старое значение переменной. Чтобы этого избежать - можно запрашивать интерактивный ввод в скрипте принудительно, испольтзуя команду:

ACCEPT table_name CHAR PROMPT 'Enter the table name >'

ACCEPT можно использовать для валидации:

SQL> ACCEPT my_variable NUMBER FORMAT 999 PROMPT 'Enter a number >
'Enter a number >1234
"1234" does not match input format "999"
Enter a number >123
SQL>

Для ввода дат в определенном формате:

ACCEPT my_variable DATE FORMAT 'MM/DD/YY' PROMPT 'Give me a date >'

SQL*Plus поддерживает четыре типа переменных: CHAR, NUMBER, BINARY_FLOAT, and BINARY_DOUBLE. При вводе с клавиатуры переменная будет типа CHAR.

Несмотря на это, можно использовать NEW_VALUE, чтобы задать числовую переменную, полученную как результат запроса.

SQL> COLUMN x NEW_VALUE m y_age
SQL> SELECT 42 x FROM dual;
X
----------
42
SQL> DEFINE my_ageDEFINE MY_AGE = 42 (NUMBER)

Bind-переменные могут использоваться для передачи данных между PL/SQL и SQL блоками:

-- Bind-переменные могут быть определены в скрипте VARIABLE s_table_name varchar2(30) 

-- Можно ссылаться на bind-переменные в PL/SQL block. BEGIN :s_table_name := 'EMPLOYEE'; END; 
/ 

-- Bind-переменные могут быть использованы и в SQL запросах. SELECT index_name FROM user_indexes WHERE table_name = :s_table_name; 

-- Bind-переменные хранятся до выхода из SQL*Plus и могут быть использованы в нескольких PL/SQL блоках. 

SET SERVEROUTPUT ON 
BEGIN 
DBMS_OUTPUT.PUT_LINE(:s_table_name); 
END; 
/

Присвоить bind-переменной значение &-переменной:

DEFINE my_sub_num = 9
VARIABLE my_bind_num NUMBER
EXECUTE :my_bind_num := &my_sub_num;

Вывести значение bind-переменной:

SQL> VAR my_bind_var VARCHAR2(35)
SQL> EXECUTE :my_bind_var := 'Brighten the corner where you are';

PL/SQL procedure successfully completed.

SQL> PRINT my_bind_var
MY_BIND_VAR
-----------------------------------------------------------------
Brighten the corner where you are

Присвоить &-переменной значение bind-переменной:

SQL> DEFINE my_sub_var = ' '
SQL> VAR my_bind_var VARCHAR2(35)
SQL> EXECUTE :my_bind_var := 'Brighten the corner where you are';

PL/SQL procedure successfully completed.SQL> COLUMN my_bind_var NEW_VALUE 
my_sub_var

SQL> PRINT my_bind_varMY_BIND_VAR
----------------------------------------------------------------------Brighten the corner where you are

SQL> PROMPT &my_sub_var
Brighten the corner where you are

Получаем OUT-параметр процедуры в bind-переменную:

ACCEPT not_can CHAR PROMPT 'Enter a table reference >'

VARIABLE can VARCHAR2(60)

EXECUTE DBMS_UTILITY.CANONICALIZE('&not_can',:can, 60);

COLUMN can HEADING 'Canonicalized Reference IS:'

PRINT can

Условное выполнение в SQLplus:

ACCEPT s_delete_confirm PROMPT 'Delete project hours data (Y/N)?'

DELETE FROM project_hours WHERE UPPER('&&s_delete_confirm') = 'Y';

Пример скрипта, принимающего несколько переменных на вход в формате c возможностью задать дефолтные значения:

sqlplus user/pass@db @script.sql "var=foo; othervar=bar;"

prompt Define parameters define script_param = "" column col_script_param new_value script_param noprint select replace(trim('&&1'), ' ') "col_script_param" from dual; undef 1 define parse_name_parameter = "var=" select nvl(max(regexp_replace(csv, '(.*)(&&parse_name_parameter)(.+?)(;|$)(.*)', 'define var="\3";', 1, 1, 'i')), 'define var=default;') r from (select '&&script_param' csv from dual) where instr(upper(csv), upper('&&parse_name_parameter')) != 0; define parse_name_parameter = "othervar=" select nvl(max(regexp_replace(csv, '(.*)(&&parse_name_parameter)(.+?)(;|$)(.*)', 'define othervar="\3";', 1, 1, 'i')), 'define othervar=default_other;') r from (select '&&script_param' csv from dual) where instr(upper(csv), upper('&&parse_name_parameter')) != 0; prompt prompt var: &&var prompt othervar: &&othervar prompt

Настройки выполнения скриптов

Действуют на протяжении сессии в SQLplus.

SET SERVEROUTPUT ON/OFF SIZE 1000000 FORMAT WORD_WRAPPED - отображать вывод dbms_output                       (размер буфера, байт) (разделение по строкам для длинного вывода)SET TERMOUT ON/OFF     - включить/отключить вывод на экран терминалаSET ECHO ON/OFF        - включить/отключить отображение команд по мере выполненияSET VERIFY ON/OFF      - включить/отключить отображение замененных переменныхнапример, так:    old 9: AND ui.table_name = UPPER('&table_name')    new 9: AND ui.table_name = UPPER('project_hours')SET FEEDBACK ON/OFF    - включить/отключить вывод результата выполнения команднапример, так:    6 rows selected.    Commit complete.SET ESCAPE ON/OFF      - включить/отключить поиск символов для экранированиянапример: SQL> SET ESCAPE ON          SQL> DEFINE friends = "Joe \& Matt"SET ESCAPE /           - задать другой символ экранирования (по умолчанию \)SET CONCAT ON/OFF      - включить/отключить конкатенацию, символ конкатенации по умолчанию - точка "."например:  SQL> DEFINE sql_type = "PL/"           SQL> PROMPT &sql_type.SQL           PL/SQLSET CONCAT !           - задать другой символ для конкатенацииSET DEFINE ON/OFF      - включить/отключить замену переменных (&var, &&var)

Запомнить настройки перед выполнением скрипта и вернуть обратно по завершении выполнения:

STORE SET original_settings REPLACE ... script code here ...@original_settings

Другие команды

PROMPT Show this text.      - вывод текста на экранREMARK This is a comment.   - комментарий в скриптеREM This is a comment too.  - краткая форма записи REMARKSPOOL some_file.txt         - включить вывод выполнения скрипта файл some_file.txt (будет обнулен)SPOOL some_file.txt APP     - включить вывод выполнения скрипта файл some_file.txt (будет дозаписан)SPOOL OFF                   - отключить вывод в файлWHENEVER SQLERROR EXIT             - выйти при ошибке SQLWHENEVER SQLERROR EXIT ROLLBACK    - откатить транзакцию и выйти при ошибке SQLWHENEVER SQLERROR EXIT SQL.SQLCODE - выйти и вернуть код ошибки при ошибке SQLWHENEVER OSERROR EXIT              - команды аналогичны WHENEVER SQLERROR ... - но при ошибке операционной системы

Взаимодействие с unix shell

Обработать результат выполнения SQLplus-скрипта в Unix:

#!/bin/bashif sqlplus -s user/secret @script.sqlthen    echo Successelse    echo Unable to create raise table.fi

Вернуть код ответа в Unix:

#!/bin/bashsqlplus -s gennick/secret << EOFCOLUMN tab_count NEW_VALUE table_countSELECT COUNT(*) tab_count FROM user_all_tables;EXIT table_countEOFlet "tabcount = $?"echo You have $tabcount tables.

Вывод из скрипты в переменную командной строки unix:

#!/bin/bashtabcount=`sqlplus -s gennick/secret << EOFSET PAGESIZE 0SELECT COUNT(*) FROM user_all_tables;EXITEOF`echo You have $tabcount tables.