sql - tabla - ¿Qué es un procedimiento almacenado?

procedimientos almacenados sql ejemplos

Question

¿Qué es un procedimiento almacenado? ¿Cómo trabajan? ¿Cuál es la composición de un procedimiento almacenado (cada cosa debe ser un procedimiento almacenado)?

Answer 1

"Qué es un procedimiento almacenado" ya se ha respondido en otras publicaciones aquí. Lo que publicaré es una forma menos conocida de utilizar el procedimiento almacenado. Es grouping stored procedures o numbering stored procedures .

Referencia de sintaxis

; number ; number según this

Un entero opcional que se utiliza para agrupar procedimientos del mismo nombre. Estos procedimientos agrupados se pueden eliminar usando una declaración DROP PROCEDURE

Ejemplo

CREATE Procedure FirstTest 
(
     @InputA INT
)
AS 
BEGIN

SELECT ''A'' + CONVERT(VARCHAR(10),@InputA)

END
GO

CREATE Procedure FirstTest;2
(
     @InputA INT,
     @InputB INT
)
AS 
BEGIN

SELECT ''A'' + CONVERT(VARCHAR(10),@InputA)+ CONVERT(VARCHAR(10),@InputB)

END
GO

Utilizar

exec FirstTest 10
exec FirstTest;2 20,30

Resultado

Otro intento

CREATE Procedure SecondTest;2
(
     @InputA INT,
     @InputB INT
)
AS 
BEGIN

SELECT ''A'' + CONVERT(VARCHAR(10),@InputA)+ CONVERT(VARCHAR(10),@InputB)

END
GO

Resultado

Msg 2730, Nivel 11, Estado 1, Prueba secundaria del procedimiento, Línea 1 [Línea de inicio por lotes 3] No se puede crear el ''Prueba secundaria'' con un número de grupo 2 porque actualmente no existe un procedimiento con el mismo nombre y número de grupo 1 la base de datos. Debe ejecutar CREAR PROCEDIMIENTO ''SecondTest''; 1 primero.

Referencias :

1. this
2. Procedimientos almacenados numerados en SQL Server - techie-friendly.blogspot.com
3. Agrupación de procedimientos almacenados - sqlmag

PRECAUCIÓN

1. Después de agrupar los procedimientos, no puedes soltarlos individualmente.
2. Esta característica se puede eliminar en una versión futura de Microsoft SQL Server.

Answer 2

El procedimiento almacenado le ayudará a hacer código en el servidor. Puede pasar parámetros y encontrar resultados.

create procedure_name (para1 int,para2 decimal)
as
select * from TableName

Answer 3

En general, un procedimiento almacenado es una "función SQL". Ellos tienen:

-- a name
CREATE PROCEDURE spGetPerson
-- parameters
CREATE PROCEDURE spGetPerson(@PersonID int)
-- a body
CREATE PROCEDURE spGetPerson(@PersonID int)
AS
SELECT FirstName, LastName ....
FROM People
WHERE PersonID = @PersonID

Este es un ejemplo enfocado en T-SQL. Los procedimientos almacenados pueden ejecutar la mayoría de las declaraciones SQL, devolver valores escalares y basados ​​en tablas, y se consideran más seguros porque evitan los ataques de inyección SQL.

Answer 4

En un DBMS, un procedimiento almacenado es un conjunto de sentencias de SQL con un nombre asignado que se almacena en la base de datos en forma compilada para que pueda ser compartido por varios programas.

El uso de un procedimiento almacenado puede ser útil en

1. Proporcionar un acceso controlado a los datos (los usuarios finales solo pueden ingresar o cambiar datos, pero no pueden escribir procedimientos)

2. Asegurar la integridad de los datos (los datos se introducirían de manera consistente) y

3. Mejora la productividad (las declaraciones de un procedimiento almacenado deben escribirse solo una vez)

Answer 5

Los procedimientos almacenados en SQL Server pueden aceptar parámetros de entrada y devolver múltiples valores de parámetros de salida; en SQL Server, las instrucciones del programa de procedimientos almacenados para realizar operaciones en la base de datos y devolver un valor de estado a un procedimiento de llamada o lote.

Los beneficios de usar procedimientos almacenados en SQL Server

Permiten la programación modular. Permiten una ejecución más rápida. Pueden reducir el tráfico de red. Se pueden utilizar como mecanismo de seguridad.

Este es un ejemplo de un procedimiento almacenado que toma un parámetro, ejecuta una consulta y devuelve un resultado. Específicamente, el procedimiento almacenado acepta BusinessEntityID como parámetro y lo usa para coincidir con la clave principal de la tabla HumanResources.Employee para devolver al empleado solicitado.

> create procedure HumanResources.uspFindEmployee    `*<<<---Store procedure name`*
@businessEntityID                                     `<<<----parameter`
as
begin
SET NOCOUNT ON;
Select businessEntityId,              <<<----select statement to return one employee row
NationalIdNumber,
LoginID,
JobTitle,
HireData,
From HumanResources.Employee
where businessEntityId =@businessEntityId     <<<---parameter used as criteria
end

Aprendí esto de essential.com ... es muy útil.

