oracle包，函数，过程，游标的使用

Question

最好把PL SQL好好学习，然后自己尝试解决问题。

Answer 1

这个过程写两个小时，终于调好了/***************************
*Name: cux_xzh_proc_teacher_sal
*Purpose: 输入课程号，
*  若该课程学生平均成绩达标并且不合格人数 <3 时，该任课老师加薪10%。
*  若超过1/3学生不合格，显示任课老师信息以及不合格学生ID，且该课程任课老师的奖金取消。
*Author: XZH
*Create At: 2013-12-11
****************************/
CREATE OR REPLACE PROCEDURE cux_xzh_proc_201312111614(
  --o_retcode OUT NUMBER,
  --o_errmsg OUT VARCHAR2,
  o_cid IN xzh_course.cid%TYPE 
)
AS
  v_avg_grade NUMBER;          /*指定课程学生的平均成绩*/     
  v_rate NUMBER;                               /*及格率*/
  v_fail NUMBER;                               /*不合格人数*/
  /*定义教师游标*/
  CURSOR teacher_info_cur
    IS
     SELECT DISTINCT t.*
       FROM xzh_teacher t, xzh_course c, xzh_grade g
      WHERE t.tid = c.tid
        AND c.cid = g.cid
        AND c.cid = o_cid;
  
  /*学生游标*/
  CURSOR fail_sno_cur
  IS
   SELECT s.sno sno,g.grade grade
     FROM xzh_student s, xzh_course c, xzh_grade g
    WHERE s.sno = g.sno
      AND c.cid = g.cid
      AND c.cid = 1
      AND g.grade < 60;
BEGIN
  /*得到平均成绩v_avg_grade*/
  SELECT AVG(g.grade) INTO v_avg_grade
    FROM xzh_course c, xzh_grade g
   WHERE c.cid = g.cid
     AND g.cid = o_cid;
  DBMS_OUTPUT.PUT_LINE(o_cid||'课程的平均成绩为：'||v_avg_grade);  
  /*得到不合格人数*/    
  SELECT COUNT(g.sno) INTO v_fail
    FROM xzh_course c, xzh_grade g
   WHERE c.cid = o_cid
     AND c.cid = g.cid
     AND g.grade <60;
  DBMS_OUTPUT.PUT_LINE(o_cid||'课程的不及格人数为：'||v_fail); 
 
  /*得到比率v_rate*/
  SELECT e1.n1 / e2.n2
    INTO v_rate
    FROM (SELECT COUNT(g.sno) n1
            FROM xzh_course c, xzh_grade g
           WHERE c.cid = g.cid
             AND g.cid = o_cid
             AND GRADE < 60) e1,
         
         (SELECT COUNT(g.sno) n2
            FROM xzh_course c, xzh_grade g
           WHERE c.cid = g.cid
             AND g.cid = o_cid) e2;
  DBMS_OUTPUT.PUT_LINE(o_cid||'课程的不及格率为:'||v_rate);              
  IF (v_avg_grade > 60) AND (v_fail < 3) 
    THEN         
    DBMS_OUTPUT.PUT_LINE('老师加工资了！');  
    UPDATE teacher_sal_v SET sal=sal+sal*0.1;    /*该任课老师加薪10% */
  ELSIF v_rate > 1/3 
    THEN
      DBMS_OUTPUT.PUT_LINE('老师奖金没有了！');
      UPDATE xzh_teacher
         SET xzh_teacher.comm = NULL
       WHERE xzh_teacher.tid IN
             (SELECT c.tid FROM xzh_course c WHERE c.cid = o_cid);
          
      DBMS_OUTPUT.PUT_LINE('教师ID  姓名  工资  奖金');
      FOR teacher_info IN teacher_info_cur LOOP
        DBMS_OUTPUT.PUT_LINE(teacher_info.tid||' '||teacher_info.tname||' '||teacher_info.sal||' '||teacher_info.comm);
      END LOOP;
      DBMS_OUTPUT.PUT_LINE('学号  成绩');
      FOR record_name IN fail_sno_cur LOOP
        DBMS_OUTPUT.PUT_LINE(record_name.sno||' '||record_name.grade);
      END LOOP;
  END IF; 
  EXCEPTION    
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('出错了!!!');    
END cux_xzh_proc_201312111614;
/
      
CALL cux_xzh_proc_201312111614(1);
SELECT *FROM xzh_teacher;
SELECT *FROM xzh_course;
SELECT *FROM xzh_grade;

