Ultimo aggiornamento: 14.08.2017

Spesso, un'operazione di dati è un insieme di istruzioni che devono essere eseguite in una sequenza specifica. Ad esempio, quando si aggiunge un articolo di acquisto, è necessario inserire i dati nella tabella degli ordini. Tuttavia, prima di ciò è necessario verificare se l'articolo acquistato è disponibile. Potrebbe essere necessario controllare una serie di condizioni aggiuntive. Cioè, in effetti, il processo di acquisto di un prodotto comprende diverse azioni che devono essere eseguite in una sequenza specifica. E in questo caso, sarebbe più ottimale incapsulare tutte queste azioni in un oggetto: procedura memorizzata (procedura memorizzata).

Cioè, in sostanza, le stored procedure sono un insieme di istruzioni eseguite nel loro insieme. Pertanto, le procedure memorizzate consentono di semplificare operazioni complesse e portarle in un singolo oggetto. Il processo di acquisto dei beni cambierà; di conseguenza, sarà sufficiente cambiare il codice della procedura. Cioè, la procedura semplifica anche la gestione del codice.

Le procedure memorizzate consentono inoltre di limitare l'accesso ai dati nelle tabelle e quindi ridurre la probabilità di azioni indesiderate intenzionali o inconsce in relazione a tali dati.

E un altro aspetto importante è la prestazione. Le procedure memorizzate sono in genere più veloci delle normali istruzioni SQL. Questo perché il codice di procedura viene compilato la prima volta che viene eseguito e quindi salvato nel modulo compilato.

Il comando CREATE PROCEDURE o CREATE PROC viene utilizzato per creare una procedura memorizzata.

Pertanto, una procedura memorizzata ha tre caratteristiche principali: semplificazione del codice, sicurezza e prestazioni.

Ad esempio, supponiamo che nel database sia presente una tabella che memorizza i dati del prodotto:

Prodotti CREATE TABLE (ID CHIAVE PRIMARIA ID INT, Nome prodotto NVARCHAR (30) NOT NULL, Produttore NVARCHAR (20) NOT NULL, ProductCount INT DEFAULT 0, Prezzo SOLDI NON NULL);

Creiamo una procedura memorizzata per recuperare i dati da questa tabella:

UTILIZZARE productsdb; PROCEDURA GO CREATE Riepilogo prodotti AS SELECT Nome prodotto AS Prodotto, produttore, prezzo DA prodotti

Poiché il comando CREATE PROCEDURE deve essere chiamato in un pacchetto separato, il comando GO viene utilizzato dopo il comando USE che installa il database corrente per definire un nuovo pacchetto.

La parola chiave AS deve seguire il nome della procedura.

Per separare il corpo della procedura dal resto dello script, il codice della procedura viene spesso inserito in un blocco BEGIN ... END:

UTILIZZARE productsdb; PROCEDURA GO CREATE Riepilogo prodotti COME INIZIO SELEZIONA Nome prodotto AS Prodotto, Produttore, Prezzo DA FINE Prodotti;

Dopo aver aggiunto la procedura, possiamo vederla nel nodo del database in SQL Server Management Studio nel nodo secondario Programmabilità -\u003e Stored procedure:

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

Esecuzione della procedura

Il comando EXEC o EXECUTE viene chiamato per eseguire la procedura memorizzata:

Riepilogo dei prodotti EXEC

Rimozione di una procedura

Per eliminare una procedura, utilizzare il comando DROP PROCEDURE:

PROCEDURA DROP Sintesi del prodotto

1. Studiare gli operatori che descrivono le procedure memorizzate e i principi del passaggio dei loro parametri di input e output.
2. Scopri l'ordine di creazione e debug delle stored procedure su MS SQL Server 2000.
3. Sviluppare cinque stored procedure di base per il database di esempio Library.
4. Preparare una relazione sul lavoro svolto in forma elettronica.

1. Informazioni generali sulle procedure memorizzate

Stored procedure È un insieme di comandi archiviati sul server ed eseguiti nel loro insieme. Le stored procedure sono un meccanismo mediante il quale è possibile creare routine in esecuzione sul server e controllate dai suoi processi. Tali routine possono essere invocate dall'applicazione chiamante. Possono anche essere attivati \u200b\u200bda regole o trigger di integrità dei dati.

Le procedure memorizzate possono restituire valori. Nella procedura, è possibile confrontare i valori immessi dall'utente con le informazioni preimpostate nel sistema. Le stored procedure utilizzano potenti soluzioni hardware di SQL Server. Sono orientati al database e lavorano a stretto contatto con SQL Server Optimizer. Ciò consente alte prestazioni nell'elaborazione dei dati.

È possibile passare valori alle stored procedure e ottenere risultati da esse, e non necessariamente correlati al foglio di lavoro. La procedura memorizzata può calcolare i risultati al volo.

Le procedure memorizzate sono di due tipi: ordinario e esteso... Le stored procedure regolari sono un insieme di comandi Transact-SQL, mentre le stored procedure estese sono rappresentate come librerie a collegamento dinamico (DLL). Tali procedure, a differenza delle solite, hanno il prefisso xp_. Il server ha una serie standard di procedure estese, ma gli utenti possono scrivere le proprie procedure in qualsiasi linguaggio di programmazione. La cosa principale è usare l'interfaccia di programmazione API Open Data Services di SQL Server... Le stored procedure estese possono risiedere solo nel database Master.

Le stored procedure regolari possono anche essere suddivise in due tipi: sistemico e costume... Le procedure di sistema sono le procedure standard utilizzate per far funzionare il server; personalizzato: qualsiasi procedura creata dall'utente.

1.1. I vantaggi delle stored procedure

Nel caso più generale, le stored procedure presentano i seguenti vantaggi:

• Alte prestazioni. È il risultato della posizione delle procedure memorizzate sul server. Il server, di regola, è una macchina più potente, quindi il tempo di esecuzione della procedura sul server è molto inferiore rispetto alla workstation. Inoltre, le informazioni dal database e la procedura memorizzata si trovano sullo stesso sistema, quindi c'è poco tempo per trasferire i record sulla rete. Le procedure memorizzate hanno accesso diretto ai database, il che rende molto veloce il lavoro con le informazioni.
• Il vantaggio della progettazione del sistema in un'architettura client-server. Sta nella possibilità di creare separatamente software client e server. Questo vantaggio è fondamentale nello sviluppo e può ridurre significativamente il tempo necessario per completare un progetto. Il codice che viene eseguito sul server può essere sviluppato separatamente dal codice lato client. In questo caso, i componenti sul lato server possono essere condivisi dai componenti sul lato client.
• Livello di sicurezza. Le procedure memorizzate possono fungere da strumento di miglioramento della sicurezza. È possibile creare procedure memorizzate per eseguire le operazioni di aggiunta, modifica, eliminazione e visualizzazione di elenchi e ottenere quindi il controllo su ciascun aspetto dell'accesso alle informazioni.
• Rafforzare le regole del server che funzionano con i dati. Questo è uno dei motivi più importanti per l'utilizzo del motore di database intelligente. Le procedure memorizzate consentono di applicare regole e altre logiche per aiutare a controllare le informazioni immesse nel sistema.

Sebbene SQL sia definito come non procedurale, SQL Server utilizza parole chiave correlate al controllo del flusso di procedure. Queste parole chiave vengono utilizzate per creare procedure che possono essere salvate per un'esecuzione successiva. Le stored procedure possono essere utilizzate al posto di programmi scritti in linguaggi di programmazione standard (come C o Visual Basic) che eseguono operazioni su un database SQL Server.

Le procedure memorizzate vengono compilate la prima volta che vengono eseguite e archiviate nella tabella di sistema del database corrente. Sono ottimizzati quando compilati. Questo sceglie il modo migliore per accedere alle informazioni sulla tabella. Questo tipo di ottimizzazione tiene conto della posizione effettiva dei dati nella tabella, degli indici disponibili, del carico della tabella, ecc.

Le stored procedure compilate possono migliorare significativamente le prestazioni del sistema. Vale la pena notare, tuttavia, che le statistiche dei dati dal momento in cui viene creata una procedura al momento in cui viene eseguita possono diventare obsolete e gli indici possono diventare inefficaci. Sebbene sia possibile aggiornare le statistiche e aggiungere nuovi indici più efficienti, il piano di esecuzione della procedura è già stato elaborato, ovvero la procedura è stata compilata e, di conseguenza, il modo di accedere ai dati potrebbe non essere più efficiente. Pertanto, è possibile ricompilare le procedure ad ogni chiamata.

D'altra parte, ci vorrà del tempo per ricompilare ogni volta. Pertanto, la questione dell'efficienza della ricompilazione di una procedura o della stesura di un piano per la sua esecuzione in una volta è piuttosto delicata e dovrebbe essere considerata separatamente per ciascun caso specifico.

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

I programmi applicativi scritti in uno dei linguaggi di alto livello come C o Visual Basic .NET possono anche chiamare stored procedure, che fornisce una soluzione di bilanciamento del carico ottimale tra il software lato client e il server SQL.

1.2. Creazione di stored procedure

L'istruzione Crea procedura viene utilizzata per creare una procedura memorizzata. Il nome della procedura memorizzata può contenere fino a 128 caratteri, inclusi i caratteri # e ##. Sintassi della definizione della procedura:

CREATE PROC nome_sp [; numero]
[(@ parametro tipo di dati) [\u003d default_value]] [, ... n]

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

Considera i parametri di questo comando:

• Nome_procedura - nome della procedura; deve rispettare le regole per gli identificatori: la sua lunghezza non può superare 128 caratteri; per le procedure temporanee locali, # viene utilizzato prima del nome e per le procedure temporanee globali, ##;
• Numero è un numero intero opzionale utilizzato per raggruppare più procedure con lo stesso nome;
• @ parametro data_type - elenco dei nomi dei parametri di procedura con indicazione del tipo di dati corrispondente per ciascuno; possono esserci fino a 2100 di tali parametri, come valore del parametro è consentito NULL. Tutti i tipi di dati possono essere utilizzati tranne text, ntext e image. Il tipo di dati Cursor può essere utilizzato come parametro di output (parola chiave OUTPUT o VARYING). I parametri con il tipo di dati Cursor possono essere solo parametri di output;
• VARYING è una parola chiave che specifica che un set di risultati viene utilizzato come parametro di output (utilizzato solo per il tipo di cursore);
• OUTPUT - indica che il parametro specificato può essere utilizzato come output;
• valore predefinito - utilizzato nel caso in cui il parametro sia omesso quando si chiama la procedura; deve essere costante e può includere caratteri maschera (%, _, [,], ^) e NULL;
• WITH RECOMPILE: parole chiave che indicano che SQL Server non scriverà il piano della procedura nella cache, ma ne creerà uno ogni volta che viene eseguito;
• CON ENCRYPTION parole chiave che indicano che SQL Server crittograferà la procedura prima di scrivere nella tabella di sistema Syscomments. Per rendere impossibile il recupero del testo delle procedure crittografate, è necessario eliminare le tuple corrispondenti dalla tabella dei sistemi dopo la crittografia;
• FOR REPLICATION: parole chiave che indicano che questa procedura è stata creata solo per la replica. Questa opzione non è compatibile con le parole chiave WITH RECOMPILE;
• AS - inizio della definizione del testo della procedura;
• <Инструкции_SQL> - l'insieme di istruzioni SQL valide, limitato solo dalla dimensione massima della procedura memorizzata - 128 KB. Le seguenti affermazioni non sono valide: ALTER DATABASE, ALTER PROCEDURE, ALTER TABLE, CREATE DEFAULT, CREATE PROCEDURE, ALTER TRIGGER, ALTER VIEW, CREATE DATABASE, CREATE RULE, CREATE SCHEMA, CREATE TRIGGER, CREITATE, VIEW, DISPASE DEFAULT DROP DEFAULT, PROCEDURA DROP, DROP REGLE, DROP TRIGGER, DROP VIEW, RESOTRE DATABASE, RESTORE LOG, RECONFIGURE, UPDATE STATISTICS.

