Oracle stored procedure 3: Advanced

1. The select into statement requires at least one record of the select result;
If RS is null, no_data_found exception is thrown. Therefore, exception capture is required;

2. When performing update and delete operations, you can replace the PK with rowid to improve execution efficiency;
Because rowid is directly located to the physical disk address, it does not need to be queried from the primary key index first, thus effectively reducing the number of I/O operations;
For example, update md_user set name = xx where rowid = ?;
However, be careful when using rowid, because 1) rowid will be reused; 2) it cannot be used in conditions with the group by statement;

3. If you do not need to do anything after the exception is captured, you can handle it as follows:
Begin... exception when no_data_found then NULL; end;

4. common types of exceptions that can be captured include:
No_data_found: The RS returned by a select into statement in the code block is null;
Too_mang_rows: Multiple records that meet the conditions of the select into statement are returned;
Dup_val_on_index: For a column in the database table, this column has been restricted to a unique index, and the program tries to store two duplicate values;
Value_error: this exception occurs when converting the character type, truncation, or length is limited. For example, if a character is assigned to a variable, the length of the variable declaration is shorter than that of the character, this exception is thrown;
Storage_error: memory overflow;
Zero_divide: the divisor is zero;
Case_not_found: For the case statement selected, there are no matching conditions, and no else statement captures other conditions;
Cursor_already_open: The program attempts to open an opened cursor;
Timeout_on_resource: The system is waiting for a resource and the time has timed out;
Others: All;
In addition, raise can be used to throw a custom exception. For example:
Declare my_error exception;
Begin
If (...) then
Raise my_error;
End if;
Exception when my_error then
...
When others then
...
End;

5. When bulk collect into is batch operated, only one SQL operation can be performed under forall, and it cannot be stored or called. If you need to execute multiple SQL statements, you need to use multiple forall statements, such
...
Loop
-- 1000 entries per batch
Fetch cur_name bulk collect into var_rowid, v_col2,... limit 1000;
Forall I in var_rowid.first... var_rowid.last
Insert ...;
Forall I in var_rowid.first... var_rowid.last
Update table_name set ...;
Commit;
Exit when cur_name % notfound;
End loop;

6. Efficient SQL cleaning for duplicate data:
Delete from table_name TN
Where tn. rowid>
(Select Min (X. rowid)
From table_name x
Where X. col1 = tn. col1
And X. col2 = tn. col2 );
-- Based on the two fields col1 and col2, the last two fields meet the unique constraint;

7. Use one SQL statement to update the data if the data already exists. Otherwise, add the data;
Merge into table_name TN
Using (select v_pk as PK from dual) TMPS
On (TN. PK = TMPS. pk)
When matched then
Update
Set col1 = ?, Col2 =? ,...
When not matched then
Insert (col1, col2 ,...)
Values (v_col1, v_col2 ,...);
Note: fields in the on clause cannot be set in the update clause;
 
8. Use the connect by statement
SQL> select rownum rn from dual connect by rownum <= 5;
 
Rn
----------
1
2
3
4
5
This statement is generally used in combination with cartesian products. A statement can handle many insert statements, which can greatly reduce the amount of SQL code;