Shulou(Shulou.com)06/01 Report--

PLSQL Programming

1. Environmental construction

(1)Create a Storage Tablespace

SQL> conn /as sysdba

Connected.

SQL> create tablespace plsql datafile '/u01/oracle/oradata/ORCL/plsql01.dbf' size 1G;

Tablespace created.

(2)Creating PLSQL Users

SQL> create user plsql identified by plsql default tablespace plsql;

User created.

(3)Authorize PLSQL users

SQL> grant connect,resource to plsql;

Grant succeeded.

(4)execute scripts

Must connect to plsql user first

SQL> conn plsql/plsql

Connected.

- ----------Choose any method to put the data objects needed for the experiment, such as: Table... Add to plsql user----------------------

SQL>@D:\baitu\data. sql--Script placed on client, executed using client tools

SQL>@/u 01/data.sql--The script is placed on the server, and the absolute path of the script in the server sqlplus is directly

(5)Create a PLSQL connection in PLSQL developer

##########################################################################################################################################################################################################################################################

PL/SQL -- procedure language/struncture query langure

Functions and advantages:

(1)Direct interaction with the database engine improves performance, but performance is affected in cases of large concurrency.

(2)Procedural query language with object-oriented functionality

(3)Tight integration with SQL statements

(4)It can be developed modularly.

2.1 basic concepts

(1)Three code blocks

A Anonymous block (code segment without name, compiled in database, temporarily executed)

declare

declare variables

begin--program starts execution

part of program execution

exception--exception handling

exception handling section

end;

B named blocks: stored procedure (store procedure = SP), function (function = FUN), package (package-PKG)

create or replace procedure xxxx (parameter list) is

declare variables

begin

procedural subject

exception

exception handling section

end;

C block: rarely used

(2)variables

A String, Number, Date, Time, Large Object, Long Text

B Composite data types: cursors, records, sets, row variables, exceptions, index-by tables

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

(3)annotation

A Single line comment--xxxxxx

B Multiline comments/* xxxx */

(4)nested block

declare

begin

- -----

begin

exception

end;

-------------------------------------------------

exception

- -----

begin

exception

end;

-------------------------------------------------

end;

2.2 program structure

(1)conditional constructs

if xxxx then

action;

elsif xxxxx then

action;

else

action;

end if;

(2)loop structure

A loop

loop

exit when (condition for loop exit)

program body

end loop;

B while loop

while (loop condition) loop

program body

end loop;

C for loop

for idx in loop

program body

end loop;

(3) case when

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.