Chapter -01 Introduction to PL/SQL

Objectives

After completing this lesson,you should be able to do the following:

• Explain the need for PL/SQL
• Explain the benefits of PL/SQL
• Indentify the different types of PL/SQL blocks
• Out messages in PL/SQL

About PL/SQL

• Stands for "Procedural Language extension to SQL".
• Is Oracle Corporation`s standard data access language for relatinal databases.
• Seamlessly integrates procedureal constructs with SQL.
• Provides a block structure for executable units of code.
  • Maintenance of code is moade easier such a well-defined structure.
• Provides procedure constructs such as:
  • -Variables,constants,and data types
  • -Control structures such as conditional statements and loops
  • -Reusable program units that are written once and executed mamy times.

PL/SQL Environment

 

Benefits of PL/SQL

• Integration of procedural constructs with SQL
• Improved performance 　　

 

Select Specific Columns

• Modularized program development
• Integration with Oracle tools
• Portablity
• Exception handing

PL/SQL Block Structure

• DECLARE(optional)
  • -Variables,cursors,user-defined exceptions
• BEGIN(mandatory)
  • -SQL statements
  • -PL/SQL statements
• EXCEPTION(optional)
  • -Actions to perform when erros occur
• END; (mandatory)

 

Block Types

• Anonymons

[DECLARE]


BEGIN

    --statements    

[EXCEPTION]

END;

• Procedure

PROCEDURE name
IS


BEGIN

    --statements

[EXCEPTION]

END;

• Function

FUNCTION name
RETURN datatype
IS
BEGIN
    --statements

    RETURN value;

END;

• Procedure 与 Function的区别
  • Procedure不需要有返回值,Function一定要有返回值.
  • Anonymous不会存储在Oracle Database中，而Procedure与Function则存储在Oracle Database.

Program Constructs

Create an Anonymous Block

Simple Demo With PL/SQL

 1 DECLARE
 2         v_fname VARCHAR2(20);
 3 BEGIN
 4         SELECT first_name INTO v_fname
 5         FROM employees
 6         WHERE employee_id = 100;
 7         
 8         DBMS_OUTPUT.PUT_LINE(v_fname);
 9 
10 END;

Execute Results

SQL> l
  1  DECLARE
  2     v_fname VARCHAR2(20);
  3  BEGIN
  4     SELECT first_name INTO v_fname
  5     FROM employees
  6     WHERE employee_id = 100;
  7
  8     DBMS_OUTPUT.PUT_LINE(v_fname);
  9
 10* END;
SQL> /
Steven

PL/SQL procedure successfully completed.

SUMMARY

In this lesson,you should have learned how to:

• Interate SQL statement with PL/SQL program constructs
• Descibe the benefits of PL/SQL
• Differentiate between PL/SQL block typs
• Output messages in PL/SQL

 

 

转载于:https://www.cnblogs.com/arcer/archive/2013/04/18/3027986.html