PROCEDIMIENTOS ALMACENADOS

PRECEDIMIENTOS ALMACENADOS


INTRODUCCIÒN

Un Procedimiento Almacenado es un programa auto controlado escrito en lenguaje del DBMS, son almacenados como parte de la Base de Datos y sus metadatos.

Una vez creado un procedimiento almacenado, se puede invocar directamente desde una aplicación, o sustituir el nombre de una tabla o vista, por el nombre de procedimiento en cláusulas SELECT. Los procedimientos almacenados pueden recibir parámetros de entrada y retornar valores a la aplicación.
Las ventajas de usar los procedimientos almacenados incluyen:
· Diseño modular.
· Aplicaciones que acceden la misma Base de Datos pueden compartir los procedimientos almacenados, eliminando el código doble y reduciendo el tamaño de las aplicaciones.
· El fácil mantenimiento.
· Cuando un procedimiento se actualiza, los cambios se reflejan automáticamente en todas las aplicaciones, sin la necesidad de recompilar y relinkear. Las aplicaciones son compiladas sólo una vez para cada cliente.
· Los procedimientos almacenados son ejecutados por el servidor, no por el cliente lo que reduce el tráfico en la red y mejora el performance o desempeño, especialmente para el acceso del cliente remoto.
Puede crear procedimientos almacenados mediante la instrucción CREATE PROCEDURE de Transact-SQL.
Antes de crearlos, tenga en cuenta lo siguiente:
Las instrucciones CREATE PROCEDURE no se pueden combinar con otras instrucciones SQL en el mismo lote.
Para crear procedimientos, debe disponer del permiso CREATE PROCEDURE en la base de datos y del permiso ALTER en el esquema donde se crea el procedimiento. En procedimientos almacenados CLR, debe ser propietario del ensamblado al que se hace referencia en o disponer del permiso REFERENCES en dicho ensamblado.
Los procedimientos almacenados son objetos de ámbito de esquema y sus nombres deben ajustarse a las reglas para los identificadores.
Sólo puede crear un procedimiento almacenado en la base de datos actual.
Cuando cree un procedimiento almacenado, deberá especificar lo siguiente:
Todos los parámetros de entrada y de salida del lote o del procedimiento que realiza la llamada.
Las instrucciones de programación que realicen operaciones en la base de datos, incluidas las llamadas a otros procedimientos.
El valor de estado devuelto al lote o al procedimiento que realiza la llamada, a fin de indicar que la operación se ha realizado correctamente o que se ha producido un error (y el motivo del mismo).
Las instrucciones de control de errores necesarias para detectar y administrar posibles errores. Microsoft SQL Server 2005 presenta nuevas funciones de control de errores como ERROR_LINE y ERROR_PROCEDURE que se pueden especificar en el procedimiento almacenado.
SEGURIDAD
Otra capacidad importante de los procedimientos almacenados es que mejoran la seguridad a través de la encriptación y el aislamiento. Los usuarios de las bases de datos pueden tener permisos de ejecutar un procedimiento almacenado sin tenerlos para acceder directamente a los objetos de la bases de datos sobre las que opera el procedimiento almacenado. Además un procedimiento almacenado puede ser encriptado cuando se lo crea o modifica inhabilitando a los usuarios a leer los comandos Transact-SQL contenidos en el procedimiento almacenado. Esta capacidad de seguridad permite aislar la estructura de la base de datos del usuario de la base de datos, con la consiguiente ganancia en seguridad.
CATEGORÍAS DE PROCEDIMIENTOS ALMACENADOS
Existen cinco categorías de procedimientos almacenados: procedimientos almacenados del sistema, procedimientos almacenados locales, procedimientos almacenados temporarios, procedimientos almacenados extendidos y procedimientos almacenados remotos.


