What is the Pl/sql program
In the 4th chapter, the standardized SQL language is used to perform various operations on the database, and only one statement can be executed at a time, with a semicolon in English. To end the identity, this is inconvenient to use, and inefficient, because the Oracle database system is not like VB, VC, such as programming language, it focuses on the management of the background database, so the ability to provide a weak programming, and structured programming language for the database support is weak, This is a huge burden for administrators if some of the more complex administrative tasks need to be implemented with the help of a programming language.
It is in this demand that, starting with Oracle 6, Oracle has developed its own pl/sql (procedural language/sql, Process SQL language) language based on standard SQL language, which converts variables, control structures, The elements of structured programming, such as processes and functions, are introduced into the SQL language, so that more complex SQL programs can be compiled, and programs written in the Pl/sql language are called Pl/sql program blocks.
The main features of the PL/SQL program block are as follows.
A modular structure.
Use procedural language to control structures.
Capable of error handling.
The PL/SQL program block can only be executed in an interpreted manner, supported by tools such as "SQL Plus", "Sqlplus Worksheet", and cannot be compiled into an executable file, leaving the supporting environment to execute.
Pl/sql Case Analysis
The following will create a data table named TestTable for the Tempuser user that was established earlier.
In the table there are recordnumber integer fields and currentdate Time fields, and a Pl/sql program completes automatically entering 100 records into the table, requiring recordnumber fields from 1 to 100, The CurrentDate field is the current system time.
(1) The Tempuser user's default tablespace is users, so the user must first be given a role named "RESOURCE" in order for it to be able to use table space to establish a data schema object.
(2) After you log on to the database as System user, SYSDBA, follow the steps to modify the user in Enterprise Manager until the Role tab of the editing user appears as shown in Figure 9.1.
Select RESOURCE in the Available Drop-down list box and click the button to add it to the granted list box. The default value cell is selected and the OK button is clicked.
(3) The reader can also do the following SQL code directly in "Sqlplus Worksheet".
―――――――――――――――――――――――――――――――――――――
GRANT "RESOURCE" to "Tempuser";
ALTER USER "Tempuser" DEFAULT role all
―――――――――――――――――――――――――――――――――――――
"Matching program location": the 9th Chapter \grantrole.sql.
(4) Follow the steps to create the datasheet until the General Information tab of the Create table shown in Figure 9.2 appears.
Enter "Testable" in the Name text box.
In the Scheme Drop-down list box, select Tempuser.
In the Table Space Drop-down list box, select Users.
Select "Table"/"Standard" radio buttons.
Select "Define Column" radio buttons.
Enter the definitions for the two data columns in the table column definition area.
Click the button when the settings are complete.
(5) The reader can also do the following SQL code directly in "Sqlplus Worksheet".
―――――――――――――――――――――――――――――――――――――
CREATE TABLE "Tempuser". TestTable "(" recordnumber "number (4) not
NULL, "currentdate" DATE not NULL)
Tablespace "USERS"
―――――――――――――――――――――――――――――――――――――
"Matching program location": the 9th Chapter \createtesttable.sql.
(6) Log in as a tempuser user "Sqlplus worksheet" and execute the following SQL code to complete the function of entering 100 records into the datasheet tempuser.testable. The results of the execution are shown in Figure 9.3.
―――――――――――――――――――――――――――――――――――――
Set Serveroutput on
Declare
MAXRECORDS constant int:=100;
I int: = 1;
Begin
For I in 1..maxrecords loop
Insert into tempuser.testtable (recordnumber,currentdate)
Values (i,sysdate);
End Loop;
Dbms_output.put_line (' Successful data entry! ');
Commit
End
―――――――――――――――――――――――――――――――――――――
"Matching program location": the 9th Chapter \inserttesttable.sql.
(7) Execute the following statement in "Sqlplus Worksheet" to query the inserted data, as shown in Figure 9.4.
―――――――――――――――――――――――――――――――――――――
SELECT * from Tempuser.testtable;
―――――――――――――――――――――――――――――――――――――
"Matching program location": the 9th Chapter \selecttesttable.sql.
The analysis of the Pl/sql program that completes the above data insertion process is shown in table 9.1.
Table 9.1 Pl/sql Instance Code analysis
| Program code |
Description |
| Set Serveroutput on |
Allow server output |
| Declare |
Define a partial identity |
| MAXRECORDS constant int:=100; |
Define maxrecords As Integer constant 100 |
| I int: = 1; |
Define I as an integer value variable with an initial number of 1 |
| Begin |
Perform a partial identification |
| For I in 1..maxrecords loop |
I cycle from 1 to maxrecords |
| Insert into tempuser.testtable (recordnumber,currentdate) values (i,sysdate); |
Inserting data into a datasheet |
| End Loop; |
End Loop |
| Dbms_output.put_line (' Successful data entry! '); |
Display successful data entry information |
| Commit |
Submit Results |
| End |
End execution |
The sysdate in the table is the system time function, Dbms_output is the system default package, Put_Line the method defined in the package, the function is output information; In Oracle, all changes to the database data do not directly manipulate the database, but are placed in the memory called the workspace. Permanent changes occur only after a commit statement has been executed.
PL/SQL Program Structure
Based on the above examples, the complete PL/SQL program structure can be divided into 3 parts.
1. Definition section
In this section, you define the constants, variables, cursors, and exception handling names to use in the program, and all the definitions used in the PL/SQL program must be defined in the declare, while in the high-level language the variables can be defined during program execution.
2. The operative part
Begins with begin and ends with end. This part is necessary for each Pl/sql program, contains the operation statements to the database and various process control statements.
3. Exception Handling Section
This part is contained in the execution part, takes the exception as the identification, carries on the processing to the exception which produces in the program execution. The overall structure of a complete Pl/sql program is shown in Figure 9.5.
Some programs are simpler, often omitting the exception handling part. Here are some basic grammatical elements of pl/sql.
