Oracle存储过程与触发器
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 LOOP 10 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---赵薇 总数:12 PL/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.63 12 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---72 17 96001---83.625 18 96002---90.16666666666666666666666666666666666667 19 96003---80 20 96004---87 21 96005---86.75 22 97001---95.5 23 97002---91.5 24 97003---58.5 25 97004---83.33333333333333333333333333333333333333 26 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 SAVG 22 ---------- ------------------------ ---- ---- ------ ---------- ---------- 23 96001 马小燕 CS 01 女 22 83.63 24 96002 黎明 CS 01 男 19 90.17 25 96003 刘东明 MA 01 男 18 80 26 96004 赵志勇 IS 02 男 20 87 27 97001 马蓉 MA 02 女 19 95.5 28 97002 李成功 CS 01 男 21 91.5 29 97003 黎明 IS 03 女 19 58.5 30 97004 李丽 CS 02 女 20 83.33 31 74313 钱常来 SC 02 女 19 72 32 96006 张然 CS 02 男 20 33 96005 司马志明 CS 02 男 19 86.75 34 35 SNO SNAME SDEP SCLA SSEX SAGE SAVG 36 ---------- ------------------------ ---- ---- ------ ---------- ---------- 37 20001 赵薇 IS 02 女 19 38 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 1 START WITH 1 MAXVALUE 99999 NOCYCLE 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 90 96001 002 83 96001 003 92 96001 004 92 96001 005 92 SQL> 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 SCORE INSERT 83 18-6月 -18 SYSTEM 2 SCORE UPDATE 97 18-6月 -18 SYSTEM LOG_ID LOG_TABLE ---------- ---------------------------------------- LOG_DML LOG_KEY_ID LOG_DATE ---------------------------------------- ---------- -------------- LOG_USER ---------------------------------------- 3 SCORE DELETE 18-6月 -18 SYSTEM
