Cursor

CURrent Set Of RecordS allow you to process one row at a time on a SELECT statement. Cursors are used, when there is no other easy way to get the desired operation.

CURrent Set Of RecordS permiten procesar de renglón en renglón los registros devueltos por un comando SELECT. Los cursores se usan cuando no existe otra forma de conseguir la operación deseada.

Tip

It is not necessary to declare a cursor before using it. You may want to name all your cursors using the cur_ prefix to make your code more readable. Note that a cursor is a variable that represents a SELECT query.

No es necesario declarar un cursor antes de usarlo. Se recomienda asignar nombres a los cursores que inicien con el prefijo cur_. Observe que un cursor es una variable que representa una consulta SELECT.

Problem 1

city_bank > Test the following stored procedure that uses a cursor in Oracle.

Pruebe el siguiente procedimiento almacenado que usa un cursor en Oracle.

MSDOS: cmd.exe

SQL> connect city_bank
Enter password:
Connected.
SQL> SET SERVEROUTPUT ON;
SQL> EXECUTE p_recList;
Sigal, Tobias
Colmena, Karen
Weiss, Matthew
Vega, Adam
Volman, Shanta
Mourgos, Kevin
Nayer, Julia
The value of _count is 7

PL/SQL procedure successfully completed.

Tip

Observe that cur_client is a cursor, while rec_client is a record. The difference between a cursor and a record is that a cursor represents all returned data from a SELECT command, while a record represents only the data of one row (tuple). Additionally, a record is built from a cursor or a SELECT command.

Observe que cur_client es un cursor, mientras que rec_client es un registro o record. La diferencia entre un cursor y un record, es que el cursor representa todo el conjunto de datos que regresa un comando SELECT, mientras que el record representa solamente los datos de un renglón (tuple). Adicionalmente, un record se construye a partir de un cursor o de una comando SELECT.

FETCH

The command FETCH is used in Microsoft SQL Server to move the cursor to the next position and retrieve a data row.

El comando FETCH es usado en Microsoft SQL Server para mover el cursor a la posición siguiente y retraer un renglón de datos.

Problem 2

city_bank > Test the following stored procedure that uses a cursor in Microsoft SQL Server.

Pruebe el siguiente procedimiento almacenado que usa un cursor en Microsoft SQL Server.

Microsoft SQL Server

CREATE PROCEDURE p_reclist
AS
DECLARE cur_client CURSOR READ_ONLY FOR SELECT client_id, name FROM client;
DECLARE @p_count AS INT;
DECLARE @p_client_id AS INT;
DECLARE @p_name AS NVARCHAR(15);
BEGIN
     SET @p_count = 0;
     OPEN cur_client
     FETCH NEXT FROM cur_client INTO @p_client_id, @p_name
     WHILE (@@FETCH_STATUS=0)
     BEGIN
          SET @p_count = @p_count + 1;
          PRINT(CAST(@p_client_id AS VARCHAR) + ' ' + @p_name);
          FETCH NEXT FROM cur_client INTO @p_client_id, @p_name
     END
     CLOSE cur_client;
     DEALLOCATE cur_client;
     PRINT('The value of p_count is ' + CAST(@p_count AS VARCHAR));
END
GO

Microsoft SQL Server

USE city_bank;
EXECUTE p_reclist;

Output

2345   Sigal, Tobias
2346   Vega, Adam
2347   Nayer, Julia
7654   Colmena, Karen
7655   Volman, Shanta
8764   Weiss, Matthew
8765   Mourgos, Kevin
The value of p_count is 7

@@FETCH_STATUS

Microsoft SQL Server provides several PL variables that start with @@. These variables provide information about the execution of the commands in the procedure. The @@FETCH_STATUS variable is zero when data was successfully retrieved from the database.

Microsoft SQL Server permite monitorear la ejecución de un procedimiento por medio de las variables PL las cuales inician con @@. La variable @@FETCH_STATUS tienen un valor de cero siempre que los datos fueron exitosamente retraídos de la base de datos.

Tip

Microsoft SQL Server does not provide the RECORD command, thus, when using a cursor, it is necessary to create all the variables of the RECORD using the DECLARE command.

