Oracle数据库(十一)——存储过程下

有参存储过程

存储过程允许带有参数，参数的使用将增加存储过程的灵活性。存储过程中如果使用了参数，在执行存储过程时必须为其指定的参数。

--使用输入参数的存储过程
--根据输入的产品类型从表PRODUCTINFO中搜索符合要求的数据，并将其打印到屏幕
CREATE PROCEDURE PRODUCT_INTYPE_PRC (parm_ctgyname IN VARCHAR2)
AS
cur_ctgyid categroyinfo.categroyid%TYPE;
cur_prtifo productinfo%ROWTYPE;

BEGIN
    SELECT CATEGROYINFO.CATEGROYID INTO cur_ctgyid
    FROM CATEGROYINFO
    WHERE CATEGROYINFO.CATEGROYNAME = parm_ctgyname;
    --根据类型编码得到产品类型名称
        IF SQL%FOUND THEN 
            DBMS_OUTPUT.PUT_LINE('----------------');
            DBMS_OUTPUT.PUT_LINE(parm_ctgyname || ':');
        END IF;
        FOR my_prdinfo_rec IN
        (
          SELECT * FROM PRODUCTINFO WHERE CATEGORY = cur_ctgyid
        )
        LOOP
            DBMS_OUTPUT.PUT_LINE(
                                '产品名称： ' || my_prdinfo_rec.PRODUCTNAME || '产品价格： '|| my_prdinfo_rec.PRODUCTPRICE || '产品数量： ' || my_prdinfo_rec.QUANTITY
                                );
        END LOOP;
        EXCEPTION
        WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.PUT_LINE('没有数据： ');
        WHEN TOO_MANY_ROWS THEN
            DBMS_OUTPUT.PUT_LINE('数据过多： ');
END;

--使用参数的默认值
--根据输入的产品类型查询对应产品类型编码的功能，有默认值

--step 1 穿件函数，函数将返回”雨具“字符串
CREATE OR REPLACE FUNCTION DEFT RETURN VARCHAR2
IS 
BEGIN 
    DBMS_OUTPUT.PUT_LINE("---已进入函数----");
    DBMS_OUTPUT.PUT_LINE("默认类型是雨具");
    RETURN '雨具'
END DEFT;
--STEP2 创建存储过程
CREATE PROCEDURE PRODUCT_INTYPE_DEFT_PRC(parm_ctgyname IN VARCHAR2 DEFAULT DEFT() )
AS
cur_ctgyid categroyinfo.categroyid%TYPE;

BEGIN 
    SELECT CATEGROYINFO.CATEGROYID INTO cur_ctgyid;
    FROM CATEGROYINFO
    WHERE CATEGROYINFO.CATEGROYNAME = parm_ctgyname;

    IF SQL%FOUND THEN
        DBMS_OUTPUT.PUT_LINE('---------------');
        DBMS_OUTPUT.PUT_LINE(parm_ctgyname || ':');             END IF;        
    FOR my_prdinfo_rec IN        
    (          
      SELECT * FROM PRODUCTINFO WHERE CATEGORY = cur_ctgyid     )        
    LOOP            
        DBMS_OUTPUT.PUT_LINE(                                                               '产品名称： ' || my_prdinfo_rec.PRODUCTNAME || '产品价格： '|| my_prdinfo_rec.PRODUCTPRICE || '产品数量： ' || my_prdinfo_rec.QUANTITY                                
                            );        
    END LOOP;        
    EXCEPTION        
    WHEN NO_DATA_FOUND THEN                                         DBMS_OUTPUT.PUT_LINE('没有数据： ');        
    WHEN TOO_MANY_ROWS THEN                                         DBMS_OUTPUT.PUT_LINE('数据过多： ');
    END;

--输出类型参数的使用
--step1 创建被调用的存储过程，该过程根据输入的产品类型查询出对应的产品类型编码功能，并将编码放到输出参数中