Answer 6

Los procedimientos almacenados son un lote de sentencias de SQL que se pueden ejecutar de varias maneras. La mayoría de los DBM principales admiten procedimientos almacenados; Sin embargo, no todos lo hacen. Deberá verificar con su documentación de ayuda de DBMS en particular para obtener información específica. Como estoy más familiarizado con SQL Server, lo usaré como muestra.

Para crear un procedimiento almacenado, la sintaxis es bastante simple:

CREATE PROCEDURE <owner>.<procedure name>

     <Param> <datatype>

AS

     <Body>

Así por ejemplo:

CREATE PROCEDURE Users_GetUserInfo

    @login nvarchar(30)=null

AS

    SELECT * from [Users]
    WHERE ISNULL(@login,login)=login

Un beneficio de los procedimientos almacenados es que puede centralizar la lógica de acceso a los datos en un solo lugar que luego es fácil de optimizar para los DBA. Los procedimientos almacenados también tienen un beneficio de seguridad, ya que puede otorgar derechos de ejecución a un procedimiento almacenado, pero el usuario no necesitará tener permisos de lectura / escritura en las tablas subyacentes. Este es un buen primer paso contra la inyección de SQL.

Los procedimientos almacenados vienen con desventajas, básicamente el mantenimiento asociado con su operación básica de CRUD . Digamos que para cada tabla tiene un Insertar, Actualizar, Eliminar y al menos una selección basada en la clave principal, eso significa que cada tabla tendrá 4 procedimientos. Ahora tome una base de datos de tamaño decente de 400 tablas, ¡y tiene 1600 procedimientos! Y eso es asumiendo que no tienes duplicados, lo que probablemente tendrás.

Aquí es donde usar un ORM o algún otro método para generar automáticamente sus operaciones CRUD básicas tiene un montón de mérito.

Answer 7

Piensa en una situación como esta,

• Tienes una base de datos con datos.
• Hay una serie de aplicaciones diferentes necesarias para acceder a esa base de datos central, y en el futuro también algunas aplicaciones nuevas.
• Si va a insertar las consultas de la base de datos en línea para acceder a la base de datos central, dentro del código de cada aplicación individualmente, entonces probablemente tenga que duplicar la misma consulta una y otra vez dentro del código de las diferentes aplicaciones.
• En ese tipo de situación, puede utilizar procedimientos almacenados (SP). Con los procedimientos almacenados, está escribiendo un número de consultas comunes (procedimientos) y las almacena con la base de datos central.
• Ahora la duplicación del trabajo nunca se realizará como antes y el acceso a los datos y el mantenimiento se realizarán de manera centralizada.

NOTA:

• En la situación anterior, puede preguntarse "¿Por qué no podemos introducir un servidor central de acceso a datos para interactuar con todas las aplicaciones? Sí. Esa será una alternativa posible. Pero,
• La principal ventaja de los SP con respecto a ese enfoque es que, a diferencia de su código de acceso a datos con consultas en línea, los SP son declaraciones precompiladas, por lo que se ejecutarán más rápido. Y los costos de comunicación (a través de redes) serán mínimos.
• Opuesto a eso, los SP agregarán más carga al servidor de base de datos. Si eso fuera una preocupación según la situación, un servidor de acceso a datos centralizado con consultas en línea será una mejor opción.

Answer 8

Un procedimiento almacenado es un conjunto de sentencias de SQL precompiladas que se utilizan para realizar una tarea especial.

Ejemplo: si tengo una tabla de Employee

Employee ID  Name       Age  Mobile
---------------------------------------
001          Sidheswar  25   9938885469
002          Pritish    32   9178542436

Primero estoy recuperando la tabla de Employee :

Create Procedure Employee details
As
Begin
    Select * from Employee
End

Para ejecutar el procedimiento en SQL Server:

Execute   Employee details

--- (Employee details is a user defined name, give a name as you want)

Luego, segundo, estoy insertando el valor en la tabla de empleados

Create Procedure employee_insert
    (@EmployeeID int, @Name Varchar(30), @Age int, @Mobile int)
As
Begin
    Insert Into Employee
    Values (@EmployeeID, @Name, @Age, @Mobile)
End

Para ejecutar el procedimiento parametrizado en SQL Server:

Execute employee_insert 003,’xyz’,27,1234567890

  --(Parameter size must be same as declared column size)

Ejemplo: @Name Varchar(30)

En la tabla Employee , el tamaño de la columna Name debe ser varchar(30) .

Answer 9

Un procedimiento almacenado es un grupo de sentencias de SQL que se ha creado y almacenado en la base de datos. Un procedimiento almacenado aceptará parámetros de entrada para que un solo procedimiento pueda ser utilizado en la red por varios clientes utilizando diferentes datos de entrada. Un procedimiento almacenado reducirá el tráfico de red y aumentará el rendimiento. Si modificamos un procedimiento almacenado, todos los clientes obtendrán el procedimiento almacenado actualizado.

Ejemplo de creación de un procedimiento almacenado.

CREATE PROCEDURE test_display
AS
    SELECT FirstName, LastName
    FROM tb_test;

EXEC test_display;

