Основные команды в Oracle на PL\SQL

Изучив некоторый списоклитературы по Oracleприходим к выводу что всю эту информацию нужно документировать и конспектировать, т.к. объем полученный информации слишком велик, изучив некоторые книги по Oracle можно законспектировать пожалуй основные команды для дальнейшей нашей работы с базой, как разработчикам, именно как разработчиком, ведь структура Oracle на столько велика что что СУБД в больших организациях обслуживает большое количество персонала, такие как администраторы Oracle, разработчики, аналитики т.д. и так выпишем основные команды, которые нам могут понадобиться для практического задания с возможными по необходимостью примерами и описаниями команды:

· CHAR(n), VARCHAR2(n) - Строки переменной длины.

· INTEGER - Масштабируемое целое.

· NUMBER(n) - Масштабируемое целое с плавающей точкой.

· DATE - Дата/Время

· ROWID, ROW - Идентификаторы записей в БД.

· BLOB - Большие двоичные обьекты.

· CLOB - Большие строковые объекты.

· BFILE - Указатели на большие внешние объекты.

· SELECT включает в себя список столбцов возвращаемых запросом.

· FROM включает в себя список таблиц для выполнения запроса.

· WHERE устанавливает условие поиска, если необходимо вернуть не все строки, а только ту часть, которая описана условием, поиска.

· GROUP BY позволяет создать итоговой запрос, разбитый на группы.

SQL> SELECT REP, AVG(AMOUNT) 2 FROM ORDERS 3 GROUP BY REP***************************SQL> SELECT REP_OFFICE, MIN(QUOTA), MAX(QUOTA) 2 FROM SALESREPS 3 GROUP BY REP_OFFICE*******************************SQL> SELECT REP, CUST, SUM(AMOUNT) 2 FROM ORDERS 3 GROUP BY REP, CUST*******************************SELECT EMPL_NUM, NAME, SUM(AMOUNT)FROM ORDERS, SALESREPSWHERE REP = EMPL_NUMGROUP BY EMPL_NUM, NAME

· HAVING определяет условие возврата групп и используется только совместно с GROUP BY.

SQL> SELECT REP, AVG(AMOUNT) 2 FROM ORDERS 3 GROUP BY REP 4 HAVING SUM(AMOUNT) > 300*******************************SQL> SELECT CITY, SUM(QUOTA), SUM(SALESREPS.SALES) 2 FROM OFFICES, SALESREPS 3 WHERE OFFICE = REP_OFFICE 4 GROUP BY CITY 5 HAVING COUNT(*) >= 2

· ORDER BY определяет порядок сортировки результирующего набора данных. DESC предикат сортировки по убыванию. ASC предикат по убыванию. Если столбец сортировки вычисляемый и не имеет имени, в выражении ORDER BY можно просто указать его порядковый номер(номер столбца)

SQL> SELECT CITY, REGION, (SALES - TARGET) 2 FROM OFFICES 3 ORDER BY REGION ASC, 3 DESC

· SUM() - Вычисляет сумму всех значений находящихся в столбце.

SQL> SELECT SUM(QUOTA), SUM(SALES)2 FROM SALESREPS************** SQL> SELECT SUM(AMOUNT) 2 FROM ORDERS, SALESREPS 3 WHERE NAME = 'Дима Маликов' 4 AND REP = EMPL_NUM

· AVG() - Вычисляет среднее всех значений содержащихся в столбце.

SQL> SELECT AVG(100 * (SALES/QUOTA)) PROCENT2 FROM SALESREPS************ SQL> SELECT AVG(PRICE) 2 FROM PRODUCTS 3 WHERE MFR_ID = 'ACI'

· MIN() - Находит наименьшее среди всех значений содержащихся в столбце.

SQL> SELECT MIN(ORDER_DATE) 2 FROM ORDERS

· MAX() - Находит наибольшее среди всех значений содержащихся в столбце.

SQL> SELECT MAX(100 * (SALES/QUOTA)) 2 FROM SALESREPS

· COUNT() - Подсчитывает количество значений, содержащихся в столбце.

