Plsql Environment and Introduction: A brief introduction to the construction and plsql of the environment

Plsql Programming

1. Construction of the environment

(1) Create a storage table space

Sql> Conn/as SYSDBA
Connected.

sql> Create tablespace plsql datafile '/u01/oracle/oradata/orcl/plsql01.dbf ' size 1G;

Tablespace created.

(2) Create Plsql user
Sql> create user Plsql identified by plsql default Tablespace plsql;

User created.

(3) Authorization to Plsql users
Sql> Grant Connect,resource to Plsql;

Grant succeeded.

(4) Execute script

Must connect to Plsql user first
Sql> Conn Plsql/plsql
Connected.

---------------Choose any of the data objects that the experiment needs, such as: Table ... Add to Plsql user--------------------

Sql> @d:\baitu\data.sql--script is placed on the client and executed using client tools

Sql> @/u01/data.sql--The absolute path of scripts placed in the server, direct server Sqlplus

(5) Create a plsql connection in Plsql Developer

Brief introduction of ########################################################################################## #2. Plsql

PL/SQL--procedure language/struncture query Langure

Functions and Advantages:
(1) Direct interaction with the database engine to improve performance, but, in case of large concurrency, performance has an impact
(2) Procedural query Language with object-oriented function
(3) is tightly integrated with SQL statements
(4) can be modular development.

2.1 Basic Concepts

(1) Three blocks of code

A Anonymous block (No name, no store compiled in database, temporary execution of code snippet)

Declare
declaring variables
Begin-Program begins execution
Part of program execution
Exception--Exception handling
Exception Handling Section
End

B named blocks: Stored Procedures (store procedure =SP), Functions (Function=fun), Packages (package-pkg)

Create or Replace procedure xxxx (formal parameter list) is
declaring variables
Begin
Program Body
exception
Exception Handling Section
End

C tag block: Rarely used

(2) Variables

A string, number, date, time, large object, long text
B Composite data types: Cursors, records, collections, row variables, exceptions, index-by tables

Note: Varchar2 as a field of the table, the longest length is 4000, and as a variable, the length can reach 32767

(3) Notes

A single-line comment--xxxxxxxx
B Multiline Comment/* xxxxxx */

(4) Nested blocks

Declare

Begin
---------------------------nested block 1---------------
Begin

exception

End
-------------------------------------------------
exception
---------------------------Nested block 2---------------
Begin

exception

End
-------------------------------------------------
End

2.2 Structure of the program

(1) Conditional structure

If XXXX Then
Action
elsif xxxxx Then
Action
Else
Action
End If;

(2) Cycle structure

A Loop Loop

Loop
Exit when (conditions for loop exit)
Program Body
End Loop;

B while Loop loop

while (loop condition) loop
Program Body
End Loop;

C for Loop Loop

For IDX in (result set or cursor) loop
Program Body
End Loop;

(3) Case

Plsql Environment and Introduction: A brief introduction to the construction and plsql of the environment