Ultimo aggiornamento: 14.08.2017

Spesso, l'operazione dei dati presenta una serie di istruzioni che devono essere eseguite in una sequenza specifica. Ad esempio, quando si aggiunge l'acquisto di merci, è necessario effettuare dati nella tabella degli ordini. Tuttavia, prima di questo è necessario verificare e se c'è un prodotto acquistato in magazzino. Forse sarà necessario controllare un altro numero di condizioni aggiuntive. Cioè, infatti, il processo di acquisto di beni copre diverse azioni che devono essere eseguite in una determinata sequenza. E in questo caso, racchiuderà in modo più ottimale tutte queste azioni in un oggetto - procedura memorizzata Procedura memorizzata).

Cioè, procedure sostanzialmente memorizzate presentano una serie di istruzioni eseguite come un singolo numero intero. Pertanto, le procedure memorizzate consentono di semplificare le operazioni complete e portarle in un unico oggetto. Il processo di acquisto di beni cambierà, rispettivamente, sarà sufficiente modificare il codice procedura. Cioè, la procedura semplifica anche il codice di gestione.

Anche le procedure memorizzate consentono di limitare l'accesso ai dati nelle tabelle e ridurre così la probabilità di azioni indesiderate deliberate o inconsce per questi dati.

E un aspetto più importante - prestazioni. Le procedure memorizzate vengono solitamente eseguite più veloci rispetto alle normali istruzioni SQL. Tutto perché il codice procedura è compilato una volta in prima volta, e quindi persisteva in forma compilata.

Per creare una procedura memorizzata, viene utilizzata la procedura Crea o creare il comando proc.

Pertanto, la stored procedure ha tre caratteristiche principali: Semplifica il codice, la sicurezza e le prestazioni.

Ad esempio, lasciare che il database abbia una tabella che memorizza i dati sui prodotti:

Creare prodotti da tavola (ID INT INTENTITY Key Primary, ProductName Nvarchar (30) Non NULL, produttore NVARCHAR (20) Non NULL, ProductCount INT Default 0, prezzo denaro non nullo);

Creare una procedura memorizzata per estrarre i dati da questa tabella:

Utilizzare prodottiDB; Vai a creare la procedura Productsummary come Select ProductName come prodotto, produttore, prezzo dai prodotti

Poiché il team di procedure di creazione dovrebbe essere chiamato in pacchetto separato, quindi dopo il comando Usa, che imposta il database corrente, utilizzare il comando Go per determinare il nuovo pacchetto.

Dopo che il nome della procedura dovrebbe andare parola chiave COME.

Per separare il corpo della procedura dal resto dello script, il codice procedura è spesso inserito nell'unità iniziale ... END:

Utilizzare prodottiDB; Vai a creare la procedura Productsummary come Begin Select ProductName come prodotto, produttore, prezzo da parte dei prodotti;

Dopo aver aggiunto la procedura, possiamo vederlo nel nodo del database nello studio di gestione di SQL Server nella sottolenco Programmabilità -\u003e Procedure memorizzate:

E saremo in grado di gestire la procedura anche attraverso l'interfaccia visiva.

Procedura

Per eseguire la procedura memorizzata, viene chiamato il comando EXEC o EXECUTE:

Exec productsummary.

Rimozione della procedura

Il comando della procedura di rilascio si applica per eliminare la procedura:

Procedura di rilascio Productsummary.

1. Esaminare gli operatori che descrivono le procedure memorizzate e i principi per il trasferimento dei parametri di input e output.
2. Esaminare la procedura per la creazione e il debugging delle procedure memorizzate sul server MS SQL Server 2000.
3. Sviluppare cinque procedure di base memorizzate per base accademica Dati "Libreria".
4. Preparare un rapporto sul lavoro svolto in formato elettronico.

1. Informazioni generali sulle procedure memorizzate

Procedura memorizzata (stored procedure) - Questa è una serie di comandi, memorizzati sul server ed eseguiti nel suo complesso. Le procedure memorizzate sono un meccanismo con cui è possibile creare subroutine in esecuzione sul server e controllate dai suoi processi. Tali subroutine possono essere attivate dall'applicazione che li chiamano. Inoltre, possono essere causati da regole che supportano l'integrità dei dati o dei trigger.

Le procedure memorizzate possono restituire i valori. Nella procedura, è possibile confrontare i valori immessi dall'utente con le informazioni impostati in anticipo nel sistema. Le procedure memorizzate vengono utilizzate nell'operazione di potenti soluzioni hardware SQL Server. Si basano sui database e interagire da vicino con SQL Server Optimizer. Ciò consente di ottenere alte prestazioni durante l'elaborazione dei dati.

Nelle procedure memorizzate, è possibile trasmettere valori e ottenere da essi i risultati del lavoro e non necessariamente correlati al tavolo di lavoro. La procedura memorizzata può calcolare i risultati durante il funzionamento.

Le procedure memorizzate sono due tipi: normale e esteso. Le procedure memorizzate convenzionali sono un insieme di comandi su Transact-SQL, mentre le procedure memorizzate estese sono presentate come librerie dinamiche (DLL). Tali procedure, in contrasto con il consueto, hanno il prefisso XP_. Il server ha un set standard di procedure estese, ma gli utenti possono scrivere le loro procedure in qualsiasi linguaggio di programmazione. La cosa principale è usare l'interfaccia di programmazione Sql. Server aperto Servizi dati API.. Le procedure memorizzate estese possono essere solo nel database master.

Le procedure memorizzate convenzionali possono anche essere suddivise in due tipi: sistemico e abitudine. Procedure di sistema - quello è procedure standardFunzionamento del server di servizio; Personalizzato - Eventuali procedure create dall'utente.

1.1. Vantaggi delle procedure memorizzate

Nel caso più generale, le procedure memorizzate hanno i seguenti vantaggi:

• Alte prestazioni. È il risultato della posizione delle procedure memorizzate sul server. Il server è solitamente una macchina più potente, quindi il tempo di esecuzione della procedura sul server è significativamente inferiore a quello workstation.. Inoltre, le informazioni derivanti dal database e dalla stored procedure si trovano nello stesso sistema, quindi la trasmissione di record sulla rete non è praticamente spesa. Le procedure memorizzate hanno accesso diretto ai database, che rende il lavoro con le informazioni molto veloci.
• Il vantaggio dello sviluppo del sistema nell'architettura del client-server. È la possibilità di creare separatamente un software e un server client. Questo vantaggio è la chiave nello sviluppo, e grazie ad esso è possibile ridurre significativamente il tempo richiesto per la fine del progetto. Il codice in esecuzione sul server può essere sviluppato separatamente dal codice del codice client. In questo caso, i componenti della parte del server possono essere condivisi dai componenti del lato client.
• Livello di sicurezza. Le procedure memorizzate possono fungere da strumento di miglioramento della sicurezza. È possibile creare procedure memorizzate che eseguono le operazioni di aggiunta, modifica, eliminazione e visualizzazione elenchi e quindi ottenere il controllo su ciascuno degli aspetti dell'accesso alle informazioni.
• Rafforzamento delle regole del server Lavorare con i dati. Questa è una delle cause più importanti dell'uso del nucleo del database intelligente. Le procedure memorizzate consentono di applicare regole e altre logiche che aiutano a controllare le informazioni abilitate al sistema.

Sebbene lingua SQL. È definito come un server non protetto, SQL Server utilizza parole chiave relative alla gestione dell'esecuzione della procedura. Tali parole chiave vengono utilizzate durante la creazione di procedure che possono essere salvate per la successiva esecuzione. Le procedure memorizzate possono essere applicate al posto dei programmi creati utilizzando lingue standard Programmazione (ad esempio, con o Visual Basic) e eseguire operazioni nel database dATI SQL. Server.

Le procedure memorizzate sono compilate nella prima esecuzione e vengono salvate nella tabella di sistema del database corrente. Quando si compila, sono ottimizzati. In questo caso, il modo migliore per accedere alle informazioni della tabella è selezionato. Tale ottimizzazione tiene conto della posizione effettiva dei dati nella tabella, indici disponibili, il carico della tabella, ecc.

Le procedure memorizzate compilate possono migliorare significativamente le prestazioni del sistema. Vale la pena, tuttavia, vale la pena notare che le statistiche dei dati dal momento della creazione della procedura fino a quando la sua esecuzione non può essere obsoleta e gli indici possono diventare inefficaci. E anche se è possibile aggiornare le statistiche e aggiungere nuovi indici più efficienti, il piano di esecuzione della procedura è già compilato, ovvero la procedura è compilata e, di conseguenza, il metodo di accesso ai dati può cessare di essere efficace. Pertanto, è possibile ricompilare le procedure con ciascuna chiamata.

D'altra parte, la ricompilazione prenderà tempo ogni volta. Pertanto, la questione dell'efficacia del ricompilazione della procedura o della compilazione una tantum della sua implementazione è piuttosto sottile e dovrebbe essere considerata per ciascun caso specifico separatamente.

Le procedure memorizzate possono essere eseguite su una macchina locale o su un sistema SQL Server remoto. Ciò consente di attivare i processi su altre macchine e lavorare non solo con i database locali, ma anche con informazioni su più server.

I programmi di applicazione scritti in una delle lingue di alto livello, come con o Visual Basic .NET, possono anche causare procedure memorizzate, che garantiscono soluzione ottimale sulla distribuzione del carico tra software Parte client e SQL Server.

1.2. Creando procedure memorizzate

Creare istruzioni per la procedura si applica per creare una procedura memorizzata. Il nome della stored procedure può essere lungo fino a 128 caratteri, compresi i simboli # e ##. Sintassi della definizione della procedura:

Creare la qualità del nome PROC [; numero]
[(@ Parametro Type_Data) [\u003d valore___uomolement]]]] [, ... n]

Come
<Инструкции_SQL>

Considera i parametri di questo comando:

• Nome: Procedure_Name - Nome della procedura; deve soddisfare le regole per gli identificatori: la sua lunghezza non può superare i 128 caratteri; Per le procedure locali locali, il nome viene utilizzato prima del nome e per le procedure temporanee globali - segni ##;
• Il numero è un numero intero opzionale utilizzato per raggruppare più procedure sotto un nome;
• @ Parametro Type_Data. - un elenco dei nomi dei parametri di procedura che indicano il tipo di dati appropriato per ciascuno; Tali parametri possono essere fino a 2100. Il valore del parametro è autorizzato a trasmettere NULL. Tutti i tipi di dati possono essere utilizzati con l'eccezione dei tipi di testo, ntext e immagini. È possibile utilizzare il tipo di dati cursore come parametro di uscita (produzione di parole chiave o variazione). I parametri con i dati del cursore possono essere solo parametri di uscita;
• Variare è una parola chiave che determina che il parametro risultante viene utilizzato come parametro risultato (utilizzato solo per il tipo cursore);
• Uscita - suggerisce che il parametro specificato può essere utilizzato come uscita;
• value_to_ux. - Utilizzato nel caso in cui quando si chiama la procedura, manca il parametro; Ci deve essere costante e può includere caratteri maschera (%, _, [,], ^) e valore nullo ;
• Con ricompilazione - parole chiave che mostrano che SQL Server non registrano il piano di procedura nella cache e lo creerà ogni volta quando eseguito;
• Con la crittografia - parole chiave che mostrano che SQL Server crittografano la procedura prima di scrivere alla tabella del sistema Syscomments. Affinché il testo delle procedure crittografate da ripristinare, è necessario rimuovere i cortili dalle tabelle dei sistemi di syscomment corrispondenti a loro dopo la crittografia;
• Per la replica - parole chiave che mostrano che questa procedura viene creata solo per la replica. Questa opzione è incompatibile con le parole chiave con ricompile;
• Come - l'inizio della definizione del testo della procedura;
• <Инструкции_SQL> - Impostato istruzioni consentite SQL, limitato solo dalla dimensione massima della stored procedure - 128 kb. I seguenti sono i seguenti operatori: alter database, alter procedura, modifica della tabella, creare predefiniti, creazione di procedure, alterare trigger, modifiche, crea database, creare regola, creare schema, creare trigger, creazione di visualizzazione, init del disco, ridimensionamento del disco , Drop Default, Drop PROCEDURE, DROP REGOLA, DROP TRIGGER, DISCLUDY Visualizza, database Resotre, Restore Log, Reconfigure, Statistiche di aggiornamento.

