Oracle Database Tutorial: PL/SQL notes

1. Create TableMyinfo (myname varchar2 (10), passwdVarchar(10 ));

Based on the above table myinfo

PL/SQL (procedural language/SQL ):Oracle is an extension based on the standard SQL language. In PL/SQL, You can nest SQL statements, define variables, define constants, and use logical statements (conditional statements, cyclic statements, etc), use exceptions to handle various errors(EXCEPTION is a PL/SQL identifier. If an error or warning occurs when you run the PL/SQL block, the exception is triggered. When the exception is triggered, the execution of the PL/SQL block is terminated by default, introduce the Exception Processing Section in PL/SQL blocks. Various exceptions can be captured and processed accordingly. Similar to try catch in java),Since it is an extension, it will certainly make its functions more powerful!

PL/SQL is a powerful database process language!

Important: PL/SQL is closely integrated in Oracle databases.

PL/SQL is one of the three languages used by the Oracle database, and the other two are SQL and Java.

Program languages with similar functions as PL/SQL and other relational databases: Sybase ASE, Microsoft SQL Server's Transact-SQL, and PostgreSQL database's PL/pgSQL (imitating pl/SQL) and IBM DB2 SQL pl [1], both comply with iso SQL/PSM standards.

Bytes ---------------------------------------------------------------------------------------------------

Advantages of pl/SQL:

Improve application performance, modular design, reduce network transmission volume, and improve security

(For traditional database operations, let's think about it: SQL statements are written in java programs to access the database. First, you get the connection and compile SQL statements so that the database can understand your SQL statements, every compilation is a waste of time. When the data volume is large, your program performance is poor! Here, if we write some operations into a process, such as paging and transfer (the function is divided into a module, which is maintained by a dedicated person ......). This greatly improves the performance of our program, and database operations are used as a module. When a java program is called, it calls compiled SQL statements, therefore, you can use the Stored Procedure for optimization during program optimization .)

[Of course, there are other advantages. Please add]

Disadvantages:

Portability is flawed when you need to change the database.

| | |

PL/SQL writing specifications:

A:

Single line comment :--

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

B:

Naming rules for identifiers:

A. Define the variable. We recommend that you use v _ as the prefix, for example, v_sale.

B. Define constants. We recommend that you use c _ as the prefix,Example:C_rate

C. Define the cursor. _ cursor is recommended as the suffix.Example:Emp_cursor

D. Define exceptions. We recommend that you use e _ as the prefix.Example:E_error

Bytes -----------------------------------------------------------------------------------

PL/SQL is based on blocks. Block Classification {mainly contains stored procedures, functions, triggers, and packages}

Block introduction:

As mentioned above,PL/SQL is based on blocks,We compile PL/SQL in writing blocks and simple application functions. We may only need one PL/SQL block to implement these functions. complex functions require multiple blocks, blocks can be nested.

A block consists of three parts: {definition part, execution part, and Exception Handling part}, namely:

Declare (optional)

/* Declaration part-Defines constants and variables. Cursor, exception, and complex data type */

Begin (must be dropped)

/* Execution part --- pl/SQL statements to be executed and SQL statements */

Exception (optional)

/* Handle various running errors */

End;(Must drop)

Compile a simple block first:

Example,

1. SetServeroutputOn;-- Enable output options
3. Begin
4. Dbms_output.put_line ('Hello, world');-- Output hello world
5. End;
6. /

Example,

1. Declare
2. V_name varchar2 (10 );-- Define variables
3. Begin
4. SelectMynameIntoV_nameWherePasswd = & pswd;
5. Dbms_output.put_line ('Here is String concatenation :'| V_name );
6. End;
7. /

Example, and display password:

1. Declare
2. V_name varchar2 (10 );-- Define variables
3. V_psw varchar2 (10 );
4. Begin
5. SelectMyname, passwdIntoV_name, v_pswWherePasswd = & psw;
6. Dbms_output.put_line ('Here is String concatenation :'| V_name |'Password is'| V_psw );
7. End;
8. /

A box will pop up here, so that the input is the psw value, where passwd = & psw

What if the data cannot be queried? Oracle throws an exception,

Here we can use the Exception, Exception

1. Declare
2. V_name varchar2 (10 );-- Define variables
3. V_psw varchar2 (10 );
4. Begin
5. SelectMyname, passwdIntoV_name, v_pswWherePasswd = & psw;
6. Dbms_output.put_line ('Here is String concatenation :'| V_name |'Password is'| V_psw );
7. -- Exception Handling
8. Exception
9. WhenNo_data_foundThen
10. Dbms_output.put_line ('No matched data');
11. End;
12. /

1 2 Next Page