Han shunping: Oracle PL/SQL lecture notes 1

Make the program a soul

By think

1 What is PL/SQL?

Procedures, functions, and triggers are written in PL/SQL.

The process, function, and trigger are in the Oracle System tablespace.

PL/SQL is a very powerful database Procedural Language

The function can be called in a Java program.

 

2
Why PL/SQL

 
1) improve the running performance of applications

Because the pre-processing SQL statements are written into modules and placed in Oracle for parsing, the application only needs to call these modules, saving a piece of SQL to be uploaded to Oracle, parsing, and compiling, then return.

 
2) Modular Design

For example, the paging process, transfer process, order process, and so on are written in Oracle by modules and called when needed. This saves manpower and improves system quality, it also optimizes management.

 
3) reduces the amount of network transmission

 
4) Improve Security

General SQL statements directly list the table name, field name, and even password in the application. If you put these statements in the stored procedure and add Oracle protection, security is self-evident.

 

3
Test Tool

 
Write a process that can add a record to a table

1)
Create a simple table

Create Table test_01 (name varchar2 (15 ))

2)
Create a simple process

Create procedure p_think

Is

Begin

Insert into test_01 values ('think ');

End;

/

3)
View error information

Show ERROR

4)
Call

Exec
Process name (parameter value)

 

4
Development Tools

 
Sqlplus PL/SQL develop
Toad

 

5. The basic unit of PL/SQL programming is block. Construct processes, functions, triggers, and packages based on blocks. Compiling PL/SQL programs is actually writing PL/SQL blocks. Complex services may require multiple parallel or nested PL/SQL blocks. A block consists of three parts:

 
Declare

 
/* The definition part, such as constant, variable, cursor, and exception. Optional */

 
Begin

 
/* The execution part. The PL/SQL statements and SQL statements to be executed must be */

 
Exception

 
/* Exception Handling Section-handle various errors during runtime. Optional */

 

6
Programming specifications

 
1) annotations

Single line comment :--

Multi-line comment :/*........ */

 
2) identifier naming rules

When defining variables, we recommend that you start with V _.

When defining constants, we recommend that you start with C _.

When defining exceptions, we recommend that you start with E _.

Suggested _ cursor suffix when defining a cursor