pl sql Basico

Prévia do material em texto

PL SQL
Básico
https://www.techonthenet.com/oracle/exists.php
PL SQL - Básico
Objetivo:
Possuir familiaridade com conceitos básicos e técnicos de PL SQL
Data Manipulation Language (DML) Statements
DELETE
INSERT
SELECT
UPDATE
-----------------
GROUP BY
ORDER BY
DISTINCT
JOINs
LIKE
UNION
UNION ALL
SUBQUERIES
MINUS
BETWEEN
EXISTS
NOT
IS NULL
IS NOT NULL
HAVING
AND e OR
DECODE
NVL
SELECT
A declaração Oracle SELECT é usada para recuperar registros de uma ou mais tabelas em um banco de dados Oracle.
SELECT [DADOS]
FROM [TABELAS]
< WHERE [CONDIÇÃO] >
INSERT
A instrução Oracle INSERT é usada para inserir uma única gravação ou vários registros em uma tabela no Oracle.
INSERT INTO <NOME> (COLUNAS) VALUES (VALORES)
INSERT INTO table
	(column1, column2, ... column_n )
	VALUES
	(expression1, expression2, ... expression_n );
DELETE
A declaração Oracle DELETE é usada para excluir um único registro ou vários registros de uma tabela no Oracle.
DELETE FROM table
	[WHERE conditions];
Exemplo:
DELETE FROM customers
	WHERE last_name = 'Smith';
UPDATE
A instrução UPDATE do Oracle é usada para atualizar registros existentes em uma tabela em um banco de dados Oracle. Existem 2 sintaxes para uma consulta de atualização no Oracle, dependendo se você está realizando uma atualização tradicional ou atualizando uma tabela com dados de outra tabela.
UPDATE table
SET column1 = expression1,
 column2 = expression2,
 ...
 column_n = expression_n
[WHERE conditions];
UPDATE table1
SET column1 = (SELECT expression1
 FROM table2
 WHERE conditions)
[WHERE conditions];
GROUP BY
A cláusula GROUP BY do Oracle é usada em uma instrução SELECT para coletar dados em vários registros e agrupar os resultados por uma ou mais colunas.
SELECT category, COUNT(*) AS "Number of suppliers"
	FROM suppliers
	WHERE available_products > 45
	GROUP BY category;
ORDER BY
A cláusula Oracle ORDER BY é usada para ordenar os registros em seu conjunto de resultados. A cláusula ORDER BY só pode ser usada em instruções SELECT
SELECT supplier_city
	FROM suppliers
	WHERE supplier_name = 'Microsoft'
 ORDER BY supplier_city ASC;
DISTINCT
A cláusula Oracle DISTINCT é usada para remover duplicidades do conjunto de resultados. A cláusula DISTINCT só pode ser usada com instruções SELECT.
SELECT DISTINCT city, state
FROM customers
WHERE total_orders > 10
ORDER BY city;
JOINs
Oracle JOIN é usado para recuperar dados de várias tabelas. Um Oracle JOIN é executado sempre que duas ou mais tabelas são unidas em uma instrução SQL.
Existem 4 tipos diferentes de junções Oracle:
Oracle INNER JOIN (ou às vezes chamado de junção simples)
Oracle LEFT OUTER JOIN (ou às vezes chamado LEFT JOIN)
Oracle RIGHT OUTER JOIN (ou às vezes chamado RIGHT JOIN)
Oracle FULL OUTER JOIN (ou às vezes chamado FULL JOIN)
INNER JOIN
É o tipo de associação mais comum. Oracle INNER JOIN retorna todas as linhas de várias tabelas onde a condição de junção é atendida.
SELECT columns
 FROM table1 
	INNER JOIN table2
	ON table1.column = table2.column;
LEFT OUTER JOIN
Esse tipo de associação retorna todas as linhas da tabela ESQUERDA especificada na condição ON e apenas as linhas da outra tabela em que os campos da junção são iguais (a condição de junção é atendida).
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
	FROM suppliers
	LEFT OUTER JOIN orders
	ON suppliers.supplier_id = orders.supplier_id;
RIGHT OUTER JOIN
Este tipo de união retorna todas as linhas da tabela DIREITA especificada na condição ON e apenas as linhas da outra tabela em que os campos da junção são iguais (a condição de junção é atendida).
SELECT orders.order_id, orders.order_date, suppliers.supplier_name
	FROM suppliers
	RIGHT OUTER JOIN orders
	ON suppliers.supplier_id = orders.supplier_id;
FULL OUTER JOIN
Esse tipo de associação retorna todas as linhas da tabela ESQUERDA e da tabela DIREITA com nulos no lugar onde a condição de junção não é atendida.
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
	FROM suppliers
	FULL OUTER JOIN orders
	ON suppliers.supplier_id = orders.supplier_id;
LIKE
Enquanto o operador de igualdade (=) corresponde exatamente a um valor de caractere para outro, as condições LIKE combinam uma parte de um valor.
SELECT last_name
	FROM customers
	WHERE last_name LIKE 'Ap%';
SELECT supplier_name
	FROM suppliers
	WHERE supplier_name LIKE 'Sm_th';