Considera un esempio della procedura memorizzata. Svilupperemo una procedura memorizzata che conta e visualizza il numero di copie di libri attualmente nella biblioteca:

Creare procedure count_ex1.
- Procedura per il conteggio del numero di copie dei libri,
- Attualmente nella biblioteca,
- Non i lettori a mano
Come
- Impostare la variabile locale temporanea
Dichiarare @n Int.
Seleziona @n \u003d conteggio (*) da esemplare dove sì_no \u003d "1"
Seleziona @n.
Partire.

Poiché la stored procedure è un componente di database a pieno titolo, come hai già capito, è possibile creare una nuova procedura per il database corrente. Quando si lavora in SQL Server Query Analyzer, l'impostazione del database corrente viene eseguita utilizzando l'operatore Utilizzo, seguito dal nome del database in cui deve essere creata la procedura memorizzata. Selezionare il database corrente può anche essere utilizzato utilizzando l'elenco a discesa.

Dopo aver creato nel sistema memorizzato procedure SQL. Server lo compila e controlla le subroutine eseguibili. Se si verificano problemi, la procedura viene respinta. Prima di ri-trasmettere, l'errore deve essere eliminato.

SQL Server 2000 utilizza la differita denominata denominata denominata denominata denominata denominata Nome Risoluzione, quindi se la stored procedure contiene un ricorso a un ricorso a un altro, non ancora implementato, l'avviso viene visualizzato, ma la chiamata alla procedura inesistente viene conservata.

Se si lascia il sistema con una procedura memorizzata non specificata, mentre si tenta di eseguirlo, l'utente riceverà un messaggio di errore.

È inoltre possibile creare una stored procedure utilizzando SQL Server Enterprise Manager:

Per verificare le prestazioni della procedura memorizzata creata, è necessario andare all'analizzatore di query ed eseguire la procedura per l'esecuzione da parte dell'operatore Exec.<имя процедуры> . I risultati del lancio delle procedure create da noi sono presentati in FIG. quattro.

Fico. 4. Eseguire la procedura memorizzata nell'analizzatore di query

Fico. 5. Risultato della procedura senza un operatore di uscita sullo schermo

1.3. Parametri delle procedure memorizzate

Le procedure memorizzate sono uno strumento molto potente, ma la massima efficienza può essere raggiunta solo rendendoli dinamici. Lo sviluppatore deve essere in grado di trasmettere il valore alla procedura memorizzata con cui funzionerà, cioè i parametri. Di seguito sono riportati i principi di base dell'applicazione dei parametri nelle procedure memorizzate.

• Per la procedura, è possibile definire uno o più parametri.
• I parametri vengono utilizzati come posizioni di archiviazione denominate, proprio come le variabili nei linguaggi di programmazione, come C, Visual Basic .NET.
• Il nome del parametro è necessariamente preceduto dal simbolo @.
• I nomi dei parametri sono locali in quella procedura in cui sono definiti.
• I parametri servono a trasmettere la procedura di informazione durante l'esecuzione. Interferiscono con la riga di comando dopo il nome della procedura.
• Se la procedura ha diversi parametri, sono separati da virgole.
• Per determinare il tipo di informazioni trasmesse come parametro, utilizzare sistemici o tipi personalizzati dati.

Di seguito è riportata la definizione di una procedura con un parametro di input. Cambia il compito precedente e assumeremo non tutte le copie dei libri, ma solo le istanze di un particolare libro. I libri sono decisamente identificati dall'ISBN unico, quindi trasferiremo questo parametro alla procedura. In questo caso, il testo della stored procedure cambierà e avrà il seguente modulo:

Creare procedure count_ex (@isbn varchar (14))
Come
Dichiarare @n Int.
Seleziona @n.
Partire.

Quando si avvia questa procedura, dobbiamo trasmettere il valore del parametro di ingresso (Fig. 6).

Fico. 6. Esecuzione della procedura con il trasferimento del parametro

Per creare più versioni della stessa procedura con lo stesso nome, seguire il nome principale per mettere un punto e virgola e un numero intero. Come eseguire ciò è mostrato nell'esempio seguente, in cui è descritta la creazione di due procedure con lo stesso nome, ma con diversi numeri di versioni (1 e 2). Il numero viene utilizzato per controllare la versione di questa procedura. Se il numero di versione non è specificato, viene eseguita la prima versione della procedura. Questa opzione non è mostrata nell'esempio precedente, ma, tuttavia, è disponibile per la tua applicazione.

Per visualizzare un messaggio che identifica la versione, entrambe le procedure applicano le istruzioni di stampa. La prima versione considera il numero di casi gratuiti e il secondo è il numero di copie sulle mani per questo libro.

Il testo di entrambe le versioni delle procedure è inferiore:

Creare procedure count_ex_all; uno
(@Isbn varchar (14))
- Procedura per il conteggio di copie gratuite di un determinato libro
Come
Dichiarare @n Int.
Seleziona @n \u003d conteggio (*) da esemplare dove isbn \u003d @isbn e yes_no \u003d "1"
Seleziona @n.
--
Partire.
--
Creare procedure count_ex_all; 2.
(@Isbn varchar (14))
- Procedura per il conteggio di copie gratuite di un determinato libro
Come
Dichiarare @ n1 int
Seleziona @ n1 \u003d conteggio (*) da esemplare dove isbn \u003d @isbn e yes_no \u003d "0"
Seleziona @ n1.
Partire.

I risultati della procedura con diverse versioni sono mostrati in FIG. 7.

Fico. 7. Esegui risultati versioni diverse uguale e la stessa procedura memorizzata

Quando si scrive più versioni, è necessario ricordare le seguenti limitazioni: Poiché tutte le versioni della procedura sono compilate insieme, tutte le variabili locali sono considerate comuni. Pertanto, se è richiesto dall'algoritmo di elaborazione, è necessario utilizzare nomi diversi delle variabili interne, che abbiamo fatto, chiamando la variabile @n nella seconda procedura da @ n1.

Abbiamo scritto le procedure non restituire alcun parametro, mostrano semplicemente il numero risultante. Tuttavia, molto spesso dobbiamo ottenere un parametro per un'ulteriore elaborazione. Esistono diversi modi per restituire i parametri dalla stored procedure. Il più semplice è utilizzare i valori di ritorno dell'operatore di ritorno. Questo operatore ti consentirà di restituire un valore numerico. Ma dobbiamo specificare il nome della variabile o dell'espressione, che è assegnato al parametro restituito. I seguenti sono i valori restituiti dall'operatore di ritorno, riservato dal sistema:

Il codice	Valore
0	Va tutto bene
-1.	L'oggetto non è stato trovato
-2.	Errore di tipo di dati
-3.	Il processo era vittima di "deadlock"
-4.	Errore di accesso
-5.	Errore di sintassi
-6.	Qualche errore
-7.	Errore con risorse (in nessun luogo)
-8.	C'era un errore interno corretto
-9.	Il limite di sistema è esaurito
-10.	Violazione incorrente dell'integrità interna
-11.	Stesso
-12.	Distruzione di un tavolo o indice
-13.	Distruzione del database
-14.	Errore dell'attrezzatura

Per quanto non contraddichi il sistema, possiamo restituire solo numeri positivi attraverso questo parametro.

Ad esempio, possiamo modificare il testo della procedura memorizzata Count_ex precedentemente scritta come segue:

Creare la procedura Count_ex2 (@isbn varchar (14))
Come
Dichiarare @n Int.
Seleziona @n \u003d conteggio (*) da esemplare
Dove isbn \u003d @isbn e yes_no \u003d "1"
- Restituire il valore della variabile @n,
- Se il valore della variabile non è definito, restituire 0
Ritorno Coalesce (@n, 0)
Partire.

Ora possiamo ottenere il valore della variabile @n e usarlo per un'ulteriore elaborazione. In questo caso, il valore restituito è assegnato alla procedura più memorizzata e, al fine di analizzarlo, è possibile utilizzare il seguente formato di istruzione chiamata della stored procedure:

Exec.<переменная> = <имя_процедуры> <значение_входных_параметров>

Un esempio di chiamare la nostra procedura è mostrato in FIG. otto.

Fico. 8. Trasmissione del valore restituito della procedura memorizzata della variabile locale

I parametri di input delle procedure memorizzati possono utilizzare il valore predefinito. Questo valore verrà utilizzato se il valore del parametro non è stato specificato quando si chiama la procedura.

Il valore predefinito è impostato tramite il cartello uguale dopo aver descritto il parametro di input e il suo tipo. Considera la procedura memorizzata, che considera il numero di copie dei libri dell'anno di rilascio dell'anno specificato. Anno di sviluppo per impostazione predefinita - 2006.

Creare procedura ex_books_now (@year int \u003d 2006)
- contando il numero di copie di libri dell'anno di rilascio specificato
Come
Dichiarare @n_books Int.
Seleziona @n_books \u003d conte (*) dai libri, esemplare
Dove libri.isbn \u003d exemplar.isbn e yearnizd \u003d @year
Ritorno coalese (@n_books, 0)
Partire.

In fig. 9 mostra un esempio di chiamare questa procedura, indicando il parametro di input e senza di esso.

Fico. 9. Chiamare la procedura memorizzata con il parametro e senza parametro

