The Oracle tutorial you are looking at is the introductory Foundation for Oracle Pl/sql languages.
Pl/sql is Oracle's extension of the standard database language, Oracle has integrated PL/SQL into Oracle servers and other tools, and more developers and DBAs have started using Pl/sql in recent years, and this article covers Pl/sql basic syntax, architecture and components, And how to design and execute a pl/sql program.
Advantages of Pl/sql
Starting with version 6, Pl/sql is reliably integrated into Oracle, and once you have the benefits of Pl/sql and its unique data management convenience, it's hard to imagine Oracle missing Pl/sql. Pl/sql is not a stand-alone product, he is a technology that integrates into Oracle Server and Oracle tools, can treat pl/sql as an engine inside Oracle server, SQL statement performer handles a single SQL statement, Pl/sql engine handles pl/ SQL program block. When the PL/SQL program block is processed by the Pl/sql engine, the SQL statement executor in the Oracle server processes the SQL statements in the PL/SQL program block.
The advantages of Pl/sql are as follows:
. Pl/sql is a high-performance, transaction-based language that can run in any Oracle environment and support all data processing commands. Process SQL data definition and data control elements by using the PL/SQL program unit.
. Pl/sql supports all SQL data types and all SQL functions, while supporting all Oracle object types
. Pl/sql blocks can be named and stored in an Oracle server, and can be invoked by other PL/SQL programs or SQL commands, and any client/server tool can access the PL/SQL program and is well reusable.
. You can use Oracle data tools to manage the security of PL/SQL programs that are stored in the server. Ability to authorize or revoke access to Pl/sql programs by other users of the database.
. Pl/sql code can be written using any ASCII text editor, so it is very convenient for any operating system that Oracle can run
. For sql,oracle, each SQL statement must be processed at the same time, which means that each individual invocation must be handled by an Oracle server in a networked environment, which takes up a lot of server time and leads to network congestion. The pl/sql is sent to the server with the entire statement block, which reduces network congestion.
PL/SQL block Structure
Pl/sql is a kind of block structure language, the unit that composes Pl/sql program is logical block, a pl/sql program contains one or more logical blocks, each block can be divided into three parts. As with other languages, a variable must be declared before it is used, and Pl/sql provides a separate section specifically for handling exceptions, which describes the different parts of the Pl/sql block:
Declarations section (Declaration sections)
The Declarations section contains the data types and initial values of variables and constants. This section starts with the keyword declare, and if you don't need to declare variables or constants, you can ignore this part; What you need to say is that the declaration of the cursor is also in this section.
Executive part (executable section)
The execution part is the instruction part in the Pl/sql block, starting with the keyword begin, all executable statements are placed in this part, and other pl/sql blocks can also be placed in this section.
Exception handling (Exception section)
This section is optional, dealing with exceptions or errors in this section, and the detailed discussion of exception handling is done later.
Pl/sql block syntax
Each statement in a pl/sql block must end with a semicolon, and the SQL statement can make more than one line, but the semicolon represents the end of the statement. You can have more than one SQL statement in a row, separated by semicolons. Each pl/sql block begins with begin or declare and ends with end. Comment by--marked.
Naming and anonymity of pl/sql blocks
The PL/SQL program block can be either a named program block or an anonymous program block. Anonymous program blocks can be used on the server side or on the client.
Named program blocks can appear in the Declarations section of other pl/sql blocks, which is more obvious than subroutines, which can be referenced in the execution part or in the Exception handling section.
The PL/SQL program block can be independently compiled and stored in the database, and any application connected to the database can access the stored pl/sql blocks. Oracle provides four types of stored programs:
. Function
. Process
. Package
. Trigger
Function
A function is a named, PL/SQL program block that is stored in a database. The function accepts 0 or more input parameters, has a return value, and the data type of the returned value is defined when the function is created. The syntax for defining a function is as follows:
Process
A stored procedure is a PL/SQL program block that accepts 0 or more parameters as input (input) or output (output), or both as input and output (INOUT), unlike functions where stored procedures do not return values and stored procedures cannot be directly used by SQL statements. The syntax for defining stored procedures is as follows, only through the execut command or the PL/SQL program block:
Package (Package)
A package is actually a collection of related objects that are grouped together, and when any function or stored procedure in a package is invoked, the package is loaded into memory, and the access speed of any function or stored procedure in the package is greatly accelerated.
The package consists of two parts: the specification and the package body, the specification describes variables, constants, cursors, and subroutines, and the package body fully defines subroutines and cursors.
Triggers (trigger)
Triggers are associated with a table or database event, and triggers that are defined on the table are triggered when a trigger event occurs.
[NextPage]
Variables and constants
Variables are stored in memory to obtain values that can be referenced by pl/sql blocks. You can think of a variable as a container that can store things, and things in a container can be changed.
declaring variables
Variables are generally declared in the Declarations section of the Pl/sql block, and Pl/sql is a strong type language, which means that the variable must be declared first in the declaration section before it can be referenced in the execution or exception handling section.
The syntax for declaring variables is as follows:
Note: You can impose a not-null constraint on a variable while declaring a variable, at which point the variable must be assigned when it is initialized.
Assigning values to variables
There are two ways to assign a value to a variable:
. Assigning values directly to variables
x:=200;
y=y+ (X*20);
. Assigning values to variables through SQL SELECT into or fetch into
SELECT sum (SALARY), sum (salary*0.1)
Into Total_salary,tatal_commission
From EMPLOYEE
WHERE dept=10;
Constant
Constants are similar to variables, but the value of a constant cannot be changed within a program, and the value of a constant is given when defined, and he declares the same way as a variable, but must include the keyword constant. Both constants and variables can be defined as SQL and user-defined data types.
This statement has a constant named Zero_value, the data type is number, and the value is 0.
Scalar (scalar) data type
Scalar (scalar) data types do not have internal components, and they can be roughly grouped into the following four categories:
. Number
. Character
. Date/time
. Boolean
Table 1 shows the numeric data types, table 2 shows the character data types, and table 3 shows the date and Boolean data types.
Table 1 Scalar types:numeric
Table 2 Character data types
Table 3 Date and Boolean
LOB data type
LOB (Large object, Large object) data type is used to store large data objects such as images, sounds, and LOB data objects can be binary or character data with a maximum length of no more than 4G. LOB data types support arbitrary access, and long only supports sequential access. The LOB is stored in a separate location, while a lob locator (LOB locator) is stored in the original table, which is a pointer to the actual data. Manipulating LOB Data Objects in Pl/sql using the package Dbms_lob provided by Oracle. LOB data types can be grouped into the following four categories:
. BFILE
. Blob
. Clob
[1] [2] Next page
The Oracle tutorial you are looking at is the introductory Foundation for Oracle Pl/sql languages. NCLOB
Operator
As with other programming languages, Pl/sql has a series of operators. The operators are grouped into the following categories:
. Arithmetic operator
. Relational operators
. comparison operator
. logical operators
The arithmetic operators are shown in table 4
Relational operators are used primarily for conditional judgment statements or for a where substring, the relational operator checks whether the condition and the result are true or false, and table 5 is the relational operator in Pl/sql
Table 6 shows the comparison operator
Table 7.8 shows the logical operators
Executive part
The execution section contains all the statements and expressions, the execution begins with the keyword begin, ends with the keyword exception, and ends with the keyword End If exception does not exist. Semicolons separate each statement, using the assignment operator: = or SELECT INTO or fetch into to assign values to each variable, and the execution part of the error will be resolved in the Exception handling section, and another PL/SQL program block can be used in the execution section, which is called a nested block
All SQL data manipulation statements can be used in the execution section, and the Pl/sql block can no longer display the output of the SELECT statement on the screen. The SELECT statement must include an into substring or a part of the cursor, and the variables and constants used by the execution must first be declared in the declaration section, and the execution must include at least one executable statement, NULL being a valid executable statement, Object control statements Commit and rollback can be used in the execution section, and the Data definition language (language) cannot be used in the execution section, and DDL statements are used with execute immediate or are DBMS_ SQL call.
Execute a pl/sql block
The execution of an anonymous pl/sql block in the Sql*plus is entered/executed after the Pl/sql block, as in the following example:
Unlike the execution of an anonymous program, a named program block must use the Execute keyword:
If you execute this program in another named program block or in an anonymous block, you do not need execute to close the word.
[NextPage]
Control structure
Control structure Control pl/sql program flow code line, PL/SQL support conditional control and cyclic control structure.
Grammar and use
IF.. THEN
Grammar:
The IF statement determines whether the condition condition is true and, if so, executes the statement following then, if condition is false or NULL, skips the statement between then to end if, and executes the statement following the "if".
IF.. THEN ... ELSE
Grammar:
If the condition condition to true, the statement between then to else is executed, otherwise the statement between else and end if is executed.
If can be nested, can be in the if or if. Else statement using the IF or if ... Else statement.
IF.. THEN.. elsif
Grammar:
If the condition condition1 to True, the statement1 is executed, then the STATEMENT5 is judged, or the condition2 is true, and if true, the statement2 is executed, and then the STATEMENT5 is executed. The same is true for Condition3, and if Condition1,condition2,condition3 does not, then the STATEMENT4 is executed and STATEMENT5 is executed.
Loop control
The basic form of loop control is the loop statement, and the statements between the loop and end loop are executed infinitely. The syntax for the Loop statement is as follows:
LOOP
statements;
End LOOP
The execution of the statement between the loop and end loop is clearly not possible, so you must use the Exit statement to force the loop to end when you use the loop statement, for example:
The value of Y now is 1010.
The EXIT when statement ends the loop and ends the loop if the condition is true.
While.. LOOP
While.. The loop has a condition that is associated with a loop and, if the condition is true, executes the statement in the loop body and ends the loop if the result is false.
For ..... LOOP
Grammar:
The loops and while loops are indeterminate, and the loop number of loops is fixed, counter is an implicitly declared variable, his initial value is Start_range, and the second value is start_range+1 until End_range, If Start_range equals end _range, then the loop executes once. If you use the Reverse keyword, the range will be a descending order.
If you want to exit the for loop, you can use the Exit statement.
Label
Users can use labels to make programs more readable. The program block or loop can be marked. The form of the label is <>.
Marking a program block
Tag Loops
Goto statement
Grammar:
GOTO LABEL;
When a goto statement is executed, control immediately turns to the statement labeled by the label. There are some restrictions on goto statements in Pl/sql, and it is illegal for blocks, loops, and if statements to jump from the outer layers to the inner layer.
Note: null is a valid executable statement.
Nesting
The inside of a program block can have another program block, which is called nesting. Nesting to be aware of is a variable, defined in the outermost block of the variables can be used in all the child blocks, if you define in the child block variable with the same variable name, in the execution of the child block will use the variables defined in the child block. A variable defined in a child block cannot be referenced by a parent block. The same goto statement cannot be diverted from the parent block to the child block, whereas the converse is legal.
Summary
In this article we introduced the basic syntax of pl/sql and how to design and run the PL/SQL program block using the Pl/sql language, and integrate the PL/SQL program into the Oracle server, although the Pl/sql program is embedded in the Oracle database as a feature block, but pl/ The tight combination of SQL and Oracle databases has enabled more and more Oracle database administrators and developers to start using Pl/sql.
Previous page
prev [1] [2]
