Описание на оракула на инстр. Лабораторна работа. Работа с низови функции

Символна функция приема една или повече символни стойности като параметър и връща знак и числова стойност. Ако символна функция връща символна стойност, тя винаги е от тип VARCHAR2 (променлива дължина) - с изключение на функциите UPPER и LOWER. Тези функции преобразуват дадения низ съответно в главни или малки букви и връщат CHAR стойност с фиксирана дължина, ако низовете, предадени като аргументи, са CHAR.

Кратко резюме на функциите за низове

Както бе споменато по-рано, PL/SQL предоставя на програмиста широк набор от мощни низови функции на високо ниво за получаване на информация за низове и модифициране на тяхното съдържание. Следващият списък дава представа за техните възможности и синтаксис. Отзад пълна информацияЗа конкретни функции, моля, вижте ръководството Oracle SQLсправка.

• ASCII(символ) Връща ЧИСЛОВИЯ код за представянето на посочения знак в набора от символи на базата данни.
• ASCIITR(string1) Получава низ във всеки набор от знаци и го преобразува в ASCII символен низ. Всички не-ASCII символи са представени във формата \XXXX, където XXXX е Unicode кодът за знака.

За информация относно Unicode и кодовете на знаци посетете http://unicode.org.

• CHR(код)
  Връща знак от тип VARCHAR2 (дължина 1), съответстващ на дадения код. Функцията е обратна на ASCII функцията. Той има вариант, който е удобен при работа с данни в национални набори от знаци:

Връща знак от тип NVARCHAR2 от националния набор от знаци.

• СЪСТАВЯНЕ (ред 1)
  Получава символен низ от Unicode и го връща в нормализирана форма. Например ненормализираното представяне "a\0303" дефинира знака "a" с водещ тилд (т.е. a). Извикването на COMPOSE("a\0303") връща "\00E3" - шестнадесетичният Unicode код за знака a.

В Oracle9i Release 1 функцията COMPOSE можеше да бъде извикана само от SQL команди; не може да се използва в PL/SQL програми. Започвайки с Oracle9i Release2, функцията COMPOSE може да се използва и в PL/SQL изрази.

• CONCAT(ред1, ред2)
  Добавя string2 към края на string1. Подобен резултат може да се постигне с помощта на израза line1 || ред2. Оператор || много по-удобно, така че функцията CONCAT се използва сравнително рядко.
• CONVERT(низ1, набор_знаци)
  Преобразува низ от набор от знаци на база данни в определен набор от знаци. Когато се обаждате, можете също да посочите първоначалния набор от знаци:

CONVERT(ред1, целеви_набор, изходен_набор)

• DECOMPOSE(ред1)
  Получава Unicode низ и връща низ с всичките му съставни знаци, разбити на елементи. Функцията е обратна на COMPOSE. Например извикването на DECOMPOSE("a") връща низа "a ~" (вижте описанието на COMPOSE).

Има два варианта на тази функция:

• DECOMPOSE(line1 CANONICAL)
  Извършва канонична декомпозиция; полученият резултат може да бъде възстановен чрез извикване на COMPOSE. Използва се по подразбиране.
• DECOMPOSE(ред1)
  Декомпозицията се извършва в така наречения режим на съвместимост. Възстановяването чрез извикване на COMPOSE може да не е възможно.

Функцията DECOMPOSE, подобно на COMPOSE, не може да бъде извикана директно в PL/SQL изрази в Oracle9i Release 1; трябва да се извика от SQL изрази. Започвайки с Oracle9i Release 2, това ограничение е премахнато.

• НАЙ-ГОЛЯМ(ред1, ред2, ...)
  Получава един или повече редове и връща реда, който би излязъл последен (т.е. най-големият), ако входните редове бяха сортирани във възходящ ред. Вижте също описанието на функцията LEST, обратна на GREATEST.
