Apostila Basica PLSQL

Curso Bsico de PL/SQL

APOSTILA CONCEITOS BSICOS PL/SQL

Pag. 1


1.1.1 CREATE TABLE

Criando Tabelas Estruturas de Dados Descrio Tabela Armazena dados. Viso Representa logicamente subconjuntos de dados de uma ou mais tabelas. Seqncia Gera valores de chaves primrias. ndice Melhora o desempenho de algumas consultas. Comando Create Table

A sintaxe do SQL para CRIAR A TABELA CREATE TABLE "table_name" ("column 1" "data_type_for_column_1", "column 2" "data_type_for_column_2", ... )Exemplo: CREATE TABLE FORNECEDOR (NUM_NUMERO NUMBER(2) PRIMARY KEY, DES_NOME VARCHAR2(25) NOT NULL, DES_TELEFONE CHAR(7), DES_ENDERECO VARCHAR2(20), VAL_FORNEC NUMBER (8,2)); Observaes:-

O NOME de uma tabela deve comear por uma letra.

Pag. 2

Curso Bsico de PL/SQL - Pode ter at 30 caracteres. - Deve conter apenas: A-Z, a-z, 0-9, _, $ e #. - No pode ter o mesmo NOME de qualquer outro objeto existente no esquema do usurio. Tipos de Dados Oracle NUMBER Nmero ponto flutuante com preciso de 38 dgitos significativos NUMBER(p,s) Valor numrico com um nmero mximo de dgitos igual a p, e com s posies decimais. DATE Data e Hora CHAR(s) String de caracteres de tamanho fixo igual a s. O valor de s pode variar de 1 a 255. VARCHAR2(s) String de caracteres de tamanho varivel. Tamanho mximo igual a s. O valor de s pode variar de 1 a 2000. LONG String de tamanho varivel at 2 gigabytes. Somente uma coluna do tipo Long permitida por tabela. RAW e Equivalem a VARCHAR2 e LONG, respectivamente. LONG RAW So utilizados para armazenar dados binrios, que no devem ser interpretados pelo servidor Oracle. Para criarmos uma tabela, deveremos utilizar a declarao CREATE TABLE, unindo a ela o NOME que ser atribudo ao objeto e suas caractersticas. Vamos criar a tabela INFORMA_ESTOQUE.

CREATE TABLE INFORMA_ESTOQUE ( identificador number(5), Des_cidade char(50), val_venda char(50), Dtc_venda date)

Pag. 3

Curso Bsico de PL/SQL Voc pode impedir que seja gravado valores nulos na tabela para isso acrescente NOT NULL quando da criao da tabela.

CREATE TABLE INFORMA_ESTOQUE (identificador number(5) NOT NULL, Des_cidade char(50) NOT NULL, Val_venda number(10,2) NOT NULL, Dtc_venda date NOT NULL)Ns podemos fazer com que o identificador na tabela seja nico , proibindo que o mesmo identificador seja utilizado mais de uma vez para dois registros diferentes. Utilizaremos o identificador PRIMARY KEY , vejamos como:

ALTER TABLE informa_estoque ADD CONSTRAINT PK_IDENTIFICADOR PRIMARY KEY (identificador)Pronto, podemos agora inserir j alguns registros na nossa tabela. Para alterar o tamanho de qualquer campo , utilizaremos o comando MODIFY , veja abaixo:

ALTER TABLE informa_estoque MODIFY (Des_cidade varchar2(100))Agora vamos acrescentar uma coluna e depois elimin-la da tabela.

ALTER TABLE number (8,2))

informa_estoque ADD

(Num_compra

ALTER TABLE informa_estoque DROP (Num_compra)

Pag. 4

Curso Bsico de PL/SQL 1.1.2 INSERT O comando INSERT insere dados em uma tabela, vejamos:

INSERT INTO "table_name" ("column1", "column2", ...) VALUES ("value1", "value2", ...)Vamos inserir em nossa tabela informa_estoque

INSERT INTO informa_estoque (identificador, Des_cidade, Val_venda ,Dtc_venda ) VALUES (1,'Salvador', 900, '01/12/2007') INSERT INTO informa_estoque (identificador, Des_cidade, Val_venda, Dtc_venda ) VALUES (2,'Brasilia', 9000, '12/11/2007') INSERT INTO informa_estoque (identificador, Des_cidade, Val_venda, Dtc_venda ) VALUES (3,'Goiania', 3400, '15/03/2007') INSERT INTO informa_estoque (identificador, Des_cidade, Val_venda, Dtc_venda ) VALUES (4,'Sergipe', 5600, '20/02/2007') INSERT INTO informa_estoque (identificador, Des_cidade, Val_venda, Dtc_venda ) VALUES (5,'Sergipe', 7900, '10/02/2007')

Pag. 5


INSERT INTO informa_estoque (identificador, Des_cidade, Val_venda, Dtc_venda ) VALUES (6,'Brasilia', 9000.76, '12/11/2007')1.1.3 SELECT Atravs do comando select, recuperamos os dados existentes no banco, de acordo com os critrios desejados: - Operadores que podero ser utilizados: 1. Operadores para comparaes lgicas: = > >= 5000

Pag. 6


5) SELECT Des_cidade FROM informa_estoque WHERE Val_venda > 5000 and Val_venda < 6000 6) SELECT * FROM informa_estoque WHERE Val_venda > 5000 and Val_venda < 6000 and Des_cidade=Sergipe 7) SELECT * FROM informa_estoque WHERE Des_cidade like %g% 8) SELECT * FROM informa_estoque WHERE Des_cidade like S% 9) SELECT * FROM informa_estoque WHERE Des_cidade like %alva% 10) SELECT * FROM informa_estoque WHERE Des_cidade like '%_ia'Observaes: % representa nenhum ou muitos caracteres. _ representa um nico caracter.

