Oracle PL/SQL language (procedural language/sql)

Oracle PL/SQL language (procedural language/sql) is a combination of structured
Query and Oracle's own process control as one of the powerful language, PL/SQL not only support more data types, with self-
Variable declarations, assignment statements, and conditions, loops, and other process control statements. Process Control structure and SQL number
The seamless combination of processing power creates a powerful programming language that allows you to create processes and functions as well as packages.
PL/SQL is a block-structured language that places a set of statements in a block and sends it to the server at once, PL/SQL
The engine analysis receives the contents of the PL/SQL statement block, and the process Control statements are executed by the PL/SQL engine itself.
The SQL statements in the PL/SQL block are given to the server for execution.
After the PL/SQL block is sent to the server, it is compiled and executed, for a named PL/SQL block (such as a subroutine)
Can be compiled separately, permanently stored in the database, ready to execute. The advantages of PL/SQL are also:
? Support SQL
SQL is the standard language to access the database, and with SQL commands, users can manipulate the data in the database. PL/SQL
Supports all SQL data manipulation commands, cursor control commands, transaction control commands, SQL functions, operators, and pseudo-columns.
At the same time, PL/SQL is tightly integrated and PL + supports all SQL data types and NULL values.
? Supports object-oriented programming
PL/SQL supports object-oriented programming, you can create types in PL/SQL, you can inherit types, and you can
Overloaded methods in subroutines, and so on.
? Better performance
SQL is a non-procedural language that can only be executed one line at a time, and PL/PL blocks the Consolidated
can also store the compiled PL/SQL blocks for reuse, reducing the application and server
Communication time, PL/SQL is fast and efficient.
? Portability
Applications written using PL/SQL can be ported to an Oracle server on any operating system platform, while
You can also write portable libraries for reuse in different environments.
? Security
The application logic between the client and the server can be delimited by a stored procedure, which limits the
Access to Oracle databases, and the ability of the database to authorize and revoke access from other users.
2. PL/SQL blocks
PL/SQL is a block-structured language in which a PL/SQL program contains one or more logical blocks, in logical blocks
Variables can be declared, and variables must be declared before they are used. In addition to the normal execution procedure, PL/SQL also provides a special
Exception handling in the exception handling section of the door. Each logical block is divided into three parts, and the syntax is:
Syntax structure: The syntax of PL/SQL blocks
[DECLARE
--declaration Statements]①
BEGIN
--executable Statements②
[EXCEPTION
--exception Statements]③
END;
Syntax parsing:
① Declaration section: The declaration section contains definitions of variables and constants. This section starts with the keyword DECLARE,
If you do not declare variables or constants, you can omit this part.
② execution part: The execution part is the instruction portion of the PL/SQL block, starting with the keyword begin, the keyword END
End. All of the executable PL/SQL statements are placed in this section, which executes the command and operates on the variable.
Other PL/SQL blocks can be nested within that section as child blocks. The execution portion of the PL/SQL block is required. Note
Ends with a semicolon after the meaning end keyword.
③ Exception Handling section: This section is optional, which uses the EXCEPTION keyword to divide the executable part into two
A small part, the previous program is a normal operation of the program, once an exception jumps to the exception section execution.
PL/SQL is a programming language that, like Java and C #, has its own unique data types, variable declarations, and assignments.
Values and Process Control statements, PL/SQL also has its own language characteristics:
PL/SQL is not sensitive to capitalization, and for a good program style, the development team will choose a suitable coding standard.
For example, some team rules: The key words are all larger, the rest of the section lowercase.
Each statement in a PL/SQL block must end with a semicolon, which can be multiple lines, but the semicolon indicates that the language
End of sentence. There can be multiple SQL statements in a row, separated by semicolons, but it is not recommended to write multiple words in one line
Sentence
Special symbol descriptions in PL/sql:
Type symbol description
Assignment operators: = both Java and C # are equal equals, and PL/SQL is assigned the following values: =
Special characters
|| String connection operator.
-a single-line comment in PL/SQL.
/*,*/multiple lines of comments in PL/SQL, multiline annotations cannot be nested.
<<,>> tag delimiter. Only to identify the program's special location.
.. Range operators, such as: 1. 5 identification from 1 to 5
Arithmetic operators
+,-,*,/the basic arithmetic operator.
* * exponentiation operation, for example: 3**2=9
Relational operators
>,<,>=,<=,= the basic relational operator, = represents an equality relationship, not an assignment.
<>,!= unequal relations.
The logical operator And,or,not logical operators.
Table 1 special symbols and operators in PL/SQL
? Variable declaration
PL/SQL supports the data types in the database, normal support for the number, varchar2,date, and other Oracle
SQL data type. Declaring a variable must indicate the data type of the variable, or you can initialize the variable when declaring it, and the variable
The declaration must be in the Declarations section. The syntax for declaring variables is:
Syntax format: declaring variables
Variable name data type [: = initial value]
Syntax parsing:
Data type if length is required, the length can be indicated by parentheses, for example: VARCHAR2 (20).
Code Demo: declaring variables
Sql> DECLARE
2 sname VARCHAR2: = ' Jerry '; ①
3 BEGIN
4 sname:=sname| | ' and Tom '; Ii
5 Dbms_output.put_line (sname); ③
6 END;
7/jerry
PL/SQL procedure successfully completed
Code parsing:
① declares a variable sname, and the initialization value is "Jerry". String in single quotation marks, if the string appears in a single
Quotation marks can be represented by two single quotation marks (' '), that is, single quotes also have the effect of escaping.
② assigns a value to the variable sname, the assignment operator is ": =".
③dbms_output.put_line is an output statement that can output the value of a variable and output it in Sql*plus
Data, there may be no results displayed, you can use the command: Set serveroutput on settings output to Sql*plus
The control table.
Assigning values to variables can also be used with SELECT ... The INTO statement assigns variables to the query data from the database. But
The result of a query can be only one row of records, not 0 or more rows.
Code Demo: Variable Assignment
Sql> DECLARE
2 sname VARCHAR2 (+) DEFAULT ' Jerry '; ①
3 BEGIN
4 SELECT ename into sname from emp WHERE empno=7934; Ii
5 Dbms_output.put_line (sname);
6 END;
7/
MILLER
PL/SQL procedure successfully completed
Code parsing:
When the ① variable is initialized, the variable can be initialized with the DEFAULT keyword.
② uses the Select...into statement to assign a value to the variable sname, which requires that the result of the query must be a row, not multiple
Line or no record.
? Declaring constants
Constants are given an initial value when declared, and are not allowed to be re-assigned at run time. Use the CONSTANT keyword to declare often
Amount
Code Demo: Declaring constants
Sql> DECLARE
2 pi CONSTANT Number: = 3.14; --Pi Long value ①
3 R number DEFAULT 3; --Circle Radius default value 3②
4 area number; --area.
5 BEGIN
6 area:=pi*r*r; --Calculating area
7 Dbms_output.put_line (area); --the area of the output circle
8 END;
9/
28.26
PL/SQL procedure successfully completed
Code parsing:
① Use the keyword CONSTANT when declaring a constant, a constant value can be assigned using the assignment operator (: =), or
You can use the default keyword to assign a value.
It is also possible to declare a session in Sql*plus, which is a process called by a client from connection to exit.
The current user's session. ) Global-level variables, which work throughout the session, such variables are called
The host variable. The host variable is referenced with a ": variable name" in PL/SQL Reference.
Code Demo: Host Constants
sql> var emp_name varchar (30); ①
Sql> BEGIN
2 SELECT ename into:emp_name from emp WHERE empno=7499; Ii
3 END;
4/
PL/SQL procedure successfully completed
Emp_name
---------ALLEN
sql> print emp_name; ③
Emp_name
---------ALLEN
Code parsing:
① can declare host variables using VAR.
When accessing a host variable in ②pl/sql, add ":" before the variable.
③ in Sql*plus, use Print to output the results in a variable