This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT t2.rnum | |
FROM (SELECT t.rnum, | |
RANK() OVER(PARTITION BY t.rnum ORDER BY(1)) rnk | |
FROM ( SELECT trunc(dbms_random.value(1, 1000)) AS rnum | |
FROM dual | |
CONNECT BY LEVEL <= 1000) t | |
) t2 | |
WHERE t2.rnk < 4; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
Имеется таблица без первичного ключа. Известно, что в таблице имеется задвоение данных. | |
Необходимо удалить дубликаты из таблицы. | |
create table t (a number, b number); | |
Пример данных: | |
a b | |
1 1 | |
2 2 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
Имеется | |
xmltype(' | |
<root> | |
<row> | |
<col>v11</col> | |
<col>v12</col> | |
<col>v13</col> | |
<col>v14</col> | |
</row> |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
Имеется: | |
xmltype(' | |
<root> | |
<row> | |
<col>v11</col> | |
<col>v12</col> | |
<col>v13</col> | |
<col>v14</col> |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
Имеется: | |
xmltype(' | |
<root> | |
<row> | |
<col>v11</col> | |
<col>v12</col> | |
<col>v13</col> | |
<col>v14</col> |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
Имеется декларация типа: | |
CREATE OR REPLACE TYPE TNUM as table of number; | |
Необходимо написать реализацию функции, возвращающую в качестве результата заполненный массив | |
имеющий тип TNUM с значениями от 1..1000 | |
*/ | |
CREATE OR REPLACE NONEDITIONABLE FUNCTION fnc_get_tnum |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
Задана строка '1,2,3,4', необходимо, используя регулярные выражения в запросе получить результат: | |
C1 C2 C3 C4 | |
-- -- -- -- | |
1 2 3 4 | |
*/ | |
SELECT regexp_substr(t.str,'[^,]+',1,1) as c1, | |
regexp_substr(t.str,'[^,]+',1,2) as c2, |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* Метод pipelined. | |
Имеется таблица dept со следующей структурой: | |
Name Type Nullable Default Comments | |
------ ------------ -------- ------- -------- | |
DEPTNO NUMBER | |
DNAME VARCHAR2(14) Y | |
LOC VARCHAR2(13) Y |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Вывод, только рабочих дней на следующие 30 дней. | |
SELECT * | |
FROM ( | |
SELECT TRUNC(SYSDATE -1) + LEVEL ToDate, | |
to_char((TRUNC(SYSDATE -1) + LEVEL),'DY') AS ToDay | |
FROM dual CONNECT BY LEVEL <= 30 | |
) | |
WHERE ToDay NOT IN ('СБ','ВС'); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* Выводит предыдущее, текущее и последующее значение даты */ | |
WITH t AS (SELECT TRUNC(SYSDATE-1) + LEVEL sdate FROM dual CONNECT BY LEVEL <= 10 ORDER BY TRUNC(SYSDATE)) | |
SELECT | |
LAG(t.sdate) OVER(ORDER BY t.sdate) prev_date | |
,t.sdate | |
,LEAD(t.sdate) OVER(ORDER BY t.sdate) next_date | |
FROM t; |
OlderNewer