Oracle DBA FAQ - Introduction to PL/SQL

Home >> FAQs/Tutorials >> Oracle DBA FAQ

By: FYIcenter.com

Part: 1 2 3 4

(Continued from previous part...)

How To Manage Transaction Isolation Level?

Transaction isolation level can be managed in a procedure by using the SET TRANSACTION and COMMIT statements. Here is a sample script on how to manage transaction isolation level:

SQL> CREATE OR REPLACE PROCEDURE HR.TOTAL_SALARY AS
  2    total_salary NUMBER(12,2);
  3  BEGIN
  4    SET TRANSACTION READ ONLY;
  5    SELECT SUM (salary) INTO total_salary FROM employees;
  6    DBMS_OUTPUT.PUT_LINE('Total salary 1: ' 
  7       || total_salary); 
  8    -- Other sessions may change salaries of some records
  9    SELECT SUM (salary) INTO total_salary FROM employees;
 10    DBMS_OUTPUT.PUT_LINE('Total salary 2: ' 
 11       || total_salary);
 12    COMMIT;
 13  END;
 14  /

SQL> EXECUTE TOTAL_SALARY;
Total salary 1: 691400
Total salary 2: 691400

"READ ONLY" transaction level takes a read only snapshot of the database. This allows other sessions to update the database without any locks. All queries in the session will produces identical results. So both SELECT statements in this script will return the same value guaranteed.

How To Pass Parameters to Procedures?

Store procedures or functions can take parameters. You need to define parameters while defining the procedure, and providing values to parameters while calling the procedure. The script below shows you how to do this:

SQL> CREATE OR REPLACE PROCEDURE DBA_TASK (day VARCHAR2) AS
  2  BEGIN
  3    IF day = 'MONDAY' THEN
  4      DBMS_OUTPUT.PUT_LINE('Checking log files.');
  5    ELSIF day = 'FRIDAY' THEN
  6      DBMS_OUTPUT.PUT_LINE('Rebuild indexes.');
  7    ELSE
  8      DBMS_OUTPUT.PUT_LINE('Reading some papers.');
  9    END IF;
 10  END;
 11  /

SQL> EXECUTE DBA_TASK('MONDAY');
Checking log files.

SQL> EXECUTE DBA_TASK('SUNDAY');
Reading some papers.

As you can see, procedures with parameters can make procedures more flexible.

How To Define a Procedure inside Another Procedure?

Define a procedure inside another procedure is supported by PL/SQL. The following tutorial script shows you an example:

SQL> CREATE OR REPLACE PROCEDURE HR.DBA_WEEK AS
  2    PROCEDURE DBA_TASK (day VARCHAR2) AS
  3    BEGIN
  4      IF day = 'MONDAY' THEN
  5        DBMS_OUTPUT.PUT_LINE('Checking log files.');
  6      ELSIF day = 'FRIDAY' THEN
  7        DBMS_OUTPUT.PUT_LINE('Rebuild indexes.');
  8      ELSE
  9        DBMS_OUTPUT.PUT_LINE('Reading some papers.');
 10      END IF;
 11    END;
 12  BEGIN
 13    DBA_TASK('MONDAY');
 14    DBA_TASK('TUESDAY');
 15  END;
 16  /
 
SQL> EXECUTE DBA_WEEK;
Checking log files.
Reading some papers.

Remember that procedures used inside a procedure must be defined in the declaration block.

What Do You Think about PL/SQL?

After following through the tutorials in the FAQ collection, you probably agree that PL/SQL is indeed a general purpose database programming language. PL/SQL is a natural extension of SQL. It is very useful for DBA to automate specific administration tasks or for developers to developer simple business backend applications.

Part: 1 2 3 4

More...