Tutti gli esempi dell'uso dei parametri nelle procedure memorizzate fornite solo per i parametri di input. Tuttavia, i parametri potrebbero essere l'output. Ciò significa che il valore del parametro dopo il completamento della procedura verrà trasferito a colui che ha causato questa procedura (un'altra procedura, un trigger, un pacchetto di comandi, ecc.). Naturalmente, per ottenere il parametro di uscita, quando la chiamata deve essere specificata come un parametro effettivo non è una costante, ma una variabile.

Si noti che la definizione nella procedura dei parametri come l'output non ti obbliga a utilizzarlo come tale. Cioè, se si specifica una costante come parametro effettivo, gli errori non accadranno e verrà utilizzato come parametro di ingresso regolare.

Per specificare che viene utilizzato il parametro di uscita, viene utilizzata l'istruzione di uscita. Questa parola chiave è registrata dopo aver descritto il parametro. Quando si descrivono i parametri delle procedure memorizzate, è desiderabile impostare i valori dei parametri di uscita dopo l'ingresso.

Considera un esempio di utilizzo dei parametri di uscita. Scriviamo la procedura memorizzata, che per un determinato libro calcola il numero totale dei suoi istanze nella libreria e il numero di istanze libere. Non saremo in grado di utilizzare l'operatore di ritorno di ritorno qui perché restituisce un solo valore, quindi è necessario determinare i parametri di uscita qui. Il testo della stored procedure può assomigliare a questo:

Creare procedure count_books_all.
(@Isbn varchar (14), @ caduta int output, @free int output)
- Procedura per il calcolo del numero totale di discariche del libro dato
- e il numero di esemplari liberi
Come
- contando il numero totale di copie
Seleziona @All \u003d Conte (*) da Exemplar dove isbn \u003d @isbn
Seleziona @Free \u003d Conte (*) da Exemplar dove ISBN \u003d @ISBN e YES_NO \u003d "1"
Partire.

Un esempio di questa procedura è mostrato in FIG. 10.

Fico. 10. Test delle procedure memorizzate con i parametri di uscita

Come accennato in precedenza, al fine di ottenere il valore dei parametri di uscita da analizzare, dobbiamo impostarli con variabili e queste variabili dovrebbero essere descritte dall'operatore di dichiarazione. L'ultimo operatore di uscita ci ha permesso di visualizzare semplicemente i valori ottenuti.

I parametri della procedura potrebbero anche essere variabili di tipo cursore. Per fare ciò, la variabile deve essere descritta come un tipo speciale di variazione dei dati, senza vincolarsi ai tipi di dati del sistema standard. Inoltre, deve essere specificato che questo è un cursore di tipo variabile.

Scriviamo la procedura più semplice che visualizza un elenco di libri nella nostra biblioteca. In questo caso, se non ci sono più libri, portiamo i loro nomi all'interno della procedura stessa, e se l'elenco dei libri supera il numero specificato, li trasmettiamo come cursore con un programma o un modulo chiamante.

Il testo della procedura è il seguente:

Creare la procedura Get3Titles.
(@ MyCursor CurStor Varying Output)
- Titoli di Procedura di stampa con cursore
Come
- Determina localmente tipo variabile Cursore in procedura
Set @MyCursor \u003d Cursore
Per selezionare il titolo distinto
Dai libri.
- Apri il cursore
Apri @MyCursor.
- Descrivi le variabili locali interne
Dichiarare @title varchar (80), @cnt int
--- Installare lo stato iniziale del contatore del libro
Imposta @cnt \u003d 0
- Vai alla prima stringa del cursore
- Mentre ci sono stringhe del cursore,
- cioè, mentre la transizione a nuova stringa Corretta
While (@@ fetch_status \u003d 0) e (@cntt<= 2) BEGIN
Stampa @title.
Fetch accanto da @MyCursor INTE @Title
- Cambia lo stato del contatore del libro
Imposta @cnt \u003d @cnt + 1
Fine.
Se @cnt \u003d 0 Stampa "Nessun libro adatto"
Partire.

Un esempio di chiamare questa procedura memorizzata è mostrata in FIG. undici.

Nella procedura di chiamata, il cursore deve essere descritto come variabile locale. Poi abbiamo chiamato la nostra procedura e gli abbiamo consegnato il nome del cursore di tipo variabile locale. La procedura ha iniziato a funzionare e ci ha portato i primi tre nomi sullo schermo, quindi trasferiva il controllo della procedura del chiamante e che ha continuato l'elaborazione del cursore. Per fare ciò, ha organizzato un ciclo di tipo tempo lungo la variabile globale @@ fetch_status, che traccia lo stato del cursore, quindi nel ciclo ha spostato tutte le altre stringhe del cursore.

Nella finestra di uscita, vediamo un intervallo ingrandito tra le prime tre righe e nomi successivi. Questo intervallo indica che la gestione viene trasferita al programma esterno.

Si noti che la variabile @title, essendo un locale per la procedura, sarà distrutta dopo il completamento del suo funzionamento, quindi nella procedura di chiamata viene nuovamente dichiarata. La creazione e l'apertura di un cursore in questo esempio si verifica nella procedura e la chiusura, la distruzione e l'elaborazione aggiuntiva vengono eseguite nel blocco di comando in cui viene chiamata la procedura.

Il modo più semplice per visualizzare il testo della procedura, cambiarlo o eliminarlo utilizzando l'interfaccia grafica Manager Enterprise Manager. Ma puoi farlo con l'aiuto di sistemi speciali stored Transact-SQL procedure. In Transact-SQL, la visualizzazione della procedura viene eseguita utilizzando la procedura di sistema sp_helptext e la procedura di sistema SP_Help consente di visualizzare le informazioni di controllo sulla procedura. Le procedure di sistema SP_HELPETXT e SP_HELP vengono utilizzate e per visualizzare tali oggetti di database come tabelle, regole e impostazioni predefinite.

Le informazioni su tutte le versioni di una procedura, indipendentemente dal numero, viene visualizzata immediatamente. Eliminazione di diverse versioni di una stored procedure si verifica anche simultaneamente. Nell'esempio seguente, viene mostrata come le versioni 1 e 2 le definizioni di conteggio_ex_all vengono visualizzate quando il suo nome è indicato come il parametro della procedura del sistema SP_HELPETYTYX (figura 12).

Fico. 12. Visualizza la procedura memorizzata del testo utilizzando la procedura memorizzata del sistema

La procedura di sistema SP_Help visualizza le caratteristiche e i parametri della procedura creata come segue:

Nome. Proprietario genere	Creato_datetime.
Conte_books_all. dbo. procedura memorizzata.	2006-12-06 23:15:01.217
Parameter_name. genere Lunghezza prec.	Scala	Param_order Collation.
@Isbn. varchar. 14 14	NULLO	1	Cirillico_general_ci_as.
@tutti. int. 4 10	0	2	NULLO
@gratuito int. 4 10	0	3	NULLO

Prova a decifrare questi parametri da solo. Di cosa stanno parlando?

1.4. Compilare la procedura memorizzata

Il vantaggio di utilizzare procedure memorizzate per eseguire il set di istruzioni Transact-SQL è che sono compilati alla prima esecuzione. Durante il processo di compilazione, l'istruzione Transact-SQL viene convertita dalla presentazione simbolica iniziale nel modulo eseguibile. Qualsiasi oggetto a cui viene anche convertito l'appello della procedura in una vista alternativa. Ad esempio, i nomi delle tabelle vengono convertiti in identificatori di oggetti e nomi di colonne negli identificatori di colonne.

Il piano di esecuzione viene creato nello stesso modo in cui eseguire un'istruzione Transact-SQL. Questo piano contiene, ad esempio, gli indici utilizzati per leggere le righe dalle tabelle a cui viene disegnata la procedura. Il piano di procedura viene salvato nella cache e viene utilizzato per ogni chiamata.

Nota: la dimensione della cache della procedura può essere determinata in modo che possa contenere la maggior parte o tutte disponibili per la procedura. Ciò farà risparmiare il tempo necessario per re-generare un piano di procedura.

1.5. Re-compilazione automatica

In genere, il piano di esecuzione è nelle procedure della cache. Ciò consente di aumentare la produttività quando viene eseguito. Tuttavia, in determinate circostanze, la procedura viene automaticamente ricompilata.

• La procedura viene sempre ricompilata quando viene avviato il server SQL. Questo di solito accade dopo il riavvio del sistema operativo e quando la procedura viene eseguita per la prima volta dopo la creazione.
• Il piano di procedura viene sempre ricompilato automaticamente se l'indice della tabella viene eliminato a cui viene disegnata la procedura. Poiché il piano corrente sembra leggere le righe della tabella all'indice che non esiste più, è necessario creare un nuovo piano di esecuzione. Le richieste di procedura saranno eseguite solo se viene aggiornato.
• Compilare il piano di esecuzione avviene nel caso in cui l'altro utente sia attualmente in esecuzione con questo piano. Per il secondo utente, viene creata una copia individuale del piano di esecuzione. Se la prima copia del piano non era occupata, non sarebbe necessario creare una seconda copia. Quando un utente completa l'esecuzione della procedura, il piano di esecuzione è disponibile nella cache su un altro utente che ha una risoluzione di accesso appropriata.
• La procedura viene automaticamente ricompilata se viene eliminata e aggiornata. Poiché la nuova procedura potrebbe differire dalla versione precedente, tutte le copie del piano di esecuzione nella cache vengono rimosse e il piano viene nuovamente compilato.

SQL Server cerca di ottimizzare le procedure memorizzate memorizzando nella cache delle procedure più intensamente utilizzate. Pertanto, è possibile utilizzare il vecchio piano di esecuzione caricato nella cache invece di un nuovo piano. Per evitare questo problema, eliminare e ricreare una procedura memorizzata o interrompere e riattivare SQL Server. Ciò pulirà la cache della procedura e escluderà la probabilità di lavorare con il vecchio piano di esecuzione.

La procedura può anche essere creata con l'opzione con ricompilazione. In questo caso, si ricompirà automaticamente ogni volta. L'opzione con ricompilazione deve essere applicata nei casi in cui la procedura si riferisce a tabelle molto dinamiche le cui stringhe sono spesso aggiunte vengono eliminate o aggiornate in quanto porta a modifiche significative negli indici definiti per le tabelle.

Se le procedure di ri-compilazione non vengono prodotte automaticamente, può essere eseguita con forza. Ad esempio, se le statistiche utilizzate per determinare l'uso dell'indice in questa query o se è stato creato un nuovo indice, dovrebbe essere effettuato il ricompuro forzato. Per soddisfare la ricomposizione forzata nell'istruzione EXECUTE, viene applicata l'offerta con ricompilazione:

Esegui NAME_NAMES;
Come
<инструкции Transact-SQL>
Con ricompilare

Se la procedura funziona con i parametri che controllano la procedura per la sua esecuzione, è necessario utilizzare l'opzione con ricompilazione. Se le impostazioni della stored procedure possono determinare il miglior percorso di esecuzione, si consiglia di formare un piano di esecuzione durante il funzionamento, anziché crearlo quando la procedura viene per la prima volta per l'uso con tutti i ricorsi successivi.

Nota: a volte è difficile determinare se è necessaria l'opzione con ricompilazione quando si crea una procedura o meno. Se ci sono dubbi, è meglio non applicare questa opzione, poiché la procedura di re-compilazione per ogni adempimento porterà a una perdita di un momento molto prezioso del processore centrale. Se è necessaria una ri-compilazione in futuro Quando si esegue una procedura memorizzata, sarà possibile produrlo aggiungendo la proposta con ricompilazione all'istruzione EXECUTE.

Non è possibile applicare l'opzione con ricompilazione nelle istruzioni di creazione della procedura contenenti l'opzione per la replicazione. Questa opzione viene utilizzata per creare una procedura eseguita durante la replica.

1.6. Nidificazione delle procedure memorizzate

Nelle procedure memorizzate, possono essere contestate altre procedure memorizzate, tuttavia, vi è una restrizione sui livelli di nidificazione. Il livello massimo di nidificazione è 32. Il livello di nidificazione corrente può essere determinato utilizzando una variabile globale @@ nidlevel.

2. Caratteristiche definite dall'utente (UDF)

In MS SQL Server 2000, ci sono molte funzioni predeterminate che consentono una varietà di azioni. Tuttavia, può sempre essere necessario utilizzare alcune funzioni specifiche. Per fare ciò, a partire dalla versione 8.0 (2000), ha l'opportunità di descrivere le funzioni utente (funzioni definite dall'utente, UDF) e memorizzarle sotto forma di un oggetto di database a full-flowedged, insieme a procedure memorizzate, rappresentazioni, ecc.

La comodità dell'utilizzo delle funzioni definite dall'utente è ovvia. A differenza delle procedure memorizzate, le funzioni possono essere incorporate direttamente all'istruzione Seleziona e per utilizzarle entrambi per ottenere valori specifici (nella sezione Selezione) e come origine dati (nella sezione Dal).

Quando si utilizza UDF come fonti di dati, il loro vantaggio rispetto alle rappresentazioni risiede nel fatto che UDF, in contrasto con le viste, potrebbe avere parametri di input con cui è possibile influenzare il risultato della funzione della funzione.

Le funzioni definite dall'utente possono essere tre tipi: funzioni scalari, funzioni in linea e più funzioni operative che restituiscono il risultato della tabella. Considera tutti questi tipi di funzioni.

2.1. Funzioni scalari

Funzioni scalari restituiscono un risultato scalare. Questo risultato può essere qualsiasi tipo sopra descritto, ad eccezione dei tipi di testo, ntext, immagine e timestamp. Questa è la forma più semplice della funzione. La sua sintassi ha il seguente modulo:

Restituisce scalar_typ_data.

Inizio.
body_function.
Ritorno scalare_mapsion.
Fine.

• Il parametro di crittografia è già stato descritto nella sezione dedicata alle procedure memorizzate;
• Schemabindiatura: lega la funzione al diagramma. Ciò significa che è impossibile eliminare le tabelle o le viste in base al quale si basa una funzione, senza eliminare o modificare la funzione stessa. È anche impossibile modificare la struttura di queste tabelle se la parte variabile viene utilizzata dalla funzione. Pertanto, questa opzione ti consente di escludere situazioni quando la funzione utilizza tabelle o viste, e qualcuno, non lo sapendo, cancellato o cambiato;
• Restituisce scalar_typ_data. - descrive il tipo di dati che restituisce una funzione;
• scalar_mapsia. - un'espressione che restituisce direttamente il risultato della funzione. Deve avere lo stesso tipo di quello che è descritto dopo i ritorni;
• la Body_Function è un insieme di istruzioni su Transact-SQL.

Considera esempi di utilizzo delle funzioni scalari.

Creare una funzione che di due numeri interi forniti all'ingresso sotto forma di parametri sceglieranno il più piccolo.

Lascia che la funzione sia così:

Crea funzione min_num (@a int, @b int)
Ritorna int.
Inizio.
Dichiarare @C INT.
Se un.< @b SET @c = @a
Altrimenti set @c \u003d @b
Ritorna @c.
Fine.

Effettueremo ora questa funzione:

Seleziona DBO.MIN_NUM (4, 7)

Di conseguenza, otteniamo il valore di 4.

Puoi applicare questa funzione per trovare il più piccolo tra i valori della colonna della tabella:

Seleziona MIN_LVL, MAX_LVL, MIN_NUM (MIN_LVL, MAX_LVL)
Da lavori.

Creare una funzione che riceverà il parametro Tipo DateTime e restituire la data e l'ora corrispondenti all'inizio del giorno specificato. Ad esempio, se il parametro di input è 20.09.03 13:31, quindi il risultato sarà 20.09.03 00:00.

