A memo about oracle

What the database can't avoid is to deal with data.

Situation: invoice information, many company employees fill in the same invoice information when conducting transactions, so company employees often want to call unified invoice information, and the company accounting login system can fill in general invoice information. in order to generate an order simply, the invoice needs to be bound with the employee number, so the invoice table wants to copy a copy of the invoice information with the employee number as the accountant and change the employee number. Invoice id, creation time, rest unchanged

I don't know if you understand the application situation, but the narrative ability is limited.

Solution: you can use select into, but it feels inconvenient and flexible. So we use the right side and the stored procedure, and because there is no return value, we don't write the function.

The above code:

Create or replace procedure myprocd (uid in varchar2) AS receipt_autoid number;BEGIN select SEQ_CHEM_DICT_RECEIPT.NEXTVAL into receipt_autoid from dual; for rs in (select * from CHEM_DICT_RECEIPT where user_id='0') loop rs.user_id:=uid; rs.id:=receipt_autoid; rs.receipt_id:='RE0215' | | receipt_autoid; rs.created_time:=sysdate; insert into chem_dict_receipt values rs End loop;END myprocd;execute myprocd ('002')

Reminder: I really don't understand how the sequence is added to the loop, so I changed the policy. I made the cursor read only once, that is, no more loops, so the changes are as follows

CREATE OR REPLACE procedure SIT_HXPGL.myproce (uid_re in varchar2, type_re in varchar2) AS receipt_autoid number (12); BEGIN select SEQ_CHEM_DICT_RECEIPT.NEXTVAL into receipt_autoid from dual; for rs in (select * from CHEM_DICT_RECEIPT where user_id='0' and receipt_type=type_re) loop rs.user_id:=uid_re; rs.id:=receipt_autoid; rs.receipt_id:='RE0215' | | receipt_autoid Rs.created_time:=sysdate; insert into CHEM_DICT_RECEIPT values rs; end loop;END myproce;/

Helpless: there is no way to mark red in the code. I added a new variable.

Type_re

In this way, my cursor can only read one set of data at a time.

Finally, with regard to the execution of stored procedures, it seems that both call and execute can be used. Call is recommended.

Little idea: it seems possible to get the self-increasing sequence in a custom function and then call it, which may not be a problem.

For functions and stored procedures or cursors, here is a good link, you can refer to

Some details of http://wen866595.iteye.com/blog/1733887 Program

There is a small problem that I actually found in practice, but I haven't read the official documentation of oracle yet.

Problem: the end END of the stored procedure

Solution: END myproc

Explanation: the name of the stored procedure should be added.