从PL/SQL FAQ中摘抄出来几篇比较有用的文章

全文阅读见PL/SQL FAQ

How can one see if somebody modified any code?

The source code for stored procedures, functions and packages are stored in the Oracle Data Dictionary. One can detect code changes by looking at the TIMESTAMP and LAST_DDL_TIME column in the USER_OBJECTS dictionary view. Example:

SELECT OBJECT_NAME,  
       TO_CHAR(CREATED,       'DD-Mon-RR HH24:MI') CREATE_TIME,  
       TO_CHAR(LAST_DDL_TIME, 'DD-Mon-RR HH24:MI') MOD_TIME,  
       STATUS  
FROM   USER_OBJECTS  
WHERE  LAST_DDL_TIME > '&CHECK_FROM_DATE';  

Note: If you recompile an object, the LAST_DDL_TIME column is updated, but the TIMESTAMP column is not updated. If you modified the code, both the TIMESTAMP and LAST_DDL_TIME columns are updated.

How can one search PL/SQL code for a string/ key value?

The following query is handy if you want to know where certain tables, columns and expressions are referenced in your PL/SQL source code.

SELECT type, name, line  
  FROM   user_source  
 WHERE  UPPER(text) LIKE UPPER('%&KEYWORD%');  

If you run the above query from SQL*Plus, enter the string you are searching for when prompted for KEYWORD. If not, replace &KEYWORD with the string you are searching for.

What is the difference between %TYPE and %ROWTYPE?

Both %TYPE and %ROWTYPE are used to define variables in PL/SQL as it is defined within the database. If the datatype or precision of a column changes, the program automatically picks up the new definition from the database without having to make any code changes.

The %TYPE and %ROWTYPE constructs provide data independence, reduces maintenance costs, and allows programs to adapt as the database changes to meet new business needs.

%TYPE

%TYPE is used to declare a field with the same type as that of a specified table's column. Example:

DECLARE  
   v_EmpName  emp.ename%TYPE;  
BEGIN  
   SELECT ename INTO v_EmpName FROM emp WHERE ROWNUM = 1;  
   DBMS_OUTPUT.PUT_LINE('Name = ' || v_EmpName);  
END;  
/  

%ROWTYPE

%ROWTYPE is used to declare a record with the same types as found in the specified database table, view or cursor. Examples:

DECLARE  
  v_emp emp%ROWTYPE;  
BEGIN  
  v_emp.empno := 10;  
  v_emp.ename := 'XXXXXXX';  
END;  
/  

How does one loop through tables in PL/SQL?

One can make use of cursors to loop through data within tables. Look at the followingnested loops code example.

DECLARE  
   CURSOR dept_cur IS  
   SELECT deptno  
     FROM dept  
    ORDER BY deptno;  
  
   -- Employee cursor all employees for a dept number  
   CURSOR emp_cur (v_dept_no DEPT.DEPTNO%TYPE) IS  
   SELECT ename  
     FROM emp  
    WHERE deptno = v_dept_no;  
BEGIN  
   FOR dept_rec IN dept_cur LOOP  
      dbms_output.put_line('Employees in Department '||TO_CHAR(dept_rec.deptno));  
  
      FOR emp_rec in emp_cur(dept_rec.deptno) LOOP  
         dbms_output.put_line('...Employee is '||emp_rec.ename);  
      END LOOP;  
  
  END LOOP;  
END;  
/  

How often should one COMMIT in a PL/SQL loop? / What is the best commit strategy?

Contrary to popular belief, one should COMMIT less frequently within a PL/SQL loop to prevent ORA-1555 (Snapshot too old) errors. The higher the frequency of commit, the sooner the extents in the undo/ rollback segments will be cleared for new transactions, causing ORA-1555 errors.

To fix this problem one can easily rewrite code like this:

FOR records IN my_cursor LOOP  
   ...do some stuff...  
   COMMIT;  
END LOOP;  
COMMIT;  

... to ...

FOR records IN my_cursor LOOP  
   ...do some stuff...  
   i := i+1;  
   IF mod(i, 10000) = 0 THEN    -- Commit every 10000 records  
      COMMIT;  
   END IF;  
END LOOP;  
COMMIT;  

If you still get ORA-1555 errors, contact your DBA to increase the undo/ rollback segments.

NOTE: Although fetching across COMMITs work with Oracle, is not supported by the ANSI standard.

---

Issuing frequent commits is bad, bad, BAD! It’s the WORST thing you can do… just don’t do it! In the following example I will create around 7 million rows and then attempt to update a portion of them serially. In addition, I will issue a commit every thousandth row.

Example 1.1: Creating a somewhat large table

SQL> create table big_employee_table  as  select  rownum as eid  ,  e.*  from  hr.employees e  ,  dba_objects do;  
Table created.  
Elapsed: 00:00:12.23  
SQL>   select  count(*)   from  big_employee_table;  
  COUNT(*)  
----------  
   7838713  
  
Elapsed: 00:00:08.11  

Before I go on, notice that Oracle’s “Create Table As” (CTAS) method blazed thru table creation. That’s 7.84 Million rows in 12.23 seconds. Sometimes, this is the very best method of updating large data sets. The following block updates 100,000 rows, serially, committing every 1000 rows:

Example 1.2: Updating serially

 
SQL> declare  
cursor c is  select  *   from  big_employee_table  where  rownum <= 100000;  
begin  
for r in c loop  
update  big_employee_table  set  salary = salary * 1.03  where  eid = r.eid;  
if mod ( r.eid, 1000 ) = 0 then  
commit;  
end if;  
end loop;  
 end;  
/  

Observe that the update took more time than I have patience for ;). At 20 minutes I killed the session. It is painfully slow and should never be done. Moreover, it chewed up an entire CPU core for the duration. If you’re only updating a few rows, why do it in PL/SQL at all? I like Tom Kyte’s approach (paraphrasing):

Do it in SQL.  
If SQL can’t do it, do it in PL/SQL.  
If PL/SQL can’t do it, do it in Java.  
If Java can’t do it ask yourself if it needs to be done.  

The following block does the same work in bulk:

Example 1.3: Updating in bulk and committing at the end

 
SQL> declare  
type obj_rowid is table of rowid  
index by pls_integer;  
lr_rowid    obj_rowid;  
lr_salary   dbms_sql.number_table;  
cursor c is  
select  rowid rid ,  salary  from  big_employee_table  where  rownum <= 100000;  
begin  
open c;  
 loop  
fetch c bulk collect  
into lr_rowid , lr_salary limit 500;  
for a in 1 .. lr_rowid.count loop  
lr_salary ( a ) := lr_salary ( a ) * 1.03;  
end loop;  
for all b in 1 .. lr_rowid.count  
update  big_employee_table  
set  salary = lr_salary ( b )  where  rowid in ( lr_rowid ( b ));  
xit when c%notfound;  
end loop;  
close c;  
commit; -- there! not in the loop  
exception  
when others then  
rollback;  
dbms_output.put_line ( sqlerrm );  
end;  
 /  
   
 PL/SQL procedure successfully completed.  
   
 Elapsed: 00:00:02.11  
 SQL>  

Notice that the update completed in 2 seconds! I’ve seen faster but my two-gerbil sandbox machine doesn’t have the power that our newer servers do. The point is that the update was incredibly fast and chewed up only 10% of one core. So, in answer to the question of “how often should I commit?” I say don’t until you absolutely have to