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

存储过程

创建存储过程

CREATE PROCEDURE TEST
AS
BEGIN
    DBMS_OUTPUT.PUT_LINE('我的第一个过程！');
END;
--执行
--SERVEROUTPUT设置，想让DBMS_OUTPUT.PUT_LINE成功输出，需要把SERVEROUTPUT选项设置为ON的状态
SHOW SERVEROUTPUT
SET SERVEROUTPUT ON 
BEGIN 
    TEST;
END;
--还具体在关键词EXEC后面加上存储过程名来执行以及存在的存储过程

查看存储过程

SELECT * FROM USER_SOURCE WHERE NAME= 'TEST' ORDER BY LINE ;
--从视图USER_SOURCE中查询过程或函数时需要把名称大写

--查看存储过程TEST_ERR的错误
SHOW ERRORS PROCEDURE TEST_ERR;

无参存储过程

无参存储过程就是创建的存储过程不带任何参数，通常这种存储过程用做数据转换的几率比较大。

--把表productinfo中价格最低的3件产品的desperation字段设置为’促销商品‘
CREATE PROCEDURE PRODUCT_UPDATE_PRC
AS
BEGIN 
UPDATE PRODUCTINFO SET DESPERATION = '促销产品'
WHERE PRODUCTID IN
(
  SELECT PRODUCTID FROM
  ( SELECT * FROM PRODUCTINFO ORDER BY PRODUCTPRICE ASC )
  WHERE ROWNUM < 4
);
COMMIT;
END;

存储过程中使用游标

--要求把productinfo表中数据根据不同的产品类型分类把数据输出到屏幕
CREATE PROCEDURE PRODUCT_CUR_PRC
AS
cur_ctgy productinfo.category%TYPE;
cur_ctgyname categroyinfo.categroyname%TYPE;
cur_prtifo productinfo%ROWTYPE;

CURSOR cur_category
IS 
SELECT CATEGORY FROM PRODUCTINFO GROUP BY CATEGORY;

BEGIN 
    OPEN cur_category;
    LOOP
        FETCH cur_category;
        EXIT WHEN cur_category%NOTFOUND;
            SELECT CATEGORYINFO.CATEGROYNAME INTO cur_ctgyname
            FROM CATEGROYINFO
            WHERE CATEGROYID = cur_ctgy;
                IF SQL%FOUND THEN
                    DBMS_OUTPUT.PUT_LINE('---------------');
                    DBMS_OUTPUT.PUT_LINE(cur_ctgyname || ':');
                END IF;

            FOR my_prdinfo_rec IN
            (
                SELECT * FROM PRODUCTINFO WHERE CATEGORY = cur_ctgy
            )
                LOOP
                 DBMS_OUTPUT.PUT_LINE(
                                    '产品名称: ' || my_prdinfo_rec.PRODUCTNAME|| '产品价格: ' || my_prdinfo_rec.PRODUCTPRICE|| '产品数量：' || my_prdinfo_rec.QUANTITY
                                      );
                END LOOP;
        END LOOP;
    CLOSE cur_category;
END ;

存储过程中的DDL语句

有时候我们会在操作数据的时候使用临时表，而为了让存储过程根据有通用性，可以选择把创建临时表的步骤一并放到过程里。这样的操作会和前面介绍的两种示例写法有所不同，它会用到EXECUTE IMMEDIATE语句，存储过程中会使用它来执行DDL语句和动态SQL语句

--要求把各种不同类型的产品中价格最低的输入到临时表PRODUCTINFO_TMP(此表需要创建)中，并在其中DESPERATION字段注明“热销商品“，如果记录中价格低于20则表示数据有问题，需要输出到屏幕

CREATE PROCEDURE PRODUCT_TMEP_UPDATE_PRC
AS

pc_delestr  VARCHAR2(50);
pc_createstr    VARCHAR2(500);
pc_insrtstr VARCHAR2(500);
tabext VARCHAR2(10); --判断临时表是否存在中间变量

cur_ctgy productinfo.Category%TYPE;
cur_prtifo productinfo%ROWTYPE;

CURSOR cur_category
IS
SELECT CATEGORY FROM PRODICTINFO GROUP BY CATEGORY;

CURSOR cur_proinfo(ctgy varchar)
IS
SELECT * FROM 
(SELECT * FROM PRODUCTINFO WHERE CATEGORY = ctgy ORDER BY PRODUCTPRICE ASC)
WHERE ROWNUM < 2;

BEGIN 
    SELECT COUNT(1)INTO tabext
    FROM ALL_TABLES
    WHERE TABLE_NAME = 'PRODUCTINFO_TMP'

    pc_delestr:= 'DELECTE FROM PRODUCTINFO_TMP'

    pc_createstr := 'CREATE GLOBAL TEMPORARY TABLE PRODUCTINFO_TMP
    (
        PRODUCTID VARCHAR2(10) NOT NULL,
        PRODUCTNAME VARCHAR2(20),
        PRODUCTPRICE NUMBER(8，2),
        QUANTITY NUMBER(10),
        CATEGORY VARCHAR2(10),
        DESPERATION VARCHAR2(1000),
        ORIGIN  BARCHAR2(10)
    )On Commit Preserve Rows';

     if tabext=0 then  --不存在就创建一个
        EXECUTE IMMEDIATE pc_createrstr;
        DBMS_OUTPUT.PUT_LINE('创建临时表成功');
      else
        EXECUTE IMMEDIATE pc_delestr;
        DBMS_OUTPUT.PUT_LINE('删除记录完成');
      end if;

    OPEN cur_category;
    LOOP
      FETCH cur_category INTO cur_ctgy;
      EXIT WHEN cur_category%NOTFOUND;
            OPEN cur_proinfo(cur_ctgy);
                FETCH cur_proinfo INTO cur_prtifo;
                IF cur_proinfo%FOUND THEN
                    IF cur_prtifo.PRODUCTPRICE < 20 THEN --产品价格低于20的打印出来
                        DBMS_OUTPUT.PUT_LINE('产品ID：'||cur_prtifo.PRODUCTID||'产品名称：' ||cur_prtifo.PRODUCTNAME||' 产品价格：' || cur_prtifo.PRODUCTPRICE
                    );
                    ELSE    --非低于20价格的产品输入到临时表 
                    EXECUTE IMMEDIATE 'INSERT INTO PRODUCTINFO_TMP(PRODUCTID,PRODUCTNAME.PRODUCTPRICE,QUANTITY,CATEGORY,DESPERATION,ORIGIN) VALUES (...||cur_prtifo.PRODUCTID||...,...||cur_prtifo.PRODUCTNAME||...,...||cur_prtifo.PRODUCTPRUCE||...,...||cur_prtifo.QUANTITY||...,...||cur_prtifo.CATEGORY||...,...||cur_prtifo.DESPERATION||...,...||cur_prtifo.ORIGIN||...)';
                    END IF;
                END IF;
            CLOSE cur_proinfo;
        END LOOP;
            COMMIT;
        CLOSE cur_category;
        EXECUTE IMMEDIATE 'UPDATE PRODUCTINFO_TMP SET DESPERATION = ''热销商品''',
     END;