Praca z SQL Plus - toya.net.pliltchev/STUDIA/DATA_BASE/Oracle_SQL_PLUS.pdf · Dlaczego SQL *Plus...

Praca z SQL Plus

Agenda

• Dlaczego SQL*Plus• Wersje SQL*Plus w Oracle na systemie operacyjnym Windows• Uruchamianie SQL*Plus i ł�czenie si� z baz� danych• Rozwi�zywanie problemu ORA-01034 ORACLE not available• Wykonywanie zapisanych zapyta�• Domy�lny katalog dla SQL*Plus• Polecenie host• Praca z edytora tekstu z poziomu SQL*Plus• Czyszczenie ekranu za pomoc� CLEAR SCREEN

Agenda

• Jak efektywnie u�ywa� SQL*Plus• Przydatne ustawienia

– Jak zmieni� rozmiar i nazwy czcionki w SQL*Plus– Jak zignorowa� puste linie w SQL*Plus?

• SQL*Plus i zmienne zwi�zane• Jak zapisa� wyeksportowa� wyniku zapytania do plik Excel• Jak zako�czy� prac� z SQL*Plus• Dokumenty, zasoby

Dlaczego SQL *Plus

• Miejsce SQL*Plus w �rodowisko Oracle• SQL*Plus jest programem dost�pu do bazy danych Oracle w

trybie znakowym, który jest instalowany przy ka�dej instalacji baz� danych

• Graficzne narz�dzia, wygodne, - nie zawsze dost�pne, - komercyjne, ró�ne interfejsy ró�nych producentów

• iSQLPlus - dost�pny w Oracle 10g• W starszych wersjach SQL * Plus - podstawowe narz�dzie do

administracji wraz z Server Manager (svrmgr do wersji bazy danych Oracle 8.2) (teraz Oracle Enterprise Manager)

Historia SQL*Plus

• Oryginalna nazwa UFI (User Friendly Interface)• Autor narz�dzia SQL*Plus Bruce Scott.

Ka�dy DBA i u�ytkownik zaawansowany zna u�ytkownika Scottz hasło „tiger” - wła�ciciel schematu Scott z przykładowymi tabelami, które s� instalowane z ka�d� wersj� baz� danych Oracle, któr� instalujemy z przykładowymi schematami– pocz�tkowo wprowadzanie kwerend i otrzymywanie ich

wyników;– nast�pne

• mo�liwo�ci skryptowe - raz przetestowa�, zachowa� zapytania

• formatowanie wyników zapyta�

Do czego jest u�ywany SQL*Plus

• Wsadowe wykonywanie polece� SQL (wykonywanie skryptów SQL);

• Interaktywne wykonywanie polece� SQL - wprowadzanie, redagowanie, zachowywanie, drukowanie, wykonywanie;

• Wykonywanie bloki PL/SQL/Blok PL/SQL - grupa powi�zanych operatorów PL/SQL, które tworz� blok anonimowy (nienazwany);

• Formatowanie wyników zapyta� w raporty i eksportowanie ich do Excela, do plików tekstowych lub strony HTML;

• Uzyskanie informacji o obiektach przechowywane w bazy danych (describe);

Do czego jest u�ywany SQL*Plus

• Kopiowanie danych mi�dzy instancjami (copy);• Administracja instancji bazy danych (teraz przewa�nie z

u�yciem Oracle Enterprise Manager) - uruchamianie skryptów podczas instalacji (aktualizacji, migracji) produktów Oracle;

• Eksport (ekstrakcja) informacji z bazy danych do płaskiego pliku.- dane o stałej szeroko�ci kolumn;

- comma-delimited file - plik stosuj�cy przecinek „,” przy eksportu informacji, która pó�niej jest wprowadzana do Excela lub Ms Access.

• Tworzenie kodu SQL poprzez zapytania SQL.

Ograniczenia SQL*Plus

• Co musimy bra� po uwag�?• SQL*Plus ni jest w stanie zaprezentowa� informacja która jest w

- binarnej formie ani - typu LOB (multimedialna, d�wi�kowa, graficzna ....)

• W SQL*Plus brakuje stwierdzenie IF• Nie ma mo�liwo�ci budowy p�tli• Jest bardzo ograniczone przetwarzanie bł�dów• Jest bardzo ograniczone wsparcie kontroli wprowadzonych

przez u�ytkownika warto�ci.

Typy polece� SQL*Plus

Według autorów ksi��ki „Oracle 8 server. Ksi�ga eksperta”* mo�na wyró�ni� sze�� typów polece� SQL*Plus:

• inicjuj�ce �rodowisko SQL*Plus;• polecenia wykonywalne SQL*Plus;• polecenie edycyjne SQL*Plus;• polecenia formatuj�ce SQL*Plus;• polecenia dost�pu do ró�nych baz danych;• inne polecenia.

• * Joe Greene, „Oracle 8 server. Ksi�ga eksperta”, Helion 2000, s. 526

Typy polece� SQL*Plus

• Data Definiton Language (DDL) polecenia, które umo�liwiaj� nam konserwowa� i modyfikowa� baz� danych- CREATE TABLE tworzy� tabele, - CREATE TABLESPACE tworzy� przestrzenie tabel (tablespaces)- CREATE INDEX- CREATE USER

• Data Manipulation Commands (DMC)- INSERT, UPDATE, DELETE, SELECT

Wersje programu SQL*Plus w systemach operacyjnych Windows

