Sequence

SEQUENCE

It is a counter in Oracle that increments each time they are used. To set up a sequence you can use the following keywords: INCREMENT BY, START WITH, MAXVALUE and CYCLE.

Este es un contador en Oracle que se incrementa cada vez que es usado. Para configurar una secuencia se pueden usar las siguientes palabras clave: INCREMENT BY, START WITH, MAXVALUE y CYCLE.

Tip

Sequences are used to create unique number for primary keys in a table. Oracle provides the SEQUENCE database object to generate values for the primary keys, while Microsoft SQL Server provides the IDENTITY command that can be used in any column of a table.

Las secuencias son usadas para crear números únicos para los valores de las llaves primarias de una tabla. Oracle ofrece el objeto de la base de datos SEQUENCE para generar valores de las llaves primarias, mientras que Microsoft SQL Server ofrece el comando IDENTITY el cual puede ser asignado a cualquier columna de una tabla.

Problem 1

kimberly > Modify kimberly.sql file to create the picture table and the seq_picture sequence as shown. Use Oracle.

Modifique el archivo kimberly.sql para crear la tabla picture y la secuencia sec_picture como se muestra. Use Oracle.

kimberly.sql

DROP TABLE picture;
DROP SEQUENCE seq_picture;
CREATE TABLE picture
(
     picture_id NUMBER(5, 0) PRIMARY KEY,
     name VARCHAR(15) NOT NULL
);

CREATE SEQUENCE seq_picture
     START WITH 1000
     MAXVALUE 9999;

INSERT INTO picture VALUES (seq_picture.nextval, 'Mona Lisa');
INSERT INTO picture VALUES (seq_picture.nextval, 'Luz de Cielo');
INSERT INTO picture VALUES (seq_picture.nextval, 'The Invitation');

MSDOS: cmd.exe

SQL> SELECT * FROM picture;

PICTURE_ID NAME
---------- ---------------
      1000 Mona Lisa
      1001 Luz de Cielo
      1002 The Invitation

Tip

A sequence can be used with the seq.currval command which returns the current value of the sequence or the seq.nextval commads which increments the value and returns the next value. The first call to seq.nextval returns the initial value of the sequence.

Una secuencia puede usarse con el comando seq.currval el cual regresa el valor actual de la secuencia o seq.nextval el cual incrementa y regresa el próximo valor. La primera llamada a seq.nextval regresa el valor inicial de la secuencia.

Problem 2

kimberly > Modify kimberly.sql file to create the picture table using IDENTITY as shown. Use Microsoft SQL Server.

Modifique el archivo kimberly.sql para crear la tabla picture usando IDENTITY como se muestra. Use Microsoft SQL Server.

kimberly.sql

CREATE TABLE [dbo].[picture]
(
[picture_id] INT NOT NULL IDENTITY CONSTRAINT [picture_pk] PRIMARY KEY,
[name] VARCHAR(15) NOT NULL
)
GO

INSERT INTO dbo.picture ([name]) VALUES ('Mona Lisa');
INSERT INTO dbo.picture ([name]) VALUES ('Luz de Cielo');
INSERT INTO dbo.picture ([name]) VALUES ('The Invitation');

Microsoft SQL Server

SELECT picture_id, name
FROM picture;

Tip

PRIMARY KEY implies that the values are unique, in other words it implies the UNIQUE command. Additionally, it is possible to create a reference to a column of the type PRIMARY KEY or UNIQUE. However, if IDENTITY is used with PRIMARY KEY, the value of key is automatically generated.

PRIMARY KEY implica que los valores son únicos en otras palabras implicar el comando UNIQUE. Adicionalmente, es posible crear referencia a columnas del tipo PRIMARY KEY o UNIQUE. Sin embargo, si se usa IDENTITY combinado con PRIMARY KEY, el valor de la llave se genera en forma automática.DEFINTION PRIMARY KEY VS UNIQUEA reference can be created to a PRIMARY KEY column or a UNIQUE column. However, it is possible to use PRIMARY KEY in one column only, while UNIQUE can be used in several columns of the same table.

Una referencia puede ser creada a una columna PRIMARY KEY o a una columna declarada como UNIQUE. Sin embargo, es posible usar PRIMARY KEY solamente en una columna, mientras que UNIQUE puede ser usado en varias columnas de la misma tabla.

SCOPE_IDENTITY()

It is a function in Microsoft SQL Server that indiques that a value in a IDENTITY column has been generated. This command (or the object SEQUENCE in Oracle) allows writing PL/SQL code of good quality when an insertion in several tables with the same key value is required.

Es una función de Microsoft SQL Server que indica que valor se ha generado en una columna del tipo IDENTITY. Este comando (o SEQUENCE en Oracle) permiten escribir código PL/SQL de calidad cuando se requiere una inserción simultánea en varias tablas con el mismo valor de la llave.

Tip

While IDENTITY automatically generates a unique value for a primary key, the SCOPE_IDENTITY() function returns the last value that was generates due to an INSERT in a table.

Mientras que IDENTITY genera automáticamente un valor único para una llave primaria, la función SCOPE_IDENTITY() regresa el último valor que se generó debido a un INSERT en la tabla.

Problem 3

hospital > Create and execute the hospital.sql file that illustrates how to use SCOPE_IDENTITY(). Use Microsoft SQL Server.

Cree y ejecute el archivo hospital.sql que ilustra el uso de SCOPE_IDENTITY(). Use Microsoft SQL Server.

Hint

