SQLPlus használata

**Az SQL*Plus használata**

· SQL*Plus parancsokat

· SQL parancsokat

· PL/SQL blokkokat

Az SQL*Plus indítása (belépés):

Sqlplus név/jelszó.

Az SQL indítása után a parancssorban megjelenik a prompt (’SQL>’ formában),

SQL>

és utána be lehet vinni a parancsokat. A parancs végét a ’;’ karakterrel jelöljük. A parancs végrehajtása után újból megjelenik az SQL> prompt.

Ha Oracle adatbáziskezelő használata esetén az sqlplus programba sysdba-ként szeretnénk bejelentkezni, akkor azt a következő módon lehet megtenni:

sqlplus sys/password@ora as sysdba

sqlplus "sys/password@ora as sysdba"

Ha egy scriptet is szeretnénk elindítani, akkor azt így lehet megtenni:

sqlplus sys/password@ora as sysdba @script.sql

sqlplus "sys/password@ora as sysdba" "@script.sql"

A macskakörmös változat Powershellben is működik, és nem kapunk hibaüzenetet a @ miatt.

A parancssor – olyan sor a képernyőn, ahová egy parancsot írhatunk.

SQL> HELP <parancs> -- megadja a parancs struktúráját, paramétereit.

Az utolsó bevitt parancs a SQL-pufferben tárolódik. Az SQL-puffer tartalmával a következő műveleteket lehet végrehajtani:

Parancs	A parancs rövid változata	Leírás
APPEND szöveg	A szöveg	Szöveget bevinni a puffer végére
CHANGE /szöveg	C /szöveg	Törli a szöveget a puffer utolsó sorából
CLEAR BUFFER	C BUFF	Törli a SQL-puffert
DEL n	-	Törli a puffer n-ik sorát
INPUT	I	Bevinni egy sor tartalmát a pufferbe
INPUT szöveg	I szöveg	Szöveg a pufferbe
LIST	L	SQL-puffer tartalmát a képernyőre
EDIT	ED	Szerkesztésre megnyitja a puffer tartalmát
EDIT file	ED file	Szerkesztésre megnyitja az állományt (ugyan úgy, mint a puffert)

Példa.

SQL> LIST

1 SELECT *

2 FROM

3 hr.jobs;

Ha a parancs hibát tartalmaz, akkor azt az SQL*Plus jelzi

SQL> SELECT

1 job

2 FROM

3 hr.jobs;

job

*Error at line 2:

ORA-00904: „JOB”: invalid identifier

Elegendő csak a hibás sort kijavítani

SQL> 2 job_id

SQL> / -- / - a parancs végrehajtása, vagy

SQL> RUN

Ha a puffer tartalmaz egy parancsot, akkor azt a RUN paranccsal lehet megjeleníteni és végrehajtani.

Példa.

Legyen a puffer tartalma

SELECT job_id FROM Hr.jobs,

akkor

SQL> RUN

1 SELECT job_id FROM Hr.jobs

job_id

-------

ad_pres

pr_rep

10 rows selected.

SQL*Plus parancsok

CLEAR SCREEN : képernyő törlése

CLEAR BUFFER : buffer törlése

EXIT : kilépés az SQL*Plusból

HELP parancsnev : egy SQL*PLus parancsról kérhetünk segítséget

HELP INDEX : felsorolja a parancsokat

SHOW ALL : SQL*Plus paraméterek listája

SHOW parameter : adott paraméter értéke

SET parameter ertek : adott paraméter beállítása

DESCRIBE tablanev : adott tábla szerkezetének kiírása

Általában a parancsszavak rövidíthetők

SAVE filename : buffer tartalmának mentése

SAVE filename REPLACE : létező file felülírása

EDIT filename : file megnyitása szerkesztésre

GET filename : file betöltése a bufferbe

DEFINE : definiált felhasználói változók listázása

DEFINE valtozo = ertek : változó definiálás

UNDEFINE valtozo : változó törlése

ACCEPT valtozo PROMPT 'szoveg' : változó értékének bekérése a szoveg megjelenítésével

Nyelvi elemek:

