Path to Oracle (III)-PL/SQL Block

-- Exception test declarev_ename EMP. empno % type; -- defines the variable beginselect ename into v_ename from EMP where empno = & gno; dbms_output.put_line ('name: '| v_ename ); exception when no_data_found then dbms_output.put_line ('unfound'); end ;/

What is PL/SQL?
PL/SQL (Procedural Language/SQL) is an extension of Oracle in the standard SQL language. PL/SQL not only allows embedding SQL languages, but also defines variables and constants, allows the use of conditional statements and loop statements, and allows the use of exceptions to handle various errors, which makes its functions more powerful.
Figure:

Why Learning PL/SQL
Learning necessity
1. Improve application running performance
2. modular design concept [paging process, order process, transfer process ..]
3. reduce network transmission volume
4. Improve Security (SQL statements include table names, passwords, and data leakage during transmission. PL/SQL won't)

Why is PL/SQL faster? Figure:
Bad:
Poor portability (changing databases won't work ),

What to use to compile PL/SQL
Sqlplus development tools

Sqlplus is a tool provided by Oracle because we have previously introduced:
Here is a simple case:
Write a stored procedure that can add records to a table.
1. Create a simple table

SQL code

1. Create Table mytest (name varchar2 (30), passwd varchar2 (30 ));

Createtable mytest (name varchar2 (30), passwd varchar2 (30 ));

2. creation process

SQL code

1. Create or replace procedure sp_pro1 is
2. Begin -- execution part
3. Insert into mytest values ('Han shunping', 'm1234 ');
4. End;
5. /

Create or replace procedure sp_pro1 is

Begin -- execution part

Insert into mytest values ('Han shunping', 'm1234 ');

End;

/

Replace: indicates that if sp_pro1 exists, replace

How to view the error message: showerror;

How to call this process:

1) exec process name (parameter value 1, parameter value 2 ...);

2) Name of the call process (parameter value 1, parameter value 2 ...);

 

PL/sqln developer development tools
PL/SQL developer is an integrated development environment (IDE) used to develop PL/SQL blocks. It is an independent product rather than an accompanying product of oracle.
A simple case:
Write a stored procedure that deletes a table record.

SQL code

1. Create or replace procedure sp_pro2 is
2. Begin -- execution part
3. Delete from mytest where name = 'Han shunping ';
4. End;

Createor replace procedure sp_pro2 is

Begin -- execution part

Deletefrom mytest where name = 'Han shunping ';

End;

 

PL/SQL Introduction

Introduction

When developers use PL/SQL to write application modules, they not only need to understand how to write SQL statements, but also understand PL/SQL statements and syntax rules. PL/SQL programming can use variables and logical control statements to write very useful functional modules. For example, the paging Stored Procedure module, the order processing Stored Procedure module, and the transfer Stored Procedure module. In addition, if PL/SQL programming is used, we can easily complete complicated query requirements.

What can PL/SQL do?
Simple Classification

| ---- Process (Stored Procedure)
|
| ---- Function
Block (programming) ----- |
| ---- Trigger
|
| ---- Package

Writing specifications

1. Notes
Single line comment --

SQL code

1. Select * from EMP where empno = 7788; -- get employee information

Select * from EMP where empno = 7788; -- get employee information

 

Multi-line comment /*... */
2. Symbolic naming rules
1) when defining a variable, we recommend that you use V _ as the prefix v_sal.
2) When defining constants, we recommend that you use C _ as the prefix c_rate.
3) when defining a cursor, we recommend that you use _ cursor as the suffix emp_cursor.
4) when defining exceptions, we recommend that you use E _ as the prefix e_error.

PL/SQL block Introduction

Introduction

Block is the basic program unit of PL/SQL. compiling a PL/SQL program is actually writing a PL/SQL block, which requires relatively simple application functions, you may only need to write one PL/SQL block, but if you want to implement complex functions, you may need to nest other PL/SQL blocks in one PL/SQL block.

Block Structure n

A pl/SQL block consists of three parts: the definition part, the execution part, and the exception handling part.

As follows:

Declare

/* Definition section-define constants, variables, cursors, exceptions, and complex data types */
Begin
/* Execution part-the PL/SQL statements to be executed and SQL statements */
Exception

/* Exception Handling part -- handle various running errors */
End;

The definition part starts with declare and is optional;
The execution part starts from begin and is required;
The exception handling part starts from the exception and is optional.
You can make a simple comparison with the Java programming structure.

PL/SQL block instances (1)

Instance 1-only PL/SQL blocks for execution

SQL code

1. Set serveroutput on -- enable output options
2. Begin
3. Dbms_output.put_line ('hello ');
4. End;

Setserveroutput on -- enable output options

Begin

Dbms_output.put_line ('hello ');

End;

Instructions:
Dbms_output is a Java-like package provided by Oracle. This package contains some processes. put_line is a process of the dbms_output package.

 

PL/SQL block instances (2)
Instance 2-PL/SQL Block N containing the definition part and execution part

SQL code

1. Declare
2. V_ename varchar2 (5); -- defines string variables
3. Begin
4. Select ename into v_ename from EMP where empno = & aa;
5. Dbms_output.put_line ('employee name: '| v_ename );
6. End;
7. /

Declare

V_ename varchar2 (5); -- defines string variables

Begin

Select ename into v_ename from EMP whereempno = & aa;

Dbms_output.put_line ('employee name: '| v_ename );

End;

/

 

If you want to display the salary, the execution part should be as follows:

SQL code

1. Select ename, Sal into v_ename, v_sal from EMP where empno = & aa;

Selectename, Sal into v_ename, v_sal from EMP where empno = & aa;

Instructions:
& Indicates the variable to be received from the console. Figure:

PL/SQL block instances (3)
Instance 3-includes the definition part, execution part, and Exception Handling part n
To avoid PL/SQL program running errors and improve PL/SQL robustness, it is necessary to handle possible errors.
1. For example, in instance 2, if an employee number does not exist, an exception should be made.
2. Sometimes an exception occurs and you want to use another logic for processing. [network display]
Let's take a look at how to fulfill the requirements of 1.
Instructions:
Oracle predefines some exceptions. no_data_found is the exception where data cannot be found.