Oracle PL/SQL Foundation

I. PL/SQL Developer Development Tools
PL/SQL Developer is an integrated development environment (IDE) for developing PL/SQL blocks, which is a standalone product, not a companion to Oracle.

Second, PL/SQL Introduction
When developers use PL/SQL to write application modules, they not only need to master the method of writing the statement, but also master the PL/SQL statements and grammar rules. PL/SQL Programming can use variables and logical control statements to write very useful functional modules. For example: Paging stored procedure module, order Processing stored procedure module, transfer stored procedure module. And if you use PL/SQL programming, we can easily accomplish very complex query requirements.

Iii. what can PL/SQL do
Can be used to write stored procedures, functions, triggers, packages, etc.

Iv. Preparation of specifications

V. PL/SQL Block introduction
Block is the Basic program unit of PL/SQL, writing a PL/SQL program is actually writing PL/SQL blocks, to complete a relatively simple application function, you may only need to write a PL/SQL block, but if you want to implement complex functions, you may need to pl/ Additional PL/SQL blocks are nested within the block.

Six, block structure
A PL/SQL block consists of three parts: the definition section, the execution section, and the Exception handling section.
As shown below:
Declare
/* Definition section--Define constants, variables, cursors, exceptions, complex data types */
Begin
/* Execute part--PL/SQL statement to execute
exception
/* Exception Handling section--processing of various errors of operation */
End

Description
The definition section starts with declare, which is optional;
The part of the implementation begins with begin, which is necessary;
The exception handling section starts with exception, which is optional.
A simple comparison can be made with the Java programming architecture.

vii. example of PL/SQL block

Instance one includes the PL/SQL block of the execution part

Set serveroutput on; --Open Output option


   Dbms_output.put_line (' Hello World ');
End
/--Execution

Related instructions:
Dbms_output is a package provided by Oracle (Java-like development package) that contains procedures that put_line is a process for dbms_output packages.

instance two of PL/SQL block

Example two contains a PL/SQL block that defines a part and an execution part

Set serveroutput on; --Open Output option
DECLARE
    --Define string variables
    
BEGIN
    --Executive Section
    Select Ename to V_ename from EMP where empno=&empno; --& indicates that you want to receive variables entered from the console

    --Show Employee name on console

    Dbms_output.put_line (' Employee Name: ' | | V_ename);
End
/

Nine, PL/SQL block instance three
Instance three contains the definition part, the execution part and the exception processing part
In order to avoid the operation error of PL/SQL program and improve the robustness of PL/SQL, it is necessary to deal with possible errors.
1. For example, in example two, if you enter a non-existent employee number, you should do an exception.
2. Sometimes the exception, hoping to use another logic processing, we see how to complete the 1 requirements.

Description: Oracle has pre-defined some exceptions, No_data_found is the exception to the data that is not found

--Open Output option

DECLARE
    --Define string variables
    
    V_sal number (7,2);
BEGIN
    --Executive Section
    
    Dbms_output.put_line (' Employee Name: ' | | v_ename| | ', Salary: ' | | V_sal);
EXCEPTION
    --Exception handling    
    When the No_data_found then Dbms_output.put_line (' friend, your number entered is wrong! ‘);
End
/

See also: http://www.cnblogs.com/linjiqin/archive/2012/02/22/2363869.html

Oracle PL/SQL Foundation