11) SELECT * FROM informa_estoque WHERE Des_cidade IN ('Salvador', 'Sergipe') 12) SELECT * FROM informa_estoque WHERE dtc_venda BETWEEN '01/02/2007' and 01/07/2007'

Pag. 7


13) SELECT * FROM informa_estoque WHERE Val_venda > 5000 ORDER BY Val_venda ASC 14) SELECT * FROM informa_estoque WHERE Val_venda > 5000 ORDER BY Val_venda DESC 15) SELECT Identificador,Des_cidade,Val_venda FROM informa_estoque WHERE Val_venda > 5000 ORDER BY 2 DESC 16) SELECT SUM(val_venda) FROM informa_estoque 17) SELECT SUM(val_venda) FROM informa_estoque WHERE Val_venda > 5000 ORDER BY Val_venda ASC 18) SELECT COUNT(Des_cidade) FROM informa_estoque 19) SELECT COUNT( DISTINCT Des_cidade) FROM informa_estoque 20) SELECT Des_cidade,SUM(val_venda) FROM informa_estoque GROUP BY Des_cidade Order by Des_cidade

Pag. 8


21) SELECT Des_cidade,SUM(Val_venda) FROM informa_estoque GROUP BY Des_cidade HAVING SUM(Val_venda) > 5000 22) SELECT Identificador as Identifica,Des_cidade as Cidade,Val_venda as Valor FROM informa_estoque 23) SELECT estoque.Identificador Identifica,estoque.Des_cidade Cidade,estoque.Val_venda Valor FROM informa_estoque estoque 24) SELECT Des_cidade || ' -----' || to_char(Val_venda) FROM informa_estoque 25) SELECT Substr(Des_cidade,4,2) FROM informa_estoque 26) SELECT LTRIM(Des_cidade) FROM informa_estoque 27) SELECT RTRIM(Des_cidade) FROM informa_estoque 28) SELECT UPPER(Des_cidade) FROM informa_estoque 29) SELECT LOWER(Des_cidade) FROM informa_estoque 30) SELECT INITCAP(Des_cidade), LENGTH(Des_cidade) FROM informa_estoque 31) SELECT LPAD(Des_cidade,10,'*') FROM informa_estoque

Pag. 9


32) SELECT RPAD(Des_cidade,10,'*') FROM informa_estoque 33) SELECT SYSDATE FROM informa_estoque 34) SELECT INITCAP(Des_cidade) FROM informa_estoque 35) SELECT Round(Val_venda,1) FROM informa_estoque 36) SELECT Round(Val_venda,0) FROM informa_estoque 37) SELECT Trunc(Val_venda,1) FROM informa_estoque 38) SELECT Trunc(Val_venda,0) FROM informa_estoqueobs.: Utilizao da funo TO_CHAR. Como exibir uma data no formato DD/MM/YY. SELECT DES_NOME, TO_CHAR (DT_ADMISSAO, DD/MM/YY) ADMISSO FROM EMPREGADOS; Observaes: Por default, a largura de uma coluna que resulta de uma expresso de 80 posies. YYYY ou YYY ou YY ou Y Os ltimos 4, 3, 2 ou 1 dgitos do ano. MM O ms representado em 2 dgitos. MON O NOME do ms abreviado em 3 letras. DDD ou DD ou D Dia do ano, ms ou semana. DAY O NOME do dia por extenso, completado com brancos at 9 caracteres. MM/YY Apenas o ms e o ano so exibidos.

Pag. 10