S� dwie wersje programu SQL*Plus w systemach operacyjnych Windows:

- sqlplusw.exe – wersja dla systemu Windows

- sqlplus.exe – wersja dla systemu Ms DOS.

Uruchamianie SQL*Plus i ł�czenie si� do baz� danych

• W celu uruchomienia SQL*Plus nale�y:- wybra� program SQL*Plus z programów- wybra� skrót do programu SQL *Plus- wybra� �cie�ka i program

• Przy uruchomieniu programu bez parametrów si� pojawia okno dialogowe w którym nale�y wprowadzi�- User (U�ytkownik)- Password (Hasło)- Service (Nazwa usługi ł�czenia do bazy danych)

• Porgram SQL*Plus mo�na uruchomi� równie� z parametramisqlplusw user/password@serwis naprzykładsqlplusw student/student@WNTEST

• Uwaga w systemach Linux/Unix polecenie sqlplus musi by� wprowadzone małymi literami, poniewa� te systemy odró�niaj� małe i du�e litery i Root <>root, SqlPlus<>sqlplus.


• Je�eli chcemy u�y� opcji roli (SYSDBA, SYSOPER) w systemie operacyjnym Ms Windows „user/password@instance role” musz� by� uj�te w „”. Z poziomu MS-DOS wpisujemy:Sqlplusw „student/student@WNTEST as SYSDBA”

• sqlplusw /nolog uruchamia SQL*Plus bez ł�czenia si� do bazy danych

• Nale�y wykona� polecenia CONNECTUSER/PASSWORD@SERVICEw celu poł�czenia z baz� danych.SQL>CONNECT student/student@WNTEST;SQL> CONNECT scott/tiger@WNTEST;

• Poprzez polecenie CONNECT równie� mo�emy si� przeł�cza� mi�dzy instancjami bazy danych naprzykł�dSQL>CONNECT student/student@LNTEST;


• Mo�na równie� ukaza� w wierszu polece� przy uruchamianiu SQL*Plus nazwa pliku, który ma by� wykonany przy uruchamianiu SQL*Plussqlplusw user/password@service @file_name.SQLsqlplusw @file_name

• Ze wzgl�dów bezpiecze�stwa sqlplusw user/password@service @file_name.SQLwy�ej podane logowanie nale�y unika�.

• Je�eli przy uruchomieniu SQL*Plus wstawimy „-S” program SQL*Plus nie wy�wietli informacji o wersji bazy danych do którejsi� poł�czyli�my.Sqlplusw -S „user/password@service”

Co je�eli nie mo�emy si� poł�czy� z instancj� bazy danych

• Je�eli si� pojawi przy próbach uruchomienia SQL*Plus:• ORA-01034 ORACLE not available

/ baza danych Oracle jest niedost�pna/• ORA-27101 Shared memory realm does not exist • nale�y najpierw z poziomu Oracle Enterprise Manager

uruchomi� (startup) instancji bazy danych i wtedy si� poł�czy� za pomoc� SQL * Plus

• Administrator bazy danych mo�e ograniczy� dost�p u�ytkowników do bazy danych Oracle z u�yciem SQL*Plus poprzez tabel� PRODUCT_USER_PROFILE lub poprzez PROFILES.

Jak uzyska� pomoc?

• SQL>HELP• SQL>HELP INDEX

pokazuje listy dost�pnych tematów pomocy• SQL>HELP TOPIC pokazuje listy dost�pnych tematów pomocy,

ka�dy na osobnym wierszu. • Nast�pne mo�emy wpisa� polecenia dla którego chcemy

uzyska� pomoc naprzykład• SQL>HELP [POLECENIE]

SQL>HELP CL lub

• SQL>HELP CLEAR

Polecenia SQL*Plus

• Wszystkie polecenia SQL*Plus s� niezale�nie od wielko�ci liter -mo�emy stosowa� małe, du�e lub małe i du�e litery.

PoleceniaSQL>clear screenSQL>CLEAR SCREENSQL>CLEAR screens� równowa�ne.

• Uwaga nazwy plików w skryptach SQL w zale�no�ci od systemu operacyjnego s� lub nie zale�nie od wielko�ci liter. W Windows nie mamy zale�no�ci, natomiast w Unix/Linux jest zale�no�� nazwy plików od wielko�ci liter i „Nasze_zapytanie.sql” nie jest jednakowe z „nasze_zapyanie.sql”.

Wykonywanie polece� SQL*Plus

• Polecenia SQL*Plus nie musz� by� zako�czane „;”, ale mo�na na koniec wstawia� „;”.

• HOSTlub

• HOST;

Bufor SQL*Plus

• SQL*Plus przechowuje w buforze kopi� ostatnie wykonanej kwerendy (zapytania) lub blok PL/SQL. Tam równie� jest przechowywana tre�� wprowadzonego zapytania.

• W buforze s� przechowywane polecenia SQL, bloki kodu PL/SQL ale nie polecenia SQL*Plus. Na przykład w buforze nie b�dzie przechowywane SET SERVEROUTPUT ON.

• Zawarto�� bufora mo�na zachowa� na dysku w plik, który pó�niej mo�na wykona� jako skrypt SQL.SAVE nazwa_pliku[.ext] [CRE[ATE] | REP[LACE] | APP[END]]

Skrypt SQL

• Skrypt SQL jest plik tekstowy, który zawiera jedna lub wi�cej kwerend (zapyta�). W nim mo�e by� odwołanie do kolejnych skryptów SQL.