Crea funzione dbo.daybegin (@dat datetime)
Restituisce la piccola moderazione come.
Inizio.
Converti di ritorno (DateTime, Piano (Converti (Float, @Dat)))
Fine.

Qui, la funzione Convert esegue la conversione del tipo. Innanzitutto, il tipo di data ora è guidato dal tipo di galleggiante. Con questo principale, l'intera parte è il numero di giorni, contando dal 1 ° gennaio 1900 e il periodo frazionale. Il prossimo è arrotondato a un intero più piccolo usando la funzione del pavimento e portando al tipo di data.

Controlla la funzione di azione:

Seleziona dbo.daybegin (GetDate ())

Qui GetDate () è una funzione che restituisce la data e l'ora corrente.

Le funzioni precedenti sono state utilizzate quando si calcolano solo i parametri di input. Tuttavia, è possibile utilizzare i dati memorizzati nel database.

Creare una funzione che riceverà due date come parametri: l'inizio e la fine dell'intervallo di tempo - e calcola i ricavi totali delle vendite per questo intervallo. La data di vendita e la quantità saranno prese dal tavolo di vendita, ei prezzi delle edizioni vendute sono nella tabella dei titoli.

Crea funzione dbo.sumsles (@Datebegin DateTime, @Dateend DateTime)
Restituisce i soldi.
Come
Inizio.
Dichiarare i soldi @sum.
Seleziona @SUM \u003d SUM (T.PRICE * S.QTY)

Ritorno @sum.
Fine.

2.2. Funzioni in linea

Questo tipo di funzioni ritorna non un valore scalare come risultato, e la tabella, o meglio, il set di dati. Questo può essere molto comodo nei casi in cui in diverse procedure, trigger, ecc., Lo stesso tipo di sottosuolo viene spesso eseguito. Quindi, invece di scrivere questa richiesta ovunque, è possibile creare una funzione e usarlo ulteriormente.

Le funzioni di questo tipo sono ancora più utili nei casi in cui è necessario che la tabella restituita dipenda dai parametri di input. Come sapete, le viste non possono avere parametri, quindi solo le funzioni in linea possono risolvere il problema.

La caratteristica della FUCIA in linea è che possono contenere solo una richiesta nel loro corpo. Pertanto, le funzioni di questo tipo sono molto simili ai punti di vista, ma possono inoltre avere parametri di input. Sintassi della funzione in linea:

Crea funzione [proprietario] Funzione del nome
([(Nome del parametro SCALAR_TIP_DATA [\u003d VALUE_P__OMOLEMENT]) [, ... n]])
TABELLA TORNA

Ritorno [(<запрос>)]

Nel definire la funzione è indicato che restituirà la tabella;<запрос> - Questa è la query, il cui risultato sarà il risultato della funzione.

Scriviamo una funzione simile a una funzione scalare dall'ultimo esempio, ma restituire non solo il risultato sommante, ma anche le linee di vendita, inclusa la data di vendita, il nome del libro, il prezzo, il numero di pezzi e la quantità di vendita. Dovrebbero essere selezionate solo quelle vendite che rientrano nel periodo di tempo specificato. Criptiamo il testo della funzione in modo che altri utenti possano usarlo, ma non potevano leggerlo e correggerlo:

Crea funzione SALES_PERIOD (@DateBegin DateTime, @Dateend DateTime)
TABELLA TORNA
Con crittografia
Come
Ritorno (
Seleziona T.Title, T.PRICE, S.QTY, OR_DATE, T.PRICE * S.QTY AS STOIM
Dai titoli t join sless s on t.title_id \u003d s.title_id
Dove ord_date tra @Datebegin e @Dateend
)

Ora chiama questa funzione. Come già accennato, è possibile chiamarlo solo nella sezione Dall'operatore Select:

Seleziona * da Sales_Period ("01.09.94", "13.09.94")

2.3. Più funzioni operative che restituiscono il risultato della tabella

Il primo tipo di funzioni considerato ha permesso di utilizzare molte istruzioni su Transact-SQL, ma solo un risultato scalare restituito. Il secondo tipo di funzioni potrebbe riportare le tabelle, ma il suo corpo rappresenta solo una richiesta. Le funzioni multiplayer che restituiscono un risultato tabulare consentono di combinare le proprietà delle prime due funzioni, cioè, possono contenere nel corpo molte istruzioni su Transact-SQL e restituire una tabella di conseguenza. La sintassi della funzione multifunzionale:

Crea funzione [proprietario] Funzione del nome
([(Nome del parametro SCALAR_TIP_DATA [\u003d VALUE_P__OMOLEMENT]) [, ... n]])
Torna @ NAME_NAMIND_TEULT Tabella
<описание_таблицы>

Inizio.
<тело_функции>
Ritorno.
Fine.

• tavolo<описание_таблицы> - descrive la struttura della tabella restituita;
• <описание_таблицы> - Contiene l'enumerazione delle colonne e delle restrizioni.

Considera ora un esempio che può essere eseguito solo utilizzando le funzioni di questo tipo.

Lascia che ci sia un albero di directory e file che si trovano in loro. Lasciare che l'intera struttura sia descritta nel database sotto forma di tabelle (Fig. 13). Infatti, qui abbiamo una struttura gerarchica per le directory, quindi lo schema contiene il collegamento della tabella delle cartelle con se stessa.

Fico. 13. Struttura del database per descrivere la gerarchia dei file e delle directory

Ora scrivi una funzione che riceverà l'identificatore della directory all'input e in uscita tutti i file memorizzati in esso e in tutte le directory in basso la gerarchia. Ad esempio, se i cataloghi dell'Istituto vengono creati nel catalogo della Faculty1, Faculty2, ecc., Ci sono cataloghi del dipartimento, e in ciascuna delle directory ci sono file, quindi quando si specifica il parametro dell'identificatore della directory, il L'Istituto deve avere un elenco di tutti i file per tutte queste directory. Per ogni file, il nome, la dimensione e la data della creazione devono essere visualizzati.

È impossibile risolvere l'attività utilizzando le funzioni in linea, poiché SQL non è destinato a eseguire query gerarchiche, quindi una richiesta SQL non deve fare qui. Anche la funzione scalare non può essere applicata, dal momento che il risultato dovrebbe essere una tabella. Qui dobbiamo aiutare e venire una funzione multifunzionale che restituisce la tabella:

Crea funzione dbo.getfiles (@folder_id int)
Restituisce la tabella @Files (Nome Varchar (100), Date_Create DateTime, filesIZ INT) come
Inizio.
Dichiarare la tabella @TMP (folder_id int)
Dichiarare @cnt Int.
Inserisci i valori @TMP (@Folder_ID)
Imposta @cnt \u003d 1
Mentre @cnt.<> 0 Iniziare.
Inserisci in @TMP Seleziona FOLDER_ID
Dalle cartelle f join @TMP T su f f.parent \u003d t.folder_id
Dove folder_id da @tmp) dove folder_id da
Set @cnt \u003d @@ rowcount
Fine.
Inserisci in @Files (Nome, Date_Create, Filesise)
Seleziona f.Name, f.Date_Create, f.F.FilesIze
Da file f join folder fl.folder_id \u003d fl.id
Iscriviti @TMP T on fl.id \u003d t.folder_id
Ritorno.
Fine.

Qui nel ciclo nella variabile @TMP aggiunge tutte le directory allegate a tutti i livelli di nidificazione fino a quando le directory nidificate rimangono. Quindi tutti gli attributi dei file necessari nelle directory elencati nella variabile @TMP sono registrati nella variabile dei risultati @Files.

Compiti per lavori indipendenti

È necessario creare e debug di cinque stored procedure dalla seguente lista obbligatoria:

Procedura 1. Un aumento del tempo di consegna delle copie del libro per una settimana se il tempo di consegna corrente si trova entro tre giorni prima della data corrente fino a tre giorni dopo la data corrente.

Procedura 2. Contando il numero di istanze libere di un determinato libro.

Procedura 3. Controllare l'esistenza del lettore con il cognome specificato e la data di nascita.

Procedura 4. Immissione di un nuovo lettore con un controllo della sua esistenza nel database e alla definizione del proprio numero del biglietto del suo nuovo lettore.

Procedura 5. Contando bene in termini monetari per i lettori dei debitori.

Breve descrizione delle procedure

Procedura 1. Aumento dei libri

Per ogni voce nella tabella esemplare, è controllato se la data di consegna del libro cade in un dato intervallo di tempo. Se colpisce, la data di ritorno del libro aumenta per una settimana. Durante l'esecuzione della procedura, è necessario utilizzare le date funzione:

Dateadd (giorno,<число добавляемых дней>, <начальная дата>)

Procedura 2. Contando il numero di istanze libere del libro specificato

Il parametro di input della procedura è un ISBN: una cifra unica del libro. La procedura restituisce 0 (zero) se tutte le istanze di questo libro sono sulle mani dei lettori. La procedura restituisce il valore di N, uguale al numero di copie del libro, che sono attualmente nelle mani dei lettori.

Se i libri con l'ISBN predeterminato non sono in libreria, la procedura restituisce -100 (meno cento).

Procedura 3. Controllo dell'esistenza del lettore con il cognome specificato e la data di nascita

La procedura restituisce il lettore del lettore se il lettore è con tali dati esiste e 0 (zero) altrimenti.

Quando si confronta la data di nascita, è necessario utilizzare la funzione di conversione convert () per convertire la data della nascita: la variabile del carattere Type Varchar (8) utilizzata come parametro di input della procedura per i dati DATATIME, che viene utilizzato nella tabella dei lettori. Altrimenti, l'operazione di confronto durante la ricerca di questo lettore non funzionerà.

Procedura 4. Immissione di un nuovo lettore

La procedura ha cinque ingressi e tre parametri di uscita.

Parametri di input:

• Nome completo con le iniziali;
• Indirizzo;
• Data di nascita;
• Telefono di casa;
• Telefono.

Parametri di uscita:

• Numero del lettore;
• Un segno di se il lettore è stato precedentemente registrato in Libreria (0 - non era, 1 era);
• Il numero di libri elencati dopo il lettore.

Procedura 5. Contando bene in termini monetari per i lettori dei debitori

La procedura funziona con il cursore, che contiene un elenco di numeri dei biglietti del lettore di tutti i debitori. Durante il lavoro, dovrebbe essere creata una tabella temporanea globale ## DOLG, in cui il suo debito totale sarà elencato per ciascun debitore per tutti i libri che durano più lunghi. La compensazione monetaria è calcolata nello 0,5% del prezzo per il libro per il giorno di ritardo.

Procedura per l'esecuzione del lavoro

• copie di schermi (screenshots) confermando le modifiche al database;
• il contenuto delle tabelle del database richiesto per confermare la correttezza del lavoro;
• procedura memorizzata con commenti;
• il processo di avviamento della procedura memorizzata con l'output dei risultati delle prestazioni.

Compiti aggiuntivi

Le seguenti procedure memorizzate aggiuntive sono destinate alle singole attività.

Procedura 6. Contando il numero di libri su una determinata area tematica che attualmente esiste nella biblioteca almeno in un'istanza. L'area del soggetto viene trasmessa come parametro di input.

Procedura 7. Inserire un nuovo libro con un'indicazione delle sue istanze. Quando si immettono le istanze del nuovo libro, non dimenticare di inserire i loro numeri di inventario corretti. Pensa come può essere fatto. Ti ricordo di avere funzioni massime e miniche che consentono di trovare il valore massimo o minimo di qualsiasi attributo numerico agli strumenti di richiesta Seleziona.

Procedura 8. Formazione di un tavolo con un elenco di lettori debitori, cioè quelli che dovrebbero restituire i libri alla biblioteca, ma non ancora restituiti. Nel tavolo risultante, ogni lettore di debitori dovrebbe apparire solo una volta, indipendentemente da quanti libri debbano. Oltre al numero del nome e al numero del lettore nella tabella risultante, è necessario specificare l'indirizzo e l'indirizzo del telefono.

Procedura 9. Cerca una copia gratuita del nome specificato del libro. Se c'è una copia gratuita, la procedura restituisce il numero di inventario dell'istanza; In caso contrario, la procedura restituisce un elenco di lettori che hanno questo libro, indicando la data di ritorno del libro e il telefono del lettore.

