7-1（存储过程）创建一个显示学生总人数的存储过程

1 SQL>SET SERVEROUTPUT  ON 2 SQL>CREATE  OR REPLACE PROCEDURE STU_COUNT 3 　　2AS           --需要定义的存储过程内的变量均写在AS下 4    3TOTAL    NUMBER --定义 5    4BEGIN 6    5     SELECT COUNT(SNO) INTO TOTAL FROM STUDENT; --SELECT 后的变量个数要与INTO 后一致 7    6     DBMS_OUTPUT.PUT_LINE('总数：'||TOTAL);  --字符'||'的含义是连接两个字符串 8    7END; 9    8/10 11 SQL>EXECUTE STU_COUNT; 总数:12

PL/SQL 过程已成功完成。

 

7-2（存储过程）创建显示学生信息的存储过程STUDENT_LIST,并引用STU_COUNT存储过程

1 SQL> SET SERVEROUTPUT ON 2 SQL> CREATE OR REPLACE PROCEDURE STUDENT_LIST 3   2  AS 4   3  SNO                STUDENT.SNO%TYPE; 5   4  SNAME      STUDENT.SNAME%TYPE; 6   5  CURSOR C_STUDENT_INFO IS SELECT SNO,SNAME FROM STUDENT; 7   6  BEGIN 8   7     FOR C_STUDENT_i IN C_STUDENT_INFO 9   8     LOOP10   9     DBMS_OUTPUT.PUT_LINE(C_STUDENT_i.SNO||'---'||C_STUDENT_i.SNAME);11  10     END LOOP;12  11     STU_COUNT();13  12  END;14  13  /15 16 过程已创建。

运行结果

SQL> EXECUTE STUDENT_LIST;96001---马小燕96002---黎明96003---刘东明96004---赵志勇97001---马蓉97002---李成功97003---黎明97004---李丽74313---钱常来96006---张然96005---司马志明20001---赵薇总数:12PL/SQL 过程已成功完成。

如果写存储过程总是会报错，可以先测试一下游标环节是否有问题，游标测试编写如下：

SQL> DECLARE  2  SNO        STUDENT.SNO%TYPE;  3  SNAME      STUDENT.SNAME%TYPE;  4  CURSOR C_STUDENT_INFO IS SELECT SNO,SNAME FROM STUDENT;  5  BEGIN  6     FOR C_STUDENT_i IN C_STUDENT_INFO  7     LOOP  8     DBMS_OUTPUT.PUT_LINE(C_STUDENT_i.SNO||'---'||C_STUDENT_i.SNAME);  9     END LOOP; 10  END; 11  /96001---马小燕96002---黎明96003---刘东明96004---赵志勇97001---马蓉97002---李成功97003---黎明97004---李丽74313---钱常来96006---张然96005---司马志明20001---赵薇PL/SQL 过程已成功完成。

 

7-3（存储过程）创建一个显示学生平均成绩的存储过程

1 SQL> SET SERVEROUTPUT ON; 2 SQL> CREATE OR REPLACE PROCEDURE AVGSCORE(NO IN STUDENT.SNO%TYPE) 3   2  AS 4   3  AVERAGE NUMBER(5,2); 5   4  BEGIN 6   5     SELECT AVG(SCORE)INTO AVERAGE FROM SCORE GROUP BY SNO HAVING SNO = NO; 7   6     DBMS_OUTPUT.PUT_LINE(NO||'---'||AVERAGE); 8   7  END; 9   8  /10 SQL> EXECUTE AVGSCORE('96001');11 96001---83.6312 13 PL/SQL 过程已成功完成。

7-4  (存储过程)  创建显示所有学生平均成绩的存储过程

1 SQL> SET SERVEROUTPUT ON 2 SQL> CREATE OR REPLACE PROCEDURE STUDENT_AVG 3   2  AS 4   3  CURSOR SCORE_AVG IS SELECT SNO,AVG(SCORE) AS AVG_SCORE FROM SCORE GROUP BY SNO; 5   4  BEGIN 6   5     FOR I IN SCORE_AVG 7   6     LOOP 8   7             DBMS_OUTPUT.PUT_LINE(I.SNO||'---'||I.AVG_SCORE); 9   8     END LOOP;10   9  END;11  10  /12 13 过程已创建。14 15 SQL> EXECUTE STUDENT_AVG;16 74313---7217 96001---83.62518 96002---90.1666666666666666666666666666666666666719 96003---8020 96004---8721 96005---86.7522 97001---95.523 97002---91.524 97003---58.525 97004---83.3333333333333333333333333333333333333326 27 PL/SQL 过程已成功完成。 --如何让输出数据格式一致？？

