ORACLE 存储过程

原创
2017/07/13 23:52
阅读数 84
  • 语法
CREATE [OR REPLACE ] PROCEDURE [ schema. ] procedure_name
  [parameter_name [[IN] datatype][{:= | DEFAULT} expression]
     | {OUT | IN OUT} [NOCOPY] datatype
  ][,...]
{IS | AS}
BODY;

1.简单无参存储过程

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;

2.存储过程中使用游标

CREATE PROCEDURE product_cur_prc
AS
   cur_ctgy       productinfo.category%TYPE;                              
   cur_ctgyname   productinfo.categoryname%TYPE;  
   cur_prtinfo    productinfo%ROWTYPE
   
   CURSOR cur_category
   IS
   SELECT category FROM productinfo GROUP BY category;

BEGIN
  OPEN cur_category;
    LOOP
      FETCH cur_category INTO cur_ctgy;
      EXIT WHEN cur_category%NOYFOUND;
    
      SELECT productinfo.categoryname INTO cur_ctgyname
      FROM productinfo
      WHERE categroyid = cur_ctgy;   --根据产品类型编码得到产品类型名称
          IF SQL%FOUND THEN
             DBMS_OUTPUT.PUT_LINE('---');
          END IF;
    END LOOP;
 CLOSE cur_category;

END;

3.存储过程中使用DDL语句:利用EXECUTE IMMEDIATE语句,来执行DDL语句和动态SQL语句.

CREATE PROCEDURE product_temp_update_prc
AS

 pc_delestr VARCHAR2(50);                             --删除临时表记录语句
 pc_createstr VARCHAR2(500);                          --创建临时表
 pc_insrtstr VARCHAR2(10);                            --向临时表中插入数据
 tabext VARCHAR2(10);                                 --用于判断临时表是否存在的中间变量

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

 CURSOR cur_category                                  --产品表中的产品类型游标
 IS 
 SELECT category FROM productinfo 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                          --判断临时表是否存在,存在为1,否则为0
 FROM ALL_TABLES
 WHERE TABLE_NAME = 'PRODUCTINFO_TEM';

  pc_delestr:='DELETE FROM productinfo_tem';

  pc_createstr:='CREATE GLOBAL TEMPORARY TABLE productinfo_tem
  (
    pruductid VARCHAR2(10) not null,
    productname VARCHAR2(20),
    productprice NUMBER(8,2)
  ) on commit preserve rows';

  IF tabext = 0 THEN                                 --不存在临时表则创建一个
    EXECUTE IMMEDIATE pc_createstr;
    DBMS_OUTPUT.PUTLINE('创建临时表成功!')
  ELSE 
    EXECUTE IMMEDIATE pc_delestr;
    DBMS_OUTPUT.PUTLINE('删除记录完成!')
  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%ROWFOUND THEN
              IF cur_prtifo.productprice <20 THEN     --产品价格低于20打印出来
                 DBMS_OUTPUT.PUTLINE('产品ID:' || cur_prtifo.productid);
              ELSE                                    --其他输入到临时表
                EXECUTE IMMEDIATE 'INSERT INTO PRODUCTINFO_TEM(
                   pruductid,
                   productname,
                   productprice
                )VALUES('''
                   ||cur_prtifo.pruductid
                   ||''','''
                   ||cur_prtifo.productname
                   ||''','''
                   ||cur_prtifo.productprice
                   ||''')';
              END IF;
           END IF;
      CLOSE cur_proinfo;
   END LOOP;
      COMMIT;
   CLOSE cur_category;
   EXECUTE IMMEDIATE 'UPDATE PRODUCTINFO_TEM SET DESPERATION = ''热销商品''';
END;

4.有参存储过程

CREATE PROCEDURE product_outtype_prc(parm_ctgyname IN VARCHAR2,parm_ctgyid OUT VARCHAR2)
AS
BEGIN
  SELECT categroyinfo.categroyid INTO parm_ctgyid
  FORM 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 product_outtype_prc;

 

展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部