PL/SQL Programming basics

PL/SQL

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

2, procedures, functions, triggers are stored in the Oracle database

3. PL/SQL is a very powerful process language

4, procedures, functions, triggers can be called in a Java program

PL/SQL Writing can save a little time is to improve the performance (volume), Java directly call the database storage process, the resolution time is saved, improve the performance

Modular design Ideas-----"stored procedures

Network transport (SQL language written in Java programs), the process of directly invoking a database saves the amount of traffic

Increased security (stored procedures avoid leakage of database information)

Disadvantages:

Bad transplant.

The basic unit of PL/SQL programming is a block that can be programmed to write out processes, functions, triggers, packages

The following is a basic programming

Case: Inserting a piece of data into a table

The Create or Repalce procedure name Is:replace indicates that if the name already exists, replace the

Begin

INSERT into test values (' xiaoming ', ' Redhat ')

End

/

CREATE procedure creating stored procedure keywords

Or Repalce: Indicates that if the name already exists, replace

Is: also a key word

The definition section between the header and begin of a stored procedure definition (defining variable constants, and so on) is mentioned later

Begin: Keywords

End: Terminator

Between Begin and end is the execution part

A simple stored procedure above is to add a piece of data to a table, and now create a table

Sql> CREATE table names (name varchar2, password varchar2 (30));

Table created.

Then add data to it by writing a stored procedure

sql> Create or replace procedure Sp_pro1 is

2 begin

3 INSERT into names values (' Xiaoming ', ' redhat ');

4 End;

5/

Procedure created.

The stored procedure has been established, how to execute it? Use the keyword EXEC or call, as follows

sql> exec Sp_pro1;

PL/SQL procedure successfully completed.

Then query the table to see if the data is added

Sql> select * from names;

NAME PASSWORD

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

Xiaoming Redhat

When writing a process that is wrong, you can view the error details by using the show Error command

PL/SQL Programming is made up of the smallest block of blocks to see the components of the block

PL/SQL blocks are made up of three parts: Definition section, execution section, exception handling section

Declare

Definition section, defining constants, variables, cursors, exceptions, complex data types

Begin

Execute section, PL/SQL statements to execute

exception

Exception handling section, handling various errors of the run

The definition section starts with declare, and the optional

The execution section begins with the Begin and must be selected

Exception handling section from exception, optional

Write the simplest block below and output Hello World

Write the screen output information of the system before opening it, otherwise you won't see the effect

Sql> set serveroutput on;

Sql> begin

2 dbms_output.put_line (' Hello World ');

3 END;

4/

Hello World

PL/SQL procedure successfully completed.

The simplest block programming, only the execution part, and only output a message Hello world.

Description: Dbms_output is a package provided by Oracle (Java-like development package) that contains a number of processes, Put_Line is

A process of dbms_output package (a process inside a package)

Example 2: Contains the definition section and the execution part

Sql> Declare

2 v_name varchar2 (20); A row represents the defined variable, the variable name V_name, and the data type is VARCHAR2

3 begin

4 Select ename to V_name from EMP where empno=&empno; & Output by keyboard

5 Dbms_output.put_line (V_name);

6 end;

7/

Enter value for empno:7788

Old 4:select ename to v_name from EMP where empno=&empno;

New 4:select ename to v_name from EMP where empno=7788;

SCOTT output to the screen information v_name

PL/SQL procedure successfully completed.

Example 3: Contains the definition section, the execution section, and the Exception handling section

In order to avoid the operation error of PL/SQL program and improve the robustness of PL/SQL, the possible errors should be handled.

Description: Oracle has pre-defined some exceptions, No_data_found is the exception to the data that is not found

As the above example, if the input annoying is not the EMP table in the empno number, then will be an error, the error is how to deal with it, here is the definition of the exception, handed him to deal with

Sql> Declare

2 v_name varchar2 (20);

3 v_sal number (7,2);

4 begin

5 Select Ename,sal to V_name,v_sal from EMP where empno=&empno;

