Admin: 1 версия

2010-05-26T10:08:23Z

1 версия

Новая страница

== An example of an anonymous block.==



<source lang="sql">
DECLARE
hundreds_counter NUMBER(1,-2);
BEGIN
hundreds_counter := 100;
LOOP
DBMS_OUTPUT.PUT_LINE(hundreds_counter);
hundreds_counter := hundreds_counter + 100;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE("That is as high as you can go.");
END;
/</source>

== Anonymous blocks can be nested in the procedure and exception blocks in as many levels as you want==



<source lang="sql">
<<MAIN>>
declare
...
Declaration section
...
begin
...
Procedural section
...
<<SUB1>>
declare
...
begin
...
end;
...
exception
...
end;</source>

You can label all blocks including nested ones by using identifiers enclosed in << >>.
This notation allows programmers to reference elements of different blocks.

== Anonymous Block Structure==

An anonymous block does not form the body of a procedure, function, or trigger.
Anonymous blocks can be used inline as part of a SQL*Plus script.
Anonymous blocks can also be nested inside procedure and function blocks for error handling.
The Syntax for PL/SQL Anonymous Blocks



<source lang="sql">
[DECLARE variable_declarations]
BEGIN
program_code
[EXCEPTION error_handling_code]
END;</source>

error_handling_code controls branches in the event of an error.
The keyword EXCEPTION begins the portion of the block that contains exception-handling code.
The exception-handling portion of a block is optional.
If the exception-handling portion is present, any runtime error or exception will cause program control to branch to this part of the block.

== Assigning values to variables==



<source lang="sql">
SQL>
SQL>
SQL> declare
2 v_length NUMBER DEFAULT 5;
3 v_height NUMBER := 4;
4 v_width NUMBER;
5 v_volume NUMBER;
6 v_min NUMBER;
7 begin
8 v_width := 3;
9 v_volume:= v_length*v_width*v_height;
10 v_min := least(v_length,v_width,v_height);
11 end;
12 /
PL/SQL procedure successfully completed.
SQL></source>

== Assign SQL query results to PL/SQL variables==



<source lang="sql">
SQL>
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /

ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- -------------------- -------------------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL>
SQL> declare
2 v_name VARCHAR2(256);
3 begin
4 select first_name
5 into v_name
6 from employee
7 where id=7;
8 DBMS_OUTPUT.put_line("v_name:"||v_name);
9
10 end;
11 /
v_name:David
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL></source>

== Character and string literals in the Oracle world are enclosed by single quotes==



<source lang="sql">
SQL>
SQL> declare
2 v_char CHAR(1):="H";
3 v_text1 VARCHAR2(10) :="Hello";
4 v_text2 CHAR(1) :=""; -- the same as NULL
5 begin
6 NULL;
7 End;
8 /
PL/SQL procedure successfully completed.
SQL></source>

== Comments==

PL/SQL allows two types of comments: single and multi-line.
Single-line comments start with a delimiter -- and go to the end of the line, as shown here:



<source lang="sql">
declare
here you should declare variables,
constants, etc.
...
begin
-- here you place your code
...
end;
/</source>

== Declaring a Variable by Reference==



<source lang="sql">
declare
variable_name table.column%TYPE;
variable_name2 variable_name%TYPE;
variable_row table%ROWTYPE;
begin
The following code shows some examples of defining datatypes:

SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /

ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- -------------------- -------------------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL>
SQL> declare
2 v_empno1 employee.id%TYPE;
3 v_empno2 v_empno1%TYPE;
4 v_emp_rec employee%ROWTYPE;
5 begin
6 NULL;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>
SQL></source>

== Declaring variables==

In PL/SQL, variables must be included in the declaration block before they can be used.
There are a number of ways to declare a variable.
The most common way is by using a direct declaration, as shown here:



<source lang="sql">
declare
variable_name [constant] DATATYPE [DEFAULT value |DEFAULT NULL];
begin
...</source>

== Delimiters==

