Red de conocimiento informático - Conocimiento informático - Cómo utilizar procedimientos almacenados

Cómo utilizar procedimientos almacenados

Pregunta 1: ¿Cómo utilizar procedimientos almacenados en SQL? Procedimientos almacenados y aplicaciones de SQL

1. Introducción:

El procedimiento almacenado (procedimiento almacenado) es un conjunto. de Para completar la declaración SQL de una función específica, el conjunto se compila

y se almacena en la base de datos. El usuario especifica el nombre del procedimiento almacenado y proporciona los parámetros si el procedimiento almacenado tiene parámetros. , se ejecuta

It,

En las versiones en serie de SQL Server, los procedimientos almacenados se dividen en dos categorías: procedimientos almacenados proporcionados por el sistema y procedimientos almacenados definidos por el usuario

El SP del sistema se almacena principalmente en la base de datos maestra y tiene el prefijo sp_. Los procedimientos almacenados del sistema obtienen

información principalmente de las tablas del sistema, administrando así SQL Server para los administradores del sistema. Los procedimientos almacenados definidos por el usuario son creados por los usuarios y pueden completar una función específica, como consultar la información de datos requerida por el usuario.

Los procedimientos almacenados tienen las siguientes ventajas

1. Los procedimientos almacenados permiten la programación de componentes estándar (diseño modular)

Una vez creado un procedimiento almacenado, se puede utilizar en el programa se puede llamar varias veces en el procedimiento almacenado sin tener que reescribir la declaración SQL del procedimiento almacenado, y los profesionales de bases de datos pueden modificar el procedimiento almacenado en cualquier momento, pero el programa de aplicación El código fuente no tiene ningún impacto. Debido a que el código fuente de la aplicación

solo contiene las declaraciones de llamada del procedimiento almacenado, la portabilidad del programa ha mejorado enormemente.

2. Los procedimientos almacenados pueden alcanzar una velocidad de ejecución rápida

Si una operación contiene una gran cantidad de código Transaction-SQL o se ejecuta varias veces, entonces el procedimiento almacenado es más rápido que el La ejecución del procesamiento por lotes

es mucho más rápida porque el procedimiento almacenado está precompilado y el optimizador de consultas realiza un

análisis de filas optimizado

y el plan de ejecución finalmente se almacena en la tabla del sistema, y la instrucción Transaction-SQL por lotes se ejecuta cada vez que se ejecuta

se compila y optimiza, por lo que es relativamente más lento.

3. Los procedimientos almacenados pueden reducir el tráfico de red

Para la misma operación en el objeto de la base de datos, como la modificación de consultas, si esta operación involucra Transaction-SQL

La declaración se organiza en un procedimiento almacenado, luego, cuando se llama al procedimiento almacenado en la computadora cliente, solo la declaración de llamada se transmite a través de la red

De lo contrario

Hay varias declaraciones SQL , lo que aumenta considerablemente el tráfico de la red y reduce la carga de la red.

4. Los procedimientos almacenados se pueden utilizar plenamente como mecanismo de seguridad

El administrador del sistema puede limitar los permisos para ejecutar un determinado procedimiento almacenado, a fin de lograr el correspondiente

Restricciones

sobre los permisos de acceso a datos.

2. Variables

3. Declaraciones de control de proceso (if else | select case | while )

Seleccionar .. .CASE Ejemplo

DECLARE @iRet INT, @PKDisp VARCHAR(20)

SET @iRet = '1'

Seleccione @iRet =

CASO

CUANDO @PKDisp = '一' ENTONCES 1

CUANDO @PKDisp = '二' ENTONCES 2

CUANDO @PKDisp = '三' ENTONCES 3

CUANDO @PKDisp = 'cuatro' ENTONCES 4

CUANDO @PKDisp = 'cinco' ENTONCES 5

MÁS 100

FIN

4. Formato del procedimiento almacenado

Crear procedimiento almacenado

Crear Proc dbo