Diamo un'occhiata a un esempio di una procedura memorizzata. Sviluppiamo una procedura memorizzata che conta e visualizza il numero di copie dei libri che sono attualmente in biblioteca:

CREATE Procedura Count_Ex1
- la procedura per contare il numero di copie di libri,
- attualmente in biblioteca,
- non nelle mani dei lettori
Come
- imposta una variabile locale temporanea
Dichiara @N int
Seleziona @N \u003d count (*) da Exemplar Where Yes_No \u003d "1"
Seleziona @N
PARTIRE

Poiché la procedura memorizzata è un componente completo del database, quindi, come già capito, è possibile creare una nuova procedura solo per il database corrente. Quando si lavora in SQL Server Query Analyzer, l'impostazione del database corrente viene eseguita con un'istruzione Use seguita dal nome del database in cui deve essere creata la procedura memorizzata. È inoltre possibile selezionare il database corrente utilizzando l'elenco a discesa.

Dopo aver creato una procedura memorizzata sul sistema, SQL Server la compila e verifica le routine che viene eseguita. In caso di problemi, la procedura viene rifiutata. Gli errori devono essere corretti prima di ritrasmettere.

SQL Server 2000 utilizza la risoluzione dei nomi ritardata, quindi se una procedura memorizzata contiene una chiamata a un'altra procedura non ancora implementata, viene emesso un avviso, ma la chiamata alla procedura inesistente viene mantenuta.

Se si lascia una chiamata a una procedura memorizzata non identificata sul sistema, l'utente riceverà un messaggio di errore quando tenterà di eseguirla.

È inoltre possibile creare una procedura memorizzata utilizzando SQL Server Enterprise Manager:

Per verificare la funzionalità della procedura memorizzata creata, è necessario accedere a Query Analyzer e avviare la procedura per l'esecuzione da parte dell'operatore EXEC<имя процедуры> ... I risultati dell'esecuzione della procedura creata sono mostrati in Fig. 4.

Figura. 4. Esecuzione della procedura memorizzata in Query Analyzer

Figura. 5. Il risultato dell'esecuzione della procedura senza un operatore di visualizzazione

1.3. Parametri della procedura memorizzati

Le procedure memorizzate sono molto potenti, ma puoi ottenerne il massimo solo rendendole dinamiche. Lo sviluppatore deve essere in grado di passare valori alla procedura memorizzata con cui funzionerà, ovvero parametri. Di seguito sono riportati i principi di base dell'utilizzo dei parametri nelle stored procedure.

• Uno o più parametri possono essere definiti per una procedura.
• 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 deve essere preceduto dal simbolo @.
• I nomi dei parametri sono locali alla procedura in cui sono definiti.
• I parametri vengono utilizzati per passare informazioni alla procedura durante la sua esecuzione. Appariranno sulla riga di comando dopo il nome della procedura.
• Se la procedura ha diversi parametri, sono separati da virgole.
• I tipi di dati di sistema o definiti dall'utente vengono utilizzati per determinare il tipo di informazioni passate come parametro.

Di seguito è la definizione di una procedura che ha un parametro di input. Cambiamo l'attività precedente e non conteremo tutte le copie dei libri, ma solo le copie di un determinato libro. I nostri libri sono identificati in modo univoco dal loro codice ISBN unico, quindi passeremo questo parametro alla procedura. In questo caso, il testo della procedura memorizzata cambierà in questo modo:

Crea procedura Count_Ex (@ISBN varchar (14))
Come
Dichiara @N int
Seleziona @N
PARTIRE

Quando si avvia questa procedura per l'esecuzione, è necessario passargli il valore del parametro di input (Fig. 6).

Figura. 6. Procedura di avvio con passaggio parametri

Per creare più versioni della stessa procedura con lo stesso nome, aggiungere un punto e virgola e un numero intero dopo il nome di base. Come fare questo è mostrato nel seguente esempio, che descrive come creare due procedure con lo stesso nome ma numeri di versione diversi (1 e 2). Il numero viene utilizzato per controllare la versione in esecuzione di questa procedura. Se non viene specificato alcun numero di versione, viene eseguita la prima versione della procedura. Questa opzione non è mostrata nell'esempio precedente, ma è comunque disponibile per l'applicazione.

Entrambe le procedure utilizzano l'istruzione print per visualizzare un messaggio che identifica la versione. La prima versione conta il numero di copie gratuite e la seconda - il numero di copie disponibili per un determinato libro.

Il testo di entrambe le versioni delle procedure è riportato di seguito:

CREATE Procedura Count_Ex_all; 1
(@ISBN varchar (14))
- la procedura per contare le copie gratuite di un determinato libro
Come
Dichiara @N int
Seleziona @N \u003d count (*) da Exemplar Where ISBN \u003d @ISBN e Yes_No \u003d "1"
Seleziona @N
--
PARTIRE
--
CREATE Procedura Count_Ex_all; 2
(@ISBN varchar (14))
- la procedura per contare le copie gratuite di un determinato libro
Come
Dichiara @ N1 int
Seleziona @ N1 \u003d count (*) da Exemplar Where ISBN \u003d @ISBN e Yes_No \u003d "0"
Seleziona @ N1
PARTIRE

I risultati dell'esecuzione della procedura con diverse versioni sono mostrati in Fig. 7.

Figura. 7. Risultati dell'esecuzione di versioni diverse della stessa procedura memorizzata

Quando si scrivono più versioni, ricordare le seguenti restrizioni: poiché tutte le versioni di una procedura sono compilate insieme, tutte le variabili locali sono considerate condivise. Pertanto, se ciò è richiesto dall'algoritmo di elaborazione, è necessario utilizzare nomi diversi di variabili interne, cosa che abbiamo fatto nominando la variabile @N con il nome @ N1 nella seconda procedura.

Le procedure che abbiamo scritto non restituiscono un singolo parametro, ma visualizzano solo il numero risultante. Tuttavia, molto spesso è necessario ottenere un parametro per un'ulteriore elaborazione. Esistono diversi modi per restituire i parametri da una procedura memorizzata. Il più semplice è usare l'operatore RETURN. Questo operatore restituirà un singolo valore numerico. Ma dobbiamo specificare un nome o un'espressione variabile che è assegnata al parametro restituito. Di seguito sono riportati i valori riservati del sistema restituiti dall'operatore RETURN:

Codice	Valore
0	Va bene
-1	Oggetto non trovato
-2	Errore del tipo di dati
-3	Il processo è diventato vittima di "deadlock"
-4	errore di accesso
-5	Errore di sintassi
-6	Qualche errore
-7	Errore di risorsa (nessuno spazio)
-8	Si è verificato un errore interno recuperabile
-9	Limite di sistema raggiunto
-10	Violazione fatale dell'integrità interna
-11	Stesso
-12	Distruggere una tabella o un indice
-13	Distruzione del database
-14	Errore hardware

Pertanto, per non contraddire il sistema, possiamo restituire solo numeri interi positivi attraverso questo parametro.

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

Crea procedura Count_Ex2 (@ISBN varchar (14))
Come
Dichiara @N int
Seleziona @N \u003d count (*) da Exemplar
Dove ISBN \u003d @ISBN e YES_NO \u003d "1"
- restituiamo il valore della variabile @N,
- se il valore della variabile non è definito, restituisce 0
Coalesce di ritorno (@N, 0)
PARTIRE

Ora possiamo ottenere il valore della variabile @N e usarlo per ulteriori elaborazioni. In questo caso, il valore restituito viene assegnato alla procedura memorizzata stessa e per analizzarlo è possibile utilizzare il seguente formato dell'istruzione di chiamata della procedura memorizzata:

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

Un esempio di chiamata alla nostra procedura è mostrato in Fig. 8.

Figura. 8. Passando il valore restituito della procedura memorizzata a una variabile locale

I parametri di input per le stored procedure possono utilizzare il valore predefinito. Questo valore verrà utilizzato se il valore del parametro non è stato specificato durante la chiamata della procedura.

Il valore predefinito è specificato con un segno di uguale dopo la descrizione del parametro di input e del suo tipo. Si consideri una procedura memorizzata che conta il numero di copie del libro di un determinato anno di emissione. L'anno predefinito è il 2006.

CREATE PROCEDURE ex_books_now (@year int \u003d 2006)
- contare il numero di copie di libri di un determinato anno di emissione
COME
Dichiara @N_books int
seleziona @N_books \u003d count (*) dai libri, esempio
dove Books.ISBN \u003d exemplar.ISBN e YEARIZD \u003d @year
coalesce di ritorno (@N_books, 0)
PARTIRE

In fig. 9 mostra un esempio di chiamata a questa procedura con e senza un parametro di input.

Figura. 9. Richiamo di una procedura memorizzata con e senza un parametro