Ventajas de utilizar procedimientos almacenados.

• Un procedimiento almacenado permite la programación modular.

  Puede crear el procedimiento una vez, almacenarlo en la base de datos y llamarlo cualquier cantidad de veces en su programa.

• Un procedimiento almacenado permite una ejecución más rápida.

  Si la operación requiere una gran cantidad de código SQL que se realiza repetitivamente, los procedimientos almacenados pueden ser más rápidos. Se analizan y optimizan cuando se ejecutan por primera vez, y una versión compilada del procedimiento almacenado permanece en un caché de memoria para su uso posterior. Esto significa que el procedimiento almacenado no necesita ser analizado y reoptimizado con cada uso, lo que resulta en tiempos de ejecución mucho más rápidos.

• Un procedimiento almacenado puede reducir el tráfico de red.

  Una operación que requiere cientos de líneas de código Transact-SQL se puede realizar a través de una sola declaración que ejecuta el código en un procedimiento, en lugar de enviar cientos de líneas de código a través de la red.

• Los procedimientos almacenados proporcionan una mejor seguridad a sus datos

  Se puede otorgar a los usuarios permiso para ejecutar un procedimiento almacenado incluso si no tienen permiso para ejecutar las declaraciones del procedimiento directamente.

  En SQL Server tenemos diferentes tipos de procedimientos almacenados:

  • Procedimientos almacenados del sistema
  • Procedimientos almacenados definidos por el usuario
  • Procedimientos almacenados extendidos

• Los procedimientos almacenados en el sistema se almacenan en la base de datos maestra y comienzan con un prefijo sp_ . Estos procedimientos se pueden usar para realizar una variedad de tareas para admitir las funciones de SQL Server para llamadas a aplicaciones externas en las tablas del sistema

  Ejemplo: sp_helptext [StoredProcedure_Name]

• Los procedimientos almacenados definidos por el usuario generalmente se almacenan en una base de datos de usuarios y generalmente están diseñados para completar las tareas en la base de datos de usuarios. Si bien la codificación de estos procedimientos no usa el prefijo sp_ porque si usamos primero el prefijo sp_ , comprobará la base de datos maestra y luego se tratará de la base de datos definida por el usuario.

• Los procedimientos almacenados extendidos son los procedimientos que llaman a funciones desde archivos DLL. Hoy en día, los procedimientos almacenados extendidos están en desuso por la razón de que sería mejor evitar el uso de procedimientos almacenados extendidos.

Answer 10

Un procedimiento almacenado es una colección con nombre de sentencias SQL y lógica de procedimiento, es decir, compilada, verificada y almacenada en la base de datos del servidor. Un procedimiento almacenado generalmente se trata como otros objetos de la base de datos y se controla a través del mecanismo de seguridad del servidor.

Answer 11

Un procedimiento almacenado no es más que un grupo de sentencias de SQL compiladas en un solo plan de ejecución.

1. Crea una vez y llámala n número de veces
2. Reduce el tráfico de red.

Ejemplo: crear un procedimiento almacenado

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE GetEmployee
      @EmployeeID int = 0
AS
BEGIN
      SET NOCOUNT ON;

      SELECT FirstName, LastName, BirthDate, City, Country
      FROM Employees 
      WHERE EmployeeID = @EmployeeID
END
GO

Alterar o modificar un procedimiento almacenado:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE GetEmployee
      @EmployeeID int = 0
AS
BEGIN
    SET NOCOUNT ON;

    SELECT FirstName, LastName, BirthDate, City, Country
    FROM Employees 
    WHERE EmployeeID = @EmployeeID
END
GO

Suelte o elimine un procedimiento almacenado:

DROP PROCEDURE GetEmployee

Answer 12

Un procedimiento almacenado se utiliza para recuperar datos, modificar datos y eliminar datos en la tabla de base de datos. No es necesario que escriba un comando SQL completo cada vez que desee insertar, actualizar o eliminar datos en una base de datos SQL.

Answer 13

Un procedimiento almacenado se utiliza principalmente para realizar ciertas tareas en una base de datos. Por ejemplo

• Obtener los conjuntos de resultados de la base de datos de alguna lógica de negocios en los datos.
• Ejecutar múltiples operaciones de base de datos en una sola llamada.
• Se utiliza para migrar datos de una tabla a otra tabla.
• Puede ser llamado para otros lenguajes de programación, como Java.

Answer 14

para simple,

Los procedimientos almacenados son programas almacenados , un programa / función almacenados en la base de datos.

Cada programa almacenado contiene un cuerpo que consiste en una declaración SQL. Esta declaración puede ser una declaración compuesta por varias declaraciones separadas por caracteres de punto y coma (;).

CREATE PROCEDURE dorepeat(p1 INT)
BEGIN
  SET @x = 0;
  REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
END;

Answer 15

• Un procedimiento almacenado es un conjunto precompilado de una o más sentencias de SQL que realizan alguna tarea específica.

• Un procedimiento almacenado debe ejecutarse de forma independiente utilizando EXEC

• Un procedimiento almacenado puede devolver múltiples parámetros

• Se puede utilizar un procedimiento almacenado para implementar transacciones.