• W momencie wykonywania zapyta� z pliku SQL one s� wykonywane tak, ja by były wprowadzane z klawiatury.

• Skrypt SQL mo�e zawiera� zapytania SQL, polecenia SQL*Plus, bloki SQL.

• W skryptach SQL mo�emy przechowywa� naszej wiedzy dotycz�cej administracji, strojenia, l�dowania danych, okre�lonej wiedzy biznesowej... Skrypty oszcz�dzaj� czas i pomyłek przy ponownym wprowadzeniu zapytania.

• Skrypty SQL s� u�ywane przez firmy Oracle przy instalacji bazy danych, jej aktualizacji, migracji, przy instalacji nowych produktów.

Wykonywanie zapisanych zapyta�, kwerend

• SQL>START d:\oracle\product\10.1.0\WNTEST\BIN\test• SQL>@nazwa_zapytania.???

/je�eli rozszerzenie ??? Zapytania jest sql ??? Mo�e by� omini�te/

• Na przykład je�eli mamy - zapytanie_1.sql- zapytanie_2.txt

• w celu ich wykonania nale�y:• SQL >@zapytanie_1.sql

jest jednakowe z • SQL >@zapytanie_1• SQL >@zapytanie_2.txt


• @@ - działa tak samo jak @ z tym ró�nicy, �e poszukuje skryptu w katalogu z którym był zapuszczony główny skrypt.

• Jest u�ywane do wykonywania zapyta� SQL, które zawieraj� odwołania do kilku plików SQL w tym samym katalogu.

• Naprzykład plik wykonaj.sql posiada @@wykonaj101

• @@wykonaj102• @@wykonaj103


• W SQL*Plus zapytania s� zako�czane:– ;– /– pusty wiersz

• Symbol „;” oznacza wykonywanie zapytania. • Symbol „/” oznacza, �e trzeba wykona� zapytania.• Pusty wiersz oznacza, �e jest zako�czone wprowadzanie

zapytania ale jeszcze nie nale�y go wykonywa�. Uwaga - puste wiersze w długich zapyta� w celu czytelno�ci zapytania. Ustawienia �rodowiskowe SET SQLBLANKLINES.

Wstawianie komentarzy

W skryptach SQL w celu ich łatwiejsze czytanie, uporz�dkowanie nale�y stosowa� nagłówki, które okre�laj�:

• do czego jest u�ywany dany skrypt,• w której wersji bazy danych• je�eli jest z Internetu, z której strony• autor skryptu• data utworzenia skryptu• grupa do której nale�y dany skrypt• przykł�dowe wywołanie skryptu• przykładowe wyniki działania skryptu


• REM [ARK] - komentarz na jednym wierszu

• /* .....• Komentarze na kilku wierszach obowi�zkowo z spacji po /* i

spacji przed */• */

• -- komentarz na jednym wierszu

• W komentarzach nie wolno u�ywa� &


• Rem - Zapytanie o placach w schemacie Scott• -- To te� jest komentarz• SELECT ename, sal from scott.emp;• /* Teraz wstawiamy kometarze• na dwóch wierszach• */

• @d:\oracle\product\10.1.0\WNTEST\BIN\komentarze

Polecenie host

Poleceniem:SQL>host

lubSQL>ho

lub SQL>$

widzimy nasz katalog roboczy.Jeste�my w stanie wykona� polece� MS DOS i wróci� z powrotem

do SQL*Plus po wpisaniu polecenia exit.Równie�, mo�emy wykona� polecenie systemu operacyjnego i

wróci� do SQL*Plus automatycznie po wykonaniu polecenia$ dir

Domy�lny katalog dla SQL*Plus

Dlaczego jest wa�ne ustawiania domy�lnego katalogu? Stosuj�c domy�lny katalog nie musimy wprowadza� pełn� �cie�k� dost�pu do skryptu, którego chcemy wykona�, uruchomi�. W domy�lnym katalogu s� zapisywane pliki tymczasowe przez SQL*Plus. Katalog BIN przechowuje programy i nie jest zalecane aby tam były przechowywane skrypty SQL. W produkcyjnych �rodowiskach mo�emy mie� podkatalogi na przykład TUNING, EXPORT ...


• Jaki jest domy�lny katalog dla SQL*Plus?

• Domy�lnym katalogiem dla SQL*Plus jest ten katalog, • - w którym u�ytkownik był w momencie uruchomienia SQL*Plus;• - $ORACLE_HOME\BIN.

ORACLE_HOME oznacza główny katalog, w którym jest zainstalowane oprogramowanie Oracle.


• Katalogi, które s� przeszukiwane o skryptach SQL s� okre�lane w zmiennej �rodowiskowej SQLPATH.

• W celu jej zmiany nale�y edytowa� rejestr Windows.- Uruchamiany cmd\regedit32.- Wyszukujemy SQLPATH i edytujemy tej zmiennej.

• Inna mo�liwo�� jest przej�cie do katalogu w którym s� przechowywane nasze pliki sql i uruchomienie stamt�d SQL*Plus poprzez wykonywanie polecenia ORACLE_HOME\bin\sqlplusw.exe w naszym przypadkuOracle\client\bin\sqlplusq.exe

Polecenie DESCRIBE

Poleceniem DESCRIBE (DESC) uzyskujemy opis tabeli, widoku (perspektywy), synonimu, opis funkcji lub procedury.

DESCRIBE schema.obiekt@serwis

