Oracle PL/SQL Tutorial/PL SQL Programming/Introduction
Содержание
- 1 An example of an anonymous block.
- 2 Anonymous blocks can be nested in the procedure and exception blocks in as many levels as you want
- 3 Anonymous Block Structure
- 4 Assigning values to variables
- 5 Assign SQL query results to PL/SQL variables
- 6 Character and string literals in the Oracle world are enclosed by single quotes
- 7 Comments
- 8 Declaring a Variable by Reference
- 9 Declaring variables
- 10 Delimiters
- 11 Each complete line of the PL/SQL code must end with a semicolon (;).
- 12 Examples of Integer and Real Literals
- 13 Literals as variable values
- 14 Multi-line comments start with /* and end with */.
- 15 Numeric literals cannot contain dollar signs or commas, but they can be written in scientific notation
- 16 The Lexical Set of Elements
- 17 There are some restrictions on the declaration of variables:
- 18 Writing a simple program
An example of an anonymous block.
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;
/
Anonymous blocks can be nested in the procedure and exception blocks in as many levels as you want
<<MAIN>>
declare
...
Declaration section
...
begin
...
Procedural section
...
<<SUB1>>
declare
...
begin
...
end;
...
exception
...
end;
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
[DECLARE variable_declarations]
BEGIN
program_code
[EXCEPTION error_handling_code]
END;
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
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>
Assign SQL query results to PL/SQL variables
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>
Character and string literals in the Oracle world are enclosed by single quotes
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>
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:
declare
here you should declare variables,
constants, etc.
...
begin
-- here you place your code
...
end;
/
Declaring a Variable by Reference
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>
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:
declare
variable_name [constant] DATATYPE [DEFAULT value |DEFAULT NULL];
begin
...
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.
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>
Examples of Integer and Real Literals
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>
Literals as variable values
Literals are explicit numeric, character, string, or Boolean values not represented by an identifier.
Two types of numeric literals exist:
- Integer literals represent optionally signed numeric values without decimal points.
- Real literals represent optionally signed whole or fractional numbers with decimal points.
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:
declare
/* This code is written by sqle.ru. Dec 20 2007 */
...
begin
...
end;
Numeric literals cannot contain dollar signs or commas, but they can be written in scientific notation
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>
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
- The PL/SQL lexical set of elements consists of identifiers, delimiters, literals, and comments.
- Identifiers are names of PL/SQL program items and units.
- These items and units could be of different kinds - constants, variables, exceptions, cursors, cursor variables, subprograms, and packages.
- An identifier cannot exceed 30 characters.
- An identifier consists of a letter optionally followed by more letters, numerals, dollar signs, underscores, and number signs.
- By default, identifiers are not case sensitive.
- Identifiers may not be the same as reserved words, so you cannot use the word end as a variable name.
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:
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.
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:
declare
...
<Declaration part>
...
begin
...
<Procedural part>
...
exception
...
<Exception handler>
...
end;
- The declaration section defines all variables, cursors, subprograms, and other elements to be used in the code.
- The declaration section is optional.
- The procedural section contains the main body of the routine.
- The procedural section starts with the begin keyword and ends with the exception keyword or the end keyword if you have no exception section.
- The procedural section is the only mandatory part of the code.
- You must have at least one line of executable code in the procedural section.
- You can use the NULL command to indicate that nothing should be executed.
- The exception section is also optional.
- The exception section allows the program to intercept and process exceptions.