39) SELECT TO_CHAR(Dtc_venda,DD/MM/YYYY) FROM informa_estoque 40) SELECT TO_CHAR(Dtc_venda,YYYY) FROM informa_estoque 41) SELECT TO_CHAR(Dtc_venda,MONTH) FROM informa_estoqueFunes de Grupo Existentes: AVG COUNT MAX MIN STDDEV SUM VARIANCE

Observaes: A clusula Distinct pode ser utilizada para que sejam considerados apenas valores no duplicatas.

42) SELECT AVG(Val_venda), MAX(Val_venda), MIN(Val_venda), SUM(Val_venda) FROM informa_estoqueObs.: A recuperao de dados com subconsultas (Subqueries)

Pag. 11

Curso Bsico de PL/SQL Subconsultas

43) SELECT Des_cidade,SUM(Val_venda) FROM informa_estoque WHERE Dtc_venda < (SELECT sysdate from dual) group by Des_cidade

Vamos criar agora uma tabela chamada REGIO para utilizarmos o comando JOIN, que faz a unia entre tabelas o chamado relacionamento, vejamos:

CREATE TABLE REGIAO (Des_regiao char(20) NOT NULL, Des_cidade char(50) NOT NULL)Vamos inserir os dados na tabela REGIAO

INSERT INTO REGIAO (Des_regiao, VALUES (Nordeste,Salvador') INSERT INTO REGIAO (Des_regiao, VALUES (Sul,Rio grande do sul') INSERT INTO REGIAO (Des_regiao, VALUES (Nordeste,Sergipe') INSERT INTO REGIAO (Des_regiao, VALUES (Centro-Oeste,Goiania')

Des_cidade) Des_cidade) Des_cidade) Des_cidade)

Pag. 12


INSERT INTO REGIAO (Des_regiao, VALUES (Sudeste,Sao Paulo') INSERT INTO REGIAO (Des_regiao, VALUES (Centro-Oeste,Brasilia')

Des_cidade) Des_cidade)

CONSTRAINTS. Utilizada para resringir entrada de dados em tabelas. Vamos criar algumas constraints:

ALTER TABLE REGIAO ADD (CONSTRAINT PK_DES_CIDADE PRIMARY KEY (DES_CIDADE)) ALTER TABLE INFORMA_ESTOQUE ADD (CONSTRAINT FK_DES_CIDADE FOREIGN KEY (DES_CIDADE) REFERENCES REGIAO (DES_CIDADE)) ALTER TABLE INFORMA_ESTOQUE ADD (CONSTRAINT CH_valor_venda CHECK (VAL_VENDA BETWEEN 0 AND 10000)) ALTER TABLE INFORMA_ESTOQUE DISABLE CONSTRAINT CH_valor_venda ALTER TABLE INFORMA_ESTOQUE ENABLE CONSTRAINT CH_valor_venda ALTER TABLE INFORMA_ESTOQUE DROP CONSTRAINT CH_valor_venda

Pag. 13


ALTER TABLE REGIAO ADD CONSTRAINT UK_valor_venda UNIQUE (Des_cidade) ALTER TABLE REGIAO DROP CONSTRAINT PK_DES_CIDADE (Note vai apresentar erro!!!)Agora , vamos voltar a utilizar nosso comando SQL para fazer o relacionamento entre as duas tabelas informa_estoque e regio. JOIN

SELECT REG.DES_regiao REGIAO, SUM(ESTOQUE.Val_venda) VENDAS FROM Regiao REG, Informa_estoque ESTOQUE WHERE RTRIM(ESTOQUE.Des_cidade) = RTRIM(REG.Des_cidade) GROUP BY REG.DES_regiaoOUTER JOIN

SELECT NVL(REG.DES_regiao,'OUTROS') REGIAO, SUM(ESTOQUE.Val_venda) VENDAS FROM Regiao REG, Informa_estoque ESTOQUE WHERE RTRIM(ESTOQUE.Des_cidade) = RTRIM(REG.Des_cidade (+)) GROUP BY REG.DES_regiaoLEFT JOIN

SELECT REG.DES_regiao REGIAO, SUM(ESTOQUE.Val_venda) VENDAS

Pag. 14


FROM Regiao REG, Informa_estoque ESTOQUE WHERE RTRIM(ESTOQUE.Des_cidade (+)) = RTRIM(REG.Des_cidade ) GROUP BY REG.DES_regiaoQuantificador Existencial

Exists representa o quantificador emprestada da lgica formal.

existencial,

uma

noo

OBS.: Vamos fazer um exemplo no exerccioUnio (Union e Union All)

SELECT Des_cidade FROM informa_estoque Where Des_cidade='Salvador' UNION SELECT Des_cidade FROM informa_estoque Where Des_cidade !='Salvador' SELECT Des_cidade FROM informa_estoque Where Des_cidade='Salvador' UNION ALL SELECT Des_cidade FROM informa_estoque Where Des_cidade !='Salvador'

