Ejemplo de procedimiento almacenado

Procedimientos almacenados de base de datos

La esencia de los procedimientos almacenados de base de datos es un conjunto de códigos de definición y SQL implementados en el lado de la base de datos. Escriba previamente trabajos de uso común o muy complejos en declaraciones SQL y guárdelos con un nombre específico, y luego llame al servicio proporcionado por la base de datos con la misma función que el procedimiento almacenado definido, simplemente llame a ejecución

Usando el lenguaje SQL, puede escribir un procedimiento almacenado que acceda a la base de datos usando la siguiente sintaxis:

CREATE PROC[EDURE] nombre_procedimiento [;número]

[

{@parameter data_type} ][VARIAN] [= predeterminado] [SALIDA]

]

[, ..n]

[CON

{

RECOMPILAR

| CIFRADO

| RECOMPILAR, CIFRADO

}

]

[PARA REPLICACIÓN]

AS

sql_statement [...n]

El contenido en [ ] es opcional. en ( ) es obligatorio

Ejemplo: Si el usuario desea crear un procedimiento almacenado select_delete para eliminar registros en la tabla tmp, se puede escribir como:

Crear Proc select_del As<. /p>

Eliminar tmp

Ejemplo:

Si el usuario desea crear un procedimiento almacenado select_delete para eliminar registros en la tabla tmp, se puede escribir como un procedimiento almacenado, El usuario quiere consultar los datos de un determinado año en la tabla tmp

Crear proc select_query @year int as

select * from tmp donde año=@año

donde @ año es un parámetro del procedimiento almacenado

Ejemplo: el propósito del procedimiento almacenado es encontrar el nodo principal superior entre n nodos. El procedimiento almacenado puede asumir este procedimiento almacenado de uso frecuente y reutilizarlo en una página web para disfrutarlo.

Vacío: indica que el nodo es un nodo de nivel superior

fjdid (número de nodo principal)

El nodo n no está vacío: indica el nodo principal del nodo número

dwmc (nombre de la unidad) CREATE?proc?search_dwmc@dwidoldint,@dwmcresult?varchar(100)?outputasdeclare?@stopintdeclare?@result?varchar(80)declare?@dwmc?varchar(80) declarar?@dwidintset?nocountonset ?@stop= 1set?@dwmc=""select?@dwmc?=?dwmc,?@dwid?=?convert(int,fjdid)?from?jtdw?where?id?=?@ dwidoldset?@resultado?= ?rtrim(@dwmc)if?@dwid?=?0set?@stop?=?0 while(@stop?=?1)y(@dwid<>0)beginset?@dwidold?=? @dwidselect?@dwmc? =?dwmc,?@dwid?=?convert(int,fjdid)?from?jtdw?where?id?=?@dwidoldif?@@rowcount?=?0set?@dwmc?=?" " elseset?@result= ?@dwmc?+?@resultif(@dwid?=?0)?o?(@@rowcount?=?0)set?@stop?=?0elsecontinueendset?@dwmcresult?=?rtrim( @result)use execpro- nombre[pram1pram2.....] Los procedimientos almacenados en SQL Server

Las sentencias SQL se compilan y ejecutan. Los procedimientos almacenados son declaraciones SQL compiladas. Se puede utilizar directamente al usarlo.

En el Analizador de consultas de SQL Server, ingrese el siguiente código:

declare @tot_amt int

ejecute order_tot_amt 1,@tot_amt salida

select @tot_amt

El código anterior está ejecutando order_tot_amt 1, salida @tot_amt

select @tot_amt

El código ejecuta el procedimiento almacenado order_tot_amt y calcula el. número de pedido Para las ventas de 1 pedido, definimos @tot_amt como parámetro de salida para obtener el resultado que queremos.

Procedimientos almacenados en Oracle

1. Crear procedimientos almacenados

Al igual que otros sistemas de bases de datos, los procedimientos almacenados de Oracle son programas escritos en PL/SQL. Ciertas funciones de procesamiento pueden. realizarse y almacenarse en un diccionario de base de datos.

Sintaxis:

crear [o reemplazar] procedimiento nombre_procedimiento

