Stored Procedure

BASES DE DADOS II

Plano de Trabalho

Lab. 5: Programação em Transact-SQL

- Procedimentos do Lado do Servidor (Stored Procedures)

1. Conceito.

- Stored Procedures são semelhantes a subrotinas ou subprogramas desenvolvidos

noutras linguagens de programação (p.e. C, Pascal, Basic, Java, etc.), mas que são

guardados no servidor.

- Aceitam parâmetros de entrada e retornam resultados. Isto é, como qualquer

subprograma, um procedimento permite a passagem de parâmetros de entrada e

de saída, aceitando valores e devolvendo algum tipo de resultado à entidade que o

invocou, que pode ser um outro procedimento, um gatilho ou mesmo uma

aplicação externa cliente.

- Retornam um valor de status indicando se aconteceu um erro, e qual foi.

- São basicamente blocos de instruções SQL compiladas num único plano de

execução.

2. Propósitos/Vantagens.

- Diminuição do tráfego na rede. A execução destes programas no seio de um

servidor permite reduzir substancialmente o tráfego de rede provocado por

aplicações que solicitem ao servidor a execução de instruções SQL. O servidor

passa a ser assim não só servidor de dados, mas também servidor de programas

para a manipulação dos dados.

- Programação por módulos. Um stored procedure após ser guardado na BD, pode

ser invocado várias vezes num programa. Pode também ser alterado sem que haja

necessidade de alterar em todos os lados.

- Execução mais rápida. Se uma operação tem muitas instruções T-SQL e/ou é

executada muitas vezes, os stored procedures conseguem ser mais rápidos, pois

são compilados e optimizados no momento da sua criação.

- Segurança. Pode ser dada permissão aos utilizadores para executar um stored

procedure, mesmo que não tenham permissão para utilizar o mesmo código

directamente através de um editor.

3. Exemplos.

Exemplo 1:

CREATE PROC Cli

AS

SELECT * FROM CLIENTE

EXEC cli

Exemplo 2:

ALTER PROC Cli @CodCli int

AS

SELECT * FROM CLIENTES

WHERE ClienteID = @CodCli

EXEC Cli 1

4. Sintaxe.

CREATE PROC[EDURE] procedure_name [;number]

[

{@parameter data_type} [VARYING] [= default] [OUTPUT]

]

[,...n]

[WITH

{

RECOMPILE

| ENCRYPTION

| RECOMPILE, ENCRYPTION

}

]

[FOR REPLICATION]

AS

sql_statement [...n]

4.1. Argumentos

procedure_name

Is the name of the new stored procedure. Procedure names must conform to the rules

for identifiers and must be unique within the database and its owner.

Local or global temporary procedures can be created by preceding the

procedure_name with a single number sign (#procedure_name) for local temporary

procedures and a double number sign (##procedure_name) for global temporary

procedures. The complete name, including # or ##, cannot exceed 128 characters.

Specifying the procedure owner name is optional.

;number

Is an optional integer used to group procedures of the same name so they can be

dropped together with a single DROP PROCEDURE statement. For example, the

procedures used with an application called orders may be named orderproc;1,

orderproc;2, and so on. The statement DROP PROCEDURE orderproc drops the

entire group. If the name contains delimited identifiers, the number should not be

included as part of the identifier; use the appropriate delimiter around

procedure_name only.

@parameter

Is a parameter in the procedure. One or more parameters can be declared in a

CREATE PROCEDURE statement. The value of each declared parameter must be

supplied by the user when the procedure is executed (unless a default for the

parameter has been defined). A stored procedure can have a maximum of 1,024

parameters.

Specify a parameter name using an at sign (@) as the first character. The parameter

name must conform to the rules for identifiers. Parameters are local to the procedure;

the same parameter names can be used in other procedures. By default, parameters

can