Naprzkład chcemy uzyska� opis tabeli hr.regions. Orzymamyopis kolumn, typ danych, długo�� (ile znaków po przecinku) DESCRIBE hr.regions@WNTEST

lubDESC hr.regions@WNTEST

Polecenie DESCRIBE

• Druga metoda uzyskania informacji jest Oracle Data Dictionary -zbiór tabel systemowych, które zbieraj� informacji o obiektach wbazie danych (repozytorium informacji o obiektach w bazie danych). Wła�ciciel tabeli opisuj�cych obiektów w bazie Oracle jest uzytkownik SYS.

• W celu uzyskaniu informacji o tabele hr.regions nale�y zada� pytanie do dwóch tabel z Oracle Data Dictionary:- ALL_TABLES;- ALL_TAB_COLUMNS.

Polecenie DESCRIBE

• Je�eli nas interesuje kwestia bezpiecze�stwa - kto ma dost�p do danej tabeli, to mo�emy uzyska� odpowied� z widoków:- ALL_TAB_PRIVS- ALL_COL_PRIVS- DBA_TAB_PRIVS_MADE- DBA_COL_PRIVS_MADE

• W celu dost�pu do powy�szych widoków, kiedy chcemy uzyska� informacj� o tabelach, które maj� inny wła�ciciel ni� mymusimy posiada� uprawnienia DBA.

Polecenie DESCRIBE

• W celu uzyskaniu informacji o indeksach w bazie nale�y u�y� tabeli:- ALL_INDEXES- ALL_IND_COLUMNS.

• Mo�emy uzyska� informacji o - wszystkich indeksach zwi�zanych z dan� tabel� lub- opis jednego indeksu

Polecenie DESCRIBE

• Wyzwalacze (triggers)• Informacja o wyzwalaczach (triggers) jest przechowywana w:

- ALL_TRIGGERS;- ALL_TRIGGER_COLS

• Synonimy (alternatywna nazwa tabeli)• Kiedy w naszych programach zamiast tabeli u�ywamy ich

synonimy zabezpieczamy si� przed zmian w nazwy, wła�ciciela, miejsca tabeli.

• W celu otrzymaniu informacji o synonimach nale�y zada� zapytanie do tabeli ALL_SYNONYMS.

Polecenie PROMPT

• Wy�wietla na monitorze pusty wiersz lub zawarto�� tekstu.

• PRO [MPT] <tekst>

• Polecenie PROMPT jest stosowane do opisu jakiej informacji dostarcz� dany skrypt, zapytanie, raport.

• Polecenie PROMPT jest stosowane wraz z ACCEPT w celu wy�wietlania informacji dotycz�cej wprowadzania niezb�dnej informacji.

Polecenie ACCEPT

• Polecenie ACCEPT prosi u�ytkownika o podaniu warto�ci zmiennej izapisuje tej warto�ci w zmiennej u�ytkownika.

• Polecenie ACCEPT pozwała w pewnym stopniu kontrolowa� warto�ci, których u�ytkownik prowadza (typ danych). Na przykład u�ytkownik nie mo�e wprowadzi� litery, tam gdzie s� wymagane warto�ci.

• ACC [EPT] <nazwa_zmiennej> [<typ zmiennej>]FOR [MAT] <format> [DEF [AULT] <warto��_domy�lna>]typ_zmiennej ::= NUM [BER] | CHAR |DATE

SQL> PROMPTSQL> PROMPT Prosz� wprowadzi� minimalnej płacy (850)SQL> ACCEPT sal_min number prompt ‘Minimalna_płaca:’

Polecenie ACCEPT

• SQL> PROMPT• SQL> PROMPT Prosz� wprowadzi� minimalnej płacy (850)• Prosz� wprowadzi� minimalnej płacy (850)• SQL> ACCEPT sal_min number prompt ‘Minimalna_płaca:’• ‘Minimalna_płaca:’870• SQL> SELECT ename, sal from scott.emp where sal>&sal_min;• old 1: SELECT ename, sal from scott.emp where sal>&sal_min• new 1: SELECT ename, sal from scott.emp where sal> 870• ENAME Płaca• ---------- ------• ALLEN 1,600• WARD 1,250• JONES 2,975• 13 rows selected.

Metodologia tworzenia raportów w SQL*Plus

• Zbieranie informacji, podejmowanie decyzji wraz z u�ytkownikiem dla którego jest przeznaczony dany raport

• Jakie informacje potrzebujemy• Jaki tytuł raportu

- Data czas wykonania raportu• Jaka cz�stotliwo�� wykonania• Jakie zmienne b�d� okre�la� zakres raportu• Jakie kolumny w raportu• Czy b�dzie suma i suma cz�stkowa w raporcie (TOTAL,

SUBTOTAL)• Nagłówek raportu• Które tabele b�dziemy u�ywa�?


• Kto b�dzie wykonywał dany raport (jako u�ytkownik biznesowy, administrator, u�ytkownik z wi�kszymi uprawnieniami)

• Czy tworzy� skrót do automatycznego wykonywania raportu w którym s� przechowywane u�ytkownik i hasło?

• Zasada bezpiecze�stwa, role...• Jaka wersja SQL*Plus w Windows b�dzie u�ywana

sqlplus lub sqlplusw?• Jaka nazwa skryptu tworz�cego raportu?• W jakim katalogu b�dzie przechowywany?• Uwagi, które wyja�niaj� kwestie zwi�zane z raportu w

celu unikni�cia dwuznaczno�ci w jego interpretacji.