Tutti gli esempi di utilizzo dei parametri nelle stored procedure discusse sopra hanno fornito solo parametri di input. Tuttavia, i parametri possono essere emessi. Ciò significa che il valore del parametro dopo il completamento della procedura verrà passato a chi ha chiamato questa procedura (un'altra procedura, trigger, batch di comandi, ecc.). Naturalmente, al fine di ottenere il parametro di output, quando si chiama, è necessario specificare non una costante, ma una variabile come parametro effettivo.

Si noti che la definizione di un parametro come output in una procedura non obbliga a usarlo come tale. Cioè, se si specifica una costante come parametro effettivo, non si verificherà un errore e verrà utilizzato come parametro di input normale.

L'istruzione OUTPUT viene utilizzata per indicare che il parametro è un parametro di uscita. Questa parola chiave è scritta dopo la descrizione del parametro. Nel descrivere i parametri delle stored procedure, è preferibile specificare i valori dei parametri di output dopo quelli di input.

Consideriamo un esempio dell'uso dei parametri di output. Scriviamo una procedura memorizzata che, per un dato libro, calcola il numero totale delle sue copie in biblioteca e il numero di copie gratuite. Non è possibile utilizzare qui l'operatore RETURN, poiché restituisce solo un valore, quindi è necessario definire qui i parametri di output. Il testo della procedura memorizzata potrebbe essere simile al seguente:

CREATE Procedura Count_books_all
(@ISBN varchar (14), @all int output, @free int output)
- la procedura per il calcolo del numero totale di copie di un determinato libro
- e il numero di copie gratuite
Come
- contando il numero totale di copie
Seleziona @all \u003d count (*) da Exemplar Where ISBN \u003d @ISBN
Seleziona @free \u003d count (*) da Exemplar Where ISBN \u003d @ISBN e Yes_No \u003d "1"
PARTIRE

Un esempio di questa procedura è mostrato in Fig. dieci.

Figura. 10. Test di una procedura memorizzata con parametri di output

Come accennato in precedenza, al fine di ottenere i valori dei parametri di output per l'analisi, dobbiamo impostarli con le variabili e queste variabili devono essere descritte dall'istruzione Declare. L'ultima istruzione di output ci ha permesso di visualizzare semplicemente i valori risultanti.

Anche le variabili di tipo Cursore possono essere parametri della procedura. Per fare ciò, la variabile deve essere descritta come un tipo di dati speciale VARYING, senza legarsi ai tipi di dati di sistema standard. Inoltre, è necessario specificare che si tratta di una variabile Cursor.

Scriviamo una semplice procedura che elenca i libri nella nostra biblioteca. In questo caso, se non ci sono più di tre libri, visualizziamo i loro nomi nel quadro della procedura stessa e se l'elenco dei libri supera un numero specificato, li trasferiamo come cursore sul programma o modulo chiamante.

Il testo della procedura è simile al seguente:

CREA PROCEDURA GET3TITLES
(@MYCURSOR CURSOR VARIAZIONE USCITA)
- procedura per la stampa di titoli di libri con cursore
COME
- definire una variabile locale di tipo Cursore nella procedura
SET @MYCURSOR \u003d CURSOR
PER SELEZIONARE IL TITOLO DISTINCT
DAI LIBRI
- apri il cursore
APERTO @MYCURSOR
- descriviamo le variabili locali interne
DECLARE @TITLE VARCHAR (80), @CNT INT
--- imposta lo stato iniziale del contatore del libro
SET @CNT \u003d 0
- vai alla prima riga del cursore
- mentre ci sono linee del cursore,
- cioè, purché la nuova riga sia corretta
WHILE (@@ FETCH_STATUS \u003d 0) AND (@CNT<= 2) BEGIN
STAMPA @TITLE
PASSA SUCCESSIVO DA @MYCURSOR IN @TITLE
- cambia lo stato del contatore del libro
SET @CNT \u003d @CNT + 1
FINE
SE @CNT \u003d 0 STAMPA "NESSUN LIBRO ADATTO"
PARTIRE

Un esempio di chiamata a questa procedura memorizzata è mostrato in Fig. undici.

Nella procedura di chiamata, il cursore deve essere dichiarato come variabile locale. Quindi abbiamo chiamato la nostra procedura e le abbiamo passato il nome di una variabile locale di tipo Cursore. La procedura ha iniziato a funzionare e ci ha mostrato i primi tre nomi, quindi ha passato il controllo alla procedura di chiamata e quella procedura ha continuato a elaborare il cursore. Per fare ciò, ha organizzato un ciclo While sulla variabile globale @@ FETCH_STATUS, che tiene traccia dello stato del cursore, quindi ha scaricato tutte le altre linee del cursore nel ciclo.

Nella finestra di output, vediamo una maggiore spaziatura tra le prime tre righe e i titoli successivi. Questo intervallo mostra solo che il controllo è stato trasferito a un programma esterno.

Si noti che la variabile @TITLE, essendo locale alla procedura, verrà distrutta al termine dell'esecuzione, quindi viene nuovamente dichiarata nel blocco chiamante. La creazione e l'apertura di un cursore in questo esempio avviene in una procedura, mentre la chiusura, la distruzione e l'elaborazione aggiuntiva vengono eseguite nel blocco comandi in cui viene chiamata la procedura.

Il modo più semplice per visualizzare il testo della procedura, modificarlo o eliminarlo utilizzando l'interfaccia grafica di Enterprise Manager. Tuttavia, è anche possibile farlo utilizzando speciali stored procedure di sistema Transact-SQL. In Transact-SQL, la procedura di sistema sp_helptext viene utilizzata per visualizzare la definizione di una procedura e la procedura di sistema sp_help visualizza le informazioni di controllo sulla procedura. Le procedure di sistema sp_helptext e sp_help vengono utilizzate anche per visualizzare oggetti di database come tabelle, regole e impostazioni predefinite.

Le informazioni su tutte le versioni di una procedura, indipendentemente dal numero, vengono visualizzate immediatamente. Anche l'eliminazione di versioni diverse della stessa procedura memorizzata avviene contemporaneamente. L'esempio seguente mostra come vengono visualizzate le definizioni versione 1 e versione 2 di Count_Ex_all quando il suo nome viene specificato come parametro nella procedura di sistema sp_helptext (Figura 12).

Figura. 12. Visualizzazione del corpo di una stored procedure mediante una stored procedure di sistema

La procedura di sistema SP_HELP visualizza le caratteristiche e i parametri della procedura creata nel seguente formato:

Nome Proprietario genere	Created_datetime
Count_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	Cyrillic_General_CI_AS
@tutti int 4 10	0	2	NULLO
@gratuito int 4 10	0	3	NULLO

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

1.4. Compilare una procedura memorizzata

Il vantaggio dell'utilizzo di stored procedure per eseguire un set di istruzioni Transact-SQL è che vengono compilate la prima volta che vengono eseguite. Durante la compilazione, le istruzioni Transact-SQL vengono convertite dalla loro rappresentazione simbolica originale in forma eseguibile. Anche gli oggetti a cui si accede nella procedura vengono convertiti in una rappresentazione alternativa. Ad esempio, i nomi delle tabelle vengono convertiti in ID oggetto e i nomi delle colonne vengono convertiti in ID colonna.

Un piano di esecuzione viene creato come per l'esecuzione di una singola istruzione Transact-SQL. Questo piano contiene, ad esempio, indici utilizzati per leggere le righe dalle tabelle a cui accede la procedura. Il piano di esecuzione della procedura viene memorizzato nella cache e utilizzato ogni volta che viene chiamato.

Nota: la cache delle procedure può essere dimensionata in modo tale da contenere la maggior parte o tutte le procedure disponibili. Ciò consente di risparmiare il tempo necessario per rigenerare il piano di trattamento.

1.5. Ricompilazione automatica

Di solito il piano di esecuzione si trova nella cache delle procedure. Ciò consente di aumentare le prestazioni durante l'esecuzione. Tuttavia, in alcune circostanze, la procedura si ricompilerà automaticamente.

• La procedura viene sempre ricompilata all'avvio di SQL Server. Questo di solito accade dopo il riavvio del sistema operativo e la prima volta che la procedura viene eseguita dopo la creazione.
• Il piano di esecuzione di una procedura viene sempre ricompilato automaticamente se viene eliminato l'indice della tabella a cui accede la procedura. Poiché il piano corrente accede a un indice che non esiste più per leggere le righe della tabella, è necessario creare un nuovo piano di esecuzione. Le query di procedura verranno eseguite solo se viene aggiornato.
• Il piano di esecuzione viene inoltre compilato se un altro utente sta attualmente lavorando con il piano nella cache. Viene creata una copia individuale del piano di esecuzione per il secondo utente. Se la prima copia del piano non fosse occupata, non sarebbe necessario crearne una seconda. Quando l'utente completa l'esecuzione della procedura, il piano di esecuzione è disponibile nella cache per un altro utente con l'autorizzazione di accesso appropriata.
• La procedura viene ricompilata automaticamente se viene eliminata e ricreata. Poiché la nuova procedura può differire dalla versione precedente, tutte le copie del piano di esecuzione nella cache vengono eliminate e il piano viene nuovamente compilato.

SQL Server si impegna a ottimizzare le stored procedure memorizzando nella cache le procedure più utilizzate. Pertanto, al posto del nuovo piano è possibile utilizzare il vecchio piano di esecuzione caricato nella cache. Per evitare questo problema, è necessario eliminare e ricreare la procedura memorizzata oppure arrestare e riattivare SQL Server. Ciò cancellerà la cache delle procedure ed eliminerà la possibilità di lavorare con il vecchio piano di esecuzione.

È inoltre possibile creare una procedura con l'opzione WITH RECOMPILE. In questo caso, si ricompilerà automaticamente ad ogni esecuzione. L'opzione WITH RECOMPILE deve essere utilizzata quando la procedura accede a tabelle molto dinamiche le cui righe vengono frequentemente aggiunte, eliminate o aggiornate, in quanto ciò comporta modifiche significative agli indici definiti per le tabelle.

Se le procedure non vengono ricompilate automaticamente, possono essere forzate. Ad esempio, se le statistiche utilizzate per determinare se un indice può essere utilizzato in una determinata query sono state aggiornate o se è stato creato un nuovo indice, è necessario eseguire una ricompilazione forzata. Per forzare la ricompilazione, l'istruzione EXECUTE utilizza la clausola WITH RECOMPILE:

EXECUTE procedure_name;
COME
<инструкции Transact-SQL>
CON RICOMPILE

Se la procedura funziona con parametri che controllano l'ordine della sua esecuzione, utilizzare l'opzione WITH RECOMPILE. Se i parametri della procedura memorizzata possono determinare il modo migliore per eseguirla, si consiglia di generare il piano di esecuzione al volo, anziché crearne uno la prima volta che la procedura viene chiamata per l'uso su tutte le chiamate successive.

Nota: a volte è difficile determinare se utilizzare l'opzione WITH RECOMPILE durante la creazione di una procedura o meno. In caso di dubbio, è meglio non utilizzare questa opzione, poiché la ricompilazione della procedura ogni volta che viene eseguita farà perdere molto tempo prezioso alla CPU. Se in futuro è necessario ricompilare durante l'esecuzione della procedura memorizzata, è possibile farlo aggiungendo una clausola WITH RECOMPILE all'istruzione EXECUTE.

Non è possibile utilizzare l'opzione WITH RECOMPILE in un'istruzione CREATE PROCEDURE contenente un'opzione FOR REPLICATION. Questa opzione viene utilizzata per creare una procedura che viene eseguita durante il processo di replica.

1.6. Nidificazione di stored procedure

Le stored procedure possono chiamare altre stored procedure, ma esiste un limite a livello di annidamento. Il livello massimo di annidamento è 32. L'attuale livello di annidamento può essere determinato utilizzando la variabile globale @@ NESTLEVEL.

2. Funzioni definite dall'utente (UDF)

MS SQL SERVER 2000 ha molte funzioni predefinite che consentono di eseguire una varietà di azioni. Tuttavia, potrebbe essere sempre necessario utilizzare alcune funzioni specifiche. Per questo, a partire dalla versione 8.0 (2000), è stato possibile descrivere le funzioni definite dall'utente (UDF) e memorizzarle come oggetto di database completo, insieme a stored procedure, viste, ecc.

L'usabilità delle funzioni definite dall'utente è ovvia. A differenza delle procedure memorizzate, le funzioni possono essere incorporate direttamente in un'istruzione SELECT e possono essere utilizzate sia per recuperare valori specifici (nella clausola SELECT) sia come origine dati (nella clausola FROM).

Quando si utilizzano UDF come origini dati, il vantaggio rispetto alle viste è che le UDF, a differenza delle viste, possono avere parametri di input con i quali è possibile influenzare il risultato della funzione.

Le funzioni definite dall'utente possono essere di tre tipi: funzioni scalari, funzioni incorporate e funzioni multiistruzione che restituiscono un risultato di tabella... Consideriamo tutti questi tipi di funzioni in modo più dettagliato.

2.1. Funzioni scalari

Le funzioni scalari restituiscono un singolo risultato scalare. Questo risultato può essere uno dei tipi sopra descritti, ad eccezione di text, ntext, image e timestamp. Questo è il tipo più semplice di funzione. La sua sintassi è la seguente:

RESI scalar_data_type

INIZIO
function_body
INVIO scalar_expression
FINE

• Il parametro ENCRYPTION è già stato descritto nella sezione sulle procedure memorizzate;
• SCHEMABINDING: associa una funzione a uno schema. Ciò significa che non è possibile eliminare le tabelle o le viste su cui si basa la funzione senza eliminare o modificare la funzione stessa. È anche impossibile modificare la struttura di queste tabelle se la parte modificata viene utilizzata da una funzione. Pertanto, questa opzione consente di escludere situazioni in cui la funzione utilizza tabelle o viste e qualcuno, inconsapevolmente, le ha eliminate o modificate;
• RESI scalar_data_type - descrive il tipo di dati restituito dalla funzione;
• scalar_expression - un'espressione che restituisce direttamente il risultato dell'esecuzione della funzione. Deve essere dello stesso tipo di quello descritto dopo RESI;
• function_body è un insieme di istruzioni Transact-SQL.

Diamo un'occhiata agli esempi di utilizzo delle funzioni scalari.

Creare una funzione che sceglie il più piccolo dei due numeri interi indicati come parametri.

Lascia che la funzione assomigli a questo:

CREATE FUNCTION min_num (@a INT, @b INT)
RESI INT
INIZIO
DECLARE @c INT
Se una< @b SET @c = @a
ELSE SET @c \u003d @b
INVIO @c
FINE

Ora eseguiamo questa funzione:

SELEZIONA dbo.min_num (4, 7)

Di conseguenza, otteniamo il valore 4.

È possibile utilizzare questa funzione per trovare il valore più piccolo tra le colonne della tabella:

SELEZIONA min_lvl, max_lvl, min_num (min_lvl, max_lvl)
DA Jobs

Creiamo una funzione che riceverà un parametro del tipo datetime come input e restituirà la data e l'ora corrispondenti all'inizio del giorno specificato. Ad esempio, se il parametro di input è 09.20.03 13:31, il risultato sarà 09.20.03 00:00.

CREA FUNZIONE dbo.daybegin (@dat DATETIME)
RESI smalldatetime AS
INIZIO
INVIO RITORNO (datetime, FLOOR (convert (FLOAT, @dat)))
FINE

Qui, la funzione CONVERT esegue la conversione del tipo. Innanzitutto, il tipo di data e ora viene trasmesso al tipo FLOAT. Con questa riduzione, la parte intera è il numero di giorni, contando dal 1 ° gennaio 1900 e la parte frazionaria è il tempo. Successivamente, l'arrotondamento al numero intero più piccolo si verifica utilizzando la funzione FLOOR e il cast al tipo data-ora.

Controlliamo l'azione della funzione:

SELEZIONA dbo.daybegin (GETDATE ())

Qui GETDATE () è una funzione che restituisce la data e l'ora correnti.

Le funzioni precedenti utilizzavano solo i parametri di input nel calcolo. Tuttavia, è anche possibile utilizzare i dati memorizzati nel database.

Creiamo una funzione che prenderà due date come parametri: l'inizio e la fine di un intervallo di tempo - e calcoliamo le entrate totali delle vendite per questo intervallo. La data e la quantità di vendita verranno prese dalla tabella Vendite, mentre i prezzi per i titoli venduti verranno presi dalla tabella Titoli.

CREATE FUNCTION dbo.SumSales (@datebegin DATETIME, @dateend DATETIME)
RESI Denaro
COME
INIZIO
DECLARE @Sum Money
SELEZIONA @Sum \u003d sum (t.price * s.qty)

RITORNO @Sum
FINE

2.2. Funzioni integrate

Di conseguenza, questo tipo di funzione non restituisce un valore scalare, ma una tabella o piuttosto un set di dati. Ciò può essere molto utile nei casi in cui lo stesso tipo di sottoquery viene spesso eseguito in procedure, trigger diversi, ecc. Quindi, invece di scrivere questa query ovunque, puoi creare una funzione e usarla in futuro.

Funzioni di questo tipo sono ancora più utili nei casi in cui si desidera che la tabella restituita dipenda dai parametri di input. Come sapete, le viste non possono avere parametri, quindi questo tipo di problema può essere risolto solo con funzioni incorporate.

La particolarità delle funzioni in linea è che possono contenere solo una richiesta nel loro corpo. Pertanto, funzioni di questo tipo sono molto simili alle viste, ma possono anche avere parametri di input. Sintassi della funzione integrata:

CREATE FUNCTION [proprietario.] Nome_funzione
([(@ parametro_name scalar_data_type [\u003d default_value]) [, ... n]])
TABELLA DI RESI

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

La definizione della funzione indica che restituirà una tabella;<запрос> - questa è la richiesta, il cui risultato sarà il risultato della funzione.

Scriviamo una funzione simile alla funzione scalare dell'ultimo esempio, ma restituendo non solo il risultato di somma, ma anche le righe delle vendite, tra cui la data di vendita, il titolo del libro, il prezzo, il numero di pezzi e l'importo venduto. Dovrebbero essere selezionate solo le vendite che rientrano nel periodo di tempo specificato. Crittografiamo il testo della funzione in modo che altri utenti possano usarlo, ma non possano leggerlo e correggerlo:

CREATE FUNCTION Sales_Period (@datebegin DATETIME, @dateend DATETIME)
TABELLA DI RESI
CON ENCRYPTION
COME
RITORNO (
SELEZIONA t.title, t.price, s.qty, ord_date, t.price * s.qty come stoim
DA Titoli t ISCRIVITI Vendite ON ON t.title_Id \u003d s.Title_ID
DOVE ord_date TRA @datebegin e @dateend
)

Ora chiamiamo questa funzione. Come già accennato, può essere chiamato solo nella clausola FROM dell'istruzione SELECT:

SELEZIONA * DA Sales_Period ("01.09.94", "13.09.94")

2.3. Funzioni multiistruzione che restituiscono un risultato di tabella

Il primo tipo di funzione considerato ti ha permesso di utilizzare tutte le istruzioni Transact-SQL che desideri, ma ha restituito solo un risultato scalare. Il secondo tipo di funzione potrebbe restituire tabelle, ma il suo corpo rappresenta solo una query. Le funzioni multi-operatore che restituiscono un risultato di tabella consentono di combinare le proprietà delle prime due funzioni, ovvero possono contenere molte istruzioni Transact-SQL nel corpo e restituire una tabella come risultato. Sintassi della funzione multi-operatore:

CREATE FUNCTION [proprietario.] Nome_funzione
([(@ parametro_name scalar_data_type [\u003d default_value]) [, ... n]])
RITORNI @ result_variable_name TABELLA
<описание_таблицы>

INIZIO
<тело_функции>
RITORNO
FINE

• TAVOLO<описание_таблицы> - descrive la struttura della tabella restituita;
• <описание_таблицы> - contiene un elenco di colonne e vincoli.

Ora diamo un'occhiata a un esempio che può essere eseguito solo utilizzando funzioni di questo tipo.

Lascia che ci sia un albero di directory e file in esse contenuti. Lascia che questa intera struttura sia descritta nel database sotto forma di tabelle (Fig. 13). In effetti, qui abbiamo una struttura gerarchica per le directory, quindi il diagramma mostra la relazione della tabella Cartelle con se stessa.

Figura. 13. Struttura del database per la descrizione della gerarchia di file e directory

Ora scriveremo una funzione che prenderà un identificatore di directory come input e visualizzerà tutti i file che sono memorizzati in esso e in tutte le directory nella gerarchia. Ad esempio, se i cataloghi Faculty1, Faculty2, ecc. Vengono creati nel catalogo Institute, hanno cataloghi dipartimentali e ciascuno dei cataloghi contiene file, quindi quando si specifica l'identificatore del catalogo Institute come parametro della nostra funzione, un elenco di tutti i file per tutti queste directory. Nome, dimensioni e data di creazione devono essere visualizzati per ciascun file.

È impossibile risolvere il problema utilizzando la funzione inline, poiché SQL non è progettato per eseguire query gerarchiche, quindi una query SQL non è sufficiente qui. Neanche la funzione scalare può essere applicata, poiché il risultato deve essere una tabella. Qui una funzione multi-operatore che restituisce una tabella verrà in nostro soccorso:

CREA FUNZIONE dbo.GetFiles (@Folder_ID int)
RETURNS @files TABLE (Nome VARCHAR (100), Date_Create DATETIME, FileSize INT) AS
INIZIO
DECLARE @tmp TABLE (Folder_Id int)
DECLARE @Cnt INT
INSERT INTO @tmp valori (@Folder_ID)
SET @Cnt \u003d 1
MENTRE @Cnt<> 0 INIZIA
INSERISCI IN @tmp SELECT Folder_Id
FROM Cartelle f JOIN @tmp t ON f.parent \u003d t.Folder_ID
DOVE F.id NON IN (SELEZIONA Folder_ID FROM @tmp)
SET @Cnt \u003d @@ ROWCOUNT
FINE
INSERT INTO @Files (Name, Date_Create, FileSize)
SELECT F.Name, F.Date_Create, F.FileSize
File FROM f UNISCITI Cartelle Fl on f.Folder_id \u003d Fl.id
ISCRIVITI @tmp t su Fl.id \u003d t.Folder_Id
RITORNO
FINE

Qui, nel ciclo, tutte le sottodirectory a tutti i livelli di annidamento vengono aggiunte alla variabile @tmp fino a quando non rimangono più sottodirectory. Quindi, la variabile di risultato @Files memorizza tutti gli attributi di file necessari nelle directory elencate nella variabile @tmp.

Compiti di studio autonomo

È necessario creare ed eseguire il debug di cinque procedure memorizzate dal seguente elenco richiesto:

Procedura 1. Aumentare la data di scadenza per le copie di un libro di una settimana se la data di scadenza corrente è compresa tra tre giorni prima della data corrente e tre giorni dopo la data corrente.

Procedura 2. Conteggio del numero di copie gratuite di un determinato libro.

Procedura 3. Verifica dell'esistenza di un lettore con il cognome e la data di nascita indicati.

Procedura 4. Inserimento di un nuovo lettore, verifica della sua esistenza nel database e determinazione del suo nuovo numero di tessera della biblioteca.

Procedura 5. Calcolo dell'ammenda in termini monetari per i lettori debitori.

Breve descrizione delle procedure

Procedura 1. Aumentare la data di scadenza dei libri

Per ogni record nella tabella Exemplar, viene verificato se la data di scadenza del libro rientra nell'intervallo di tempo specificato. In tal caso, la data di ritorno del libro viene aumentata di una settimana. Quando si esegue la procedura, è necessario utilizzare la funzione per lavorare con le date:

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

Procedura 2. Conteggio del numero di copie gratuite di un determinato libro

Il parametro di input della procedura è ISBN - un codice a libro unico. La procedura restituisce 0 (zero) se tutte le copie di questo libro sono nelle mani dei lettori. La procedura restituisce un valore N pari al numero di copie del libro che sono attualmente nelle mani dei lettori.

Se il libro con il codice ISBN specificato non è nella libreria, la procedura restituisce –100 (meno cento).

Procedura 3. Verifica dell'esistenza di un lettore con il cognome e la data di nascita indicati

La procedura restituisce il numero del ticket del lettore, se esiste un lettore con tali dati e 0 (zero) in caso contrario.

Quando si confronta la data di nascita, è necessario utilizzare la funzione di conversione Convert () per convertire la data di nascita, una variabile di carattere di tipo Varchar (8) utilizzata come parametro di input nella procedura, in dati di tipo datatime, utilizzati nella tabella Readers. In caso contrario, l'operazione di confronto non funzionerà durante la ricerca del lettore specificato.

Procedura 4. Immettere un nuovo lettore

La procedura ha cinque parametri di input e tre di output.

Parametri di input:

• Nome completo con iniziali;
• Indirizzo;
• Data di nascita;
• Telefono di casa;
• Il telefono funziona.

Parametri di uscita:

• Numero di carta della Biblioteca;
• Un'indicazione se il lettore era stato precedentemente registrato nella libreria (0 - non era, 1 - era);
• Il numero di libri che il lettore possiede.

Procedura 5. Calcolo dell'ammenda in termini monetari per i lettori debitori

La procedura funziona con un cursore che contiene un elenco di numeri di tessere della biblioteca di tutti i debitori. Nel corso del lavoro, dovrebbe essere creata una tabella temporanea globale ## DOLG, in cui per ciascun debitore il suo debito totale in termini monetari sarà inserito per tutti i libri che ha tenuto più a lungo del periodo di rimborso. La compensazione monetaria è calcolata allo 0,5% del prezzo per libro per il giorno di ritardo.

Ordine di lavoro

• schermate (schermate) che confermano le modifiche apportate ai database;
• il contenuto delle tabelle del database necessarie per convalidare il corretto funzionamento;
• il testo della procedura memorizzata con commenti;
• il processo di esecuzione di una procedura memorizzata con l'output dei risultati del lavoro.

Compiti aggiuntivi

Le seguenti procedure memorizzate aggiuntive sono per singoli lavori.

Procedura 6. Contare il numero di libri in una determinata area tematica che sono attualmente disponibili nella biblioteca in almeno una copia. L'area tematica viene passata come parametro di input.

Procedura 7. Immissione di un nuovo libro che indica il numero di copie. Quando si inseriscono copie di un nuovo libro, assicurarsi di inserire i rispettivi numeri di magazzino. Pensa a come puoi farlo. Come promemoria, sono disponibili le funzioni Max e Min che consentono di trovare il valore massimo o minimo di qualsiasi attributo numerico utilizzando una query Seleziona.

Procedura 8. Formazione di una tabella con un elenco di lettori debitori, ovvero quelli che dovevano restituire i libri alla biblioteca, ma non sono ancora tornati. Nella tabella risultante, ogni lettore di debitori dovrebbe apparire una sola volta, indipendentemente da quanti libri deve. Oltre al nome completo e al numero della tessera della biblioteca, l'indirizzo e il numero di telefono devono essere specificati nella tabella risultante.

Procedura 9. Ricerca di una copia gratuita in base al titolo del libro indicato. Se esiste una copia gratuita, la procedura restituisce il numero di inventario dell'istanza; in caso contrario, la procedura restituisce un elenco di lettori che dispongono di questo libro, indicando la data di ritorno del libro e il numero di telefono del lettore.

Procedura 10. Elenco dei lettori che attualmente non detengono libri. Specificare il nome e il numero di telefono nell'elenco.

Procedura 11. Visualizzazione di un elenco di libri che indica il numero di copie di questo libro nella biblioteca e il numero di copie gratuite al momento.

versione stampata

Le stored procedure SQL sono un'unità di programma eseguibile che può essere archiviata come vari oggetti. In altre parole, è un oggetto che contiene istruzioni SQL. Queste procedure memorizzate possono essere eseguite nel client dell'applicazione per ottenere buone prestazioni. Inoltre, tali oggetti sono spesso chiamati da altri script o anche da qualche altra sezione.

introduzione

Molte persone pensano di essere simili a procedure diverse (rispettivamente, tranne MS SQL). Forse è vero. Hanno parametri simili e possono produrre valori simili. Inoltre, in numerosi casi toccano. Ad esempio, possono essere combinati con database DDL e DML e funzioni utente (nome in codice UDF).

In realtà, le stored procedure SQL presentano una vasta gamma di vantaggi che le distinguono da processi simili. Sicurezza, variabilità di programmazione, produttività: tutto ciò attira sempre più utenti che lavorano con i database. Il picco della popolarità delle procedure è diminuito nel periodo 2005-2010, quando è stato rilasciato un programma Microsoft chiamato "SQL Server Management Studio". Con il suo aiuto, lavorare con i database è diventato molto più semplice, più pratico e più conveniente. Di anno in anno questo stava guadagnando popolarità tra i programmatori. Oggi è un programma assolutamente familiare, che per gli utenti che "comunicano" con i database, è aumentato alla pari di Excel.

Quando viene chiamata una procedura, viene immediatamente elaborata dal server stesso senza processi e interventi dell'utente non necessari. Successivamente, è possibile eseguire qualsiasi eliminazione, esecuzione, modifica. Tutto questo è responsabilità dell'operatore DDL, che da solo esegue le operazioni più complesse per l'elaborazione degli oggetti. E tutto ciò accade molto rapidamente e il server non è effettivamente caricato. Tale velocità e prestazioni consentono un trasferimento molto rapido di grandi quantità di informazioni dall'utente al server e viceversa.

Per implementare questa tecnologia per lavorare con le informazioni, esistono diversi linguaggi di programmazione. Questi includono, ad esempio, PL / SQL di Oracle, PSQL nei sistemi InterBase e Firebird, nonché il classico "Microsoft" Transact-SQL. Tutti sono progettati per creare ed eseguire stored procedure, che consente ai gestori di database di grandi dimensioni di utilizzare i propri algoritmi. Ciò è necessario anche affinché coloro che gestiscono tali informazioni possano proteggere tutti gli oggetti dall'accesso non autorizzato da parte di terzi e, di conseguenza, dalla creazione, modifica o cancellazione di determinati dati.

Produttività

Questi oggetti di database possono essere programmati in vari modi. Ciò consente agli utenti di scegliere il tipo di metodo più appropriato, risparmiando tempo e fatica. Inoltre, la procedura viene gestita da sola, evitando così l'enorme tempo impiegato nello scambio tra il server e l'utente. Inoltre, il modulo può essere riprogrammato e modificato nella direzione desiderata in qualsiasi momento. Vale la pena notare in particolare la velocità con cui viene avviata la procedura memorizzata SQL: questo processo è più veloce di altri, simile ad esso, il che lo rende comodo e versatile.

Sicurezza

Questo tipo di elaborazione delle informazioni differisce da processi simili in quanto garantisce una maggiore sicurezza. Ciò è dovuto al fatto che l'accesso di altri utenti alle procedure può essere completamente e completamente escluso. Ciò consentirà all'amministratore di condurre operazioni con loro da solo, senza timore di intercettazione di informazioni o accesso non autorizzato al database.

Trasferimento dati

La relazione tra la procedura memorizzata SQL e l'applicazione client è attraverso l'uso di parametri e valori restituiti. Non è necessario che quest'ultimo passi i dati alla procedura memorizzata, ma queste informazioni (principalmente su richiesta dell'utente) vengono elaborate per SQL. Dopo che la procedura memorizzata ha completato il suo lavoro, invia i pacchetti di dati (ma, di nuovo, facoltativamente) all'applicazione chiamante utilizzando vari metodi che possono essere utilizzati sia per chiamare la procedura memorizzata SQL che per restituirla, ad esempio:

