logo Записная книжка ежа

Шпаргалка по SQL*Plus

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

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

К написанию этой статьи меня подтолкнула книга "Oracle SQL*Plus: The Definitive Guide, 2nd Edition", написанная Jonathan Gennick. Как обычно, появилось желание систематизировать информацию и представить ее в удобном виде.

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

Область возможного использования SQLplus при этом гораздо шире, например - построение отчетов, в том числе в формате HTML. 

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

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_age
DEFINE 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_var

MY_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/SQL

SET 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.  - краткая форма записи REMARK

SPOOL some_file.txt         - включить вывод выполнения скрипта файл some_file.txt (будет обнулен)
SPOOL some_file.txt APP     - включить вывод выполнения скрипта файл some_file.txt (будет дозаписан)
SPOOL OFF                   - отключить вывод в файл


WHENEVER SQLERROR EXIT             - выйти при ошибке SQL
WHENEVER SQLERROR EXIT ROLLBACK    - откатить транзакцию и выйти при ошибке SQL
WHENEVER SQLERROR EXIT SQL.SQLCODE - выйти и вернуть код ошибки при ошибке SQL

WHENEVER OSERROR EXIT              - команды аналогичны WHENEVER SQLERROR ... - но при ошибке операционной системы

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

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

#!/bin/bash
if sqlplus -s user/secret @script.sql
then
    echo Success
else
    echo Unable to create raise table.
fi

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

#!/bin/bash
sqlplus -s gennick/secret << EOF
COLUMN tab_count NEW_VALUE table_count
SELECT COUNT(*) tab_count FROM user_all_tables;
EXIT table_count
EOF

let "tabcount = $?"
echo You have $tabcount tables.

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

#!/bin/bash
tabcount=`sqlplus -s gennick/secret << EOF
SET PAGESIZE 0
SELECT COUNT(*) FROM user_all_tables;
EXIT
EOF`

echo You have $tabcount tables.