Oracle Cursor를 이용한 Recursive Query 구현

Oracle Cursor를 이용한 Recursive Query 구현

since 10 March, 1999 last modified 10 March, 1999

Oracle Cursor을 이용하여 Recursive Logic 을 구현

Bill Of Material 상의 단위부품과 조립부품 사이의 상하관계 표현
SQL 자체는 일반적인 Recursive Query가 불가하므로 N 계층의 하위 부품 검색 불가
DB Application을 사용할 경우 Cursor 가 Recursive Call을 지원하지 못함 (ORACLE 7.3.3)
C Program Language가 Recursive Call이 가능하므로 이를 이용하여 구현 (tree_nav() function 참조)

/* -----------------------------------------
   navi.pc 1999.3.9 by Namchul Do
   navigate all the sub parts in VPM database
   It uses part_list and $EXT tables
-------------------------------------------*/
#include 
#include 
#include 

#include 
#include 

/* Oracle error code for 'table or view does not exist'. */
#define NON_EXISTENT -942

void do_connect();
void sql_error();
void tree_nav();
void indent();

/*==========================
  Tree navigation         */
void tree_nav(part_nbr,level)
char *part_nbr;
int level;
{
char s_part_no [12];
char *part_no_list[100];
int i = 0;
int count = 0;
printf("%d %s \n",level part_nbr);
EXEC SQL VAR s_part_no is string(12);
EXEC SQL whenever sqlerror do sql_error("tree_nav");
EXEC SQL declare key_cursor cursor for

SELECT c.s_part_no
FROM parts.part_list a, parts."$EXT" b, parts.part_list c
WHERE a.s_part_no = :part_nbr AND
 a."$COID"=b."$COID" AND b."$COID_REF"=c."$COID" AND
 b."$TYPE" ='CATASS';

EXEC SQL open key_cursor;

while(1)
 {
 EXEC SQL whenever not found do break;
 i=i+1;
 EXEC SQL fetch key_cursor into :s_part_no;
 part_no_list[i]=malloc(12);
 strcpy(part_no_list[i],s_part_no);
 }
count=i;
EXEC SQL whenever not found continue;
EXEC SQL close key_cursor;
i=0;
for(i=1;i < count;i=i+1)
 {
        tree_nav(part_no_list[i],level+1);
  }
}

/*========================
   Indentation          */
void indent(level)
int level;
{
 int i;
 for (i=0 ; i < level ; i++)
        printf("+");
}

/*========================
   MAIN PROGRM BEGINES
========================*/
main(argc, argv)
int argc;
char **argv;
{

  do_connect();
  tree_nav(argv[1],0);

  EXEC SQL commit work release;
}


/* ============================
   Connect to the database.  */
void do_connect()
{

/* Note this declaration: uid is a char *
 * pointer, so Oracle will do a strlen() on it
 * at runtime to determine the length.
 */
  char *uid = "parts/parts";

  EXEC SQL whenever sqlerror do sql_error("Connect");
  EXEC SQL connect :uid;

  printf("Connected.\n");
}


/* ============================
   Generic error handler. */
void sql_error(routine)
  char *routine;
{
  char message_buffer[512];
  size_t buffer_size;
  size_t message_length;

/* Turn off the call to sql_error() to avoid
 * a possible infinite loop.
 */
  EXEC SQL WHENEVER SQLERROR CONTINUE;

  printf("\nOracle error while executing %s!\n", routine);

/* Use sqlglm() to get the full text of the error message. */
  buffer_size = sizeof(message_buffer);
  sqlglm(message_buffer, &buffer_size, &message_length);
  printf("%.*s\n", message_length, message_buffer);

  EXEC SQL ROLLBACK WORK RELEASE;
  exit(1);
}