Oracle SQL*Plus, полезные возможности

Здесь собраны примеры, демонстрирующие некоторые из полезных возможностей Oracle SQL*Plus. Все приведенные скрипты выполнены в SQL*Plus Release 11.2.0.1.0 в консоли Windows.

Подборка носит отчасти ностальгический характер, поскольку в последние годы всю повседневную оперативную работу с СУБД Oracle я делаю в PL/SQL Developer. Однако, SQL*Plus остается незаменимым для работы в консоли Unix/Linux и из shell-скриптов. Пример в конце статьи - только для Unix/Linux.

Для упрощения демонстрации написаны несколько маленьких скриптов-"подпрограмм", которые в дальнейшем вызываются из других скриптов. Вот они:

-- _banner.sql

prompt
prompt ******************************************************************
prompt &1
prompt ******************************************************************
prompt

-- _connect.sql

accept database char prompt "Enter database: "
accept username char prompt "Enter username: "

-- you'll be asked to provide password
connect &username@&database

set sqlprompt '&username.@&database. SQL> '

-- _pause_and_exit.sql

prompt
pause Press any key to exit...
quit

При запуске, открытии и закрытии сеанса работы с СУБД SQL*Plus обычно радует нас несколькими информационными сообщениями от одной до нескольких строк (выделены ниже):

C:\sandbox\sqlplus> sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Thu Jul 24 12:38:02 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

SQL> connect ay@may
Enter password:
Connected.

SQL> disconnect
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> exit

C:\sandbox\sqlplus>

Чтобы избежать этого "спама", будем запускать SQL*Plus в молчаливом (silent) режиме. Следующий скрипт использует приведенные выше скрипты-подпрограммы и демонстрирует работу в silent режиме:

-- silent.sql

@@_banner "Demonstrating silent mode of SQL*Plus"

prompt No introductory info in -silent mode, right?

prompt
pause Press any key to connect to database...
@@_connect

prompt
prompt No connected message in -silent mode, right?

prompt
pause Press any key to disconnect...
disconnect

prompt
prompt No disconnected message in -silent mode, right?

@@_pause_and_exit

Запускаю скрипт в silent режиме:

C:\sandbox\sqlplus> sqlplus -silent /nolog @silent.sql

******************************************************************
Demonstrating silent mode of SQL*Plus
******************************************************************

No introductory info in -silent mode, right?

Press any key to connect to database...

Enter database: may
Enter username: ay
Enter password:

No connected message in -silent mode, right?

Press any key to disconnect...

No disconnected message in -silent mode, right?

Press any key to exit...

C:\sandbox\sqlplus>

Переходим собственно к демонстрации полезных возможностей SQL*Plus.

Скрипт define.sql показывает, как определить, использовать и очистить переменные (substitution variables) в SQL*Plus, а также выводит значения встроенных переменных:

-- define.sql

@@_banner "Demonstrating DEFINE and UNDEFINE"

define one=ichi
define two=ni
define three=sun
define greeting="Hello World :-)"
define alo=Alo
define ha=ha

prompt &one &two &three &greeting &alo.&ha

undefine one two three greeting alo ha

prompt
prompt ******************************************************************
prompt Currently defined variables
prompt ******************************************************************
prompt

define

@@_pause_and_exit

Запускаю скрипт:

C:\sandbox\sqlplus> sqlplus -silent /nolog @define

******************************************************************
Demonstrating DEFINE and UNDEFINE
******************************************************************

ichi ni sun Hello World :-) Aloha

******************************************************************
Currently defined variables
******************************************************************

DEFINE _DATE           = "24-JUL-14" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "" (CHAR)
DEFINE _USER           = "" (CHAR)
DEFINE _PRIVILEGE      = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1102000100" (CHAR)
DEFINE _EDITOR         = "Notepad" (CHAR)
DEFINE 1               = "Demonstrating DEFINE and UNDEFINE" (CHAR)

Press any key to exit...

C:\sandbox\sqlplus>

Следующий скрипт показывает, что делать, если необходимо использовать символ & буквально (как символ):

-- @ampersand.sql

@@_banner "Demonstrating ampersand and SET DEFINE ON | OFF"

prompt "Hello & Good bye"