Answer 2

请教下啊？
--视图　　显示课程名，授课老师，课程所选学生数，课程是否可选。create or replace view V_COU_TEA_STU(cou_name,tea_name,count1,count2)
as
select co.cou_name,te.tea_name,decode(sign(sta.count2),'1','可选','0','不可选','-1','不可选'),count(ch.stu_id)
from course co,teacher te,choise ch,start_course sta group by ch.stu_id
where co.cou_id = te.cou_id and te.cou_id = ch.cou_id and ch.cou_id = sta.cou_id

Answer 3

我改了下 表数据对不上 能帮忙看下吗？？
CREATE OR REPLACE PROCEDURE P_TEA_SAL_BOUNS( 
--o_retcode OUT NUMBER, 
--o_errmsg OUT VARCHAR2, 
o_cid IN COURSE.COU_ID%TYPE 
) 
AS 
v_avg_SCORE NUMBER; /*指定课程学生的平均成绩*/ 
v_rate NUMBER; /*及格率*/ 
v_fail NUMBER; /*不合格人数*/ 
/*定义教师游标*/ 
CURSOR teacher_info_cur 
IS 
SELECT DISTINCT t.* 
FROM TEACHER t, COURSE c, SCORE g 
WHERE t.COU_ID = c.COU_ID 
AND c.COU_ID = g.COU_ID 
AND c.COU_ID = o_cid; 
/*学生游标*/ 
CURSOR fail_STU_ID_cur 
IS 
SELECT s.STU_ID STU_ID,g.SCORE SCORE 
FROM TEACHER s, COURSE c, SCORE g 
WHERE s.STU_ID = g.STU_ID
AND c.COU_ID = g.COU_ID 
AND c.COU_ID = 1 
AND g.SCORE < 60; 
BEGIN 
/*得到平均成绩v_avg_SCORE*/ 
SELECT AVG(g.SCORE) INTO v_avg_SCORE 
FROM COURSE c, SCORE g 
WHERE c. COU_ID = g. COU_ID 
AND g. COU_ID = o_cid; 
DBMS_OUTPUT.PUT_LINE(o_cid||'课程的平均成绩为：'||v_avg_SCORE); 
/*得到不合格人数*/ 
SELECT COUNT(g.STU_ID) INTO v_fail 
FROM COURSE c, SCORE g 
WHERE c. COU_ID = o_cid 
AND c. COU_ID = g. COU_ID 
AND g.SCORE <60; 
DBMS_OUTPUT.PUT_LINE(o_cid||'课程的不及格人数为：'||v_fail); 
/*得到比率v_rate*/ 
SELECT e1.n1 / e2.n2 
INTO v_rate 
FROM (SELECT COUNT(g.STU_ID) n1 
FROM COURSE c, SCORE g 
WHERE c. COU_ID = g. COU_ID AND g. COU_ID = o_cid 
AND SCORE < 60) e1, 
(SELECT COUNT(g.STU_ID) n2 
FROM COURSE c, SCORE g 
WHERE c. COU_ID = g. COU_ID 
AND g. COU_ID = o_cid) e2; 
DBMS_OUTPUT.PUT_LINE(o_cid||'课程的不及格率为:'||v_rate); 
IF (v_avg_SCORE > 60) AND (v_fail < 3) 
THEN 
DBMS_OUTPUT.PUT_LINE('老师加工资了！'); 
UPDATE teacher_sal_v SET sal=sal+sal*0.1; /*该任课老师加薪10% */ 
ELSIF v_rate > 1/3 
THEN 
DBMS_OUTPUT.PUT_LINE('老师奖金没有了！'); 
UPDATE TEACHER 
SET TEACHER.BOUNS = NULL 
WHERE TEACHER.TEA_ID IN 
(SELECT c.TEA_ID FROM COURSE c WHERE c.COU_ID = o_cid); 
DBMS_OUTPUT.PUT_LINE('教师ID 姓名 工资 奖金'); 
--FOR teacher_info IN teacher_info_cur LOOP 
--DBMS_OUTPUT.PUT_LINE(teacher_info.TEA_ID||' '||teacher_info.TEA_NAME||' '||--teacher_info.sal||' '||teacher_info.BOUNS); 
loop
fetch teacher_info_cur into cou_id,tea_name,sal,bouns;
DBMS_OUTPUT.PUT_LINE(TEA_ID||' '||TEA_NAME||' '||sal||' '||BOUNS);--teacher_info.sal||' '||teacher_info.BOUNS);
END LOOP; 
DBMS_OUTPUT.PUT_LINE('学号 成绩'); 
--FOR record_name IN fail_STU_ID_cur LOOP 
--DBMS_OUTPUT.PUT_LINE(record_name.STU_ID||' '||record_name.SCORE); 
loop
fetch fail_STU_ID_cur into stu_id,SCORE; 
DBMS_OUTPUT.PUT_LINE(STU_ID||' '||SCORE);
END LOOP; 
END IF; 
EXCEPTION 
WHEN OTHERS THEN 
DBMS_OUTPUT.PUT_LINE('出错了!!!'); 
END P_TEA_SAL_BOUNS; 
/ CALL P_TEA_SAL_BOUNS(1); 
SELECT *FROM TEACHER; 
SELECT *FROM COURSE; 
SELECT *FROM SCORE;
下面是我们写的表
CREATE TABLE STUDENT(      --学生表
  STU_ID  number primary key,           --学号 主键
  STU_NAME  varchar2(20) not null,      --学生姓名
  SEX  number not null, --学生性别
  AGE  number, --学生年龄
  TEL  number --联系方式
);
CREATE TABLE CHOISE ( --选课表
  STU_ID number not null, --学号 主键
  COU_ID number not null, --课程号 主键
  constraint pk_choise primary key (stu_id,cou_id)
);CREATE TABLE SCORE ( --成绩表
  SCORE number not null, --学生成绩
  COU_ID number not null, --课程号
  COU_NAME varchar2(50) not null, --课程名
  STU_ID number not null, --学生学号
  STU_NAME varchar2(20) not null, --学生姓名
  constraint pk_score primary key (stu_id,cou_id,score)
);CREATE TABLE START_COURSE ( --开课表
  COU_ID number primary key, --课程号 主键
  TEA_ID number not null, --教师工号
  COUNT1 number not null, --开课人数
  COUNT2 number not null --限选人数
);CREATE TABLE TEACHER (  --教师表
  TEA_ID number primary key, --教师工号 主键
  TEA_NAME varchar2(20) not null, --教师姓名
  SEX  number, --教师性别
  COU_ID number not null, --课程号
  SAL   number not null, --工资
  BOUNS number, --奖金
  TEL   number, --联系方式
  TEA_ADD varchar2(50) --所在办公室
);CREATE TABLE COURSE ( --课程表
  COU_ID   number primary key, --课程号 主键
  COU_NAME  varchar2(50) not null, --课程名
  CREDIT  number not null, --学分
  HOUR    number not null, --课时
  COU_ADD  varchar(50) not null, --上课地点
  COU_DATE date not null --开课时间
);