Megjegyzések:

REMARK szoveg (PL/SQL blokkban nem használható)

-- szoveg (tetszőleges nem SQL*Plus utasítás után)

/* */ (többsoros megjegyzés)

Fontosabb adattípusok:

NUMBER(hossz,tizedes)

INTEGER - NUMBER-rel egyenértékű

CHAR(n) – n fix hosszú (alapból n=1) karaktersorozat

VARCHAR2(n) – változó, de max. n hosszúságú karaktersorozat

LONG -változó hosszúságú karaktersorozat

BINARY_FLOAT

BINARY_DOUBLE

DATE - dátum

TIMESTAMP - idő

Változók:

-rendszerváltozók (SQL*Plus paraméterek)

-felhasználói vagy helyettesítő változók (input)

-hozzárendelt változók (output PL/SQL blokkból)

Definiáljunk egy változót: DEFINE nevem = 'Balazs Peter' (alapból

minden keppen string lesz, az ACCEPT paranccsal explicite is meg lehet adni a típust)

ACCEPT nev PROMPT 'mi a nev:'

ACCEPT valtozo NUMBER PROMPT

Bekérés & paranccsal (a változó nem definiálódik):

SELECT owner,table_name FROM all_tables WHERE owner LIKE &name;

Adja meg a(z) name értékét: 'DEMO'

régi 1: select owner,table_name from all_tables where owner

like &name

új 1: select owner,table_name from all_tables where owner like

'DEMO'

Bekérés && paranccsal (a változó definiálódik):

SELECT owner,table_name FROM all_tables WHERE owner LIKE &&name;

Adja meg a(z) name értékét: 'DEMO'

Ha nem akarjuk megjeleníteni a tárolt parancs tartalmát, akkor a / parancsot kell alkalmazni.

Az AB Adminisztrátor fontosabb parancsai a SQL*Plus-ban:

STARTUP (elindítja az AB-ist)
SHUTDOWN (megállítja az Oracle-példányt)
ARHIVE LOG (elindítja, vagy megállítja az automatikus archiválást).

DESCRABE parancs

A DESCABE paranccsal az objektumokról összefoglaló információt lehet lekérdezni. Például, meg lehet tudni, hogy milyen a customer tábla struktúrája

SQL> DESCRIBE customer

Name Null? Type

--------------------------------------------------------------

LAST_NAME NOT NULL VARCHAR2(50)

STATE_CD NOT NULL VARCHAR2(2)

SALES NOT NULL NUMBER

Az SQL parancsok végrehajtására az SQL*Plus-ban.

SQL> CREATE TABLE customer

2 (customer_id NUMBER(10) NOT NULL,

3 surname VARCHAR2(30) NOT NULL,

4 first_name VARCHAR2(20),

5 sales_region CHAR(2),

6 ytd_sales NUMBER(10.2),

7 total_sales NUMBER (14.2) );

SQL> ALTER TABLE customer

2 ADD tax_exemp_ind VARCHAR2(1);

SQL> ALTER TABLE customer

2 DROP COLUMN tax_exemp_ind;

EXECUTE (EXEC) parancs

Ezzel a paranccsal program-egységeket lehet futtatni. Például, tárolt eljárásokat.

Példa.

Először létrehozunk egy HR.Test nevű függvényt:

CREATE FUNCTION HR.Test(i IN INTEGER) RETURN INTEGER

n NUMBER;

BEGIN

n:=i*100;

RETURN(n);

END;

Utána megadjuk az INPUT N paramétert, és végrehajtjuk a függvényt. Az eredményt a PRINT parancs jeleníti meg.

SQL> variable n number

SQL> EXECUTE :n:=HR.Test(10);

PL/SQL procedure successfully completed.

SQL> PRINT n

…………….

1000

A SHOW ALL parancs által meg lehet nézni az SQL*Plus összes beállításait.

Az SQL*Plus parancsokat és azok eredményeit a

SPOOL < állomány_név>

paranccsal egy (output) állományba lehet elhelyezni.

Ez a beállítás csak akkor kezd működni, amikor azt bekapcsoljuk (OUT)