Procedura 10. L'output dell'elenco dei lettori che non tiene un singolo libro sulle loro mani al momento. Nell'elenco, specificare il nome e il telefono.

Procedura 11. L'output dell'elenco dei libri che indicano il numero di copie di questo libro nella libreria e il numero di istanze gratuite al momento.

Print Version.

Le procedure memorizzate SQL sono un modulo software eseguibile che può essere memorizzato in vari oggetti. In altre parole, questo è un oggetto in cui le istruzioni SQL sono contenute. Queste procedure memorizzate possono essere eseguite nel client dell'applicazione per ottenere buone prestazioni. Inoltre, tali oggetti vengono spesso invocati da altri scenari o anche da qualsiasi altra partizione.

introduzione

Molti credono di essere simili alle procedure di varie (rispettivamente, tranne MS SQL). Forse questo è vero. Hanno parametri simili, possono produrre valori simili. Inoltre, in alcuni casi entrano in contatto. Ad esempio, vengono combinati con i database DDL e DML, oltre a funzioni utente (nome del codice - UDF).

In realtà, le procedure stored SQL hanno una vasta gamma di vantaggi che li assegnano tra tali processi. Sicurezza, variabilità di programmazione, produttività - Tutte queste attraggono gli utenti che lavorano con database, sempre di più. Il picco della popolarità delle procedure è diminuito per il periodo 2005-2010, quando è stato pubblicato il programma da Microsoft chiamato "SQL Server Management Studio". Con esso, è stato molto più facile lavorare con i database, più pratico e più conveniente. Da anno in anno, questo stava guadagnando popolarità nei programmatori. Oggi è un programma assolutamente familiare che per gli utenti, "comunicante" con i database, stava al pari con "Excel".

Quando si chiama una procedura, viene immediatamente elaborato dal server stesso senza processi non necessari e interventi dell'utente. Successivamente, è possibile esercitare qualsiasi rimozione, esecuzione, cambiamento. Per tutto ciò è risposto dall'operatore DDL, che da sola rende gli effetti più complessi dell'elaborazione dell'oggetto. E tutto ciò accade molto rapidamente e il server non è effettivamente caricato. Tale velocità e prestazione consentono di trasmettere molto rapidamente grandi quantità di informazioni dall'utente al server e viceversa.

Per implementare questa tecnologia, ci sono diversi linguaggi di programmazione. Questi includono, ad esempio, PL / SQL da Oracle, PSQL nei sistemi Interbase e Firebird, nonché il classico "Microsoft" Transact-SQL. Tutti sono destinati a creare ed eseguire procedure memorizzate, che consentono ai gestori di database di grandi dimensioni di utilizzare i propri algoritmi. Ciò è necessario per coloro che gestiscono tali informazioni, possono proteggere tutti gli oggetti dall'accesso non autorizzato di persone terzi e, di conseguenza, creare, modificare o eliminare determinati dati.

Produttività

Questi oggetti del database possono essere programmati in vari modi. Ciò consente agli utenti di scegliere il tipo di metodo utilizzato, che sarà il più appropriato, che consente di risparmiare forza e tempo. Inoltre, viene elaborata la procedura stessa, che evita gli enormi costi di scambio temporaneo tra il server e l'utente. Inoltre, il modulo può essere riprogrammato e modificato nella direzione desiderata in modo assolutamente in qualsiasi momento. Vale particolarmente la pena notare la velocità con cui è avviata l'avvio della procedura SQL memorizzata: questo processo si verifica più velocemente di altri simili, il che lo rende conveniente e universale.

Sicurezza

Questo tipo di elaborazione delle informazioni è diversa da processi simili in quanto garantisce una maggiore sicurezza. Ciò è garantito a causa del fatto che l'accesso di altri utenti alle procedure può essere eliminato interamente. Ciò consentirà all'amministratore di condurre le operazioni con loro indipendentemente, senza paura di intercettare informazioni o accesso non autorizzato al database.

Trasferimento dati

La relazione tra la procedura SQL memorizzata e l'applicazione client è di utilizzare i parametri e i valori restituiti. Quest'ultimo non può essere trasferito ai dati nella stored procedure, tuttavia, queste informazioni (principalmente sulla richiesta dell'utente) e elaborate per SQL. Dopo che la procedura memorizzata ha completato il suo lavoro, si riferisce ai pacchetti di dati indietro (ma, ancora, se lo si desidera) all'applicazione che ha causato l'utilizzo di vari metodi che possono essere implementati come chiamata alla procedura memorizzata SQL e restituire, ad esempio:

Trasmissione dei dati utilizzando il parametro Tipo di uscita;

Trasferimento dei dati utilizzando l'operatore di ritorno;

Trasmissione dei dati utilizzando l'operatore di selezione.

E ora lo scopriremo come questo processo guarda dall'interno.

1. Creazione di una procedura memorizzata in SQL

È possibile creare una procedura in MS SQL (Gestione Studio). Dopo aver creato la procedura, verrà elencato in un nodo del database programmabile in cui la procedura di creazione viene eseguita dall'operatore. Per eseguire le procedure SQL memorizzate utilizzare il processo di esecuzione che contiene il nome dell'oggetto stesso.

Durante la creazione della procedura, il suo nome appare prima, dopo il quale uno o più parametri assegnati ad esso. I parametri possono essere opzionali. Dopo i parametri, ovvero, il corpo della procedura verrà scritto, è necessario effettuare alcune operazioni necessarie.

Il fatto è che il corpo può avere variabili locali situate in esso, e queste variabili sono locali e relative alle procedure. In altre parole, possono essere considerati solo all'interno del corpo della procedura di Microsoft SQL Server. Le procedure memorizzate in questo caso sono considerate locali.

Pertanto, per creare una procedura, abbiamo bisogno del nome della procedura e di almeno un parametro come corpo della procedura. Si prega di notare che un'opzione eccellente in questo caso è la creazione e l'esecuzione della procedura con il nome dello schema nel classificatore.

Il corpo della procedura potrebbe avere qualsiasi tipo di esempio, come la creazione di una tabella, inserire una o più righe di tabella, impostare il tipo e il carattere del database, e così via. Ciononostante, il corpo della procedura limita l'esecuzione di alcune operazioni in esso. Alcune delle importanti restrizioni sono elencate di seguito:

Il corpo non dovrebbe creare qualsiasi altra procedura memorizzata;

Il corpo non dovrebbe creare una falsa comprensione dell'oggetto;

Il corpo non dovrebbe creare trigger.

2. Installazione della variabile nel corpo della procedura

Puoi rendere le variabili locali per la procedura del corpo, e quindi saranno esclusivamente all'interno del corpo della procedura. Buona pratica è creare variabili all'inizio del corpo della stored procedure. Ma puoi anche impostare le variabili ovunque nel corpo di questo oggetto.

A volte è possibile vedere che diverse variabili sono installate in una riga e ciascun parametro variabile è separato da una virgola. Si noti inoltre che la variabile ha un prefisso @. Nel corpo della procedura è possibile impostare la variabile dove vuoi. Ad esempio, la variabile @ Name1 può essere dichiarata più vicina alla fine del corpo della procedura. Per assegnare il valore della variabile annunciata, viene utilizzata una serie di dati personali. A differenza della situazione quando ha dichiarato più di una variabile in una riga, viene utilizzata solo una serie di dati personali in tale situazione.

Spesso, gli utenti fanno una domanda: "Come assegnare più valori in un operatore nel corpo della procedura?" Bene. La domanda è interessante, ma è molto più facile fare ciò che pensi. Risposta: usare vapore come "Seleziona var \u003d valore". Puoi usare queste coppie, separando la propria virgola.

In un'ampia varietà di esempi, le persone mostrano la creazione di una semplice procedura memorizzata ed eseguendola. Tuttavia, la procedura può adottare tali parametri che il processo che ha causato che avrà i valori vicini ad esso (ma non sempre). Se corrispondono, i processi corrispondenti iniziano all'interno del corpo. Ad esempio, se crei una procedura che prenderà la città e la regione dal chiamante e restituirà i dati su quanti autori si riferiscono alla città e alla regione corrispondente. La procedura richiederà la tabella degli autori del database, ad esempio, i pub, per eseguire questo conteggio degli autori. Per ottenere questi database, ad esempio, Google carica lo script SQL dalla pagina SQL2005.

Nell'esempio precedente, la procedura prende due parametri, che in inglese sarà condizionatamente chiamato @State e @City. Il tipo di dati corrisponde al tipo definito nell'applicazione. Il corpo della procedura ha variabili interne @totalathors (Autori totali) e questa variabile viene utilizzata per visualizzare la loro quantità. Viene visualizzata la seguente sezione di selezione, che conta tutto. Infine, il valore calcolato viene visualizzato nella finestra di uscita utilizzando l'istruzione Stampa.

Come eseguire una procedura memorizzata in SQL

Esistono due modi per eseguire la procedura. Il primo percorso mostra il passaggio dei parametri poiché l'elenco separato dalla commo viene eseguito dopo il nome della procedura. Supponiamo di avere due valori (come nell'esempio precedente). Questi valori vengono raccolti utilizzando le variabili dei parametri della procedura @State e @City. In questo metodo per trasmettere parametri, l'ordine è importante. Questo metodo è chiamato il trasferimento ordinario degli argomenti. Nel secondo metodo, i parametri sono già stati nominati direttamente, e in questo caso l'ordine non è importante. Questo secondo metodo è noto come trasferimento di argomenti denominati.

La procedura può essere in qualche modo deviata dal tipico. Stilutamente, come nell'esempio precedente, ma solo qui i parametri vengono spostati. Cioè, il parametro @City viene memorizzato prima e @State è memorizzato accanto al valore predefinito. Il parametro predefinito è solitamente separato separatamente. Le procedure memorizzate SQL passano come semplicemente parametri. In questo caso, a condizione che il parametro UT sostituisce il "CA" predefinito. Nella seconda esecuzione, solo un valore dell'argomento per il parametro @City passa e il parametro @State prende il "CA" predefinito. I programmatori esperti consigliano che tutte le variabili per impostazione predefinita siano più vicine alla fine dell'elenco dei parametri. Altrimenti, l'esecuzione non è possibile, e quindi è necessario lavorare con il trasferimento di argomenti denominati, che è più lungo e più difficile.

4. Procedure SQL Server memorizzate: Metodi di ritorno

Esistono tre modi importanti per inviare dati nella procedura memorizzata causata. Sono elencati di seguito:

Restituire il valore della procedura memorizzata;

L'output delle procedure memorizzate;

La scelta di una delle procedure memorizzate.

4.1 Ritorno dei valori della procedura memorizzati SQL

In questa tecnica, la procedura assegna il valore della variabile locale e lo restituisce. La procedura può anche restituire direttamente un valore costante. Nell'esempio seguente, abbiamo creato una procedura che restituisce il numero totale di autori. Se si confronta questa procedura con quelle precedenti, è possibile vedere che il valore di stampa è sostituito dal contrario.

Ora vediamo come eseguire la procedura e visualizzare il valore restituito ad esso. L'esecuzione della procedura richiede l'istituzione di una variabile e di stampa, che viene eseguita dopo l'intero processo. Si prega di notare che invece dell'istruzione di stampa, è possibile utilizzare l'operatore Select, ad esempio, selezionare @retValue, oltre a EXTIMEVALUE.

4.2 Uscita dei parametri della procedura memorizzata SQL

Il valore di risposta può essere utilizzato per restituire una variabile che abbiamo visto nell'esempio precedente. L'utilizzo del parametro di uscita consente alla procedura di inviare uno o più valori variabili per il chiamante. Il parametro di uscita è indicato come se da questa parola chiave "output" durante la creazione di una procedura. Se il parametro è specificato come parametro di uscita, l'oggetto della procedura deve assegnare un valore ad esso. Procedure stored SQL, esempi di cui si possono vedere di seguito, nel qual caso vengono restituiti con le informazioni finali.

Nel nostro esempio, ci saranno due nomi di output: @totalathors e @totalnocontract. Sono specificati nell'elenco dei parametri. Queste variabili assegnano valori all'interno del corpo della procedura. Quando utilizziamo i parametri di uscita, il chiamante può vedere il valore impostato all'interno del corpo della procedura.