CREATE PROCEDURE PRODUCT_OUTTYPE_PRC(parm_ctgyname IN VARCHAR2, parm_ctgyid OUT VARCHAR2)
AS
BEGIN 
    SELECT CATEGROYINFO.CATEGROYID INTO parm_ctgyid
    FROM CATEGROYINFO
    WHERE CATEGROYINFO.CATEGROYNAME = parm_ctgyname;
        IF SQL%FOUND THEN
            DBMS_OUTPUT.PUT_LINE('传出参数是： ' || parm_ctgyid);
        END IF;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN                                         DBMS_OUTPUT.PUT_LINE('没有数据： ');        
    WHEN TOO_MANY_ROWS THEN                                         DBMS_OUTPUT.PUT_LINE('数据过多： ');
    END;

--STEP2创建调用存储过程，该过程根据输入的产品类型以及价格从表PRODUCTINFO中查询符合要求的数据并输出
CREATE PROCEDURE PRODUCT_CLOUTTYPE_PRC(parm_ctgyname IN VARCHAR2,parm_pric NUMBER)
AS
cur_ctgyid categroyinfo.categoryid%TYPE;
cur_prtifo productinfo%ROWTYPE;

BEGIN
        PRODUCT_OUTTYPE_PRC(parm_ctgyname,cur_ctgyid);
        IF SQL%FOUND THEN
            DBMS_OUTPUT.PUT_LINE('--------')；
            DBMS_OUTPUT.PUT_LINE(parm_ctgyname || '对应的编码是:' || cur_ctgyid); 
        END IF;
         FOR my_prdinfo_rec IN 
            (
              SELECT * FROM PRODUCTINFO
              WHERE CATEGORY = cur_ctgyid
              AND PRODUCTINFO.PRODUCTPRICE < parm_pric
            )
            LOOP
                DBMS_OUTPUT.PUT_LINE(
                                '产品名称： ' || my_prdinfo_rec.PRODUCTNAME || '产品价格： '|| my_prdinfo_rec.PRODUCTPRICE || '产品数量： ' || my_prdinfo_rec.QUANTITY                                
                            );      
            END LOOP;
END;

--使用输入输出类型的参数
--step1 创建被调用的存储过程，该过程提供根据输入的产品类型编号和降价比例对表PRODUCTINFO的数据进行降价修改，并返回修改的记录数
CREATE PROCEDURE PRODUCT_INOUTTYPE_PRC(parm_ctgyid IN VARCHAR2, parmparm_pric  IN OUT NUMBER)
AS
BEGIN 
    UPDATE PRODUCTINFO
    SET PRODUCTPRICE = PRODUCTINFO.PRODUCTPRICE*(1-parmparm_pric)
    WHERE PRODUCTINFO.CATEGORY = parm_ctgyid;
    IF SQL%FOUND THEN 
        parmparm_pric := SQL%ROWCOUNT;
    END IF;
END PRODUCT_INOUTTYPE_PRC;
--parmparm_pric 输入时表示降价比例输出时表示修改语句修改的记录数
--step2 创建调用存储构成，该过程输入产品类型名称，根据产品类型名称查询出产品类型编码并调用PRODUCT_INOUTTYPE_PRC存储过程，最终输出修改的记录数
CREATE PROCEDURE PRODUCT_CLINOUTTYPE_PRC(parm_ctgyname IN VARCHAR2)
AS
cur_ctgyid categroyinfo.categroyid%TYPE;
cur_pric number;

BEGIN 
    SELECT CATEGROYINFO.CATEGROYID INTO cur_ctgyid
    FROM CATEGROYINFO
    WHERE CATEGROYINFO.CATEGROYNAME = parm_ctgyname;

    PRODUCT_INOUTTYPE_PRC(cur_ctgyid,cur_pric);
    IF cur_pric > 0 THEN
        DBMS_OUTPUT.PUT_LINE('共修改' || cur_pric || '条记录。');
    END IF;

    EXCEPTION
    WHEN NO_DATA_FOUND THEN                                         DBMS_OUTPUT.PUT_LINE('没有数据： ');        
    WHEN TOO_MANY_ROWS THEN                                         DBMS_OUTPUT.PUT_LINE('数据过多： ');
    END;