Han shunping: Oracle PL/SQL Lecture Notes II

This is the 25th lecture, mainly about the basic understanding of blocks and processes.

1. One of the simplest blocks:
Set serveroutput on -- open the output option. If it is off, put_line () has no output.
Begin
Dbms_output.put_line ('hello ');
End;
Note: dbms_output is a package provided by Oracle. Contains procedures and functions. Put_line is a process of dbms_output.

The simplest part two:
Declare
V_ename varchar2 (5); -- defines string variables
V_sal number (7,2)
Begin
Select ename, Sal into v_ename, v_sal from EMP where empno = 7788;
Dbms_output.put_line ('user name is: '| v_ename );
End;

The simplest part 3:
Problem introduction:
A post from chianunix
Select cdzj into CZ from T1 where Cn = 1;
For example, in the preceding SELECT statement, if no_data_found is not found when CN = 1 is not found, the following statements cannot be executed. I want to make the CN value 0 when CN = 1 is not found, then you can continue executing the following statements. Do you know if you have any solutions?
Method: capture and process exceptions.
Declare
V_ename varchar2 (5); -- defines string variables
V_sal number (7,2)
Begin
Select ename, Sal into v_ename, v_sal from EMP where empno = 7788;
Dbms_output.put_line ('user name is: '| v_ename );
Exception
When no_data_found then
Dbms_output.put_line ('Friend, your input is incorrect ');
End;

2 Process
Definition: programs that perform specific operations
One case:
Modify Employee Salary Based on employee name
Create procedure sp_think (spname varchar2, newsal number) -- specify the type and the size is not required.
Is
Begin
Update EMP set sal = newsal where ename = spname;
End;
/
Call:
Exec sp_think ('Scott, 5000 );
Note: If a stored procedure is called in sqlplus or toad, it will survive, that is, a test. a meaningful way is to call the process in the main language such as Java.