Baixe Programação em Oracle - Apostilas - Informática Part3 e outras Notas de estudo em PDF para Informática, somente na Docsity!
-- the addition of the student to the class. If it is FALSE, -- then they are updated for the removal of the student. PROCEDURE UpdateStudentsAndClasses( p_Add IN BOOLEAN, p_StudentID IN students.id%TYPE, p_Department IN classes.department%TYPE, p_Course IN classes.course%TYPE) IS
-- Number of credits for the requested class v_NumCredits classes.num_credits%TYPE; BEGIN -- First determine NumCredits. SELECT num_credits INTO v_NumCredits FROM classes WHERE department = p_Department AND course = p_Course;
IF (p_Add) THEN -- Add NumCredits to the student's course load UPDATE STUDENTS SET current_credits = current_credits + v_NumCredits WHERE ID = p_StudentID;
-- And increase current_students UPDATE classes SET current_students = current_students + 1 WHERE department = p_Department AND course = p_Course; ELSE -- Remove NumCredits from the students course load UPDATE STUDENTS SET current_credits = current_credits - v_NumCredits WHERE ID = p_StudentID;
-- And decrease current_students UPDATE classes SET current_students = current_students - 1 WHERE department = p_Department AND course = p_Course; END IF; END UpdateStudentsAndClasses;
-- Add a new student for the specified class. PROCEDURE AddStudent(p_StudentID IN students.id%TYPE,
p_Department IN classes.department%TYPE, p_Course IN classes.course%TYPE) IS BEGIN INSERT INTO registered_students (student_id, department, course) VALUES (p_StudentID, p_Department, p_Course);
UpdateStudentsAndClasses(TRUE, p_StudentID, p_Department, p_Course); END AddStudent;
-- Removes the specified student from the specified class. PROCEDURE RemoveStudent(p_StudentID IN students.id%TYPE, p_Department IN classes.department%TYPE, p_Course IN classes.course%TYPE) IS BEGIN DELETE FROM registered_students WHERE student_id = p_StudentID AND department = p_Department AND course = p_Course;
-- Check to see if the DELETE operation was successful. If -- it didn't match any rows, raise an error. IF SQL%NOTFOUND THEN RAISE e_StudentNotRegistered; END IF;
UpdateStudentsAndClasses(FALSE, p_StudentID,p_Department, p_Course);
END RemoveStudent; ... END ClassPackage;
No corpo de pacote UpdateStudentAndClasses é declarado local. O seu escopo é, portanto o próprio corpo de pacote. Consequentemente, ele pode ser chamado a partir de outras procedures no corpo (a saber, AddStudent e RemoveStudent), mas não estará visivel fora do corpo.
p_IDs OUT t_StudentIDTable, p_NumStudents IN OUT BINARY_INTEGER); END ClassPackage;
CREATE OR REPLACE PACKAGE BODY ClassPackage AS -- Add a new student for the specified class. PROCEDURE AddStudent(p_StudentID IN students.id%TYPE, p_Department IN classes.department%TYPE, p_Course IN classes.course%TYPE) IS BEGIN INSERT INTO registered_students (student_id, department, course) VALUES (p_StudentID, p_Department, p_Course); END AddStudent;
-- Add a new student by name, rather than ID. PROCEDURE AddStudent(p_FirstName IN students.first_name%TYPE, p_LastName IN students.last_name%TYPE, p_Department IN classes.department%TYPE, p_Course IN classes.course%TYPE) IS v_StudentID students.ID%TYPE; BEGIN /* First we need to get the ID from the students table. */ SELECT ID INTO v_StudentID FROM students WHERE first_name = p_FirstName AND last_name = p_LastName;
-- Now we can add the student by ID. INSERT INTO registered_students (student_id, department, course) VALUES (v_StudentID, p_Department, p_Course); END AddStudent; ... END ClassPackage;
Agora pode-se adicionar um aluno para Música 410 tanto com um:
BEGIN
ClassPackage.AddStudent(10000, 'MUS',410); END;
como com:
BEGIN ClassPackage.AddStudent('Rita', 'Ramirez','MUS',410); END;
Sobrecarregar subprogramas é bastante útil, porém está sujeito a várias restrições:
- Não se pode sobrecarregar dois subprogramas se os seus parâmetros diferirem apenas em nome ou modo (IN, OUT ou IN OUT).
- Não se pode sobrecarregar duas funções apenas em seu tipo de retorno diferentes (por exemplo, RETURN DATE em uma e RETURN NUMBER na outra).
- Os parametros de funções sobrecarregadas devem diferir pela família do tipo - não se pode sobrecarregar na mesma família (CHAR como VARCHAR2).
Obs. O compilador PL/SQL permite criar um pacote com subprogramas que violam restrições, porém na execução ele não será capaz de resolver as referências e irá gerar um erro (PLS-307: too many declarations of 'subprogram' match this call).
Inicialização do pacote
Na primeira vez em que um subprograma empacotado é chamado, ou qualquer referencia para uma variável ou tipo empacotado ocorre, o pacote é instanciado. Isso significa que o pacote é lido do disco para a memória e o código compilado é executado a partir do subprograma chamado. Nesse ponto, é alocada memória para todas as variáveis definidas no pacote. Em várias situações, o código de inicialização precisa ser executado na primeira vez que o pacote é instanciado dentro de uma sessão. Isso pode ser feito adicionando uma seção de incialização ao corpo do pacote, depois de todos os outros objetos, com a sintaxe:
CREATE OR REPLACE PACKAGE BODY nome_de_pacote { IS | AS} ... BEGIN código_de inicialização END [nome_de_pacote];
onde nome_de_pacote é o nome do pacote e código_de_inicialização é o codigo a ser executado. O exemplo seguinte implementa uma função de número aleatório.
CREATE OR REPLACE PACKAGE Random AS -- Random number generator. Uses the same algorithm as the -- rand() function in C.
-- Used to change the seed. From a given seed, the same
END RandMax;
PROCEDURE GetRandMax(p_RandomNumber OUT NUMBER, p_MaxVal IN NUMBER) IS BEGIN -- Simply call RandMax and return the value. p_RandomNumber := RandMax(p_MaxVal); END GetRandMax;
BEGIN /* Inicialização do pacote. Inicializa a semente com a hora atual em segundos */ ChangeSeed(TO_NUMBER(TO_CHAR(SYSDATE, 'SSSSS'))); END Random;
Ao chamar Random.Rand, a sequência dos números aleatórios é controlada pela semente inicial - a mesma sequencia é gerada para uma dada semente. Assim, para fornecer mais valores aleatórios é preciso inicializar a semente com um valor diferente todas as vezes que o pacote for instanciado. Para isso, o procedimento ChangeSeed é chamado a partir da seção de inicialização do pacote.
UTILIZANDO PROCEDURES, FUNÇÕES E PACOTES
Localizações do subprograma
Os subprogramas e pacotes podem ser armazenados no dicionário de dados. Contudo, um subprograma pode ser definido dentro da seção declarativa de um bloco, neste caso, ele é conhecido subprograma local. Os pacotes podem ser armazenados no dicionario de dados e não podem ser locais.
Subprogramas armazenados e o dicionário de dados
Quando um subprograma é criado com CREATE OR REPLACE, ele é armazenado no dicionário de dados. Além do texto de origem, o subprograma é armazenado de uma forma compilada, conhecida como p-code (lido a partir do disco e armazenado na SGA podendo ser acessado por vários usuários). A visão user_objects contém informações sobre todos os objetos possuídos pelo usuário atual, incluindo subprogramas armazenados (data de criação, ultima modificação, tipo de objeto, validade do objeto, etc.) A visão user_objects contém o código fonte-original para o objeto. A visão user_errors contém informações sobre erros de compilação. Observação: na SQL*Plus, o comando show errors consultará user_errors sobre informações do último objeto criado.
Compilação nativa
No Oracle9 i pode-ser ter a PL/SQL compilada no código nativo criando uma biblioteca compartilhada então executada pelo processo interno do Oracle. Para utilizar este recurso é necessário ter um compilador C instalado no sistema. Para obter detalhes sobre como fazer isso deve-se consultar a documentação do Oracle.
Subprogramas locais
Um subprograma local é declarado na seção na seção declarativa de um bloco PL/SQL, como por exemplo:
DECLARE CURSOR c_AllStudents IS SELECT first_name, last_name FROM students;
v_FormattedName VARCHAR2(50);
/* Function which will return the first and last name concatenated together, separated by a space. */ FUNCTION FormatName(p_FirstName IN VARCHAR2, p_LastName IN VARCHAR2) RETURN VARCHAR2 IS BEGIN RETURN p_FirstName || ' ' || p_LastName;
Declarações prévias
Uma vez que os nomes de subprogramas locais PL/SQL são identificadores, eles devem ser declarados antes de serem referenciados. No caso de subprogramas mutuamente referencias, considere o exemplo:
DECLARE v_TempVal BINARY_INTEGER := 5;
-- Declaração introdutória da procedure B PROCEDURE B(p_Counter IN OUT BINARY_INTEGER);
PROCEDURE A(p_Counter IN OUT BINARY_INTEGER) IS BEGIN DBMS_OUTPUT.PUT_LINE('A(' || p_Counter || ')'); IF p_Counter > 0 THEN B(p_Counter); p_Counter := p_Counter - 1; END IF; END A;
PROCEDURE B(p_Counter IN OUT BINARY_INTEGER) IS BEGIN DBMS_OUTPUT.PUT_LINE('B(' || p_Counter || ')'); p_Counter := p_Counter - 1; A(p_Counter); END B; BEGIN B(v_TempVal); END;
Sobrecarregando subprogramas locais
Assim como os subrprogramas declarados em pacotes podem ser sobrecarregados (como já visto), isso também é verdadeiro para subprogramas locais. Por exemplo:
DECLARE -- Two overloaded local procedures PROCEDURE LocalProc(p_Parameter1 IN NUMBER) IS BEGIN DBMS_OUTPUT.PUT_LINE('In version 1, p_Parameter1 = ' || p_Parameter1); END LocalProc;
PROCEDURE LocalProc(p_Parameter1 IN VARCHAR2) IS BEGIN DBMS_OUTPUT.PUT_LINE('In version 2, p_Parameter1 = ' || p_Parameter1); END LocalProc; BEGIN -- Call version 1 LocalProc(12345);
-- And version 2 LocalProc('abcdef'); END;
Subprogramas locais versus armazenados
Se você possuir um subprograma útil e querer chamá-lo a partir de mais de um bloco, considere que deva ser armazenado no banco de dados. Os subprogramas locais geralmente são considerados apenas a partir de uma seção específica do programa utilizadas para evitar duplicação de código dentro de um único bloco.
Considerações sobre subprogramas e pacotes armazenados
Subprogramas e pacotes armazenados como objetos do dicionário de dados têm a vantagem de serem compartilhados entre os usuários do banco de dados, porém as dependências dos subprogramas com os objetos armazenados podem sofrer implicações. No caso de uma procedure compilada e armazenada no dicionário de dados ela é dependente dos objetos que referencia, e pode tornar-se inválida caso uma operação DDL seja realizada em um dos seus objetos dependentes - por exemplo, ao acrescentar uma coluna extra na tabela.
Recompilação automática
Se o objeto dependente for invalidado, o mecanismo PL/SQL tentará recompilá-lo automaticamente na próxima vez que for chamado. Uma vez que procedure, no caso, não referenciam a nova coluna, a recompilação será bem-sucedida. No caso de falha, o bloco de chamada receberá um erro de compilação em tempo de execução.
Pacotes e dependências
No caso de pacotes o corpo de pacote depende do cabeçalho do pacote, contudo o cabeçalho de pacote não depende do corpo do pacote (caso os argumentos não precisem ser alterados). Pode-se alterar o corpo de pacote sem alterar o cabeçalho, outros objetos que dependem do cabeçalho não terão de ser recompilados. Porém, se o cabeçalho for alterado, automaticamente invalida o corpo.
Obs. As visualizações de dicionario de dados user_dependencies, all_dependencies e dba_dependencies listam diretamente o relacionamento entre os objetos de esquema.
Como as invalidações são determinadas
As invalidações que foram determinadas ocorreram devido ao dicionário de dados que monitora as depedências. Por exemplo, caso duas procedures P1 e P2 na qual P depende de P2, o que significa que recompilar P2 invalidará P1.
CREATE OR REPLACE PROCEDURE P2 AS
Se SPECIFICATION e BODY não forem especificados, ambos são compilados.
Estado em tempo de execução de pacote
O estado da execução de um pacote - a saber, variaveis empacotadas e cursores - são mantidos na memória de cada sessão que 'instanciou' o pacote e permanente até que a sessão seja fechada, assim sendo, as variveis em um cabecalho de pacote podem ser utilizadas como variaveis globais.
Privilégios e subprogramas armazenados
Subprogramas e pacotes armazenados são objetos no dicionario de dados, e como tais são possuidos por um usuario em particular ou esquema do banco de dados. Se forem concedidos os privilégios corretos, outros usuarios podem acessar esses objetos.
Privilégio EXECUTE
Para executar subprogramas e pacotes armazenados, o privilégio EXECUTE é necessário ser concedido pela instrução GRANT. Ao conceder o privilégio EXECUTE de um subprograma pertencente ao UsuarioA para um UsuarioB, o UsuarioB poderá executar o subprograma; os objetos dependentes do subprograma e pertencentes ao UsuarioA terão preferência. Por exemplo, se o subprograma altera uma tabela Temp1 existente tanto em UsuarioA quanto UsuarioB a preferência é executar os objetos pertencentes ao proprietário do subprograma, no caso, UsuarioA. O Oracle8 i inclui um novo recurso conhecido como "direitoss do chamador" que pode modificar essa situação (visto adiante).
Obervação importante: Os únicos objetos disponíveis dentro de uma procedure armazenada, função, pacote ou trigger são aqueles possuídos pelo proprietário do subprograma ou explicitamente concedidos ao proprietário.
Direito do chamador versus direito do definidor
O Oracle8 i introduz um tipo diferente de solução de referência externa. Em um subprograma de direitos do chamador , as referências externas são resolvidas com o conjunto de privilégios do chamador, não do proprietário. Uma rotina de direitos do chamador é criada utilizando a cláusula AUTHID. Ela é válida apenas em subprogramas independentes, especificações de pacote e especificações de tipo de objeto. A sintaxe de AUTHID é:
CREATE [OR REPLACE] FUNCTION nome_de_função [ lista_de_parâmetros ] RETURN tipo_de_retorno [AUTHID {CURRENT_USER | DEFINER}] {IS | AS} corpo_de_função;
CREATE [OR REPLACE] PROCEDURE nome_de_procedure [lista_de_parâmetros]
[AUTHID {CURRENT_USER | DEFINER}] {IS | AS} corpo_de_procedure;
CREATE [OR REPLACE] PACKAGE nome_da_especificação_do_pacote [AUTHID {CURRENT_USER | DEFINER}] {IS | AS} especificação_de_procedure;
Se CURRENT_USER for especificado na cláusula AUTHID, o objeto terá os direitos do chamador. Se DEFINER for especificado, então o objeto terá os direitos do definidor. Default -> direitos do definidor. O exemplo a seguir é uma procedure de direitos do chamador:
CREATE OR REPLACE PROCEDURE RecordFullClasses AUTHID CURRENT_USER AS
-- Note that we have to preface classes with -- UserA, since it is owned by UserA only. CURSOR c_Classes IS SELECT department, course FROM UserA.classes; BEGIN FOR v_ClassRecord IN c_Classes LOOP -- Record all classes which don't have very much room left -- in temp_table. IF AlmostFull(v_ClassRecord.department, v_ClassRecord.course) THEN INSERT INTO temp_table (char_col) VALUES (v_ClassRecord.department || ' ' || v_ClassRecord.course || ' is almost full!'); END IF; END LOOP; END RecordFullClasses;
Triggers, visualizações e direitos do chamador
Um trigger de banco de dados sempre será executado com os direitos do definidor e com o conjunto de privilégios do esquema que possui a tabela-trigger. Isso também é verdadeiro em uma função PL/SQL que é chamada a partir de uma visão. Nesse, caso a função será executada com o conjunto de privilégios do proprietário visão.
Utilizando funções armazenadas em instruções SQL
Se uma função independente ou uma função empacotada cumprir certas restrições, ela pode ser chamada durante a execução de uma instrução SQL. Recurso inicialmente introduzido na PL/SQL 2.1 (Oracle7 versão 7.1) e aprimorada no Oracle8 i. As funções definidas pelo usuário são chamadas de mesma maneira que as funções predefinidas como TO_CHAR, UPPER, ADD_MONTHS. Dependendo de onde uma função definida pelo usuário é utilizada e qual versão do Oracle esta sendo executada, ela deve cumprir diferentes restrições. Essas restrições são definidas em termos de níveis de pureza.
Como um exmplo, a função FullName receve um número de ID de aluno como uma entrada e retorna o nome e o sobrenome concatenados:
CREATE OR REPLACE FUNCTION FullName ( p_StudentID students.ID%TYPE) RETURN VARCHAR2 IS
v_Result VARCHAR2(100); BEGIN SELECT first_name || ' ' || last_name INTO v_Result FROM students WHERE ID = p_StudentID; RETURN v_Result; END FullName;
executando... SELECT ID, FullName(ID) "Full Name" FROM students;
e... INSERT INTO temp_table (char_col) VALUES (FullName(10010));
Chamando funções armazenadas a partir da SQL no Oracle8 i
Nas versões anteriores ao Oracle8 i era utilizado o pragma RESTRICT_REFERENCES para impor níveis de pureza em tempo de compilação para as funções empacotadas. A partir do Oracle8 i , se o pragma não estiver presente, o banco de dados verificará o nível de pureza de uma função em tempo de execução. Porém, utilizar o pragama pode poupar algumtempo de execução e também serve para documentar o comportamento da função. Por exemplo:
CREATE OR REPLACE PACKAGE StudentOps AS
FUNCTION FullName(p_StudentID IN students.ID%TYPE) RETURN VARCHAR2;
/* Returns the number of History majors. */ FUNCTION NumHistoryMajors RETURN NUMBER; END StudentOps;
CREATE OR REPLACE PACKAGE BODY StudentOps AS -- Packaged variable to hold the number of history majors. v_NumHist NUMBER;
FUNCTION FullName(p_StudentID IN students.ID%TYPE) RETURN VARCHAR2 IS v_Result VARCHAR2(100); BEGIN SELECT first_name || ' ' || last_name
INTO v_Result FROM students WHERE ID = p_StudentID;
RETURN v_Result; END FullName;
FUNCTION NumHistoryMajors RETURN NUMBER IS v_Result NUMBER; BEGIN IF v_NumHist IS NULL THEN /* Determine the answer. / SELECT COUNT() INTO v_Result FROM students WHERE major = 'History'; /* And save it for future use. */ v_NumHist := v_Result; ELSE v_Result := v_NumHist; END IF;
RETURN v_Result; END NumHistoryMajors; END StudentOps;
Chamando a função a partir da SQL: SQL> SELECT StudentOps.FullName(ID) FROM students WHERW major='History';
ou... SQL> INSERT INTO temp_table (num_col) VALUES (StudentOps.NumHistoryMajors);
Chamando funções a partir de instruções de DML
Antes do Oracle8 i , uma função chamada a partir de uma instrução de DML não poderia atualizar o banco de dados (isso é, ela deve afirmar o nível de pureza de WNDS). Entretanto, com o Oracle8 i , essa restrição foi abrandada. Agora, uma função chamada a partir de uma instrução de DML não deve ler ou modificar a(s) tabela(s) que esta(ão) sendo modificada(s) por essa instrução de DML, porém ela pode atualizar outras tabelas. Por exemplo:
CREATE OR REPLACE FUNCTION UpdateTemp(p_ID IN students.ID%TYPE) RETURN students.ID%TYPE AS BEGIN INSERT INTO temp_table (num_col, char_col) VALUES(p_ID, 'Updated!'); RETURN p_ID; END UpdateTemp;
SQL> UPDATE students SET major = 'Nutrition' WHERE UpdateTemp(ID) = ID;
T – Tipo de objeto (Oracle8 e superior) JS – Fonte Java (Oracle8 i e superior) JC – Classe Java (Oracle8 i e superior) JR – Recurso Java (Oracle8 i e superior) JD – Dados de Java compartilhados (Oracle8 i e superior) C – Cursor SQL
UNKEEP
UNKEEP é a única maneira de remover um objeto mantido do pool compartilhado sem reinicilizar o banco de dados. Objetos mantidos nunca expiram automaticamente. UNKEEP é definido com
PROCEDURE UNKEEP ( nome VARCHAR2, flag CHAR DEFAULT ‘P’);
Os argumentos são os mesmo de KEEP. Se o objeto especificado ainda não existe no pool compartilhado, um erro é levantado. SIZES
SIZES ecoará o conteúdo do pool compartilhado para a tela. Ele é definido com:
PROCEDURE SIZES ( tamanho_mínimo NUMBER);
Objetos com um tamanho maior do que tamanho_mínimo serão retornados. SIZES utiliza DBMS_OUTPUT para retornar os dados, portanto certifique-se de que utilizar “set serveroutput on” na SQL*Plus ou Server Manager antes de chamar a procedure.
ABORTED_REQUEST_THRESHOLD
Quando o banco de dados determina que não há memória suficiente no pool compartilhado para satisfazer uma dada solicitação, ele começa a expirar os objetos até que haja memória suficiente. Se um número suficiente de objetos expirar, isso pode ter um impacto no desempenho em outras sessões do banco de dados. ABORTED_REQUEST_THRESHOLD pode ser utilizada para reparar isso. Ela é definida com:
PROCEDURE ABORTED_REQUEST_THRESHOLD ( tamanho_limiar NUMBER);
Uma vez que essa procedure tenha chamada, o Oracle não começara a expirar os objetos no pool a menos que pelo menos bytes tamanho_limiar sejam necessários.
TRIGGERS DE BANCO DE DADOS
Triggers (ou gatilhos) são semelhantes as procedures ou funções, pelo fato de serem blocos identificados PL/SQL com seções declarativas, executáveis e de tratamento de exceções. Por outro lado, um trigger é executado implicitamente sempre que o evento desencadeador acontece. Um trigger também não aceita argumentos. Os triggers têm várias utilidades, incluindo: Manter restrições complexas de integridade Fazer auditoria das informações com detalhes Sinalizar automaticamente a outros programas que uma ação precisa acontecer quando são feitas alterações em uma tabela
Há três tipos principais de triggers: DML, instead-of e triggers de sistema.
Sintaxe para criação de triggers
CREATE [OR REPLACE] TRIGGER nome {BEFORE | AFTER | INSTED-OF} evento [ cláusula_de_referência ] [WHEN condição_da_trigger ] [FOR EACH ROW] corpo_de_trigger;
Criando triggers de DML
São acionados em uma operação INSERT, UPDATE ou DELETE de uma tabela de banco de dados. Pode ser acionado antes ou depois que uma instrução é executada e pode ser acionado uma vez por linha ou uma vez por instrução. Uma tabela pode ter qualquer número de triggers definidos nela, incluindo mais de um dado tipo de DML.
Exemplo:
SQL> CREATE SEQUENCE trig_seq 2 START WITH 1 3 INCREMENT BY 1;
Sequence created.
SQL> SQL> CREATE OR REPLACE PACKAGE TrigPackage AS 2 -- Global counter for use in the triggers 3 v_Counter NUMBER; 4 END TrigPackage; 5 /
Package created.
