文章/答案/技术大牛

发布

社区首页 >问答首页 >创建PLSQL代码以打印10名学生的成绩

问创建PLSQL代码以打印10名学生的成绩
EN

Stack Overflow用户

提问于 2020-10-17 14:09:18

回答 1查看 1.2K关注 0票数 1

‘以下是PLSQL打印学生成绩的程序。程序执行成功，但无法打印成绩。有什么建议/更正吗？

步骤:1

CREATE TABLE STUDENT(
  ROLL_NO   NUMBER,
  NAME      VARCHAR2(100),
  SECTION   NUMBER,
  CLASS     VARCHAR2(100),
  ORACLE    NUMBER,
  Dev_2000  NUMBER
);

INSERT INTO STUDENT (ROLL_NO, NAME, SECTION, CLASS, ORACLE, DEV_2000) values (1, 'AVINASH', 9025, 'STUDENT', 75, 85)
INSERT INTO STUDENT (ROLL_NO, NAME, SECTION, CLASS, ORACLE, DEV_2000) values (2, 'AKASH', 9025, 'WORKING', 45, 85);
INSERT INTO STUDENT (ROLL_NO, NAME, SECTION, CLASS, ORACLE, DEV_2000) values (3, 'ASHISH', 9025, 'WORKING', 48, 67)
INSERT INTO STUDENT (ROLL_NO, NAME, SECTION, CLASS, ORACLE, DEV_2000) values (4, 'AJAY', 9025, 'WORKING', 84, 56)
INSERT INTO STUDENT (ROLL_NO, NAME, SECTION, CLASS, ORACLE, DEV_2000) values (5, 'AKANSHA', 9025, 'WORKING', 62, 78)
INSERT INTO STUDENT (ROLL_NO, NAME, SECTION, CLASS, ORACLE, DEV_2000) values (6, 'AKSHAY', 9025, 'STUDENT', 75, 85)
INSERT INTO STUDENT (ROLL_NO, NAME, SECTION, CLASS, ORACLE, DEV_2000) values (7, 'ATHARVA', 9025, 'WORKING', 45, 85)
INSERT INTO STUDENT (ROLL_NO, NAME, SECTION, CLASS, ORACLE, DEV_2000) values (8, 'RAHUL', 9025, 'WORKING', 48, 67)
INSERT INTO STUDENT (ROLL_NO, NAME, SECTION, CLASS, ORACLE, DEV_2000) values (9, 'VYANKATESH', 9025, 'WORKING', 84, 56)
INSERT INTO STUDENT (ROLL_NO, NAME, SECTION, CLASS, ORACLE, DEV_2000) values (10, 'RUSHIKESH', 9025, 'STUDENT', 62, 78)

步骤:2

CREATE TABLE Dev_2000_result ( ROll_NO Number (4), TOTAL Number (3), PERCENT Number (5,2), GRADE Varchar2 (10) )

步骤:3

DECLARE 
    v_student student%rowtype;
    v_result dev_2000_result%rowtype;
    grade varchar2(10);

    CURSOR c1 IS
        SELECT *
        FROM student;
BEGIN
    FOR v_student IN c1 LOOP 
        IF v_student.class='Working' THEN
            IF v_student.Dev_2000 <50 THEN 
                grade:='FAIL';
            ELSE grade:='PASS';
            END IF;
        ELSIF v_student.class='Student' THEN 
            IF v_student.Dev_2000 >=80 THEN grade:='HONOURS';
                elsif v_student.Dev_2000 >=60 THEN grade:='A';
                elsif v_student.Dev_2000 >=50 THEN grade:='B';
                elsif v_student.Dev_2000 >=40 THEN grade:='C';
                ELSE grade:='B';
            END IF;
        END IF; 

        INSERT INTO Dev_2000_result
        VALUES(v_student.Roll_no,
               v_student.Oracle,
               v_student.Dev_2000,
               grade);
    END LOOP;
END;
/

步骤:4

Execute dev_2000_result;

sql

oracle

plsql

cursor

回答 1

Stack Overflow用户

回答已采纳

发布于 2020-10-17 14:40:35

问题是，如果将表中的大写SQL值与PL/ class中的非大写常量进行比较，则可以使用以下代码

DECLARE
    v_student student%rowtype;
    v_result dev_2000_result%rowtype;
    grade varchar2(10);
    
    CURSOR c1 IS
    SELECT *
    FROM STUDENT;
BEGIN
    FOR v_student IN c1 LOOP
        IF v_student.class='WORKING' THEN
            IF v_student.Dev_2000 <50 THEN grade:='FAIL'; ELSE grade:='PASS'; END IF;
        elsif v_student.class='STUDENT' THEN
            IF v_student.Dev_2000 >=80 THEN grade:='HONOURS';
            elsif v_student.Dev_2000 >=60 THEN grade:='A';
            elsif v_student.Dev_2000 >=50 THEN grade:='B';
            elsif v_student.Dev_2000 >=40 THEN grade:='C';
            ELSE grade:='B';
            END IF;
        END IF;
        
        INSERT INTO Dev_2000_result
        VALUES(v_student.Roll_no, v_student.Oracle, v_student.Dev_2000, grade);
    END LOOP;
END;
/

此外，您也不需要使用PL/SQL来完成此任务。这可以使用纯SQL来实现

insert into dev_2000_result
select roll_no, oracle, dev_2000, decode(class,
        'WORKING', (case when dev_2000 < 50 then 'FAIL' else 'PASS' end),
        'STUDENT', (case when dev_2000 >= 80 then 'HONOURS' when dev_2000 >= 60 then 'A' when dev_2000 >= 50 then 'B' when dev_2000 >= 40 then 'C' else 'B' end)
    ) grade
from STUDENT;

fiddle

票数 0

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/64399507

复制

相似问题

问创建PLSQL代码以打印10名学生的成绩
EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问创建PLSQL代码以打印10名学生的成绩EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问创建PLSQL代码以打印10名学生的成绩
EN