关于存储过程、函数、包、触发器的演习1

来源：互联网发布：淘宝代理怎么发货编辑：程序博客网时间：2024/06/15 13:07

--包的创建

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;

0 0