– Cały proces tworzenia raportu - zapyta� SQL zachowujemy w pliku dla pó�niejszego wykonania lub zmiany.

– Proces tworzenia raportu jest interaktywny proces - budowa zapytania;- formatowanie wyników zapytania;

- rozmiar strony - parametr ile wiersze na stronySET NEWPAGE 0SET PAGESIZE 55

- ile symbole na wierszuSET LINESIZE 80

- nagłówek raportuTTITLE CENTER ‘NAZWA RAPORTU’ SKIP 2 -

LEFT ‘ODDZIAL’LEFT ‘=============================‘ -

‘=============================‘


- stopka raportuBTITLE LEFT ‘-------------------------------------------------------’ -

‘-------------------------------------------------------’SKIP 1LEFT ‘Uruchomił ‘ SQL.USERRIGHT ‘Strona ‘ FORMAT 999 SQL.PNO

- formatowanie nagłówek kolumnCOLUMN pole_1 HEADING ‘Nagłówek 1’ FORMAT 999COLUMN pole_2 HEADING ‘Nagłówek 2’ FORMAT A20 -WORD_WRAPEDCOLUMN pole_3 HEADING ‘Data ’ FORMAT A11


• - po zako�czeniu raportu wył�czenie tytułów inagłówkówTTITLE OFFBTITLE OFF

• - przerwy na stronach - BREAK- test pierwszego wariantu raportu; - wstawianie sum, sum cz�stkowych (TOTAL, SUBTOTAL)

polecenia BREAK i COMPUTE - akceptacja i proponowanie zmian przez u�ytkownika; - kolejny wariant raportu


- Komentarze, teraz wiemy co zrobili�my: - Czy za par� miesi�cy b�dziemy pami�ta�?- Praca w grupie

-- Nazwa pliku : ....................sql-- Działanie ........................-- Data Utworzenia -- Data Modyfikacji ......-- Autor ........-- Wymagana informacja wej�ciowa ........-- Przykładowy wynik ........

Formatowanie wyników zapytania

• W SQL*Plus mo�emy formatowa� wyników zapytania w celu okre�lenia jak maj� by� wy�wietlane dane, nazwa kolumn, nagłówki stron.

• Obecnie s� dost�pne graficzne narz�dzia do edycji zapyta� i eksportu wyników do Excelu (Toad) lub tworzenia raportów (Oracle Reports).

• W iSqlPlus wyniki zapytania s� lepiej przedstawione ni� w SQL*Plus.

• Podstawa formatowania wyników w SQL*Plus jest polecenie COLUMN COL [UMN].


• COL nazwa_kolumna format 9,999.99ukazuje, �e format kolumny nazwa_kolumna b�dzie liczbowy z dwóch znaków po przecinu i , oddzielaj�ca tysi�cach.

• COL nazwa_kolumna heading „NAGLÓWEK KOLUMNY”oznacza, �e nazwa_kolumna b�dzie miała nagłówek pod

nazwy „NAGLÓWEK KOLUMNY”

• Naprzykład• COL sal format 9,999.99• COL sal heading „Płaca” • Select sal, ename from scott.emp;


• SET LINESIZE 80ustała szeroko�� linii raportu na 80 symboli

• SET PAGESIZE 50ustała liczb� linii (50) na stronie raportu.

• SET TERMOUT OFFzapobiega wy�wietlaniu wyników zapytania na monitorze. Ta opcja jest u�ywana przy korzystaniu z polecenia SPOOL.

• SET ECHO OFFnie wy�wietla polece� SQL.

Zapis w pliku i wydruk wyników wykonania zapytania

• Do tego celu jest wykorzystywane polecenie SPOOL• SPO [OL] nazwa_pliku.??? OFF | OUT• Standardowe rozszerzenie nazwy pliku jest LST lub LIS• Polecenie SPOOL OFF wył�cza spooling• Polecenie SPOOL OUT wysyła wynik na drukarce.• Je�eli ustawimy zmienn� �rodowiskow�

SET TERMOUT OFF wynik zapytania nie b�dzie wy�wietlany na monitorze.

• W Windows domy�lny katalog plików jest $ORACLE_HOME\BIN. Ten katalog nie jest najlepsze miejsce dla przechowywaniu plików ze skryptami.

Praca z edytora tekstu z poziomu SQL*Plus

• Je�eli nam si� nie podoba wbudowany w SQL*Plus edytor tekstu mo�emy stosowa� domy�lny edytor (w Windows notepad; w Unix/Linux - VI)

• Kiedy jeste�my w SQL * Plus i chcemy uruchomi� domy�lnego edytora nale�y wpisa�:

• SQL> edit nazwa_zapytania.sqllub

• SQL>ed plik.sql

• Uruchomi si� domy�lny edytor SQL

Czyszczenie ekranu za pomoc� CLEAR SCREEN

• Je�eli chcemy wyczy�� ekran nale�y zastosowa� polecenia

CLEAR SCREENlub

CL SCR

Strojenie zapyta� SQL

• Uzyskanie informacji o czasie wykonywania zapyta�

• SET AUTOTRACE ON | OFF

Uzyskanie informacji o czasie wykonywania zapyta�

• W celu uzyskaniu informacji o czasie niezb�dnym dla wykonania poszczególnych zapyta� (kwerend) i planowane ich wykonywaniu ( pó�niejsza ich optymalizacji) nale�y u�y� polecenia TIMING.

• TIMI[NG] START [nazwa_zegara] | SHOW | STOP