SPOOL OUT

A kikapcsolás (OFF) a

SPOOL OFF

paranccsal történik.

Példa.

SQL> SPOOL Test.log

SQL> SELECT * FROM all_objects WHERE owner=’HR’;

SQL> SPOOL OFF

Így script-állományokat lehet létrehozni, és azokat a

SQL> @Test.log

parancs segítségével végrehajtani.

DUAL tábla

Az Oracle tartalmaz egy úgy nevezet DUAL táblát. A DUAL – egyetlen sorból és egyetlen oszlopból álló tábla Az Oracle bemutatásához alkalmazzák. A parancsokban ezt a táblát akkor célszerű alkalmazni, amikor a parancsban táblára nincs szükség, de az SQL-parancs tartalmaz FROM záradékot, melyben meg kell adni bármilyen tábla-nevet, és ez lehet a DUAL tábla. A DUAL tábla tulajdonosa SYS.

Példa.

SELECT SYSDATE FROM dual;

SYSDATE

-------------

2021.08.27

set echo off
-- PLSQL beállító rész
set pagesize 0
set linesize 5000
set trims on
set head off
set feed off
--set termout off
set serveroutput on
truncate table klajosw.adatszolg;
commit;

-- dbms_output.put_line ('#-- PROCCESS VEGE --#');

ORACLE Felhasználók listája:

SELECT * FROM all_users;

A felhasználó tábláinak nevei:

SELECT table_name FROM user_tables;

SELECT table_name FROM user_tables

valtozokat tartalmazo kifejezesek

select nev, belepes from alkalmazott

where belepes between '&kezdo' and '&veg';

select nev, beosztas, fizetes, fonok, oszt_azon from alkalmazott

where beosztas = '&beosztas';

/* sqlplus valtozo hasznalata */

define eves = 'fizetes*12+nvl(jutalom,0)'

select nev, &eves from alkalmazott where &eves > 30000;

karakteres fuggvenyek

/* nagybetusse alakitas */

select nev, beosztas from alkalmazott

where beosztas = upper ('&beosztas');

/* kisbetusse alakitas */

select lower(beosztas) from alkalmazott;

/* szavak kezdo betui nagy betuve */

select initcap(nev) from alkalmazott;

/* feltoltes jobbrol vagy balrol */

select rpad(nev, 10, '_') nev, lpad(beosztas, 10) beosztas,

lpad (oszt_azon, 7, '.') reszleg from alkalmazott;

/* karakterek eltavolitasa jobb vagy bal szelrol */

select rtrim(beosztas, 'KR'), ltrim(nev, 'SM') from alkalmazott;

/* sztring pozicioja az oszlopban */

select nev, instr(nev, 'A') from alkalmazott;

/* sztring hossza */

select nev, length(nev) from alkalmazott;

/* karakterek csereje, ekezetek nelkuli kiiratas */

select translate(nev, 'ÁÉÓÖÔÚÜŰ', 'AEOOOUUU') from alkalmazott;

/* fuggvenyek egymasba agyazasa, E betuk szama a nevekben */

select nev, length(nev)-length(replace(nev, 'E')) E from alkalmazott;

numerikus fuggvények

/* kerekites */

define x=fizetes/30

select &x, round(&x), round(&x,2), round(&x,-1) from alkalmazott

where oszt_azon = 10;

/* csonkitas */

select &x, trunc(&x), trunc(&x,2), trunc(&x,-1) from alkalmazott

where oszt_azon = 10;

/* fizetes 15%-al novelt erteke */

select nev, round(fizetes*1.15) "novelt fizetes" from alkalmazott;

dátum függvények

/* aktualis datum lekerdezese */

select sysdate from sys.dual;

/* aritmetikai muveletek datumokkal */

select belepes, belepes+7, sysdate-belepes from alkalmazott

where oszt_azon = 10;

/* kulonbseg honapokban, a 160 honapnal regebben belepett alkalmazottak */

select nev, belepes, months_between(sysdate, belepes)

from alkalmazott

where months_between(sysdate, belepes) > 160;