Inoltre, nello scenario precedente, vengono annunciate due variabili per vedere i valori che impostare le procedure memorizzate MS SQL Server nel parametro di output. Quindi la procedura viene eseguita inviando il valore normale del parametro "CA". I seguenti parametri vengono emessi e, pertanto, le variabili dichiarate vengono trasmesse nel modo prescritto. Si noti che quando si passa variabili, la parola chiave di uscita è anche impostata qui. Dopo che la procedura ha esito positivo, i valori restituiti dai parametri di uscita vengono visualizzati sulla finestra dei messaggi.

4.3 Selezione di una delle procedure memorizzate SQL

Questa tecnica viene utilizzata per restituire il set di valori come tabella di dati (recordset) nella procedura storerata che causa. In questo esempio, la procedura SQL memorizzata con i parametri @AuthID richiede la tabella "Autori" filtrando le voci restituite utilizzando questo parametro @authid. L'istruzione Seleziona decide che deve essere restituita alla procedura memorizzata dalla chiamata. Quando si esegue la procedura memorizzata, l'AuthID viene trasmesso. Tale procedura qui restituisce sempre una sola voce o uno. Ma la procedura memorizzata non ha restrizioni al ritorno di più di un record. Spesso, è possibile trovare esempi in cui il reso dei dati utilizzando parametri selezionati con la partecipazione di variabili calcolati si verifica fornendo diversi valori di risultato.

Finalmente

La procedura memorizzata è un modulo software abbastanza serio che restituisce o trasmettendo, oltre a installare le variabili necessarie grazie all'applicazione client. Poiché la procedura memorizzata viene eseguita sul server stesso, è possibile evitare lo scambio di dati in enormi volumi tra il server e l'applicazione client (per alcuni calcoli). Ciò consente di ridurre il carico sul SQL Server, che, ovviamente, va alla mano dei loro titolari. Una delle sottospecie sono memorizzate le procedure SQL, ma il loro studio è necessario per coloro che sono impegnati nella creazione di impressioni database. C'è anche un grande numero, anche un numero enorme di sfumature che può essere utile quando si studiano procedure memorizzate, ma è necessario di più per coloro che hanno intenzione di impegnarsi strettamente nella programmazione, incluso professionalmente.

Scopo del lavoro - Impara come creare e utilizzare le procedure memorizzate sul server del database.

1. Studi di tutti gli esempi, analisi dei risultati della loro esecuzione nell'utilità SQL Server Management Studio. Controllo della presenza di processi creati nel database corrente.

2. Eseguire tutti gli esempi e le attività nel corso del lavoro di laboratorio.

3. Eseguire singole attività per opzioni.

Spiegazione del lavoro

Per padroneggiare le procedure memorizzate di programmazione, utilizzare il database quando il database del nome Db_books.che è stato creato in ilboratorio numero 1. Durante l'esecuzione di esempi e attività, pagare per la conformità con i nomi del database, tabelle e altri oggetti del progetto.

Procedura di archiviazione Esistono un insieme di comandi costituiti da uno o più operatori SQL o funzioni e memorizzati nel database in forma di compilazione.

Tipi di procedure memorizzate

Le procedure memorizzate del sistema sono destinate a svolgere varie azioni amministrative. Quasi tutte le azioni di amministrazione del server vengono eseguite con la loro aiuto. Si può dire che le procedure memorizzate del sistema sono un'interfaccia che fornisce il lavoro con le tabelle di sistema. Le procedure memorizzate del sistema hanno il prefisso SP_, vengono memorizzate nel database di sistema e possono essere causate nel contesto di qualsiasi altro database.

Le procedure memorizzate personalizzate implementano determinate azioni. Procedure memorizzate: un oggetto database a full-hadged. Di conseguenza, ciascuna procedura memorizzata si trova in un database specifico, dove viene eseguita.

Le procedure memorizzate temporanee esistono solo un po ', dopo di che vengono automaticamente distrutte dal server. Sono suddivisi in losal e globali. Le procedure memorizzate temporanee locali possono essere causate da quel composto in cui viene creata. Durante la creazione di una procedura tale, ha bisogno di dare un nome a partire da un simbolo #. Come tutti gli oggetti temporanei, le procedure memorizzate di questo tipo vengono eliminate automaticamente quando l'utente è disconnesso, riavviare o interrompere il server. Le procedure memorizzate temporanee globali sono disponibili per qualsiasi connessione server su cui vi è la stessa procedura. Per determinarlo, è sufficiente dargli un nome a partire da caratteri ##. Queste procedure vengono eliminate quando si riavvia o si fermano il server, nonché durante la chiusura della connessione, nel contesto di cui sono state create.

Creazione, modifica delle procedure memorizzate

La creazione della procedura memorizzata prevede la decisione dei seguenti aliquote: diritti di accesso alla pianificazione. Durante la creazione di una procedura memorizzata, occorre ricordare che avrà gli stessi diritti di accesso agli oggetti del database, che ha creato il suo utente; Determinazione dei parametri delle procedure di archivio, le procedure memorizzate possono avere parametri di input e output; Sviluppo della procedura memorizzata del codice. Il codice procedura può contenere una sequenza di qualsiasi comando SQL, inclusa la chiamata ad altre procedure memorizzate.

La sintassi dell'operatore di creare una nuova o modifica della procedura memorizzata disponibile nelle designazioni MS SQL Server:

(Creare | Alter) Proc [EDURE] NAME_NAME [; Numero] [(@ Parameter_Name Type_Data) [Varying] [\u003d Default] [Output]] [, ... n] [con (ricompile | crittografia | Ricompile, Crittografia)] [Per la replica] come SQL_ Operator [... n]

Considera i parametri di questo comando.

Usando SP_, #, ## I prefissi creati dalla procedura possono essere definiti come sistemici o temporanei. Come si può vedere dalla sintassi dei comandi, non è consentito specificare il nome del proprietario a cui è stata creata la procedura, nonché il nome del database, dove deve essere posizionato. Pertanto, per posizionare la procedura memorizzata creata in un database specifico, è necessario eseguire il comando Crea procedura nel contesto di questo database. Quando si contatta il corpo della stored procedure agli oggetti dello stesso database, è possibile utilizzare nomi abbreviati, I.e. Senza specificare il nome del database. Quando è necessario fare riferimento agli oggetti situati in altri database, specificando il nome del database deve.

Per trasferire i dati di input e output nella procedura memorizzata creata, i nomi dei parametri devono iniziare con il simbolo @. In una procedura memorizzata, è possibile impostare molti parametri separati da virgole. Nel corpo della procedura, non dovrebbe essere applicata le variabili locali, i cui nomi sono coincisori con i nomi dei parametri di questa procedura. Per determinare il tipo di questi parametri della stored procedure, qualsiasi tipo di dati SQL è adatto, incluso dall'utente definito. Tuttavia, il tipo di dati del cursore può essere utilizzato solo come parametro di uscita della stored procedure, I.e. Con la produzione di parole chiave.

La presenza della parola chiave di uscita indica che il parametro corrispondente è progettato per restituire i dati dalla stored procedure. Tuttavia, ciò non significa che il parametro non sia adatto per la trasmissione di valori nella procedura memorizzata. Specificare la parola chiave di uscita indica il server quando si esce dalla procedura memorizzata per assegnare il valore corrente del parametro variabile locale, che è stato specificato quando la procedura è chiamata il valore del parametro. Si noti che quando si specifica l'uscita della parola chiave, il valore del parametro corrispondente quando si chiama la procedura può essere specificata solo utilizzando una variabile locale. Non è consentito utilizzare espressioni o costanti ammessi per i parametri convenzionali. La parola chiave variabile viene utilizzata in combinazione con il parametro di uscita avente un tipo di cursore. Determina che il parametro di uscita sarà il set risultante.

La parola chiave predefinita è un valore che accetterà il parametro predefinito corrispondente. Pertanto, quando viene chiamata la procedura, non è possibile specificare il valore del parametro corrispondente.

Poiché il server memorizza la cache del piano di esecuzione della query e il codice compilato, dopo la chiamata successiva, le procedure saranno utilizzate già pronte. Tuttavia, in alcuni casi, è ancora necessario ricompilare il codice della procedura. Specifica della parola chiave Ricompili prescrive il sistema per creare un piano di procedura memorizzato per ciascuna chiamata.

Il parametro per la replica è richiesto durante la replica dei dati e l'inclusione della procedura memorizzata creata come articolo di pubblicazione. La parola chiave crittografia indica al server di crittografare il codice della stored procedure, che può fornire protezione contro l'uso di algoritmi del copyright che implementano il lavoro della procedura memorizzata. La parola chiave come si trova all'inizio del corpo della stored procedure. Nel corpo della procedura, quasi tutti i comandi SQL possono essere applicati, le transazioni dichiarate, bloccano il blocco e chiamare altre procedure memorizzate. L'uscita della procedura memorizzata può essere eseguita utilizzando il comando di ritorno.

Rimozione della procedura memorizzata

Procedura di rilascio (nome) [, ... n]

Eseguendo procedure memorizzate

