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

PL/SQL

1. Procedures, functions and triggers are written by pl/sql

2. Procedures, functions and triggers are stored in the oracle database

3. Pl/sql is a very powerful procedural language

4. Procedures, functions and triggers can be called in java programs

Pl/sql writing can save a little time is to improve the performance (large amount), java directly calls the process of database storage, parsing time is saved and performance is improved.

Modular design idea-"stored procedure"

Network transmission (sql language written in java program), the process of calling database directly saves the amount of transmission.

Improve security (stored procedures avoid leakage of database information)

Disadvantages:

Poor portability

The basic unit of pl/sql programming is block, through which you can write procedures, functions, triggers and packages.

Let's do the most basic programming.

Case: insert a piece of data into a table

Create or repalce procedure name is: replace means that if the name already exists, replace it

Begin

Insert into test values ('xiaoming','redhat')

End

/

Create procedure create stored procedure keyword

Or repalce: indicates that if the name already exists, replace it

Is: also a keyword

Between the header of the stored procedure definition and the begin is the definition part (defining variable constants, etc.), which is mentioned later

Begin: keyword

End: Terminator

The execution part is between the begin and the end of the end.

The above simple stored procedure is to add a piece of data to a table. Now create a table first.

SQL > create table names (name varchar2 (20), 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 should it be executed? Use the keyword exec or call, as follows

SQL > exec sp_pro1

PL/SQL procedure successfully completed.

Then query the table to see if data has been added

SQL > select * from names

NAME PASSWORD

Xiaoming redhat

If there is an error in the process of writing, you can check the error details through the command show error.

Pl/sql programming is made up of the smallest unit block. Look at the components of the block.

The pl/sql block consists of three parts: the definition part, the execution part, and the exception handling part.

Declare

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

Begin

Execution part, pl/sql statement and sql statement to be executed

Exception

Exception handling section, dealing with all kinds of running errors

The definition section starts with declare, optional

The executive part starts with begin, which is required.

Exception handling part from exception, optional

Let's write the simplest block and output hello world

Open the screen output information of the system before writing, 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.

In the simplest block programming, there is only the execution part, and only one message hello world is output.

Related instructions: dbms_output is the package provided by oracle (similar to java development package), this package contains some processes, put_line is

A process in a dbms_output package (a process in a package)

Example 2: contains the definition part and the execution part

SQL > declare

2 v_name varchar2 (20); renamed to indicate the defined variable, variable name v_name, data type varchar2

3 begin

4 select ename into 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 into v_name from emp where empno=&empno

New 4: select ename into v_name from emp where empno=7788

Information output from SCOTT to screen v_name

PL/SQL procedure successfully completed.

Example 3: contains a definition section, an execution section and an exception handling section

In order to avoid running errors of pl/sql program and improve the robustness of pl/sql, possible errors should be dealt with.

Related note: oracle pre-defined some exceptions, no_data_found is the exception that can not find the data

As in the above example, if you enter an annoying empno number that is not in the emp table, an error will be reported. How to deal with the error? here we define the exception and let him handle it.

SQL > declare

2 v_name varchar2 (20)

3 v_sal number (7 v_sal number 2)

4 begin

5 select ename,sal into v_name,v_sal from emp where empno=&empno

6 dbms_output.put_line (v_name | |'| | v_sal)

7 exception defines the exception keyword exception

8 when no_data_found then when data cannot be queried, take measures to print error

9 dbms_output.put_line ('error')

10 end

11 /

Enter value for empno: 78 is not in empno in the emp table.

Old 5: select ename,sal into v_name,v_sal from emp where empno=&empno

New 5: select ename,sal into v_name,v_sal from emp where empno=78

Error print error

Process

Procedures are used to perform specific operations, and when the procedure is established, you can specify either input parameters (in) or output parameters (out). The data can be transferred with the execution part by using the input parameters in the process, and the data from the execution part can be passed to the application environment by using the output parameters. Use the create procedure command in sqlplus to establish the procedure

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): the parameters in this are equivalent to the formal parameters in programming, 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

SQL > select * from names

NAME PASSWORD

Xiaoming redhat

Xiaobai redhat

Modify the employee of table emp to the salary of smith, 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 the stored procedure to pass the parameter data

SQL > exec sp_pro1 ('SMITH',1200)

PL/SQL procedure successfully completed.

Query change result

SQL > select ename,sal from emp where ename='SMITH'

ENAME SAL

--

SMITH 1200

Procedures are used to perform specific operations, and when the procedure is established, you can specify either input parameters (in) or output parameters (out)

The parameter added in sp_pro1 (name varchar2,pass varchar2) is in by default. If you want to output a stored procedure with a return value, you must add out. See the following example

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

SQL > create or replace procedure sp_pro1 (spno in number,spname out varchar2) is

2 begin

3 select ename into spname from emp where empno=spno

4 end

5 /

Procedure created.

How to call it? you can't just exec procedure_name it here. The way to call it is as follows.

SQL > declare

2 v_name varchar (20); define a variable in which the value returned by the stored procedure is stored

3 begin

4 sp_pro1 (7788)

5 dbms_output.put_line (v_name); print the value of the variable, that is, the returned value

6 end

7 /

SCOTT

PL/SQL procedure successfully completed.

Under what circumstances do you use exec to call and when do you use PLSQL to call stored procedures?

Exec is suitable for calling stored procedures with no return value.

Plsql is suitable for calling stored procedures with return values, no matter how many

Function

Procedures are used to return specific data. When you create a function, you must include the return sentence in the function header and the return statement in the function body.

The data returned, create the function with create function

SQL >-- function case

Structure: create or replace function return.. is, see the following example

SQL > create or replace function sp_fun1 (name varchar2) return number is

2 yearsal number (7par 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 has been created, how can I call it?

SQL > declare

2 v_sal number (7); 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, which is: =

5 dbms_output.put_line (v_sal)

6 end

7 /

14400

PL/SQL procedure successfully completed.

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.