Pl/sql Comprehensive Example

This section will analyze a more complex example based on the pl/sql of the previous study to teach readers how to write a complete pl/sql program.

Example Design

1. Function design
The postgraduate enrollment system developed by a university requires the design of the pl/sql procedure to deal with the candidate's performance data, and the logic of the processing is to automatically classify candidates as admission candidates, dispensing candidates and unsuccessful candidates according to the minimum fraction and total score of each professional course.
To this end, the design of two data tables, graduate data tables to store candidates ' results, result data table storage processing results, PL/SQL program completion of the function is to graduate data table of the data by line scan, according to the bar to judge, calculate the total score of each branch, Automatically adds a flag field on the result datasheet to either "accept" or "lose".
2. Datasheet design
The Graduate data table structure is shown in table 9.3.




Creating data Tables

For simplicity, the two data tables for the instance are built under the default Scott user, so that the reader can fully simulate an instance-consistent environment for learning.
1. Create graduate Datasheet
Follow these steps in the "General Information" tab of the Create table shown in Figure 9.58.
Enter "Graduate" in the Name text box.
In the Scheme Drop-down list box, select SCOTT.
In the Table Space Drop-down list box, select USERS.
Set in the table structure definition area as shown in the diagram.
Click the button when the settings are complete.

The reader can also execute the following SQL code in "sqlplus Worksheet" to create a datasheet scott.graduate directly.
――――――――――――――――――――――――――――――――――――――
CREATE TABLE "SCOTT". " Graduate "(
"BH" number (a) is not NULL,
' XM ' VARCHAR2 not NULL,
"LB" VARCHAR2 (Ten) is not NULL,
' Yingyu ' number (4, 1) is not NULL,
' Zhengzhi ' number (4, 1) is not NULL,
' ZHUANYE1 ' number (4, 1) is not NULL,
' ZHUANYE2 ' number (4, 1) is not NULL,
' ZHUANYE3 ' number (4, 1) not NULL)
Tablespace "USERS"
――――――――――――――――――――――――――――――――――――――
"Matching program Location": 9th Chapter \ Creategraduate.sql.
2. Create a result data table
Follow these steps in the "General Information" tab of the Create table shown in Figure 9.59.
In the Name text box, enter result.
In the Scheme Drop-down list box, select SCOTT.
In the Table Space Drop-down list box, select USERS.
Set in the table structure definition area as shown in the diagram.
Click the button when the settings are complete.

The reader can also execute the following SQL code in "sqlplus Worksheet" to create a datasheet scott.result directly.
――――――――――――――――――――――――――――――――――――――
CREATE TABLE "SCOTT". " Result "(
"BH" number (a) is not NULL,
' XM ' VARCHAR2 not NULL,
"LB" VARCHAR2 (Ten) is not NULL,
' Yingyu ' number (4, 1) is not NULL,
' Zhengzhi ' number (4, 1) is not NULL,
' ZHUANYE1 ' number (4, 1) is not NULL,
' ZHUANYE2 ' number (4, 1) is not NULL,
' ZHUANYE3 ' number (4, 1) is not NULL,
' Totalscore ' number (5, 1) is not NULL,
' FLAG ' VARCHAR2 (4) not NULL)
Tablespace "USERS"
――――――――――――――――――――――――――――――――――――――
"Matching program Location": 9th Chapter \ Createresult.sql.

Input data

Use the table data Editor to input the following experimental data to the Scott.graduate datasheet. As shown in Figure 9.60.

The reader can also execute the following SQL code in the "Sqlplus worksheet" and directly input the test data to the datasheet scott.graduate.


"Matching program Location": 9th Chapter \ Insertgraduate.sql.

Programming

1. Create Process Scott.graduateprocess
Execute the following pl/sql code in "Sqlplus Worksheet" to create the process scott.graduateprocess. The results of the execution are shown in Figure 9.61.