Per eseguire la procedura memorizzata, il comando viene utilizzato: [[EXEC [UTE] NAME_NAME [; Numero] [[@ Parameter_name \u003d] (Valore | @_Name_Name) [Uscita] | [Predefinito]] [, ... n]

Se la chiamata alla stored procedure non è l'unico comando nel pacchetto, è richiesta la presenza del comando EXECUTE. Inoltre, questo comando è tenuto a chiamare la procedura dal corpo di un'altra procedura o trigger.

Utilizzando la parola chiave di uscita quando si chiama la procedura viene risolta solo per i parametri che sono stati annunciati durante la creazione di una procedura con un'uscita di parole chiave.

Quando si chiama una procedura, la parola chiave predefinita è specificata per il parametro, verrà utilizzato il valore predefinito. Essenzialmente, l'impostazione predefinita della parola specificata è consentita solo per tali parametri per i quali è definito il valore predefinito.

Dalla sintassi del comando EXECUTE, è chiaro che i nomi dei parametri possono essere omessi quando viene chiamata la procedura. Tuttavia, in questo caso, l'utente deve specificare i valori per i parametri nello stesso ordine, in cui sono stati elencati durante la creazione di una procedura. Assegna il valore predefinito al parametro semplicemente passandolo se elencato, è impossibile. Se i parametri sono necessari per i quali è definito il valore predefinito, un'indicazione del nome del parametro sufficientemente esplicita durante la chiamata della procedura memorizzata. Inoltre, in tal modo, è possibile elencare i parametri e i loro valori in un ordine arbitrario.

Si noti che quando si chiama la procedura, i nomi dei parametri con valori o solo i valori senza il nome del parametro sono indicati. La loro combinazione non è consentita.

Utilizzare il reso nella procedura memorizzata

Consente di uscire dalla procedura in qualsiasi momento sulla condizione specificata e consente anche di trasferire il risultato della procedura da parte del numero con cui è possibile giudicare la qualità e la correttezza della procedura. Un esempio di creazione di una procedura senza parametri:

Creare procedure Count_books come conteggio del conteggio (codice_book) dai libri vanno

Esercizio 1.

Exec count_books.

Controlla il risultato.

Un esempio di creazione di una procedura con parametro di input:

Creare la procedura Count_books_pages @Cout_pages come int come Select Count (code_book) dai libri in cui pagine\u003e \u003d @count_pages vai

Attività 2.. Creare questa procedura Nella sezione Procedure memorizzate dei dati DB_Books tramite l'utilità SQL Server Management Studio. Eseguilo usando il comando

Exec count_books_pages 100.

Controlla il risultato.

Un esempio di creazione di una procedura con parametri di input:

Creare procedure count_books_title @count_pages come int, @title come char (10) come conteggio del conteggio (codice_book) dai libri in cui pagine\u003e \u003d @count_pages e title_book come @title Go

Attività 3. Creare questa procedura nella sezione Procedure memorizzate dei dati DB_Books tramite l'utilità SQL Server Management Studio. Eseguilo usando il comando

Exec count_books_title 100, "p%"

Controlla il risultato.

Un esempio di creazione di una procedura con parametri di input e parametro di output:

Creare procedure count_books_itogo @count_pages int, @title char (10), @itogo int output come seleziona @itogo \u003d conteggio (code_book) dai libri dove pagine\u003e \u003d @count_pages e title_book come @title Go

Attività 4. Creare questa procedura nella sezione Procedure memorizzate dei dati DB_Books tramite l'utilità SQL Server Management Studio. Esegui usando il set di comando:

SQL\u003e Dichiarare @Q come int exec count_books_itogo 100, "p%", @Q output Select @Q

Controlla il risultato.

Un esempio di creazione di una procedura con parametri di input e ritorno:

Creare il check -name della procedura @Param Int come se (Selezionare Nome_Author da Autors dove code_author \u003d @param) \u003d "Pushkin A.S." Ritorna 1 altrimenti Return 2

Attività 5. Creare questa procedura nella sezione Procedure memorizzate dei dati DB_Books tramite l'utilità SQL Server Management Studio. Eseguilo usando i comandi:

Dichiarare @return_status int exec @return_status \u003d CheckName 1 Selezionare "Stato restituire" \u003d @return_status

Un esempio di creazione di una procedura senza parametri per aumentare il valore del campo chiave nella tabella Acquisti 2 volte:

Creare Proc Update_Proc come acquisto di aggiornamenti Set code_purchase \u003d code_purchase * 2

Attività 6. Creare questa procedura nella sezione Procedure memorizzate dei dati DB_Books tramite l'utilità SQL Server Management Studio. Eseguilo usando il comando

Exec update_proc.

Un esempio di una procedura con un parametro di input per tutte le informazioni sugli autori specifici:

Crea Proc Select_Author @k Char (30) come Select * da Autors in cui NAME_AUTHOR \u003d @K

Attività 7.

Exec Select_Author "Pushkin A.S." o selezionare_author @ k \u003d "Pushkin A.S." o Exec Select_Author @ k \u003d "Pushkin A.S."

Un esempio di creazione di una procedura con un parametro di input e valore predefinito per aumentare il valore del campo chiave nella tabella Acquisti in un numero specificato di volte (per impostazione predefinita 2 volte):

Creare Proc Update_proc @p int \u003d 2 come acquisto di aggiornamenti Set code_purchase \u003d code_purchase * @p

La procedura non restituisce dati.

Attività 8. Creare questa procedura nella sezione Procedure memorizzate dei dati DB_Books tramite l'utilità SQL Server Management Studio. Eseguilo usando i comandi:

Exec Update_proc 4 o Exec update_proc @p \u003d 4 o exec update_proc - Viene utilizzato il valore predefinito.

Un esempio di creazione di una procedura con parametri di input e output. Creare una procedura per determinare il numero di ordini eseguiti durante il periodo specificato:

Crea Proc Count_Purchases @ D1 SmallDateTime, @ D2 SmallDateTime, @C int output come seleziona @ c \u003d conteggio (code_purchase) dagli acquisti in cui Date_Order tra @ D1 e @ D2 Set @C \u003d IsNull (@C, 0)

Attività 9. Creare questa procedura nella sezione Procedure memorizzate dei dati DB_Books tramite l'utilità SQL Server Management Studio. Eseguilo usando i comandi:

Dichiarare @ C2 Int Exec Count_Purchases '01 - Jun- 2006 ', '01 - Jul- 2006', @ uscita C2 Seleziona @ c2

Opzioni di K. lavoro di laboratorio №4

Generale. Nell'utilità SQL Server Management Studio da creare nuova pagina Per il codice ("Crea un pulsante di query"). Fare a livello di programmazione un database DB_Books attivo utilizzando l'operatore d'uso. Creare procedure memorizzate utilizzando gli operatori di procedura Creazione e determinare in modo indipendente i nomi delle procedure. Ogni procedura si esibirà da una query SQL eseguita nel secondo lavoro di laboratorio. Inoltre, il codice query SQL deve essere modificato in modo che possano essere trasmessi ai valori del campo per i quali viene eseguita la ricerca.

Ad esempio, l'attività iniziale e la richiesta nel lavoro di laboratorio numero 2:

/ * Scegli tra i provider di libri di riferimento (consegna delle consegne) nomi, telefoni e locanda (nome_company, telefono e settori della locanda), il cui nome della società (Nome_company field) "OAO World".

Seleziona nome_company, telefono, locanda dalle consegne in cui NAME_COMPANY \u003d "OAO WORLD"

* / - Verrà creata questa procedura di lavoro:

Crea Proc Select_Name_Company @comp Char (30) come Select Name_Company, Telefono, Inn Dalle consegne Where_Company \u003d @comp

-L'ordine della procedura è utilizzata dal comando:

Exec Select_Name_Company "OJSC World"

Elenco delle attività

Nell'utilità SQL Server Management Studio, creare un nuovo programma. Programmaticamente effettuare un database individuale attivo creato in il numero di lavoro di laboratorio numero 1, utilizzando l'operatore d'uso. Creare procedure memorizzate utilizzando gli operatori di procedura Creazione e determinare in modo indipendente i nomi delle procedure. Ogni procedura eseguirà da una query SQL presentata sotto forma di compiti separati da parte delle opzioni.

opzione 1

1. Ritirare un elenco di dipendenti che hanno almeno un figlio.

2. Ritirare un elenco di bambini che hanno dato regali nel periodo specificato.

3. Ritirare un elenco di genitori che hanno minori bambini.

4. Ritirare informazioni sui regali con un costo superiore al numero specificato ordinato per data.

Opzione 2.

1. Elenco degli strumenti con tipo specificato.

2. Rimuovere il numero di dispositivi riparati e il costo totale delle riparazioni nella procedura guidata specificata.

3. Portare l'elenco dei proprietari degli strumenti e il numero dei loro ricorsi, ordinati per il numero di ricorsi decrescenti.

4. Visualizza le informazioni sui maestri con una scarica più del numero specificato o con una data di ricezione inferiore alla data specificata.

Opzione 3.

2. Per visualizzare un elenco di codici di vendita per i quali i colori venduti sono più del numero specificato.

3. Rimuovere la vendita, l'importo, il venditore e il fiore nel codice di vendita specificato.

4. Visualizzare un elenco di colori e grado per i fiori con un'altezza del numero o fioritura specificati.

Opzione 4.

1. Per prelevare un elenco di farmaci con l'indicazione specificata per l'uso.

2. Per visualizzare un elenco di consegne, che ha venduto più del numero specificato dello stesso farmaco.

3. Per prelevare la data di consegna, l'importo, il titolo della testa dal fornitore e il nome del farmaco sul codice di ricezione superiore al numero specificato.

Opzione 5.

2. Visualizzare un elenco di apparecchiature scritte per la ragione indicata.

3. Per prelevare la data di ricezione, il nome dell'apparecchiatura, il nome del responsabile e la data della cancellazione per l'apparecchiatura cancellata al periodo specificato.

4. Visualizzare l'elenco delle apparecchiature con il tipo specificato o con la data di ricezione più di un determinato valore.

Opzione 6.

1. Elenco dei piatti con peso più del numero specificato.

2. Ritirare un elenco di prodotti, nel titolo di cui viene trovato il frammento specificato della parola.

3. Rimuovere il volume del prodotto, il nome del piatto, il nome del prodotto con il codice del piatto dal specificato significato iniziale Su valore definitivo definito.

4. Visualizzare il processo di cottura e il nome del piatto con la quantità di carboidrati è maggiore di un determinato valore o la quantità di calorie è maggiore del valore specificato.

Opzione 7.

1. Ritirare un elenco di dipendenti con questo post.

3. Per prelevare la data di registrazione, il tipo di documento, il nome del Registrar e il nome dell'organizzazione per i documenti registrati nel periodo specificato.

4. Visualizza un elenco di documenti registrati con un tipo specifico di documento o la data di registrazione è maggiore del valore specificato.

Opzione 8.

1. Per ritirare un elenco di dipendenti con il motivo specificato per il licenziamento.

3. Per prelevare la data di registrazione, il motivo del licenziamento, il nome del dipendente per i documenti registrati nel periodo specificato.

Opzione 9.

1. Ritirare un elenco di dipendenti che sfruttano la vacanza del tipo specificato.

2. Wire un elenco di documenti nella data di registrazione nel periodo specificato.

3. Per prelevare la data di registrazione, il tipo di vacanza, il fio del dipendente per i documenti registrati nel periodo specificato.

4. Visualizzare un elenco di documenti registrati con un codice documento nell'intervallo specificato.

Opzione 10.

1. Ritirare un elenco di dipendenti con questo post.

2. Ritirare un elenco di documenti in cui si trova il frammento specificato della parola.

3. Per prelevare la data di registrazione, il tipo di documento, il fio del mittente e il nome dell'organizzazione per i documenti registrati nel periodo specificato.

4. Visualizzare un elenco di documenti registrati con il tipo di documento specificato o con il codice del documento inferiore a un determinato valore.

Opzione 11.

1. Ritirare un elenco di dipendenti nominati alla posizione specificata.

2. Wire un elenco di documenti nella data di registrazione nel periodo specificato.

3. Per prelevare la data di registrazione, la posizione, il nome completo per i documenti registrati nel periodo specificato.

4. Visualizzare un elenco di documenti registrati con un codice documento nell'intervallo specificato.

Opzione 12.

3. Ritirare un elenco di persone che elegano attrezzature per assumere e il numero dei loro ricorsi, ordinati per il numero di ricorsi discendenti.

Opzione 13.

1. Visualizzare l'elenco delle apparecchiature con il tipo specificato. 2. Visualizzare l'elenco delle apparecchiature che hanno scritto un dipendente specifico.

3. Visualizzare la quantità di apparecchiature scritte raggruppate per apparecchiature.

4. Visualizza informazioni sui dipendenti con la data di occupazione più di una data specifica.

Opzione 14.

1. Visualizzare l'elenco dei fiori con il tipo di foglio specificato.

2. Wire Un elenco di codici di reddito per i quali i colori venduti sono più di un determinato valore.

3. Rimuovere la data di ricezione, la quantità, i nomi del fornitore e dei colori su un codice specifico del fornitore.

4. Elenco dei colori e varietà per i fiori con un'altezza di più di un certo numero o fioritura.

Opzione 15.

1. Per ritirare un elenco di clienti che sono arrivati \u200b\u200bai numeri durante il periodo specificato.

2. Ritirare una quantità totale di pagamenti per ciascun client.

3. Rimuovere la data di arrivo, il tipo di stanza, i nomi dei clienti registrati nel periodo specificato.

4. Wire un elenco di clienti registrati in un determinato tipo.

Opzione 16.

1. Visualizzare l'elenco delle apparecchiature con il tipo specificato.

2. Pubblica un elenco di attrezzature che hanno preso un certo cliente in affitto.

3. Ritirare un elenco di persone che rispettano l'attrezzatura per assumere e il numero dei loro ricorsi ordinati per il numero di ricorsi decrescenti.

4. Disporre le informazioni ordinate per indirizzi.

Opzione 17.

1. Per visualizzare un elenco di valori con i costi di approvvigionamento più di un determinato valore o un periodo di garanzia più del numero specificato.

2. Per portare un elenco di posizioni dei valori materiali, nel titolo di cui viene trovata la parola specificata.

3. Rimuovere il valore del valore dei valori con il codice nell'intervallo specificato.

4. Visualizza un elenco di persone finanziariamente responsabili con una data di occupazione nell'intervallo specificato.

Opzione 18.

1. Visualizzare un elenco di lavori di riparazione eseguiti da un master specifico.

2. Ritirare un elenco di fasi di lavoro incluso nel lavoro, nel titolo di cui viene trovata la parola specificata.

3. Per ricavare l'importo del costo dei passaggi del lavoro di riparazione per il lavoro con il codice nell'intervallo specificato.

4. Wire un elenco di maestri dalla data di occupazione nell'intervallo specificato.

Opzione 19.

1. Per ritirare un elenco di farmaci con una certa indicazione.

2. Wire un elenco di controlli di assegni per i quali è più di un certo numero di farmaci venduti.

3. Per prelevare la data di vendita, l'importo, il nome completo e la cura per un assegno con il numero specificato.

4. Visualizzare un elenco di farmaci e unità di misurazione per i farmaci con un pacchetto nell'imballaggio più del numero o del codice specificato del farmaco è inferiore a un determinato valore.

Opzione 20.

1. Ritirare un elenco di dipendenti con questo post.

2. Ritirare un elenco di documenti in cui si trova il frammento specificato della parola.

3. Per prelevare la data di registrazione, il tipo di documento, il nome completo e il fatto di esecuzione per i documenti registrati nel periodo specificato.

4. Visualizzare un elenco di documenti registrati con il tipo di documento specificato o con il codice documento in un intervallo specifico.

In MySQL 5 ci sono molte nuove funzionalità, una delle quali è la creazione di procedure memorizzate. In questa lezione, ti dirò cosa rappresentano, così come come possono rendere la vita più facile per te.

introduzione

La procedura memorizzata è un metodo di incapsulamento delle azioni ricorrenti. Nelle procedure memorizzate, è possibile dichiarare le variabili, gestire i flussi di dati, nonché applicare altre tecniche di programmazione.

La ragione della loro creazione è chiara e confermata da un uso frequente. D'altra parte, se parli con coloro che lavorano con loro irregolarmente, le opinioni sono divise in due fianchi completamente opposti. Non dimenticarti.

Per

• Separazione della logica con altre applicazioni. Procedure memorizzate Encapsulate funzionalità; Ciò garantisce la connettività ai dati e la gestione tra diverse applicazioni.
• Isolamento utente dalle tabelle del database. Ciò consente di accedere alle procedure memorizzate, ma non ai dati in sé.
• Fornisce un meccanismo di sicurezza. Conformemente al paragrafo precedente, se è possibile accedere ai dati solo tramite procedure memorizzate, nessun altro può cancellare i dati tramite il comando SQL Elimina.
• Migliorare le prestazioni a causa della riduzione traffico di rete. Con procedure memorizzate, possono essere combinate molte richieste.

Vs.

• Aumento del carico sul server del database a causa del fatto che la maggior parte del lavoro viene eseguita sulla parte del server e più piccola sul client.
• Ci saranno molte cose da pensare. Dovrai imparare mySQL Sintassi espressioni per scrivere le loro procedure memorizzate.
• Duplica la logica della tua applicazione in due luoghi: codice server e codice per le procedure memorizzate, complicando così il processo di manipolazione dei dati.
• La migrazione con un DBMS a un altro (DB2, SQL Server, ecc.) Può portare a problemi.

Lo strumento in cui lavoro è chiamato Browser query MySQL, è piuttosto standard per interagire con i database. Attrezzo riga di comando MySQL è un'altra scelta eccellente. Ti parlo per la ragione per il motivo che il Phpmyadmin preferito di tutti non supporta l'esecuzione di procedure memorizzate.

A proposito, uso la struttura elementare dei tavoli in modo che ti senta più facile capire questo argomento. Dopotutto, sto parlando di procedure memorizzate, e sono piuttosto complesse da approfondire la struttura ingombrante delle tabelle.

Passaggio 1: mettiamo il limitatore

Il limitatore è un carattere o una stringa simbolo che viene utilizzata per indicare il client MySQL che hai completato la scrittura dell'espressione SQL. Un'eternità completa era il limitatore era un simbolo di un punto e virgola. Tuttavia, possono sorgere problemi, poiché potrebbero esserci diverse espressioni nella procedura memorizzata, ognuna delle quali dovrebbe finire con un punto e virgola. In questa lezione uso una stringa "//" come limitatore.

Passaggio 2: come lavorare con le procedure memorizzate

Creazione di una procedura memorizzata

Delimiter // Creare la procedura `P2` () Lingua SQL Deterministic SQL Security SQL Determinazione SQL Security Definire Commento" Una procedura "Iniziare Selezionare" Ciao mondo! "; FINE //

La prima parte del codice crea una procedura memorizzata. Next - contiene parametri opzionali. Poi c'è un nome e, infine, il corpo della procedura stessa.

I nomi delle procedure memorizzati sono sensibili al registro. Puoi anche non creare diverse procedure con lo stesso nome. All'interno della stored procedure non può essere espressioni che cambiano il database stesso.

4 Caratteristiche della procedura memorizzata:

• Lingua: per eseguire la portabilità, viene specificato l'SQL predefinito.
• Deterministica: se la procedura restituisce lo stesso risultato tutto il tempo e prende gli stessi parametri in entrata. Questo è per la replica e il processo di registrazione. Il valore predefinito non è deterministico.
• Sicurezza SQL: durante la chiamata, i diritti dell'utente sono controllati. Invoker è un utente che causa una procedura memorizzata. Definer è la procedura "creatore". Valore predefinito: definito.
• Commento: per documentamento, il valore predefinito è ""

Chiamare la procedura memorizzata

Per invocare una procedura memorizzata, è necessario stampare una parola chiave chiamata, quindi il nome della procedura e specificare i parametri (variabili o valori) tra parentesi. Le parentesi sono richieste.

Chiama memorizzato_procedure_name (Param1, Param2, ....) Chiamata Procedura1 (10, "parametro stringa", @parameter_var);

Cambia procedura memorizzata

MySQL ha un'espressione di procedura alterata per modificare le procedure, ma è adatto per cambiare solo alcune caratteristiche. Se è necessario modificare i parametri o il corpo della procedura, è necessario rimuoverlo e crearlo di nuovo.

Rimozione della procedura memorizzata

Procedura di rilascio se esiste P2;

Questa è una squadra semplice. Un'esissione se esiste un'espressione sta effettuando un errore se tale procedura non esiste.

Passaggio 3: parametri

Vediamo come i parametri possono essere trasmessi alla stored procedure.

• Creare la procedura Proc1 (): Elenco vuoto dei parametri
• Creare Procedura Proc1 (in Varname Data-Type): un parametro in entrata. La parola in è facoltativa, poiché i parametri predefiniti sono in (in entrata).
• Creare Procedura Proc1 (OUT Varname Data-Type): un parametro restituito.
• Creare Procedura Proc1 (tipo di dati Varname inout): un parametro allo stesso tempo in entrata e restituito.

Naturalmente, è possibile impostare diversi parametri di diversi tipi.

Un esempio di parametro in

Delimiter // Creare la procedura `Proc_in` (in VAR1 INT) Inizia Selezionare Var1 + 2 come risultato; FINE //

Esempio di parametro

Delimiter // Creare la procedura `Proc_out` (OUT VAR1 VARCHAR (100)) Iniziare SET VAR1 \u003d" Questo è un test "; FINE //

Un esempio del parametro INOUT

Delimiter // Creare la procedura `Proc_inout` (out var1 int) Inizia Set var1 \u003d var1 * 2; FINE //

Passaggio 4: variabili

Ora ti insegnerò a creare variabili e manterrli all'interno delle procedure. Devi dichiararli chiaramente all'inizio del blocco iniziale / fine, insieme ai loro tipi di dati. Una volta dichiarata una variabile, puoi usarlo nello stesso luogo in cui le variabili di sessione, i letterali o i nomi degli altoparlanti.

La sintassi della Dichiarazione della variabile è simile a questa:

Dichiarare Varname Tipo di dati Default DefaultValue;

Dichiariamo diverse variabili:

Dichiarare A, B int predefinito 5; Dichiarare str varchar (50); Dichiarare oggi TimeStamp Default_Date; Dichiarare V1, V2, V3 Tinyint;

Lavorare con variabili

Dopo aver dichiarato una variabile, è possibile specificare il valore utilizzando set o selezionare i comandi:

Delimiter // Creare la procedura `var_proc` (in paramstr varchar (20)) Iniziare Dichiarare A, B Int predefinito 5; Dichiarare str varchar (50); Dichiarare oggi TimeStamp Default_Date; Dichiarare V1, V2, V3 Tinyint; Inserire nei valori del tavolo1 (A); Set str \u003d "Io sono una stringa"; Selezionare Concat (STR, paramstr), oggi dalla Table2 dove B\u003e \u003d 5; FINE //

Passaggio 5: strutture di gestione del flusso

MySQL supporta se, caso, itera, lascia il ciclo, mentre e ripeti la gestione del flusso all'interno della procedura memorizzata. Guarderemo come usare se, caso e mentre, come sono più spesso utilizzati.

Se design.

Usando il design IF, possiamo eseguire le attività contenenti le condizioni:

Delimiter // Creare la procedura `Proc_IF` (in Param1 INT) Inizia Dichiarare la variabile1 INT; Impostare la variabile1 \u003d Param1 + 1; Se variabile1 \u003d 0 Seleziona la variabile1; FINISCI SE; Se param1 \u003d 0 Seleziona "Valore parametro \u003d 0"; Altrimenti selezionare "Valore del parametro<> 0 "; fine se; fine //

Case Design.

Il caso è un altro metodo per controllare le condizioni e scegliere una soluzione adatta. Questo è un ottimo modo per sostituire più i disegni. Il design può essere descritto in due modi, fornendo flessibilità nel controllo di molteplici espressioni condizionali.

Delimiter // Creare la procedura `Proc_case` (in Param1 INT) Inizia Dichiarare la variabile1 int; Impostare la variabile1 \u003d Param1 + 1; Caso variabile1 quando 0 quindi inserire nei valori tabella1 (param1); Quando 1 quindi inserire nei valori tabella1 (variabile1); Altrimenti inserire nei valori del tavolo1 (99); Custodia di fine; FINE //

Delimiter // Creare la procedura `Proc_case` (in Param1 INT) Inizia Dichiarare la variabile1 int; Impostare la variabile1 \u003d Param1 + 1; Caso quando variabile1 \u003d 0 quindi inserire nei valori tabella1 (param1); Quando variabile1 \u003d 1 quindi inserire nei valori tabella1 (variabile1); Altrimenti inserire nei valori del tavolo1 (99); Custodia di fine; FINE //

Costruzione di tempo

Tecnicamente, ci sono tre tipi di cicli: mentre ciclo, ciclo ciclo e ripetere il ciclo. Puoi anche organizzare un ciclo usando la tecnica di programmazione "Darth Vader": le espressioni di goto. Ecco un esempio di un ciclo:

Delimiter // Creare la procedura PROCE "PROC_WHILE` (in Param1 INT) Inizia Dichiarare la variabile1, variabile2 int; Imposta la variabile1 \u003d 0; Mentre variabile1.< param1 DO INSERT INTO table1 VALUES (param1); SELECT COUNT(*) INTO variable2 FROM table1; SET variable1 = variable1 + 1; END WHILE; END //

Passaggio 6: cursori

I cursori vengono utilizzati per passare la richiesta di restituzione della riga, oltre a elaborare ogni riga.

MySQL supporta i cursori nelle procedure memorizzate. Ecco una breve sintassi della creazione e dell'uso del cursore.

Dichiarare il cursore del cursore del cursore per selezionare ...; / * Cursore Annuncio e riempimento IT * / Dichiarare il gestore Continua per non trovato / * Cosa fare, quando non ci sono più voci * / Apri il nome del cursore; / * Aprire il cursore * / recupera il nome del cursore in variabile [, variabile]; / * Assegnare un valore variabile uguale al valore corrente della colonna * / chiudi nome del cursore; / * Chiudi cursore * /

In questo esempio, spenderemo alcune semplici operazioni usando il cursore:

Delimiter // Creare la procedura `Proc_Cursor` (OUT FAM1 INT) Inizia Dichiarare A, B, C INT; Dichiarare il cursore CUR1 per selezionare COL1 dal tavolo1; Dichiarare il gestore continua per non trovato impostato B \u003d 1; Apri CUR1; Impostare B \u003d 0; Impostare c \u003d 0; Mentre B \u003d 0 recupera CUR1 in A; Se B \u003d 0, quindi impostare c \u003d c + a; FINISCI SE; Fine mentre; Chiudi Cur1; Impostare param1 \u003d c; FINE //

I cursori hanno tre proprietà che è necessario comprendere per evitare di ottenere risultati imprevisti:

• Non sensibile: C'era una volta, il cursore non mostrerà cambiamenti nella tabella che è accaduto più tardi. Infatti, MySQL non garantisce cosa verrà aggiornato il cursore, quindi non indossarlo.
• Solo leggi: i cursori non possono essere modificati.
• Senza riavvolgimento: il cursore è in grado di passare solo in una direzione - in avanti, non sarai in grado di saltare le stringhe senza sceglierle.

Conclusione

In questa lezione, ti ho presentato le basi del lavoro con le procedure memorizzate e con alcune proprietà specifiche associate ad essa. Naturalmente, dovrai approfondire la conoscenza in settori come la sicurezza, le espressioni SQL e l'ottimizzazione prima di diventare un vero guru di procedura MySQL.

È necessario calcolare quali Vantaggi ti darà l'uso di procedure memorizzate nella tua applicazione specifica e solo quindi creare solo le procedure necessarie. In generale, uso le procedure; A mio parere, dovrebbero essere implementati nei progetti come conseguenza della loro sicurezza, servizio di codice e design generale. Inoltre, non dimenticare che le procedure MySQL stanno ancora lavorando. Aspettatevi miglioramenti relativi alla funzionalità e ai miglioramenti. Non esitate a condividere opinioni.