Parámetros del procedimiento almacenado

Ejecutar sentencia

RETURN

Ejecutar procedimiento almacenado

*** ************************************************** * ****/

-- Declaración de variables Al declarar variables en SQL, debe agregar el símbolo @ delante de la variable

DECLARE @I INT

-- Asignación de variables, al asignar variables se debe agregar set antes de la variable

SET @I = 30

-- Declarar múltiples variables

DECLARE @s varchar(10 ), @a INT

-- declaración if en Sql

condición IF BEGIN

declaración de ejecución

FIN

ELSE BEGIN

...gt;gt;

Pregunta 2: ¿Por qué utilizar procedimientos almacenados? Varios hermanos que fueron a IBM para una entrevista regresaron y se quejaron: fui a varios equipos de proyectos de IBM diferentes y casi todos los entrevistadores preguntaron sobre las bases de datos si habían utilizado procedimientos almacenados. Los programadores que conocimos no son los autores. Creo que los procedimientos almacenados son simplemente la fusión de un montón de SQL. Se agrega algo de control lógico en el medio. Sin embargo, los procedimientos almacenados son más prácticos cuando se trata de negocios más complejos. Por ejemplo, una operación de datos compleja. Si lo manejas en recepción. Puede implicar múltiples conexiones de bases de datos. Pero si usa procedimientos almacenados. Sólo una vez. Hay ventajas en términos de tiempo de respuesta. En otras palabras, los procedimientos almacenados pueden brindarnos el beneficio de una mayor eficiencia operativa. Además, los programas que son propensos a errores tienen una pequeña cantidad de datos, o los proyectos que no tienen nada que ver con el dinero pueden funcionar normalmente sin procedimientos almacenados. Los procedimientos almacenados de MySQL aún no se han probado. Si se trata de un proyecto formal, se recomienda utilizar sqlserver u procedimientos almacenados de Oracle. Si trabaja con datos, el proceso será mucho más rápido que el programa. El entrevistador preguntó si se utilizó almacenamiento. De hecho, quería saber si el programador que vino a la entrevista alguna vez había realizado un proyecto con una gran cantidad de datos. Si vienes de una formación, o de una pequeña empresa con proyectos pequeños, definitivamente tendrás menos exposición al almacenamiento.

Por lo tanto, si desea ingresar a una gran empresa, no podrá hacerlo sin una amplia experiencia en procedimientos almacenados. equivocado. Los procedimientos almacenados no solo son adecuados para proyectos grandes, sino también para proyectos pequeños y medianos, el uso de procedimientos almacenados es muy necesario. Su poder y ventajas se reflejan principalmente en: 1. Los procedimientos almacenados solo se compilan cuando se crean. No es necesario volver a compilar cada vez que se ejecuta el procedimiento almacenado en el futuro. Generalmente, las declaraciones SQL se compilan una vez cada vez que se ejecutan. , por lo que el uso de procedimientos almacenados puede mejorar la eficiencia de la velocidad de ejecución de la base de datos. 2. Al realizar operaciones complejas en la base de datos (como Actualizar, Insertar, Consultar, Eliminar en varias tablas), esta operación compleja puede encapsularse en un procedimiento almacenado y usarse junto con el procesamiento de transacciones proporcionado por la base de datos. Si estas operaciones se completan utilizando un programa, se convertirán en declaraciones SQL una tras otra, lo que puede requerir múltiples conexiones a la base de datos. Si cambia al almacenamiento, solo necesita conectarse a la base de datos una vez. 3. Los procedimientos almacenados se pueden reutilizar, lo que puede reducir la carga de trabajo de los desarrolladores de bases de datos. 4. Alta seguridad, puede configurar que solo un determinado usuario tenga derecho a utilizar el proceso almacenado especificado. Desventajas de los procedimientos almacenados 1: La depuración es problemática, ¡pero depurar con PL/SQL Developer es muy conveniente! compensar esta deficiencia. 2: Problema de trasplante. El código del lado de la base de datos, por supuesto, está relacionado con la base de datos. Pero si se trata de un proyecto de ingeniería, básicamente no hay problema de trasplante. 3: Problema de recompilación, debido a que el código back-end se compila antes de ejecutarse, si los objetos con relaciones de referencia cambian, los procedimientos almacenados y paquetes afectados deberán volver a compilarse (pero también se puede configurar para que se compile automáticamente en tiempo de ejecución). 4: Si se utiliza una gran cantidad de procedimientos almacenados en un sistema de programa, el aumento de las necesidades del usuario provocará cambios en la estructura de datos cuando el programa se entregue para su uso. Finalmente, habrá problemas relacionados con el sistema. El usuario quiere mantener el sistema, puede decir que es muy difícil y el costo no tiene precedentes.