/* honapok hozzaadasa, levonasa */

select add_months(sysdate, 2) from sys.dual;

select add_months(sysdate, -5) from sys.dual;

/* kovetkezo nap */

select next_day(sysdate, 'SUNDAY') from sys.dual;

/* honap utolso napja */

select last_day(sysdate) from sys.dual;

/* datum kerekites */

select sysdate, round(sysdate, 'month'), round(sysdate, 'year') from sys.dual;

/* datum csonkitas */

select sysdate, trunc(sysdate, 'month'), trunc(sysdate, 'year') from sys.dual;

konverzios fuggvenyek

/* szam sztringge alakitas */

select nev, to_char(fizetes, '$9,999') from alkalmazott

where beosztas = 'MANAGER';

/* datum sztringge alakitas */

select to_char(sysdate, 'yyyy month dd day') from sys.dual;

/* ido es datum */

select to_char(sysdate, 'yyyy.mm.dd HH:MI:SS') from sys.dual;

/* mahoz 2 evre milyen nap lesz */

select to_char(add_months(sysdate, 24), 'DAY') from sys.dual;

/* sztring datumma alakitas */

select to_date('96.11.20', 'YY.MM.DD') from sys.dual;

halmaz muveletek

/* unio osztalyok, ahol manager vagy ugynok dolgozik */

select distinct oszt_azon from alkalmazott

where beosztas = 'MANAGER'

union

select distinct oszt_azon from alkalmazott

where beosztas = 'UGYNOK';

/* metszet, azok az osztalyok amelyeknek van dolgozoja */

select distinct oszt_azon from alkalmazott

intersect

select oszt_azon from osztaly;

/* kulonbseg, azok az osztalyok, ahol nem dolgozik senki */

select oszt_azon from osztaly

minus

select oszt_azon from alkalmazott;

Az adatszótár leggyakrabban használt nézetei:

· ALL_CATALOG – a felhasználó számára elérhető táblák, nézetek, szinonimák, szekvenciák

· ALL_INDEXES – a felhasználó számára elérhető indexek

· ALL_IND_COLUMNS – a felhasználó számára elérhető táblákra épített indexek oszlopai

· ALL_OBJECTS – a felhasználó számára elérhető objektumok

· ALL_SEQUENCES – a felhasználó számára elérhető szekvenciák

· ALL_SYNONIMS – a felhasználó számára elérhető szinonimák

· ALL_TABLES – a felhasználó számára elérhető relációs táblák

· USERS_CATALOG – a felhasználó saját táblái, nézetei, szinonimái és szekvenciái

· USERS_TABLES – a felhasználó saját táblák leírása

· USERS_TRIGGERS – a felhasználó triggereinek leírása

Adatmodell kialakítás/kiolvasás hasznos eszközei

---- Minden tábla, minden mezője

SELECT * FROM all_tab_columns --dba_tab_columns vagy user_tab_columns

WHERE owner=<OWNER>

-- részletek itt: https://sqljana.wordpress.com/2016/12/21/oracle-data-dictionary-views-user-vs-all-vs-dba-views-100-level-basics/

;

---- Minden objektum lekérdezése

SELECT *

FROM dba_objects

WHERE owner=<OWNER>

;

---- Constraint-ek lekérdezése

SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.owner

FROM all_constraints cons, all_cons_columns cols

WHERE cons.constraint_type = 'P'

AND cons.constraint_name = cols.constraint_name

AND cons.owner = cols.owner

ORDER BY cols.table_name, cols.position

;

-----Adott táblára hivatkozó táblák

SELECT

cons.owner as child_owner,

cons.table_name as child_table,

cons.constraint_name as constaint_name,

cons.constraint_type as constraint_type,

col.owner as parent_owner,

col.table_name as parent_table,

col.column_name as column_name

FROM

dba_cons_columns col,

dba_constraints cons

WHERE

cons.r_owner = col.owner

AND

cons.r_constraint_name = col.constraint_name

AND

cons.table_name = <TABLE>

AND

col.owner = <OWNER>

;

IT, BI, DWH, DM, AI

Keresés ebben a blogban