ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Stored procedure on AS400 - how works cursor and update statement

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Stored procedure on AS400 - how works cursor and update statement

    Hi guys, I have a question, I have a stored procedure on AS400 and I see that I can call like this: call library.prcocedure_name(parameters), the object on AS400 is *pgm type. I never worked since now with stored procedure and for this reason I don't have very large knowledge.
    I understand that I can call it also on Iseries navigator with SQL interpretor- run sql scripts.
    Regarding this stored procedure I have something like this:

    create procedure proc_name()
    results sets 1
    language sql
    specific mylibrary.myproc_name

    begin

    Declare C1 cursor for
    select field1, field2, field3, field4, field5, field6 from mylibrary.file1 --- select only some fields
    where field3 in (' ', '1') --- with some conditions
    order by field6
    fetch first 10 row only;

    open c1;
    update mylibrary.file1
    set field2='adsa'
    where field5 ='1'
    or field6=' '
    end;


    Can you tell me please how it should work? the update it will be made on the results stored in the cursor? (the criteria specified in the cursor will be available when I make the update or only the criteria specified in the ''where'' clause in update statement?

    Thanks in advance.

  • #2
    Re: Stored procedure on AS400 - how works cursor and update statement

    Hi,

    "result sets 1" is for exposing a SET produced in the stored procedure, to the CALLER of the stored procedure. In other words, the CALLER of the stored procedure will be consuming, or reading through the SET rows that the stored procedure has produced. In your case, C1 represents a SET defined by DECLARE C1 CURSOR. By using OPEN C1, and not closing it, the SET represented by C1 is made available to the caller of the stored procedure. If you CALL the stored procedure from Run SQL Scripts, and everything works correctly (no errors), Run SQL Scripts should open your C1 SET and display the results for you.

    If you want to caller a stored procedure from another stored procedure, or SQL function, and you want the calling object to consume a result set exposed by the called stored procedure, refer to "ASSOCIATE LOCATORS" in the SQL Reference manual for examples on doing that.

    If you want to UPDATE FILE1, and then want to expose the updated results to the caller, I think it makes more logical sense to run the UPDATE before the OPEN.

    How your code will behave, depends on the sensitivity setting of the DECLARE CURSOR. If you use DECLARE C1 INSENSITIVE CURSOR, updates made to FILE1 AFTER the OPEN will NOT be displayed / available at the caller. If you use DECLARE C1 SENSITIVE CURSOR, updates made to FILE1 AFTER the OPEN will be displayed / available at the caller. See the sensitivity setting of DECLARE CURSOR in the SQL Reference manual.

    When PREPARE of a CURSOR in NOT in use, the DECLARE CURSOR defaults to ASENSITIVE, which means that SENSITIVE or INSENSITIVE may be used, depending on how the SQL statement is optimized. In your case, since SENSITIVE or INSENSITIVE will produce different results, you don't want to allow the default of ASENSITIVE to be used, because the results become unpredictable in the presence of data updates. So, either run the UPDATE before the OPEN, or add either the SENSITIVE or INSENSITIVE keyword to the DECLARE CURSOR, depending on which behavior you want.

    If you're not interested in passing results to the caller, then remove the DECLARE CURSOR, remove the OPEN, and remove RESULT SETS 1.

    The UPDATE does not need the involvement of a CURSOR. In fact, the best use of SQL is when you use as few cursors as possible, because eliminating CURSOR processing (i.e. single row processing), and sticking to SET based processing, yields MUCH higher performance. Your UPDATE is a SET based update, because you're updating all rows in FILE1 that match your WHERE clause.

    "results sets 1" should be "result sets 1".

    Mike

    Comment

    Working...
    X