Passare i dati usando un parametro di tipo Output;

Trasferimento di dati mediante una dichiarazione di reso;

Passando i dati usando un operatore selezionato.

E ora scopriamo come appare questo processo dall'interno.

1. Creazione di una stored procedure EXEC in SQL

È possibile creare una procedura in MS SQL (Managment Studio). Una volta creata, la procedura verrà elencata in un nodo di database programmabile, in cui la procedura di creazione viene eseguita dall'operatore. Le stored procedure SQL utilizzano un processo EXEC per l'esecuzione, che contiene il nome dell'oggetto stesso.

Quando viene creata una procedura, il suo nome appare per primo, dopo di che vengono prodotti uno o più parametri assegnati ad essa. I parametri possono essere opzionali. Dopo che i parametri, ovvero il corpo della procedura, sono scritti, è necessario eseguire alcune operazioni necessarie.

Il punto è che il corpo può avere variabili locali localizzate in esso e queste variabili sono anche locali alle procedure. In altre parole, possono essere visualizzati solo nel corpo di una procedura di Microsoft SQL Server. Le procedure memorizzate sono considerate locali in questo caso.

Pertanto, per creare una procedura, abbiamo bisogno del nome della procedura e di almeno un parametro come corpo della procedura. Si noti che un'ottima opzione in questo caso è quella di creare ed eseguire una procedura denominata schema nel classificatore.

