Writing stored procedures with PL/SQL to access the database

First, the purpose of the experiment

Be familiar with using stored procedures to design database applications.

Ii. contents of the experiment

For the student-curriculum database, write the stored procedure to complete the following functions:

1. Statistics on the distribution of the results of discrete mathematics, that is, according to the statistics of each fractional segment;

2. Statistics on the average score of any course;

3. Change the students ' course selection from percentile to hierarchical (i.e. A, B, C, D, E).

Requirement: Submits the source program and represents the necessary comments. Ensure that the program compiles and runs correctly, and fills in the experiment report carefully.

Third, the experimental steps

Prior to the experiment, a database was established, with Student,course and SC three basic tables.

(i) Statistical Discrete Mathematics of the results distribution, that is, according to the number of points in the statistics.

1. Establish table Rank, where the first column division shows the division of scores, and the second column numbers shows the number of students who scored in that score section.

CREATE TABLE Rank (

Division CHAR (20),

Number INT);

2. write the stored procedure.

CREATE PROCEDURE Statistic_mark (@name char (50))

As

DECLARE

@less60 INT,

@b60a70 INT,

@b70a80 INT,

@b80a90 INT,

@more90 INT,

@curcno VARCHAR (8);

Begin

Select @curcno = CNO

From Course

where CNAME [email protected];

if (@curcno is null)

RAISERROR (' Course number is empty ', 16, 1);

Else

SELECT @less60 =count (*)

From SC

WHERE CNO [email protected] and grade <60;

SELECT @b60a70 =count (*)

From SC

WHERE CNO [email protected] and grade >=60 and grade<70;

SELECT @b70a80 =count (*)

From SC

WHERE CNO [email protected] and grade >=70 and grade<80;

SELECT @b80a90 =count (*)

From SC

WHERE CNO [email protected] and grade >=80 and grade<90;

SELECT @more90 =count (*)

From SC

WHERE CNO [email protected] and grade >=90;

INSERT into RANK VALUES (' [0,60] ', @less60);

INSERT into RANK VALUES (' [60,70] ', @b60a70);

INSERT into RANK VALUES (' [70,80] ', @b70a80);

INSERT into RANK VALUES (' [80,90] ', @b80a90);

INSERT into RANK VALUES (' [90,100] ', @more90);

END;

3. Execute the stored procedure

After writing the stored procedure Statistic_mark, in Query Analyzer, select the single transaction Execution command in the menu so that the system creates the stored procedure

Then use perform to call the procedure and view the results of the execution in table rank.

exec statistic_mark ' discrete '

SELECT *

From rank

( II) to count the average scores of any one course

1. Create a stored procedure

(1) Create the desired table structure.

According to the experimental requirements, to count the average scores of any one course, it is necessary to establish a table Avggrade, where the first column of the CNAME shows the course name of the statistics, the second column AVG shows the average grade of the students who took the course.

CREATE TABLE Avggrade (

CNAME char (50),

Avg numeric (10,6));

(1) Writing stored procedures

Create or Replace procedure Collect_avg ()

As

DECLARE//declaring variables

Curname char (50);

Curno char (4);

Curavgg char (10,6);

Cursor MyCursor for//Declaration cursor MyCursor Query Course number and course name

Select Cno,cname from Course;

Begin

Open mycursor; Open cursor

If Mycursor%isopen then//condition control, the cursor opens with the following processing

Loop//loop control

FETCH mycursor into Curcno,curname; Cursor pushes a row to fetch the result of the variable

EXIT when (mycursor%notfound); If there is no return value, exit the loop

SELECT AVG (grade) to Curavgg from SC//Ask the average of the course to send variables

WHERE CNO = curcno;

Insert a record into the Avggrade//table showing the course name and average score

Insere into Avggrade VALUES (CURNAME,CURAVGG);

END LOOP; End loop control

END IF; End Condition Control

CLOSE MyCursor;

END;

2. execute the stored procedure

First execute the well-written stored procedure Collect_avg, and then view the results of the execution in table Avggrade.

PERFORM PROCEDURE collect_avg ();

SELECT * from Avggrade;

(iii) in table SC Change the students ' choice of course from percentile to grade

1. Create a stored procedure

According to the experimental requirements, the execution of the stored procedure in this experiment does not need to return the result on the client, so it does not need to establish the corresponding table structure to hold the execution result of the stored procedure. Write the stored procedure directly.

Create or Replace procedure change_critical ()

As

DECLARE

Chgrade CHAR (1);

Currecord record;

BEGIN

ALTER TABLE SC ADD COLUMN (Newgrade CHAR (1));

For Currecord in Select*from SC LOOP

IF Currecord.grade<60 Then

Chgrade = ' E ';

Elsif Currecord.grade<70 Then

Chgrade = ' D ';

Elsif Currecord.grade<80 Then

Chgrade = ' C;

Elsif Currecord.grade<90 Then

Chgrade = ' B ';

ELSE

Chgrade = ' A ';

END IF;

UPDATE SC SET Newgrade =chgrade

WHERE Sno =currecord.sno and Cno=currecord.cno;

END LOOP;

ALTER TABLE SC DROP COLUMN grade;

ALTER TABLE SC RENAME newgrade to grade;

END;

2 Executing stored procedures

PERFORM PROCEDURE change_critical ();

