2015/8/17 Notes finishing 12th Chapter PL/SQL Programming introduction 1 block Process function

The Oracle database contains a procedural programming language pl/sql,pl/sql is an extension of Oracle to the standard database language.

　　I. advantages of PL/SQL

PL/SQLnot a standalone product, he is an integrated intoORACLEservers, andORACLEThe technology in the tool, you can putPL/SQLasORACLEan engine within the server,SQLThe statement performer handles a singleSQLstatement,PL/SQLEngine processingPL/SQLThe program block. WhenPL/SQLThe program blockPL/SQLwhen the engine is processed,ORACLEin the serverSQLStatement Executor processingPL/SQLIn the program blockSQLstatement.

PL/SQL statement -"ORACLE Server -" Decomposition code-PL/SQL engine

--"SQL engine "

for SQL , ORACLE Each article must be processed at the same time SQL statement, which in the context of the network means that each individual call must be Oracle server processing, which takes up a lot of server time and causes the network to be congested. and PL/SQL is to send the entire statement block to the server, which reduces network congestion .

　　two. PL/SQL block structure
　　 PL/SQLis a block structure of the language, composed the unit of a PL/SQL program is a logical blockOnePL/SQLThe program contains one or more logical blocks, each of which can be divided into three parts. As with other languages, variables must be declared before they are used,PL/SQLprovides a separate section specifically for handling exceptions, as described belowPL/SQLdifferent parts of the block:

1.Declarations Section(Declaration section)
The Declarations section contains the data types and initial values of variables and constants. This section is made up of keywordsDECLARETo start, if you don't need to declare variables or constants, you can ignore this part;It should be stated that the declaration of the cursor is also in this section.
2.Executive Section(executable section)
The Executive section isPL/SQLpart of the instruction in the block, by the keywordBEGINat the beginning, all the executable statements are placed in this section, and the otherPL/SQLblocks can also be placed in this section.
3.Exception Handling Section(Exception section)
This section is optional, dealing with exceptions or errors in this section, and we'll take a detailed discussion of exception handling.

PL/SQLblock Syntax

[DECLARE] --declaration statements ; BEGIN --executable statements; [EXCEPTION] --exception statements; END;

pl/sql Every statement in a block must end with a semicolon Span style= "font-family:??;" >sql The statement can make multiple lines, but semicolons represent the end of the statement. A row can have multiple sql statements, separated by semicolons. Each pl/sql block by begin or declare end end. Note by -- mark.

Exercise : define three variables are rectangle v_width, V_height,v_area height and area have initial value : 2,6, to V_width is evaluated and output ( consider an exception with a height of 0 ).

DECLARE

V_width number;

V_height number: = 2;

V_area Number: = 6;

BEGIN

V_width: = V_area/v_height;

Dbms_output. Put_Line (' v_width: ' | | v_width);

END;

/
　　Three. named blocks and anonymous blocks for PL/SQL blocks

　　PL/SQL program blocks: named blocks and anonymous blocks of programs.
Named block: can appear in other PL/SQL program block Declarations section, this is more obvious is the subroutine, the subroutine can be in the execution part of the reference, can also be referenced in the Exception handling section.
　　PL/SQL blocks can be compiled independently and stored in a database, and any application connected to the database can access these stored PL/SQL blocks.

    ORACLE Four types of storage available Program:

.  function

.&NBSP, procedure

.  Span style= "font-family: the song Body;" > package

.  trigger
The
1. Function

function is a named pl/sql program block. The function accepts 0 or more input parameters, has a return value, and the data type of the return value is defined when the function is created. The syntax for defining a function is as follows:

create or replace function& nbsp;name [(parameter [in| Out| In out] datatype[,parameter,...])  return datatypes is [local declarations] BEGIN execute statements [ EXCEPTION Exception handlers] End [name]

2. Procedure

Stored procedure is a pl/sql block, accept 0 or more parameters as input (output) , or both as input and output (INOUT) , unlike functions, The stored procedure does not return a value of , stored procedure cannot be sql statements are used directly, only through the call keyword/ execut command or pl/sql block Internal call , the syntax for defining a stored procedure is as follows:

CREATE [OR REPLACE] PROCEDURE name [(Parameter [in | Out | INOUT] datatype [, parameter,...])] Is [Local declarations] BEGIN Execute statements [EXCEPTION Exception handlers] END [Name]

In: You must assign an initial value to the in parameter of the procedure, function , only incoming, not outgoing, and cannot be re-assigned in the code. (pass value)

Out: only outgoing, not incoming, no assignment received (address)

In -out: Can be input, change its value, and output.

In the latter two cases, only the existing variables are allowed to be entered.

Example fan:

Examples of processes:

CREATE OR REPLACE PROCEDURE Test (v_ height number, V_area number) is

V_width number;

BEGIN

V_width: = V_area/v_height;

Dbms_output. Put_Line (' v_width: ' | | v_width);

END;

/

CREATE OR REPLACE PROCEDURE Ptest (v_ename in Emp.ename%type) is

V_empno Emp.empno%type;

BEGIN

SELECT empno to v_empno from emp WHERE ename = v_ename;

V_ename: = 'SOCTT23';

Dbms_output. Put_Line (' Enmpno ' | | v_empno| | ' is ' | | V_ename);

END Ptest;

CREATE OR REPLACE PROCEDURE Ptest2 (v_out in number) is

BEGIN

Dbms_output. Put_Line (NVL (v_out,0));

--V_out: = 10;

Dbms_output. Put_Line (v_out);

END;

If a compilation error is displayed, use show error to show where and why the error occurred

View stored Procedures : SELECT * from User_procedures;

Delete : Drop PROCEDURE procedure name

Non-parametric PROCEDURE:

CREATE OR REPLACE PROCEDURE Test is

V_width number;

BEGIN

V_width: = 6/2;

Dbms_output. Put_Line (' v_width: ' | | v_width);

END;

/

Example of a function: asking for the radius of the input circle, to calculate the area

CREATE OR REPLACE FUNCTION circle_area (P_radius in number) RETURN number is

V_PI number: = 3.1415926;

V_area number;

BEGIN

V_area: = v_pi* POWER (p_radius,2);

RETURN V_area;

END Circle_area;

Call: Note that you cannot use the form of the EXEC function name!

The first way:

Sql> Select Circle_area (3) from dual;

Circle_area (3)

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

28.2743334

The second way:

Sql> Declare

2 V_num number;

3 begin

4 V_num: = Circle_area (3);

5 Dbms_output.put_line (V_num);

6 end;

7/

28.2743334

The PL/SQL block can also receive input from the user, for example: The user is now asked to enter an employee number, and then queries the employee's name based on what is entered.

• The user's input information is completed using "&".

DECLARE

ENO number;

En VARCHAR2 (30);

BEGIN--The information entered is kept in Eno.

Eno: = &no; --after which the database is queried based on the value of ENO

SELECT first_name to en from customers WHERE Customer_id=eno;

Dbms_output.put_line (' number: ' | | eno| | ' The employee's name is: ' | | EN);

EXCEPTION

When No_data_found

Then Dbms_output.put_line (' No such customer ');

END;

/

2015/8/17 Notes finishing 12th Chapter PL/SQL Programming introduction 1 block Process function