Il corpo della procedura può essere di qualsiasi tipo, ad esempio, come creare una tabella, inserire una o più righe di una tabella, stabilire il tipo e la natura del database e così via. Tuttavia, il corpo della procedura limita l'esecuzione di determinate operazioni al suo interno. Alcune delle limitazioni importanti sono elencate di seguito:

Il corpo non dovrebbe creare altre procedure memorizzate;

Il corpo non deve creare una falsa impressione dell'oggetto;

Il corpo non dovrebbe creare alcun innesco.

2. Impostazione di una variabile nel corpo della procedura

È possibile rendere le variabili locali al corpo della procedura, quindi saranno localizzate esclusivamente all'interno del corpo della procedura. È buona norma creare variabili all'inizio del corpo della procedura memorizzata. Ma puoi anche impostare variabili ovunque nel corpo di questo oggetto.

A volte noterai che diverse variabili sono impostate su una riga e ogni parametro variabile è separato da una virgola. Si noti inoltre che la variabile è preceduta da @. Nel corpo della procedura, puoi impostare la variabile dove vuoi. Ad esempio, @ NAME1 può essere dichiarato verso la fine del corpo della procedura. Per assegnare un valore a una variabile dichiarata, viene utilizzato un insieme di dati personali. A differenza di una situazione in cui più di una variabile viene dichiarata su una riga, in tale situazione viene utilizzata solo una serie di dati personali.

Spesso gli utenti pongono la domanda: "Come assegnare più valori in un'istruzione nel corpo di una procedura?" Bene. Una domanda interessante, ma è molto più facile da fare di quanto si pensi. Risposta: usando coppie come "Seleziona Var \u003d valore". È possibile utilizzare queste coppie separandole con una virgola.

Attraverso un'ampia varietà di esempi, le persone mostrano come creare una semplice procedura memorizzata ed eseguirla. Tuttavia, una procedura può accettare parametri tali che il processo chiamante avrà valori vicini (ma non sempre). Se coincidono, i processi corrispondenti iniziano all'interno del corpo. Ad esempio, se si crea una procedura che accetterà la città e la regione dal chiamante e restituirà informazioni su quanti autori appartengono alla città e alla regione corrispondenti. La procedura eseguirà una query nelle tabelle degli autori del database, ad esempio Pub, per eseguire questo conteggio degli autori. Per ottenere questi database, ad esempio, Google scarica lo script SQL dalla pagina SQL2005.

Nell'esempio precedente, la procedura accetta due parametri, che in inglese saranno convenzionalmente denominati @State e @City. Il tipo di dati corrisponde al tipo definito nell'applicazione. Il corpo della procedura ha variabili interne @TotalAuthors (autori totali) e questa variabile viene utilizzata per visualizzare il loro numero. Successivamente, viene visualizzata la sezione di selezione della query, che calcola tutto. Infine, il valore calcolato viene visualizzato nella finestra di output usando un'istruzione print.

Come eseguire una procedura memorizzata in SQL

