Examples of Chapter 1 in PL/SQL Learning

1. Create a PL/SQL block instance and compile a PL/SQL program. The actual process is to compile a PL/SQL block record at, January 14 ,.

Example 1: PL/SQL blocks that only contain the execution part

SQL> set serveroutput on;
SQL> begin
2 dbms_output.put_line ('hello, everyone! ');
3 end;
4/
Hello, everyone!

Note: when using the dbms_output package to output data or messages, you must set the SQL * Plus environment variable serveroutputs to on

Example 2: PL/SQL blocks that contain the definition part and execution part

SQL> declare v_ename varchar2 (5 );
2 begin
3 select ename into v_ename from EMP
4 where empno = & No;
5 dbms_output.put_line ('employee name: '| v_ename );
6 end;
7/
Input no value: 7788
4: Where empno = & No;
New Value: 4: Where empno = 7788;
Employee name: Scott

The PL/SQL process is successfully completed.

Variables must be defined to temporarily store employee names. In the preceding example, & No is an alternative variable for SQL * Plus.

Example 3: PL/SQL blocks that contain the definition part, execution part, and Exception Handling part

To avoid PL/SQL program running errors and improve the robustness of PL/SQL programs, you should properly handle PL/SQL program running errors.

Declare v_ename varchar2 (5 );
Begin
Select ename into v_ename from EMP
Where empno = & No;
Dbms_output.put_line ('employee name: '| v_ename );
Exception
When no_data_found then
Dbms_output.put_line ('Enter the correct employee ID! ');
End;

NOTE: If no data is found in the Select name into v_name from table where condition during the stored procedure,
If the v_name value cannot be assigned, the no_data_found exception will be thrown and the program cannot continue to execute.


3.1.2 PL/SQL Block Classification

1. Anonymous Block

SQL> declare v_avgsal number (6, 2 );
2 begin
3 select AVG (SAL) into v_avgsal from EMP
4 where deptno = & No;
5 dbms_output.put_line ('average wage '| v_avgsal );
6 end;
7/
Input no value: 10
4: Where deptno = & No;
New Value: 4: Where deptno = 10;
Average salary 2916.67
The PL/SQL process is successfully completed.

Note: Since the PL/SQL block starts with declare directly and no name is given, the PL/SQL block is an anonymous block.

2. Name Block

A name block is a PL/SQL block with a specific name. The name block is very similar to an anonymous block, except that it is marked before a PL/SQL block <>.

3. subprograms are divided into processes, functions, and packages.

(1) create a function
Create Function heji3 (name varchar2)
Return number is
Salary number (7,2 );
Begin
Select Sal * 12 + nvl (Comm, 0) into salary from EMP
Where lower (ename) = lower (name );
End;
/

Because the function has output parameters, use SQL * Plus to bind variables to store the output results;
VaR income number
Call heji3 ('Scott ') into: income;
Print income

Income
------
2400