Microsoft SQL Server no soporta el comando RECORD, por lo que para usar el comando CURSOR es necesario crear variables de procedimiento con el comando DECLARE para cada columna que se desea consultar.

Problem 3

circuit_city > Write a procedure using cursor to display prices from the item table. The procedure must display 10% more of the regular price for those products that are more expensive than 100 dollars. For the rest of the products the procedure should display 1% more of the regular price. Using: (a) Oracle, (b) Microsoft SQL Server.

Escriba un procedimiento usando cursores para mostrar los precios de los artículos. El procedimiento debe mostrar 10 % más del precio regular de aquellos artículos que cuestan más de 100 dolares. Para el resto de los productos el procedimiento muestra 1% más del precio regular. Usando (a) Oracle, (b) Microsoft SQL Server.

MSDOS: cmd.exe

SQL> connect circuit_city
Enter password:
Connected.
SQL> SET SERVEROUTPUT ON;
SQL> EXECUTE p_increment;
Monitor X8: 200.5 -> 220.55
Monitor X2: 399.5 -> 439.45
Monitor S3: 454.5 -> 499.95
Hard Drive 15G: 80.99 -> 81.8
Hard Drive 80G: 280 -> 308
Printer U9: 550.5 -> 605.55
CDROM: 39.5 -> 39.9
CD Burner: 99.78 -> 100.78
RAM 255M: 54.5 -> 55.05
RAM 512M: 80.99 -> 81.8
Keyboard: 20 -> 20.2
Mouse: 15.68 -> 15.84

PL/SQL procedure successfully completed.

Microsoft SQL Server

USE circuit_city;
EXECUTE p_increment;

Output

Monitor X8 : 200.50 -> 220.55
Monitor X2 : 399.50 -> 439.45
Monitor S3 : 454.50 -> 499.95
Hard Drive 15G : 80.99 -> 81.80
Hard Drive 80G : 280.00 -> 308.00
Printer U9 : 550.50 -> 605.55
CDROM : 39.50 -> 39.90
CD Burner : 99.78 -> 100.78
RAM 255M : 54.50 -> 55.05
RAM 512M : 80.99 -> 81.80
Keyboard : 20.00 -> 20.20
Mouse : 15.68 -> 15.84
The value of p_count is 12

Problem 4

circuit_city > The manager of Circuit City has decided to give away a monitor to those clients who place an order that contains more than 15 items. If the order has more than 5 items, the client wins a mouse. Write a stored procedure to control the client rewards. Using: (a) Oracle, (b) Microsoft SQL Server.

El gerente de Circuit City ha decidido regalar un monitor a aquellos de sus clientes que hagan una compra con más de 15 artículos, y regalar un ratón para aquellos clientes con órdenes con más de 5 artículos. Escriba un procedimiento almacenado para lograrlo. Usando (a) Oracle, (b) Microsoft SQL Server.

MSDOS: cmd.exe

SQL> connect circuit_city
Enter password:
Connected.
SQL> SET SERVEROUTPUT ON;
SQL> EXECUTE p_reward;
2000 wins Mouse -> 9
2001 wins Mouse -> 6
2002 wins Monitor -> 23
2003 wins nothing -> 5
2004 wins nothing -> 4
2005 wins nothing -> 2

PL/SQL procedure successfully completed.

Microsoft SQL Server

USE circuit_city;
EXECUTE p_reward;

Output

2000 wins Mouse -> 9
2001 wins Mouse -> 6
2002 wins Monitor -> 23
2003 wins nothing -> 5
2004 wins nothing -> 4
2005 wins nothing -> 2

Problem 5

circuit_city > Create the p_rewcli stored procedure to display the name of the clients that have placed the orders with theirs rewards. Your procedure must not call user defined functions. Using: (a) Oracle, (b) Microsoft SQL Server.

Cree el procedimiento almacenado p_rewcli para mostrar el nombre del cliente que hizo la compra con sus regalos. Su procedimiento almacenado no debe llamar funciones definidas por el usuario. Usando (a) Oracle, (b) Microsoft SQL Server.

MSDOS: cmd.exe

SQL> EXECUTE p_rewclie;
Smith, Tony ->2000 wins Mouse -> 9
Frunch, Ken ->2001 wins Mouse -> 6
DiTore, Mike ->2002 wins Monitor -> 23
Smith, Julie ->2003 wins nothing -> 5
Frunch, Ken ->2004 wins nothing -> 4
Smith, Julie ->2005 wins nothing -> 2