SQL> SELECT COUNT(CUST_NUM)2 FROM CUSTOMERS************************** SQL> SELECT COUNT(NAME) 2 FROM SALESREPS 3 WHERE SALES > QUOTA

· COUNT(*) - Подсчитывает количество строк в таблице результатов запроса.

· DISTINCT убирает одинаковые значения в столбце

SQL> SELECT COUNT(DISTINCT TITLE)2 FROM SALESREPS************************** SQL> SELECT COUNT(DISTINCT REP_OFFICE) 2 FROM SALESREPS 3 WHERE SALES > QUOTA

· TO_DATE('01.06.1988','DD/MM/YYYY') - встроенная функция PL/SQL Oracle для работы с датами.

· BETWEEN, по своей сути это выражение проверки на принадлежность к диапазону значений

SQL> SELECT ORDER_NUM, ORDER_DATE, MFR, PRODUCT, AMOUNT 2 FROM ORDERS 3 WHERE ORDER_DATE BETWEEN TO_DATE('01.11.1989','DD/MM/YYYY') AND TO_DATE('31.12.1989','DD/MM/YYYY')· NOT обуславливает обратное инвертирование условия, то есть "не принадлежит"SQL> SELECT NAME, SALES, QUOTA 2 FROM SALESREPS 3 WHERE SALES NOT BETWEEN (0.8 * QUOTA) AND (1.2 * QUOTA)· IN это проверка на принадлежность множеству значений или, иначе говоря, членство в множестве

Найти все заказы, сделанные в разные дни июня месяца 1990 года.

SQL> SELECT ORDER_NUM, ORDER_DATE, AMOUNT 2 FROM ORDERS 3 WHERE ORDER_DATE IN (TO_DATE('14.06.1990','DD/MM/YYYY'), TO_DATE('08.06.1990','DD/MM/YYYY'), 4 TO_DATE('29.06.1990','DD/MM/YYYY'), TO_DATE('04.06.1990','DD/MM/YYYY')) 5 /· LIKE оператор поискаSQL> SELECT COMPANY, CREDIT_LIMIT 2 FROM CUSTOMERS 3 WHERE COMPANY LIKE '%бесы'

· '_' - это означает, что вы не уверены, например в правильности ввода какого либо слова

· OR – или

· Алгебра выражения AND.

Значения Результат-------------- ---------- TRUE AND TRUE -> TRUEFALSE AND TRUE -> FALSETRUE AND FALSE -> FALSEFALSE AND FALSE -> FALSENULL AND TRUE -> NULLTRUE AND NULL -> NULLFALSE AND NULL -> FALSENULL AND FALSE -> FALSENULL AND NULL -> NULL

· Алгебра выражения OR.

Значения Результат-------------- ---------- TRUE OR TRUE -> TRUEFALSE OR TRUE -> TRUETRUE OR FALSE -> TRUEFALSE OR FALSE -> FALSENULL OR TRUE -> TRUETRUE OR NULL -> TRUEFALSE OR NULL -> NULLNULL OR FALSE -> NULLNULL OR NULL -> NULL

· Алгебра выражения NOT.

