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