Answer 4

你只要清楚表与表之间的关联就行了
我定的表示这样的：
课程表cid与 成绩表的cid关联
课程表cid与 教师表的cid关联
成绩表sno与 学生表的sno关联
我弄得简单了点，没有定义选课表，但是这些原理还是一样的其实我也是刚上班，是做oracle方面的，上司跟我们说表能不加约束就不加，主键外键什么的根本没有必要，一个 not null就足够了
 

Answer 5

这是我定义的表结构，你看看就行了，原理还是一样的--老师表
CREATE TABLE xzh_teacher(
tid NUMBER,
tname VARCHAR2(30),
sal NUMBER,
comm NUMBER
)
INSERT INTO xzh_teacher(tid,tname,sal,comm) VALUES(1,'小明',1300,100);
INSERT INTO xzh_teacher(tid,tname,sal,comm) VALUES(2,'多发点',2300,null);
INSERT INTO xzh_teacher(tid,tname,sal,comm) VALUES(3,'小粉',1400,50);
INSERT INTO xzh_teacher(tid,tname,sal,comm) VALUES(4,'风格',1360,150);
INSERT INTO xzh_teacher(tid,tname,sal,comm) VALUES(5,'为',860,100);
INSERT INTO xzh_teacher(tid,tname,sal,comm) VALUES(6,'人体',3300,1100);
INSERT INTO xzh_teacher(tid,tname,sal,comm) VALUES(7,'为收付',1200,null);
INSERT INTO xzh_teacher(tid,tname,sal,comm) VALUES(8,'一塌糊',1600,60);
--课程表
CREATE TABLE xzh_course(
cid NUMBER,
tid NUMBER,
cname VARCHAR2(30),
count_student NUMBER
)INSERT INTO xzh_course(cid,tid,cname,count_student) VALUES(1,1,'语文',30);
INSERT INTO xzh_course(cid,tid,cname,count_student) VALUES(2,7,'英语',43);
INSERT INTO xzh_course(cid,tid,cname,count_student) VALUES(3,6,'数学',63);
INSERT INTO xzh_course(cid,tid,cname,count_student) VALUES(4,5,'物理',45);
INSERT INTO xzh_course(cid,tid,cname,count_student) VALUES(5,4,'化学',37);
INSERT INTO xzh_course(cid,tid,cname,count_student) VALUES(6,3,'地理',33);
INSERT INTO xzh_course(cid,tid,cname,count_student) VALUES(7,2,'生物',43);
INSERT INTO xzh_course(cid,tid,cname,count_student) VALUES(8,8,'历史',35);
SELECT *FROM xzh_course;
--分数表
CREATE TABLE xzh_grade(
cid NUMBER,
sno NUMBER,
grage NUMBER
)
--修改列名
ALTER TABLE xzh_grade RENAME COLUMN grage TO grade;
INSERT INTO xzh_grade(cid,sno,grade) VALUES(1,1,45);
INSERT INTO xzh_grade(cid,sno,grade) VALUES(1,2,33);
INSERT INTO xzh_grade(cid,sno,grade) VALUES(1,3,65);
INSERT INTO xzh_grade(cid,sno,grade) VALUES(1,4,56);
INSERT INTO xzh_grade(cid,sno,grade) VALUES(1,5,77);
INSERT INTO xzh_grade(cid,sno,grade) VALUES(1,6,39);
INSERT INTO xzh_grade(cid,sno,grade) VALUES(1,7,88);
INSERT INTO xzh_grade(cid,sno,grade) VALUES(1,8,67);
INSERT INTO xzh_grade(cid,sno,grade) VALUES(2,1,45);
INSERT INTO xzh_grade(cid,sno,grade) VALUES(2,2,66);
INSERT INTO xzh_grade(cid,sno,grade) VALUES(2,3,67);
INSERT INTO xzh_grade(cid,sno,grade) VALUES(2,4,45);
INSERT INTO xzh_grade(cid,sno,grade) VALUES(2,5,34);
INSERT INTO xzh_grade(cid,sno,grade) VALUES(2,6,77);
INSERT INTO xzh_grade(cid,sno,grade) VALUES(2,7,23);
INSERT INTO xzh_grade(cid,sno,grade) VALUES(2,8,98);
INSERT INTO xzh_grade(cid,sno,grade) VALUES(3,1,45);
INSERT INTO xzh_grade(cid,sno,grade) VALUES(3,2,34);
INSERT INTO xzh_grade(cid,sno,grade) VALUES(3,3,44);
INSERT INTO xzh_grade(cid,sno,grade) VALUES(3,4,66);
INSERT INTO xzh_grade(cid,sno,grade) VALUES(3,5,77);
INSERT INTO xzh_grade(cid,sno,grade) VALUES(3,6,88);
INSERT INTO xzh_grade(cid,sno,grade) VALUES(3,7,67);
INSERT INTO xzh_grade(cid,sno,grade) VALUES(3,8,55);
INSERT INTO xzh_grade(cid,sno,grade) VALUES(4,1,34);
INSERT INTO xzh_grade(cid,sno,grade) VALUES(4,2,55);
INSERT INTO xzh_grade(cid,sno,grade) VALUES(4,3,57);
INSERT INTO xzh_grade(cid,sno,grade) VALUES(4,4,77);
INSERT INTO xzh_grade(cid,sno,grade) VALUES(4,5,88);
INSERT INTO xzh_grade(cid,sno,grade) VALUES(4,6,99);
INSERT INTO xzh_grade(cid,sno,grade) VALUES(4,7,66);
INSERT INTO xzh_grade(cid,sno,grade) VALUES(4,8,46);
INSERT INTO xzh_grade(cid,sno,grade) VALUES(5,1,44);
INSERT INTO xzh_grade(cid,sno,grade) VALUES(5,2,56);
INSERT INTO xzh_grade(cid,sno,grade) VALUES(5,3,76);
INSERT INTO xzh_grade(cid,sno,grade) VALUES(5,4,78);
INSERT INTO xzh_grade(cid,sno,grade) VALUES(5,5,67);
INSERT INTO xzh_grade(cid,sno,grade) VALUES(5,6,56);
INSERT INTO xzh_grade(cid,sno,grade) VALUES(5,7,34);
INSERT INTO xzh_grade(cid,sno,grade) VALUES(5,8,33);
INSERT INTO xzh_grade(cid,sno,grade) VALUES(6,1,44);
INSERT INTO xzh_grade(cid,sno,grade) VALUES(6,2,55);
INSERT INTO xzh_grade(cid,sno,grade) VALUES(6,3,66);
INSERT INTO xzh_grade(cid,sno,grade) VALUES(6,4,77);
INSERT INTO xzh_grade(cid,sno,grade) VALUES(6,5,11);
INSERT INTO xzh_grade(cid,sno,grade) VALUES(6,6,22);
INSERT INTO xzh_grade(cid,sno,grade) VALUES(6,7,33);
INSERT INTO xzh_grade(cid,sno,grade) VALUES(6,8,44);
INSERT INTO xzh_grade(cid,sno,grade) VALUES(7,1,46);
INSERT INTO xzh_grade(cid,sno,grade) VALUES(7,2,76);
INSERT INTO xzh_grade(cid,sno,grade) VALUES(7,3,77);
INSERT INTO xzh_grade(cid,sno,grade) VALUES(7,4,88);
INSERT INTO xzh_grade(cid,sno,grade) VALUES(7,5,99);
INSERT INTO xzh_grade(cid,sno,grade) VALUES(7,6,45);
INSERT INTO xzh_grade(cid,sno,grade) VALUES(7,7,76);
INSERT INTO xzh_grade(cid,sno,grade) VALUES(7,8,65);
INSERT INTO xzh_grade(cid,sno,grade) VALUES(8,1,55);
INSERT INTO xzh_grade(cid,sno,grade) VALUES(8,2,67);
INSERT INTO xzh_grade(cid,sno,grade) VALUES(8,3,87);
INSERT INTO xzh_grade(cid,sno,grade) VALUES(8,4,67);
INSERT INTO xzh_grade(cid,sno,grade) VALUES(8,5,56);
INSERT INTO xzh_grade(cid,sno,grade) VALUES(8,6,65);
INSERT INTO xzh_grade(cid,sno,grade) VALUES(8,7,45);
INSERT INTO xzh_grade(cid,sno,grade) VALUES(8,8,56);
SELECT *FROM xzh_grade;
--学生表
CREATE TABLE xzh_student(
sno NUMBER,
sname VARCHAR2(30)
)
INSERT INTO xzh_student(sno,sname) VALUES(1,'发的');
INSERT INTO xzh_student(sno,sname) VALUES(2,'为');
INSERT INTO xzh_student(sno,sname) VALUES(3,'热');
INSERT INTO xzh_student(sno,sname) VALUES(4,'我');
INSERT INTO xzh_student(sno,sname) VALUES(5,'去');
INSERT INTO xzh_student(sno,sname) VALUES(6,'人');
INSERT INTO xzh_student(sno,sname) VALUES(7,'他');
INSERT INTO xzh_student(sno,sname) VALUES(8,'粉');
INSERT INTO xzh_student(sno,sname) VALUES(9,'通过');
INSERT INTO xzh_student(sno,sname) VALUES(0,'他');
INSERT INTO xzh_student(sno,sname) VALUES(11,'二分');
INSERT INTO xzh_student(sno,sname) VALUES(12,'让人');
INSERT INTO xzh_student(sno,sname) VALUES(13,'谔谔');
INSERT INTO xzh_student(sno,sname) VALUES(14,'去');
INSERT INTO xzh_student(sno,sname) VALUES(15,'为');
INSERT INTO xzh_student(sno,sname) VALUES(16,'请求');
INSERT INTO xzh_student(sno,sname) VALUES(17,'啊啊');
INSERT INTO xzh_student(sno,sname) VALUES(18,'请求');
INSERT INTO xzh_student(sno,sname) VALUES(19,'查询');
INSERT INTO xzh_student(sno,sname) VALUES(20,'突然');
INSERT INTO xzh_student(sno,sname) VALUES(21,'团推');
INSERT INTO xzh_student(sno,sname) VALUES(22,'英语');