set define off

prompt "Hello & Good bye"

set define on

@@_pause_and_exit

Запускаю скрипт:

C:\sandbox\sqlplus> sqlplus /nolog @ampersand.sql

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jan 14 11:50:31 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


******************************************************************
Demonstrating ampersand and SET DEFINE ON | OFF
******************************************************************

Enter value for good: qwerty
"Hello qwerty bye"
"Hello & Good bye"

Press any key to exit...

C:\sandbox\sqlplus>

Как видим, в первом случае SQL*Plus запросил значение для переменной good, интерпретировав символ & как префикс имени переменной, значение которой необходимо подставить. А во втором случае, после выполнения директивы set define off, символ & был использован буквально.

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

-- calculate.sql

@@_connect

@@_banner "Demonstrating accept and SET VERIFY ON | OFF"

accept expr char prompt "Enter arithmetic expression to calculate: "

set verify off

select &expr "Result" from dual;

set verify on

select &expr "Result" from dual;

@@_pause_and_exit

Запускаю скрипт:

C:\sandbox\sqlplus> sqlplus -silent /nolog @calculate
Enter database: may
Enter username: ay
Enter password:

******************************************************************
Demonstrating accept and SET VERIFY ON | OFF
******************************************************************

Enter arithmetic expression to calculate: 2 * 2

    Result
----------
         4

old   1: select &expr "Result" from dual
new   1: select 2 * 2 "Result" from dual

    Result
----------
         4


Press any key to exit...

C:\sandbox\sqlplus>

Пользователь может ввести любое допустимое выражение SQL, в том числе, использовать функции SQL. Узнаем, например, последнюю дату текущего месяца:

C:\sandbox\sqlplus> sqlplus -silent /nolog @calculate
Enter database: may
Enter username: ay
Enter password:

******************************************************************
Demonstrating accept and SET VERIFY ON | OFF
******************************************************************

Enter arithmetic expression to calculate: last_day(sysdate)

Result
------------------
31-JUL-14

old   1: select &expr "Result" from dual
new   1: select last_day(sysdate) "Result" from dual

Result
------------------
31-JUL-14


Press any key to exit...

C:\sandbox\sqlplus>

Вычисляемые или извлекаемые из базы данных значения SQL*Plus позволяет присвоить переменным, чтобы использовать эти значения далее по ходу скрипта. Как это делается, демонстрирует следующий скрипт:

-- col_new_value.sql

@@_connect

@@_banner "Demonstrating COL ... NEW_VALUE and SET SQLPROMPT"

undefine usr db
col usr new_value usr
col db  new_value db

set termout off

select lower(user) usr,
    substr(global_name, 1, instr(global_name, '.')-1) db
from global_name;

set termout on

prompt User &&usr at database &&db

@@_pause_and_exit

Запускаю скрипт:

C:\sandbox\sqlplus> sqlplus -silent /nolog @col_new_value
Enter database: may
Enter username: ay
Enter password:

******************************************************************
Demonstrating COL ... NEW_VALUE and SET SQLPROMPT
******************************************************************

User ay at database MAY

Press any key to exit...

C:\sandbox\sqlplus>

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

-- hello_user.sql

@@_connect

@@_banner "Demonstrating SPOOL and HOST commands"

set feedback off
set echo off
set heading off
set pagesize 0
set trimspool on

spool hello_user.txt

select 'Hello '||username||'!' from all_users;

spool off

-- execute OS command
host type hello_user.txt

@@_pause_and_exit

Запускаю скрипт:

C:\sandbox\sqlplus> sqlplus -silent /nolog @hello_user
Enter database: may
Enter username: ay
Enter password:

******************************************************************
Demonstrating SPOOL and HOST commands
******************************************************************

Hello AY!
Hello IKBFACE!
Hello TEST!
... вырезал длинный список ...
Hello OUTLN!
Hello SYSTEM!
Hello SYS!


Press any key to exit...

C:\sandbox\sqlplus>

Только что выполненный скрипт - простейший прототип отчета, выводимого в текстовый файл. Другое применение данной техники - динамически сформировать скрипт и выполнить его. Например, следующий скрипт выдает привилегии на чтение всех таблиц текущей схемы указанному пользователю (grantee):

