SQL Procedural
Junho/2006
SQL Procedural
Agregada em SQL-92 As ferramentas tm nomes para suas linguagens SQL procedurais/embutidas
Oracle : PL/SQL Postgres PL/Pgsql SQL Server : Transact-SQL
SQL Procedural
Vantagens
Melhor performance Suporte da linguagem SQL Portabilidade
SQL Procedural
Suporte a mdulos de linguagem Cursores Estrutura de Seleo Estrutura de Loop Combinao com SQL declarativo Combinao com transaes Tratamento de excees Suporte a escopo de variveis Suporte aos tipos primitivos, complexos e domnios ( definidos pelo usurio)
Formato geral cabealho
CREATE FUNCTION name ( [ [ argname ] argtype [, ...] ] ) RETURNS rettype
Ex. CREATE FUNCTION atualizaValor ( varchar ) RETURNS boolean
Formato geral cabealho
CREATE [ OR REPLACE ] FUNCTION
cria e especifica o tipo de objeto ( funo/procedure) name ( [ [ argname ] argtype [, ...] ] )
RETURNS rettype
Ex. CREATE FUNCTION atualizaValor ( varchar ) RETURNS boolean
Formato geral cabealho
CREATE [ OR REPLACE ] FUNCTION
name ( [ [ argname ] argtype [, ...] ] ) especifica o nome do objeto funo RETURNS rettype
Ex. CREATE FUNCTION atualizaValor ( varchar ) RETURNS boolean
Formato geral cabealho
CREATE [ OR REPLACE ] FUNCTION
name ( [ [ argname ] argtype [, ...] ] ) especifica o(s) parmetro(s) da funo, se houver RETURNS rettype
Ex. CREATE FUNCTION atualizaValor ( varchar ) RETURNS boolean
Formato geral cabealho
CREATE [ OR REPLACE ] FUNCTION
name ( [ [ argname ] argtype [, ...] ] )
RETURNS rettype
especifica o tipo de retorno a ser esperado da invocao da funo
Onde rettype pode ser :
Tipo primitivo Estrutura composta Tipo do domnio Coluna de tabela
Formato geral cabealho
CREATE [ OR REPLACE ] FUNCTION name ( [ [ argname ] argtype [, ...] ] ) RETURNS rettype
especifica o tipo de retorno a ser esperado da invocao da funo Onde rettype pode ser :
Tipo primitivo Estrutura composta Tipo do domnio Coluna de tabela
Aspecto de funo
CREATE FUNCTION name ( [[ argname ] argtype [, ...] ] ) RETURNS rettype AS
declaraes
Language plpgsql ;
Language pode ser C, sql, plpgsql
Aspecto do corpo da funo
CREATE FUNCTION name ( [[ argname ] argtype [, ...] ] ) RETURNS rettype AS
Declare
variavel tipo ; Begin variavel := 20 ; return ?? End;
Language plpgsql ;
Exemplo
Na tabela Aluno Criar funo armazenada ( stored procedure) para atualizar o valor currculo do aluno. Parmetros
valor novo para currculo Num_matricula
Tipo do retorno: boolean
Exemplo
CREATE FUNCTION atualizaCurriculo(varchar, varchar) RETURNS boolean AS $$ BEGIN UPDATE aluno SET curriculo = $1 WHERE num_matricula = $2;
RETURN FOUND; END; $$ LANGUAGE 'plpgsql' ;
$1, $2 so parmetros passados pela chamada da funo
FOUND : palavra reservada do sistema; booleano que retorna true sse houve alterao
Binding da SP
SP criada com sucesso (compilada)
Binding significa invocao da SP !
Postgres
select suaFuncao( [parametros]); Mysql
call suaFuncao([parametros])
SQL-Server
exec suaFuncao([parametros])
Binding da SP/SF
select atualizaCurriculo('CX','90')
Estudo de caso: plpgsql
Tipos utilizveis Estrutura de loop Estrutura de seleo Cursores transaes
Atributos
Facilitar manuseio dos objetos de BD ROWTYPE TYPE
Pgplsql: atributo ROWTYPE
Estrutura flexvel Acomoda a estrutura da tabela Dinmico Sintaxe
Variavel nomeTabela%rowtype
Pgplsql: ROWTYPE
create function exibeLinhaAluno ( varchar ) returns text as $$ declare linha aluno%rowtype ; begin select * from aluno into linha where num_matricula like $1 ; return [Link] || ',' || linha.num_matricula ; end; $$ LANGUAGE plpgsql;
Pgplsql: RECORD
Estrutura mais flexvel que rowtype Acomoda a estrutura durante FOR/LOOP Dinmico NO um tipo realmente Sintaxe
Variavel RECORD ;
Plpgsql: estruturas de seleo
IF ... THEN IF ... THEN ... ELSE IF ... THEN ... ELSE IF IF ... THEN ... ELSIF ... THEN ... ELSE IF ... THEN ... ELSEIF ... THEN ... ELSE
5 formas O bloco sempre deve fechar com END IF ;
Plpgsql: estruturas de seleo
IF condio THEN
comandos;
ELSE
comandos;
END IF;
Plpgsql: estruturas de repetio
FOR..LOOP LOOP WHILE ... LOOP
Plpgsql: FOR...LOOP
Varivel para FOR pode ser RECORD ou ROW
DECLARE reg RECORD; BEGIN FOR reg IN consulta LOOP
comandos
END LOOP;
Plpgsql: FOR...LOOP
FOR i IN 1..10 LOOP RAISE NOTICE 'i is %', i; END LOOP;
FOR i IN REVERSE 10..1 LOOP -- comandos END LOOP;
FOR..LOOP e RECORD
create or replace function exibeLinhaAluno () returns boolean as $$ declare linha record ; begin FOR linha IN select * from aluno order by num_matricula LOOP RAISE NOTICE '%', [Link] ; END LOOP; return FOUND; end; $$ LANGUAGE plpgsql; Chamada : select exibeLinhaAluno();
WHILE LOOP
WHILE (condicao_v) LOOP
declaraes
END LOOP; Teste no incio
LOOP
LOOP
declaraes
END LOOP; Incondicional Uso de EXIT ou RETURN para sada
EXIT
EXIT; EXIT WHEN condio ;
