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 Run the Anonymous Block Again?

If you have an anonymous block defined in your session, you can run it any time by using the "/" command as shown in the following script:

SQL> set serveroutput on;

SQL> begin 
  2    dbms_output.put_line('This is a PL/SQL FAQ.'); 
  3  end;
  4  /
This is a PL/SQL FAQ.

PL/SQL procedure successfully completed.

SQL> /
This is a PL/SQL FAQ.

PL/SQL procedure successfully completed.

What Is Stored Program Unit?

A stored program unit is a named block of codes which:

Has a name.
Can take parameters, and can return values.
Is stored in the data dictionary.
Can be called by many users.

How To Create a Stored Program Unit?

If you want to create a stored program unit, you can use the CREATE PROCEDURE or FUNTION statement. The example script below creates a stored program unit:

SQL> set serveroutput on;

SQL> CREATE PROCEDURE Hello AS
  2  BEGIN
  3    DBMS_OUTPUT.PUT_LINE('Hello world!');
  4  END;
  5  /
Procedure created.

How To Execute a Stored Program Unit?

If you want to execute a stored program unit, you can use the EXECUTE statement. The example script below shows how to executes a stored program unit:

SQL> set serveroutput on;

SQL> CREATE PROCEDURE Hello AS
  2  BEGIN
  3    DBMS_OUTPUT.PUT_LINE('Hello world!');
  4  END;
  5  /
Procedure created.

SQL> EXECUTE Hello;
Hello world!

How Many Data Types Are Supported?

PL/SQL supports two groups of data types:

SQL Data Types - All data types used for table columns.
PL/SQL Special Data Types - Like BOOLEAN or PLS_INTEGER.

The script below shows some data type examples:

SQL> set serveroutput on;

SQL> DECLARE                                                        
  2    title VARCHAR(8);                                            
  3    salary NUMBER;                                               
  4    seeking_job BOOLEAN;                                         
  5  BEGIN                                                          
  6    title := 'DBA';                                              
  7    salary := 50000;                                             
  8    seeking_job := TRUE;                                         
  9    DBMS_OUTPUT.PUT_LINE('Job Title: ' || title);                
 10    DBMS_OUTPUT.PUT_LINE('Expected salary: ' 
 11       || TO_CHAR(salary));
 12  END;                                                           
 13  /
Job Title: DBA
Expected salary: 50000

(Continued on next part...)

Part: 1 2 3 4

More...