Oracle PL/SQL Basics

PL/SQL is a structured programming language, blocks are the most basic structure in PL/SQL programs, and all PL/SQL programs are composed of blocks.
PL/SQL consists of three parts: Variable description, program code, and exception handling code.

Declare ----- mark declaration part
Begin ------ mark the beginning of the program body
Exception ------ mark the start of Exception Handling
End; -------- mark the end of the program body

 

PL/SQL example

1>

Set serveroutput on;
Declare
Empname varchar2 (20 );
Begin
Select emp_name into empname from cus_emp_basic where emp_no = '20140901 ';
Dbms_output.put_line (empname );
End;

Use the set serveroutput on command to set the environment variable serveroutput to the open state, so that the PL/SQL program can output the results in SQL * Plus and SQL * Plus.

You can use the dbms_output.put_line () function to output the parameter value.

1> condition statements

Set serveroutput on;
Declare
Num INTEGER: = 3;
Begin
If num <0 then
Dbms_output.put_line ('negative number ');
Elsif num> 0 then
Dbms_output.put_line ('positive number ');
Else
Dbms_output.put_line ('0 ');
End if;
End;

 

2>

Loop... exit... end

Set serveroutput on;
Declare
Num INTEGER: = 1;
V_sum INTEGER: = 0;
Begin
Loop
V_sum: = v_sum + num;
Dbms_output.put_line (Num );
If num = 4 then
Exit;
End if;

Dbms_output.put_line ('+ ');
Num: = num + 1;
End loop;

Dbms_output.put_line ('= ');
Dbms_output.put_line (v_sum );
End;

3>
Loop... exit when... end

Set serveroutput on;
Declare
V_num INTEGER: = 1;
V_sum INTEGER: = 0;
Begin

Loop
V_sum: = v_sum + v_num;
Dbms_output.put_line (v_num );
Exit when v_num = 4;

Dbms_output.put_line ('+ ');
V_num: = v_num + 1;
End loop;

Dbms_output.put_line ('= ');
Dbms_output.put_line (v_sum );
End;

4>

Loop statement while... loop... end Loop

Set serveroutput on;
Declare

V_num INTEGER: = 1;
V_sum INTEGER: = 0;

Begin

While v_num <= 4
Loop
V_sum: = v_sum + v_num;
Dbms_output.put_line (v_num );
If v_num <4 then
Dbms_output.put_line ('+ ');
End if;

V_num: = v_num + 1;
End loop;
Dbms_output.put_line ('= ');
Dbms_output.put_line (v_sum );
End;

 

5>

Loop statement for... in... loop... end Loop

 

Set serveroutput on;
Declare
V_num integer;
V_sum INTEGER: = 0;
Begin

For v_num in 1 .. 4
Loop
V_sum: = v_sum + v_num;
Dbms_output.put_line (v_num );
If v_num <4 then
Dbms_output.put_line ('+ ');
End if;
End loop;

Dbms_output.put_line ('= ');
Dbms_output.put_line (v_sum );
End;