―――――――――――――――――――――――――――――――――――――
/* Define Process parameters * *
Create or replace procedure scott.graduateprocess (
Tempzhengzhi in Scott.graduate.zhengzhi%type,
Tempyingyu in Scott.graduate.yingyu%type,
Tempzhuanye1 in Scott.graduate.zhuanye1%type,
Tempzhuanye2 in Scott.graduate.zhuanye2%type,
Tempzhuanye3 in Scott.graduate.zhuanye3%type,
Temptotalscore in Scott.result.totalscore%type) as
/* Defines Graduaterecord as a record variable, temporarily storing records extracted from the graduate datasheet via a cursor * *
Graduaterecord Scott.graduate%rowtype;
/* Define Graduatetotalscore as numeric variables, statistical total score * *
Graduatetotalscore Scott.result.totalscore%type;
/* Define Graduateflag as a character variable, put "unsuccessful" or "accepted" according to the result, and write to the datasheet result*/
Graduateflag VARCHAR2 (4);
/* Defines the cursor graduatecursor, storing all the records in the Graduate datasheet * *
Cursor Graduatecursor is
SELECT * from Scott.graduate;
/* Define exception handling errormessage*/
ErrorMessage exception;
/* Start Executing * *
Begin
/* OPEN CURSOR/*
Open graduatecursor;
/* If the cursor has no data, activate exception handling * *
If Graduatecursor%notfound Then
Raise errormessage;
End If;
/* The cursor has data, the pointer points to the first record, every fetch command, automatically down, loop execution until the record is extracted.
Loop
Fetch graduatecursor into Graduaterecord;
/* Calculate TOTAL Score * *
graduatetotalscore:=graduaterecord.yingyu+graduaterecord.zhengzhi+graduaterecord.zhuanye1+ Graduaterecord.zhuanye2
+graduaterecord.zhuanye3;
* To determine whether a single section and the total score to meet the requirements of admission, if satisfied, Graduateflag variable value for "admission", otherwise "unsuccessful" * *
if (Graduaterecord.yingyu>=tempyingyu and
Graduaterecord.zhengzhi>=tempzhengzhi and
Graduaterecord.zhuanye1>=tempzhuanye1 and
Graduaterecord.zhuanye2>=tempzhuanye2 and
Graduaterecord.zhuanye3>=tempzhuanye3 and
Graduatetotalscore>=temptotalscore) Then
graduateflag:= ' admission ';
Else
graduateflag:= ' unsuccessful ';
End If;
/* When the cursor data is extracted, exit the loop "* * *
Exit when Graduatecursor%notfound;
/* Insert processed data into result data table * *
INSERT INTO
Scott.result (Bh,xm,lb,zhengzhi,yingyu,zhuanye1,zhuanye2,zhuanye3,totalscore,flag)
VALUES (graduaterecord.bh,graduaterecord.xm,graduaterecord.lb,graduaterecord.zhengzhi,graduate
Record.yingyu,graduaterecord.zhuanye1,graduaterecord.zhuanye2,graduaterecord.zhuanye3,graduat
Etotalscore,graduateflag);
End Loop;
/* Close CURSOR/*
Close graduatecursor;
* * Submission Result * *
Commit
/* Exception handling, prompt error message * *
exception
When ErrorMessage Then
Dbms_output.put_line (' Unable to open datasheet ');
/* Program execution End "* *
End
―――――――――――――――――――――――――――――――――――――――――――
"Matching program Location": 9th Chapter \ Creategraduateprocess.sql.
2. Main program mainprocess Design
The main program calls the procedure named Graduateprocess to complete processing, the code is designed as follows:
―――――――――――――――――――――――――――――――――――――
Set Serveroutput on
/* Define 6 entry variables corresponding to the specialized courses and total scores in the graduate datasheet.
Declare
Score1 number (4,1);
Score2 number (4,1);
Score3 number (4,1);
Score4 number (4,1);
Score5 number (4,1);
Scoretotal number (5,1);
/* will bar assignment, here to modify the values on behalf of the different fractional bar * *
Begin
score1:=50;
score2:=56;
score3:=60;
score4:=62;
score5:=64;
scoretotal:=325;
/* Call Processing Process * *
Scott.graduateprocess (score1,score2,score3,score4,score5,scoretotal);
End
――――――――――――――――――――――――――――――――――――――――――――
"Matching program Location": 9th Chapter \ Mainprocess.sql.
In the above process design, the Pl/sql programming elements, such as exception handling, cursor, variable and so on, are used to pass parameters to the process with parameters through the main program.

Execution results

1. Results of the first group of implementation
(1) Change the Mainprocess.sql file slightly, execute the following pl/sql program in "Sqlplus Worksheet", and perform the result as shown in Figure 9.62.



"Matching program Location": 9th Chapter \ Mainprocess1.sql.
(2) Use the table Data Editor to view the results of the Scott.result datasheet, as shown in Figure 9.63.

2. Results of the second group of implementation
(1) Change the Mainprocess.sql file slightly, execute the following pl/sql program in "Sqlplus Worksheet", and perform the result as shown in Figure 9.64.


"Matching program Location": 9th Chapter \ Mainprocess1.sql.
(2) Use the table Data Editor to view the results of the Scott.result datasheet, as shown in Figure 9.63.

2. Results of the second group of implementation
(1) Change the Mainprocess.sql file slightly, execute the following pl/sql program in "Sqlplus Worksheet", and perform the result as shown in Figure 9.64.


"Matching program Location": 9th Chapter \ Mainprocess2.sql.

(2) Use the table Data Editor to view the results of the Scott.result datasheet, as shown in Figure 9.65.

By using Pl/sql's design elements synthetically, many complex processing programs can be designed, which is an important task of DBA.