(iv) To Delete a stored procedure

Once the stored procedure is established, it is saved in the database, which makes it easy for users to invoke and execute repeatedly at any time. If you no longer need the stored procedure, you can delete it.

Delete the stored procedure Statistic_mark.

DROP PROCEDURE Statistic_mark;

(1) Delete stored procedure collect_avg,

DROP PROCEDURE Collect_avg,

(2) Delete the stored procedure change_critical;

DROP PROCEDURE change_critical;

Iv. Summary of the experiment

Recommended Reading Blog: http://www.cnblogs.com/knowledgesea/archive/2013/01/02/2841588.html

Http://www.cnblogs.com/hoojo/archive/2011/07/19/2110862.html

Stored procedure procedure is a set of SQL statements that are compiled to complete a particular function, stored in a database, and executed by specifying the name of the stored procedure and giving parameters.

Stored procedures can contain logical control statements and data manipulation statements, which can accept parameters, output parameters, return single or multiple result sets, and return values.

Because stored procedures are compiled on the database server and stored in the database when they are created, the stored procedure runs faster than a single SQL statement block. At the same time, because it only needs to provide the stored procedure name and the necessary parameter information in the call, it can reduce the network traffic and the simple network burden to some extent.

Advantages of stored Procedures

1. Stored procedures allow standard component-type programming;

2. The stored procedure can achieve faster execution speed;

3. Stored procedures to reduce network traffic;

4. Stored procedures can be used as a security mechanism to make full use of them.

Common system stored procedures are:

exec sp_databases; --View Database
Execsp_tables; --View Table
EXEC sp_columnsstudent;--View columns
Execsp_helpindex student;--View Index
Execsp_helpconstraint student;--constraints
Execsp_stored_procedures;
exec sp_helptext ' sp_stored_procedures ';--view stored procedure creation, definition statement
EXEC sp_renamestudent, stuinfo;--modify the name of the table, index, column
exec sp_renamedbmytempdb, mydb;--change database name
Execsp_defaultdb ' master ', ' MyDB ';--Change the default database for logins
execsp_helpdb;--database Help, querying database information
exec sp_helpdbmaster;

To create parameters for a stored procedure:

1.procedure_name: The name of the stored procedure, preceded by # for the local temporary stored procedure, plus # #为全局临时存储过程.

2. Number: is an optional integer that is used to group procedures of the same name so that the same set of procedures can be removed with a single dropprocedure statement. For example, an application named orders uses a procedure that can be named Orderproc;1, Orderproc;2, and so on. The drop PROCEDURE orderproc statement will drop the entire group. If the name contains a bounding identifier, the number should not be included in the identifier, only the appropriate delimiter should be used before and after procedure_name.

[email protected]: parameters of the stored procedure. can have one or more. The user must provide the value of each declared parameter when the procedure is executed (unless the default value for that parameter is defined). A stored procedure can have a maximum of 2.1 parameters.

Use the @ symbol as the first character to specify a parameter name. Parameter names must conform to the rules for identifiers. The parameters for each procedure are used only for the procedure itself, and the same parameter names can be used in other procedures. By default, a parameter can be used instead of a constant instead of the name of a table name, column name, or other database object. For more information, see Execute.

4.data_type: The data type of the parameter. All data types, including text, ntext, and image, can be used as parameters for stored procedures. However, the cursor data type can only be used for OUTPUT parameters. If the specified data type is cursor, you must also specify both the VARYING and the OUTPUT keywords. For more information about the data types provided by SQL Server and their syntax, see Data types.

Description There is no maximum number of limits for output parameters that can be the cursor data type.

5.VARYING: Specifies a result set that is supported as an output parameter (dynamically constructed by stored procedures and content can vary). Only the cursor parameter is applicable.

6.default: The default value for the parameter. If you define a default value, you do not have to specify the value of the parameter to perform the procedure. The default value must be constant or null. If the procedure uses the LIKE keyword for the parameter, the default value can include wildcards (%, _, [], and [^]).

7.OUTPUT: Indicates that the parameter is a return parameter. The value of this option can be returned to Exec[ute]. Use the output parameter to return information to the calling procedure. The Text, ntext, and image parameters can be used as OUTPUT parameters. The output parameter that is used with the Export keyword can be a cursor placeholder.

8.RECOMPILE: Indicates that SQL Server does not cache the schedule for this procedure, which is recompiled at run time. Use the RECOMPILE option when you are using atypical or temporary values and you do not want to overwrite the execution plan that is cached in memory.

9.ENCRYPTION: Represents an entry in SQL Server encrypted syscomments table that contains the text of the CREATE PROCEDURE statement. Use encryption to prevent the process from being published as part of SQL Server replication. Description during the upgrade process, SQL Server re-creates the encryption process by leveraging the cryptographic annotations stored in syscomments.

10.FOR REPLICATION: Specifies that stored procedures created for replication cannot be performed at the Subscriber. The stored procedure that is created with the For REPLICATION option can be used as a stored procedure filter and can only be executed during the replication process. This option cannot be used with the WITH RECOMPILE option.

11.AS: Specifies the action to be performed by the procedure.

12.sql_statement: A Transact-SQL statement of any number and type to be included in the procedure. But there are some limitations.

Writing stored procedures with PL/SQL to access the database