• INITCAP(ред1)
  Променя главния регистър на буквите на аргумент от низ, като прави първата буква от всяка дума в низа главна, а останалите букви малки. Думата е поредица от знаци, разделени от други знаци с интервал или небуквено-цифров знак (например # или _). Например извикването на INITCAP (" това е по-ниско") дава резултата "Това е по-ниско".
• INSTR(ред1, ред2)
  Връща позицията, от която string2 влиза в string1; ако не бъде намерен запис, функцията връща 0.

Има няколко разновидности на тази функция:

• INSTR(ред1, ред2, начална_позиция)
  Търсенето на string2 в string1 започва от позицията, определена от последния параметър. По подразбиране търсенето започва от позиция 1, така че извикването на INSTR(string1, string2, 1) е еквивалентно на извикването на INSTR(string1, string2).
• INSTR(ред1, ред2, отрицателна_начална_позиция)
  Отместването на началната позиция се задава не от началото, а от края на линията1.
• INSTR(ред1, ред2, начална_позиция, n)
  Находки n-то събитие lines2, започвайки от дадената начална позиция.
• INSTR(ред1, ред2, отрицателна_начална_позиция, n)
  Намира n-то срещане на string2, започвайки от дадена начална позиция от края на string1.

Функцията INSTR третира низ като последователност от знаци. Неговите варианти INSTRB, INSTR2 и INSTR4 третират низ съответно като последователност от байтове, кодови единици или Unicode кодови точки. Разновидност на INSTRC третира низ като последователност пълни знаци Unicode. Например низът "a\0303", който е разширеният еквивалент на "\00E3", или a, се третира като един знак. За разлика от това, функцията INSTR третира "a\0303" като поредица от два знака.

• НАЙ-МАЛКО(ред1, ред2, ...)
  Получава един или повече редове и връща реда, който ще се появи първи (т.е. най-малкия), ако входните редове са сортирани във възходящ ред. Вижте също описанието на функцията GREATEST, обратна на LEST.
• LENGTH(ред1)
  Връща броя знаци в низ. Вариантите LENGTHB, LENGTH2 и LENGTH4 връщат съответно броя Unicode байтове, кодови единици или кодови точки. Ароматът LENGTHC връща броя на пълните Unicode символи, нормализирани доколкото е възможно (т.е. преобразуване на "a\0303" в "\00E3").

Функцията LENGTH обикновено не връща нула. Спомнете си, че Oracle третира празния низ ("") като NULL, така че извикването на LENGTH ("") е ефективно еквивалентно на опит за получаване на дължината на NULL; неговият резултат също ще бъде NULL. Единственото изключение възниква при прилагане на LENGTH към тип CLOB. Типът CLOB може да съдържа 0 байта и пак да не е NULL. В този единствен случай LENGTH връща 0.

• LOWER(ред1)
  Преобразува всички букви даден низдо малки букви. Функцията е обратна на UPPER. Върнатият тип съответства на входния тип данни (CHAR, VARCHAR2, CLOB). Вижте също NLS_LOWER.
• LPAD(ред1, обща_дължина)
  Връща стойността на string1, подплатена отляво с интервали до final_length. Функцията има следните разновидности:
• LPAD(ред1, крайна_дължина, контейнер)
  Добавя достатъчно пълни или частични срещания на контейнер, за да накара общата дължина на низа да достигне указаната обща_дължина. Например извикването на LPAD("Весела Коледа!", 25, "Хо!") ще върне резултата "Хо! Хо! HВесела Коледа!".

• ?LTRIM(ред1)
  Премахва интервали от левия край на низ1. Вижте също описанията на функциите TRIM (ISO стандарт) и RTRIM. Функцията има следните разновидности:
• LTRIM(ред1, набор_изтриване)
  Премахва всички знаци, включени в низа delete_set от левия край на низ1.
• NCHR (код)
  Връща знак от тип NVARCHAR2 (дължина 1), съответстващ на дадения код. Функцията CHR с клаузата USING NCHAR_CS изпълнява същата функционалност като NCHR.
• NLS_INITCAP (ред 1)
  Връща версия на string1, която трябва да е от тип NVARCHAR2 или NCHAR, като първата буква на всяка дума е преобразувана в главни букви, а останалите букви в малки букви. Функцията връща стойност от тип VARCHAR2. „Думата“ е поредица от знаци, разделени от други знаци с интервал или небуквено-цифров знак.

Можете да зададете ред на сортиране, който засяга дефиницията на „първата буква“:

• NLS_INITCAP(ред1, "NLS_SORT=правило_за_сортиране")
  В тази форма на синтаксис sort_rule е едно от валидните имена на правила за сортиране, изброени в Ръководството за поддръжка на глобализацията на базата данни на Oracle, Приложение A, раздел „Лингвистични сортировки“.

Следният пример показва как функцията INITCAP се различава от NLS_INITCAP:

BEGIN DBMS_OUTPUT.PUT_LINE(INITCAP("ijzer")); DBMS_OUTPUT.PUT_LINE(NLS_INITCAP("ijzer","NLS_SORT=XDUTCH")); КРАЙ; Резултат: Ijzer IJzer

На холандски последователността от знаци „? " се третира като един символ. Функцията NLS_INITCAP разпознава този факт при посочване на правилото NLS_SORT и правилно преобразува знаците на думата „?zer“ („хардуер“ на холандски).

• NLS_LOWER(string1) и NLS_LOWER(string1, "NLS_SORT=sorting_rule") Връща низ1, преобразуван в малки букви според правилата на посочения език. Как NLS_SORT може да повлияе на резултата от преобразуването е описано в описанието на функцията NLS_INITCAP.
• NLS_UPPER(string1) и NLS_UPPER(string1, "NLS_SORT=sort_rule") Връща низ1, преобразуван в главни букви според правилата на посочения език. Как NLS_SORT може да повлияе на резултата от преобразуването е описано в описанието на функцията NLS_INITCAP.
• NLSSORT(string1) и NLSSORT(string1, "NLS_SORT=sorting_rule") Връща байтов низ, който може да се използва за сортиране на стойност на низ според правилата на посочения език. Низът се връща в RAW формат. Например сравняване на два низа според правилата Френскинаправено по следния начин: IF NLSSORT(x, "NLS_SORT=XFRENCH") > NLSSORT(y, "NLS_SORT=XFRENCH") THEN... Ако вторият параметър не е указан, функцията използва реда на сортиране по подразбиране, присвоен на сесията. Пълен списъкправилата са дадени в Ръководството за поддръжка на глобализацията на базата данни на Oracle, Приложение A, раздел „Езикови сортове“.
• REGEXP_COUNT, REGEXP_INSTR, REGEXP_LIKE, REGEXP_REPLACE, REGEXP_SUBSTR За описания на тези функции, предназначени да работят с регулярни изрази, можете да изучавате.
• REPLACE(string1, search_string, replace) Връща низа, резултат от замяната на всички срещания на search_string в ред1 със заместващия низ. Функцията REPLACE може да се използва за замяна на всички срещания на конкретен подниз в един оператор.
• REPLACE(низ1, низ за_търсене)
  Връща низа, резултат от премахването на всички срещания на search_string от string1.
• RPAD(ред1, обща_дължина)
  Връща стойността на string1, подплатена отдясно с интервали до final_length. Функцията има следните разновидности:
• RPAD(ред1, обща_дължина, контейнер)
  Добавя достатъчно пълни или частични срещания на контейнер, за да накара общата дължина на низа да достигне указаната обща_дължина. Извикването на RPAD("Весела Коледа!", 25, "Хо!") ще върне резултата "Весела Коледа! Хо! Хо!".

Функцията RPAD допълва низа отдясно, а нейната сдвоена функция LPAD допълва низа отляво.

• RTRIM(ред1)
  Премахва интервали от десния край на низ1. Вижте също описанията TRIM функции(ISO стандарт) и LTRIM. Функцията има следните разновидности:
• RTRIM(ред1, набор_изтриване)
  Премахва всички знаци, включени в низа delete_set от десния край на низ1.
• SOUNDEX(ред1)
  Връща низ с "фонетичното представяне" на аргумента.
  Пример:

Има няколко правила, които трябва да запомните, когато използвате функцията SOUNDEX:

• Стойността на SOUNDEX винаги започва с първата буква на входния низ.
• Върнатата стойност се генерира само от първите пет съгласни в низа.
• За изчисляване на цифровата част на SOUNDEX се използват само съгласни. Всички гласни в реда с изключение на началните се игнорират.
• Функцията SOUNDEX игнорира малки и големи букви; същите стойности на SOUNDEX се генерират за главни и малки букви.

Функцията SOUNDEX е полезна за заявки, при които точното изписване на стойността в базата данни е неизвестно или не може да бъде лесно определено.

Алгоритъмът SOUNDEX е фокусиран върху английски език; на други езици може да работи зле (или изобщо да не работи).

• SUBSTR(ред1, начална_позиция, дължина)
  Връща подниз от string1, който започва от начална_позиция и има зададена дължина. Ако броят знаци до края на string1 е по-малък от дължината, се връщат всички знаци от началната позиция до края на низа. Функцията има следните разновидности:
• SUBSTR(ред1, начална_позиция)
  Връща всички знаци от начална_позиция до края на низ1.
• SUBSTR(низ1, отрицателна_начална_позиция, дължина)
  Началната позиция на подниза се брои от края на низа1.
• SUBSTR(ред1, отрицателна_начална_позиция)
  Връща последните редове ABS(negative_start_position).

Функцията SUBSTR третира низ като поредица от знаци. Неговите варианти SUBSTRB, SUBSTR2 и SUBSTR4 третират низ съответно като поредица от байтове, кодови единици или Unicode кодови точки. Вкусът SUBSTRC третира низ като поредица от пълни Unicode знаци. Например низът "a\0303", който е разширеният еквивалент на "\00E3" или a, се третира като един знак. За разлика от това, функцията SUBSTR третира "a\0303" като поредица от два знака.

• TO_CHAR(данни_национален_знак)
  Преобразува данни от националния набор от знаци в еквивалентното им представяне в набора от знаци на базата данни. Вижте също TO_NCHAR.

Функцията TO_CHAR може също да се използва за преобразуване на дати/часове и числа в четима от човека форма.

• TO_MULTI_BYTE(ред1)
  Преобразува еднобайтови знаци в техните многобайтови еквиваленти. В някои многобайтови кодировки, най-вече UTF-8, може да има няколко опции за представяне на един знак. Да речем, в UTF-8, представянето на буквата "G" може да съдържа от 1 до 4 байта. За да преминете от еднобайтово представяне към многобайтово представяне, използвайте функцията TO_MULTI_BYTE. Тази функция е обратна на TO_SINGLE_BYTE.
• TO_NCHAR(знаци_в_набор_база_данни)
  Преобразува данни от набор от знаци на база данни в еквивалентното им представяне в националния набор от знаци. Вижте също TO_CHAR и TRANSLATE...USING.

Функцията TO_NCHAR може също да се използва за преобразуване на дата/час и числа в четима от човека форма.

• TO_SINGLE_BYTE(ред1)
  Преобразува многобайтови знаци в техните еднобайтови еквиваленти. Функцията е обратна на TO_MULTI_BYTE.
• TRANSLATE(line1, search_set, replacement_set)
  Заменя всяко появяване на знак в search_set в line1 със съответния знак в replacement_set. Пример:

Ако search_set съдържа повече знациотколкото replacement_set, "допълнителни" знаци, които нямат съвпадение в replacement_set, не са включени в резултата. Пример:

ПРЕВОД ("abcdefg", "abcd", "zyx") --> "zyxefg"

Буквата "d" се премахва, защото присъства в search_set, но няма еквивалент в replacement_set. Функцията TRANSLATE замества отделни знаци, докато функцията REPLACE замества цели низове.

• TRANSLATE(текст, ИЗПОЛЗВАЩ CHAR_CS) и TRANSLATE(текст, използващ NCHAR_CS)
  Преобразува символни данни в набор от знаци на база данни (CHAR_CS) или национален набор от знаци (NCHAR_CS). Типът изходни данни ще бъде VARCHAR2 или NVARCHAR2 в зависимост от това дали преобразуването е съответно към набора от символи на базата данни или националния набор от знаци.

Функцията TRANSLATE...USING е една от стандартните SQL функции на ISO. От Oracle9i Release 1 можете просто да присвоите стойността на VARCHAR2 променлив тип NVARCHAR2 , и обратно - системата имплицитно ще извърши необходимото преобразуване. Ако искате да направите преобразуването изрично, използвайте функциите TO_CHAR и TO_NCHAR, за да преобразувате текст съответно в набора от знаци на базата данни и националния набор от знаци. Oracle препоръчва използването определени функциивместо TRANSLATE...USING, защото поддържат по-широк набор от типове входни данни.

• TRIM(ОТ ред1)
  Връща низа, резултат от премахването на всички начални и завършващи интервали от string1. Функцията има следните разновидности:
• ТРИМ (ВОДЕЩ ОТ ...)
  Премахване само на водещи интервали.
• КРАЙ (СЛЕДВАЩ ОТ ...)
  Премахва само интервали в края.
• ПОДКРИТ (ДВАТА ОТ ...)
  Премахва началните и крайните интервали (по подразбиране).
• TRIM (...знак за премахване ОТ ред1)
  Премахване на срещания на един deleted_character по преценка на програмиста.

Функцията TRIM беше включена в Oracle8i, за да осигури по-голяма съвместимост със стандарта ISO SQL. Той съчетава функционалността на LTRIM и RTRIM, но се различава от тях по това, че TRIM ви позволява да посочите само един знак за премахване, докато с LTRIM и RTRIM можете да посочите набор от знаци за премахване.

• UNISTRA(ред 1)
  Връща string1, преобразуван в Unicode; следователно функцията е обратна на ASCIISR. За да представите непечатаеми знаци във входния низ, можете да използвате нотацията \XXXX, където XXXX е Unicode кодовата точка на знака. Пример:

Функцията осигурява удобен достъп до целия набор от Unicode символи, включително тези, които не могат да се въвеждат директно от клавиатурата.

• ГОРЕН(ред1)
  Преобразува всички букви от посочения низ в главни букви. Върнатият тип съответства на входния тип данни (CHAR, VARCHAR2, CLOB). Функцията е обратна на LOWER. Вижте също NLS_UPPER.

В тази част ще говорим за функции за работа с текстова информация, която може да се използва в заявки и програмен код на езика PL/SQL.

Функция CONCAT(strl, str2)

Тази функция свързва низовете strl и str2. Ако един от аргументите е NULL, той се третира като празен низ. Ако и двата аргумента са NULL, тогава функцията връща NULL. Пример:

SELECT CONCAT("Свещеникът имаше куче") x1,
CONCATCTest" , NULL) x2,
CONCAT(NULL, "Тест") x3,
CONCAT(NULL, NULL) x4
ОТ двойно

За свързване на низове Oracle поддържа специален оператор за свързване "||", който работи подобно на функцията CONCAT, например:

SELECT CONCAT("Свещеникът "има куче") x1, "Свещеникът " || "има куче" x2
ОТ двойно

Операторът за конкатенация "||", който е еквивалентен на извикването на функцията CONCAT, не трябва да се бърка с оператора "+", използван в аритметичните операции. В Oracle това е различни оператори, но поради автоматичното преобразуване на типове са възможни фини грешки, например:

ИЗБЕРЕТЕ "5" + "3" x1
ОТ двойно

В този случай се връща числовата стойност 8, а не текстов низ"53". Това е така, защото когато Oracle открие аритметичния оператор „+“, Oracle автоматично се опитва да прехвърли аргументите към тип NUMBER.

Функция LOWER(str)

Функцията LOWER преобразува всички знаци в str в малки букви. Пример:

SELECT LOWER("TEXt DATA") X
ОТ двойно

функцияГОРЕН (низ)

Функцията UPPER преобразува всички знаци в низа str в главни букви. Пример:

SELECT UPPER("TEXt DATA") X
ОТ двойно

Функция INITCAP(str).

Връща низа str с първите букви на всички думи, преобразувани в главни букви. Функцията е удобна за форматиране на пълното име при изграждане на справки. Пример:

ИЗБЕРЕТЕ ИНИЦАПЦИВАНОВ Петър Сидорович") X
ОТ двойно

ФункцииLTRIM(str [,set])ИRTRIM(str [,set])

Функцията LTRIM премахва всички знаци от началото на низа до първия знак, който не е в зададения набор от знаци. По подразбиране наборът се състои от един интервал и може да не бъде посочен. Функцията RTRIM е подобна на LTRIM, но премахва символи, започващи от края на низа. Нека да разгледаме няколко примера:

SELECT LTRIM(" TeXt DATA") X1,
LTRIM(" _ # TeXt DATA", " #_") X2,
LTRIM(" 1234567890 TeXt DATA", " 1234567890") X3
ОТ двойно

Функция REPLACE(str, search_str, [,replace_str])

Функцията REPLACE търси модел search_str в низа str и заменя всяко намерено срещане със replace_str. По подразбиране replace_str е празен низ, така че извикването на REPLACE с два аргумента премахва всички намерени срещания. Търсенето на подниз е чувствително към главни и малки букви. Пример:

SELECT REPLACE("Свещеникът имаше куче", "куче", "котка") x1,
REPLACE("Свещеникът имаше зло куче", "зло") x2,
REPLACE("Свещеникът имаше куче", "Куче", "Котка") x3
ОТ двойно

Функция TRANSLATE(str, from_mask, to_mask)

Функцията TRANSLATE анализира низа str и заменя всички знаци, появяващи се в низа from_mask, със съответните знаци от to_mask. За да работи функцията правилно, низовете from_mask и to_mask трябва да са с еднаква дължина или низът from_mask трябва да е по-дълъг от to_mask. Ако from_mask е по-дълъг от to_mask и по време на обработката на низа str се открият знаци, които съвпадат с един от символите from_mask, и няма съвпадение за тях в to_mask, тогава такива знаци ще бъдат премахнати от низа str. Ако подадете from_mask или to_mask равно на NULL, функцията ще върне NULL. Сравнението се прави с малки и големи букви.

SELECT TRANSLATE("Тест 12345", "e2\"E!") x1,
TRANSLATE("Тест 12345", "e234", "E") x2
ОТ двойно

Тази функция е удобна за решаване на редица практически проблеми, свързани с преобразуване на знаци или търсене на забранени знаци. Например, трябва да анализирате парола и да разберете дали съдържа поне една цифра. Изпълнението на тази проверка с помощта на TRANSLATE изглежда така:

IF TRANSLATE(PassWd, "0123456789", "*") = PassWd THEN
ADD_ERR0R("Грешка - Паролата трябва да съдържа поне една цифра!");
ВРЪЩАНЕ 1;
ENDIF;

Друг пример: подготвя се число за преобразуването му в NUMBER. Необходимо е да се сменят десетичните разделители "," и "." На "." и премахнете интервалите. Изпълнението на тази операция с помощта на TRANSLATE изглежда така:

SELECT TRANSLATE("123 455.23", "., ", " . . . ") x1,
TRANSLATE("-123 455.23", "., ", " . . ") x2
ОТ двойно

Функция SUBSTR(str, m [,n])

Функцията SUBSTR връща фрагмент от низа str, започващ от символ m, с дължина n знака. Дължината може да бъде пропусната - в този случай низът се връща от знака m до края на низа str. Символите се номерират, започвайки от 1. Ако посочите m = 0, тогава копирането пак ще започне от първия знак. Задаването на отрицателна стойност за m кара символите да се броят от края на низа, а не от началото. Задаването на стойности на m, които са по-големи като абсолютна стойност от дължината на низа, кара функцията да връща NULL.

SELECT SUBSTR("Свещеникът имаше куче", 13) x1,
SUBSTR("Свещеникът имаше куче", -6) x2,
SUBSTR("Това е тестов текст", 5, 8) x3,
SUBSTR("Свещеникът имаше куче", 150) x4
ОТ двойно

Функция INSTR(str, search_str [,n[,m]])

Функцията INSTR връща позицията на първия знак m-ro от фрагмента от низ str, който съответства на search_str. Сравнението се извършва от n-тия символ на низа str; сравнението е чувствително към малки и главни букви. По подразбиране n = m = 1, т.е. търсенето се извършва от началото на реда и се връща позицията на първия намерен фрагмент. Ако търсенето е неуспешно, функцията връща 0.

SELECT INSTR("y butt was a dog", "dog") x1,
INSTR("y butt was a dog", "cat") x2,
INSTR("Това е текст за демонстрация на търсене на текст", "текст", 1, 2) x3,
INSTR('11111000000001", "1", 7) x4
ОТ двойно

Тази функция, както и всички останали в Oracle, често е разрешена типични грешкисвързани с обработката на NULL стойности. Ако str=NULL, тогава функцията ще върне NULL, а не нула! Това трябва да се вземе предвид при изграждането на различни условия. Например, този фрагмент от PL/SQL програма не взема предвид тази функция:

IF INSTR(TXT_VAR,"*") = 0 ТОГАВА
...
КРАЙАКО;

В този случай би било правилно да напишете така:

IF NVL(INSTR(TXT_VAR, "*"), 0) = 0 THEN
...
ENDIF;

Функции LENGTH (str) и LENGTHB (str).

Функцията LENGTH(str) връща дължината на низа str в знаци. За празен низ и стойност NULL функцията връща NULL, така че се препоръчва използването на NVL заедно с тази функция.

SELECT LENGTH("Свещеникът имаше куче") x1,
LENGTH("") x2,
LENGTH(NULL) x3,
NVL(ДЪЛЖИНА(""), 0) x4
ОТ двойно

Функцията LENGTHB е подобна на функцията LENGTH, но връща дължината на низа в байтове.

Функция ASCII(str).

Връща ASCII стойността на първия знак от низа str, когато се използва ASCII кодиране на знаци, и стойността на първия байт на многобайтов знак, когато се използва многобайтово кодиране на знаци. Пример:

SELECT ASCII("Тест") x1 FROM dual

Функция CHR(n)

Връща знак по неговия код.

ИЗБЕРЕТЕ CHR(64) x1
ОТ двойно

Въведение

оракул регистър низ аритметика

Основни идеи на модерното информационни технологиисе основават на концепцията, че данните трябва да бъдат организирани в бази данни, за да отразяват адекватно променящия се реален свят и да отговарят на информационните нужди на потребителите.

Уместност:Компютърът не е нищо повече от устройство за преобразуване на информация. Ако няма много информация, тогава самият алгоритъм за преобразуване отнема по-голямата част от времето за обработка. Ако трябва да работите с големи обемиданни - ефективността на обработката започва да зависи пряко от ефективността на събиране на данни, филтриране и т.н. За да се опрости процеса на разработка и да се увеличи производителността на работата подобни системи, са създадени различни СУБД. Те имат свой собствен формат за съхранение на данни, свои собствени алгоритми за търсенето и извличането им, но основен езикПовечето от тях са с едно искане. И този език е SQL.

Цели:

1. Получаване на теоретични и практически знаниячрез Oracle SQL функции;

2. Приложение на теорията в практиката.

Задачи:Този курс е посветен на изучаването на функциите на Oracle SQL, както и на основните техники за работа с тях. В тази работа ще изучаваме функции, които работят с прости едноредови входни параметри и връщат резултат за всеки ред.

Oracle SQL функции

Функцията е подобна на оператор по това, че манипулира елементи от данни и връща резултат. Функциите се различават от операторите по формата, в който са посочени с техните аргументи. Този формат позволява на функциите да работят с нула, едно, две или Повече ▼аргументи:

функция (аргумент, аргумент,...)

Функциите могат да се използват за извършване на изчисления върху данни, конвертиране на типове данни, промяна на изходните формати за дата и т.н. SQL функциите се предлагат в два основни типа:

1. едноредови (или скаларни) функции;

2. групови (или агрегатни) функции.

Тези функции се различават по броя на линиите, на които работят. Функцията за един ред връща една стойност за всеки ред от таблица, докато груповата функция връща една стойност за цяла група редове.

Едноредови функции . Едноредовите функции могат да се появят в клаузите SELECT, WHERE и ORDER BY на оператор SELECT. Като аргументи те могат да приемат зададени от потребителя константи, стойности на променливи, имена на колони в таблици на бази данни или изрази, съставени с помощта на оператори и функции.

Списък с функции

Всички едноредови функции обикновено се разделят на няколко групи според типовете данни на техните аргументи и връщаните стойности. Акцент:

· числови функции;

· символни функции;

· функции за работа с дати;

· функции на трансформация.

Характерни функции

Функциите за символи работят върху низови стойности. За да се опрости работата с низове, има редица вградени функции, които значително улесняват различните операции.

Функции за преобразуване на регистър

Функцията за дължина на низа LENGTH(низ) връща броя знаци в низа, включително интервалите в края.

ИЗБЕРЕТЕДЪЛЖИНА( низ) ОТ ДВОЙНОще върне стойността 7.

Функции за преобразуване на главни и малки букви UPPER(низ), LOWER(низ), INITCAP(низ). За да конвертирате знаци в главни букви, използвайте функцията UPPER().

ИЗБЕРЕТЕГОРЕН( низ) ОТ ДВОЙНОЩе се върне STRING.

Ако трябва да преобразувате символи в низ в малки букви, използвайте функцията LOWER().

ИЗБЕРЕТЕНИСЪК( низ) ОТ ДВОЙНОЩе се върне STRING.

Функцията INITCAP преобразува всеки първи знак от дума в главна буква и всички останали знаци в малка буква, при условие че знакът за разделител на думата е интервал.

ИЗБЕРЕТЕ INITCAP(низ1 низ2) ОТ ДУАЛще върне низа STRING1 STRING2.

Функции за изрязване на начални и завършващи интервали LTRIM(низ), RTRIM(низ), TRIM(низ). Съответно първата функция изрязва всички начални интервали на реда, втората - всички завършващи интервали, а третата - всички начални и завършващи интервали.

ИЗБЕРЕТЕ LTRIM(`str1") FROM DUAL ще върне низа str1,

ИЗБЕРЕТЕ RTRIM(`str2") FROM DUAL ще върне низа str2,

ИЗБЕРЕТЕ TRIM(`str3") FROM DUAL ще върне низа str3.

Функцията за заместване на част от низ с друг низ REPLACE (оригинален низ, заменен подниз, заместващ подниз). За по-голяма яснота разгледайте пример:

Числото се съхранява в определено текстово поле на таблицата, като разделителят между целите и дробните части в някои полета е “.”, а за по-нататъшна обработка на данните ни е необходимо той да бъде “,” във всички полета.

За да направим това, ще използваме функцията REPLACE, както следва: REPLACE (поле1,".", ","") и всички знаци "." в полето полето ще бъде заменено със символа „,“.

ИЗБЕРЕТЕ REPLACE (`My_string", "_", "@") FROM DUAL ще върне низа My@string.

Функции за преобразуване на данни в други типове данни: TO_CHAR(число) преобразува число в текст, TO_NUMBER(низ) преобразува текст в число, TO_DATE (низ, формат на дата) преобразува низ в дата с определен формат.

ИЗБЕРЕТЕ TO_CHAR(123) FROM DUAL ще върне ред 123,

ИЗБЕРЕТЕ TO_NUMBER(`12345") FROM DUAL ще върне числото 12345,

Функция за определяне на появата на подниз в низ INSTR (изходен низ, подниз, номер на символ). Тази функция ви позволява да определите номера на знака в изходния низ, от който започва търсеният подниз (ако има такъв). В противен случай се връща 0. Например трябва да определим всички позиции в таблицата Table1, в името на която фигурира поднизът “manager”. Следният оператор е доста подходящ за това:

ИЗБЕРЕТЕ*ОТМАСА 1 КЪДЕТО INSTR (POST, `мениджър', 1) > 0.

Тоест изразът SELECT ще покаже само онези записи от таблица TABLE1, където ще бъде намерен търсеният подниз „мениджър“. Освен това търсенето ще се извърши от първия знак. Ако търсенето трябва да се извърши от друга позиция, тогава номерът на знака, за да започне търсенето, се посочва в третия параметър.

ИЗБЕРЕТЕ INSTR (`Малък низ", `низ", 1) FROM DUAL ще върне стойността 7,

ИЗБЕРЕТЕ INSTR (`Малък низ", `Низ", 1) FROM DUAL ще върне стойността 0.

Функция за избор на подниз в изходния низ SUBSTR (изходен низ, номер на начален символ, брой знаци). Нека разгледаме този пример: потребителската таблица съхранява адреса под формата на името на населеното място, името на улицата и номера на къщата. Освен това със сигурност знаем, че за името на населено място са предвидени строго 20 знака (ако името на населеното място има по-малко от 20 знака, тогава останалото се запълва с интервали), за името на улица 30 знака, за знака номер на къща 3. След това трябва да прехвърлим всички адреси от нашата таблица в друга, като всичките 3 компонента на адреса трябва да са в различни полета. За да изберете адресни компоненти, използвайте функцията SUBSTR().

ИЗБЕРЕТЕ SUBSTR (TABLE_1.ADDRESS, 1,20) ГРАД, SUBSTR (TABLE_1.ADDRESS, 21,30) STREET, SUBSTR (TABLE_1.ADDRESS, 52, 3) ГРАД ОТМАСА 1;

Разбира се, за да прехвърлите данни, трябва да използвате израза INSERT, но за да разберете работата на функцията SUBSTR, обсъжданият пример е доста подходящ.

ИЗБЕРЕТЕ SUBSTR (`My_string", 4, 3) FROM DUAL ще върне низа str.

Обсъдените по-горе функции могат да се използват във входни параметри. Така че, ако трябва да изберем всички знаци след конкретен, тогава можем да предадем номера на търсения знак от функцията INSTR към функцията SUBSTR. Например, ако трябва да прехвърлите всички знаци от поле на таблица, които се намират след „,“, тогава можете да използвате следната конструкция:

ИЗБЕРЕТЕ SUBSTR (My_string, INSTR (My_string, `, ",1), LENGTH (My_string) - INSTR (My_string, `, ", 1)+1) ОТ ДВОЙНО.

За да определим началния знак, извикваме функцията INSTR(), която ще върне номера на символа на първото появяване на поднизовете ",". След това дефинираме броя знаци до края на низа като разликата между дължината на низа и номера на първото срещане на подниза.

За определяне на кода на символа се използва функцията ASCII (string), която връща кода на 1 знак от низа. Например:

ИЗБЕРЕТЕ ASCII(W) FROM DUAL ще върне стойността 87.

Обратна функция за преобразуване на код на символ в символ CHR (число).

ИЗБЕРЕТЕ CHR(87) FROM DUAL ще върне знака W.

Функции за манипулиране на символен низ

Oracle предлага обширен набор от функции за манипулиране на низови данни:

CHR(N) - Връща ASCII кодовия знак за десетичния код N;

ASCII(S) - Връща десетичния ASCII код на първия знак от низа;

INSTR (S2. S1.pos[, N] - Връща позицията на низ S1 в низ S2 по-голяма или равна на поз. N - брой срещания;

LENGHT(S) – Връща дължината на низа;

LOWER(S) - Заменя всички символи в низ с главни букви;

INITCAP(S) - Задава главни букви на първия знак от всяка дума в реда, а останалите символи на всяка дума - в главни букви;

SUBSTR (S, pos, [, len]) - Избира подниз с дължина len в низа S, започвайки от позиция pos;

ГОРЕН (S) - Преобразува главни буквина ред с главни букви;

LPAD(S, N[, A]) – Връща низа S, подплатен отляво със знака A до броя на знака N. Знакът за допълване по подразбиране е интервал;

RPAD(S, N[, A]) – Връща низа S, подплатен отдясно с A до брой знака N. Знакът за допълване по подразбиране е интервал;

LTRIM (S, ) – Връща съкратения отляво низ S. Символите се премахват, докато символът, който трябва да се премахне, е включен в низа – модел S1 (по подразбиране – интервал);

RTRIM (S, ) – Връща съкратения отдясно низ S. Символите се премахват, докато символът, който трябва да се премахне, е включен в низа – шаблон S1 (по подразбиране – интервал);

TRANSLATE (S, S1, S2) – Връща низа S с всички срещания на низ S1, заменени от низ S2. Ако S1<>S2, тогава символите, които не съвпадат, се изключват от резултантния низ;

REPLACE (S, S1, [, S2]) – Връща низа S, за който всички срещания на низа S1 са заменени с подниза S2. Ако S2 не е посочен, тогава всички срещания на подниза S1 се премахват от резултантния низ;

NVL (X, Y) – Ако X е NULL, тогава връща или низ, число или дата в Y, в зависимост от оригиналния тип на Y;

SOUNDEX(S) – Връща фонетичното представяне на низ;

Лабораторна работа. Работа с низови функции

Функции за низове в Oracle SQL заявки, функции UPPER(), CONCAT(), SUBSTR().

Упражнение:

Трябваше да създадете идентификатор за всеки служител, който трябва да изглежда като първите 3 знака от собственото име плюс първите два знака от фамилията. Всички знаци в този идентификатор трябва да са в главни букви.

Напишете заявка, която да върне информация за името и фамилията на служителя, както и идентификатора на служителя от таблицата hr.employees в съответствие с посочените условия. Резултатът от заявката трябва да бъде както е показано на фиг. 3.1-1.

Решение:

Съответният код на заявката може да бъде така:

ИЗБЕРЕТЕ first_name AS "Собствено име", last_name Като "Family Name", UPPER (CONCAT(SUBSTR (first_name, 1, 3), SUBSTR (last_name, 1,2))) AS "Identifier" ОТ hr.служители.

Функциите, които ще обсъдим в тази част, обикновено използват вграден PL/SQL код, пакетиран и доставен от Oracle. Някои обработват числови стойности, знаци и дати, други преобразуват данни в Различни видоведанни. Функциите могат да използват вложени извиквания, а някои функции са проектирани да работят с NULL стойности. Условните функции CASE и DECODE ви позволяват да показвате различен резултат в зависимост от стойностите на данните, което осигурява възможност за разклоняване в контекста на SQL заявка

Функциите са разделени на две големи групи: тези, които изчисляват стойности за всеки ред, и тези, които извършват едно изчисление за всички редове. Ще разгледаме функции за преобразуване, функции за работа със символни данни, числови данни и данни за дата.

Определение на функцията

Функцията е програма, която може (но по избор) да приема входни параметри, да извършва някои операции и да връща литерална стойност. Функцията връща само една стойност на повикване.

Три важни компонента формират основата за дефиниране на функция. Първият е списък с входни параметри. Той дефинира нула или повече параметри, които могат да бъдат предадени на функцията за обработка. Тези параметри или аргументи може да са незадължителни (имат стойност по подразбиране) и могат да бъдат различни видоведанни. Вторият компонент е типът данни на върнатия резултат. След изпълнение функцията връща само една стойност от предварително дефинирания тип данни. Третият компонент капсулира детайлите на обработката на изпълняваната функция и съдържа програмния код, който оперира с входните параметри, извършва изчисления и връща стойност.

Функцията често се описва като черна кутия, която приема входни данни, прави нещо и връща резултат. Вместо да се фокусираме върху подробностите за изпълнението на функциите, по-полезно е да разберем каква функционалност предоставят вградените функции.

Извикванията на функции могат да бъдат вложени, например като F1(x, y, F2(a, b), z), където функцията F2 приема два входни параметъра и връща третия от четирите параметъра на функция F1. Функциите могат да работят с всякакъв тип данни: най-често използваните са знаци, числа и данни за дата. Тези функционални параметри могат да бъдат колони или изрази.

Като пример, разгледайте функция, която изчислява възрастта на човек. Функцията AGE приема само един параметър, рожден ден. Резултатът, върнат от функцията AGE, е число, представляващо възрастта на лицето. Изчисленията на черната кутия включват получаване на разликата в годините между текущата дата и рождения ден, подаден като входен параметър.

Видове функции

Функциите могат да бъдат разделени в две категории: такива, които обработват низ (функции за низ) и такива, които обработват набор от низове (функции за групиране). Това подчертаване е много важно за разбирането на контекста, в който се използват различни функции.

Стрингови функции

Налични са няколко вида функции за низове, включително функции за низове, функции за числа, функции за дата, функции за преобразуване на типове и общи функции. Тези функции обработват един ред от набор наведнъж. Ако една заявка избере десет реда, функцията ще бъде изпълнена десет пъти, веднъж за всеки ред, като е възможно да се използват стойностите на колоната на реда като входни параметри за функцията.

Следната заявка избира две колони от таблицата REGIONS и израз с помощта на функцията LENGTH и колоната REGION_NAME

изберете region_id, region_name, length(region_name) от региони;

Дължината на стойността на колоната REGION_NAME се изчислява за всеки от четирите реда в таблицата REGIONS; Функцията се изпълнява четири пъти, като всеки път връща литерална стойност.

Функциите за низ работят върху дадени елементи от низ, за ​​да ги извлекат и форматират преди показване. Входните стойности на низова функция могат да бъдат дефиниран от потребителяконстанта или литерал, данни от колони, променливи или изрази, евентуално използване на други вложени низови функции и т.н. Вложените повиквания са често използвана техника. Функциите могат да върнат стойност от тип данни, различен от типа данни на входните параметри. Предишната заявка показва как функцията LENGTH приема низ като вход и връща число.

В допълнение към използването на функции в клаузата SELECT, низовите функции могат да се използват в клаузите WHERE и ORDER BY.

Функции, които работят с набор от данни

Както подсказва името, тези функции работят с повече от един низ. Типична употреба на функцията за много редове е да се изчисли сумата или средната стойност на някаква цифрова колона или да се преброи броят на редовете в резултата. Тези функции понякога се наричат ​​групиращи функции и ще ги разгледаме в следващата глава.

Използване на функции за промяна на регистъра

Данните в таблиците могат да се попълват от различни източници: програми, криптовалути и др. Не трябва да разчитате на въведени данни за символи в предварително зададени главни букви. Низовите функции за смяна на регистър служат на две важни цели. Те могат да се използват, на първо място, за промяна на регистъра на данните при записване или показване на информация или в условия WHERE за по-гъвкаво търсене. Много по-лесно е да търсите низ с помощта на фиксирани главни букви, вместо да проверявате всички комбинации от главни и малки букви. Не забравяйте, че извикването на функции не променя данните, съхранени в таблицата. Те трансформират резултатите от заявката.

Входните параметри могат да бъдат символни литерали, колони от символен тип данни, символни изрази или числа и дати (които ще бъдат имплицитно преобразувани в низове).

LOWER функция

Функцията LOWER заменя всички главни букви с еквивалентни малки букви. Синтаксис на функцията LOWER(низ). Нека да разгледаме пример за заявка, използваща тези функции

изберете по-нисък (100+100), по-нисък ('SQL'), по-нисък (sysdate) от двойно

Да приемем, че текущата дата е 17 декември 2015 г. Резултатът от заявката ще бъде низовете „200“, „sql“ и „17-dec-2015“. Числовият израз и датата се преобразуват неявно в низ, преди да се извика функцията LOWER.

Следващият пример използва функцията LOWER за намиране на низове, където буквите „U“ и „R“ във всички случаи са последователни

изберете first_name, last_name, lower(last_name) от служители

където по-ниско (фамилно_име) като „%ur%“;

Можете да напишете подобна заявка, без да използвате функцията LOWER. Например така

изберете first_name, last_name от служителите

където last_name като „%ur%“ или last_name като „%UR%“

или фамилно име като „%uR%“ или фамилно име като „%Ur%“

Тази заявка работи, но е твърде тромава и броят на операторите ИЛИ се увеличава експоненциално с увеличаването на реда.

UPPER функция

Функцията UPPER е логическа противоположност на функцията LOWER и замества всички знаци с малки букви с техните еквиваленти с главни букви. Синтаксисът на функцията е UPPER(низ). Нека разгледаме един пример

изберете * от държави, където горно (име на държава) като „%U%S%A%“;

Тази заявка избира редове от таблицата COUNTRIES, където COUNTRY_NAME съдържа буквите „U“, „S“, „A“ във всеки случай в този ред.

Функция INITCAP

Функцията INITCAP често се използва за показване на данни. Първите знаци на всяка дума в ред се преобразуват в главни букви, всички останали знаци се преобразуват в малки еквиваленти. Думата е набор от знаци, който не съдържа интервали или специални знаци. Интервал, долна черта и специални знаци като знака за процент, удивителен знак, знаците за долар се третират като разделители. Функцията INITCAP приема един параметър и синтаксиса INITCAP(низ). Следващият пример показва пример за използване на функцията INITCAP

изберете initcap(‘init cap или init_cap или init%cap’) от dual

Резултатът от тази заявка ще бъде низът Init Cap или Init_Cap или Init%Cap

Използване на функции за низове

Функциите за работа с низове са едни от най-много мощни функциипредоставена от Oracle. Те са много полезни и разбираеми, почти без подробни обяснения и много често се използват от различни програмисти при обработка на данни. Често се използват вложени извиквания към тези функции. Операторът за конкатенация може да се използва вместо функцията CONCAT. Функциите LENGTH, INSTR, SUBSTR и REPLACE могат да се допълват взаимно, както и RPAD, LPAD и TRIM.

Функция CONCAT

Функцията CONCAT свързва два литерала, колони или изрази, за да образува един голям израз. Функцията CONCAT има два входни параметъра. Синтаксисът на функцията е CONCAT(низ1, низ2), където низ1 и низ2 могат да бъдат литерал, колона или израз, чийто резултат е символен литерал. Следващият пример показва използването на функцията CONCAT

изберете concat('Днес е:',SYSDATE) от dual

Вторият параметър на функцията е функцията SYSDATE, която връща текущия Системно време. Стойността се преобразува в низ и първият параметър се добавя към нея. Ако текущата системна дата е 17 декември 2015 г., тогава заявката ще върне низа „Днес е: 17-DEC-2015“.

Нека да разгледаме как да използваме функция за комбиниране на три елемента. Тъй като функцията CONCAT може да приеме само два входни параметъра, само два елемента могат да бъдат свързани. В този случай можете да използвате извикване на функция като параметър за извикване на друга функция. Тогава заявката ще изглежда така

изберете concat('Outer1', concat('Inner1',' Inner2')) от dual;

Първата функция има два параметъра: първият параметър е литерала „Outer1“, а вторият параметър е вложената функция CONCAT. Втората функция приема два параметъра: литерала „Inner1“ и литерала „Inner2“. Резултатът от тази заявка ще бъде низът „Външен1 Вътрешен1 Вътрешен 2“. Ще разгледаме вложените функции малко по-късно.

Функция LENGTH

Функцията LENGTH връща броя знаци, съставляващи низ. Интервалите, разделите и специалните знаци се броят от функцията LENGTH. Функцията има един параметър и синтаксисът е LENGTH(низ). Нека разгледаме молбата

изберете * от държави, където дължина (име_на_държава) > 10;

Функцията LENGTH се използва за избор на онези държави, чието име е с дължина над десет знака.

RPAD функциии LPAD

Функциите RPAD и LPAD връщат низ с фиксирана дължина и по избор допълват оригиналната стойност със специфичен набор от знаци отляво или отдясно. Знаците, използвани за добавяне, могат да бъдат литерал, стойност на колона, израз, интервал (по подразбиране), табулация и специални знаци. Функциите LPAD и RPAD приемат три входни параметъра и синтаксиса LPAD( с, н, стр) и RPAD( с, н, стр) Където с– стойност на низа, който ще се обработва, н– брой знаци за резултат и стр– символи за добавяне. Ако се използва LPAD, тогава символите стрсе добавят отляво, докато се достигне дължина n. Ако RPAD - тогава отдясно. Моля, имайте предвид, че ако дължината сповече от дължината н– тогава резултатът ще е първи нзнаци значение с. Разгледайте заявките на Фигура 10-1

Фигура 10-1 – Използване на функциите RPAD и LPAD

Първата заявка не променя данните и резултатът не е много четим в сравнение с резултата от втората заявка. RPAD се използва за добавяне на интервали, където е необходимо за first_name и last_name, така че всички стойности да са с фиксирана дължина от 18 знака, а LPAD се използва за добавяне на интервали в началото на стойността на заплатата, докато достигне дължина от 6 знака.

Функция TRIM

Функцията TRIM премахва знаци и началото или края на низ, за ​​да го направи потенциално по-къс. Функцията приема задължителен параметър и незадължителен. Синтаксис на функция TRIM([ изоставащ|водещ|и двете] шнур за подстригванеот низ). Параметърът за входен низ (s) е задължителен. Следните елементи изброяват опциите

• TRIM(s) премахва интервалите от началото до края на реда
• TRIM(завършващ низ за изрязване от s) премахва символите от низа за изрязване от края на низа
• TRIM(водещ тримниз от s) премахва тримгниз знаци от началото на низа
• TRIM(и двата низа от s) ИЛИ TRIM(низ от s) премахва всички знаци от низа в началото и края на низа

изберете изрязване (и двете ‘*’ от ‘****Hidden****’),

изрязване (водещо „*“ от „****Скрито****“),

изрязване (след „*“ от „****Скрит****“) от двойно;

Връща „Hidden“, „Hidden****“ и „****Hidden“. Моля, имайте предвид, че като посочите само един знак, всички знаци се премахват, ако се повтарят последователно.

Функция INSTR

Функцията INSTR търси подниз в низ. Връща се число, указващо позицията, където започва n-тото срещане, като се започне от позицията за търсене, спрямо началото на низа. Ако поднизът не е намерен в низа, се връща 0.

Функцията INSTR има два задължителни параметъра и два незадължителни параметъра. Синтаксис на функцията INSTR(източен низ, низ за търсене, , ). Стойността по подразбиране за начална позиция на търсене=1 или с други думи началото на изходния низ. Стойността по подразбиране за n срещане=1 или първото срещане. Нека да разгледаме няколко примера

Заявка 1: изберете instr(‘1#3#5#7#9#’, ‘#’) от dual;

Заявка 2: изберете instr(‘1#3#5#7#9#’, ‘#’ ,5) от dual;

Заявка 3: изберете instr(‘1#3#5#7#9#’, ‘#’, 3, 4) от dual;

Първата заявка търси първото появяване на хеш маркера в низа и връща стойността 2. Втората заявка търси хеш маркера в низа, започвайки от петия знак, и намира първото срещане от 6-ия знак. Третата заявка търси четвъртото срещане на хеш маркера, започващ от третия знак, и го намира на позиция 10.

Функция SUBSTR

Функцията SUBSTR връща подниз с определена дължина от изходен низ, започващ от определена позиция. Ако началната позиция е по-голяма от дължината на оригиналния низ, се връща NULL. Ако дължината на изходния низ не е достатъчна, за да се получи стойността на необходимата дължина, като се започне от определена позиция, тогава се връща частта от низа от изходния знак до края на реда.

Функцията SUBSTR има три параметъра, първите два са задължителни и синтаксисът е SUBSTR(източен низ, начална позиция, ). Стойността по подразбиране за знаци за извличане = разликата между дължината на изходния низ и началната позиция. Разгледайте следните примери

Заявка 1: изберете substr(‘1#3#5#7#9#’, 5) от dual;

Заявка 2: изберете substr(‘1#3#5#7#9#’, 5, 3) от dual;

Заявка 3: изберете substr(‘1#3#5#7#9#’, -3, 2) от dual;

Заявка 1 връща подниз, започващ от позиция 5. Тъй като третият параметър не е посочен, броят на знаците е равен на дължината на оригиналния низ минус началната позиция и ще бъде равен на шест. Първата заявка ще върне подниза '5#7#9#'. Втората заявка връща три знака, като се започне от петия знак и низът на резултата е „5#7“. Заявка три започва от позиция минус три. Отрицателна начална позиция казва на Oracle, че началната позиция се изчислява от края на низа. Така че началната позиция ще бъде дължината на низа минус три и е равна на 8. Третият параметър е равен на две и се връща стойността '#9'.

Функция REPLACE

Функцията REPLACE замества всички срещания на търсения елемент със стойността на низа, който трябва да бъде заменен. Ако дължината на елемента, който се заменя, не е равна на дължината на елемента, който се заменя, дължината на получения низ ще бъде различна от оригиналния низ. Ако търсеният подниз не бъде намерен, низът се връща непроменен. Има три налични параметъра, първите два са задължителни и извикващият синтаксис е REPLACE(източен низ, елемент за търсене, ). Ако не посочите изрично параметъра за заместващ елемент, всички срещания на елемент за търсене се премахват от изходния низ. С други думи, заместващият елемент е равен на празния низ. Ако всички знаци в оригиналния низ са заменени с празен елемент, replace element връща NULL. Нека разгледаме няколко искания

Заявка 1: изберете replace('1#3#5#7#9#','#','->') от двойно

Заявка 2: изберете replace('1#3#5#7#9#','#') от двойно

Заявка 3: изберете replace('#','#') от dual

Хешът в първата заявка показва знака, който да търсите, и низа, който да замени „->“. Хешът се появява в низа пет пъти и се заменя, което води до крайния низ ‘1->3->5->7->9->’. Заявка 2 не указва изрично заместващия низ. Стойността по подразбиране е празният низ и резултатът ще бъде „13579“. Заявка номер три ще върне NULL.

Използване на числови функции

Oracle има много вградени функции за работа с числа. Съществената разлика между числовите функции и другите е, че тези функции приемат само числа като параметри и връщат само числа. Oracle предоставя числени функции за работа с тригонометрични, експоненциални и логаритмични изрази и много други. Ще се съсредоточим върху прости цифрови низови функции: ROUND, TRUNC и MOD.

ROUND функция

Функцията ROUND закръгля число в зависимост от необходимата точност. Върнатата стойност се закръгля нагоре или надолу, в зависимост от стойността на последната цифра в исканата цифра. Ако стойността за точност е n, тогава цифрата, която ще бъде закръглена, ще бъде на позиция n след десетичната запетая и стойността ще зависи от цифрата на позиция (n+1). Ако стойността на точността е отрицателна, тогава всички цифри след цифрата n вляво от десетичната запетая ще бъдат 0, а стойността на n ще зависи от n+1. Ако стойността на цифрата, от която зависи закръгляването, е по-голяма или равна на 5, тогава се извършва закръгляване нагоре, в противен случай надолу.

Функцията ROUND приема два входни параметъра и синтаксиса ROUND(номер на източника, десетична точност). Номерът на източника може да бъде всяко число. Параметърът за десетична точност указва необходимата точност и не е задължителен. Ако този параметър не е посочен, стойността по подразбиране ще бъде 0, което показва, че трябва да се закръгли до най-близкото цяло число.

Разгледайте таблица 10-1 за числото 1601.916. Отрицателните стойности на точност са вляво от точката (цяла част), докато положителните стойности на точност се считат вдясно от точката (дробна част).

Ако стойността на точността е единица, стойността се закръгля до най-близките десет. Ако две, тогава стойността се закръгля до втори ред и т.н. Следните заявки показват използването на тази функция

Заявка 1: изберете кръгъл (1601.916, 1) от двойно;

Заявка 2: изберете кръгъл (1601.916, 2) от двойно;

Заявка 3: изберете кръгъл (1601.916, -3) от двойно;

Заявка 4: изберете кръг (1601.916) от двойно;

Първата заявка използва параметъра за точност, равен на едно, което означава, че числото ще бъде закръглено до най-близката десета. Тъй като стойността на стотната част е равна на едно (по-малко от 5), тя се закръгля надолу и се връща стойността 1601,9. Точността на втората заявка е две, така че стойността е заобиколена до най-близката стотна. Тъй като стойността на хилядните е 6 (което е по-голямо от 5), стойността на стотните се закръгля нагоре и върнатата стойност е 1601,92. Стойността на параметъра за точност в третата заявка е минус три. Тъй като стойността е отрицателна, това означава, че закръгляването ще се извърши въз основа на стойността на третата позиция вляво от периода, на второ място (стотици), и стойността е 6. Тъй като 6 е по-голямо от пет, то ще закръгли и ще върне стойността 2000. Заявка 4 извиква функция без параметър за точност. Това означава, че числото е закръглено до най-близкото цяло число. Тъй като десетата част е 9, стойността се закръгля нагоре и върнатата стойност е 1602.

Числова функция TRUNC

Функцията TRUNC намалява стойността на число въз основа на стойността на параметъра за точност. Редукцията се различава от закръгляването по това, че по време на редукцията излишната част просто се отрязва и не настъпват промени в останалите цифри на числото. Ако стойността на точността е отрицателна, тогава входната стойност се отрязва в позицията вляво от десетичната запетая. Синтаксисът на функцията е TRUNC(номер на източника, десетична точност). Параметърът номер източник може да бъде произволно число и този параметър е задължителен. Параметърът за десетична точност указва позицията на закръгляване и не е задължителен; стойността по подразбиране ще бъде нула, което означава редуциране до цяло число.

Ако стойността на десетичната точност е едно, тогава числото се редуцира до десети, ако две, след това до стотни и т.н. Нека да разгледаме някои примери за използване на тази функция

Заявка 1: изберете trunc(1601.916, 1) от dual;

Заявка 2: изберете trunc(1601.916, 2) от dual;

Заявка 3: изберете trunc(1601.916, -3) от dual;

Заявка 4: изберете trunc(1601.916) от dual;

Заявка 1 използва точност до едно, което означава, че намалява стойността до десети и връща стойността 1601,9. Точността във втората заявка е две, първоначалната стойност се намалява до стотни и се връща стойността 1601,91. Моля, имайте предвид, че получената стойност ще се различава от стойността, върната от функцията ROUND със същите параметри, тъй като извикването на ROUND ще закръгли нагоре (6 е по-голямо от 5). Заявка номер три използва отрицателно число като стойност за параметъра за точност. Позицията три вляво от десетичната запетая означава, че съкращението ще бъде до третата цифра (стотици съкратени), както е показано в таблица 10-1, и върнатата стойност ще бъде 1000. И накрая, в четвъртата заявка стойността за точност е не е изрично указано и дробната част от оригиналното число е съкратена. Резултатът ще бъде 1601.

MOD функция

Функцията MOD връща остатъка от деление. Две числа, дивидентът (числото, което се дели) и делителят (числото, на което се дели) се дефинират като параметри и операцията деление се изчислява. Ако дивидентът е разделен от делителя на цяло, тогава се връща нула, тъй като няма остатък. Ако делителят е нула, тогава не възниква грешка при деление на нула, но се връща дивидентът. Ако делителят е по-голям от дивидента, дивидентът се връща.

Функцията MOD има два входни параметъра и синтаксисът е MOD(дивидент, делител). Параметрите на дивидент и делител могат да бъдат числови литерали, колони или изрази и могат да бъдат положителни или отрицателни. Следните примери показват използването на тази функция

Заявка 1: изберете mod(6, 2) от dual

Заявка 2: изберете mod(5, 3) от dual

Заявка 3: изберете mod(7, 35) от dual

Заявка 4: изберете mod(5.2, 3) от dual

В заявката едно 6 се дели на две без остатък и се връща 0. В заявката две 5 се дели на 3, цялата част ще бъде 1 и се връща остатъкът 2. В заявка номер три седем се дели на 35. Тъй като делителят е по-голям от дивидента, дивидентът се връща, тези. цялата част е 0. Заявка четири използва дроб като неин дивидент. Цялата част ще бъде единица, а остатъкът ще бъде 2,2.

Всяко четно число се дели на две без остатък, всяко нечетно число, когато е разделено на две, ще върне остатък от 1. Следователно функцията MOD често се използва за разграничаване между четни и нечетни числа.

Работа с дати

Предлагат функции за дата удобен начинрешаване на проблеми, свързани с дати, без да се налага да се вземат предвид високосните години, колко дни има в даден месец. Първо, нека да разгледаме как се съхраняват данните за датата и форматирането на датата, както и функцията SYSDATE. След това ще разгледаме функциите ADD_MONTHS, MONTHS_BETWEEN, LAST_DAT, NEXT_DAY, ROUND и TRUNC.

Съхраняване на дата в база данни

Базата данни съхранява данни като число, което е в състояние да поддържа изчислението на век, година, месец и ден, както и информация за времето като час, минута и секунда. Когато възникне заявка за данни, към числото се прилага определено форматиране (маска) и по подразбиране маската показва деня, първите три букви от името на месеца и две цифри, представляващи годината.

Функция SYSDATE

Функцията SYSDATE не използва входни параметри и връща текущия час и дата, зададени на сървъра на базата данни. По подразбиране функцията SYSDATE връща дата във формат DD-MON-RR и показва датата на сървъра. Ако сървърът е инсталиран в различна часова зона от клиентската машина, тогава времето и датата, върнати от SYSDATE, може да се различават от локалните стойности на клиентската машина. Можете да стартирате заявка като тази, за да покажете системната дата на сървъра

изберете sysdate от dual

Аритметика с дати

Следното уравнение показва важен принцип при работа с дати

Дата1 – Дата2 = Число1

Датата може да бъде извадена от друга дата. Разликата между две дати се разбира като броя на дните между тях. Всяко число, включително дроби, може да се добавя или изважда от дата. В този контекст числото представлява броя на дните. Сумата или разликата между число и дата винаги е дата. Този принцип предполага, че събирането, умножаването или разделянето на две дати е невъзможно.

Функция МЕСЕЦИ_МЕЖДУ

Функцията MONTHS_BETWEEN връща броя месеци между два задължителни входни параметъра. Синтаксис на функцията MONTHS_BETWEEN(дата1, дата2). Функцията изчислява разликата между date1 и date2. Ако date1 е по-малко от date2, тогава се връща отрицателно число. Връщаната стойност може да се състои от цяло число, което представлява броя на месеците между две дати, и дробна част, която представлява колко дни и часове остават (на базата на месец от 31 дни) след изваждане на целия брой месеци . Цялото число се връща, ако денят на сравняваните месеци е един и същ или последният ден на съответния месец.

Следните примери използват функцията MONTHS_BETWEEN

Заявка 1: изберете months_between(sysdate, sysdate-31) от dual;

Да приемем, че текущата дата е 16 април 2009 г. Заявка едно ще върне единица като брой месеци между 16 април 2009 г. и 16 март 2009 г. Заявка две имплицитно преобразува литералите в дати, използвайки формата ДД-ПОН-ГГГГ. Тъй като часовата част липсва, Oracle ще зададе стойността на часа на 00.00.00 и за двете дати. Функцията ще върне стойност, приблизително равна на 1,03225806. Цялата част от резултата означава, че има един месец между датите. Между 28 февруари и 28 март има точно един месец. Тогава дробната част трябва да показва точно един ден. Резултатът включва часове, минути и секунди, но в нашия случай времевият компонент на датите е същият. Умножаването на 0,03225806 по 31 ще върне 1, тъй като дробната част, върната от MONTHS_BETWEEN, се изчислява, като се приема, че месецът е точно 31 дни. Следователно заявка номер три ще върне стойността 32.

Популярна грешка е да се приеме, че типът данни за връщане на дадена функция зависи от типа на функцията (функциите за дата трябва да връщат дата, функциите за обработка на низ трябва да връщат низ). Това важи само за числови функции. Функциите за символи и дати могат да върнат стойност от произволен тип данни. Например INSTR е символна функция, а MONTS_BETWEEN е функция за дата, но и двете връщат число. Също така често погрешно се смята, че разликата между датите е датата, когато това всъщност е датата.

Функция ADD_МЕСЕЦА

Функцията ADD_MONTHS връща дата, получена чрез добавяне на определен брой месеци към оригинална дата. Тази функция има два задължителни параметъра и синтаксисът е ADD_MONTHS(начална дата, брой месеци). Стойността на параметъра брой месеци може да бъде отрицателна, тогава първоначалната стойност ще бъде намалена с този брой месеци и дробна, но само целочислената част ще бъде взета предвид. Следващите три заявки показват използването на функцията ADD_MONTHS

Резултатът от първото запитване ще бъде 7 май 2009 г., тъй като денят остава същият, ако е възможно, а месецът се увеличава с единица. Във втората заявка броят на месеците е дробен, което се игнорира, т.е. тази заявка е равна на ADD_MONTHS('31-DEC-2008',2). Добавянето на два месеца трябва да върне 31-FEB-2009, но такава дата не съществува, така че се връща последният ден от месеца. Последният пример използва отрицателно число за параметъра брой месеци и връща датата 07-APR-2008, която е дванадесет месеца по-рано от първоначалната стойност.

Функция NEXT_ДЕН

Функцията NEXT_DATE връща следващия най-близък зададен ден от седмицата след първоначалната дата. Тази функция има два задължителни параметъра и синтаксисът е NEXT_DAY (начална дата, ден от седмицата). Функцията изчислява стойността, когато посоченият ден от седмицата настъпи след началната дата. Параметърът ден от седмицата може да бъде определен като число или низ. Валидните стойности се определят от параметъра NLS_DATE_LANGUAGE и по подразбиране се използват първите три букви от името на деня от седмицата във всеки случай (SUN, mon и т.н.) или цели числа, където 1 е неделя, 2 е понеделник и така На. Също така имената на дните от седмицата могат да бъдат с повече от три знака; например неделя може да се посочи като слънце, неделя, неделя. Нека разгледаме няколко искания

1 януари 2009 г. е четвъртък. Следващият вторник ще бъде след 5 дни, 6 януари 2009 г. Втората заявка ще се върне на 7 януари 2009 г. - следващата сряда след 1 януари. Третата заявка използва число като параметър и ако имате зададени американски стойности, тогава петият ден е четвъртък. Следващият четвъртък след 1 януари е точно седмица по-късно - 8 януари 2009 г.

ПОСЛЕДНА функция_ДЕН

Функцията LAST_DAY връща датата на последния ден от месеца на първоначалната дата. Тази функция изисква един задължителен параметър и синтаксиса LAST_DAY (начална дата). Функцията избира месеца на изходната дата и след това изчислява последния ден от месеца. Следната заявка ще върне 31 януари 2009 г

ROUND функцияза работа с дати

Функцията ROUND закръгля стойност на дата до указаната точност на датата. Върнатата стойност се закръгля нагоре или надолу в зависимост от стойността на елемента, който се закръгля. Тази функция изисква един задължителен параметър и приема един незадължителен параметър, а синтаксисът на функцията е ROUND(изходна дата, ). Параметърът на данните за източника може да бъде всеки елемент от типа данни за дата. Параметърът за формат на точността на датата указва нивото на закръгляване и стойността по подразбиране е ден. Форматът за точност на датата може да бъде век (CC) година ГГГГ тримесечие Q месец M седмица W ден DD час HH минута MI.

Закръгляването до век е еквивалентно на добавяне на единица към текущия век. Закръгляването до месеца ще бъде нагоре, ако денят е повече от 16, в противен случай ще се закръгли до първия ден от месеца. Ако месецът е от едно до шест, закръгляването ще бъде към началото на текущата година, в противен случай се връща началната дата на следващата година. Нека разгледаме молбата

Да приемем, че тази заявка е била изпълнена на 17 април 2009 г. в 00:05. Първо, текущата дата се закръгля до най-близкия ден (параметърът за точност не е изрично посочен). Тъй като часът е 00:05, денят не е закръглен. Тъй като 1 април 2009 г. е сряда, втората колона ще върне срядата от седмицата, която включва първоначалната дата. Първата сряда от седмицата, която включва 19 април, е 15 април 2009 г. Третата колона закръгля месеца до следващия (тъй като 17 е по-голямо от 16) и връща 01 май 2009 г. Последната колона закръглява датата до следващата година и връща 1 януари 2009 г., тъй като април е 4-ият месец.

Функция TRUNCпри работа с дати

Функцията TRUNC съкращава датата въз основа на параметъра за точност. Тази функция има един задължителен и един незадължителен параметър и синтаксисът на извикването е TRUNC(изходна дата, ). Параметърът за дата на източника може да бъде всяка валидна дата. Параметърът за формат на точността на датата указва нивото на съкращение на датата и не е задължителен; стойността по подразбиране е съкращение на деня. Това означава, че всички времеви стойности се нулират на 00 часа 00 минути 00 секунди. Намаляването до месец ще върне дата, равна на първия ден от месеца на първоначалната дата. Съкращаването до година ще върне първия ден от годината от първоначалната дата. Помислете за заявка, използваща функция с различни параметри

Тази заявка ще бъде изпълнена на 17 април в 00:05 ч. В първата колона има съкращения системна датакъм ден, часът се преобразува от 00:05 в 00:00 (параметърът за точност не е изрично указан, използва се стойността по подразбиране) и се връща текущият ден. Втората колона съкращава датата до същия ден от седмицата като първото от месеца (сряда) и връща срядата от текущата седмица - 15 април. Третата колона съкращава датата до месец и връща първия ден от месеца - 1 април. Четвъртата колона съкращава датата до година и връща първия ден от годината.