• Opcja START - uruchamia mierzenie czasu wykonywania kwerendy.

• Opcja SHOW pokazuje czas, który upłyn�ł od uruchomienia zegara.

• Opcja STOP zatrzymuje zegara i go usuwa z bufora.

Uzyskanie informacji o czasie wykonywania zapyta�

timig start zegarselect * from hr.regions;timing showtimig stop

W celu usuni�cia wszystkich uruchomionych zegarów nale�y zastosowa� polecenia CLEAR TIMING.

Je�eli ustawimy opcji �rodowiska SET AUTOTRACE ON b�dziemy utrzymywa� po ka�dym wykonaniu zapytania jaki czas był potrzebny.

SET AUTOTRACE ON | OFF

• Je�eli mamy zapytania, o których uwa�amy, �e s� złe zoptymalizowane mo�emy uzyska� informacje jak one s� przetwarzane przez Oracle wcelu optymalizacji ich wykonania.

• Stosuj�c ustawienia zmiennej �rodowiskowej SQL*Plus AUTOTRACE na ON uzyskujemy analiza wykonania zapytania (EXPLAIN PLAN).

• SET AUTOTRACE ON• Wykonanie zapytania, dla którego chcemy uzyska� planu wykonania• Utrzymanie na monitorze planu wykonania zapytania• SET AUTOTRACE OFF - wył�cza tej opcji• Przed u�yciem opcji AUTOTRACE musi by� utworzona tabela

PLAN_TABLE, je�eli nie istnieje z u�yciem skryptu UTLXPLAN.SQL.• Problemy zwi�zane z u�yciem tej metody napotykamy wtedy, kiedy

chcemy uzyska� plany wykonania zapyta�, które maj� długi czas wykonania.

Przydatne ustawienia

• Co jest przydatne zmieni�, doda� w SQL*Plus:• Je�eli si� ł�czymy do ró�nych instancji Oracle (ró�nych) baz danych

jest przydatne wiedzie� do której bazy si� ł�czymy• Równie� jest przydatne wiedzie� czas, który jest potrzebny do

wykonania poszczególnych kwerend.• Zmieni� domy�lnego edytora w Windows z notatnik na Wordpad.exe

lub vi.exe (dla pracuj�cych pod Unix/Linux)• Jak to zrobi� (osi�gn��)?• W pliku konfiguracyjnym GLOGIN.SQL jest przechowywana informacja

o personalizacji �rodowiska SQL*Plus, która obowi�zuje dla wszystkich u�ytkowników danego komputera, którzy si� loguj� do bazy Oracle przy u�yciu SQL*Plus. Plik GLOGIN.SQL jest wykonywany automatyczne przy ka�dym uruchomieniem SQL*Plus.

• W pliku konfiguracyjnym LOGIN.SQL jest przechowywana informacja o profilu u�ytkownika.

Przydatne ustawienia - nazwa instancji bazy danych

• Przechodzimy od katalogu ORACLE_HOME d:\Oracle\client• Przechodzimy do katalogu SQL Plus \sqlplus\admin• Edytujemy lub tworzymy plik Glogin.sql• Dodajemy nast�puj�cy tekst

set lines 100set pages 100set termout offcol dbname new_value prompt_dbnameselect substr(global_name,1,instr(global_name,’.’)-1) dbnamefrom global_name;set sqlprompt „&&prompt_dbname> „set termout onset time on

• Herve Deschamps, Useful Prompt in SQL*Plus

Przydatne ustawienia - zmiana domy�lnego edytora z notatnik na wordpad

• Zmieni� domy�lnego edytora w Windows z notatnik na • Wordpad.exe lub • vi.exe (dla pracuj�cych pod Unix/Linux) jest przydatne• Nale�y skopiowa� edytora z

Program Files\Windows NT\Accessories do ORACLE_HOME\BIN

• Nale�y doda� wiersz do glogin.sql• define_editor=„wordpad.exe”

Polecenie DEFINE

Polecenie DEFINE bez parametrów pokazuje ustawie� zmiennych u�ytkownika- edytor- wersja instancji bazy danych- identyfikator serwisu (instancji)- u�ytkownika- rolaSQL>define a=textSQL>define a

DEFINE A = "text" (CHAR)UNDEFINE <nazwa_zmiennej> - parametr traci przyswojon�

mu warto�� poprzez polecenia DEFINE - usuwa zmienne u�ytkownika wprowadzone poprzez polecenia DEFINE.

Jak zmieni� rozmiar i nazwy czcionki w SQL*Plus?

• Je�eli uruchomimy sqlplus.exe (nie sqlplusw.exe) to wybieramy lewy górny k�t i lewym przyciskiem myszy wybieramy wła�ciwo�ci. Mo�emy zmieni� czcionki, kolory, układ.

• Je�eli chcemy zmieni� czcionki dla sqlplusw (SQL*Plus dla Windows) to musimy zrobi� te zmiany w rejestrach Windows.

• Uruchamiany cmd\regedit32.• Wybieramy

HKEY_LOCAL_MACHINE \SOFTWARE \ ORACLE \ ORACLE_HOME.• Tworzymy nowe wpisy w rejestru:- SQLPLUS_FONT i wprowadzany czcionki, której chcemy u�ywa�;- SQLPLUS_FONT_SIZE i okre�lamy jaki rozmiar czcionki nam

odpowiada. • Zamykamy regedit i uruchamiamy sqlplusw.exe w celu zobaczenia czy

