èçó÷åíèå îñíîâíûõ âñòðîåííûõ ôóíêöèé, ñèñòåìíûõ ïåðåìåííûõ è âûðàæåíèé ÿçûêà PL/SQL
Ëàáîðàòîðíàÿ ðàáîòà ¹3
Ïî êóðñó ÑÓÁÄ ORACLE
Ââåäåíèå
Äàííàÿ ëàáîðàòîðíàÿ ðàáîòà îðèåíòèðîâàííà íà èçó÷åíèå îñíîâíûõ âñòðîåííûõ ôóíêöèé, ñèñòåìíûõ ïåðåìåííûõ è âûðàæåíèé ÿçûêà PL/SQL:
1. Ñèñòåìíûå ïåðåìåííûå:
– SYSDATE – òåêóùàÿ äàòà è âðåìÿ
– USER – èìÿ òåêóùåãî ïîëüçîâàòåëÿ
– USERENV – çíà÷åíèÿ ñèñòåìíûõ ïàðàìåòðîâ
2. ×èñëîâûå ôóíêöèè
– ROUND – îêðóãëåíèå äî çàäàííîãî ÷èñëà çíàêîâ ïîñëå çàïÿòîé
– TRUNC – îòñå÷åíèå äî çàäàííîãî ÷èñëà çíàêîâ ïîñëå çàïÿòîé
– FLOOR – áëèæàéøåå öåëîå, íå ïðåâûøàþùåå value
– CEIL – áëèæàéøåå öåëîå, íå ìåíüøåå ÷åì value
3. Ìàòåìàòè÷åñêèå ôóíêöèè
– ABS – ìîäóëü ÷èñëà (àáñîëþòíàÿ âåëè÷èíà)
– SQRT – êâàäðàòíûé êîðåíü
– MOD – äåëåíèå ïî ìîäóëþ
– SIN, COS, TAN, ASIN, ACOS, ATAN, SINH, COSH, TANH – îñíîâíûå òðèãîíîìåòðè÷åñêèå ôóíêöèè
– EXP – ýêñïîíåíòà
– LOG(base, x), LN(x) – ëîãàðèôìû
– POWER – ñòåïåíü
4. Òåêñòîâûå ôóíêöèè
– UPPER – ïðåîáðàçîâàòü ñòðîêó ê âåðõíåìó ðåãèñòðó
– LOWER – ïðåîáðàçîâàòü ñòðîêó ê íèæíåìó ðåãèñòðó
– INITCAP – ïðåîáðàçîâàòü ïåðâûå áóêâû ñëîâ ê âåðõíåìó ðåãèñòðó
– LENGTH – äëèíà ñòðîêè ñèìâîëîâ
– SUBSTR – èçâëå÷ü ïîäñòðîêó èç ñòðîêè
– INSTR – ïîèñê ïîäñòðîêè â ñòðîêå
– LTRIM – îòñå÷åíèå âåäóùèõ ñèìâîëîâ èç íàáîðà
– RTRIM – îòñå÷åíèå êîíöåâûõ ñèìâîëîâ èç íàáîðà
– TRIM – îòñå÷åíèå çàäàííîãî ñèìâîëà
– REPLACE – çàìåíà ñèìâîëîâ â ñòðîêå
– CONCAT – ñöåïëåíèå äâóõ ñòðîê
5. Ôóíêöèè ðàáîòû ñ äàííûìè òèïà DATE
– ROUND - îêðóãëåíèå äàòû
– TRUNC – îòñå÷åíèå èíôîðìàöèè î âðåìåíè äíÿ èç äàòû
– ADD_MONTHS – äîáàâèòü çàäàííîå ÷èñëî ìåñÿöåâ ê äàòå
– LAST_DAY – ïîëó÷èòü ïîñëåäíèé äåíü ìåñÿöà óêàçàííîé äàòû
– MONTHS_BETWEEN – ÷èñëî ìåñÿöåâ ìåæäó äàòàìè
– EXTRACT – èçâëå÷ü ÷àñòü äàòû
6. Ôóíêöèè ïðåîáðàçîâàíèÿ äàííûõ
– TO_CHAR - ïðåîáðàçîâàòü âûðàæåíèå ê ñòðîêå
– TO_DATE - ïðåîáðàçîâàòü âûðàæåíèå ê òèïó äàòà
– TO_NUMBER - ïðåîáðàçîâàòü âûðàæåíèå ê òèïó ÷èñëî
– CAST - ïðåîáðàçîâàíèå òèïîâ âûðàæåíèé
7. Ñïåöèàëüíûå ôóíêöèè è âûðàæåíèÿ
– CASE – ñðàâíåíèå âûðàæåíèÿ ñ íàáîðîì çíà÷åíèé
– DECODE – ñðàâíåíèå âûðàæåíèÿ ñ íàáîðîì çíà÷åíèé
– NVL – çàìåíà NULL çíà÷åíèÿ íà çàäàííóþ âåëè÷èíó
– NVL2 – ïðîâåðêà íà NULL è âîçâðàò çàäàííîãî çíà÷åíèÿ
– COALESCE – âîçâðàò ïåðâîãî íå NULL çíà÷åíèÿ â ñïèñêå
Çàäàíèå
1. Ñèñòåìíûå ïåðåìåííûå:
– âûâåñòè íà ýêðàí òåêóùóþ äàòó, èìÿ òåêóùåãî ïîëüçîâàòåëÿ è èìÿ êîìïüþòåðà
2. ×èñëîâûå ôóíêöèè:
– âûïîëíèòü îêðóãëåíèå ÷èñëà 3276.53 äî 3276.50 è 3300.00
– âûïîëíèòü îòñå÷åíèå ÷èñëà 3276.53 äî 3276.00 è 3000.00
– íàéòè öåëûå ÷èñëà ìåæäó êîòîðûìè çàêëþ÷åí ðåçóëüòàò âûðàæåíèÿ 5.32*<òåêóùåå ÷èñëî ìåñÿöà>
3. Ìàòåìàòè÷åñêèå ôóíêöèè:
– âû÷èñëèòü ðåçóëüòàò âûðàæåíèÿ:
4. Òåêñòîâûå ôóíêöèè
– Ïðåîáðàçîâàòü ñòðîêó ‘test string FOR CONVERSION’ ê âèäó ‘Test String For Conversion’ èñïîëüçóÿ ôóíêöèè ïðåîáðàçîâàíèÿ ðåãèñòðà
– Ïðåîáðàçîâàòü ñòðîêó ‘*_abcd 1234567890 efgh_*’ ê âèäó ‘12345abcdefgh67890’ èñïîëüçóÿ ôóíêöèè îòñå÷åíèÿ, èçâëå÷åíèÿ è êîíêàòåíàöèè ñòðîê
– Ïðåîáðàçîâàòü ñòðîêó ‘20/12/2004’ â ’20.12.2004’
8. Ôóíêöèè ðàáîòû ñ äàííûìè òèïà DATE
– Âûâåñòè äàòó ïîñëåäíåãî äíÿ òåêóùåãî ìåñÿöà (èñïîëüçîâàòü ôóíêöèþ SYSDATE)
– Âû÷èñëèòü ÷èñëî ìåñÿöåâ ìåæäó ïîñëåäíèì äíåì òåêóùåãî ìåñÿöà è 1 ÿíâàðÿ 2010 ãîäà
– Âû÷èñëèòü ÷èñëî äíåé äî êîíöà ãîäà
9. Ôóíêöèè ïðåîáðàçîâàíèÿ äàííûõ
– Äàíà ñòðîêà ’(50 plus 90) divided by 10’ èñïîëüçóÿ òåêñòîâûå ôóíêöèè è ôóíêöèè ïðåîáðàçîâàíèÿ äàííûõ âû÷èñëèòü åå ðåçóëüòàò â âèäå ÷èñëà
Ïðèìåðû
--
SQL> -- ÑÎÇÄÀÍÈÅ ÒÀÁËÈÖÛ
SQL> --
SQL> DROP TABLE test_3;
DROP TABLE test_3
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> CREATE TABLE test_3 (
2 string_value VARCHAR2(20),
3 number_value NUMBER(6,2),
4 symbol_value CHAR(1),
5 date_value DATE );
Table created.
SQL>
SQL> -- ÂÑÒÀÂÊÀ ÄÀÍÍÛÕ
SQL> INSERT INTO test_3 VALUES ('AbcdeF', 1234.56, 'Z', '2004-01-12');
1 row created.
SQL> INSERT INTO test_3 VALUES ('fEDCBa', 6543.21, 'A', '2004-10-04');
1 row created.
SQL> INSERT INTO test_3 VALUES ('xyz. abc', 99.99, 'X', '2004-12-31');
1 row created.
SQL>
SQL> --
SQL> -- ÈÑÏÎËÜÇÎÂÀÍÈÅ ÑÈÑÒÅÌÍÛÕ ÏÅÐÅÌÅÍÍÛÕ
SQL> --
SQL> SELECT 'Current date:' || SYSDATE FROM DUAL;
'CURRENTDATE:'||SYSDATE
-----------------------
Current date:2004-07-24
SQL> SELECT 'User name:' || USER FROM DUAL;
'USERNAME:'||USER
----------------------------------------
User name:SYSTEM
SQL> SELECT 'User environment parameter:' || USERENV('TERMINAL') FROM DUAL;
'USERENVIRONMENTPARAMETER:'||USERENV(`TERMI
-------------------------------------------
User environment parameter:NTSRV
SQL> --
SQL> -- ÈÑÏÎËÜÇÎÂÀÍÈÅ ×ÈÑËÎÂÛÕ ÔÓÍÊÖÈÉ
SQL> --
SQL>
SQL> -- îêðóãëåíèå ÷èñëà ROUND(÷èñëîâîå âûðàæåíèå, êîëè÷åñêîâ çíàêîâ ïîñëå çàïÿòîé)
SQL> SELECT ROUND(1234.5678, 4) FROM DUAL;
ROUND(1234.5678,4)
------------------
1234.5678
SQL> SELECT ROUND(1234.5678, 3) FROM DUAL;
ROUND(1234.5678,3)
------------------
1234.568
SQL> SELECT ROUND(1234.5678, 2) FROM DUAL;
ROUND(1234.5678,2)
------------------
1234.57
SQL> SELECT ROUND(1234.5678, 1) FROM DUAL;
ROUND(1234.5678,1)
------------------
1234.6
SQL> SELECT ROUND(1234.5678, 0) FROM DUAL;
ROUND(1234.5678,0)
------------------
SQL> SELECT ROUND(1234.5678,-1) FROM DUAL;
ROUND(1234.5678,-1)
-------------------
SQL> SELECT ROUND(1234.5678,-2) FROM DUAL;
ROUND(1234.5678,-2)
-------------------
SQL> SELECT ROUND(1234.5678,-3) FROM DUAL;
ROUND(1234.5678,-3)
-------------------
SQL> SELECT ROUND(1234.5678,-4) FROM DUAL;
ROUND(1234.5678,-4)
-------------------
SQL>
SQL> -- îòñå÷åíèå ÷èñëà TRUNC(÷èñëîâîå âûðàæåíèå, êîëè÷åñêîâ çíàêîâ ïîñëå çàïÿòîé)
SQL> SELECT TRUNC(1234.5678, 4) FROM DUAL;
TRUNC(1234.5678,4)
------------------
1234.5678
SQL> SELECT TRUNC(1234.5678, 3) FROM DUAL;
TRUNC(1234.5678,3)
------------------
1234.567
SQL> SELECT TRUNC(1234.5678, 2) FROM DUAL;
TRUNC(1234.5678,2)
------------------
1234.56
SQL> SELECT TRUNC(1234.5678, 1) FROM DUAL;
TRUNC(1234.5678,1)
------------------
1234.5
SQL> SELECT TRUNC(1234.5678, 0) FROM DUAL;
TRUNC(1234.5678,0)
------------------
SQL> SELECT TRUNC(1234.5678,-1) FROM DUAL;
TRUNC(1234.5678,-1)
-------------------
SQL> SELECT TRUNC(1234.5678,-2) FROM DUAL;
TRUNC(1234.5678,-2)
-------------------
SQL> SELECT TRUNC(1234.5678,-3) FROM DUAL;
TRUNC(1234.5678,-3)
-------------------
SQL> SELECT TRUNC(1234.5678,-4) FROM DUAL;
TRUNC(1234.5678,-4)
-------------------
SQL> -- FLOOR(value) áëèæàéøåå öåëîå, íå ïðåâûøàþùåå value
SQL> SELECT FLOOR(100), FLOOR(100.5), FLOOR(99.1), FLOOR(99.5) FROM DUAL;
FLOOR(100) FLOOR(100.5) FLOOR(99.1) FLOOR(99.5)
---------- ------------ ----------- -----------
100 100 99 99
SQL>
SQL> -- CEIL(value) - áëèæàéøåå öåëîå, íå ìåíüøåå ÷åì value
SQL> SELECT CEIL(100), CEIL(100.5), CEIL(99.1), CEIL(99.5) FROM DUAL;
CEIL(100) CEIL(100.5) CEIL(99.1) CEIL(99.5)
---------- ----------- ---------- ----------
100 101 100 100
SQL>
SQL>
SQL> --
SQL> -- ÈÑÏÎËÜÇÎÂÀÍÈÅ ÌÀÒÅÌÀÒÈ×ÅÑÊÈÕ ÔÓÍÊÖÈÉ
SQL> --
SQL> SELECT -10, +10, ABS(-1), ABS(10) FROM DUAL;
-10 +10 ABS(-1) ABS(10)
---------- ---------- ---------- ----------
-10 10 1 10
SQL> SELECT 4, SQRT(4) FROM DUAL;
4 SQRT(4)
---------- ----------
4 2
SQL> SELECT SIN(1), COS(1), ASIN(0), ACOS(0) FROM DUAL;
SIN(1) COS(1) ASIN(0) ACOS(0)
---------- ---------- ---------- ----------
.841470985 .540302306 0 1.57079633
SQL> SELECT EXP(LOG(10, 10)) FROM DUAL;
EXP(LOG(10,10))
---------------
2.71828183
SQL> SELECT POWER(2,3) + SQRT(4) FROM DUAL;
POWER(2,3)+SQRT(4)
------------------
SQL> SELECT MOD(5,3) FROM DUAL;
MOD(5,3)
----------
SQL> SELECT SQRT( POWER(number_value, 2)) FROM test_3;
SQRT(POWER(NUMBER_VALUE,2))
---------------------------
1234.56
6543.21
99.99
SQL> --
SQL> -- ÒÅÊÑÒÎÂÛÅ ÔÓÍÊÖÈÈ
SQL> --
SQL>
SQL> -- ïðåîáðàçîâàíèå ðåãèñòðà òåêñòîâîé ñòðîêè
SQL> SELECT string_value,
2 UPPER(string_value) "Upper case",
3 LOWER(string_value) "Lower case"
4 FROM test_3;
STRING_VALUE Upper case Lower case
-------------------- -------------------- --------------------
AbcdeF ABCDEF abcdef
fEDCBa FEDCBA fedcba
xyz. abc XYZ. ABC xyz. abc
SQL>
SQL> -- ïðåîáðàçîâàíèå ðåãèñòðà áóêâ ñëîâ
SQL> SELECT string_value, INITCAP(string_value) "Initial capital" FROM test_3;
STRING_VALUE Initial capital
-------------------- --------------------
AbcdeF Abcdef
fEDCBa Fedcba
xyz. abc Xyz. Abc
SQL>
SQL> -- âû÷èñëåíèå äëèíû ñòðîêè
SQL> SELECT string_value, LENGTH(string_value) "String length" FROM test_3;
STRING_VALUE String length
-------------------- -------------
AbcdeF 6
fEDCBa 6
xyz. abc 8
SQL>
SQL> -- èçâëå÷åíèå ïîäñòðîêè SUBSTR(ñòðîêà, íà÷àëüíûé èíäåêñ, äëèíà)
SQL> -- (íà÷àëüíûé ñèìîâîë èìååò èíäåêñ = 1, îòðèöàëüíûé èíäåêñ îçíà÷àåò
SQL> -- îòñ÷åò ïîçèöèè ñ êîíöà ñòðîêè)
SQL> SELECT SUBSTR('ABCDEfgh', 4, 3) FROM DUAL;
SUB
---
DEf
SQL> SELECT string_value, SUBSTR(string_value, -4, 2) FROM test_3;
STRING_VALUE SU
-------------------- --
AbcdeF cd
fEDCBa DC
xyz. abc a
SQL>
SQL> -- ïîèñê ïîäñòðîêè â ñòðîêå INSTR(ñòðîêà, ÷òî_èñêàòü, íà÷àëüíûé èíäåêñ)
SQL> SELECT INSTR('abcdefg123456', 'ef', 1) FROM DUAL;
INSTR('ABCDEFG123456','EF',1)
-----------------------------
SQL> SELECT INSTR('abcdefg123456', 'ef', -3) FROM DUAL;
INSTR('ABCDEFG123456','EF',-3)
------------------------------
SQL> -- ïîêàçàòü ïî òðè ñèìâîëà íà÷èíàÿ ñ '.' èç ïîëÿ string_value
SQL> -- äëÿ ñòðîê òàáëèöû ïîëå ãäå string_value ñîäåðæèò ñèìâîë '.'
SQL> SELECT SUBSTR(string_value, INSTR(string_value, '.', 1), 3)
2 FROM test_3
3 WHERE string_value LIKE '%.%';
SUB
---
. a
SQL>
SQL> -- LTRIM(string, symbols_set) - îòñå÷åíèå âåäóùèõ ñèìâîëîâ èç íàáîðà
SQL> -- RTRIM(string, symbols_set) - îòñå÷åíèå êîíöåâûõ ñèìâîëîâ èç íàáîðà
SQL> -- åñëè íàáîð íå óêàçàí, òî ïîäðàçóìåâàåòñÿ ïðîáåë
SQL> SELECT LTRIM(' abcABC') FROM DUAL;
LTRIM(
------
abcABC
SQL> SELECT LTRIM(' abcABC', ' abc') FROM DUAL;
LTR
---
ABC
SQL> SELECT RTRIM('abcABC ') FROM DUAL;
RTRIM(
------
abcABC
SQL> SELECT RTRIM('abcABC ', ' ABC') FROM DUAL;
RTR
---
abc
SQL> -- TRIM - óíèâåðñàëüíàÿ ôóíêöèÿ îòñ÷å÷åíèÿ ñèìâîëîâ
SQL> SELECT TRIM(' ' FROM ' abcABCxyz ') FROM DUAL;
TRIM(``FR
---------
abcABCxyz
SQL> SELECT TRIM(LEADING ' ' FROM ' abcABCxyz ') FROM DUAL;
TRIM(LEADI
----------
abcABCxyz
SQL> SELECT TRIM(TRAILING ' ' FROM ' abcABCxyz ') FROM DUAL;
TRIM(TRAIL
----------
abcABCxyz
SQL> SELECT TRIM(BOTH ' ' FROM ' abcABCxyz ') FROM DUAL;
TRIM(BOTH
---------
abcABCxyz
SQL> SELECT TRIM('a' FROM 'abcABCxyz ') FROM DUAL;
TRIM(`A`F
---------
bcABCxyz
SQL> SELECT TRIM('b' FROM 'bbbABCbb') FROM DUAL;
TRI
---
ABC
SQL> SELECT TRIM(LEADING 'z' FROM 'zzzABCxyz') FROM DUAL;
TRIM(L
------
ABCxyz
SQL>
SQL> -- çàìåíà ñèìâîëîâ â ñòðîêå REPLACE(ñòðîêà, ÷òî_èñêàòü, íà_÷òî_çàìåíèòü)
SQL> SELECT REPLACE('ab_1_cd_1_ef', '_1_', ' ') FROM DUAL;
REPLACE(
--------
ab cd ef
SQL>
SQL> -- ñöåïëåíèå ñòðîê
SQL> SELECT CONCAT('abc', 'XYZ') FROM DUAL;
CONCAT
------
abcXYZ
SQL> SELECT CONCAT('xyz', CONCAT('abc', 'XYZ')) FROM DUAL;
CONCAT(`X
---------
xyzabcXYZ
SQL> SELECT 'abc' || 'XYZ' FROM DUAL;
'ABC'|
------
abcXYZ
SQL> SELECT 'xyz' || 'abc' || 'XYZ' FROM DUAL;
'XYZ'||`A
---------
xyzabcXYZ
SQL>
SQL> --
SQL> -- Ôóíêöèè ðàáîòû ñ äàííûìè òèïà DATE
SQL> --
SQL>
SQL> -- îêðóãëåíèå äàòû ROUND(äàòà, ñòðîêà_ôîðìàòà)
SQL> SELECT date_value, ROUND(date_value, 'YEAR') FROM test_3;
DATE_VALUE ROUND(DATE
---------- ----------
2004-01-12 2004-01-01
2004-10-04 2005-01-01
2004-12-31 2005-01-01
SQL> SELECT date_value, ROUND(date_value, 'MONTH') FROM test_3;
DATE_VALUE ROUND(DATE
---------- ----------
2004-01-12 2004-01-01
2004-10-04 2004-10-01
2004-12-31 2005-01-01
SQL>
SQL> -- îòñå÷åíèå èíôîðìàöèè î âðåìåíè TRUNC(äàòà, ñòðîêà_ôîðìàòà)
SQL> SELECT SYSDATE, TRUNC(SYSDATE) FROM DUAL;
SYSDATE TRUNC(SYSD
---------- ----------
2004-07-24 2004-07-24
SQL> SELECT date_value, TRUNC(date_value, 'YEAR') FROM test_3;
DATE_VALUE TRUNC(DATE
---------- ----------
2004-01-12 2004-01-01
2004-10-04 2004-01-01
2004-12-31 2004-01-01
SQL> SELECT date_value, TRUNC(date_value, 'MONTH') FROM test_3;
DATE_VALUE TRUNC(DATE
---------- ----------
2004-01-12 2004-01-01
2004-10-04 2004-10-01
2004-12-31 2004-12-01
SQL> -- äîáàâèì ñòðîêó ñîäåðæàùóþ òåêóùóþ äàòó è âðåìÿ
SQL> INSERT INTO test_3 VALUES ('str', 10, 'A', SYSDATE);
1 row created.
SQL> -- ïîïðîáóåì íàéòè ñòðîêè ñ òåêóùåé ÄÀÒÎÉ
SQL> -- íå êîððåêòíî:
SQL> SELECT date_value FROM test_3 WHERE date_value = SYSDATE;
DATE_VALUE
----------
2004-07-24
SQL> -- êîððåêòíî:
SQL> SELECT date_value FROM test_3 WHERE TRUNC(date_value) = TRUNC(SYSDATE);
DATE_VALUE
----------
2004-07-24
SQL>
SQL> -- ADD_MONTH - äîáàâèòü çàäàííîå ÷èñëî ìåñÿöåâ ê äàòå
SQL> SELECT SYSDATE, ADD_MONTHS(SYSDATE, 2) FROM DUAL;