A delimiter is a simple or compound symbol that has a special meaning in PL/ SQL.
Delimiter
Description
+, -, *, /
Addition, subtraction/negation, multiplication, division
%
Attribute indicator
"
Character string delimiter
.
Component selector
(,)
Expression or list delimiter
:
Host variable indicator
,
Item separator
"
Quoted identifier delimiter
=
Relational operator
@
Remote access indicator
;
Statement terminator
:=
Assignment operator
=>
Association operator
||
Concatenation operator
**
Exponentiation operator
<<, >>
Label delimiter (begin and end)
/*, */
Multi-line comment delimiter (begin and end)
--
Single-line comment indicator
..
Range operator
<, >, <=, >=
Relational operators
<>, "=, ~=, ^=
Different version of NOT EQUAL

== Each complete line of the PL/SQL code must end with a semicolon (;).==

To run the code, type / at the beginning of the first blank line after the last line of the code.



<source lang="sql">
SQL> set SERVEROUTPUT ON
SQL> declare
2 v_string varchar2(256):="Hello, World!";
3 begin
4 dbms_output.put_line(v_string);
5 end;
6 /
Hello, World!
PL/SQL procedure successfully completed.
SQL></source>

== Examples of Integer and Real Literals==



<source lang="sql">
SQL>
SQL>
SQL> declare
2 v_int1 BINARY_INTEGER :=5; -- integer
3 v_int2 BINARY_INTEGER :=-5; -- integer
4 v_int3 BINARY_INTEGER :=0; -- integer
5 v_int4 BINARY_INTEGER :=+5; -- integer
6
7 v_real1 NUMBER :=1.0; -- real
8 v_real2 NUMBER :=1.; -- real
9 v_real3 NUMBER :=-7.113; -- real
10 v_real4 NUMBER :=0.2; -- real
11 v_real5 NUMBER :=.3; -- real
12 v_real6 NUMBER :=0.1; -- real
13 v_real7 NUMBER :=2/6; -- real
14 begin
15 NULL;
16 end;
17 /
PL/SQL procedure successfully completed.
SQL></source>

== Literals as variable values==

Literals are explicit numeric, character, string, or Boolean values not represented by an identifier.
Two types of numeric literals exist:
<OL><LI>Integer literals represent optionally signed numeric values without decimal points.</LI><LI>Real literals represent optionally signed whole or fractional numbers with decimal points.</LI></OL>
24. 1. Introduction
24. 1. 1.
<A href="/Tutorial/Oracle/0480__PL-SQL-Programming/Writingasimpleprogram.htm">Writing a simple program</a>
24. 1. 2.
<A href="/Tutorial/Oracle/0480__PL-SQL-Programming/EachcompletelineofthePLSQLcodemustendwithasemicolon.htm">Each complete line of the PL/SQL code must end with a semicolon (;).</a>
24. 1. 3.
<A href="/Tutorial/Oracle/0480__PL-SQL-Programming/AnonymousBlockStructure.htm">Anonymous Block Structure</a>
24. 1. 4.
<A href="/Tutorial/Oracle/0480__PL-SQL-Programming/Anexampleofananonymousblock.htm">An example of an anonymous block.</a>
24. 1. 5.
<A href="/Tutorial/Oracle/0480__PL-SQL-Programming/Anonymousblockscanbenestedintheprocedureandexceptionblocksinasmanylevelsasyouwant.htm">Anonymous blocks can be nested in the procedure and exception blocks in as many levels as you want</a>
24. 1. 6.
<A href="/Tutorial/Oracle/0480__PL-SQL-Programming/TheLexicalSetofElements.htm">The Lexical Set of Elements</a>
24. 1. 7.
<A href="/Tutorial/Oracle/0480__PL-SQL-Programming/Delimiters.htm">Delimiters</a>
24. 1. 8.
<A href="/Tutorial/Oracle/0480__PL-SQL-Programming/Comments.htm">Comments</a>
24. 1. 9.
<A href="/Tutorial/Oracle/0480__PL-SQL-Programming/Multilinecommentsstartwithandendwith.htm">Multi-line comments start with /* and end with */.</a>
24. 1. 10.
<A href="/Tutorial/Oracle/0480__PL-SQL-Programming/Declaringvariables.htm">Declaring variables</a>
24. 1. 11.
<A href="/Tutorial/Oracle/0480__PL-SQL-Programming/DeclaringaVariablebyReference.htm">Declaring a Variable by Reference</a>
24. 1. 12.
<A href="/Tutorial/Oracle/0480__PL-SQL-Programming/Therearesomerestrictionsonthedeclarationofvariables.htm">There are some restrictions on the declaration of variables:</a>
24. 1. 13.
<A href="/Tutorial/Oracle/0480__PL-SQL-Programming/Assigningvaluestovariables.htm">Assigning values to variables</a>
24. 1. 14.
<A href="/Tutorial/Oracle/0480__PL-SQL-Programming/AssignSQLqueryresultstoPLSQLvariables.htm">Assign SQL query results to PL/SQL variables</a>
24. 1. 15.
Literals as variable values
24. 1. 16.
<A href="/Tutorial/Oracle/0480__PL-SQL-Programming/ExamplesofIntegerandRealLiterals.htm">Examples of Integer and Real Literals</a>
24. 1. 17.
<A href="/Tutorial/Oracle/0480__PL-SQL-Programming/Numericliteralscannotcontaindollarsignsorcommasbuttheycanbewritteninscientificnotation.htm">Numeric literals cannot contain dollar signs or commas, but they can be written in scientific notation</a>
24. 1. 18.
<A href="/Tutorial/Oracle/0480__PL-SQL-Programming/CharacterandstringliteralsintheOracleworldareenclosedbysinglequotes.htm">Character and string literals in the Oracle world are enclosed by single quotes</a>