jest efekt wskutek dokonanych przez nas zmian.

Jak zignorowa� puste linie w SQL*Plus?

• Je�eli ten parametr nie jest ustawiony i mamy puste linie przy jego wykonaniu otrzymamy bł�d.

• W celu unikni�cia bł�dów, kiedy mamy puste linie w skrypcie nale�y wprowadzi� w SQL*Plus

• Set sqlblanklines on

Wykonywanie bloków PL/SQL

• PL/SQL jest j�zyk programowania opracowany i rozwijany przez firm� Oracle, który jest proceduralne rozszerzenie j�zyka SQL. PL/SQL jest u�ywany do pisania przechowanych w bazie danych procedur, funkcji, pakietów, wyzwalaczy i zaczynaj�c od Oracle8 równie� typy obiektowe.

• SQL*Plus był na pocz�tku jedynym narz�dziem u�ywanym do pisaniu kodu PL/SQL, jego weryfikacji i wykonaniu.


• W celu wprowadzenia bloków PL/SQL trzeb by� w re�imie PL/SQL. SQL*Plus przechodzi w tym stanie je�eli:- W wierszu polece� SQL*Plus jest wprowadzone jedno z kluczowych słów

DECLARE lub BEGIN.- Wprowadzone jest polecenie SQl, które jest u�ywane do

tworzenia obiektów programowych, naprzykładCREATE PROCEDURECREATE PACKAGE

• W celu zako�czeniu i wykonaniu bloku PL/SQL nale�y wprowadzi� na nowym wierszu symbol „/”.


• W celu wy�wietlenia wyników procedury (kodu) bloku anonimowego nale�y wprowadzi� polecenieSET SERVEROUTPUT ON

• Ta opcja jest wł�czona do momentu zako�czenia pracy z SQL*Plus lub wydania poleceniaSET SERVEROUTPUT OFF

• Przykładowy blok anonimowy (nienazwany) kodu PL/SQL. Wykonywany jest pakiet DBMS.OUTPUT.BEGIN

DBMS.OUTPUT.PUT_LINE(„Witam Was”);END; Po zako�czeniu wprowadzania kodu nale�y wpisa� „/” na nowej linii.

Wprowadzenie warto�ci parametrów w czasie wykonania

• Przy wykonywanie skryptów SQL mo�emy podawa� warto�ci parametrów w wierszu polece�. SQL*Plus podstawia podan� przez nas warto�� parametru w zapytaniu. Tak uzyskujemy bardziej inteligentny kod, nie musimy zmienia� zapytania, jak si� zmieniaj� warto�ci parametrów stosowanych do filtrowania, ograniczenia wyniku zapytania.

• @nazwa_skryptu parametr1, parametr 2.... Parametr n• Chcemy uzyska� informacji o pracowników, którzy pracuj� w

departamencie 10SQL>clear buffer/wyczyszczamy bufora/ SQL> inputSQL>select ename, sal from scott.emp where deptno=&1SQL>save d:\oracle\product\10.1.0\WNTEST\BIN\placaSQL>@d:\oracle\product\10.1.0\WNTEST\BIN\placa 10


• SQL>clear buffer/wyczyszczamy bufora/ SQL> inputselect ename, sal from scott.emp where deptno=&1 and sal>&2

• save d:\oracle\product\10.1.0\WNTEST\BIN\placa2

• SQL>@d:\oracle\product\10.1.0\WNTEST\BIN\placa2 10 2500chcemy uzyska� informacje o pracowników, którzy pracuj� w 10 departamencie i maj� płac� powy�ej 2500


• SQL>select &func.(&col.) from &tab;

• Enter value for func: MAX• Enter value for col: SAL• Enter value for tab: scott.emp• old 1: select &func.(&col.) from &tab• new 1: select MAX(SAL) from scott.emp

• MAX(SAL)• ----------• 5000• Uwaga je�eli zmienna jest przed symbolem bez odst�pu nale�y po ni�

zastosowa� „.”


• SQL>Select max(&col), avg(&col), min(&col) from &tab;• Enter value for col: sal• Enter value for col: sal• Enter value for col: sal• Enter value for tab: scott.emp• old 1: Select max(&col), avg(&col), min(&col) from &tab• new 1: Select max(sal), avg(sal), min(sal) from scott.emp

• MAX(SAL) AVG(SAL) MIN(SAL)• ---------- ---------- ----------• 5000 2073,21429 800


• W celu tylko jednorazowego wprowadzania warto�ci zmiennej, która jest u�ywana kilkakrotnie nale�y zamiast & zastosowa� &&

• SQL>Select max(&&col), avg(&&col), min(&&col) from &tab;• Enter value for col: sal• Enter value for tab: scott.emp• old 1: Select max(&&col), avg(&&col), min(&&col) from &tab• new 1: Select max(sal), avg(sal), min(sal) from scott.emp• MAX(SAL) AVG(SAL) MIN(SAL)• ---------- ---------- ----------• 5000 2073,21429 800• Uwaga w stosowaniu symbolu „&” w komentarzach!

SQL*Plus i zmienne zwi�zane (bind variables)

• Zmienne zwi�zane (bind variables) s� u�ywane w celu podania warto�ci dla skryptu SQL w którym nie chcemy �eby optymalizator zmieniał planu wykonania zapytania.

• Zmienne zwi�zane (bind variables) s� zmienne, które maj� typ (number, varchar2 ...) i rozmiar, długo�� (20..).