SELECT *
FROM suppliers
WHERE account_number LIKE '92314_';
UNION
Usado para combinar os conjuntos de resultados de 2 ou mais instruções Oracle SELECT. Ele remove as linhas duplicadas entre as várias instruções SELECT. Cada instrução SELECT dentro do operador UNION deve ter o mesmo número de campos nos conjuntos de resultados com tipos de dados semelhantes.
SELECT supplier_id
	FROM suppliers
	UNION
 SELECT supplier_id
	FROM order_details;
UNION ALL
O operador Oracle UNION ALL é usado para combinar os conjuntos de resultados de 2 ou mais instruções SELECT. Ele retorna todas as linhas da consulta e não remove as linhas duplicadas entre as várias instruções SELECT.
SELECT supplier_id
	FROM suppliers
	UNION ALL
 SELECT supplier_id
	FROM order;
SUBQUERIES
No Oracle, uma subconsulta é uma consulta dentro de uma consulta. Você pode criar subconsultas dentro de suas instruções SQL. Essas subconsultas podem residir na cláusula WHERE, na cláusula FROM ou na cláusula SELECT.
WHERE
SELECT * 
	FROM all_tables tabs
	WHERE tabs.table_name IN (SELECT cols.table_name
	 FROM all_tab_columns cols
 	 WHERE cols.column_name = 'SUPPLIER_ID');
SUBQUERIES
FROM
SELECT suppliers.name, subquery1.total_amt
	FROM suppliers,
	(SELECT supplier_id, SUM(orders.amount) AS total_amt
	FROM orders
	GROUP BY supplier_id) subquery1
	WHERE subquery1.supplier_id = suppliers.supplier_id;
SUBQUERIES
SELECT
SELECT tbls.owner, tbls.table_name,
 	 (SELECT COUNT(column_name) AS total_columns
	 FROM all_tab_columns cols
	 WHERE cols.owner = tbls.owner
	 AND cols.table_name = tbls.table_name) subquery2
	FROM all_tables tbls;
MINUS
É usado para retornar todas as linhas na primeira instrução SELECT que não são retornadas pela segunda instrução SELECT.
SELECT supplier_id
	FROM suppliers
	MINUS
	SELECT supplier_id
	FROM orders;
BETWEEN
A condição Oracle BETWEEN é usada para recuperar valores dentro de um intervalo em uma instrução SELECT, INSERT, UPDATE ou DELETE.
SELECT *
	FROM order_details
	WHERE order_date BETWEEN TO_DATE ('2014/02/01', 'yyyy/mm/dd')
	AND TO_DATE ('2014/02/28', 'yyyy/mm/dd');
EXISTS
É usada em combinação com uma subconsulta e é considerada "a ser atendida" se a subconsulta retornar pelo menos uma linha. 
SELECT *
	FROM customers
	WHERE EXISTS (SELECT *
 FROM order_details
 WHERE customers.customer_id = order_details.customer_id);
NOT
A condição Oracle NOT (também chamada de Operador NOT) é usada para negar uma condição em uma instrução SELECT, INSERT, UPDATE ou DELETE. 
SELECT *
	FROM customers
	WHERE customer_name NOT IN ( 'IBM', 'Hewlett Packard', 'Microsoft' );
IS NULL
A condição Oracle IS NULL é usada para testar um valor NULL. Você pode usar a condição Oracle IS NULL em uma instrução SQL ou em um bloco de código PLSQL.
INSERT INTO suppliers
	(supplier_id, supplier_name)
	SELECT account_no, name
	FROM customers
	WHERE city IS NULL;
IS NOT NULL
A condição Oracle IS NOT NULL é usada para testar um valor NOT NULL. Você pode usar a condição Oracle IS NOT NULL em uma instrução SQL ou em um bloco de código PLSQL.
INSERT INTO suppliers
	(supplier_id, supplier_name)
	SELECT account_no, name
	FROM customers
	WHERE city IS NOT NULL;
HAVING
A cláusula Oracle HAVING é usada em combinação com a cláusula GROUP BY para restringir osgrupos de linhas retornadas a apenas aqueles cuja condição é VERDADEIRA.
SELECT department, COUNT(*) AS "Number of employees"
	FROM employees
	WHERE salary < 49500
	GROUP BY department
	HAVING COUNT(*) > 10;
AND e OR
A condição Oracle AND e OR podem ser combinadas em uma instrução SELECT, INSERT, UPDATE ou DELETE. Ao combinar essas condições, é importante usar parênteses para que o banco de dados saiba qual ordem avaliar cada condição. (Assim como quando você estava aprendendo a ordem das operações na aula de Matemática!)
INSERT INTO suppliers
	(supplier_id, supplier_name)
	SELECT account_no, customer_name
	FROM customers
	WHERE (customer_name = 'Apple' OR customer_name = 'Samsung')
	AND customer_id > 20;
DECODE
A função DECODE Oracle / PLSQL possui a funcionalidade de uma instrução IF-THEN-ELSE.
SELECT supplier_name,
	DECODE(supplier_id, 10000, 'IBM',
	 10001, 'Microsoft',
	 10002, 'Hewlett Packard',
	 'Gateway') result
 	FROM suppliers;
NVL
A função Oracle / PLSQL NVL permite substituir um valor quando um valor nulo é encontrado.
SELECT NVL(supplier_city, 'n/a')
	FROM suppliers;
Executing