PROCEDIMIENTOS ALMACENADOS DEL SISTEMA
Los procedimientos almacenados del sistema son guardados en la base de datos Master y son típicamente identificados por el prefijo sp_ . Ellos realizan una amplia variedad de tareas para soportar las funciones del SQL Server soportando: llamadas de aplicaciones externas para datos de las tablas del sistema, procedimientos generales para administración de las bases de datos, y funciones de administración de seguridad. Por ejemplo, se pueden ver los privilegios de una tabla usando el procedimiento almacenado de catálogo sp_table_privileges. El comando siguiente utiliza este procedimiento almacenado para mostrar los privilegios de la tabla stores en la base de datos Pubs:
USE PubsGOEXECUTE sp_table_privileges Stores
PROCEDIMIENTOS ALMACENADOS LOCALES
Los procedimientos almacenados locales son usualmente almacenados en una base de datos y están típicamente diseñados para completar tareas en la base de datos donde residen. Un procedimiento almacenado local se podría crear también para personalizar código de los procedimientos almacenados del sistema.
PROCEDIMIENTOS ALMACENADOS TEMPORARIOS
Un procedimiento almacenado temporario es similar a un procedimiento almacenado local, pero existe sólo hasta que se cierre la conexión que lo creó o se dé de baja el SQL Server, dependiendo del tipo de procedimiento almacenado. Estos procedimientos tienen una existencia volátil debido a que son creados son almacenados en la base de datos TempDB. TempDB se recrea cuando se reinicia el servidor; por lo tanto, todos los objetos dentro de la base de datos desaparecen después de la base de datos.
Hay tres tipos de procedimiento almacenado temporarios: locales (también llamados privados), globales, y procedimientos almacenados en TempDB. Un procedimiento almacenado temporario local siempre comienza con #, un procedimiento almacenado temporario global siempre comienza con ##.
Cualquier conexión para la base de datos puede ejecutar un procedimiento almacenado temporario global. Este tipo de procedimientos debe tener un nombre único, dado todas las conexiones pueden ejecutarlo y, cómo todos los procedimientos almacenados temporarios, este es creado en TempDB. El permiso para ejecutar procedimientos almacenados es automáticamente garantizado al rol público y no puede ser cambiado. Un procedimiento almacenado temporario global es casi tan volátil como los locales. Este tipo de procedimiento es removido cuando la conexión usada para crear el procedimiento se cierra y cualquier conexión que este ejecutando el procedimiento almacenado es completada.
Los procedimientos almacenados temporarios creados directamente en la TempDB son diferentes a los procedimientos almacenados locales y globales en lo siguiente:
· Se pueden configurar permisos para ellos.
· Existen aún después que la conexión que los creó se terminan
· No son removidos hasta que el SQL Server no sea apagado.
Ya que este tipo de procedimiento almacenado se crea directamente en la TempDB, es importante calificar completamente los objetos de base de datos referenciados por comandos Transact-SQL en el código.
PROCEDIMIENTOS ALMACENADOS EXTENDIDOS
Un procedimiento almacenado extendido usa un programa externo, compilado como una DLL, librería de vínculos dinámicos (dynamic link library) para expandir las capacidades de un procedimiento almacenado. Un número de procedimiento almacenado del sistema es también calificado como procedimientos almacenados extendidos. La mayoría de los procedimientos almacenados extendidos usan el prefijo xp_ como una convención de nombre. Sin embargo, hay algunos procedimientos almacenados extendidos que comienzan con el prefijo sp_, y hay algunos procedimientos almacenados del sistema que no son procedimientos extendidos y usan el prefijo xp_. Por lo tanto, no se puede depender sobre convención de nombres para identificar procedimientos almacenados del sistema y procedimientos almacenados extendidos.
Se puede usar la función OBJECTPROPERTY para determinar si un procedimiento almacenado es extendido o no. OBJECTPROPERTY retorna un valor de 1 si es un procedimiento extendido (IsExtendedProc), indicando un procedimiento almacenado extendido, o retorna un valor de 0, indicando que no es un procedimiento extendido. El siguiente ejemplo demuestra que el sp_prepare es un procedimiento almacenado extendido y que xp_logininfo no es un procedimiento almacenado extendido:
USE Master
· un procedimiento almacenado extendido que usa el prefijo sp_ SELECT OBJECTPROPERTY(object_id(‘sp_prepare’), ‘IsExtendedProc’)
Este ejemplo retorna un valor de 1
USE Master
· un procedimiento almacenado que no es extendido y usa el prefijo xp_ SELECT OBJECTPROPERTY(object_id(‘xp_logininfo’), ‘IsExtendedProc’)
Este ejemplo retorna un valor de 0.
COMO SE GUARDA UN PROCEDIMIENTO
Cuando se crea un procedimiento, SQL Server chequea la sintaxis de los comandos Transact-SQL que incluye. Si la sintaxis es incorrecta, SQL Server generará un mensaje de error “sintax incorrect” (sintaxis incorrecta), y el procedimiento no será creado. Si el procedimiento pasa el chequeo de sintaxis, el procedimiento se guarda, escribiéndose su nombre y otras informaciones (tal como un número autogenerado de identificación) en la tabla SysObject. El texto usado para crear el procedimiento se escribe en la tabla SysComments de la base de datos actual.
El siguiente comando SELECT consulta a la tabla SysObjects en la base de datos Pubs para mostrar el número de identificación del procedimiento almacenado ByRoyalty:
SELECT [name].[id] FROM [pubs].[dbo].[SysObjects]
WHERE [name] = ‘byroyalty’
Esta consulta retorna lo siguiente:
name
Id
byroyalty

