Oracle PL / SQL - não foram encontradas excepções para o desempenho do procedimento armazenado?
SELECT COUNT(*) INTO var WHERE condition;
IF var > 0 THEN
SELECT NEEDED_FIELD INTO otherVar WHERE condition;
....
-ou-
SELECT NEEDED_FIELD INTO var WHERE condition;
EXCEPTION
WHEN NO_DATA_FOUND
....
O segundo caso parece-me um pouco mais elegante, porque depois posso usar o NEEDED_FIELD, que eu ... teria que selecionar na primeira declaração após a condição no primeiro caso. Menos código. Mas se o procedimento armazenado vai correr mais rápido usando a contagem (*), então eu não me importo de digitar um pouco mais para recuperar a velocidade de processamento.
Alguma pista? Estou a perder outra possibilidade?
editar Eu deveria ter mencionado que tudo isso já está aninhado em um laço FOR. Não sei se isto faz alguma diferença com o uso de um cursor, uma vez que acho que não posso declarar o cursor como um selecione no laço FOR.
12 answers
Eu não usaria um cursor explícito para fazer isto. Steve F. já não aconselha as pessoas a usar cursores explícitos quando um cursor implícito poderia ser usado.
O método com count(*)
não é seguro. Se outra sessão apagar a linha que atendeu a condição após a linha com o count(*)
, e antes da linha com o select ... into
, o código irá lançar uma exceção que não será tratada.
A segunda versão do post original não tem este problema, e é geralmente preferido.
Dito isto, há uma pequena sobrecarga usando a exceção, e se você tem 100% de certeza que os dados não vão mudar, você pode usar o count(*)
, mas eu recomendo contra isso.
Corri estes parâmetros de referência emOracle 10.2.0.1 em32 bits Windows . Só estou a olhar para o tempo decorrido. Existem outros arneses de teste que podem dar mais detalhes (como contagens de fechos e memória usada).
SQL>create table t (NEEDED_FIELD number, COND number);
Tabela criada.
SQL>insert into t (NEEDED_FIELD, cond) values (1, 0);
1 linha criado.
declare
otherVar number;
cnt number;
begin
for i in 1 .. 50000 loop
select count(*) into cnt from t where cond = 1;
if (cnt = 1) then
select NEEDED_FIELD INTO otherVar from t where cond = 1;
else
otherVar := 0;
end if;
end loop;
end;
/
O procedimento PL / SQL foi concluído com sucesso.
Decorrido: 00:00:02.70
declare
otherVar number;
begin
for i in 1 .. 50000 loop
begin
select NEEDED_FIELD INTO otherVar from t where cond = 1;
exception
when no_data_found then
otherVar := 0;
end;
end loop;
end;
/
O procedimento PL / SQL foi concluído com sucesso.
Decorrido: 00:00:03.06
Uma vez que Seleccionar em assume que será devolvida uma única linha, poderá usar uma declaração do formulário:
SELECT MAX(column)
INTO var
FROM table
WHERE conditions;
IF var IS NOT NULL
THEN ...
A selecção dar-lhe-á o valor se estiver disponível, e um valor nulo em vez de uma excepção NO_ DATA_FOUND. A sobrecarga introduzida pelo MAX () será mínima a zero, uma vez que o conjunto de resultados contém uma única linha. Ele também tem a vantagem de ser compacto em relação a uma solução baseada em cursor, e não ser vulnerável a problemas de concorrência, como os dois passos solução no post original.
DECLARE
CURSOR foo_cur IS
SELECT NEEDED_FIELD WHERE condition ;
BEGIN
FOR foo_rec IN foo_cur LOOP
...
END LOOP;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END ;
O laço não é executado se não houver dados. Cursor para loops são o caminho a seguir - eles ajudam a evitar um monte de limpeza. Uma solução ainda mais compacta:
DECLARE
BEGIN
FOR foo_rec IN (SELECT NEEDED_FIELD WHERE condition) LOOP
...
END LOOP;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END ;
Que funciona se souber a instrução de selecção completa à hora de compilação.
@DCookie
Só quero salientar que podes deixar de fora as linhas que dizem:EXCEPTION
WHEN OTHERS THEN
RAISE;
Você terá o mesmo efeito se você deixar fora do bloco de exceção todos juntos, e o número de linha relatado para a exceção será a linha onde a exceção é realmente lançada, não a linha no bloco de exceção onde foi re-levantado.
Stephen Darlington faz um ponto muito bom, e você pode ver que se você mudar a minha referência para usar uma tabela de tamanho mais realisticamente se eu preencher a tabela para 10000 linhas usando o seguinte:
begin
for i in 2 .. 10000 loop
insert into t (NEEDED_FIELD, cond) values (i, 10);
end loop;
end;
Então volte a executar os parâmetros de referência. (Eu tive que reduzir o loop conta para 5000 para obter tempos razoáveis).
declare
otherVar number;
cnt number;
begin
for i in 1 .. 5000 loop
select count(*) into cnt from t where cond = 0;
if (cnt = 1) then
select NEEDED_FIELD INTO otherVar from t where cond = 0;
else
otherVar := 0;
end if;
end loop;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:04.34
declare
otherVar number;
begin
for i in 1 .. 5000 loop
begin
select NEEDED_FIELD INTO otherVar from t where cond = 0;
exception
when no_data_found then
otherVar := 0;
end;
end loop;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.10
O método com excepção é agora mais do dobro da velocidade. Assim, para quase todos os casos, o método:
SELECT NEEDED_FIELD INTO var WHERE condition;
EXCEPTION
WHEN NO_DATA_FOUND....
É o caminho a seguir. Ele vai dar o correto resultados e é geralmente o mais rápido.
Se é importante você realmente precisa de aferir ambas as opções!
Dito isto, sempre usei o método de excepção, sendo que o raciocínio é melhor só acertar na base de dados uma vez.Sim, estás a perder usando Cursores
DECLARE
CURSOR foo_cur IS
SELECT NEEDED_FIELD WHERE condition ;
BEGIN
OPEN foo_cur;
FETCH foo_cur INTO foo_rec;
IF foo_cur%FOUND THEN
...
END IF;
CLOSE foo_cur;
EXCEPTION
WHEN OTHERS THEN
CLOSE foo_cur;
RAISE;
END ;
É certo que isto é mais código, mas não usa exceções como controle de fluxo que, tendo aprendido a maior parte do meu PL/SQL do livro de programação PL/SQL de Steve Feuerstein, acredito ser uma coisa boa.
Se isto é mais rápido ou não eu não sei (eu faço muito pouco PL/SQL hoje em dia).
Em vez de ter aninhados laços de cursor uma abordagem mais eficiente seria usar um laço de cursor com uma junção exterior entre as tabelas.
BEGIN
FOR rec IN (SELECT a.needed_field,b.other_field
FROM table1 a
LEFT OUTER JOIN table2 b
ON a.needed_field = b.condition_field
WHERE a.column = ???)
LOOP
IF rec.other_field IS NOT NULL THEN
-- whatever processing needs to be done to other_field
END IF;
END LOOP;
END;
Não tem de usar o open quando está a usar para laços.
declare
cursor cur_name is select * from emp;
begin
for cur_rec in cur_name Loop
dbms_output.put_line(cur_rec.ename);
end loop;
End ;
Ou
declare
cursor cur_name is select * from emp;
cur_rec emp%rowtype;
begin
Open cur_name;
Loop
Fetch cur_name into Cur_rec;
Exit when cur_name%notfound;
dbms_output.put_line(cur_rec.ename);
end loop;
Close cur_name;
End ;
Pode estar batendo um cavalo morto aqui, mas eu errei o cursor para o loop, e isso realizado sobre, bem como o método no_ data_found:
declare
otherVar number;
begin
for i in 1 .. 5000 loop
begin
for foo_rec in (select NEEDED_FIELD from t where cond = 0) loop
otherVar := foo_rec.NEEDED_FIELD;
end loop;
otherVar := 0;
end;
end loop;
end;
O procedimento PL / SQL foi concluído com sucesso.
Decorrido: 00:00:02.18
A contagem (*) nunca levantará a excepção porque devolve sempre a contagem actual ou 0 - zero, aconteça o que acontecer. Eu usaria a contagem.
A primeira (excelente) resposta afirmada -
O método com contagem () não é seguro. Se outra sessão apaga a linha que atendeu a condição após a linha com a contagem (*), e antes da linha com a seleção ... em, o código vai lançar uma exceção que não será tratada.
Não é verdade. Dentro de uma dada unidade lógica de trabalho Oráculo é totalmente consistente. Mesmo que alguém comete a remoção da linha entre uma contagem e um oráculo selecionado irá, para a sessão ativa, obtenha os dados dos logs. Se não puder, você terá um erro "snapshot demasiado velho".