== Multi-line comments start with /* and end with */.==

These delimiters may span as many lines as needed. An example is shown here:



<source lang="sql">
declare
/* This code is written by sqle.ru. Dec 20 2007 */
...
begin
...
end;</source>

== Numeric literals cannot contain dollar signs or commas, but they can be written in scientific notation==



<source lang="sql">
SQL>
SQL> declare
2 -- v_real1 NUMBER:=$123456.00; -- INVALID
3 -- v_real2 NUMBER:=123,456.00; -- INVALID
4 v_real3 NUMBER:=5e10; -- VALID
5 v_real3 NUMBER:=5e-3; -- VALID
6 begin
7 NULL;
8 end;
9 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL></source>

Oracle supports scientific notation for numbers between 1E-130 and 1E+126, where E stands for "times ten to the power of".

== The Lexical Set of Elements==

<OL><LI>The PL/SQL lexical set of elements consists of identifiers, delimiters, literals, and comments.</LI><LI>Identifiers are names of PL/SQL program items and units.</LI><LI>These items and units could be of different kinds - constants, variables, exceptions, cursors, cursor variables, subprograms, and packages.</LI><LI>An identifier cannot exceed 30 characters.</LI><LI>An identifier consists of a letter optionally followed by more letters, numerals, dollar signs, underscores, and number signs.</LI><LI>By default, identifiers are not case sensitive.</LI><LI>Identifiers may not be the same as reserved words, so you cannot use the word end as a variable name.</LI></OL>
24. 1. Introduction
24. 1. 1.
<A href="/Tutorial/Oracle/0480__PL-SQL-Programming/Writingasimpleprogram.htm">Writing a simple program</a>
24. 1. 2.
<A href="/Tutorial/Oracle/0480__PL-SQL-Programming/EachcompletelineofthePLSQLcodemustendwithasemicolon.htm">Each complete line of the PL/SQL code must end with a semicolon (;).</a>
24. 1. 3.
<A href="/Tutorial/Oracle/0480__PL-SQL-Programming/AnonymousBlockStructure.htm">Anonymous Block Structure</a>
24. 1. 4.
<A href="/Tutorial/Oracle/0480__PL-SQL-Programming/Anexampleofananonymousblock.htm">An example of an anonymous block.</a>
24. 1. 5.
<A href="/Tutorial/Oracle/0480__PL-SQL-Programming/Anonymousblockscanbenestedintheprocedureandexceptionblocksinasmanylevelsasyouwant.htm">Anonymous blocks can be nested in the procedure and exception blocks in as many levels as you want</a>
24. 1. 6.
The Lexical Set of Elements
24. 1. 7.
<A href="/Tutorial/Oracle/0480__PL-SQL-Programming/Delimiters.htm">Delimiters</a>
24. 1. 8.
<A href="/Tutorial/Oracle/0480__PL-SQL-Programming/Comments.htm">Comments</a>
24. 1. 9.
<A href="/Tutorial/Oracle/0480__PL-SQL-Programming/Multilinecommentsstartwithandendwith.htm">Multi-line comments start with /* and end with */.</a>
24. 1. 10.
<A href="/Tutorial/Oracle/0480__PL-SQL-Programming/Declaringvariables.htm">Declaring variables</a>
24. 1. 11.
<A href="/Tutorial/Oracle/0480__PL-SQL-Programming/DeclaringaVariablebyReference.htm">Declaring a Variable by Reference</a>
24. 1. 12.
<A href="/Tutorial/Oracle/0480__PL-SQL-Programming/Therearesomerestrictionsonthedeclarationofvariables.htm">There are some restrictions on the declaration of variables:</a>
24. 1. 13.
<A href="/Tutorial/Oracle/0480__PL-SQL-Programming/Assigningvaluestovariables.htm">Assigning values to variables</a>
24. 1. 14.
<A href="/Tutorial/Oracle/0480__PL-SQL-Programming/AssignSQLqueryresultstoPLSQLvariables.htm">Assign SQL query results to PL/SQL variables</a>
24. 1. 15.
<A href="/Tutorial/Oracle/0480__PL-SQL-Programming/Literalsasvariablevalues.htm">Literals as variable values</a>
24. 1. 16.
<A href="/Tutorial/Oracle/0480__PL-SQL-Programming/ExamplesofIntegerandRealLiterals.htm">Examples of Integer and Real Literals</a>
24. 1. 17.
<A href="/Tutorial/Oracle/0480__PL-SQL-Programming/Numericliteralscannotcontaindollarsignsorcommasbuttheycanbewritteninscientificnotation.htm">Numeric literals cannot contain dollar signs or commas, but they can be written in scientific notation</a>
24. 1. 18.
<A href="/Tutorial/Oracle/0480__PL-SQL-Programming/CharacterandstringliteralsintheOracleworldareenclosedbysinglequotes.htm">Character and string literals in the Oracle world are enclosed by single quotes</a>