581577110





Erika Martínez Martínez.




PROCEDIMIENTOS ALMACENADOS EN SQLSERVER.

En SQL si se pueden crear los procedimientos almacenados se pueden crear de una forma parecida a las vistas solo que en estos siempre debe de llevar la palabra GO antes de poner la sentencia de CREATE PROCEDURE y para ejecutarlos es con el comando EXECUTE y no con SELECT y para borrarlos se usa el mismo comando que utilizamos para borrar es DROP y también se utiliza el comando ALTER para modificar los procedimientos almacenados.
Ejemplos:
Para crear el procedimiento almacenado:
USE Pubs
GO
CREATE PROCEDURE proc_pubs
AS
SELECT au_fname FROM authors
Para ver el resultado del procedimiento almacenado:
EXECUTE proc_pubs
Para eliminar el procedimiento almacenado:
GO
DROP PROCEDURE proc_pubs

Para cambiar el procedimiento almacenado:
ALTER PROCEDURE proc_pubs


Rosa Maria Aguado Reyes.



PROCEDIMIENTOS ALMACENADOS MYSQL
Un procedimiento almacenado es un conjunto de comandos SQL que pueden almacenarse en el servidor.
Los procedimientos almacenados son soportados solo en MySql versión 5.0 o superior.
Para poder crear procedimientos almacenados se debe contar con la tabla de permisos, esta se crea durante la instalación de MySql, si no se cuanta con esta se tendrá que actualizar MySql.
Sintaxis para crear un procedimiento almacenado en MySql:

Create procedure () select from ;

Ejemplo: usando la tabla lineas crear un procedimiento.

Create procedure lin () select * from líneas;

Sintaxis para llamar al procedimiento almacenado que hemos creado:

Call ();

Ejemplo: para llamar un procedimiento.

Call lin();

Sintaxis para eliminar el procedimiento que hemos creado:

Drop procedure ;

Ejemplo: para borrar un proceso almacenado.

Drop procedure lin;



Maria de Monserrat Samano Samano.



PROCEDIMIENTOS ALMACENADOS FILEMAKER

En el gestor FileMaker Pro 9.0 no se pueden crear procedimientos almacenados ya que no cuenta con ninguna opción donde se puedan crear manualmente y tampoco acepta el código que se utiliza en SQL para crearlos:

use pubs
go
create procedure proa_pubs
as select au_fame from authors

Al seleccionar la ayuda de FileMaker Pro tampoco muestra información sobre como crearlos. Y al buscar en Internet muestra información donde dice que las vistas y los procedimientos en este gestor están en proceso.



Diana Laura Moctezuma Rodríguez.



PROCEDIMIENTOS ALMACENADOS EN SQLITE

En el gestor de base de datos SQLite no se pueden crear, modificar y borrar procedimientos almacenados.

Si buscas en Internet muestra texto que dice que si se pueden crear, pero no muestra ningún ejemplo de cómo hacer un procedimiento almacenado, supongo que depende de la versión del gestor. En el gestor que nosotros hemos estado utilizando no se puede realizar.

Si buscas en la ayuda del gestor sobre como crear procedimientos almacenados lo que muestra es como crear objetos y no procedimientos almacenados.



Mariela Zavala Hurtado.



PROCEDIMIENTOS ALMACENADOS EN POSTGRESQL

Un procedimiento almacenado en Postgresql funciona como una función para crearlo utilizamos la siguiente sintaxis.
CREATE FUNCTION Nombre (int) RETURNS SETOF Table AS $$ SELECT * FROM Tabla WHERE campo = $valor;$$ LANGUAGE SQL;
Realizar la consulta SELECT * FROM Nombre (1) AS t1; Borrar la función (procedimiento)

drop function nombre de la function(int);

EJEMPLO

CREATE FUNCTION proc2(int) RETURNS SETOF agents AS $$
SELECT * FROM agents WHERE agent_id = $1;
$$ LANGUAGE SQL;

Realizar la consulta

SELECT * FROM proc2 (1) AS t1;

Borrar una función procedimiento almacenado

drop function proc2(int);


CONCLUSIONES

El gestor Postgresql es similar al SQLServer pero en el caso de los procedimientos almacenados Postgresql lo maneja como una función.




Patricia Ramírez Ramírez.