[ (argumento [ { entrada| entrada salida } ] tipo,

argumento [ { in | out | in out } ] tipo

{ es |

(Nota: no utilizar declaración de declaración)

Inicio

Excepción

Fin;

1.1 IN aquí significa pasar parámetros al procedimiento almacenado, OUT significa devolver parámetros del procedimiento almacenado y IN OUT significa pasar parámetros y devolver parámetros;

1.2 Procedimientos almacenados. Los parámetros en solo pueden especificar el tipo de parámetro, pero no la longitud;

1.3 Declarar el nombre de la variable, el tipo de variable y la longitud que se usarán después de AS o IS;

1.4 No use AS o IS Agregue una declaración de declaración después de IS para declarar variables

2. Procedimientos almacenados

Después de crear un procedimiento almacenado, se puede usar en SQLPLUS, herramientas de desarrollo de Oracle o terceros. Herramientas de desarrollo de terceros siempre que esté autorizado. Llamar a procedimientos almacenados

Oracle utiliza la instrucción CALL para implementar llamadas a procedimientos almacenados

Sintaxis:

llamar. procedimiento_nombre(parámetro1, parámetro2...);

p>

3. Proceso de desarrollo

Las herramientas de escritura de procedimientos almacenados proporcionadas por varios de los principales proveedores de bases de datos en la actualidad no están estandarizadas, aunque sus estilos de escritura tienen ciertas similitudes para escribir almacenamiento PL/SQL. Los pasos para procedimientos, funciones, paquetes y activadores son los siguientes:

3.1 Editar el código fuente de los procedimientos almacenados Cuando se utiliza software de edición y procesamiento de texto. Para editar el código fuente de los procedimientos almacenados, debe guardar el código fuente en formato de texto para poder utilizar algo como el software de procesamiento de textos WORD para editar.

3.2 Interpretación del programa de procedimiento almacenado Interprete el programa de procedimiento almacenado en. SQLPLUS o una herramienta de depuración;

Utilice comandos de Oracle como iniciar o en SQL> explicación de inicio. Por ejemplo:

SQL>start c:\stat1.sql

Si utiliza la herramienta de depuración, puede editar directamente y hacer clic en el botón correspondiente para generar el procedimiento almacenado.

3.3 Depurar el código fuente hasta que sea correcto No podemos garantizar que los procedimientos almacenados La escritura será correcta la primera vez. Por tanto, la depuración es una de las cosas que todo programador debe hacer. En la depuración descendente de SQLPLUS, los principales métodos utilizados son:

1. Utilice el comando SHOW ERROR para solicitar la ubicación del error del código fuente

2. ver cada almacenamiento La ubicación incorrecta del proceso.

3.4 Autorizar a usuarios o roles relevantes para ejecutar procedimientos almacenados. Si el procedimiento almacenado correcto no está autorizado para ser depurado, solo el propio constructor puede ejecutar el procedimiento almacenado. Por lo tanto, los procedimientos almacenados que forman parte de la aplicación también deben estar autorizados para cumplir con los requisitos. En SQLPLUS, puede utilizar el comando GRANT para autorizar la ejecución de procedimientos almacenados.

Sintaxis:

CONCEDE system_privilege | rol AL usuario | PÚBLICO

[CON OPCIÓN DE ADMINISTRADOR]

o

CONCEDER privilegio_objeto | TODA la columna EN esquema.objeto

PARA usuario | PÚBLICO CON OPCIÓN DE CONCESIÓN

Donde

privilegio_sistema

role: nombre del rol

usuario: nombre de usuario autorizado

object_privilege: el nombre del permiso concedido, que puede ser

ALTER

BORRAR

EJECUTAR

ÍNDICE

INSERTAR

REFERENCIAS

SELECCIONAR

ACTUALIZAR

Columna: nombre de la columna

Modo: nombre del esquema

Objeto: nombre del objeto

4. Diccionario de datos

USER_SOURCE Diccionario de código fuente para funciones y procedimientos almacenados por el usuario

DBA_SOURCE Diccionario de código fuente para todos los usuarios de todo el sistema. SOURCE Diccionario de procedimientos almacenados y códigos fuente de funciones para todos los usuarios de todo el sistema

ALL_SOURCE Diccionario de procedimientos almacenados y códigos fuente de funciones que puede utilizar el usuario actual (incluida su autorización de usuario)

USER_ERRORS Procedimientos almacenados por el usuario y diccionario de mensajes de error en el código fuente de la función

Tabla temporal

(aplicable a SQL2000/2005)

Puede crear archivos temporales locales tablas y tablas temporales globales. Las tablas temporales locales son visibles solo en la sesión actual; las tablas temporales globales son visibles en todas las sesiones.

Las tablas temporales locales tienen nombres precedidos por un carácter numérico (#table_name), mientras que las tablas temporales globales tienen nombres precedidos por dos caracteres numéricos (##table_name).

La instrucción SQL hace referencia a la tabla temporal utilizando el nombre especificado para nombre_tabla en la instrucción CREATE TABLE:

CREATE TABLE #MyTempTable (cola INT PRIMARY KEY)

INSERT INTO #MyTempTable VALUES (1)

Si las tablas temporales locales se crean mediante procedimientos almacenados o aplicaciones ejecutadas por varios usuarios simultáneamente, SQL Server debe poder distinguir entre tablas creadas por diferentes usuarios. Para hacer esto, SQL Server agrega internamente un sufijo numérico al nombre de la tabla de cada tabla temporal local. El nombre completo de una tabla temporal almacenada en la tabla sysobjects de la base de datos tempdb consta del nombre de la tabla especificado en la instrucción CREATE TABLE y un sufijo numérico generado por el sistema. Para permitir que se agreguen sufijos, el nombre de la tabla nombre_tabla especificado para una tabla temporal local no debe exceder los 116 caracteres.

A menos que la tabla temporal se elimine explícitamente mediante la instrucción DROP TABLE, el sistema la eliminará automáticamente cuando salga de su alcance:

Tablas temporales locales creadas en el archivo almacenado El procedimiento se eliminará automáticamente cuando se complete el procedimiento almacenado. Todos los procedimientos almacenados anidados ejecutados por el procedimiento almacenado que creó la tabla pueden hacer referencia a la tabla. Sin embargo, un proceso que llama al procedimiento almacenado que creó la tabla no puede hacer referencia a la tabla.

Todas las demás tablas temporales locales se eliminarán automáticamente al final de la sesión actual.

Las tablas temporales globales se eliminan automáticamente cuando finaliza la sesión que las creó y otras tareas dejan de hacer referencia a las tablas. Las asociaciones entre tareas y tablas se mantienen solo durante la vida útil de una única instrucción Transact-SQL.

En otras palabras, cuando finaliza la sesión que creó la tabla temporal global, la tabla se elimina automáticamente después de que se completa la última instrucción Transact-SQL que hizo referencia a la tabla.