1.O que é um procedimento armazenado?
O procedimento armazenado é compilado uma vez e pode ser executado várias vezes (o procedimento armazenado é armazenado no servidor). É uma coleção pré-compilada e executada rapidamente.
2. Procedimentos armazenados do sistema comumente usados
código
-- Finalidade: Procedimentos armazenados do sistema comumente usados
EXEC sp_databases --Lista os bancos de dados no sistema atual
EXEC sp_renamedb 'test','test1'--Alterar nome do banco de dados (acesso de usuário único)
USAR estuDB
IR
EXEC sp_tables --Lista de objetos consultados no banco de dados atual
EXEC sp_columns stuInfo --retorna informações sobre uma coluna da tabela
EXEC sp_help stuInfo --Ver informações da tabela stuInfo
EXEC sp_helpconstraint stuInfo --Ver as restrições da tabela stuInfo
EXEC sp_helpindex stuMarks --Ver o índice da tabela stuMarks
EXEC sp_helptext 'view_stuInfo_stuMarks' --Ver o texto da instrução da visualização
EXEC sp_stored_procedures --retorna a lista de procedimentos armazenados no banco de dados atual
usar mestre
ir
exec xp_cmdshell 'mkdir D:bank',no_output--Criar pasta
3. Procedimentos armazenados personalizados
(1.) Procedimento armazenado sem parâmetros
código
usar pino
ir
se existir (selecione * em sysobjects onde name='proc_stu')
descartar proc proc_stu
ir
criar procedimento proc_stu
COMO
DECLARE @writeAvg float,@labAvg float --Variável de pontuação média do teste escrito e do teste de computador
SELECT @writeAvg=AVG(writingExam), @labAvg=AVG(labExam) FROM stuMarks
print 'Pontuação média no teste escrito:'+convert(varchar(5),@writeAvg)
print 'Pontuação média do teste de computador:'+convert(varchar(5),@labAvg)
SE (@writeAvg>70 E @labAvg>70)
print 'Resultado do exame desta aula: Excelente'
OUTRO
print 'Resultados do exame desta aula: ruim'
imprimir '------------------------------------------------ --'
print 'Alunos que não passaram neste exame:'
SELECIONE stuName,stuInfo.stuNo,writingExam,labExam FROM stuInfo
INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo
ONDE escritoExame<60 OU labExam<60
IR
exec proc_stu --Executa procedimentos armazenados (2.) Procedimentos armazenados com parâmetros de entrada
código
USAR estuDB
IR
/*---Detecta se existe: o procedimento armazenado é armazenado na tabela do sistema sysobjects---*/
SE EXISTE (SELECT * FROM sysobjects WHERE name = 'proc_stu' )
PROCEDIMENTO DE QUEDA proc_stu
IR
/*---Criar procedimento armazenado----*/
CRIAR PROCEDIMENTO proc_stu
@writePass int,
@labPass int-- Você pode adicionar um valor padrão para que a execução seja assim exec proc_stu sem especificar parâmetros.
COMO
print 'Marca de aprovação escrita:'+convert(varchar(5),@writingPass)
print 'Linha de aprovação no teste de computador:'+convert(varchar(5),@labPass)
imprimir '------------------------------------------------ --'
print 'Alunos que não passaram neste exame:'
SELECIONE stuName,stuInfo.stuNo,writingExam,labExam FROM stuInfo
INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo
ONDE escritoExam<@writingPass OU labExam<@labPass
IR
exec proc_stu 60,55
exec proc_stu 60,@labPass=55
exec proc_stu @writePass=60,@labPass=55
exec proc_stu @writingPass=60,55--Esta linha reportará um erro: o parâmetro 2 deve ser passado e os parâmetros subsequentes devem ser passados na forma de '@name = value'.
-- Depois que o formulário '@nome = valor' for usado, todos os parâmetros subsequentes deverão ser passados no formulário '@nome = valor'
(3.) Procedimento armazenado com parâmetros de saída
código
USAR estuDB
IR
/*---Detecta se existe: o procedimento armazenado é armazenado na tabela do sistema sysobjects---*/
SE EXISTE (SELECT * FROM sysobjects WHERE name = 'proc_stu' )
PROCEDIMENTO DE QUEDA proc_stu
IR
/*---Criar procedimento armazenado----*/
CRIAR PROCEDIMENTO proc_stu
@notpassSum int OUTPUT, --OUTPUT palavra-chave, caso contrário, é considerado um parâmetro de entrada
@writePass int=60, --Os parâmetros padrão são colocados após
@labPass int=60 --Os parâmetros padrão são colocados após
COMO
print 'Marca de aprovação escrita:'+convert(varchar(5),@writingPass)
+ 'Linha de aprovação de teste baseado em computador:'+convert(varchar(5),@labPass)
imprimir '------------------------------------------------ --'
print 'Alunos que não passaram neste exame:'
SELECIONE stuName,stuInfo.stuNo,writingExam,labExam FROM stuInfo
INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo
ONDE escritoExam<@writingPass OU labExam<@labPass
/*--Conta e retorna o número de alunos que foram reprovados no exame--*/
SELECIONE @notpassSum=COUNT(stuNo)FROM stuMarks
ONDE escritoExam<@writingPass OU labExam<@labPass
IR
/*---Chama o procedimento armazenado----*/
DECLARE @soma int
EXEC proc_stu @sum OUTPUT ,64 --A nota de aprovação no teste de computador adota o valor padrão: a nota de aprovação no teste escrito é de 64 pontos e a nota de aprovação no teste de computador é de 60 pontos.
imprimir '------------------------------------------------ --'
SE @soma>=3
print 'Número de pessoas que falharam:'+convert(varchar(5),@sum)+ 'Pessoas, mais de 60%, a pontuação de aprovação deveria ser reduzida'
OUTRO
print 'Número de pessoas que falharam:'+convert(varchar(5),@sum)+ 'Pessoas, foi controlado abaixo de 60% e a pontuação de aprovação é moderada'
IR