PROCEDIMIENTOS ALMACENADOS EN DB2

Los procedimientos almacenados en DB2 se crean de forma compleja, es necesario contar con el centro de desarrollo para desarrollarlos.

En la práctica solo se pudo crear un procedimiento vacío, con la siguiente sentencia:

CREATE PROCEDURE CLIENT1 LANGUAGE SQL
P1:
BEGIN
ENDP1

EJEMPLO:

El siguiente código se tomó como base para crear procedimientos almacenados. La base de datos tomada como referencia es Bra_Bank:
CREATE PROCEDURE sqlCliente ( IN dpt varchar(4) ) SPECIFIC sqlCliente RESULT SETS 1 LANGUAGE SQL-------------------------------------------------------------------------- Procedimiento almacenado de SQL sqlsamp------------------------------------------------------------------------P1: BEGIN -- Declarar cursor DECLARE cursor1 CURSOR WITH RETURN FOR SELECT * FROM CUSTOMERS -- Cursor dejado abierto para aplicación cliente OPEN cursor1; END P1 CONCLUSION

En el ejercicio, solo pude crear un procedimiento almacenado vacío, se consultaron varias fuentes de información y ninguna fue de utilidad, para los procedimientos almacenados necesitan del centro de desarrollo para su creación.



María de la Luz Guijosa Orduña.

PROCEDIMIENTOS ALMACENADOS ORACLE
Un procedimiento almacenado es un conjunto de instrucciones en PL/SQL, que pueden ser llamados usando el nombre que se le haya asignado.
La sintaxis para crear un procedimiento es la siguiente:
CREATE [OR REPLACE] PROCEDURE name [(param [INOUTIN OUT] datatype) . . .][ISAS] pl/sql_subprogram
El uso de OR REPLACE permite sobreescribir un procedimiento existente. Si se omite, y el procedimiento ya existe, se producirá un error. Los modificadores IN, OUT, IN OUT indican si el parametro es de entrada, salida o ambos.

A continuación se presenta un ejemplo de creación de un procedimiento en Oracle:
SQL> CREATE PROCEDURE credit (acc_no IN NUMBER, amount IN NUMBER)1> AS BEGIN2> UPDATE accounts3> SET balance = balance + amount4> WHERE account_id = acc_no;5> END;
EJEMPLO
1.- Creacion de la tabla alumnos
create table ALUMNOS(
Numero_De_Control integer not null,
Nombre varchar (30) not null,
Cal_Primer_Parcial integer not null,
Cal_Segundo_Parcial integer not null,
Cal_Tercer_Parcial integer not null,
Promedio integer not null,
primary key (Numero_De_Control));


Insertamos registros a la tabla ALUMNOS

insert INTO ALUMNOS values('05120077', 'Salvador','90','90','90','90')
insert INTO ALUMNOS values('05120078', 'Angeles','90','100','90','93.3')
insert INTO ALUMNOS values('05120079', 'Paty','80','100','90','90')
insert INTO ALUMNOS values('05120080', 'Ival','85','95','100','95')
insert INTO ALUMNOS values('05120081', 'Jose','90','100','95','94.3')
insert INTO ALUMNOS values('05120082', 'Monse','90','100','90','93.3')
insert INTO ALUMNOS values('05120082', 'Monica','95','100','90','93.3')


select * from ALUMNOS


2.- Procedimiento almacenado de la tabla alumnos en Oracle.
CREATE PROCEDURE AlumnopruebA(emp_id IN NUMBER)
AS
-- declare variables to hold values from table columns, use %TYPE attribute
emp_comm alumnos.nombre%TYPE;

BEGIN -- executable part starts here
-- select the column values into the local variables
SELECT Nombre INTO emp_comm FROM alumnos WHERE Numero_De_Control =emp_id;

DBMS_OUTPUT.PUT_LINE(emp_comm );

END AlumnopruebA;
/



3.-Mostrar el procedimiento almacenado de la tabla alumnos en oracle.

BEGIN
AlumnopruebA(05120079);
END;
/



4.- Borrar procedimiento almacenado de la tabla alumnos en oracle.

DROP PROCEDURE AlumnopruebA;



Conclusión

El procedimiento almacenado nos sirve para que la consulta que se guarde pueda ser llamada con el nombre que se le fue asignado también observe que el procedimiento en el gestor Oracle es muy diferente al que se utiliza en el SQL, algunas de las sentencias que cambian son el BEGIN Y EL END y también como hemos venido utilizando el gestor Oracle al final de cada consulta se sierra con punto y coma.


José Manuel Sámano Camargo.