• Stosuj�c zmienne zwi�zane (bind variables) w skryptach SQL*Plus mo�emy podawa� warto�ci które utrzymujemy w wyniku wykonania bloku PL/SQL z powrotem do SQL*Plus.

SQL*Plus i zmienne zwi�zane (bind variables)

var gr1 number;var gr2 varchar2(20);exec :gr1 :=7369;exec :gr2 :=’GARRY’;print gr1;print gr2;select :gr1, :gr2 from dual;select * from scott.emp where empno= :gr1;

• SQL*Plus Tips, http://www.oracleadvice.com/Tips/sqlplus.htm

Wybór tabeli do eksportu do Excela

• select owner, table_name from all_tables where rownum<3;

• select * from hr.regions;

Jak zapisa� wyeksportowa� wyniku zapytania do plik Excel

• Ten skrypt pokazuje jak wyeksportowa� wyniku zapytania w SQL *Plus do pliku arkusza kalkulacyjnego

SET LINESIZE 999 VERIFY OFF FEEDBACK OFFSET MARKUP HTML ON ENTMAP ON SPOOL ON PREFORMAT OFFDEFINE table_name=&lEnter value for l:hr.regions

/tabela REGIONS nale��ca do u�ytkownika HR/SPOOL & table_name..xlsSELECT * FROM &table_name;SPOOL OFFSET MARKUP HTML OFF ENTMAP OFF SPOOL OFF PREFORMAT ONSET LINESIZE 80 VERIFY ON FEEDBACK ON• Start Excel z SQL*Plus w graficznym trybie (sqlplusw.exe) HOST START excel.exe &table_name..xls

Jak zapisa� wyeksportowa� wyniku zapytania do plik Excel

SET MARKUP HTML [ON | OFF] [HEAD text] [BODY text][ENTMAP {ON | OFF}] [SPOOL {ON | OFF}][PRE[FORMAT] {ON | OFF}]

- podstawa do eksportu do arkusza kalkulacyjnego Excel

Administracja przy pomocy SQL*Plus

• Przy pomocy SQL*Plus (oraz Oracle Enterprise Manager) mo�emy:– startowa�, uruchamia� baz� danych

STARTUP– zatrzymywa� baz� danych

SHUTDOWN [NORMAL]SHUTDOWN IMMEDIATESHUTDOWN ABORT

– zmienia� trybu archiwizacjiSHOW LOGSOURCESHOW AUTORECOVERY

– monitorowa� u�ycie pami�ci - System Global Area (SGA) SHOW SGA

– zmienia� parametry instancji

Polecenie SHOW

• Stosuj�c polecenia SHOW uzyskuje informacje o parametrach bazy danych i zmiennych �rodowiska

• SHO[W] ALLPARAMETERS [parameter_name]SGA - wymaga uprawnienia DBASPOO[L]SQLCODEUSERLNOPNOREL[EASE]RECYC[LEBIN] [oryginalna_nazwa]BTI[TLE] / TTI[TLE]

Polecenie SET

Jak zako�czy� prac� z SQL*Plus?

W celu zako�czenia pracy programu SQL*Plus nale�y wpisa�:

• SQL>exit

• SQL>quit

• zamykanie okna SQL*Plus

Tematy, które nie rozpatrywali�my

• Polecenie COMPUTE• Polecenie BREAK• Polecenie PAUSE• Polecenia redagowania w SQL*Plus /Append, Changes, Input, Del .../

oraz polecenie SAVE - zachowywanie zawarto�ci bufora do pliku• Polecenie REPHEADER, REPFOOTER• Polecenie COPY - kopiowanie danych z jednej do drugiej bazy danych• Polecenie SAVE - zachowanie zawarto�ci buffera w pliku• Ustawie� �rodowiska SQL*Plus• Zachowanie w pliku zawarto�ci ustawie� �rodowiska SQL*Plus -

STORE SET• SHOW ALL - wy�wietlenie zmiennych �rodowiska SQL*Plus• Administracja bazy danych przy u�yciu SQL*Plus

Dokumentacja na temat SQL*Plus

http://www.oracle.com/technology/documentation/database10g.htmlhttp://otn.oracle.comh/sql_plushttp://asktom.oracle.comhttp://otn.oracle.com/pls/db10g/portal.portal_demo3?selected=3#index-

SQLSQL*Plus® Quick Reference Release 10.1 December 2003 Part No.

B12171-01SQL*Plus Tips, http://www.oracleadvice.com/Tips/sqlplus.htmJonathan Gennick, Oracle SQL*Plus:The Definitive Guide, O’Reilly,

ISBN:1-56592-578-5Bill Pribyl, Steven Feurstein, Oracle PL/SQL, Helion, 2002, s. 40-44• Joe Grene, Oracle 8 Server, Ksi�ga Eksperta, Helion, 2000, s. 526 -

544

Informacja w Internecie na temat SQL*Plus

• http://www.orafaq.com/faqplus.htm• http://www.oreilly.com/catalog/orsqplus/• http://otn.oracle.com/pls/db10g/portal.po

rtal_demo3?selected=1•

Praca z SQL Plus - toya.net.pliltchev/STUDIA/DATA_BASE/Oracle_SQL_PLUS.pdf · Dlaczego SQL *Plus...

Documents

Transcript of Praca z SQL Plus - toya.net.pliltchev/STUDIA/DATA_BASE/Oracle_SQL_PLUS.pdf · Dlaczego SQL *Plus...