Hi,
I have a lot of PL/SQL tips to share. Here’s a first installment—perhaps more to come!
Tip #1
The first thing I encourage all to use is %TYPE and %ROWTYPE on your variable definitions. In yesteryear, there was a tad bit of overhead with these, because when the routine started up, Oracle always had to run some SQL on your behalf (this is called recursive SQL) to pull these definitions from the database. I call this ‘runtime inheritance’.
CREATE or REPLACE PROCEDURE Example_Tip1 as
v_start_time NUMBER := 0;
v_end_time NUMBER := 0;
v_empno user0.emp_info.empno%TYPE;
v_emp_info_row user0.emp_info%ROWTYPE;
a number := 0;
.
.
.
In the above example, v_empno will pick up the field attribute type and length from the EMP_INFO table, and the EMPNO column specifically. v_emp_info_row picked up all the column names and attributes from the EMP_INFO table. This is a convenient setup when doing a SELECT * INTO V_EMP_INFO_ROW …type syntax. Upside: saves a ton of typing when setting up columns from tables. You reference the EMPNO column in this rowtype with the syntax ‘v_emp_info_row.empno’.
This technique also guarantees you will not get a data type mismatch error or a string too small-type error when working with these columns.
This technique also aids at test time when making some columns in the database a bit longer, for instance. You still have to test all the routines, as you would with any change, but using this technique should cut down on the actual code changes needed to implement the desired database change.
You can use a bodiless package to implement the %ROWTYPE and only do the recursive SQL call to create the rowtype once.
CREATE OR REPLACE PACKAGE USERx_GLOBAL_VARS AS
Access_cnt number := 0;
StartTime DATE := SYSDATE;
Emp_Rec emp%rowtype;
END USERx_GLOBAL_VARS;
/
set serveroutput on
BEGIN
userx_global_vars.access_cnt := userx_global_vars.access_cnt + 1;
dbms_output.put_line('global count = ' || userx_global_vars.access_cnt);
SELECT * into userx_global_vars.Emp_Rec
FROM emp
WHERE …
Dbms_output.put_line(“Employee Name = ‘ || userx_global_vars.emp_rec.ename);
END;
In the above example, the USERX_GLOBAL_VARS package has no body, just the specification.
***Sub Tip*** When working with packages, I always create the spec of the body first; then you can do a describe on it and make sure your names and IN/OUT variables appear as you want them to. If the spec part of the body doesn’t get created correctly, the body part will have errors it should not have.
Notice in the above example that there is an implicit cursor that selects a row into the %ROWTYPE. Notice the syntax needed to load and access these (see the dbms_output.put_line for additional syntax example).
Tip #2
Don’t use the GOTO statement. It makes for sloppy coding techniques. Also, maybe you can get out of a loop accidentally. You really need to end your loops normally. PL/SQL keeps track of the loops, iterations, and most importantly, when the loop ends. If you GOTO out of a loop, and your code leads you back into the same loop syntax, PL/SQL could think it’s still in the same loop…and you can get unpredictable results.
Oracle11 introduced the CONTINUE clause. This should also help eliminate a GOTO to skip code when you want to just start the next iteration of the same LOOP.
Continue Clause Example
Notice in the middle, if X < 3, the code does not print a line and logic is transferred back to the LOOP statement just after the BEGIN clause. It does exit the loop when x = 5.
Looping Clause using Labels Example
This example comes from the Oracle Documentation PL/SQL Users Guide and illustrates exiting an inner loop without using a GOTO statement. Notice the label outside the outer loop ‘<<outer>>’. Notice the ‘EXIT outer WHEN’ clause in the inner LOOP. This syntax will exit both loops and continue processing after the ‘END LOOP outer’ code.
I have used labels some in my PL/SQL adventures; not a lot, but some. This is a good practice when you want to exit the entire nested loop. This syntax helps you not have to have additional IF statements to check to see if you wish to exit the outer loop as well.
1: DECLARE
2: loop_counter NUMBER := 0;
3: BEGIN
4: FOR rec IN (SELECT *
5: FROM emp)
6: LOOP
7: insert into multi_key_tbl2 values (30, 300, rec.empno, rec.ename);
8: FOR key1_counter in 1 .. 50
9: LOOP
10: loop_counter := 0;
11: FOR key2_counter in 200 .. 400
12: LOOP
13: loop_counter := 0;
14: insert into multi_key_tbl1 values (key1_counter, key2_counter, rec.empno, round(rec.sal *.025) );
15: END LOOP;
16: Commit;
17: END LOOP;
18: END LOOP;
19: commit;
20: END;
21: /
This loop is 3-deep. I’m show it because this is a clever way I used to generate a lot of test data quickly. Instead of doing export/import or saving my test data in another table, I’d simply rerun this script to refresh the table data for the next test.
When I did this technique, the DBMS_RANDOM.VALUE random number generator did not exist. Today, I’d use the random number generator instead of doing math on the REC.SAL column.
In the above example I’ve added line numbers to make the code easier to discuss.
The main loop starts at line 4. Notice this is a classic cursor loop where PL/SQL will set up the cursor area, do the open/close/ and perform the loop while fetching rows. The second loop (starting line 8) sets up some data used in the insert at line 14. This loop is performed 50 times for each row returned from the cursor loop at line 4. The innermost loop starting at line 11 also sets up some variable data that will be inserted at line 14 and is performed 200 times for each iteration of the loop at line 8. Outside this loop at line 16 is a commit that will commit these 200 rows, thus not filling rollback segments for the Oracle RDBMS. I always put a final commit (see line 19) in to ensure that all the rows got written to my test table.
This script will generate 64,000 rows of test data. It can easily be modified to use the random number generator.
As always, you can ask me for the scripts and sample code.
I hope you find these tips useful in your day to day use of the Oracle RDBMS.
Dan Hotka
Author/Instructor/Oracle Expert
Start the discussion at forums.toadworld.com