--包的创建
create or replace PACKAGE PLAY1PACKAGE AS
PROCEDURE Add_Score(mainid_in IN Number, subjectid_in IN Number, score_in IN Number);
FUNCTION Check_exist_Score(mainid_in IN Number, subjectid_in IN Number) RETURN NUMBER;
FUNCTION Check_exist_Failure(mainid_in IN Number, subjectid_in IN Number) RETURN NUMBER;
END PLAY1PACKAGE;
--包体的创建
create or replace PACKAGE BODY PLAY1PACKAGE AS
--过程的创建
PROCEDURE Add_Score( mainid_in IN Number, subjectid_in IN Number, score_in IN Number) AS
name_var varchar2(20);
exist_var NUMBER;
BEGIN
name_var := 'name_' || to_char(mainid_in) || '_' || to_char(subjectid_in) || '_' || to_char(score_in);
DBMS_OUTPUT.PUT_LINE('NAME = ' || name_var);
IF score_in >=0 AND score_in <= 100 THEN
exist_var := Check_exist_Score(mainid_in, subjectid_in);
IF exist_var > 0 THEN
DBMS_OUTPUT.PUT_LINE('Update data');
UPDATE TAB_SCORE SET score = score_in
WHERE mainid = mainid_in
AND subjectid = subjectid_in
;
ELSE
DBMS_OUTPUT.PUT_LINE('Insert data');
INSERT INTO TAB_SCORE VALUES (mainid_in, name_var, subjectid_in, score_in);
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('SCORE = ' || score_in);
DBMS_OUTPUT.PUT_LINE('Data Error ......');
END IF;
END Add_Score;
--函数的创建
FUNCTION Check_exist_Score(mainid_in IN Number, subjectid_in IN Number) RETURN NUMBER AS
count_var NUMBER;
BEGIN
SELECT count(*) INTO count_var FROM tab_score
WHERE mainid = mainid_in
AND subjectid = subjectid_in
;
RETURN count_var;
END Check_exist_Score;
--函数的创建
FUNCTION Check_exist_Failure(mainid_in IN Number, subjectid_in IN Number) RETURN NUMBER AS
count_var NUMBER;
BEGIN
SELECT count(*) INTO count_var FROM tab_failure
WHERE mainid = mainid_in
AND subjectid = subjectid_in
;
RETURN count_var;
END Check_exist_Failure;
END PLAY1PACKAGE;
--触发器的创建
create or replace trigger add_after
after insert or update of mainid,subjectid,score on tab_score
referencing old as old_t new as new_t
for each row
declare
exist_var NUMBER;
begin
IF :new_t.score < 60 THEN
DBMS_OUTPUT.PUT_LINE('SCORE < 60');
exist_var := play1package.check_exist_failure(:new_t.mainid, :new_t.subjectid);
IF exist_var > 0 THEN
DBMS_OUTPUT.PUT_LINE('Update To TAB_FAILURE');
UPDATE TAB_FAILURE SET score = :new_t.score
WHERE mainid = :new_t.mainid
AND subjectid = :new_t.subjectid
;
ELSE
DBMS_OUTPUT.PUT_LINE('Insert Into TAB_FAILURE');
INSERT INTO TAB_FAILURE VALUES (:new_t.mainid, :new_t.subjectid, :new_t.score);
END IF;
END IF;
end;