Cursor in ORACLE

×

Error message

  • Deprecated function: implode(): Passing glue string after array is deprecated. Swap the parameters in drupal_get_feeds() (line 394 of /home4/ccollins/public_html/ccollins/includes/common.inc).
  • Deprecated function: The each() function is deprecated. This message will be suppressed on further calls in menu_set_active_trail() (line 2405 of /home4/ccollins/public_html/ccollins/includes/menu.inc).

Let’s say you need to update all the orders from batch_number = '37250006-11'

Working with a test table


DROP TABLE FULFILL.TESTCURSOR CASCADE CONSTRAINTS;

CREATE TABLE FULFILL.TESTCURSOR
(
ORDER_NUMBER INTEGER,
INSERT_DATE DATE DEFAULT sysdate,
VALUE INTEGER
)
TABLESPACE USERS
RESULT_CACHE (MODE DEFAULT)
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

So you write a query like this.


SELECT ORDER_NUMBER
FROM fulfill.ORDERS
where batch_number = '37250006-11'

Now you need to update each order and set the value to 1.

You would run it through a loop .

BEGIN
for r1 IN (
SELECT ORDER_NUMBER
FROM fulfill.ORDERS where batch_number = '37250006-11'
)
LOOP
update testcursor
set value = 1
where ORDER_NUMBER = r1.ORDER_NUMBER; END LOOP;
END;