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构成。 ?????