三分钟学会数据库，最简单的存储过程之项目运用 --- （4）

创建测试存储过程表：

CREATE TABLE TEST_S_C_SC (
    Sno nchar(10) NULL,
    Sname char(10) NOT NULL,
    Cno char(4) NOT NULL,
    VDATE char(8)
)

建表1：

CREATE TABLE student(
    Sno char(15) NOT NULL,
    Sname char(20) NULL,
    Ssex char(2) NULL,
    Sage smallint NULL,
    Sdept char(20) NULL
)
INSERT student (Sno, Sname, Ssex, Sage, Sdept) VALUES ('2012000', '李一平', '男', '18', 'IS')
INSERT student (Sno, Sname, Ssex, Sage, Sdept) VALUES ('2012111', '张立', '男', '19', 'IS')
INSERT student (Sno, Sname, Ssex, Sage, Sdept) VALUES ('2012222', '刘晨', '女', '19', 'CS')
INSERT student (Sno, Sname, Ssex, Sage, Sdept) VALUES ('2012333', '王敏', '女', '18', 'MA')
INSERT student (Sno, Sname, Ssex, Sage, Sdept) VALUES ('2012444', '李勇', '男', '20', 'CS')
INSERT student (Sno, Sname, Ssex, Sage, Sdept) VALUES ('201255555', '张琴', '女', '19', 'CS')
INSERT student (Sno, Sname, Ssex, Sage, Sdept) VALUES ('201266666', '王方', '女', '20', 'MA')
INSERT student (Sno, Sname, Ssex, Sage, Sdept) VALUES ('201277777', '黄林林', '男', '21', 'IS')

建表2：

CREATE TABLE Course (
    Cno char(4) NOT NULL,
    Cname char(40) NULL,
    Cpno char(4) NULL,
    Ccredit smallint NULL,
    Sno nchar(10) NULL
)
INSERT Course (Cno, Cname, Cpno, Ccredit, Sno) VALUES ('1','数据库','5','4','2012000')
INSERT Course (Cno, Cname, Cpno, Ccredit, Sno) VALUES ('2','数学', NULL, '2', '2012111   ')
INSERT Course (Cno, Cname, Cpno, Ccredit, Sno) VALUES ('3','信息系统','1', '4', '2012222   ')
INSERT Course (Cno, Cname, Cpno, Ccredit, Sno) VALUES ('4','操作系统', '6', '3', '2012333   ')
INSERT Course (Cno, Cname, Cpno, Ccredit, Sno) VALUES ('5','数据结构', '7', '4', '2012444   ')
INSERT Course (Cno, Cname, Cpno, Ccredit, Sno) VALUES ('6','数据处理', NULL, '2', '201277777 ')
INSERT Course (Cno, Cname, Cpno, Ccredit, Sno) VALUES ('7','C语言 ', '6', '4', '201288888 ')
INSERT Course (Cno, Cname, Cpno, Ccredit, Sno) VALUES ('8','语文', '8', '8', '201299999 ')

建表3：

CREATE TABLE SC (
    Sno nchar(10) NULL,
    Cno char(4) NOT NULL,
    Grade char(4) NOT NULL,
    VDATE datetime
)
INSERT SC(Sno, Cno, Grade,VDATE) VALUES ('2012000', '1', '60','2013-01-20 00:00:00.000')
INSERT SC(Sno, Cno, Grade,VDATE) VALUES ('2012111', '2', '70','2013-04-21 00:00:00.000')
INSERT SC(Sno, Cno, Grade,VDATE) VALUES ('2012222', '2', '70','2013-02-22 00:00:00.000')
INSERT SC(Sno, Cno, Grade,VDATE) VALUES ('2012333', '2', '70','2013-05-23 00:00:00.000')
INSERT SC(Sno, Cno, Grade,VDATE) VALUES ('2012444', '2', '70','2013-06-24 00:00:00.000')
INSERT SC(Sno, Cno, Grade,VDATE) VALUES ('2012555', '2', '70','2013-11-25 00:00:00.000')

 

 

创建简单的存储过程：

例子：

CREATE PROCEDURE P_TEST_S_C_SC
AS
BEGIN

    BEGIN TRANSACTION
    TRUNCATE TABLE TEST_S_C_SC;

    INSERT INTO TEST_S_C_SC
    (
         Sno
        ,Sname
        ,Cname
        ,GRADE
        ,Cno
        ,VDATE
    )
    SELECT
         student.Sno
        ,student.Sname
        ,Course.Cname
        ,cast(Grade/CAST(100 AS decimal(18,4))AS decimal(18,4))
        ,Course.Cno
        ,CONVERT(CHAR(8),SC.VDATE,112)
        
    FROM student
         LEFT JOIN Course ON student.Sno=Course.Sno
         LEFT JOIN SC     ON student.Sno=SC.Sno

    COMMIT TRANSACTION
END

未运行 EXEC P_TEST_S_C_SC

 

运行 EXEC P_TEST_S_C_SC 后

 

*******************************************************************************************************************************************************

有不对的地方请指正，欢迎关注，日后本人会把开发中遇到的一些问题，和常用的函数更新，欢迎评论一起进步