NOT TRUE -> FALSENOT FALSE -> TRUENOT NULL -> NULL· UNION, он позволяет объединить результаты двух запросовSQL> SELECT MFR_ID, PRODUCT_ID 2 FROM PRODUCTS 3 WHERE PRICE > 500.00 4 UNION 5 SELECT DISTINCT MFR, PRODUCT 6 FROM ORDERS 7 WHERE AMOUNT > 4.00· CREATE создание таблицы SQL> CREATE TABLE PEOPLE 2 (ID NUMBER, 3 NM VARCHAR2(50), 4 FAMIL VARCHAR2(50), 5 OTCH VARCHAR2(50), 6 DROG DATE 7)· INSERT добавление строки в таблицу INSERT INTO PEOPLE(ID, NM, FAMIL, OTCH, DROG) VALUES(1, 'John', 'Godwin', 'Petrovich', TO_DATE('03-12-1967','DD-MM-YYYY'))· CREATE INDEX создать индекс· ROLLBACK Если нужно все вернуть на круги своя, оборвав действия текущей транзакции, введите оператор · Избавляемся от лишней «писанины» SELECT S.NAME, S.QUOTA, B.BIRTHDAYS.BIRTH_DAY FROM SALESREPS S, SAM.BIRTHDAYS B WHERE S.NAME = B.NAME *****************SELECT EMPS.NAME, MGRS.NAME FROM SALESREPS EMPS, SALESREPS MGRS WHERE EMPS.MANAGER = MGRS.EMPL_NUMСамо объединение таблицы SQL> SELECT EMPS.NAME, EMP_OFFICE.CITY, MGRS.NAME, MGR_OFFICE.CITY 2 FROM SALESREPS EMPS, SALESREPS MGRS, OFFICES EMP_OFFICE, 3 OFFICES MGR_OFFICE 4 WHERE EMPS.REP_OFFICE = EMP_OFFICE.OFFICE 5 AND MGRS.REP_OFFICE = MGR_OFFICE.OFFICE 6 AND EMPS.MANAGER = MGRS.EMPL_NUM 7 AND EMPS.REP_OFFICE <> MGRS.REP_OFFICE

· Арифметические операторы:

+	Сложение и унарный плюс
-	Вычитание и унарный минус
*	Умножение
/	Деление
**	Возведение в степень

· Операторы отношения (используются в логических выражениях):

=	Равенство
<	Меньше
>	Больше
<>	Не равно
!=	Не равно (альтернатива)
~=	Не равно (альтернатива)
^=	Не равно (альтернатива)
<=	Меньше или равно
>=	Больше или равно

· Выражение и списки (используются в операторах, объявлениях типов данных, объявлениях списков параметров, ссылках на переменные и таблицы):

:=	Присвоение
(	Начало списка или подвыражения
)	Конец списка или подвыражения
,	Отдельные элементы списка (как в списке параметров)
..	Оператор диапазона используется в операторах FOR-IN
||	Конкатенация строк
=>	Ассоциация (используется в списке параметров)
;	Конец выражения
%	Атрибут курсора или типа объекта
.	Спецификация объекта
@	Индикатор удаленной базы данных
'	Начало/конец строки символов
:	Индикатор внешней переменной
&	Индикатор связанной переменной

· Комментарии и метки

--	Комментарий в одной строке
/*	Начало многострочного комментария
*/	Конец многострочного комментария
>>	Начало метки
<<	Конец метки

· SET SERVEROUTPUT ON выводит сообщение в консоль.· DBMS_OUTPUT подключает пакет вывода текста в консоль.

· put_line() метод отображения информации в теле BEGIN END.

· BEGIN END - это обрамляющий программный блок.

· DECLARE объявление переменных.

· TO_CHAR() явное преобразование типа в текстовый

· TO_NUMBER() преобразует строковый литерал в числовой литерал