-- grant_select.sql

@@_connect

accept grantee char prompt "Enter grantee: "

set feedback off
set echo off
set heading off
set pagesize 0
set trimspool on
set verify off

@@_banner "Generating script..."

spool grant_select.txt

select 'GRANT SELECT ON '||table_name||' TO &grantee;' from user_tables;

spool off

@@_banner "Executing generated script..."

@@grant_select.txt

@@_banner "Done"

@@_pause_and_exit

Выполню скрипт, сделав таблицы текущей схемы публично доступными:

C:\sandbox\sqlplus> sqlplus -silent /nolog @grant_select
Enter database: may
Enter username: ay
Enter password:
Enter grantee: PUBLIC

******************************************************************
Generating script...
******************************************************************

GRANT SELECT ON FOLK TO PUBLIC;
GRANT SELECT ON AUTH_GROUP_PERMISSIONS TO PUBLIC;
GRANT SELECT ON AUTH_GROUP TO PUBLIC;
GRANT SELECT ON AUTH_USER_GROUPS TO PUBLIC;
GRANT SELECT ON AUTH_USER_USER_PERMISSIONS TO PUBLIC;
GRANT SELECT ON AUTH_USER TO PUBLIC;
GRANT SELECT ON DJANGO_CONTENT_TYPE TO PUBLIC;
GRANT SELECT ON DJANGO_SESSION TO PUBLIC;
GRANT SELECT ON DJANGO_SITE TO PUBLIC;
GRANT SELECT ON POLLS_POLL TO PUBLIC;
GRANT SELECT ON POLLS_CHOICE TO PUBLIC;
GRANT SELECT ON DJANGO_ADMIN_LOG TO PUBLIC;
GRANT SELECT ON AUTH_PERMISSION TO PUBLIC;

******************************************************************
Executing generated script...
******************************************************************

******************************************************************
Done
******************************************************************

Press any key to exit...

C:\sandbox\sqlplus>

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

А следующий пример демонстрирует передачу параметров в вызываемый скрипт:

-- parameters.sql
-- this script expects three or more parameters

prompt
prompt ******************************************************************
prompt Demonstrating passing parameters to script
prompt ******************************************************************
prompt

prompt You passed parameters: "&1", "&2", and "&3"

@@_pause_and_exit

Запускаю скрипт:

C:\sandbox\sqlplus> sqlplus -silent /nolog @parameters 123 qwerty "Hello world!"

******************************************************************
Demonstrating passing parameters to script
******************************************************************

You passed parameters: "123", "qwerty", and "Hello world!"

Press any key to exit...

C:\sandbox\sqlplus>

Следующий скрипт демонстрирует прерывание работы SQL*Plus при возникновении ошибки SQL или ошибки операционной системы:

-- whenever_error.sql

@@_banner "Demonstrating WHENEVER ERROR"

whenever sqlerror exit failure
whenever oserror exit failure

@@nofile

propmt "This is never printed out"

Вначале запущу скрипт, выполняющийся без ошибок, и проверю код возврата. Затем запущу whenever_error.sql и также проверю код возврата:

C:\sandbox\sqlplus> sqlplus -silent /nolog @_pause_and_exit

Press any key to exit...


C:\sandbox\sqlplus> echo Exit code is %errorlevel%
Exit code is 0

C:\sandbox\sqlplus> sqlplus -silent /nolog @whenever_error

******************************************************************
Demonstrating WHENEVER ERROR
******************************************************************

O/S Message: Permission denied

C:\sandbox\sqlplus> echo Exit code is %errorlevel%
Exit code is 1

C:\sandbox\sqlplus> 

В заключение, приведу пример, невозможный в консоли Windows, но заурядный для консоли Unix/Linux. Команды для SQL*Plus в shell-скрипте удобно записывать, используя конструкцию документ-здесь. Например, следующий фрагмент shell-скрипта создает файл all_users.lst с именами всех пользователей базы данных:

sqlplus -silent user/passwd@db <<EOT
set feedback off
set echo off
set heading off
set pagesize 0
set trimspool on
spool all_users
select username from all_users;
spool off
quit
EOT

Скачать zip-файл со всеми рассмотренными примерами можно здесь.