Home » SQL & PL/SQL » SQL & PL/SQL » logic and cosed (Windows 2003 and Oracle 10g)
logic and cosed [message #444952] Wed, 24 February 2010 22:46 Go to next message
aijaz786
Messages: 91
Registered: February 2010
Member
need to implemennt this situation using PL/SQL and SQL

create table master_rec(
c1 varchar2(5),
c2 varchar2(1),
numberrangefrom varchar2(4),
numberrangeto varchar2(4));

create table trans_rec(
c1 varchar(5),
c2 varchar2(1),
c3 varchar2(50),
issuedfrom varchar2(5),
issuedto varchar2(5),
transcode varchar2(10))

create table seq_detail_rec(

c1 varchar2(5),
c2 varchar2(1),
seq_no varchar2(4));

insert into master_rec values('2','DB','01','99');


master_rec keep records of all categories with their start and end range. Let us suppose for a certain task, for c1='DB' and c2='2', a sequence range is issued from 00-10 for coding purpose. Now the master_rec table is updated as


previous record - master-rec

c1 c2 numberrangefrom numberrangeto
DB 2 00 99

updated record - master_rec [after first transaction]

c1 c2 numberrangefrom numberrangeto
DB 2 11 89


let us suppose, second time for c1='DB' and c2='2', a sequence range is issued from 11-15 for coding purpose. Now the master_rec table is updated as


previous record - master_rec

c1 c2 numberrangefrom numberrangeto
DB 2 11 89


updated record - master_rec [after second transaction]

c1 c2 numberrangefrom numberrangeto
DB 2 15 84


And for transaction, a code need to be generated and stored in trans_rec table to uniqely identify each transaction.


then for each transaction record should be broken down to store like this in seq_detail_rec


c1 c2 seq_no
DB 2 00
DB 2 01
DB 2 02
DB 2 03
DB 2 04
DB 2 05
DB 2 06
DB 2 07
DB 2 08
DB 2 09
DB 2 10
DB 2 11
DB 2 12
DB 2 13
DB 2 14
DB 2 15
Re: logic and cosed [message #444955 is a reply to message #444952] Wed, 24 February 2010 22:53 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
>master_rec keep records of all categories with their start and end range.

My initial reaction is that this is a flawed design, that might suffer greatly in very active multi-user environment.
Re: logic and cosed [message #444956 is a reply to message #444952] Wed, 24 February 2010 23:14 Go to previous messageGo to next message
aijaz786
Messages: 91
Registered: February 2010
Member
Guys,

appreciated to do a better design to achieve this.
Re: logic and cosed [message #445156 is a reply to message #444952] Fri, 26 February 2010 04:35 Go to previous messageGo to next message
aijaz786
Messages: 91
Registered: February 2010
Member
Hi,

I will appreciate any solution to my porblem.
Re: logic and cosed [message #445167 is a reply to message #444952] Fri, 26 February 2010 05:14 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with data as (select 11 numberfrom, 15 numberto from dual)
  2  select numberfrom+level-1 nb 
  3  from data
  4  connect by level <= numberto-numberfrom+1
  5  /
        NB
----------
        11
        12
        13
        14
        15

5 rows selected.

Regards
Michel
Previous Topic: Commit and Ref cursor from GLOBAL TEMPORARY TABLE
Next Topic: Stored procedures variable arguments
Goto Forum:
  


Current Time: Sat Dec 03 19:54:52 CST 2016

Total time taken to generate the page: 0.10187 seconds