SQL> DECLARE 2 3 A VARCHAR2(1):= ' '; 4 B VARCHAR2(128):= 'Hello World!!!'; 5 C VARCHAR2(128):= 'How ''are'' you?'; 6 D VARCHAR2(128):= 'Hello Bob - "ok"!'; 7 E VARCHAR2(128):= '12345'; 8 F VARCHAR2(128):= '01/01/1989'; 9 G VARCHAR2(128):= '!@#$%^&*()_":;<,.?'; 10 H VARCHAR2(128):= ''' '''; 11 12 I NUMBER:= 12345; 13 J NUMBER:= -12345; 14 K NUMBER:= 12345.023745; 15 L NUMBER:= 100.; 16 M NUMBER:= 1.0237E2; 17 N NUMBER:= 1.0237E-2; 18 O NUMBER:= 0.34223; 19 P NUMBER:=.321434; 20 21 BEGIN 22 23 DBMS_OUTPUT.enable; 24 25 DBMS_OUTPUT.put_line(A); 26 DBMS_OUTPUT.put_line(B); 27 DBMS_OUTPUT.put_line(C); 28 DBMS_OUTPUT.put_line(D); 29 DBMS_OUTPUT.put_line(E); 30 DBMS_OUTPUT.put_line(F); 31 DBMS_OUTPUT.put_line(G); 32 DBMS_OUTPUT.put_line(H); 33 34 DBMS_OUTPUT.put_line(TO_CHAR(I)); 35 DBMS_OUTPUT.put_line(TO_CHAR(J)); 36 DBMS_OUTPUT.put_line(TO_CHAR(K)); 37 DBMS_OUTPUT.put_line(TO_CHAR(L)); 38 DBMS_OUTPUT.put_line(TO_CHAR(M)); 39 DBMS_OUTPUT.put_line(TO_CHAR(N)); 40 DBMS_OUTPUT.put_line(TO_CHAR(O)); 41 DBMS_OUTPUT.put_line(TO_CHAR(P)); 42 43 END; 44 /· DECLARE - Открывается неименованный блок. Price NUMBER(5,2):= 12.43; - Переменная типа NUMBER (с плавающей точкой Sytki NUMBER:= 123; - Переменная типа NUMBER простая. LGos INTEGER:= 2; - Переменная типа INTEGER подкласс NUMBER типа. Max_Dist CONSTANT REAL:= 0.45; -- Константа типа REAL. Тип CONSTANT требует обязательной инициализации! Dis_Tp VARCHAR2(1):= NULL; - Строковая переменная типа VARCHAR2 (присвоено значение NULL) Ds_Nm CHAR(20):= NULL - Строковая переменная типа CHAR (присвоено значение NULL) Tk_Ir VARCHAR2(50) NOT NULL:= 'Hello World!!!'; - Строковая переменная типа VARCHAR2 не может иметь значение NULL! Tk_Sr BOOLEAN NOT NULL:= TRUE; - БУЛЕВА! переменная типа BOOLEAN не может иметь значение NULL! It_Nm VARCHAR2(50) DEFAULT 'Hummer'; - Строковая переменная типа VARCHAR2 имеет значение по умолчанию· IF логический оператор IF (val = 1) THEN -- проверка условия..ELSIF (val = 2) THEN..ELSIF (val = 3) THEN..ELSIF (val = 9) THEN..ELSE -- не сработало не одно из условий! END IF; -- конец условного оператора.****************************************SQL> SET SERVEROUTPUT ONSQL> DECLARE 2 3 A INTEGER:= 7; 4 B INTEGER:= 4; 5 OPER VARCHAR2(2):= '+'; 7 BEGIN 9 DBMS_OUTPUT.enable; 10 IF (OPER = '+') THEN 11 DBMS_OUTPUT.put_line('Operation is '||OPER||' '||'sum = '||TO_CHAR(A+B)); 12 ELSIF (OPER = '-') THEN 13 DBMS_OUTPUT.put_line('Operation is '||OPER||' '||'res = '||TO_CHAR(A-B)); 14 ELSIF (OPER = '*') THEN 15 DBMS_OUTPUT.put_line('Operation is '||OPER||' '||'mul = '||TO_CHAR(A*B)); 16 ELSIF (OPER = '/') THEN 17 DBMS_OUTPUT.put_line('Operation is '||OPER||' '||'div = '||TO_CHAR(A/B)); 18 END IF; 19 20 END;

· || оператор соединения в выводе

· & дает возможность вводить данные при компиляции пример: ‘&todo’

· PRIMARY KEY то есть является первичным ключом таблицы.

Перезаливка таблицы осуществляется созданием новой таблицы, затем в нее копируем

SQL> INSERT INTO OLD_PEOPLE –-пустая таблица 2 SELECT * FROM PEOPLE –таблица с которой копируем 3 /· DELETE удаление строкSQL> DELETE FROM PEOPLE 2 WHERE ID = 3 3 / ******************************DELETE FROM PEOPLE –удаление всех полей в талице/COMMIT/ UPDATE обновление данных в таблице SQL> UPDATE PEOPLE 2 SET FM = 'Jason' 3 WHERE FM IS NULL 4 /

*************************

SQL> UPDATE PEOPLE 2 SET NM = 'Irvin', 3 FM = 'Show', 4 OT = 'Brefovich' 5 WHERE ID = 5

***************************

SQL> UPDATE PEOPLE 2 SET ID = 5+2 3 WHERE ID = 5

· LOOP оператор цикла бесконечного уловия выхода: EXIT WHEN - Выход при выполнении условия. GOTO - Выход из цикла во внешний контекст. EXIT - Безусловный выход из цикла. Используется посредством применения оператора IF.

SQL> DECLARE 2 k NUMBER:= 0; 3 4 BEGIN 5 6 WHILE (k < 10) LOOP 7 k:= k + 1; 8 END LOOP; 9 10 END; 11 /**********************************SQL> DECLARE 2 i NUMBER:= 0; 3 4 BEGIN 5 6 LOOP 7 i:= i + 1; 8 IF (i >= 100) THEN 9 i:= 0; 10 EXIT; 11 END IF; 12 END LOOP; 13 14 LOOP 15 i:= i + 1; 16 EXIT WHEN (i >= 100); 17 END LOOP; 18 19 END;Вывод четных значенийSQL> DECLARE 2 i NUMBER:= 0; 3 4 BEGIN 5 6 LOOP 7 i:= i + 1; 8 IF (i >= 100) THEN 9 i:= 0; 10 EXIT; 11 END IF; 12 END LOOP; 13 14 LOOP 15 i:= i + 1; 16 EXIT WHEN (i >= 100); 17 END LOOP; 18 19 END;· FOR оператор цикла SQL> DECLARE 2 s NUMBER:= 0; 3 4 BEGIN 5 DBMS_OUTPUT.enable; 6 FOR i IN 1..20 LOOP 7 IF(MOD(i, 2) = 1) THEN 8 DBMS_OUTPUT.put_line(TO_CHAR(i)||' is even!'); 9 s:= i; 10 END IF; 11 END LOOP; 12 DBMS_OUTPUT.put_line('last odd number was '||TO_CHAR(s)); 13 END; 14 /

***************************************

SQL> BEGIN 2 DBMS_OUTPUT.enable; 3 FOR i IN REVERSE 1..10 LOOP –реверс цикла 4 DBMS_OUTPUT.put_line(TO_CHAR(i)||'-'); 5 END LOOP; 6 DBMS_OUTPUT.put_line('Blastoff!'); 7 END;· %TYPE копирует тип переменной DECLARE v_MYCITY OFFICES.CITY%TYPE; v_MYSALES OFFICES.SALES%TYPE; v_TEMPOLD NUMBER(7,4) NOT NULL; v_DOPTEMP v_TEMPOLD%TYPE;· SUBTYPE создание подтипа DECLARE SUBTYPE t_LoopCn IS NUMBER; - Определяем новый типv_LpCounter t_LoopCn; - Объявляем переменную с эти подтипом %ROWTYPE создание типа как таблицыDECLAREv_RecOffices OFFICES%ROWTYPE; - таким образом переменная v_RecOffices получает переменные таблицы OFFICESПримеры курсоровDECLARE - 1. - Выбрать все заказы: CURSOR get_orders IS SELECT * FROM ORDERS; - 2. - Выбрать несколько столбцов - для определенного номера заказа CURSOR get_orders(Pord_num ORDERS.order_num%TYPE) IS SELECT ORDER_DATE, MFR, AMOUNT FROM ORDERS WHERE order_num = Pord_num; - 3. - Получить полную запись - для определенного номера заказа CURSOR get_orders(Pord_num ORDERS.order_num%TYPE) IS SELECT * FROM ORDERS WHERE order_num = Pord_num RETURN ORDERS%ROWTYPE; - 4. - Получение имени сотрудника - по его номеру CURSOR get_name(empl_nm SALESREPS.empl_num%TYPE) RETURN SALESREPS.name%TYPE IS SELECT name FROM SALESREPS WHERE empl_num =

Дата добавления: 2015-08-10