¡Es más problemático de mantener!

Pregunta 3: ¿Cuál es la función de los procedimientos almacenados en Oracle y cómo usarlos en el código? No sé de dónde lo saqué. Cuando lo miro, creo que LZ es un principiante. ¿No puedes darme algunos ejemplos?

Por ejemplo, cree una tabla de prueba

cree una tabla de prueba (id int, nombre varchar2 (10), conteos int, inserte en los valores de prueba (1, 'Zhang San); ', 100) ; insertar en valores de prueba (2, '李思', 200);

Ahora aquí tienes una pregunta:

Verifica los recuentos totales. de todos

Crear procedimiento almacenado

crear o reemplazar p_test --Crear procedimiento almacenado, asv_counts int; --Definir variable comenzar --Iniciar seleccionar suma(counts) en v_counts de la prueba; --Pondrá los resultados obtenidos en la variable DBMS_OUTPUT.PUT_LINE(v_counts); --Imprimirá los resultados end;

Luego verificas el resultado

Luego creas una consulta con parámetros de entrada para ti

La pregunta es, ¿cuál es el nombre de la persona cuya identificación es? 1?

crear o reemplazar p_test1(v_id int)asv_name varchar2(10); comienza a seleccionar el nombre en v_name de la prueba donde id=v_id; p> Esto es lo que sucede cuando se ejecuta

Beginp_test1(1);

No escribí un comentario para ti sobre el segundo, deberías poder entenderlo. usted mismo

Para agregar, procedimientos almacenados No tiene que ser solo para ejecutar consultas, también puede realizar declaraciones SQL como eliminación o modificación. En términos generales, es una combinación de varias o N declaraciones SQL. para completar ciertas necesidades específicas en el sistema. Estas necesidades se pueden resolver con un sql o puede ser una combinación de múltiples SQL.

Pregunta 4: ¿Cómo crear y utilizar procedimientos almacenados SQL? de Sql Server es una declaración Transacation-Sql denominada *** almacenada en el servidor. Es una forma de encapsular el trabajo repetitivo y admite variables declaradas por el usuario, ejecución condicional y otras potentes funciones de programación. Los procedimientos almacenados tienen las siguientes ventajas sobre otros métodos de acceso a bases de datos: (1) Uso repetido. Los procedimientos almacenados se pueden reutilizar, lo que reduce la carga de trabajo de los desarrolladores de bases de datos. (2) Mejorar el rendimiento. Los procedimientos almacenados se compilan cuando se crean y no es necesario volver a compilarlos cuando se utilicen en el futuro. Las declaraciones SQL generales deben compilarse cada vez que se ejecutan, por lo que el uso de procedimientos almacenados mejora la eficiencia. (3) Reducir el tráfico de la red. El procedimiento almacenado se encuentra en el servidor. Al llamar, solo necesita pasar el nombre y los parámetros del procedimiento almacenado, lo que reduce la cantidad de datos transmitidos a través de la red. (4) Seguridad. Los procedimientos almacenados parametrizados pueden prevenir ataques de inyección SQL y se pueden aplicar permisos de concesión, denegación y revocación a los procedimientos almacenados. Los procedimientos almacenados se dividen en tres categorías: procedimientos almacenados definidos por el usuario, procedimientos almacenados extendidos y procedimientos almacenados del sistema.

Entre ellos, los procedimientos almacenados definidos por el usuario se dividen en dos tipos: Transaction-SQL y CLR. Los procedimientos almacenados de Transaction-SQL se refieren a declaraciones de Transaction-SQL guardadas *** que pueden aceptar y devolver parámetros proporcionados por el usuario. Los procedimientos almacenados CLR hacen referencia a métodos de tiempo de ejecución de lenguaje público (CLR) de .Net Framework que pueden aceptar y devolver parámetros proporcionados por el usuario. Se implementan como métodos estáticos públicos de clases en el ensamblado de .Net Framework. (Este artículo no lo presentará) La declaración para crear un procedimiento almacenado es la siguiente: Código

CREATE { PROC | PROCEDIMIENTO } [nombre_esquema.] nombre_procedimiento [ ]

[; { @parameter [ type_schema_name . ] data_type }

[ VARIANDO ] [ = predeterminado ] [ [ SALIDA [ PUT ]

] [ , n ]

[ CON [ , n ]

[ PARA REPLICACIÓN ]

COMO { [;][ n ] }

[;]

: :=

[ ENCRIPTACIÓN ]

[ REPILAR ]

[ EXECUTE_AS_Clause ]

::=

{ [ COMENZAR ] declaraciones [ FIN ] }

::=

NOMBRE EXTERNO nombre_ensamblaje.nombre_clase.nombre_método [nombre_esquema]: Representa el nombre del esquema al que pertenece el procedimiento almacenado. ejemplo: Crear esquema yangyang8848

Crear Proc yangyang8848.AllGoods

Como Seleccionar * De Master_Goods

Ir Ejecución: Exec AllGoods An se produjo un error. Ejecutar...gt;gt;

Pregunta 5: ¡Cómo utilizar procedimientos almacenados en la base de datos! ! Resolver... 10 puntos Palabra clave: procedimiento

Ejemplo:

SQLgt; crear [o reemplazar] procedimiento nombre_procedimiento es

comenzar

--Iniciar ejecución

insert into test('10001', 'Visket');

end

Lo anterior; La operación puede agregar un dato a la tabla de prueba

El comando utilizado para ejecutar el procedimiento almacenado es exec

Recuerde que en el procedimiento almacenado debe haber un punto y coma al final final de la declaración

Pregunta 6: ¿Se utilizan más o menos procedimientos almacenados? Cuando trabajamos en proyectos, a veces nos enfrentamos a una elección. ¿Deberíamos escribir más procedimientos almacenados o menos? Este tema se ha debatido durante mucho tiempo y, a menudo, se obtienen diferentes respuestas de diferentes empresas y diferentes líderes técnicos.

En proyectos reales, los métodos que finalmente adoptamos a menudo no son más que los siguientes tres métodos.

El primer método es exigir que todas las operaciones de la base de datos no utilicen ningún procedimiento almacenado. Todas las operaciones se completan utilizando declaraciones SQL estándar. Incluso si una acción requiere la finalización de operaciones de base de datos de varios pasos, no hay procedimientos almacenados. se utilizan. , pero para usar transacciones en el código del programa para completar; el segundo método es requerir que todas las operaciones de la base de datos estén encapsuladas con procedimientos almacenados, incluso la operación de inserción más simple. No hay una sola línea de declaraciones SQL visibles en el código del programa. Si se utiliza la división del trabajo y la cooperación, es posible que los programadores ni siquiera comprendan la sintaxis SQL. El tercer método es que las operaciones de bases de datos relativamente simples generalmente se completan utilizando declaraciones SQL estándar, y alguna lógica empresarial relativamente compleja se completa utilizando procedimientos almacenados.

Por supuesto, si el sistema usa un marco como hibernate o nhibernate y no necesita escribir declaraciones SQL, creo que aún debería pertenecer al tercer método, porque el marco de hibernación nos permite usar el marco apropiado. A veces, deje de lado su marco y escriba usted mismo procedimientos almacenados y declaraciones SQL para completar las operaciones de la base de datos. De hecho, estos tres métodos tienen sus propias ventajas y desventajas.

El primer método es utilizar declaraciones SQL estándar para completar todas las operaciones de la base de datos. La eficiencia de ejecución del programa definitivamente no es tan buena como los dos últimos métodos. Si el sistema es un ERP a gran escala, este es el caso. método El método es absolutamente indeseable. Porque una vez que se completa básicamente el desarrollo, si el sistema necesita o quiere optimizarse, será algo muy problemático para los desarrolladores, porque el enfoque de la optimización se centra básicamente en las operaciones de la base de datos y en lo que los desarrolladores pueden hacer. Lo que debemos hacer es verificar cada declaración SQL para ver si se puede optimizar aún más. En particular, algunas declaraciones de consulta relativamente complejas son el foco de nuestra inspección. La visualización de paginación es un ejemplo típico de procedimientos almacenados que mejoran la eficiencia del programa. Si utilizamos procedimientos almacenados para realizar operaciones de paginación, utilizamos procedimientos almacenados para extraer los conjuntos de registros que necesitamos del sistema y la eficiencia de la paginación mejora enormemente. Por otro lado, si no utilizamos procedimientos almacenados para las operaciones de paginación, usamos declaraciones SQL para leer todos los conjuntos de registros en la memoria y luego obtener los registros que necesitamos de la memoria ***, por lo que la eficiencia de la paginación naturalmente se reducirá. Por supuesto, también podemos usar declaraciones SQL para obtener los registros que necesitamos, no todos los registros, pero eso es mucho más problemático y está más allá del alcance de nuestra discusión.

Este método también tiene otra desventaja. Un sistema o un proyecto siempre tendrá una lógica de negocios compleja y fácilmente modificable. Si se encapsula esta lógica de negocios compleja, en los procedimientos almacenados, los cambios en la lógica de negocios solo implican cambios en. procedimientos almacenados y no están relacionados con el código del programa, por lo que sería una lástima no utilizar procedimientos almacenados.

Aunque este método tiene desventajas, una vez que se adopta, si trasplantamos la base de datos del proyecto, los desarrolladores sentirán lo grandes y sabios que fueron los tomadores de decisiones en ese momento. Y sabemos que las versiones anteriores de Access y MySQL no proporcionaban soporte para procedimientos almacenados, y todos los proyectos pequeños y medianos a menudo no tienen más remedio que tomar esta decisión. Existe la ventaja de no utilizar procedimientos almacenados. Es mucho más conveniente depurar código sin procedimientos almacenados. Por lo tanto, en muchos proyectos, se utilizan declaraciones SQL estándar sin utilizar ningún procedimiento almacenado. Esta es la razón directa por la que la mayoría de los programadores utilizan SQL estándar en lugar de procedimientos almacenados. Para decirlo sin rodeos, es demasiado problemático.

El segundo método es encapsular todas las operaciones de la base de datos mediante procedimientos almacenados. Si se adopta este método, la eficiencia de ejecución del programa será relativamente alta, especialmente cuando se enfrenta a alguna lógica empresarial compleja, no solo la hay. una mejora significativa en la eficiencia, y cuando la lógica empresarial cambia, nuestros desarrolladores a menudo no necesitan modificar el código del programa al realizar las modificaciones correspondientes. Simplemente modificar los procedimientos almacenados puede satisfacer los cambios del sistema.

Otra ventaja es que después de desarrollar un sistema, si encontramos que un modo o lenguaje es difícil de satisfacer las necesidades en algunos aspectos, podemos usar rápidamente otros dos lenguajes, lo cual sería muy conveniente. tiempo. Por ejemplo, en 2002, una empresa de la Academia de Ciencias de China utilizó ASP para desarrollar...gt;gt;

Pregunta 7: ¿Cuál es la sintaxis general de los procedimientos almacenados en SQL? Sintaxis del procedimiento almacenado del servidor SQL

Un procedimiento almacenado es uno o más comandos SQL almacenados en la base de datos como objetos ejecutables.

Las definiciones son siempre abstractas. Un procedimiento almacenado es en realidad un conjunto de declaraciones SQL que pueden completar ciertas operaciones, pero este conjunto de declaraciones se coloca en la base de datos (aquí solo hablamos de SQL Server). Si creamos procedimientos almacenados y llamamos a procedimientos almacenados en ASP, podemos evitar mezclar declaraciones SQL con código ASP. Hay al menos tres beneficios al hacer esto:

Primero, mejora enormemente la eficiencia. La velocidad de ejecución del procedimiento almacenado en sí es muy rápida y llamar al procedimiento almacenado puede reducir en gran medida la cantidad de interacciones con la base de datos.

En segundo lugar, mejorar la seguridad. Si mezcla declaraciones SQL en código ASP, una vez que el código se ve comprometido, también significa que la estructura de la biblioteca está comprometida.

En tercer lugar, favorece la reutilización de declaraciones SQL.

En ASP, los procedimientos almacenados generalmente se llaman a través del objeto mand. Dependiendo de la situación, este artículo también presenta otros métodos de llamada. Para facilitar la explicación, se realiza la siguiente clasificación simple basada en la entrada y salida del procedimiento almacenado:

1. Un procedimiento almacenado que solo devuelve un único conjunto de registros

Supongamos que existe el siguiente procedimiento almacenado (el propósito de este artículo no es describir la sintaxis T-SQL, por lo que el procedimiento almacenado solo proporciona el código sin explicación):

/*SP1*/

CREAR PROCEDIMIENTO dbo.getUserList

como

establecer nocount en

comenzar

seleccionar * de dbo.[userinfo ]

end

El procedimiento almacenado anterior obtiene todos los registros en la tabla de información de usuario y devuelve un conjunto de registros. El código ASP para llamar al procedimiento almacenado a través del objeto mand es el siguiente:

'**Llamar al procedimiento almacenado a través del objeto mand**

DIM Mym, MyRst

Set Mym = Server.CreateObject(ADODB.mand)

Mym.ActiveConnection = MyConStr 'MyConStr es la cadena de conexión de la base de datos

Mym.mandText = getUserList 'Especifica el archivo almacenado nombre del procedimiento

Mym.mandType = 4 'Indica que se trata de un procedimiento almacenado

Mym.Prepared = true 'Requiere que los comandos SQL se compilen primero

Establecer MyRst = Mym.Execute

Set Mym = Nothing

Almacene el conjunto de registros obtenido por el proceso y asígnelo a MyRst. A continuación, puede operar en MyRst.

En el código anterior, el atributo mandType indica el tipo de solicitud. El valor y la descripción son los siguientes:

-1 indica que el tipo del parámetro mandText no se puede determinar. /p>

1 indica que mandText es un tipo de comando general

2 indica que el parámetro mandText es el nombre de una tabla existente

4 indica que el parámetro mandText es el nombre de un procedimiento almacenado

También puede pasar El objeto Connection o el objeto Recordset llama al procedimiento almacenado. Los métodos son los siguientes:

'**Llame al procedimiento almacenado a través del objeto Connection. **

DIM MyConn, MyRst

Set MyConn = Server.CreateObject(amp; qu...gt; gt;

Pregunta 8: Un ejemplo sencillo de cómo utilizar los procedimientos almacenados de Oracle Hola, anfitrión

-- -Crear tabla

crear tabla TESTTABLE

(

id1 VARCHAR2( 12),

nombre VARCHAR2(32)

)

seleccione t.id1, t.name de TESTTABLE t