If you insert in the patient_visit table without using the p_insert_patient_visit stored procedure, there is no way to know the value of visit_id because a database is multiuser and there can be many simultaneous insertions to the same table. Thus, the procedure inserts a new patient visit and returns the value of the visit_id that corresponds to the visit of the specified patient.

Si usted inserta en la tabla patient_visit sin usar el procedimiento almacenado p_insert_patient_visit, no existe forma de saber el valor de visit_id porque una base de datos es multiusuario y puede haber muchas inserciones simultaneas. Así, el procedimiento inserta la nueva visita del paciente y regresa el valor de visit_id que le corresponde a la visita del paciente indicado.

hostpital.sql

USE master;
GO

IF EXISTS(SELECT * FROM sysdatabases WHERE name='hospital')
BEGIN
     RAISERROR('Dropping existing hospital database...', 0, 1)
     DROP DATABASE hospital;
END
GO

CREATE DATABASE hospital;
GO

USE hospital;
GO

IF db_name() <>'hospital' BEGIN
     RAISERROR('Use database failed...', 22, 127) WITH LOG
     DROP DATABASE hospital;
END
GO

CREATE TABLE [dbo].[patient]
(
     [patient_id] INT NOT NULL IDENTITY PRIMARY KEY,
     [name] VARCHAR(32) NOT NULL UNIQUE,
     [age] INT NOT NULL
);
GO
INSERT INTO [patient] ([name], [age]) VALUES('Henry June', 20);
INSERT INTO [patient] ([name], [age]) VALUES('Mary Johnson', 54);
GO

CREATE TABLE [dbo].[patient_visit]
(
     [visit_id] INT NOT NULL IDENTITY PRIMARY KEY,
     [patient_id] INT NOT NULL
          REFERENCES [patient]([patient_id]) ON DELETE CASCADE,
     [visit_date] SMALLDATETIME NOT NULL DEFAULT GETDATE()
);
GO

CREATE PROCEDURE [p_insert_patient_visit]
     @in_patient_id INT,
     @out_patient_visit_id INT OUTPUT
AS
     INSERT INTO patient_visit(patient_id) VALUES(@in_patient_id);
     SET @out_patient_visit_id = SCOPE_IDENTITY();
     RETURN 0;
GO

Problem 4

hospital > Create and execute the hospital.sql file to generate the hospital database (of the previous problem) using Oracle and sequences. Observe that in Oracle SEQUENCES are used instead of INDENTITY and SCOPE_IDENTITY().

Cree y ejecute el archivo hospital.sql para generar la base de datos hospital (del problema anterior) usando Oracle y las secuencias. Observe que en Oracle las SECUENCIAS son usadas en lugar de IDENTITY and SCOPE_IDENTITY().

Problem 5

hospital > The p_insert_patient_visit stored procedure (from the previous problem) is executed using a programming language (such as C++, C# or Java) and returns the next generated ID, this ID is used to perform other operations in the program. Note that the visit_id column is declared using IDENTITY. Create an application using C++, C# or Java called Hospital that shows the visit_id that is generated when calling the p_insert_patient_visit stored procedure. If you are using Wintempla, drag from the toolbox the SQL: stored procedure template. Using: (a) the Oracle database, (b) the Microsoft SQL database.

El procedimiento almadenado p_insert_patient_visit (en el problema anterior) es ejecutado vía un lenguaje de programación (tal como C++, C# o Java) y regresa el valor del ID generado, el cual se utiliza para realizar otras operaciones en el programa. Observe que la columna visit_id está declarada como IDENTITY. Cree una aplicación en C++, C# o Java llamada Hospital que muestre el valor de visit_id generado al llamar el procedimiento p_insert_patient_visit. Si usted está usando Wintempla, arrastre desde la caja de herramientas (toolbox) la plantilla de SQL: stored procedure. Usando: (a) the Oracle database, (b) the Microsoft SQL database.

Microsoft SQL Server

SELECT visit_id,
patient_id,
visit_date
FROM patient_visit;

MSDOS: cmd.exe

SQL> connect hospital
Enter password:
Connected.
SQL> SELECT visit_id,
  2  patient_id,
  3  visit_date
  4  FROM patient_visit;

  VISIT_ID PATIENT_ID VISIT_DAT
---------- ---------- ---------
      1000       1000 14-DEC-12
      1001       1000 14-DEC-12

Tip

The SCOPE_IDENTITY() command is typically used with the SQL commands:

BEGIN TRANSACTION
ROLLBACK
COMMIT TRANSACTION

Típicamente el comando SCOPE_IDENTITY() se usa en combinación con los comandos de SQL:

BEGIN TRANSACTION
ROLLBACK
COMMIT TRANSACTION

Problem 6

Explain why SCOPE_IDENTITY() is typically used with

BEGIN TRANSACTION
ROLLBACK
COMMIT TRANSACTION

Explique porque SCOPE_IDENTITY() es típicamente usada con

BEGIN TRANSACTION
ROLLBACK
COMMIT TRANSACTION

Problem 7

Suppose it is necessary to insert in a table where the primary key is of the type IDENTITY, explain if it is the same to use SELECT MAX(visit_id) FROM patient_visit or the stored procedure previously implemented.

Suponga que se quiere insertar en una tabla en la cual la llave primaria es del tipo IDENTITY, explique si es lo mismo usar SELECT MAX(visit_id) FROM patient_visit o el procedimiento almacenado previamente creado.

Sequence

SEQUENCE

SCOPE_IDENTITY()

© Copyright 2000-2021 Wintempla selo. All Rights Reserved. Jul 22 2021. Home