Esistono due modi per completare la procedura. Il primo percorso mostra, passando i parametri, come viene eseguito un elenco separato da virgole dopo il nome della procedura. Diciamo che abbiamo due valori (come nell'esempio precedente). Questi valori vengono raccolti utilizzando i parametri di procedura @State e @City. In questo modo di passare i parametri, l'ordine è importante. Questo metodo è chiamato passaggio ordinale di argomenti. Nel secondo metodo, i parametri sono già assegnati direttamente, nel qual caso l'ordine non è importante. Questo secondo metodo è noto come passare argomenti denominati.

La procedura può discostarsi in qualche modo da quella tipica. Tutto è uguale all'esempio precedente, ma qui i parametri vengono spostati. Cioè, @City viene memorizzato per primo e @State viene archiviato accanto all'impostazione predefinita. Il parametro predefinito è di solito evidenziato separatamente. Le stored procedure SQL passano solo come parametri. In questo caso, a condizione che il parametro "UT" sostituisca la "CA" predefinita. Nella seconda esecuzione, viene passato solo un valore argomento per il parametro @City e il parametro @State accetta il valore predefinito "CA". I programmatori esperti avvisano che tutte le variabili predefinite sono vicine alla fine dell'elenco dei parametri. Altrimenti, l'esecuzione non è possibile, quindi devi lavorare con il passaggio di argomenti denominati, che è più lungo e più complicato.

4. Stored procedure di SQL Server: metodi di restituzione

Esistono tre modi importanti per inviare i dati in una chiamata procedura memorizzata. Sono elencati di seguito:

Restituzione del valore di una procedura memorizzata;

Output dei parametri della procedura memorizzata;

Selezione di una delle procedure memorizzate.

4.1 Restituzione di valori per le stored procedure SQL

In questa tecnica, la procedura assegna un valore a una variabile locale e lo restituisce. La procedura può anche restituire direttamente un valore costante. Nel seguente esempio, 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 è invertito.

Ora vediamo come eseguire la procedura e stampare il valore restituito. L'esecuzione della procedura richiede l'impostazione di una variabile e la stampa, che viene eseguita dopo l'intero processo. Si noti che anziché un'istruzione print, è possibile utilizzare un'istruzione Select, ad esempio Select @RetValue e OutputValue.

4.2 Output dei parametri delle stored procedure SQL

Il valore restituito può essere utilizzato per restituire una singola variabile, come abbiamo visto nell'esempio precedente. L'uso del parametro Output consente a una procedura di inviare uno o più valori variabili al chiamante. Il parametro output è identificato dalla stessa parola "Output" durante la creazione di una procedura. Se un parametro viene specificato come parametro di output, l'oggetto procedura deve assegnargli un valore. Le stored procedure SQL, di cui è possibile vedere alcuni esempi, vengono quindi restituite con informazioni di riepilogo.

Il nostro esempio avrà due nomi di output: @TotalAuthors e @TotalNoContract. Sono indicati nell'elenco dei parametri. A queste variabili vengono assegnati valori all'interno del corpo della procedura. Quando usiamo i parametri, il chiamante può vedere il valore impostato all'interno del corpo della procedura.

Inoltre, nello script precedente, vengono dichiarate due variabili per visualizzare i valori che impostano le procedure memorizzate di MS SQL Server nel parametro out. Quindi la procedura viene eseguita fornendo il valore normale del parametro "CA". Vengono emessi i seguenti parametri e quindi le variabili dichiarate vengono passate nell'ordine specificato. Si noti che quando si passa attraverso le variabili, qui viene impostata anche la parola chiave di output. Al termine della procedura, i valori restituiti dai parametri di output vengono visualizzati in una finestra di messaggio.

4.3 Selezione di una delle procedure memorizzate SQL

Questa tecnica viene utilizzata per restituire un set di valori come tabella di dati (RecordSet) alla procedura memorizzata di chiamata. In questo esempio SQL, una procedura memorizzata con parametri @AuthID interroga la tabella degli autori filtrando i record restituiti con questo parametro @AuthId. L'istruzione Select decide cosa deve essere restituito al chiamante della procedura memorizzata. Quando viene eseguita la procedura memorizzata, AuthId viene restituito. Tale procedura restituisce sempre solo un record o nessuno. Ma la procedura memorizzata non ha restrizioni sulla restituzione di più di un record. Non è raro vedere esempi in cui si verifica la restituzione dei dati utilizzando parametri selezionati che coinvolgono variabili calcolate fornendo più totali.

Finalmente

Una procedura memorizzata è un software piuttosto serio che restituisce o passa e imposta le variabili richieste tramite l'applicazione client. Poiché la procedura memorizzata si esegue automaticamente sul server, è possibile evitare enormi quantità di scambio di dati tra il server e l'applicazione client (per alcuni calcoli). Ciò consente di ridurre il carico sui server SQL, che, ovviamente, passa nelle mani dei loro titolari. Le procedure memorizzate SQL sono una delle sottospecie, ma il loro studio è necessario per coloro che sono impegnati nella creazione di database impressionanti. Esistono anche molte, anche enormi quantità di sfumature che possono essere utili quando si studiano le procedure memorizzate, ma ciò è necessario di più per coloro che hanno intenzione di intraprendere da vicino la programmazione, anche a livello professionale.

Obbiettivo - impara come creare e utilizzare le procedure memorizzate sul server di database.

1. Elaborazione di tutti gli esempi, analisi dei risultati della loro esecuzione nell'utilità SQL Server Management Studio. Verifica della presenza di procedure create nel database corrente.

2. Completamento di tutti gli esempi e incarichi nel corso del lavoro di laboratorio.

3. Esecuzione di singoli incarichi mediante opzioni.

Spiegazioni per il lavoro

Per padroneggiare la programmazione delle procedure memorizzate, useremo un esempio di un database chiamato DB_Books, creato nel lavoro di laboratorio n. 1. Quando si eseguono esempi e assegnazioni, prestare attenzione alla corrispondenza dei nomi del database, delle tabelle e di altri oggetti del progetto.

Procedura di archiviazione sono una serie di comandi, costituiti da una o più istruzioni o funzioni SQL, e memorizzati in un database in forma compilata.

Tipi di procedure memorizzate

Le stored procedure di sistema sono progettate per eseguire varie azioni amministrative. Quasi tutte le azioni di amministrazione del server vengono eseguite con il loro aiuto. Possiamo dire che le stored procedure di sistema sono l'interfaccia per lavorare con le tabelle di sistema. Le stored procedure di sistema sono precedute da sp_, sono archiviate nel database di sistema e possono essere richiamate nel contesto di qualsiasi altro database.

Le stored procedure personalizzate implementano determinate azioni. Le procedure memorizzate sono un oggetto database completo. Di conseguenza, ogni procedura memorizzata si trova in un database specifico, dove viene eseguita.

Le stored procedure temporanee esistono solo per un breve periodo, dopo di che vengono automaticamente distrutte dal server. Sono divisi in locali e globali. Le stored procedure temporanee locali possono essere chiamate solo dalla connessione in cui sono state create. Quando si crea una tale procedura, deve essere assegnato un nome che inizia con un carattere #. Come tutti gli oggetti temporanei, le procedure memorizzate di questo tipo vengono eliminate automaticamente quando l'utente si disconnette, riavvia o arresta il server. Le stored procedure temporanee globali sono disponibili per tutte le connessioni al server che hanno la stessa procedura. Per definirlo, devi solo dargli un nome che inizia con i simboli ##. Queste procedure vengono eliminate al riavvio o all'arresto del server o alla chiusura della connessione nel contesto in cui sono state create.

Creazione, modifica di stored procedure

La creazione di una procedura memorizzata comporta la risoluzione dei seguenti problemi: pianificazione dei diritti di accesso. Quando si crea una procedura memorizzata, tenere presente che avrà gli stessi diritti di accesso agli oggetti del database dell'utente che l'ha creata; definizione di parametri di una stored procedure, le stored procedure possono avere parametri di input e output; sviluppo di codice di procedura memorizzata. Il codice di procedura può contenere qualsiasi sequenza di comandi SQL, comprese le chiamate ad altre procedure memorizzate.

La sintassi per l'operatore di creare una nuova o modificare una stored procedure esistente nella notazione MS SQL Server:

(CREATE | ALTER) PROC [EDURE] procedure_name [; number] [(@ parametro_name tipo di dati) [VARYING] [\u003d DEFAULT] [OUTPUT]] [, ... n] [WITH (RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION)] [FOR REPLICATION] AS sql_ statement [... n]

Consideriamo i parametri di questo comando.

Utilizzando i prefissi sp_, #, ##, la procedura creata può essere definita come sistema o temporanea. Come puoi vedere dalla sintassi del comando, non è consentito specificare il nome del proprietario proprietario della procedura creata, nonché il nome del database in cui dovrebbe trovarsi. Pertanto, per posizionare la procedura memorizzata che si sta creando in un database specifico, è necessario eseguire il comando CREATE PROCEDURE nel contesto di quel database. Quando si fa riferimento a oggetti dello stesso database dal corpo di una procedura memorizzata, è possibile utilizzare nomi abbreviati, ovvero senza specificare il nome del database. Quando è necessario fare riferimento a oggetti situati in altri database, è necessario specificare il nome del database.

I nomi dei parametri devono iniziare con il simbolo @ per passare i dati di input e output alla procedura memorizzata generata. È possibile specificare più parametri in un'unica procedura memorizzata, separati da virgole. Il corpo di una procedura non dovrebbe usare variabili locali i cui nomi sono uguali ai nomi dei parametri di questa procedura. Qualsiasi tipo di dati SQL, incluso definito dall'utente, è adatto per definire il tipo di dati dei parametri della procedura memorizzata. Tuttavia, il tipo di dati CURSOR può essere utilizzato solo come parametro di output di una procedura memorizzata, ad es. specificando la parola chiave OUTPUT.

La presenza della parola chiave OUTPUT indica che il parametro corrispondente è per la restituzione dei dati da una procedura memorizzata. Tuttavia, ciò non significa che il parametro non sia adatto per il passaggio di valori a una procedura memorizzata. La specifica della parola chiave OUTPUT indica al server, quando si esce dalla procedura memorizzata, di assegnare il valore del parametro corrente alla variabile locale specificata come valore del parametro quando si chiama la procedura. Notare che quando viene specificata la parola chiave OUTPUT, il valore del parametro corrispondente quando si chiama una procedura può essere impostato solo usando una variabile locale. Non è consentito utilizzare espressioni o costanti valide per parametri normali. La parola chiave VARYING viene utilizzata insieme al parametro OUTPUT di tipo CURSOR. Specifica che il parametro di output sarà il set di risultati.

La parola chiave DEFAULT è un valore che accetterà il parametro predefinito corrispondente. Pertanto, quando si chiama una procedura, non è possibile specificare esplicitamente il valore del parametro corrispondente.

Poiché il server memorizza nella cache il piano di esecuzione della query e il codice compilato, la chiamata successiva alla procedura utilizzerà i valori già pronti. Tuttavia, in alcuni casi, è comunque necessario ricompilare il codice della procedura. La specifica della parola chiave RECOMPILE indica al sistema di generare un piano di esecuzione per la procedura memorizzata ogni volta che viene chiamata.

Il parametro FOR REPLICATION è necessario per replicare i dati e includere la procedura memorizzata generata come articolo in una pubblicazione. La parola chiave ENCRYPTION indica al server di crittografare il codice della procedura memorizzata, che può fornire protezione dagli algoritmi di creazione che implementano la procedura memorizzata. La parola chiave AS viene posizionata all'inizio del corpo della procedura memorizzata effettiva. Il corpo di una procedura può utilizzare quasi tutti i comandi SQL, dichiarare transazioni, acquisire blocchi e chiamare altre procedure memorizzate. È possibile uscire dalla procedura memorizzata utilizzando il comando RETURN.

Eliminazione di una procedura memorizzata

DROP PROCEDURE (procedure_name) [, ... n]

Esecuzione della procedura memorizzata

Per eseguire la procedura memorizzata, utilizzare il comando: [[EXEC [UTE] procedure_name [; numero] [[@ parametro_name \u003d] (valore | @ variabile_name) [OUTPUT] | [DEFAULT]] [, ... n]

Se la chiamata della procedura memorizzata non è l'unico comando nel pacchetto, è necessario il comando EXECUTE. Inoltre, questo comando è necessario per chiamare una procedura dal corpo di un'altra procedura o trigger.

L'uso della parola chiave OUTPUT in una chiamata di procedura è consentito solo per i parametri dichiarati quando la procedura è stata creata con la parola chiave OUTPUT.

Quando viene specificata la parola chiave DEFAULT per un parametro quando si chiama una procedura, verrà utilizzato il valore predefinito. Naturalmente, la parola DEFAULT specificata è consentita solo per quei parametri per i quali è definito un valore predefinito.

Dalla sintassi del comando EXECUTE è possibile vedere che i nomi dei parametri possono essere omessi quando si chiama una procedura. Tuttavia, in questo caso, l'utente deve fornire i valori per i parametri nello stesso ordine in cui sono stati elencati durante la creazione della procedura. Non è possibile assegnare un valore predefinito a un parametro semplicemente saltandolo durante l'enumerazione. Se è necessario omettere i parametri per i quali è definito un valore predefinito, è sufficiente specificare esplicitamente i nomi dei parametri quando si chiama la procedura memorizzata. Inoltre, in questo modo è possibile elencare i parametri e i loro valori in qualsiasi ordine.

Si noti che quando si chiama una procedura, vengono specificati i nomi dei parametri con valori o solo i valori senza nome di parametro. La loro combinazione non è consentita.

Utilizzo di INVIO in una stored procedure

Consente di uscire dalla procedura in qualsiasi momento in base alla condizione specificata e inoltre di trasmettere il risultato della procedura come numero, in base al quale è possibile giudicare la qualità e la correttezza della procedura. Un esempio di creazione di una procedura senza parametri:

CREA PROCEDURA Count_Books COME SELEZIONA COUNT (Code_book) DA Books GO

Esercizio 1.

EXEC Count_Books

Controlla il risultato

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

CREA PROCEDURA Count_Books_Pages @Count_pages COME INT COME SELEZIONA COUNT (Code_book) DA Libri DOVE Pagine\u003e \u003d @Count_pages GO

Cessione 2... Creare questa procedura nella sezione Stored Procedures del database DB_Books utilizzando l'utilità di SQL Server Management Studio. Eseguilo con il comando

EXEC Count_Books_Pages 100

Controlla il risultato

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

CREA PROCEDURA Count_Books_Title @Count_pages COME INT, @Title COME CHAR (10) COME SELEZIONA CONTEGGIO (Code_book) DA Libri DOVE Pagine\u003e \u003d @Count_pages E Title_book COME @Title GO

Compito 3. Creare questa procedura nella sezione Stored Procedures del database DB_Books utilizzando l'utilità di SQL Server Management Studio. Eseguilo con il comando

EXEC Count_Books_Title 100, "P%"

Controlla il risultato

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

CREA PROCEDURA Count_Books_Itogo @Count_pages INT, @Title CHAR (10), @Itogo INT OUTPUT AS SELECT @Itogo \u003d COUNT (Code_book) DA Libri DOVE Pagine\u003e \u003d @Count_pages AND Title_book LIKE @Title GO

Compito 4. Creare questa procedura nella sezione Stored Procedures del database DB_Books utilizzando l'utilità di SQL Server Management Studio. Esegui con il set di comandi:

Sql\u003e Dichiara @q As 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 INVIO:

CREATE PROCEDURE checkname @param INT AS IF (SELEZIONA Name_author DA autori DOVE Code_author \u003d @param) \u003d "Pushkin A.S." RITORNO 1 RITORNO ALTRO 2

Compito 5. Creare questa procedura nella sezione Stored Procedures del database DB_Books utilizzando l'utilità di SQL Server Management Studio. Inizia con i comandi:

DECLARE @return_status INT EXEC @return_status \u003d checkname 1 SELEZIONA "Return Status" \u003d @return_status

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

CREATE PROC update_proc COME AGGIORNAMENTO Acquisti SET Code_purchase \u003d Code_purchase * 2

Compito 6. Creare questa procedura nella sezione Stored Procedures del database DB_Books utilizzando l'utilità di SQL Server Management Studio. Eseguilo con il comando

EXEC update_proc

Un esempio di una procedura con un parametro di input per ottenere tutte le informazioni su un autore specifico:

CREATE PROC select_author @k CHAR (30) COME SELEZIONA * DA Autori DOVE name_author \u003d @k

Compito 7.

EXEC select_author "Pushkin A.S." oppure select_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 un valore predefinito per aumentare il valore di un campo chiave nella tabella Acquisti di un determinato numero di volte (per impostazione predefinita, 2 volte):

CREATE PROC update_proc @p INT \u003d 2 COME AGGIORNAMENTO Acquisti SET Code_purchase \u003d Code_purchase * @p

La procedura non restituisce alcun dato.

Compito 8. Creare questa procedura nella sezione Stored Procedures del database DB_Books utilizzando l'utilità di SQL Server Management Studio. Inizia con i comandi:

EXEC update_proc 4 o EXEC update_proc @p \u003d 4 o EXEC update_proc - verrà 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 effettuati durante il periodo specificato:

CREATE PROC count_purchases @ d1 SMALLDATETIME, @ d2 SMALLDATETIME, @c INT OUTPUT AS SELECT @ c \u003d COUNT (Code_purchase) FROM Acquisti DOVE Date_order TRA @ d1 AND @ d2 SET @c \u003d ISNULL (@c, 0)

Compito 9. Creare questa procedura nella sezione Stored Procedures del database DB_Books utilizzando l'utilità di SQL Server Management Studio. Inizia con i comandi:

DECLARE @ c2 INT EXEC count_purchases '01 - giu- 2006 ’, ’01 - lug- 2006’, @ c2 OUTPUT SELECT @ c2

Opzioni per incarichi per lavori di laboratorio n. 4

Disposizioni generali. Nell'utilità SQL Server Management Studio, creare una nuova pagina per il codice (pulsante Crea query). A livello di programmazione, rendere attivi i DB_Books creati utilizzando l'operatore Usa. Crea procedure memorizzate usando Crea istruzioni di procedura e definisci tu stesso i nomi delle procedure. Ogni procedura eseguirà una query SQL eseguita nel secondo laboratorio. Inoltre, il codice di query SQL deve essere modificato in modo che sia possibile trasferire i valori dei campi in base ai quali viene eseguita la ricerca.

Ad esempio, l'attività originale e la query nel laboratorio n. 2:

/ * Selezionare dall'elenco dei fornitori (tabella Consegne) i nomi di società, telefoni e TIN (campi Nome_azienda, Telefono e INN), che hanno il nome dell'azienda (campo Nome_azienda) "OJSC MIR".

SELEZIONA Nome_azienda, Telefono, INN DA Consegne DOVE Nome_azienda \u003d "OJSC MIR"

* / –In questo lavoro, verrà creata una procedura:

CREATE PROC select_name_company @comp CHAR (30) AS SELECT Name_company, Telefono, INN FROM Consegne DOVE Name_company \u003d @comp

–Per avviare la procedura, utilizzare il comando:

EXEC select_name_company "OJSC MIR"

Elenco delle attività

Creare un nuovo programma in SQL Server Management Studio. Attiva a livello di programmazione un singolo database attivo, creato nel lavoro di laboratorio n. 1, utilizzando l'operatore Usa. Crea procedure memorizzate usando Crea istruzioni di procedura e definisci tu stesso i nomi delle procedure. Ogni procedura eseguirà una query SQL, che vengono presentate come attività separate da opzioni.

opzione 1

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

2. Visualizza un elenco di bambini a cui sono stati dati regali durante il periodo specificato.

3. Visualizza un elenco di genitori che hanno figli minori.

4. Visualizza informazioni sui regali con un valore maggiore del numero specificato, ordinati per data.

opzione 2

1. Visualizza un elenco di dispositivi con il tipo specificato.

2. Visualizza il numero di dispositivi riparati e il costo totale delle riparazioni dal master specificato.

3. Visualizza l'elenco dei proprietari dei dispositivi e il numero delle loro chiamate, ordinati in base al numero di chiamate in ordine decrescente.

4. Visualizzare le informazioni sui master con un rango superiore al numero specificato o con la data di assunzione inferiore alla data specificata.

Opzione 3

2. Visualizza un elenco di codici di vendita per i quali sono stati venduti fiori per un importo superiore al numero specificato.

3. Visualizza la data di vendita, l'importo, il venditore e il fiore per il codice di vendita specificato.

4. Elencare i fiori e una varietà di fiori con un'altezza maggiore del numero o della fioritura specificati.

Opzione 4

1. Visualizza un elenco di farmaci con l'indicazione indicata per l'uso.

2. Visualizza un elenco di date di consegna per le quali è stato venduto più del numero specificato del farmaco con lo stesso nome.

3. Visualizza la data di consegna, l'importo, il nome completo del gestore dal fornitore e il nome del farmaco in base al codice di ricevuta superiore al numero specificato.

Opzione 5

2. Visualizzare un elenco di apparecchiature disattivate per il motivo specificato.

3. Visualizzare la data di ricezione, il nome dell'attrezzatura, il nome della persona responsabile e la data di cancellazione per l'apparecchiatura cancellata durante il periodo specificato.

4. Visualizza un elenco di apparecchiature con il tipo specificato o con la data di ricezione superiore a un determinato valore

Opzione 6

1. Visualizza un elenco di piatti con un peso superiore al numero specificato.

2. Visualizza un elenco di prodotti con il frammento di parola specificato nel nome.

3. Output il volume del prodotto, il nome del piatto, il nome del prodotto con il codice piatto dal valore iniziale specificato a un valore finale specifico.

4. Visualizza l'ordine di preparazione del piatto e il nome del piatto con la quantità di carboidrati maggiore di un determinato valore o il numero di calorie maggiore del valore specificato.

Opzione 7

1. Visualizza un elenco di dipendenti con la posizione specificata.

3. Visualizza la data di registrazione, il tipo di documento, il nome completo del registrar e il nome dell'organizzazione per i documenti registrati durante il periodo specificato.

4. Visualizza un elenco di documenti registrati con un determinato tipo di documento o con una data di registrazione maggiore del valore specificato.

Opzione 8

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

3. Visualizza la data di registrazione, il motivo del licenziamento, il nome completo dell'impiegato per i documenti registrati durante il periodo specificato.

Opzione 9

1. Visualizza un elenco di dipendenti che si sono congedati del tipo specificato.

2. Visualizza un elenco di documenti con la data di registrazione nel periodo specificato.

3. Visualizza la data di registrazione, il tipo di vacanza, il nome completo dell'impiegato per i documenti registrati durante il periodo specificato.

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

Opzione 10

1. Visualizza un elenco di dipendenti con la posizione specificata.

2. Visualizza un elenco di documenti contenente il frammento di parole specificato.

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

4. Elencare i documenti registrati con il tipo di documento specificato o con un codice documento inferiore a un determinato valore.

Opzione 11

1. Visualizza un elenco di dipendenti assegnati alla posizione specificata.

2. Visualizza un elenco di documenti con la data di registrazione nel periodo specificato.

3. Visualizza la data di registrazione, la posizione, il nome completo dell'impiegato per i documenti registrati durante il periodo specificato.

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

Opzione 12

3. Visualizza un elenco di persone che hanno noleggiato l'attrezzatura e il numero delle loro chiamate, ordinati per numero di chiamate in ordine decrescente.

Opzione 13

1. Visualizza un elenco di apparecchiature con il tipo specificato. 2. Visualizza un elenco di apparecchiature cancellate da un determinato dipendente.

3. Visualizza la quantità di apparecchiature disattivate, raggruppate per tipo di apparecchiatura.

4. Visualizzare informazioni sui dipendenti con una data di assunzione superiore a una determinata data.

Opzione 14

1. Elencare i fiori con il tipo di foglia specificato.

2. Visualizza un elenco di codici ricevuta per i quali sono stati venduti fiori per importi superiori a un determinato valore.

3. Visualizza la data di ricezione, l'importo, il nome del fornitore e i colori per un codice fornitore specifico.

4. Elencare i fiori e una varietà di fiori con un'altezza maggiore di un certo numero o fioritura.

Opzione 15

1. Visualizza un elenco di clienti che hanno effettuato il check-in nelle camere durante il periodo specificato.

2. Visualizza l'importo totale dei pagamenti per le camere per ciascun cliente.

3. Visualizza la data di arrivo, il tipo di camera, il nome completo dei clienti registrati durante il periodo specificato.

4. Visualizza un elenco di clienti registrati in stanze di un certo tipo.

Opzione 16

1. Visualizza un elenco di apparecchiature con il tipo specificato.

2. Visualizza un elenco di attrezzature noleggiate da un cliente specifico.

3. Visualizza un elenco di persone che hanno noleggiato l'attrezzatura e il numero delle loro chiamate, ordinati per numero di chiamate in ordine decrescente.

4. Visualizza le informazioni sui client, ordinate per indirizzi.

Opzione 17

1. Visualizzare un elenco di valori con un valore di acquisto maggiore di un determinato valore o un periodo di garanzia maggiore di un numero specificato.

2. Visualizza un elenco di posizioni di risorse materiali, nel nome del quale compare la parola specificata.

3. Visualizza la somma del valore degli oggetti di valore con un codice nell'intervallo specificato.

4. Visualizzare un elenco di persone finanziariamente responsabili con la data di assunzione nell'intervallo specificato.

Opzione 18

1. Visualizza un elenco di riparazioni eseguite da un master specifico.

2. Elencare le fasi del lavoro incluse nell'opera, nel titolo di cui compare la parola specificata.

3. Visualizza la somma del costo delle fasi di riparazione per lavoro con un codice nell'intervallo specificato.

4. Visualizza un elenco di master con la data di assunzione nell'intervallo specificato.

Opzione 19

1. Visualizza un elenco di farmaci con un'indicazione specifica.

2. Visualizza un elenco di numeri di controllo per i quali sono stati venduti più di un certo numero di farmaci.

3. Visualizza la data di vendita, l'importo, il nome completo della cassa e la medicina sulla ricevuta con il numero specificato.

4. Visualizzare un elenco di farmaci e unità di misura per farmaci con una quantità del pacchetto superiore a un numero specificato o un codice farmaco inferiore a un determinato valore.

Opzione 20

1. Visualizza un elenco di dipendenti con la posizione specificata.

2. Visualizza un elenco di documenti contenente il frammento di parole specificato.

3. Visualizzare la data di registrazione, il tipo di documento, il nome completo del contraente e il fatto dell'esecuzione dei documenti registrati durante il periodo specificato.

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

Ci sono molte nuove funzionalità in MySQL 5, una delle più significative delle quali è la creazione di stored procedure. In questo tutorial, parlerò di cosa sono e di come possono semplificarti la vita.

introduzione

Una procedura memorizzata è un modo per incapsulare azioni ripetitive. Nelle procedure memorizzate, è possibile dichiarare variabili, manipolare flussi di dati e utilizzare altre tecniche di programmazione.

Il motivo della loro creazione è chiaro e confermato dal loro uso frequente. D'altra parte, se parli con coloro che non lavorano regolarmente con loro, le opinioni si divideranno in due fianchi completamente opposti. Non dimenticarlo.

Dietro a

• Condivisione della logica con altre applicazioni. Le procedure memorizzate incapsulano la funzionalità; ciò garantisce coerenza nell'accesso e nella gestione dei dati tra diverse applicazioni.
• Isolamento degli utenti dalle tabelle del database. Ciò consente di dare accesso alle procedure memorizzate, ma non ai dati effettivi della tabella.
• Fornisce un meccanismo di difesa. Come per il punto precedente, se è possibile accedere ai dati solo tramite procedure memorizzate, nessun altro può cancellare i dati tramite il comando SQL DELETE.
• Prestazioni migliorate in conseguenza della riduzione del traffico di rete. Con le procedure memorizzate, è possibile combinare più query.

Contro

• Aumento del carico sul server database a causa del fatto che la maggior parte del lavoro viene eseguita sul lato server e meno sul lato client.
• C'è molto da imparare. Dovrai imparare la sintassi delle espressioni MySQL per scrivere le tue procedure memorizzate.
• Duplicate la logica dell'applicazione in due punti: codice lato server e codice per le procedure memorizzate, complicando così il processo di manipolazione dei dati.
• La migrazione da un DBMS a un altro (DB2, SQL Server, ecc.) Può causare problemi.

Lo strumento su cui sto lavorando si chiama MySQL Query Browser ed è abbastanza standard per interagire con i database. Lo strumento da riga di comando MySQL è un'altra scelta eccellente. Te lo sto dicendo perché phpMyAdmin preferito da tutti non supporta l'esecuzione di stored procedure.

A proposito, sto usando una struttura di tabella rudimentale per rendere più facile la comprensione di questo argomento. Sto parlando di stored procedure, che sono abbastanza complesse da approfondire la struttura ingombrante dei tavoli.

Passaggio 1: impostare il limitatore

Un delimitatore è un carattere o una stringa di caratteri che viene utilizzato per indicare al client MySQL che hai finito di scrivere un'istruzione SQL. Per secoli, il punto e virgola è stato il delimitatore. Tuttavia, possono sorgere problemi perché in una stored procedure possono essere presenti più espressioni, ognuna delle quali deve terminare con un punto e virgola. In questo tutorial, uso la stringa "//" come delimitatore.

Passaggio 2: come lavorare con le stored procedure

Creazione di una procedura memorizzata

DELIMITER // CREATE PROCEDURE `p2` () LINGUA SQL DETERMINISTICO SQL SECURITY DEFINER COMMENT" "Una procedura" INIZIA SELEZIONA "Hello World!"; FINE //

Il primo pezzo di codice crea una procedura memorizzata. Il prossimo contiene parametri opzionali. Quindi viene il nome e, infine, il corpo della procedura stessa.

I nomi delle procedure memorizzati fanno distinzione tra maiuscole e minuscole. Inoltre, non è possibile creare più procedure con lo stesso nome. Non ci possono essere espressioni all'interno di una procedura memorizzata che modifica il database stesso.

4 caratteristiche di una procedura memorizzata:

• Lingua: ai fini della portabilità, SQL è specificato per impostazione predefinita.
• Deterministico: se la procedura restituisce sempre lo stesso risultato e accetta gli stessi parametri di input. Questo è per il processo di replica e registrazione. L'impostazione predefinita NON È DETERMINISTICA.
• Sicurezza SQL: durante la chiamata, vengono controllati i diritti dell'utente. INVOKER è l'utente che chiama la procedura memorizzata. DEFINER è il "creatore" della procedura. L'impostazione predefinita è DEFINER.
• Commento: ai fini della documentazione, il valore predefinito è ""

Chiamata di procedura memorizzata

Per chiamare una procedura memorizzata, è necessario digitare la parola chiave CALL seguita dal nome della procedura, seguito dai parametri (variabili o valori) tra parentesi. Sono necessarie le parentesi.

CALL stored_procedure_name (param1, param2, ....) CALL procedure1 (10, "parametro stringa", @parameter_var);

Modifica di una procedura memorizzata

MySQL fornisce un'istruzione ALTER PROCEDURE per la modifica delle procedure, ma è adatto a modificare solo alcune caratteristiche. Se è necessario modificare i parametri o il corpo della procedura, è necessario eliminarlo e ricrearlo.

Eliminazione di una procedura memorizzata

PROCEDURA DROP SE ESISTE p2;

Questo è un comando semplice. L'espressione IF EXISTS rileva un errore se tale procedura non esiste.

Passaggio 3: Opzioni

Vediamo come è possibile passare i parametri a una procedura memorizzata.

• CREATE PROCEDURE proc1 (): elenco parametri vuoto
• CREATE PROCEDURE proc1 (IN Varname DATA-TYPE): un parametro di input. La parola IN è facoltativa perché i parametri predefiniti sono IN (in entrata).
• CREATE PROCEDURE proc1 (OUT varname DATA-TYPE): un parametro di ritorno.
• CREATE PROCEDURE proc1 (INOUT varname DATA-TYPE): un parametro, sia in entrata che in ritorno.

Naturalmente, puoi specificare diversi parametri di diversi tipi.

Esempio di parametro IN

DELIMITER // CREATE PROCEDURE `proc_IN` (IN var1 INT) INIZIA SELEZIONA var1 + 2 come risultato AS; FINE //

Esempio di parametro OUT

DELIMITER // CREATE PROCEDURE `proc_OUT` (OUT var1 VARCHAR (100)) BEGIN SET var1 \u003d" Questo è un test "; FINE //

Esempio di parametro INOUT

DELIMITER // CREATE PROCEDURE `proc_INOUT` (OUT var1 INT) INIZIA SET var1 \u003d var1 * 2; FINE //

Passaggio 4: variabili

Ora ti insegnerò come creare variabili e memorizzarle all'interno di procedure. È necessario dichiararli esplicitamente all'inizio del blocco BEGIN / END, insieme ai relativi tipi di dati. Dopo aver dichiarato una variabile, è possibile utilizzarla nella stessa posizione di variabili di sessione, valori letterali o nomi di colonne.

La sintassi per dichiarare una variabile è simile alla seguente:

DECLARE varname DATA-TYPE DEFAULT valore predefinito;

Dichiariamo alcune variabili:

DECLARE a, b INT DEFAULT 5; DECLARE str VARCHAR (50); DECLARE oggi TIMESTAMP DEFAULT CURRENT_DATE; DECLARE v1, v2, v3 TINYINT;

Lavorare con le variabili

Dopo aver dichiarato una variabile, è possibile impostare un valore utilizzando i comandi SET o SELECT:

DELIMITER // CREATE PROCEDURE `var_proc` (IN paramstr VARCHAR (20)) INIZIA DECLARE a, b INT DEFAULT 5; DECLARE str VARCHAR (50); DECLARE oggi TIMESTAMP DEFAULT CURRENT_DATE; DECLARE v1, v2, v3 TINYINT; INSERIRE nella tabella1 VALORI (a); SET str \u003d "I am a string"; SELEZIONA CONCAT (str, paramstr), oggi DA tabella2 DOVE b\u003e \u003d 5; FINE //

Passaggio 5: Strutture di controllo del flusso

MySQL supporta i costrutti IF, CASE, ITERATE, LEAVE LOOP, WHILE e REPEAT per il controllo del flusso all'interno di una procedura memorizzata. Vedremo come utilizzare IF, CASE e WHILE in quanto sono i più comunemente utilizzati.

IF costruzione

Usando il costrutto IF, possiamo eseguire compiti contenenti condizioni:

DELIMITER // CREATE PROCEDURE `proc_IF` (IN param1 INT) BEGIN DECLARE variabile1 INT; SET variabile1 \u003d param1 + 1; SE variabile1 \u003d 0 POI SELEZIONA variabile1; FINISCI SE; SE param1 \u003d 0 POI SELEZIONA "Valore parametro \u003d 0"; ELSE SELECT "Valore parametro<> 0 "; END IF; END //

Costruzione CASE

CASE è un altro metodo per verificare le condizioni e scegliere la soluzione giusta. Questo è un ottimo modo per sostituire molti costrutti IF. Il costrutto può essere descritto in due modi, offrendo flessibilità nella gestione di più condizionali.

DELIMITER // CREATE PROCEDURE `proc_CASE` (IN param1 INT) INIZIA DECLARE variabile1 INT; SET variabile1 \u003d param1 + 1; CASE variabile1 QUANDO 0 POI INSERIRE IN Tabella1 VALORI (param1); QUANDO 1 INSERISCI NELLA Tabella 1 VALORI (variabile1); INSERISCI ALTRO NELLA Tabella 1 VALORI (99); CASSA FINE; FINE //

DELIMITER // CREATE PROCEDURE `proc_CASE` (IN param1 INT) INIZIA DECLARE variabile1 INT; SET variabile1 \u003d param1 + 1; CASO QUANDO variabile1 \u003d 0 POI INSERIRE IN Tabella1 VALORI (param1); QUANDO variabile1 \u003d 1 POI INSERISCI NELLA Tabella1 VALORI (variabile1); INSERISCI ALTRO NELLA Tabella 1 VALORI (99); CASSA FINE; FINE //

MENTRE costruzione

Tecnicamente, ci sono tre tipi di loop: loop WHILE, loop LOOP e loop REPEAT. Puoi anche organizzare un loop usando la tecnica di programmazione di Darth Vader: espressioni GOTO. Ecco un esempio di un ciclo:

DELIMITER // CREATE PROCEDURE `proc_WHILE` (IN param1 INT) BEGIN DECLARE variabile1, variabile2 INT; SET 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 attraversare l'insieme di righe restituite da una query e anche elaborare ciascuna riga.

MySQL supporta i cursori nelle procedure memorizzate. Ecco una sintassi rapida per la creazione e l'utilizzo di un cursore.

DECLARE cursore nome-cursore CURSORE PER SELEZIONA ...; / * Dichiarare il cursore e compilarlo * / DECLARE CONTINUA IL HANDLER PER NON TROVATO / * Cosa fare quando non ci sono più record * / OPEN nome-cursore; / * Apri cursore * / FETCH nome-cursore INTO variabile [, variabile]; / * Assegna un valore a una variabile uguale al valore corrente della colonna * / CLOSE nome-cursore; / * Chiudi cursore * /

In questo esempio, eseguiremo alcune semplici operazioni con il cursore:

DELIMITER // CREATE PROCEDURE `proc_CURSOR` (OUT param1 INT) INIZIA DECLARE a, b, c INT; DECLARE cur1 CURSORE PER SELEZIONA col1 DA tabella1; DICHIARA CONTINUA HANDLER PER IL SET NON TROVATO b \u003d 1; APERTO cur1; SET b \u003d 0; SET c \u003d 0; MENTRE b \u003d 0 FETCH cur1 INTO a; IF b \u003d 0 THEN SET c \u003d c + a; FINISCI SE; FINE MENTRE; CHIUDI cur1; SET param1 \u003d c; FINE //

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

• Insensibile: una volta aperto, il cursore non rifletterà le modifiche successive alla tabella. In realtà, MySQL non garantisce che il cursore verrà aggiornato, quindi non sperare.
• Sola lettura: i cursori non possono essere modificati.
• Nessun riavvolgimento: il cursore può spostarsi solo in una direzione: in avanti, non è possibile saltare le linee senza selezionarle.

Conclusione

In questo tutorial, ti ho presentato le basi per lavorare con le procedure memorizzate e alcune delle proprietà specifiche ad esso associate. Naturalmente, dovrai approfondire la tua conoscenza di aree come sicurezza, espressioni SQL e ottimizzazione prima di diventare un vero guru delle procedure MySQL.

È necessario calcolare i vantaggi dell'utilizzo delle procedure memorizzate nella propria applicazione specifica e solo successivamente creare solo le procedure necessarie. In generale, utilizzo le procedure; a mio avviso, dovrebbero essere implementati in progetti a causa della loro sicurezza, manutenzione del codice e design generale. Inoltre, tieni presente che sono ancora in corso lavori sulle procedure MySQL. Aspettati miglioramenti e miglioramenti funzionali. Non esitare a condividere le tue opinioni.