6 Dbms_output.put_line (v_name| | ' ' | | V_sal);

7 exception Define exception keyword exception

8 when No_data_found then take action to print error when the data is not queried

9 dbms_output.put_line (' Error ');

Ten end;

11/

Enter value for empno:78 78 is not in the EMP table empno Good

Old 5:select ename,sal to v_name,v_sal from EMP where empno=&empno;

New 5:select ename,sal to v_name,v_sal from EMP where empno=78;

Error print Error

Process

A procedure is used to perform a specific operation, and when the procedure is established, you can specify either an input parameter (in) or an output parameter (out). You can pass data to the execution part by using input parameters in the process, and you can pass the execution part of the data to the application environment by using the output parameters. Use the CREATE PROCEDURE command in Sqlplus to establish the process

Sql>--Process writing

sql> Create or Replace procedure Sp_pro1 (name Varchar2,pass varchar2) is

2 begin

3 INSERT into names values (Name,pass);

4 End;

5/

Procedure created.

Sp_pro1 (name Varchar2,pass varchar2): This parameter is equivalent to the formal parameter inside the program, the data passed

Then call the stored procedure

sql> exec sp_pro1 (' Xiaobai ', ' redhat ');

PL/SQL procedure successfully completed.

Then query whether the data has been inserted into the

Sql> select * from names;

NAME PASSWORD

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

Xiaoming Redhat

Xiaobai Redhat

Modify Table EMP's employee is Smith's salary, write the stored procedure implementation

sql> Create or Replace procedure Sp_pro1 (name Varchar2,v_sal number) is

2 begin

3 update emp set sal=v_sal where Ename=name;

4 End;

5/

Procedure created.

Call stored procedure, pass-through parameter data

sql> exec sp_pro1 (' SMITH ', 1200);

PL/SQL procedure successfully completed.

Query change Results

Sql> Select Ename,sal from emp where ename= ' SMITH ';

Ename SAL

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

SMITH 1200

A procedure is used to perform a specific action when the procedure is established, either by specifying an input parameter (in) or by specifying an output parameter (out)

Sp_pro1 (name Varchar2,pass varchar2) This adds the parameter to the default is in, if you want to output a stored procedure with a return value must be added out, see the following example

Given a empno employee number, return the employee name, write a stored procedure

sql> Create or Replace procedure Sp_pro1 (spno in Number,spname off varchar2) is

2 begin

3 Select ename into Spname from EMP where empno=spno;

4 End;

5/

Procedure created.

How to call, here can not directly exec procedure_name This, call the following way

Sql> Declare

2 v_name varchar (20); Define a variable to deposit the value returned by the stored procedure in this variable

3 begin

4 Sp_pro1 (7788,v_name);

5 Dbms_output.put_line (V_name); Prints the value of the variable, which is the value returned

6 end;

7/

SCOTT

PL/SQL procedure successfully completed.

Under what circumstances do I call a stored procedure with Plsql, using exec?

exec is appropriate for calling stored procedures with no return value

Plsql is suitable for calling stored procedures with a return value, regardless of how many

Function

Procedures are used to return specific data, and when a function is established, the function header must contain a return sentence, and the function body must contain a return statement

The returned data, creating functions with the CREATE function

Sql>--function case

Structure: Create or replace function return. is, see the example below

sql> Create or Replace function sp_fun1 (name VARCHAR2) return number is

2 yearsal number (7,2);

3 begin

4 Select sal*12 into Yearsal from EMP where ename=name;

5 return yearsal; Defines what needs to be returned.

6 end;

7/

Function created.

The function is created, how do I call it?

Sql> Declare

2 v_sal number (7,2); Define a variable to receive the value returned by the function

3 begin

4 v_sal:=sp_fun1 (' SMITH '); Assign the value returned by the function to v_sal with the value: =

5 Dbms_output.put_line (v_sal);

6 end;

7/

14400

PL/SQL procedure successfully completed.

PL/SQL Programming basics