== There are some restrictions on the declaration of variables:==

There is no forward declaration.
Multiple declarations are supported in PL/SQL, as shown here:



<source lang="sql">
SQL> declare
2 --v1, v2 NUMBER; -- INVALID
3 -- VALID
4 v1 NUMBER;
5 v2 NUMBER;
6 begin
7 NULL;
8 end;
9 /
PL/SQL procedure successfully completed.</source>

== Writing a simple program==

The simplest kind of PL/SQL code is called an anonymous block.
An anonymous block is a block of code that has its own DECLARE/BEGIN/END structure.
Anonymous blocks can either stand on their own (as shown here) or they can sit within any other PL/SQL program.
The general syntax for an anonymous block:



<source lang="sql">
declare
...
<Declaration part>
...
begin
...
<Procedural part>
...
exception
...
<Exception handler>
...
end;</source>

<OL><LI>The declaration section defines all variables, cursors, subprograms, and other elements to be used in the code.</LI><LI>The declaration section is optional.</LI><LI>The procedural section contains the main body of the routine.</LI><LI>The procedural section starts with the begin keyword and ends with the exception keyword or the end keyword if you have no exception section.</LI><LI>The procedural section is the only mandatory part of the code.</LI><LI>You must have at least one line of executable code in the procedural section.</LI><LI>You can use the NULL command to indicate that nothing should be executed.</LI><LI>The exception section is also optional.</LI><LI>The exception section allows the program to intercept and process exceptions.</LI></OL>

Oracle PL/SQL Tutorial/PL SQL Programming/Introduction - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)