SQL> DECLARE  2  SNO                SCORE.SNO%TYPE;  3  AVG_SCORE          SCORE.SCORE%TYPE;  4  CURSOR STUDENT_AVG IS SELECT AVG(SCORE) INTO AVG_SCORE FROM SCORE GROUP BY SNO;  5  BEGIN  6     FOR I IN STUDENT_AVG  7     LOOP  8             DBMS_OUTPUT.PUT_LINE(I.SNO||'---'||I.AVG_SCORE);  9     END LOOP; 10  END; 11  /                DBMS_OUTPUT.PUT_LINE(I.SNO||'---'||I.AVG_SCORE);                                       *第 8 行出现错误:ORA-06550: 第 8 行, 第 26 列:PLS-00302: 必须声明 'SNO' 组件ORA-06550: 第 8 行, 第 3 列:PL/SQL: Statement ignored

这是什么错？

7-5（修改数据库）在STUDENT表中增加SAVG(N,6,2)字段

1 SQL> ALTER TABLE STUDENT ADD SAVG NUMBER(6,2);2 3 表已更改。

 

7-6（存储过程）创建存储过程，计算每个学生的平均成绩保存到学生表中SAVG字段中

1 SQL> SET SERVEROUTPUT ON 2 SQL> CREATE OR REPLACE PROCEDURE SAVE_SAVG 3   2  AS 4   3  CURSOR STUDENT_AVG IS SELECT SNO,AVG(SCORE) AS AG FROM SCORE GROUP BY SNO; 5   4  BEGIN 6   5     FOR I IN STUDENT_AVG 7   6             LOOP 8   7             UPDATE STUDENT SET SAVG = I.AG WHERE SNO = I.SNO; 9   8     END LOOP;10   9  END;11  10  /12 13 过程已创建。14 15 SQL> EXECUTE SAVE_SAVG;16 17 PL/SQL 过程已成功完成。18 19 SQL> SELECT * FROM STUDENT;20 21 SNO        SNAME                    SDEP SCLA SSEX         SAGE       SAVG22 ---------- ------------------------ ---- ---- ------ ---------- ----------23 96001      马小燕                   CS   01   女             22      83.6324 96002      黎明                     CS   01   男             19      90.1725 96003      刘东明                   MA   01   男             18         8026 96004      赵志勇                   IS   02   男             20         8727 97001      马蓉                     MA   02   女             19       95.528 97002      李成功                   CS   01   男             21       91.529 97003      黎明                     IS   03   女             19       58.530 97004      李丽                     CS   02   女             20      83.3331 74313      钱常来                   SC   02   女             19         7232 96006      张然                     CS   02   男             2033 96005      司马志明                 CS   02   男             19      86.7534 35 SNO        SNAME                    SDEP SCLA SSEX         SAGE       SAVG36 ---------- ------------------------ ---- ---- ------ ---------- ----------37 20001      赵薇                     IS   02   女             1938 39 已选择12行

 

7-7  (触发器)　　当更新学生成绩表SCORE 中的学生成绩时，自动计算该学生的平均成绩保存到学生表中SAVG字段中

 

SQL>CREATE OR REPLACE PACKAGE MY_PACK            AS        a    STUDENT.SNO%TYPE;        END;        /SQL>CREATE OR REPLACE TRIGGER UPD_SC        BEFORE UPDATE ON SCORE        REFERENCING NEW AS NEW OLD AS OLD        FOR EACH ROW        BEGIN            MY_PACK.a := :NEW.SNO;        END;        /SQL>CREATE OR REPLACE TRIGGER UPD_SC_1        AFTER UPDATE ON SCRE        REFERENCING NEW AS NEW OLD AS OLD        FOR EACH ROW        DECLARE        b    SCORE.SCORE%TYPE;        PRAGMA AUTONOMOUS TRANSATION;        BEGIN            IF UPDATING    THEN                SELECT AVG(SCORE) INTO b FROM SCORE WHERE SNO = MY_PACK.a  GROUP BY SNO;            UPDATE STUDENT SET SAVG=b WHERE SNO= MY_PACK.a;            END IF ;        COMMIT;        END;        /

 

