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.
<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:
- 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:
<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
- 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:
<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>
- 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.
