Oracle's stored procedure programming

What is a stored procedure.

is a collection of SQL that can be manipulated programmatically.

the advantages of stored procedures. execution is efficient because the stored procedure is precompiled, that is, when the compile is created, and the SQL statement is executed once, compiled once. Invoking a stored procedure can significantly reduce the number of interactions with the database.  Reduce network traffic because the stored procedure executes with only the call stored procedure name and no large number of SQL statements need to be delivered. Useful for reuse.

disadvantages of stored procedures. portability is very poor, and if stored procedures are written on Oracle, porting to MySQL requires modification. Code is less readable and implements a simple logic that can be very long.

The purpose of the stored procedure. Build test data: You can use stored procedures to create millions of of data into the table. Data synchronization: Data synchronization between two tables according to certain business logic. Data mining.

stored procedure considerations. when the data volume is large (100,000 +), must do the stress test, some stored procedures in the case of large amount of data will appear problems. If you insert or update more frequently, you can perform 10,000 times and commit once more to improve efficiency. If you insert a record first, no commit, and then update the record, it can cause a deadlock. If the same record is updated successively, there is no commit, also can cause deadlock. Because the latter statement waits for the previous statement to be submitted. If this happens, a commit is required. Don't forget to write a commit in the stored procedure.

how to write a stored procedure.

--Create or update stored procedures update_user_p Create or replace procedure update_user_p (param1 in varchar2) is   v_taskname VARCHAR2 (20) ;
--Define variables, Oracle types.
  v_i        Number (12);
 -Assigns the result set of the User_advisor_log table to cur   CURSOR cur is     SELECT * from User_advisor_log;  --sql start tag, above is defined variable, the following write program begin   Dbms_output.
Put_Line (param1);
  V_i: = 0;   Dbms_output.
Put_Line (' start! ');  --traverse result set   for Cur_result in cur LOOP       begin       V_tasknam E: = Cur_result. Task_name;
--Assign the result set to the variable v_creator, and the end of a statement requires a semicolon to end.          --if statement start       if v_taskname > 0 Then & nbsp;       begin           NULL;
--null statement shows that nothing is done, this sentence cannot be deleted, because at least one sentence is needed in the pl/sql body;
        end;
      End If;   &nBsp      --while cycle       while v_taskname > 0 Loop   
      begin           NULL;
        end;
      End LOOP;
         -recommended 10,000 times per cycle to submit       v_i: = v_i + 1;
      if mod (v_i, 10000) = 0 Then         commit;
      End If;          --there is an abnormal output, or rollback here     exception        when others then         Dbms_output.
Put_Line (' update_user_p has error! ');
    end;   End LOOP;
--cycle closure   commit;   Dbms_output.
Put_Line (' End and commit! ');
 End Update_user_p;

A simple data-making stored procedure

--build 400,000 data into the table.
Create or Replace procedure vas_create_acookie_data_p is
  v_i number (a);

Begin
  V_i: = 0;
  While V_i < 400000 loop
    begin
      inserts into TableName (gmt_created,
         CREATOR,
         gmt_modified,
         MODIFIER,
         member_id)
      values
        (sysdate, ' sys ', sysdate, ' sys ', v_i);
      V_i: = v_i + 1;
    
    End;
  End LOOP;
  commit;
End Vas_create_acookie_data_p;

how to execute a stored procedure.

Execute stored procedure: Call Update_user_p (' It is Param '). Dbms_output can be seen in output . The output of the put_line.

How to debug a stored procedure.

Edit the stored procedure in the Plsql, click Execute, and the system will tell you the wrong number of rows and reasons. and can display the code structure.

You can also use Dbms_output. Put_Line print exceptions, and note that when printing an exception, the output context (such as the wrong TaskName) is printed.

The performance test inserts 400,000 data into the stored procedure for 10 seconds.  Traversing and judging 400,000 data was used for 25 seconds. 800,000 times SQL decision + 400,000 SQL Insert = 25 seconds.

other problem stored procedures are slow to execute, possibly because the UPDATE statement caused a deadlock, or if the statement execution is slow (requires indexing). The stored procedure compiles very slowly, possibly because the current stored procedure is executing and is locked. (Unlock using DBA account).