Oracle: stored procedure error

Question

I'm really new to SQL and I must create a stored procedure.

My idea is that I want to type my PaperRoll_ID and to get the "Worker_ID". Since my PaperRoll_ID values in the table are from 1 to 500 and my Worker_id values are from 1500 to 2000, I want to make it so that PaperRoll_ID is equal to the Worker_id index, not value by index (I mean, index 1 is the first Worker_id I added, 2 is the second and so on till 500 (the number of workers)). PaperRoll_ID is located in the table invoice_Paper and Worker_id in the table machine_operator.

Sorry if it's hard to understand, but I lack a lot of knowledge in SQL, so it's a bit hard for me to express myself.

create or replace PROCEDURE name_worker(pi IN NUMBER, mi OUT NUMBER) IS
BEGIN
    Select q.worker_ID2 INTO mi 
    from invoice_paper z,machine_operator o 
    where z.PaperRoll_ID=pi AND o.WORKER_ID2 = q.worker_ID2;
END;

The tables are

create Table invoice_paper(
PaperRoll_ID Number(10) constraint ppr_id not null,
Single_Layer Varchar(20) Default 'None in stock',
Double_Layer Varchar(20) Default 'None in stock',
Manufacturer_FactoryID Integer,
primary key(PaperRoll_ID),
Constraint pprid_invoice Foreign key (Manufacturer_FactoryID) References   Paper_Factory(Factory_ID)
 );

create table machine_operator(
Insurence_ID number(10) constraint in_numb not null,
Worker_ID2 number(10) constraint worka_id not null,
operator_name Varchar(20),
Email Varchar(30),
Primary key (Insurence_ID, Worker_ID2),
Constraint wka_id Foreign key(Worker_ID2) References worker(worker_id)
);

Sample data and desired results would really help express what you want to do. — Gordon Linoff, Jan 23 '17 at 12:53
What database object is represented by the alias `q`. It doesn't map to anything in the FROM clause. As it stands you have no join criteria between `invoice_paper` and `machine_operator`. — APC, Jan 23 '17 at 13:01
my mistake, the alias should be the **o** from the machine operator table — specbk, Jan 23 '17 at 13:03
there is a ROWNUM which can give you a row number for for each and you can join on the basis of that but the ROWNUM generated is dynamic and can change. Refer http://stackoverflow.com/questions/2701782/rowid-oracle-any-use-for-it — Rajat Mishra, Jan 23 '17 at 13:21
@RajatMishra - that answer relates to ROWID which is rather different from ROWNUM. Although it's moot, as neither is particularly useful as a solution to the OP's problem. — APC, Jan 23 '17 at 14:01

APC · Answer 1 · 2017-01-23T14:07:32.227

"I want to make it so that PaperRoll_ID is equal to the Worker_id index, not value by index (I mean, index 1 is the first Worker_id I added, 2 is the second and so on till 500 (the number of workers)"

That's really not how relational databases work. You should enforce such a relationship with a foreign key between invoice_paper and machine_operator, probably by adding Worker_ID2 column to invoice_paper (*).

Beyond that it's not clear what you are trying to achieve with your procedure, so it's hard to suggest anything better. However, let's assume what you want to do is get the next free worker for assignment to invoice_paper.

create or replace PROCEDURE name_worker(mi OUT NUMBER) IS
BEGIN
     Select min(o.worker_ID2) INTO mi 
     from machine_operator o 
     where o.WORKER_ID2 not in (select p.worker_ID2 
                                from invoice_paper p)
     ;
END;

Kept as a procedure for ease of comparison, although this sort of thing is usually written as a FUNCTION with a return value instead.

(*) Just noticed that machine_operator has a compound primary key. If this is correct the foreign key would have to be (Insurence_ID, Worker_ID2) which is ugly. It these situations it is better to have a single column surrogate primary key and enforce the compound key with an additional unique constraint.

Oracle: stored procedure error

1 Answers1