Accompanying notes for Oracle Database (ii)-Block

1. Single-line Comment--multiline comment/*...*/

2. Naming conventions for glyphs
1) When defining variables, it is recommended to use v_ as a prefix v_sal
2) When defining constants, it is recommended to use C_ as a prefix c_rate
3) When defining cursors, it is recommended to use _cursor as a suffix emp_cursor
4) When defining an exception, it is recommended to use E_ as the prefix e_error

3. Block structure
The PL/SQL block consists of three parts: the definition section, the execution section, the exception handling part
As shown below:
declear--Optional
/* Define a section----define constants, variables, cursors, exceptions, complex data types */
begin--must
/* Execute part----the PL/SQL statement to execute */
exception--Optional
/* Exception Handling section----various errors in handling the operation */
End

4. Define and execute a process
CREATE PROCEDURE Xf_pro1 is
Begin
Insert into mytest (NAME,PASSWD) VALUES (' Xiaofei ', ' 1234 ');
End
--Viewing error messages
Show error;

5. How to call this procedure
exec process name;
The exec Procedure name (parameter 1, parameter 2, ...). ）；
Call Procedure Name (parameter 1, parameter 2, ...) ）；
Example: Execute Xf_pro1;
exec Xf_pro1;

6.java Program Results
public static void Main (string[] args) {
int a=1;//Definition
try{
a++;//execution
}catch (Exception e) {}//exception handling
}

7. Example:
Example 1. Only PL/SQL blocks that perform part
Set serveroutput on--open Output option
Set serveroutput off--off output option
The simplest block:
Begin
Dbms_output.put_line (' Hello,world ');
End
/*
Related instructions:
Dbms_output is a package provided by Oracle (Java-like development package) that contains procedures that put_line is a process of dbms_output packages
*/
Example 2. PL/SQL block with definition and execution part
Declare
V_ename VARCHAR2 (5);
Begin
Select Ename to V_ename from EMP where empno=&no;
Dbms_output.put_line (' Employee Name: ' | | V_name);
End
/
/*
Related instructions:
--The address symbol is entered from the console
--into V_name means to put the found information into the variable v_name (i.e. assignment)
--|| Represents a connector
*/

Declare
-Define variables
V_name varchar2 (5);
　　　　Begin
--Execution part
Select ename into V_ename from EMP where EMPNO=&AA;
--Displays the user name in the console
Dbms_output.put_line (' User name: ' | | V_ename);
End;
/

Show the user name and salary.
V_ename VARCHAR2 (5);
V_sal number (7,2);
Begin
Select Ename,sal to V_ename,v_sal from EMP where empno=&empno;
--Pay attention to the variable names to correspond
Dbms_output.put_line (' Employee Name: ' | | v_ename| | ' Salary: ' | | V_sal);
End
/

Example 3. Include the definition section, the execution section, and the Exception handling section
In order to avoid the operation error of PL/SQL program and improve the robustness of PL/SQL, the possible errors should be handled
1) For example, in Example 2, if you enter a non-existent employee number, you should do the exception handling
2) Sometimes abnormal, want to use the exception of the logic processing
Description: Oracle has pre-defined some exceptions, No_data_found is the exception to the data that is not found

Declare
V_ename VARCHAR2 (5);
V_sal number (7,2);
Begin
Select Ename,sal to v_ename,v_sal from EMP wherer empno=&aa;
Dbms_output.put_line (' username is: ' | | v_ename| | ' Salary: ' | | V_sal);
--Exception handling
exception
When No_data_found Then
Dbms_output.put_line (' friend, you entered the wrong number ');
End
/

Accompanying notes for Oracle Database (ii)-Block