Repeat Cursor [message #664510] |
Thu, 20 July 2017 09:42 |
|
SteveShephard
Messages: 41 Registered: August 2012
|
Member |
|
|
Hi all,
I have a cursor within a procedure (below). Is it possible to run through the cursor and if PART_STATUS equals a certain value, run through the cursor again but changing the value of PART_NO_ to the value of SUPERSEDED_PART
cursor get_details is
SELECT
SUPERSEDED_PART,
PART_STATUS
FROM PART_INO
WHERE PART_NO = PART_NO_;
Many thanks
Steve
|
|
|
|
|
Re: Repeat Cursor [message #664514 is a reply to message #664512] |
Thu, 20 July 2017 10:21 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Looping through a cursor till you find a particular value makes no sense unless the cursor has order, yours doesn't. What should it be ordered by?
If you give us a test case, along with the result you expect it's going to be a lot easier to work out what you are trying to achive
|
|
|
|
|
Re: Repeat Cursor [message #664517 is a reply to message #664516] |
Thu, 20 July 2017 11:15 |
|
Michel Cadot
Messages: 68658 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
This is NOT a test case.
Please read the link we gave you and post what we requested.
From your previous topic:
Michel Cadot wrote on Fri, 19 June 2015 13:54
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
...
Also you did not feedback in your previous topics, do you think we are your servants just good to solve your problems but do not deserve any gesture of gratitude from you not even an answer to our requests in order to help you?
[Updated on: Thu, 20 July 2017 11:16] Report message to a moderator
|
|
|
Re: Repeat Cursor [message #664519 is a reply to message #664517] |
Thu, 20 July 2017 12:08 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
this screams for using a simple query using the connect by clause. can you give a table create command and some sample data in insert commands. Thanks
|
|
|
|
|
Re: Repeat Cursor [message #664545 is a reply to message #664539] |
Fri, 21 July 2017 14:20 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
-- test environment:
SCOTT@orcl_12.1.0.2.0> -- version:
SCOTT@orcl_12.1.0.2.0> SELECT banner FROM v$version
2 /
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production
5 rows selected.
SCOTT@orcl_12.1.0.2.0> -- statements to create table with primary key and insert sample data like we expected you to provide:
SCOTT@orcl_12.1.0.2.0> CREATE TABLE part_ino
2 (part_no VARCHAR2(15) PRIMARY KEY,
3 status VARCHAR2(8),
4 superseded_part VARCHAR2(15))
5 /
Table created.
SCOTT@orcl_12.1.0.2.0> INSERT ALL
2 INTO part_ino VALUES ('PART_A', 'ACTIVE', 'NULL')
3 INTO part_ino VALUES ('PART_B', 'INACTIVE', 'PART_C')
4 INTO part_ino VALUES ('PART_C', 'ACTIVE', 'NULL')
5 INTO part_ino VALUES ('PART_D', 'INACTIVE', 'PART_E')
6 INTO part_ino VALUES ('PART_E', 'INACTIVE', 'PART_F')
7 INTO part_ino VALUES ('PART_F', 'ACTIVE', 'NULL')
8 SELECT * FROM DUAL
9 /
6 rows created.
SCOTT@orcl_12.1.0.2.0> -- resultng sample data:
SCOTT@orcl_12.1.0.2.0> SELECT * FROM part_ino
2 /
PART_NO STATUS SUPERSEDED_PART
--------------- -------- ---------------
PART_A ACTIVE NULL
PART_B INACTIVE PART_C
PART_C ACTIVE NULL
PART_D INACTIVE PART_E
PART_E INACTIVE PART_F
PART_F ACTIVE NULL
6 rows selected.
-- method using recursive subquery:
SCOTT@orcl_12.1.0.2.0> COLUMN active_part FORMAT A15
SCOTT@orcl_12.1.0.2.0> SELECT p2.part_no, p2.status, p2.superseded_part,
2 (SELECT MAX (p1.part_no) KEEP (DENSE_RANK LAST ORDER BY LEVEL)
3 FROM part_ino p1
4 START WITH p1.part_no = p2.part_no
5 CONNECT BY PRIOR p1.superseded_part = p1.part_no
6 AND PRIOR p1.status != 'ACTIVE') AS active_part
7 FROM part_ino p2
8 /
PART_NO STATUS SUPERSEDED_PART ACTIVE_PART
--------------- -------- --------------- ---------------
PART_A ACTIVE NULL PART_A
PART_B INACTIVE PART_C PART_C
PART_C ACTIVE NULL PART_C
PART_D INACTIVE PART_E PART_F
PART_E INACTIVE PART_F PART_F
PART_F ACTIVE NULL PART_F
6 rows selected.
-- method using recursive function:
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE FUNCTION active_part
2 (part_no_ IN part_ino.part_no%TYPE)
3 RETURN part_ino.part_no%TYPE
4 AS
5 superseded_part_ part_ino.superseded_part%TYPE;
6 status_ part_ino.status%TYPE;
7 return_value_ part_ino.part_no%TYPE;
8 BEGIN
9 SELECT superseded_part, status
10 INTO superseded_part_, status_
11 FROM part_ino
12 WHERE part_no = part_no_;
13 IF status_ = 'ACTIVE' THEN return_value_ := part_no_;
14 ELSIF status_ = 'INACTIVE' THEN return_value_ := active_part (superseded_part_);
15 END IF;
16 RETURN return_value_;
17 END active_part;
18 /
Function created.
SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
SCOTT@orcl_12.1.0.2.0> COLUMN active_part FORMAT A15
SCOTT@orcl_12.1.0.2.0> SELECT part_no, status, superseded_part,
2 active_part (part_no) AS active_part
3 FROM part_ino
4 ORDER BY part_no
5 /
PART_NO STATUS SUPERSEDED_PART ACTIVE_PART
--------------- -------- --------------- ---------------
PART_A ACTIVE NULL PART_A
PART_B INACTIVE PART_C PART_C
PART_C ACTIVE NULL PART_C
PART_D INACTIVE PART_E PART_F
PART_E INACTIVE PART_F PART_F
PART_F ACTIVE NULL PART_F
6 rows selected.
|
|
|