Programação em Oracle - Apostilas - Informática Part1, Notas de estudo de Informática

Baixe Programação em Oracle - Apostilas - Informática Part1 e outras Notas de estudo em PDF para Informática, somente na Docsity! PROGRAMANDO EM ORACLE 9i PL/SQL Desenvolvida Por: Rondinele Santos de Morais 2 5 NLSSORT.....................................................................................................................58 TRANSLATE ...............................................................................................................58 UNISTR(s)....................................................................................................................58 Funções Numéricas...........................................................................................................58 ABS(x) ..........................................................................................................................58 ACOS(x) .......................................................................................................................58 ASIN(x) ........................................................................................................................58 ATAN(x).......................................................................................................................58 ATAN2 (x,y).................................................................................................................58 BITAND(x,y)................................................................................................................58 CEIL(x) .........................................................................................................................58 COS(x) ..........................................................................................................................58 COSH(x) .......................................................................................................................58 EXP(x) ..........................................................................................................................59 FLOOR(x).....................................................................................................................59 LN(x) ............................................................................................................................59 LOG (x,y)......................................................................................................................59 MOD(x,y) .....................................................................................................................59 POWER(x,y).................................................................................................................59 ROUND(x[,y]) ..............................................................................................................59 SIGN(x) ........................................................................................................................59 SIN(x) ...........................................................................................................................59 SINH(x) ........................................................................................................................59 SQRT(x)........................................................................................................................59 TAN(x)..........................................................................................................................59 TANH(x).......................................................................................................................59 TRUNC (x,[,y]).............................................................................................................59 WIDTH_BUCKET .......................................................................................................60 Funções de data e hora......................................................................................................60 ADD_MONTHS(d,x) ...................................................................................................60 CURRENT_DATE .......................................................................................................60 CURRENT_TIMESTAMP...........................................................................................61 DBTIMEZONE ............................................................................................................61 LAST_DAY..................................................................................................................61 LOCALTIMESTAMP ..................................................................................................61 MONTHS_BETWEEN ................................................................................................61 NEW_TIME (d, zona1,zona2)......................................................................................61 NEXTDAY ...................................................................................................................61 ROUND (d[,formato]) ..................................................................................................61 SESSIONTIMEZONE..................................................................................................61 SYS_EXTRACT_UTC.................................................................................................61 SYSDATE ....................................................................................................................61 SYSTIMETAMP ..........................................................................................................61 TRUNC (d,[,formato]) ..................................................................................................61 TZ_OFFSET(fuso horário) ...........................................................................................62 Funções de conversão .......................................................................................................62 ASCIISTR (string)........................................................................................................62 6 BIN_TO_NUM (num[,num]...) ....................................................................................62 CHARTOROWID ........................................................................................................62 COMPOSE....................................................................................................................62 DECOMPOSE ..............................................................................................................62 FROM_TZ (timestamp,fuso horário) ...........................................................................62 HEXTORAW................................................................................................................62 NUMTODSINTERVAL...............................................................................................62 NUMTOYMINTERVAL .............................................................................................62 REFTOHEX..................................................................................................................62 RAWTOHEX................................................................................................................62 RAWTONHEX.............................................................................................................62 ROWIDTOCHAR ........................................................................................................63 ROWIDTONCHAR......................................................................................................63 TO_CHAR (datas e horas)............................................................................................63 TO_CHAR (Números)..................................................................................................63 TO_DATE ....................................................................................................................64 TO_NUMBER ..............................................................................................................64 TO_TIMESTAMP e TO_TIMESTAMP_TZ...............................................................64 Funções de grupo ..............................................................................................................65 AVG ([DISTINCT|ALL] col).......................................................................................65 COUNT (*|[DISTINCT|ALL]col) ................................................................................65 GROUP_ID() ................................................................................................................65 GROUPING..................................................................................................................65 GROUPING_ID............................................................................................................65 MAX([DISTINCT|ALL]col) ........................................................................................65 MIN([DISTINCT|ALL]col)..........................................................................................65 STDDEV ([DISTINCT|ALL]col).................................................................................65 SUM([DISTINCT|ALL]col).........................................................................................66 Outras funções ..................................................................................................................66 BFILENAME (diretório nome_de_arquivo) ................................................................66 COALESCE..................................................................................................................66 DECODE (expressão_de_base, comparação1,valor1,comparação2,valor2...padrão) ..66 EMPTY_BLOB/EMPTY_CLOB.................................................................................66 GREATEST (expr1 [,expr2])... ....................................................................................66 LEAST (expr1 [,expr2])… ...........................................................................................66 NULLIF (a,b)................................................................................................................66 NVL (expr1,expr2) .......................................................................................................66 NVL2 (expr1,expr2,expr3) ...........................................................................................66 SYS_CONNECT_BY_PATH ......................................................................................66 SYS_CONTEXT ..........................................................................................................66 SYS_GUID ...................................................................................................................67 SYS_TYPEID (tipo_de_objecto) .................................................................................67 TREAT (expr AS [REF] [esquema.]tipo )....................................................................67 UID ...............................................................................................................................67 USER ............................................................................................................................67 DUMP...........................................................................................................................67 CURSORES..........................................................................................................................68 7 Cursores explícitos............................................................................................................69 Parâmetros de cursor.........................................................................................................70 Atributos de cursor............................................................................................................71 Cursores implícitos ...........................................................................................................71 Loops de Busca de Cursores .............................................................................................72 Loop Simples ................................................................................................................73 Loops WHILE...............................................................................................................73 Loops FOR de cursor ....................................................................................................74 Loops FOR implícitos...................................................................................................75 NO_DATA_FOUND versus %NOTFOUND ..................................................................75 Cursores SELECT FOR UPDATE ...................................................................................76 FOR UPDATE..............................................................................................................76 WHERE CURRENT OF ..............................................................................................76 COMMIT dentro de um Loop de cursor FOR UPDATE .............................................77 Variáveis de cursor ...........................................................................................................78 COLEÇÕES..........................................................................................................................81 Tabelas Index-by...............................................................................................................81 Elementos inexistentes..................................................................................................81 Tabelas index-by de tipos compostos ...........................................................................81 Tabelas index-by de registros .......................................................................................82 Tabelas index-by de tipos de objeto..............................................................................82 Tabelas aninhadas (Nested tables) ....................................................................................82 Inicialização de uma tabela aninhada ...........................................................................83 Tabelas vazias ...............................................................................................................84 Adicionando elementos a uma tabela existente ............................................................85 VARRAYS .......................................................................................................................85 Inicialização de varray ..................................................................................................86 Manipulando os elementos de um varray .....................................................................86 Coleções de múltiplos níveis ............................................................................................87 Coleções no banco de dados .............................................................................................88 A estrutura de varrays armazenados .............................................................................88 Estrutura das tabelas aninhadas armazenadas...............................................................89 Manipulando coleções inteiras..........................................................................................89 INSERT ........................................................................................................................89 UPDATE.......................................................................................................................90 DELETE .......................................................................................................................91 SELECT........................................................................................................................91 Operadores de tabela SQL ................................................................................................92 Métodos de coleção ..........................................................................................................92 EXISTS.........................................................................................................................93 COUNT.........................................................................................................................93 LIMIT ...........................................................................................................................94 FIRST e LAST..............................................................................................................95 NEXT e PRIOR ............................................................................................................95 EXTEND ......................................................................................................................96 TRIM ............................................................................................................................97 DELETE .......................................................................................................................98 10 UTL_SMTP ................................................................................................................179 UTL_HTTP.................................................................................................................179 UTL_INADDR ...........................................................................................................180 DBMS_JOB ................................................................................................................180 DBMS_LOB ...............................................................................................................181 11 INTRUDUÇÃO À LINGUAGEM PL/SQL PL/SQL é uma linguagem de programação sofisticada utilizada para acessar um banco de dados Oracle a partir de vários ambientes. ElAé integrada com o serviror do banco de dados de modo que o código PL/SQL possa ser processado de maneira rápida e eficiente. Essa linguagem também está disponível em algumas ferramentas Oracle do lado do cliente. Em linhas gerais, a PL/SQL (Procedural Language/SQL) combina o poder e flexibilidade da SQL com as construções procedurais de uma linguagem de 3ª geração. Interagindo com o usuário A PL/SQL não tem nenhuma funcionalidade de entrada ou de saída construída diretamente na linguagem. Para retificar isso, o SQL*Plus, em combinação com o pacote DBMS_OUTPUT, fornece a capacidade de dar saída para mensagens em tela. Isso é feito em dois passos: 1. Permitir a saída no SQL*Plus com o comando set serveroutput on : SET SERVEROUTPUT {ON | OFF} [SIZE n] Onde o n é o tamanho do buffer de saída. Seu valor padrão é 2.000 bytes 2. Dentro do seu programa PL/SQL, utilize a procedure DBMS_OUTPUT.PUT_LINE(msg). Exemplo: SQL> SET SERVEROUTPUT ON SQL> begin 2 DBMS_OUTPUT.PUT_LINE('Teste de pl/sql!!!'); 3 end; 4 / Teste de pl/sql!!! PL/SQL procedure successfully completed. PL/SQL e tráfego de rede No modelo cliente/servidor, o próprio programa reside na máquina cliente e envia solicitações de informações para um servidor de banco de dados. As solicitações são feitas utilizando SQL. Em geral, isso resulta em várias viagens pele rede, uma para cada instrução SQL, diferente do uso da PL/SQL que pode estar armazenada no banco de dados ou mesmo permitir que vários comandos SQL sejam empacotados em bloco PL/SQL e enviados ao servidor como uma única unidade. 12 Estrutura do bloco PL/SQL A unidade básica em um programa PL/SQL é um bloco. Todos os programas da PL/SQL são compostos por blocos, que podem ser aninhados dentro do outro. Em geral , cada bloco realiza uma unidade lógica de trabalho no programa, assim separando um do outro diferente tarefas. Um bloco tem a seguinte estrutura: DECLARE /* Seção declarativa – variáveis, tipos, cursores e subprogramas locais */ BEGIN /* Seção executável - instruções SQL e procedurais entram aqui. Essa é a principal sessão do bloco PL/SQL, e é a única obrigatória. */ EXCEPTION /* Seção de tratamento de exceções – instruções de tratamento de erros entram aqui. */ END; Tipos de blocos PL/SQL: Blocos anônimos: construídos dinámicamente e ejecutados apenas umas vez. Podem ser rotulados ou não. Blocos rotulados são geralmente utilizados da mesma maneira que os blocos anônimos, mas os rotulados permitem referenciar variáveis que de outro modo não seriam visíveis. Subprogramas: consistem em procedures e funções. Podem ser armazenados no banco de dados como objetos independentes, como parte de um pacote ou como métodos de um tipo de objeto. Triggers: consistem em um bloco PL/SQL que está associado a um evento que ocorre no banco de dados. Exemplos: REM BLOCO ANONIMO NÃO ROTULADO DECLARE /* Declare variables to be used in this block. */ v_Num1 NUMBER := 1; v_Num2 NUMBER := 2; v_String1 VARCHAR2(50) := 'Hello World!'; v_String2 VARCHAR2(50) := '-- This message brought to you by PL/SQL!'; v_OutputStr VARCHAR2(50); BEGIN /* First, insert two rows into temp_table, using the values of the variables. */ INSERT INTO temp_table (num_col, char_col) 15 FROM temp_table WHERE num_col = v_Num2; DBMS_OUTPUT.PUT_LINE(v_OutputStr); /* Rollback our changes */ ROLLBACK; END InsertIntoTemp; / REM BLOCO ANONIMO PARA CHAMAR A PROCEDURE BEGIN InsertIntoTemp; END; / Blocos aninhados Um bloco pode ser aninhado dentro da seção executável ou de exceção de um bloco externo, como no exemplo abaixo: DECLARE /* Start of declarative section */ v_StudentID NUMBER(5) := 10000; -- Numeric variable initialized -- to 10,000 v_FirstName VARCHAR2(20); -- Variable length character string -- with maximum length of 20 BEGIN /* Start of executable section */ -- Retrieve first name of student with ID 10,000 SELECT first_name INTO v_FirstName FROM students WHERE id = v_StudentID; -- Start of a nested block, which contains only an executable -- section BEGIN INSERT INTO log_table (info) VALUES ('Hello from a nested block!'); END; EXCEPTION /* Start of exception section */ WHEN NO_DATA_FOUND THEN 16 -- Start of a nested block, which itself contains an executable -- and exception section BEGIN -- Handle the error condition INSERT INTO log_table (info) VALUES ('Student 10,000 does not exist!'); EXCEPTION WHEN OTHERS THEN -- Something went wrong with the INSERT DBMS_OUTPUT.PUT_LINE('Error inserting into log_table!'); END; END; / Identificadores Os identificadores são usados para nomear objetos da PL/SQL como variáveis, cursores, subprogramas, etc. Como regra geral, os identificadores: Consistem em uma letra, opcionalmente seguida por qualquer seqüência de caracteres incluindo números e ($), (#) e (_). Devem possuir comprimento máximo de 30 caracteres Não há distinção entre letras maiúscula e minúscula Não pode possuir nome igual uma palavra reservada, ex: BEGIN, END Podem ser identificados com aspas para possuírem espaços e distinção entre letras maiúsculas e minúsculas. EX: “X / Y”, “variavel A” Literais São valores constantes, podendo ser Caracter : (‘Teste literal’), Numérico: (132, - 44), ou Booleanos: (TRUE, FALSE, NULL) Comentários Melhoram a legibilidade e tornam os programas mais compreensíveis. Comentários de uma única linha, através de dois traços “--": v_data := SYSDATE; --variável recebe data atual Comentários de múltiplas linha, usando “/*” para inicar e “*/” para fechar: BEGIN /* Estamos agora dentro de um comentário. Aqui é a continuação do comentário. */ NULL; END; 17 Declaração de variável Nome_da_variável [CONSTANT] datatype [NOT NULL] [{:= | DEFAULT} valor]; Onde nome_da_variável é o identificador, datatype é tipo e valor é o conteúdo inicial da variável. EX: DECLARE v_dataInicial DATE; v_contador BINARY_INTEGER NOT NULL := 0; v_nome VARCHAR2(20); c_PI CONSTANT NUMBER DEFAULT 3.14; Tipos PL/SQL O Oracle PL/SQL possui diferentes tipos de dados (datatypes) para atender suas necessidades, que são divididos nas seguintes categorias: CHARACTER, NUMBER, DATE, LOB, BOOLEANOS, TIPOS COMPOSTOS, TIPOS DE OBJETO e TIPOS DE REFERÊNCIA. Character Datatype Usados para armazenar dados alfanuméricos. CHAR(<n>) armazena string de tamanho fixo. Tamanho default 1, máximo 32.767. Subtipo: CHARACTER VARCHAR2(<n>) armazena string de tamanho variável. É possível armazenar string de até 32.767 bytes. Subtipo: STRING VARCHAR(<n>) sinônimo para o tipo VARCHAR2. NCHAR(<n>) e NVARCHAR2(<n>) possuem as mesmas características dos tipos CHAR e VARCHAR2 e são usados para armazenar dados NLS (National Language Support). A arquitetura Oracle NLS permite armazenar, processar e recuperar informações em linguagens nativas. LONG é um tipo de dados que se tornou “obsoleto” com a chegada dos tipos LOB (Large Object). O tipo LONG armazena strings de tamanho variável de no máximo 32.760 bytes. Numeric Datatype Usado para armazenar dados numéricos com precisão de até 38 digitos. NUMBER(<x>, <y>) onde <X> corresponde ao número de dígitos e <Y> o número de casas decimais. Valores inseridos em colunas numéricas com número de casas decimais menor que o dado inserido serão arredondados. Subtipos: DEC, DECIMAL, DOUBLE PRECISION, FLOAT, INTEGER, INT, NUMERIC, REAL, SMALLINT. 20 DECLARE v_Nome STUDENTS.FIRST_NAME%TYPE; v_Idade PLS_INTEGER NOT NULL :=0; v_IdadeTemp v_Idade%TYPE; --não herda restrição nem valor default Subtipos definidos pelo usuário Utilizado para fornecer um tipo alternativo para um tipo, descrevendo sua utilização pretendida. Sintaxe: SUBTYPE novo_tipo IS tipo_original; Exemplo: DECLARE SUBTYPE T_Contador IS NUMBER(3); v_Contador T_Contador; SUBTYPE T_Nome IS STUDENTS.FIRST_NAME%TYPE; Convertendo entre tipos de dados Quando possível, a PL/SQL converterá automaticamente (conversão implícita) tipos dentro da mesma família e entre as famílias de tipos de dados: Caracteres e números Caracteres e datas Funções para conversão explícita de tipos de dados: Função Descrição TO_CHAR Converte seu argumento em um tipo VARCHAR2 TO_DATE Converte seu argumento em um tipo DATE TO_TIMESTAMP Converte seu argumento em um tipo TIMESTAMP TO_TIMESTAMP_TZ Converte seu argumento em um tipo TIMESTAMP WITH TIMEZONE TO_DSINTERVAL Converte seu argumento em um tipo INTERVAL DAY TO SECOND TO_YMINTERVAL Converte seu argumento em um tipo INTERVAL YEAR TO MONTH TO_NUMBER Converte seu argumento em um tipo NUMBER HEXTORAW Converte uma representação hexadecimal na quantidade binária equivalente 21 Função Descrição RAWTOHEX Converte um valor RAW em uma representação hexadecimal da quantidade binária CHARTOROWID Converte uma representação de caractere de um ROWID em formato binário interno ROWIDTOCHAR Converte uma variável binária interna de ROWID em formato externo de 18 caracteres Escopo de variável e visibilidade O escopo de uma variável é a parte do programa onde a variável pode ser acessada antes de ser liberada da memória. A visibilidade de uma variável é a parte do programa onde a variável pode ser acessada sem ter de qualificar a referência. <<bloco1>> DECLARE v_flag BOOLEAN; v_SSN NUMBER(9); BEGIN /* Aquí v_flag e v_SSN estão visíveis */ DECLARE v_Data DATE; v_SSN CHAR(11); BEGIN /* Aquí v_flag, v_Data e v_SSN CHAR(11) estão visíveis. Para acessar v_SSN NUMBER(9) é necessário informar o bloco a que pertence: bloco1.v_SSN */ END; /* Aquí v_flag e v_SSN estão visíveis */ END; Operadores O operador mais básico na PL/SQL é o de atribuição. A sintaxe é: Variável := valor; Veja a tabela abaixo com os operadores PL/SQL: 22 Operador Tipo Descrição **, NOT Binário Exponenciação, negação lógica +,- Unário Identidade, negação *,/ Binário Multiplicação, divisão +,-,|| Binário Adição, subtração, concatenação =,!=,<,>,<=,>=, IS NULL, LIKE, BETWEEN, IN Binário (exceto IS NULL que é unário) Comparação lógica AND Binário Conjunção lógica OR Binário Inclusão lógica Expressões booleanas Todas as estruturas de controle PL/SQL envolvem expressões booleanas, também conhecidas como condições. Uma expressão booleana é qualquer expressão que é avaliada como um valor booleano (TRUE, FALSE ou NULL). Três operadores (AND, OR, NOT) recebem argumentos booleanos e retornam valores booleanos. Seus comportamentos são descritos na tabela abaixo: NOT TRUE FALSE NULL FALSE TRUE NULL AND TRUE FALSE NULL TRUE FALSE FALSE NULL FALSE FALSE FALSE FALSE NULL NULL FALSE NULL OR TRUE FALSE NULL TRUE TRUE TRUE TRUE FALSE TRUE FALSE NULL NULL TRUE NULL NULL Estruturas de controle PL/SQL Permitem controlar o comportamento do bloco à medida que ele está sendo executado. IF-THEN- ELSE DECLARE v_NumberSeats rooms.number_seats%TYPE; v_Comment VARCHAR2(35); BEGIN SELECT number_seats INTO v_NumberSeats FROM rooms WHERE room_id = 20008; 25 -- insure that it is still less than 50. WHILE v_Counter <= 50 LOOP INSERT INTO temp_table VALUES (v_Counter, 'Loop index'); v_Counter := v_Counter + 1; END LOOP; END; / Se desejado, as instruções EXIT ou EXIT WHEN podem ainda ser utilizadas dentro de um loop WHILE para sair prematuramente do loop. Tenha em mente que se a condição de loop não for avaliada como TRUE na primeira vez que ela for verificada, o loop não será executado. Exemplo: DECLARE v_Counter BINARY_INTEGER; BEGIN -- This condition will evaluate to NULL, since v_Counter -- is initialized to NULL by default. WHILE v_Counter <= 50 LOOP INSERT INTO temp_table VALUES (v_Counter, 'Loop index'); v_Counter := v_Counter + 1; END LOOP; END; / Loops FOR numéricos Loop que possui um número definido de iterações. Sintaxe: FOR contador IN [REVERSE] limite_inferior .. limite_superior LOOP Seqüência_de_instruções; END LOOP; Exemplo: BEGIN --não é necessário declarer a variável v_counter --ela será automaticamente declarada como BINARY_INTERGER FOR v_Counter IN 1..50 LOOP INSERT INTO temp_table VALUES (v_Counter, 'Loop Index'); END LOOP; END; 26 Se a palavra REVERSE estiver presente no loop FOR, o índice de loop irá iterar a partir do valor alto para o valor baixo. Exemplo: DECLARE v_Baixo NUMBER := 10; v_Alto NUMBER := 40; BEGIN FOR v_Counter in REVERSE v_Baixo .. v_Alto LOOP INSERT INTO temp_table VALUES(v_Counter, ‘Teste’); END LOOP; END; / GOTOs e rótulos A linguagem PL/SQL também possui uma instrução GOTO para passar o controle para uma área especifica do bloco. A PL/SQL não permite utilizar GOTO para desviar o controle para um bloco interno, para dentro de uma condição IF ou sair da seção de exceção do bloco para uma seção executável. Exemplo: DECLARE v_Counter BINARY_INTEGER := 1; BEGIN LOOP INSERT INTO temp_table VALUES (v_Counter, 'Loop count'); v_Counter := v_Counter + 1; IF v_Counter >= 50 THEN GOTO l_EndOfLoop; END IF; END LOOP; <<l_EndOfLoop>> INSERT INTO temp_table (char_col) VALUES ('Done!'); END; / Rotulando LOOPs Os próprios Loops podem ser rotulados. Podemos rotular um loop e utilizá-los em uma instrução EXIT para indicar qual loop deve ser terminado. Exemplo: 27 BEGIN <<loop_pai>> FOR v_Pai IN 1..50 LOOP ... <<loop_filho>> FOR v_filho IN 2..10 LOOP ... if v_Pai > 40 then EXIT loop_pai; --termina ambos os loops end if; END LOOP loop_filho; END LOOP loop_pai; END; NULO como uma instrução Emm alguns casos, você talvez queira indicar explicitamente que nenhuma ação deve acontecer. Isso pode ser feito via a instrução NULL. Exemplo: DECLARE v_TempVar NUMBER := 7; BEGIN IF v_TempVar < 5 THEN INSERT INTO temp_table (char_col) VALUES ('Too small'); ELSIF v_TempVar < 20 THEN NULL; -- Do nothing ELSE INSERT INTO temp_table (char_col) VALUES ('Too big'); END IF; END; / Registros PL/SQL Os registros PL/SQL são semelhantes a estruturas C. Um registro fornece uma maneira de lidar com variáveis separadas, mas relacionadas como uma unidade. Sintaxe: TYPE nome_tipo IS RECORD(campo1 tipo1 [NOT NULL] [:= valor], campo2 tipo2 [NOT NULL] [:= valor], ...); Exemplo: DECLARE TYPE t_Rec1Type IS RECORD ( Field1 NUMBER, 30 SQL DENTRO DA LINGUAGEM PL/SQL A linguagem SQL (Structure Query Language) define como os dados do Oracle são manipulados. As construções procedurais com PL/SQL tornam-se mais úteis quando combinadas com o poder de processamento da SQL, permitindo que os programas manipulem os dados no Oracle. As únicas instruções SQL permitidas diretamente em um programa PL/SQL são DMLs (SELECT, INSERT, UPDATE, DELETE) instruções de controle de transação (COMMIT, ROLLBACK, SAVEPOINT...). Select Recupera os dados do banco de dados para as variáveis PL/SQL. Exemplo: DECLARE v_StudentRecord students%ROWTYPE; v_Department classes.department%TYPE; v_Course classes.course%TYPE; BEGIN SELECT * INTO v_StudentRecord FROM students WHERE id = 10000; SELECT department, course INTO v_Department, v_Course FROM classes WHERE room_id = 20003; END; / Insert Insere novas linhas na tabela a partir de variáveis, registros, subquerys, etc. Exemplo: DECLARE v_StudentID students.id%TYPE; BEGIN SELECT student_sequence.NEXTVAL INTO v_StudentID FROM dual; INSERT INTO students (id, first_name, last_name) 31 VALUES (v_StudentID, 'Timothy', 'Taller'); INSERT INTO students (id, first_name, last_name) VALUES (student_sequence.NEXTVAL, 'Patrick', 'Poll'); END; / Update Atualiza colunas das tabelas a partir de variáveis, subquerys, registros, etc. Exemplo: DECLARE v_Major students.major%TYPE; v_CreditIncrease NUMBER := 3; BEGIN v_Major := 'History'; UPDATE students SET current_credits = current_credits + v_CreditIncrease WHERE major = v_Major; UPDATE temp_table SET num_col = 1, char_col = 'abcd'; END; / Delete Remove linhas de uma tabela do banco de dados. Exemplo: DECLARE v_StudentCutoff NUMBER; BEGIN v_StudentCutoff := 10; DELETE FROM classes WHERE current_students < v_StudentCutoff; DELETE FROM students WHERE current_credits = 0 AND major = 'Economics'; END; / 32 A cláusula RETURNING Utilizada para obter as informações sobre a linha ou linhas que acabaram de ser processadas por um comando DML, como por exemplo conhecer a ROWID da linha que acabou de ser incluída sem a necessidade de submeter um comando SELECT para o banco de dados. Exemplo: set serveroutput on DECLARE v_NewRowid ROWID; v_FirstName students.first_name%TYPE; v_LastName students.last_name%TYPE; v_ID students.ID%TYPE; BEGIN INSERT INTO students (ID, first_name, last_name, major, current_credits) VALUES (student_sequence.NEXTVAL, 'Xavier', 'Xemes', 'Nutrition', 0) RETURNING rowid INTO v_NewRowid; DBMS_OUTPUT.PUT_LINE('Newly inserted rowid is ' || v_NewRowid); UPDATE students SET current_credits = current_credits + 3 WHERE rowid = v_NewRowid RETURNING first_name, last_name INTO v_FirstName, v_LastName; DBMS_OUTPUT.PUT_LINE('Name: ' || v_FirstName || ' ' || v_LastName); DELETE FROM students WHERE rowid = v_NewRowid RETURNING ID INTO v_ID; DBMS_OUTPUT.PUT_LINE('ID of new row was ' || v_ID); END; / 35 Principais privilégios de objeto SQL Privilégio Tipos de objetos Descrição ALTER Tabelas, sequences Permite ALTER TABLE no objeto DELETE Tabelas, views Permite DELETE contra o objeto EXECUTE Procedures, funções e packages Permite executar um objeto PL/SQL armazenado INDEX Tabelas Permite criar índice na tabela especificada INSERT Tabelas, views Permite inserir linhas no objeto READ Diretórios Permite ler a partir do diretório especificado REFERENCES Tabelas Permite criar FKs para referenciar a tabela SELECT Tabelas, views, sequences Permite selecionar dados do objeto UPDATE Tabelas, views Permite atualizar linhas do objeto Você poderá conhecer todos os privilégios de objeto através da tabela de sistema TABLE_PRIVILEGE_MAP e os privilégios de sistema através da tabela SYSTEM_PRIVILEGE_MAP. Grant A instrução GRANT é utilizada para permitir algum privilégio para determinado usuário. A sintaxe é a seguinte para permissões de objeto: GRANT privilégio ON objeto TO usuário/role [WITH GRANT OPTION]; Já para permissões de sistema: GRANT privilégio TO usuário/role [WITH ADMIN OPTION]; Exemplo: CONNECT ALUNO3/SENHA3@ORACURSO; GRANT UPDATE, DELETE ON classes TO aluno2; GRANT ALL ON empregado TO scott; GRANT SELECT ON classes TO aluno8 WITH GRANT OPTION; CONNECT ALUNO8/SENHA8@ORACURSO; GRANT SELECT ON aluno3.classes TO aluno9; 36 Como WITH GRANT OPTION foi especificada, então aluno8 poderá repassar o privilégio para outro usuário. Caso aluno8 perca o privilégio, aluno9 também o perderá. Isso não vale para WITH ADMIN OPTION, pois o privilégio continuará concedido. Revoke A instrução REVOKE revoga um privilégio concedido a um usuário. Sintaxe: REVOKE privilégio ON objeto FROM usuário/role [CASCADE CONSTRAINTS]; Exemplo: REVOKE SELECT ON classes FROM aluno8; Se a cláusula CASCADE CONSTRAINTS foi incluída e o privilégio REFERENCES estiver sendo revogado, todas as restrições de integridade referencial criadas pelo usuário que detinha o privilégio serão eliminadas. Roles Permite facilitar a administração dos privilégios. Consiste em uma coleção de privilégios, tanto de objetos quanto de sistema. Exemplo: CREATE ROLE pesquisa_tabelas; GRANT SELECT ON students TO pesquisa_tabelas; GRANT SELECT ON classes TO pesquisa_tabelas; GRANT SELECT ON rooms TO pesquisa_tabelas; GRANT pesquisa_tabelas TO aluno7; GRANT pesquisa_tabelas TO PUBLIC; A role PUBLIC é internamente definida pelo Oracle e é concedida automaticamente para todos os usuários. Portanto, ao conceder algum privilégio para PUBLIC você estará concedendo para todos os usuários do seu banco de dados de uma vez. As roles mais comuns predefinidas pelo Oracle são: CONNECT, RESOURCE, DBA, EXP_FULL_DATABASE e IMP_FULL_DATABASE. 37 TRATAMENTO DE ERROS A PL/SQL implementa tratamento de erro por meio de exceções e handlers de exceção. As exceções podem estar associadas com erros do Oracle ou com os próprios erros definidos pelo usuário. Neste capítulo, discutiremos a sintaxe para exceções e handlers de exceção, como as exceções são levantadas e tratadas, e as regras de propagação de exceção. O capitulo termina com as diretrizes sobre a utilização das exceções. O que é uma exceção A PL/SQL está baseada na linguagem Ada. Um dos recursos Ada, que também foi incorporado na PL/SQL, é o mecanismo de exceção. Utilizando as exceções e handlers de exceção, você pode tornar seus programas PL/SQL mais poderosos e capazes de lidar durante a execução tanto com erros esperados como inesperados. As exceções PL/SQL também são semelhantes a exceções em Java. Por exemplo, exceções em Java são lançadas e capturadas da mesma maneira como na PL/SQL. Entretanto, diferente do que ocorre em Java, as exceções PL/SQL não são objetos e não têm nenhum método para defini-las. As exceções são projetadas para tratamento de erros em tempo de execução, em vez te tratamento de erros na compilação. Os erros que ocorrem durante a fase de compilação são detectados pelo mecanismo PL/SQL e informados ao usuário. O programa não pode tratar esses erros, pelo fato de o programa ainda não ter sido executado. Por exemplo, o seguinte bloco levantará o erro de compilação: PLS-201: Identifier ‘SSTUDENTS’ must be declared DECLARE v_NumStudents NUMBER; BEGIN SELECT count(*) INTO v_NumStudents FROM students; END; Tipos de erros PL/SQL Tipo de Erro Informado pelo Como é tratado Na compilação Compilador PL/SQL Interativamente – o compilador informa os erros e você tem de corrigi-los. Em tempo de execução Mecanismo de Execução da PL/SQL Programaticamente - as exceções são levantadas e capturadas pelos handlers de exceção. 40 Observe que o escopo de uma exceção é o mesmo que o escopo de qualquer outra variável ou cursor na mesma seção declarativa. Exceções predefinidas O Oracle tem várias exceções predefinidas que correspondem aos erros mais comuns do Oracle. Como os tipos predefinidos (NUMBER, VARCHAR2 e outros), os identificadores dessas exceções são definidas no pacote STANDART. Por causa disso, eles já estão disponíveis no programa, não sendo necessário declará-los na seção declarativa. NOTA Também é possível associar as exceções definidas pelo usuário com os erros do Oracle. Consulte a seção “O pragma EXCEPTION_INIT”, mais adiante neste capitulo para obter informações adcionais.. Exceções predefinidas pelo Oracle Erro do Oracle Exceção Equivalente Descrição ORA-0001 DUP_VAL_ON_INDEX Uma única restrição violada. ORA-0051 TIMEOUT_ON_RESOURCE O tempo limite ocorreu ao esperar pelo recurso. ORA-0061 TRANSACTION_BACKED_OUT A transação foi revertida devido a um impasse. ORA-1001 INVALID CURSOR Operação ilegal de cursor. ORA-1012 NOT_LOGGED_ON Não conectado ao Oracle. ORA-1017 LOGIN_DENIED Nome usuário/senha invalida. ORA-1403 NO_DATA_FOUND Nenhum dado localizado. ORA-1410 SYS_INVALID_ROWID Conversão para um ROWID universal falhou. ORA-1422 TOO_MANY_ROWS Uma instrução SELECT….INTO corresponde a mais de uma linha. ORA-1476 ZERO_DIVIDE Divisão por zero. ORA-1722 INAVLID_NUMBER Conversão para um número falhou – por exemplo. “IA” não e valido. ORA-6500 STORAGE_ERROR Erro interno PL/SQL é levantado se a PL/SQL ficar na memória. ORA-6501 PROGRAM_ERROR Erro interno PL/SQL. ORA-6502 VALUE_ERROR Erro de truncamento, aritmética ou de conversão. ORA-6504 ROQTYPE_MISMATCH Variável do cursor do host e variável de cursor PL/SQL que tem tipos de linhas incompatíveis; ORA-6511 CURSOR_ALREADY_OPEN Tentavida de abrir um cursor ja aberto. 41 ORA-6530 ACESS_INTO_NULL Tentativa para atribuir valores para atributos de um objeto null. ORA-6531 COLLECTION_IS_NULL Tentativa para aplicar metodos de coleções diferentes de EXISTS em uma tabela ou varray NULL PL/SQL. ORA-6532 SUBSCRIPT_OUTSIDE_LIMIT Relatório de uma tabela animada ou um índice varray fora do intervalo ( como – I ). ORA-6533 SUBSCRIPT_BEYOND_COUNT Referência a uma tabela aninhada ou um índice varray maior que o número de elementos da coleção. ORA-6592 CASE_NOT_FOUND Nenhuma correspondencia com uma clausula WHEN quando uma instrução CASE é localizada. ORA-30625 SELF_IS_NULL2 Tentativa para chamar um método em uma instancia NULL de Objeto. Abaixo algumas descrições de exceções predefinidas. INVALID_CURSOR Esse erro e levantado quando é realizada uma operação ilegal de cursor como ao tentar fechar um cursor que já esta fechado, uma situação igual ao tentar abrir um cursor que já esta aberto CURSOR_ALREADY_OPEN. NO_DATA_FOUND Essa exceção pode ser levantada em duas diferentes situações. A primeira é quando uma instrução SELECT... INTO não retorna nenhuma linha. Se a instrução retornar mais de uma linha, TOO_MANY_ROWS é levantada. A segunda situação é uma tentativa de referenciar um elemento “index-by table” da PL/SQL ao qual não foi atribuído um valor. Por exemplo, o seguinte bloco anônimo levantará NO_DATA_FOUND: DECLARE TYPE t_NumberTableType IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; v_NumberTable t_NumberTableType; v_TempVar NUMBER; BEGIN v_TempVar := v_NumberTable(1); END; DECLARE * ERROR at line 1: ORA-01403: no data found ORA-06512: at line 7 42 VALUE_ERROR Essa exceção é levantada quando um erro de aritmética, conversão, truncamento, ou de restrição ocorrer em uma instrução procedural. Se o erro ocorrer em uma instrução SQL, um erro como INVALID_NUMBER é levantado. O Erro pode ocorrer como resultado de uma instrução de atribuição, uma instrução SELECT... INTO, parâmetros RETURNING INTO de uma instrução SQL ou parâmetros de um subprograma. Todas essas situações são resultados de uma valor atribuído para uma variável PL/SQL. Se houver um problema com essa atribuição, VALUE_ERROR e levantado. DECLARE v_TempVar VARCHAR2(3); BEGIN v_TempVar := 'ABCD'; END; DECLARE * ERROR at line 1: ORA-06502:PL/SQL:numeric or value error: character string buffer too small ORA-06512: at line 4 ========= DECLARE v_TempVar NUMBER(2); BEGIN SELECT id INTO v_TempVar FROM students WHERE last_name = 'Smith'; END; DECLARE * ERROR at line 1: ORA-06502:PL/SQL: numeric or value error: number precision too large ORA-06512: at line 4 ROWTYPE_MISMATCH Essa exceção é levantada quando os tipos de uma variável de cursor de host e de uma variável de cursor PL/SQL não correspondem. Por exemplo, se os tipos de retornos reais e formais não correspondem a um procedimento que recebe uma variável de cursor como um argumento, ROWTYPE_MISMATCH é levantado. 45 WHERE department = 'HIS' AND course = 101; /* Check the number of students in this class. */ IF v_CurrentStudents > v_MaxStudents THEN /* Too many students registered -- raise exception. */ RAISE e_TooManyStudents; END IF; EXCEPTION WHEN e_TooManyStudents THEN /* Handler which executes when there are too many students registered for HIS-101. We will insert a log message explaining what has happened. */ INSERT INTO log_table (info) VALUES ('History 101 has ' || v_CurrentStudents || 'students: max allowed is ' || v_MaxStudents); END; Um único handler também pode ser executado em mais de uma exceção. Simplesmente liste os nomes de exceção na claúsula WHEN separada pela palavra-chave OR: EXCEPTION WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN INSERT INTO log_table (info) VALUES (‘A select erro occurred’); END; Uma dada exceção pode ser no máximo tratada por um handler em uma seção de exceção. Se houver mais de um handler para uma exceção, o compilador PL/SQL levantará o PLS-486, como abaixo: DECLARE -- Declare 2 user defined exceptions e_Exception1 EXCEPTION; e_Exception2 EXCEPTION; BEGIN -- Raise just exception 1. RAISE e_Exception1; EXCEPTION WHEN e_Exception2 THEN INSERT INTO log_table (info) VALUES ('Handler 1 executed!'); WHEN e_Exception1 THEN 46 INSERT INTO log_table (info) VALUES ('Handler 3 executed!'); WHEN e_Exception1 OR e_Exception2 THEN INSERT INTO log_table (info) VALUES ('Handler 4 executed!'); END; WHEN e_Exception1 OR e_Exception2 THEN * ERROR at line 15: ORA-06550 line 15, column 3: PLS-00483: exception ‘E_EXCEPTION2’ may appear in at most one exception handler in this block ORA-06550: line 0, column 0: PL/SQL: compilation unit analysis terminated O handler de exceção OTHERS A PL/SQL define um handler especial de exceção, conhecido como WHEN OTHERS. Esse handler será executado em todas as exceções levantadas que forem tratadas por quaisquer outras cláusulas WHEN definidas na seção atual de exceção. Ele sempre deve ser o último handler no bloco, de modo que todos os handlers anteriores, serão varridos primeiros. WHEN OTHERS interromperá todas as exceções definidas pelo usuário ou predefinidas. Abaixo um exemplo com o handler OTHERS: DECLARE -- Exception to indicate an error condition e_TooManyStudents EXCEPTION; -- Current number of students registered for HIS-101 v_CurrentStudents NUMBER(3); -- Maximum number of students allowed in HIS-101 v_MaxStudents NUMBER(3); BEGIN /* Find the current number of registered students, and the maximum number of students allowed. */ SELECT current_students, max_students INTO v_CurrentStudents, v_MaxStudents FROM classes WHERE department = 'HIS' AND course = 101; /* Check the number of students in this class. */ 47 IF v_CurrentStudents > v_MaxStudents THEN /* Too many students registered -- raise exception. */ RAISE e_TooManyStudents; END IF; EXCEPTION WHEN e_TooManyStudents THEN /* Handler which executes when there are too many students registered for HIS-101. We will insert a log message explaining what has happened. */ INSERT INTO log_table (info) VALUES ('History 101 has ' || v_CurrentStudents || 'students: max allowed is ' || v_MaxStudents); WHEN OTHERS THEN /* Handler which executes for all other errors. */ INSERT INTO log_table (info) VALUES ('Another error occurred'); END; SQLCODE e SQLERRM Dentro de um handler OTHERS, freqüentemente é útil saber qual erro Orackle levantou a exceção, quer o erro tenha ou não uma exceção predefinida para ele.Uma das razões seria registrar em LOG o erro que ocorreu, em vez do fato de que um erro aconteceu.. Ou você talvez queira fazer coisas diferentes dependendo de qual erro foi levantado. A PL/SQL fornece essas informações via duas funções predefinidas:SQLCODE e SQLERRM. SQLCODE retorna o código do erro atual e SQLERRM retorna o texto da mensagem do erro atual. Em uma exceção definida pelo usuário, a SQLCODE retorna 1 e a SQLERRM retorna ‘Exceção definida pelo Usuário’. NOTA A função DBMS_UTILITY.FORMAT_ERRO_STACK também retorna uma mensagem do erro atual e pode ser utilizado além da SQLERRM. Abaixo o bloco completo PL/SQL que desenvolvemos até agora, com um handler de exceção OTHERS completo: -- This block contains a WHEN OTHERS handler that records which -- runtime error occurred. DECLARE -- Exception to indicate an error condition e_TooManyStudents EXCEPTION; -- Current number of students registered for HIS-101 v_CurrentStudents NUMBER(3); 50 onde nome_da_exceção é o nome de uma exceção declarada antes do pragma e numero_erro_do_Oracle é o código de erro do desejado a ser associado com essa exceção nomeada. Esse pragma deve estar na seção declarativa. O seguinte exemplo levantara a exceção definida pelo usuário: DECLARE e_MissingNull EXCEPTION; PRAGMA EXCEPTION_INIT(e_MissingNull, -1400); BEGIN INSERT INTO students (id) VALUES (NULL); EXCEPTION WHEN e_MissingNull then INSERT INTO log_table (info) VALUES ('ORA-1400 occurred'); END; Utilizando RAISE_APPLICATION_ERROR Você pode utilizar a função predefinida RAISE_APPLICATION_ERROR para criar suas próprias mensagens de erro, que podem ser mais descritivas que as exceções identificadas. Os erros definidos pelo usuário são passados para o bloco da mesma maneira como os erros do Oracle são passados para o ambiente de chamada. A sintaxe é: RAISE_APPLICATION_ERROR (numero_do_erro, mensagem_do_erro,”manter_erros”); onde numero_do_erro e o valor entre –20.000 e –20.999, mensagem_do_erro é o texto associado, e manter_erros é um valor booleano. Exemplo: /* Registers the student identified by the p_StudentID parameter in the class identified by the p_Department and p_Course parameters. */ CREATE OR REPLACE PROCEDURE Register ( p_StudentID IN students.id%TYPE, p_Department IN classes.department%TYPE, p_Course IN classes.course%TYPE) AS v_CurrentStudents classes.current_students%TYPE; v_MaxStudents classes.max_students%TYPE; v_NumCredits classes.num_credits%TYPE; v_Count NUMBER; BEGIN 51 /* Determine the current number of students registered, and the maximum number of students allowed to register. */ BEGIN SELECT current_students, max_students, num_credits INTO v_CurrentStudents, v_MaxStudents, v_NumCredits FROM classes WHERE course = p_Course AND department = p_Department; /* Make sure there is enough room for this additional student. */ IF v_CurrentStudents + 1 > v_MaxStudents THEN RAISE_APPLICATION_ERROR(-20000, 'Can''t add more students to ' || p_Department || ' ' || p_Course); END IF; EXCEPTION WHEN NO_DATA_FOUND THEN /* Class information passed to this procedure doesn't exist. */ RAISE_APPLICATION_ERROR(-20001, p_Department || ' ' || p_Course || ' doesn''t exist'); END; /* Ensure that the student is not currently registered */ SELECT COUNT(*) INTO v_Count FROM registered_students WHERE student_id = p_StudentID AND department = p_Department AND course = p_Course; IF v_Count = 1 THEN RAISE_APPLICATION_ERROR(-20002, 'Student ' || p_StudentID || ' is already registered for ' || p_Department || ' ' || p_Course); END IF; /* There is enough room, and the student is not already in the class. Update the necessary tables. */ INSERT INTO registered_students (student_id, department, course) VALUES (p_StudentID, p_Department, p_Course); UPDATE students 52 SET current_credits = current_credits + v_NumCredits WHERE ID = p_StudentID; UPDATE classes SET current_students = current_students + 1 WHERE course = p_Course AND department = p_Department; END Register; Execute abaixo os comandos para verificar os erros ocorridos pelo RAISE_APPLICATION_ERROR: -- Illustrate the ORA-2001 and ORA-2002 errors exec Register(10000, 'CS', 999); exec Register(10000, 'CS', 102); -- Register 2 students for MUS 410, which will raise ORA-2003 exec Register(10002, 'MUS', 410); exec Register(10005, 'MUS', 410); Compare a saida anterior com o proximo bloco anônimo que simplesmente levanta a exceção NO_DATA_FOUND: BEGIN RAISE NO_DATA_FOUND; END; BEGIN * ERROR at line 1: ORA-01403: no data found ORA-06512: at line 2 Exceções levantadas na seção de exceção As exceções também podem ser levantadas quando ainda estiverem em um handler de exceção, tanto explicitamente via instrução RAISE como implicitamente via um erro em tempo de execução. Em qualquer um dos casos, a exceção é imediatamente propagada para o bloco “pai”. Um problema com as exceções é que quando uma exceção ocorre, não há nenhuma maneira fácil para informar qual parte do código estava sendo executado nesse momento. A PL/SQL fornece uma solução para isso, com a função DBMS_UTILITY.FORMAT_CALL_STACK. Essa função retornará um “trace” para se chegar ao ponto onde a exceção foi gerada. 55 Select substr(‘abc123def ‘, -4,4) “Second” From dual; Second -------- 3def Select substr(‘abc123def ‘, 5) “Third” From dual; Third ------- 23def SOUNDEX Sintaxe: SOUNDEX(string) Retorna a representação fonética de string. Isso é útil para comparar palavras que apresentam grafia diferente, mas são pronunciadas de modo semelhante. A representação fonética é definida no livro. The Art of Computer Programming, Volume 3: Sorting and Searching de Donald E. Knuth. Exemplo: Select first_name, soundex(first_name) From students; First_name SOUN -------------------------------------- Scott S300 Margaret M626 Select first_name From students Where soundex(first_name) = soundex(‘skit’); First_Name -------------- Scott 56 Funções de caractere que retornam valores numéricos Essas funções recebem argumentos de caractere e retornam resultados numéricos. Os argumentos por ser CHAR ou VARCHAR2. Embora vários dos resultados sejam de fato valores inteiros, o valor de retorno é simplesmente NUMBER, sem definição de precisão ou escala. ASCII (string) Retorna a representação decimal do primeiro byte de string no conjunto de caracteres do banco de dados. INSTR (string1,string2 [,a] [,b]) Retorna a posição dentro de string 1 onde string2 está contida, com a e b medidos em caracteres. LENGTH (string) Retorna o comprimento de string medido em caracteres. INSTR Sintaxe: INSTR (string1, string2 [,a] [,b]) Retorna a posição dentro de string1 em que string2 está contida. Exemplo: SELECT INSTR(‘Scott”s spot’ ,’ot’,1,2) “First” From dual; First ------ 11 LENGTH Sintaxe: LENGTH(string) Retorna o comprimento de string. Uma vez que valores de CHAR são preenchidos por espaços em branco, se string tiver tipo de dados de CHAR, os espaços em branco finais são incluídos no comprimento. Exemplo: 57 Select length (‘Mary had a little lamb’) “Length” From dual; Length -------- 22 Funções de NLS Exceto por NCHR, essas funções recebem argumentos de caractere e retornam os valores em caracteres. CONVERT (string, conjunto_de_caracteres_dest[,conjunto_de_caracteres_orig]) Converte a entrada de string no conjunto de caracteres conjunto_de_caracteres_dest especificado. NCHR(x) Retorna o caractere que tem o valor equivalente ao x no banco de dados do conjunto de caracteres nacional. NCHR(x) é equivalente a CHR (x USING NCHAR_CS). NLS_CHARSET_DECL_LEN Retorna (em caracteres) a largura da declaração de um valor de NCHAR, largura_de_byte é o comprimento do valor em bytes e conjunto_de_caracteres é o ID do conjunto de caracteres do valor. NLS_CHARSET_ID Retorna o ID numérico do conjunto especificado de caracteres nomeado nome_do_conjunto_de_caracteres. NLS_CHARSET_NAME Retorna o ID do conjunto de caracteres especificado chamado ID_do_conjunto_de_caracteres. NLS_INITCAP Retorna string com o primeiro caractere de cada palavra em letras maiúsculas e os caracteres restantes de cada palavra em letras minúsculas. NLS_LOWER Retorna string com todas as letras em minúsculas. Os caracteres que não são letras permanecem intactos. NLS_UPPER Retorna string com todas as letras em Maiúsculas. 60 WIDTH_BUCKET Sintaxe: WIDTH_BUCKET(x,mín, máx, num_buckets) WIDTH_BUCKET permite que você crie histogramas com comprimento igual com base nos parâmetros de entrada. O intervalo mín...máx é divido em num_buckts seções, tendo cada seção o mesmo tamanho. A seção em que x cai então é retornada. Se x for menor que mín, 0 é retornado. Se x for maior que ou igual a máx, num_bucktes+1 é retornado. Nem mim nem máx podem ser NULL; e num_buckets deve ser avaliado como um inteiro positivo. Se x for NULL, então NULL é retornado. Exemplo: O seguinte exemplo configura 20 buckets, cada um com um tamanho de 50 (1.000/20): Select number_seats, WIDTH_BUCKET(number_seats, 1,1000,20) Bucket FROM rooms; NUMBER_SEATS BUCKET ------------------ ------------ 1000 21 500 10 50 1 1000 21 Funções de data e hora As funções de data recebem argumentos do tipo DATE. Exceto pela função MONTHS_BETWENN, que retorna um NUMBER, todas as funções retornam valores DATE ou datetime. ADD_MONTHS(d,x) Retorna a data d mais x meses. X pode ser qualquer inteiro. Se o mês resultante tiver menos dias que o mês d, o último dia do mês resultante é retornado. Se não, o resultado tem o mesmo componente de dia que d. O componente de hora de d e o resultado são os mesmos. CURRENT_DATE Retorna a data atual na sessão do fuso horário com um valor de DATE. Essa função é semelhante a SYSDATE, exceto que SYSDATE não é sensível ao fuso horário da sessão atual. 61 CURRENT_TIMESTAMP Retorna a data atual na sessão do fuso horário como um valar de TIMESTAMP WITH TIMEZONE. Se precisão for especificada, ela representa a precisão decimal do número de segundo que é retornado. O valor de 6 é assumido por padrão. DBTIMEZONE Retorna o fuso horário do banco de dados. O formato é o mesmo utilizado pele instrução CREATE DATABASE ou mais recente ALTER DATABASE. LAST_DAY Retorna a data do último dia do mês que contém d.Essa função pode ser utilizada para determinar quantos dias restam no mês atual. LOCALTIMESTAMP Retorna a data atual no fuso horário da sessão como um valor TIMESTAMP. MONTHS_BETWEEN Retorna o número de meses entre a data1 ea data2. NEW_TIME (d, zona1,zona2) Retorna a data ea hora do fuso horário da zona2 quando a data e a hora no fuso horário da zona1 forem d. NEXTDAY Retorna a data do primeiro dia nomeado por string que é mais antiga que a data d. ROUND (d[,formato]) Arredonda a data d para a unidade especificada por formato. SESSIONTIMEZONE Retorna o fuso horário da sessão atual. SYS_EXTRACT_UTC Retorna a hora em UTC (Coordinated Universal Time, antigamente Greenwich Mean Time) a partir do datetime fornecido, o qual deve incluir um fuso horário. SYSDATE Retorna a data e a hora atual no tipo DATE. SYSTIMETAMP Retorna a data e hora atual do tipo TIMESTAMP WITH TIMEZONE. TRUNC (d,[,formato]) Retorna a data d truncado para a unidade especificada por formato.