Procediments emmagatzemats de SQL Server

Els procediments emmagatzemats ofereixen una alta eficiència i avantatges de seguretat

Microsoft SQL Server proporciona el mecanisme de procediments emmagatzemats per simplificar el procés de desenvolupament de la base de dades agrupant les instruccions de Transact-SQL en blocs manejables. Els procediments emmagatzemats són apreciats per la majoria dels desenvolupadors de SQL Server que troben l'eficiència i els beneficis de seguretat que obtenen valen la inversió anticipada a temps.

Beneficis d'utilitzar procediments emmagatzemats

Per què un desenvolupador hauria d'utilitzar els procediments emmagatzemats?

Aquests són els avantatges clau d'aquesta tecnologia:

• Execució precompilada: SQL Server recopila cada procediment emmagatzemat una vegada i després reutilitza el pla d'execució. Això dóna lloc a un gran impuls de rendiment quan els procediments emmagatzemats es diuen repetidament.
• Reducció del trànsit del client / servidor: si l'ample de banda de la xarxa és una preocupació en el vostre entorn, us agradarà saber que els procediments emmagatzemats poden reduir les consultes SQL llargs a una única línia que es transmet a través del cable.
• Reutilització eficaç del codi i l'abstracció de programació: els procediments emmagatzemats poden ser utilitzats per diversos usuaris i programes de clients. Si els utilitza de forma planificada, trobareu que el cicle de desenvolupament porta menys temps.
• Controls de seguretat millorats: podeu concedir permís als usuaris per executar un procediment emmagatzemat independentment dels permisos de la taula subjacent.

Els procediments emmagatzemats són similars a les funcions definides per l'usuari, però hi ha diferències subtils.

Estructura

Els procediments emmagatzemats són similars a les construccions que es veuen en altres llenguatges de programació.

Accepten dades en forma de paràmetres d'entrada que s'especifiquen a l'execució. Aquests paràmetres d'entrada (si s'apliquen) s'utilitzen en l'execució d'una sèrie d'afirmacions que produeixen algun resultat. Aquest resultat es torna a l'entorn de trucada mitjançant l'ús d'un conjunt de registres, paràmetres de sortida i un codi de retorn.

Això pot sonar com un mos, però trobareu que els procediments emmagatzemats són bastant senzills.

Exemple

Fem un cop d'ull a un exemple pràctic relacionat amb la taula anomenada inventari que es mostra a la part inferior d'aquesta pàgina. Aquesta informació s'actualitza en temps real, i els gerents de magatzem comproven constantment els nivells de productes emmagatzemats al seu magatzem i disponibles per a l'enviament. En el passat, cada gestor executaria consultes similars a les següents:

SELECCIONAR producte, quantitat
FROM Inventory
WHERE Warehouse = 'FL'

Això va donar com a resultat un rendiment ineficient en SQL Server. Cada vegada que un gestor de magatzems va executar la consulta, el servidor de bases de dades es va veure obligat a recompilar la consulta i executar-la des de zero. També va exigir que l'administrador del magatzem tingués coneixement de SQL i els permisos adequats per accedir a la informació de la taula.

En canvi, el procés es pot simplificar mitjançant l'ús d'un procediment emmagatzemat. Aquest és el codi d'un procediment anomenat sp_GetInventory que recupera els nivells d'inventari d'un magatzem determinat.

CREA PROCEDIMENT sp_GetInventory
@location varchar (10)
AS
SELECCIONAR producte, quantitat
FROM Inventory
WHERE Warehouse = @ ubicació

El gestor de magatzems de Florida pot accedir als nivells d'inventari mitjançant l'emissió de la comanda:

EXECUTE sp_GetInventory 'FL'

El gestor de magatzems de Nova York pot utilitzar el mateix procediment emmagatzemat per accedir a l'inventari d'aquesta àrea:

EXECUTE sp_GetInventory 'NY'

Per descomptat, aquest és un exemple senzill, però els beneficis de l'abstracció es poden veure aquí. El gestor de magatzems no necessita comprendre SQL o el funcionament intern del procediment. Des del punt de vista del rendiment, el procediment emmagatzemat fa meravelles. SQL Server crea un pla d'execució una vegada i després el reutilitza connectant els paràmetres adequats en el moment de l'execució.

Ara que heu après els avantatges dels procediments emmagatzemats, sortiu i utilitzeu-los.

Proveu alguns exemples i mesureu les millores de rendiment aconseguides: us sorprendrà!

Taula d'inventari