PL/SQL procedure successfully completed.

Microsoft SQL Server

USE circuit_city;
EXECUTE p_rewclie;

Output

Smith, Tony -> 2000 wins Mouse -> 9
Frunch, Ken -> 2001 wins Mouse -> 6
DiTore, Mike -> 2002 wins Monitor -> 23
Smith, Julie -> 2003 wins nothing -> 5
Frunch, Ken -> 2004 wins nothing -> 4
Smith, Julie -> 2005 wins nothing -> 2

Problem 6

circuit_city > Create the rewards function that takes an order_id and returns the rewards. If the order has more than 15 items the function returns 'monitor'. If the order has more than 5 items, it returns 'mouse'. Otherwise, it returns 'nothing'. Using: (a) Oracle, (b) Microsoft SQL Server.

Escriba una función llamada rewards. La función toma como entrada el orden_id. Si la orden contiene más de 15 artículos, la función regresa 'monitor'. Si la orden tiene más de 5 artículos regresa 'mouse'. De otra forma la función regresa 'nothing'. Usando (a) Oracle, (b) Microsoft SQL Server.

MSDOS: cmd.exe

SQL> SELECT order_id, rewards(order_id) AS rewards
FROM orderx;

  ORDER_ID  REWARDS
----------  -----

      2000  Mouse
      2001  Mouse
      2002  Monitor
      2003  nothing
      2004  nothing
      2005  nothing

Microsoft SQL Server

SELECT order_id,
dbo.rewards(order_id) AS rewards
FROM orderx;

Problem 7

circuit_city > Write a SQL query to display the clients and their rewards using the rewards function. Using: (a) Oracle, (b) Microsoft SQL Server.

Escriba una consulta en SQL para mostrar los clientes con los regalos usando la función rewards. Usando (a) Oracle, (b) Microsoft SQL Server.

MSDOS: cmd.exe

  ORDER_ID NAME GIFT
---------- -------------------- --------------
      2000 Smith, Tony          Mouse
      2003 Smith, Julie         nothing
      2005 Smith, Julie         nothing
      2002 DiTore, Mike         Monitor
      2004 Frunch, Ken          nothing
      2001 Frunch, Ken          Mouse

Problem 8

circuit_city > Create the p_reward2 stored procedure. p_reward2 is equal to the p_reward stored procedure, but it calls internally the rewards function. Do not display the number of items in the order. Using: (a) Oracle, (b) Microsoft SQL Server.

Crear el procedimiento almacenado p_reward2. p_reward2 es igual al procedimiento almacenado p_reward previamente creado, pero llama internamente a la función rewards. No muestre el número de artículos en la orden. Usando (a) Oracle, (b) Microsoft SQL Server.

MSDOS: cmd.exe

SQL> SET SERVEROUTPUT ON;

SQL> EXECUTE p_reward2;
2000 wins Mouse
2001 wins Mouse
2002 wins Monitor
2003 wins nothing
2004 wins nothing
2005 wins nothing

PL/SQL procedure successfully completed.

Microsoft SQL Server

USE circuit_city;
EXECUTE p_reward;

Output

2000 wins Mouse -> 9
2001 wins Mouse -> 6
2002 wins Monitor -> 23
2003 wins nothing -> 5
2004 wins nothing -> 4
2005 wins nothing -> 2

Tip

The main differences when using Microsoft SQL Server cursors:

Microsoft does not supports records, therefore, one variable must be declared for each column in the query of the cursor
The cursor must be open and close
The FETCH command must be used twice
Cursor memory must be released using DEALLOCATE CURSOR
WHILE must be used instead of FOR

Las principales diferencias en los cursores en Microsoft SQL Server son:

No so soporta los records y por lo tanto, se deben declarar una variable por cada columna de la consulta del cursor
El cursor debe abrirse y cerrarse
Se usa el comando FETCH dos veces
Se debe liberar la memoria del cursor usando DEALLOCATE CURSOR
Se debe usar WHILE en lugar de FOR

Cursor

Cursor

FETCH

@@FETCH_STATUS

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