Oracle BASICS (4) pl/SQL

Oracle BASICS (4) pl/SQL

PL/SQL is also a program Language called Procedural Language/SQL ). PL/SQL is an extension of SQL statements in Oracle databases. The features of the programming language are added to the use of common SQL statements. Therefore, PL/SQL organizes data operations and query statements into procedural units of PL/SQL code, implement complex functions or computing programming languages through logical judgment, loops, and other operations.

In summary, it is an extension of the SQL language. SQL statements, variables and constants, condition statements, cyclic statements, and exceptions are used to handle various errors!

Functions of PL/SQL

PL/SQL can be used to write programs with many advanced functions. Although multiple SQL statements can also achieve the same function, PL/SQL has more obvious advantages:

Explain makes the functions of a group of SQL statements more modular;

Pipeline adopts the structure of Procedural language control programs;

The supervisor can automatically handle errors in the program so that the program will not be interrupted when an error occurs;

Slave has good portability and can be transplanted to another Oracle database;

Shards are integrated into the database, making calls faster;

⒍ Reduces Network Interactions and improves program performance.

When multiple SQL statements are used to implement the function, each statement must be passed on the client and server, and the execution results of each statement must also be interacted on the network, it consumes a lot of network bandwidth and consumes a lot of time for network transmission. The results transmitted in the network are usually intermediate results, rather than what we care about.

The PL/SQL program is used because the program code is stored in the database, and the analysis and execution of the program are completely performed in the database, all you need to do is run the PL/SQL command on the client side. After the database receives the command, the whole PL/SQL program is executed within the database, the final execution result is returned to the user. During the entire process, only a small amount of data is transmitted over the network, reducing the time required for network transmission. Therefore, the overall program execution performance will be significantly improved.

Basic pl/SQL

Next, we will introduce the compilation process based on blocks, functions, packages, and three advanced control statements of pl/SQL. Next, we will introduce the views, triggers, and paging stored procedures.

Block Structure

Pl/SQL block consists of definition, execution, and Exception Handling

Declear defines constants, changes, cursors, exceptions, and complex data types

Begin execution

Exception Handling

End;

For example:

Declare

V_ename varchar2 (5); defines string variables

V_sal number (7,2 );

Begin

Select ename, sal into v_ename, v_sal from emp where empno = & no;

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

-- Exception Handling

Exception

When no_date_found thendbms_output.put_line ('Friend, input error ');

End

Use Sqlplus development tools: Pl/SQL develper independent tools

(1) 1. Create a simple table

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

2. creation process

Create or replace procedure sp_prol is

Begin

-- Execution part

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

End;

3. Call

Exec process name (parameter)

Call process name (parameter)

 

Stored Procedure without return values

Create procedure sp_pro3 (spnamevarchar2, newSal number) is

Begin

-- Run the command to modify the salary according to the user name.

Updateemp set sal = newsal where ename = spName;

End;

(2) Functions

Createfunction sp_fun2 (spName varchar2) return

Number isyearSal number (7, 2 );

Begin

-- Execution part

Selectsal * 12 + nvl (comm, 0) * 12 into yearSal from emp where ename = spName;

ReturnyearSal;

End;

Call a function

SQL varincome number

Call annual_income ('Scott ') from into: income;

SQL> printincome

In Java, rs. getInt (1) is used to obtain the returned result.

SQL> showerorr -- Display Error

(3) package

-- Create a package

-- Create a package sp_package

_ Declares that there is a process update_sal in the package.

--- Declare a function

Createpackage sp_packge is

Procedureupdate_sal (name varchar2, newsal number );

Functionannual_income (name varchar2) reture number;

End;

Create a package

Createpackage body sp_package is

Procedure

Function

Begin

Select

Return

End

End

Call

SQL> call sp_package.function (procedule)

Advanced Pl/SQL

Three condition branches

If then

Create or replace procedure sp_pro6 (spName varchar2) is

-- Definition

V_sal smp. sal % type;

Begin

-- Execute

Select sal into v_sal from empwhere ename = spName;

-- Judgment

If v_sal <2000 then

Update empset sal = sal-sal * 10% where ename = spName;

End if;

End;

Call

SQL> execsp_pro6 ('Scott ') scott is the user name

Dual-condition BranchIf-then-else

Create or replace proceduresp_pro6 (spName varchar2) is

--Definition

V_sal smp. sal % type;

Begin

-- Execute

Select sal into v_sal from empwhere ename = spName;

-- Judgment

If v_com <> 0 then

Update empset comm = comm + 100 where ename = spName;

Else

Update empset comm = comm + 200 where ename = spName;

End if;

End;

Call

SQL> execsp_pro6 ('Scott ') scott is the user name

Multi-condition branch if-then-elseif -- else

If the employee's position is president, his salary will be increased by 1000. If the employee's position is manager, his salary will be increased by 500, and the employee in other positions will be increased by 200.

Create orreplace procedure sp_pro6 (spNo number) is

-- Definition

V_job emp. job % type;

Begin

-- Execute

Selectjob into v_job from emp where empno = spNo;

If v_job = 'President 'then

Update emp set sal = sal + 1000 where empno = spNo;

Elseif v_job = 'manager' then

Update empset sal = sal + 500 where empno-spNo;

Else

Update empset sal = sal + 200 where empno = spNo;

End if;

End;

Call SQL> Execsp_pro6 (7839)

Loop statement

LoopThe End loop must be executed at least once. The End loop must be executed cyclically before judgment.

Create or replace proceduresp_pro6 (spName varchar2) is

-- Definition: = indicates a value.

V_num number: = 1;

Begin

Loop

Insert into users values (v_num, spName );

-- Determine whether to exit the loop

Exit when v_num = 10;

-- Auto-Increment

V_num: = v_num + 1

End loop;

End;

Loop statementWhile Loop

Judge first and then loop

Create or replace proceduresp_pro6 (spName varchar2) is

-- Definition: = indicates a value.

V_num number: = 11;

Begin

While v_num <= 20 loop

-- Execute

Insert into users values (v_num, spName );

-- Auto-Increment

V_num: = v_num + 1

End loop;

End;

For Loop

Begin

For I inreverse 1 .... 10 loop

Insert into users values (I, 'shunping ');

End loop;

End; not recommended

Sequential Control statement

GotoIt is recommended that the number of nested loops not exceed three layers.

It is used to jump to a specific label to execute the statement. Note that it is used to increase complexity and readability by using go.

Declare

I int: = 1;

Begin

Loop

Dbms_output.put_line ('output I = '| I );

If I = 10 then

Got end_loop;

End if;

I: = I + 1;

End loop;

< >

-- <> Goto label

Dbms_output.put_line ('loop termination ');

End;

Null

Will not perform any operation, pass the control to the next sentence

Improve readability

Declare

V_salemp.sal % type;

V_enameemp.ename % type;

Begin

Select ename, sal into v_ename, v_val

From empwhere empno = % no;

If v_sal <3000 then

Updated empsel comm = sal * 0.1 where ename = v_ename;

Else

Null;

End if;

End;

Pl/SQL is out of SQL, so a large part of it follows SQL. Before, SQL server has not been so deeply touched. This time, more things have been learned in pl/SQL, this way, you can view the problem from different perspectives and then fully understand it.