1.1.4 TRUNCATE

Pag. 15

Curso Bsico de PL/SQL Este comando , apaga e recria a tabela sem nenhum dado, portanto caso voc deseje limpar totalmente a tabela , voc pode utilizar o comando TRUNCATE

TRUNCATE TABLE REGIAO

1.1.5 DROP Apaga definitivamente a tabela, portanto caso voc deseje eliminar a tabela , voc pode utilizar o comando DROP

DROP TABLE REGIAO

1.1.6 UPDATE

Utilizado para modificar dados j cadastrados. Pode ser usado para atualizar todas as linhas ou para atualizar linhas que correspondam a determinados critrios.

UPDATE INFORMA_ESTOQUE SET Des_cidade=BeloHorizonte WHERE Val_venda > 5000Comando ROLLBACK Caso voc deseje desfazer a atualizao, insero, deleo de registros Comando COMMIT Caso voc deseje confirmar a atualizao, insero, deleo de registros.

Pag. 16


1.1.7 DELETE Para apagar linhas de uma tabela.

DELETE INFORMA_ESTOQUE WHERE Val_venda > 50001.1.8 CRIANDO NDICES

Os ndices ajudam-nos recuperar mais rapidamente dados das tabelas. Sintaxe:

CREATE INDEX "INDEX_NAME" ON "TABLE_NAME" (COLUMN_NAME)

CREATE INDEX IDX_Des_cidade on INFORMA_ESTOQUE (Des_cidade) CREATE INDEX IDX_Des_cidade_data on INFORMA_ESTOQUE (Des_cidade,Dtc_venda) Tipos de ndices

B-Tree

nico No nico

ClusterizadoPag. 17


Index Cluster

Hash Cluster

Bitmap

Cuidado: Uma maior quantidade de ndices nem sempre aumenta o desempenho do banco.

1.1.9 ALTERANDO ESTRUTURA DA TABELA - adicione uma coluna - apague uma coluna - mude um NOME da coluna - mude o tipo de dados para uma coluna Adicionando campo:

ALTER table INFORMA_ESTOQUE add des_Gerente char(50)Alterando campo:

ALTER table INFORMA_ESTOQUE modify (des_Gerente varchar(70))Apagando campo:

ALTER table INFORMA_ESTOQUE drop des_Gerente

1.1.10

Criando Vises de tabelas

As vises podem ser consideradas como tabelas virtuais. Vamos criar uma viso da tabela informa_estoque

Pag. 18


CREATE VIEW Visao_soma_estoque (CIDADE,Soma_Vendas) AS SELECT Des_cidade Cidade,SUM(val_venda) Soma_vendas FROM informa_estoque GROUP BY Des_cidadeEXERCCIOS 1) Crie as seguintes tabelas: Empresa ide_empresa number(3) des_empresa char(30) des_endereco char(50) Regiao ide_regiao des_regiao des_estado Receita ide_receita ide_empresa ide_regiao val_receita number(3) number(3) number(3) number(10,2) number(3) char(10) char(50)

Pag. 19


1.1) Crie uma chave primaria para cada tabela. 1.2) Crie duas chaves estrangeiras para a tabela receita , relacionando as chaves ide_empresa e ide_regiao com as tabelas regiao e empresa. 2) Insira nas tabelas as seguintes informaes Empresa Ide_empresa 1 2 3 4 Regiao Ide_regiao 1 2 3 Receita Ide_receita 1 2 3 4 5 Ide_empresa 1 1 4 3 4 Ide_regiao 1 1 3 2 1 val_receita 1500 4500 3000 7000 9000 Des_regiao Nordeste Sul Sudoeste Des_estado Bahia Rio Grande do Sul Sao Paulo des_empresa Alimentos S.A. Sandalias S.A. Fernandes LTDA Sousa e Sousa LTDA des_endereco Rua da forca Rua das alcatias Rua das accias Rua da florida

3) Liste os dados nos seguintes casos: 3.1. todas as descries das empresas da tabela empresa 3.2. todas as regioes da tabela regio

Pag. 20

Curso Bsico de PL/SQL 3.3. todas os registros da tabela regio onde estado=Bahia 3.4. todas os registros da tabela Receita onde Receita=3000 3.5. todas os registros da tabela Receita onde Receita>3000 3.6. todas os registros da tabela Receita onde Receita LTDA Fernandes LTDA ---- > LTDA Sousa e Sousa LTDA ---- > LTDA

Pag. 21


Sites interessantes: http://www.techonthenet.com/oracle/index.php http://dbis.ucdavis.edu/courses/sqltutorial/tutorial.pdf http://www.java2s.com/Tutorial/Oracle/CatalogOracle.htm

Pag. 22

Documents

Apostila Basica PLSQL