7-8 （触发器）　创建包含插入、删除、修改多种触发事件的触发器DBM_LOG，对SCORE表的操作进行记录。用INSETING、DELETING、UPDATING谓词来区别不同的DML操作

先创建事件记录表LOGS，该表用来对操作进行记录。该表的字段含义解释如下：

    LOG_ID：操作记录的编号，数值型，它是该表的主键，自动增1，可由序列自动生成。

    LOG_TABLE：进行操作的表名，字符型，非空，该表设计成可以由多个触发器共享使用。比如我们可以为Student表创建类似的触发器，同样将操作记录到该表。

    LOG_DML：操作的动作，即INSERT、DELETE或UPDATE三种之一。

    LOG_KEY_ID：操作时表的主键值，数值型。之所以记录表的主键，是因为主键是表的记录的惟一标识，可以识别是对哪一条记录进行了操作。对于Score表，主键是由SNO_CNO构成。

    LOG_DATE：操作的日期，日期型，取当前的系统时间。

    LOG_USER：操作者，字符型，取当时的操作者账户名。比如登录SCOTT账户进行操作，在该字段中，记录账户名为SCOTT。

CREATE TABLE LOGS(    LOG_ID    NUMBER(10) PRIMARY KEY,    LOG_TABLE    VARCHAR2(10) NOT NULL,    LOG_DML        VARCHAR2(10),    LOG_KEY_ID    NUMBER(10),    LOG_DATE    DATE,    LOG_USER     VARCHAR2(15));

CREATE SEQUENCE LOGS_ID_SQU INCREMENT BY 1START WITH 1MAXVALUE 99999NOCYCLE NOCACHE;

 

SQL> CREATE OR REPLACE TRIGGER DML_LOG  2  BEFORE  3  DELETE OR INSERT OR UPDATE ON SCORE  4  REFERENCING NEW AS NEW OLD AS OLD  5  FOR EACH ROW  6  BEGIN  7     IF INSERTING THEN  8             INSERT INTO LOGS VALUES(LOGS_ID_SQU.NEXTVAL,'SCORE','INSERT',:NEW.SCORE,SYSDATE,USER);  9     ELSIF DELETING THEN 10             INSERT INTO LOGS VALUES(LOGS_ID_SQU.NEXTVAL,'SCORE','DELETE',:NEW.SCORE,SYSDATE,USER); 11     ELSIF UPDATING THEN 12             INSERT INTO LOGS VALUES(LOGS_ID_SQU.NEXTVAL,'SCORE','UPDATE',:NEW.SCORE,SYSDATE,USER); 13     END IF ; 14  END; 15  /触发器已创建SQL> INSERT INTO SCORE VALUES('96001','002',83);已创建 1 行。SQL> select * from logs;    LOG_ID LOG_TABLE                                LOG_DML                                  LOG_KEY_ID LOG_DATE       LOG_USER---------- ---------------------------------------- ---------------------------------------- ---------- -------------- ----------------------------------------          1 SCORE                                    INSERT                                     83 18-6月 -18     SYSTEM

LOG_KEY_ID：操作时表的主键值，数值型,对于Score表，主键是由SNO_CNO构成。 ?????

 

SQL> SELECT * FROM SCORE WHERE SNO='96001';SNO        CNO         SCORE---------- ------ ----------96001      001            9096001      002            8396001      003            9296001      004            9296001      005            92SQL> UPDATE SCORE SET SCORE = 97 WHERE SNO = '96001' AND CNO= '003';已更新 1 行。SQL> DELETE FROM SCORE WHERE SNO='96001' AND CNO='005';已删除 1 行。SQL> SELECT * FROM LOGS;    LOG_ID LOG_TABLE---------- ----------------------------------------LOG_DML                                  LOG_KEY_ID LOG_DATE---------------------------------------- ---------- --------------LOG_USER----------------------------------------         1 SCOREINSERT                                           83 18-6月 -18SYSTEM         2 SCOREUPDATE                                           97 18-6月 -18SYSTEM    LOG_ID LOG_TABLE---------- ----------------------------------------LOG_DML                                  LOG_KEY_ID LOG_DATE---------------------------------------- ---------- --------------LOG_USER----------------------------------------         3 SCOREDELETE                                              18-6月 -18SYSTEM