create or replace
PACKAGE BODY set_pass_rate_pkg AS
ln_total_cnt NUMBER DEFAULT 0;
ln_pass_cnt NUMBER DEFAULT 0;
ln_pass_rate NUMBER DEFAULT 0;
ln_adjust_cnt NUMBER DEFAULT 0;
lv_messager VARCHAR2(2000);
type adjust_ttype IS TABLE OF rowid INDEX BY pls_integer;
it_adjust adjust_ttype;
PROCEDURE main(p_pass_rate IN NUMBER
,p_class_id IN NUMBER
,p_course_name IN VARCHAR2
) AS
it_student wl_score_tab.student_id%TYPE;
it_course wl_score_tab.COURSE_ID%TYPE;
CURSOR get_adjust_cur(p_adjust_cnt in number) IS
SELECT
tt.rowid AS row_id
From(
SELECT
wst.ROWID AS row_id,
ws.CLASS_ID AS class_id,
ws.STUDENT_ID AS student_id,
ws.STUDENT_NAME AS student_name,
wct.COURSE_ID AS course_id,
wct.COURSE_NAME AS course_name,
wst.SCORE AS score,
CASE
WHEN wst.score -60 < 0 THEN
rank() over(PARTITION BY
CASE
WHEN wst.score -60 >= 0 THEN NULL
ELSE 'A'
END
ORDER BY wst.score)
END adjust_flag
FROM wl_student_tab ws,
wl_score_tab wst,
wl_course_tab wct
WHERE ws.class_id = p_class_id
AND wst.course_id =
(SELECT wct.course_id
FROM wl_course_tab wct
WHERE wct.course_name = p_course_name))tt
where tt.adjust_flag <= p_adjust_cnt; BEGIN
SELECT COUNT(*)
INTO ln_total_cnt
FROM wl_score_tab wst,
wl_student_tab ws
WHERE ws.class_id = p_class_id
AND wst.course_id =
(SELECT course_id
FROM wl_course_tab
WHERE course_name = p_course_name)
; SELECT COUNT(*)
INTO ln_pass_cnt
FROM wl_score_tab wst,
wl_student_tab ws
WHERE ws.class_id = p_class_id
AND wst.course_id =
(SELECT course_id
FROM wl_course_tab
WHERE course_name = p_course_name)
AND wst.score -60 > 0; IF(ln_total_cnt = 0) THEN
DBMS_OUTPUT.PUT_LINE('Data need not adjust!');
END IF; ln_pass_rate := ROUND(ln_pass_cnt / ln_total_cnt *100, 2); IF(ln_pass_rate -p_pass_rate > 0) THEN
DBMS_OUTPUT.PUT_LINE('date need not adjust!!');
ELSE
ln_adjust_cnt := ceil((p_pass_rate -ln_pass_rate) *ln_total_cnt / 100); OPEN get_adjust_cur(ln_adjust_cnt);
FETCH get_adjust_cur bulk collect
INTO it_adjust;
CLOSE get_adjust_cur; forall i IN 1 .. it_adjust.COUNT
UPDATE wl_score_tab wst
SET wst.score = 60
WHERE wst.rowid = it_adjust(i) returning wst.student_id,
wst.course_id bulk collect
INTO it_student,it_course;
FOR i IN it_student.FIRST .. it_student.LAST
LOOP
IF(i = it_student.FIRST) THEN
DBMS_OUTPUT.PUT_LINE('updated record list');
END IF;
DBMS_OUTPUT.PUT_LINE('student_id:' || to_char(it_student(i)) || 'course_id:' || to_char(it_course(i)));
END LOOP;
COMMIT;
END IF;
NULL;
END main;END set_pass_rate_pkg;forall i IN 1 .. it_adjust.COUNT
UPDATE wl_score_tab wst
SET wst.score = 60
WHERE wst.rowid = it_adjust(i) returning wst.student_id,
wst.course_id bulk collect
INTO it_student,it_course;这块报错
无法混合INTO 列表中的 单行和多行求助啊
解决方案 »
- 问个按时间段汇总统计的问题
- Oracle 10g的第一次用户登录问题
- 请问新建一个表时,有一个“方案”下拉供我选择,这是什么意思,,感谢
- 求一条SQL语句
- OracleDataAdapter.Update(dataTable) 提示“ORA-00903: 表名无效”的错误,查询没有问题,dataTable里面的数据有修改。
- 各位高手,小弟遇到个 “无法解析指定的连接标识符” 问题,帮帮小弟吧!!!!!!!!!!!!!!!!!!!!!!!!!!!!
- 郁闷,一个超级简单的问题就把我困死了,只怪oracle的提示太没含义了。耽误大家一分钟来帮帮我!
- 如何复制大量记录,只是主键编码改一下,其中某一个字段也相应改编
- 在pl/sql 中,取字符串中第二个字符到第五个字符该如何取?用什么函数?
- 请问一个IP配置的问题:
- Oracle EBS系统非常难的一个问题(获取销售价格)
- oracle erp工具有哪些
it_course wl_score_tab.COURSE_ID%TYPE;
这个是普通类型你要当数组用么?改成下面这样的。
it_student_type is table of wl_score_tab.student_id%TYPE index by binary_intger;
it_course_type is table if wl_score_tab.COURSE_ID%TYPE index by binary_intger;
it_student it_student_type;
it_course it_course_type;