oracle proceduce

来源:互联网 发布:vscode调试安卓 编辑:程序博客网 时间:2024/06/11 14:09
CREATE OR REPLACE PROCEDURE JY2_test(SegMent           in char,
                                     FAVOURABLE_EXTENT IN NUMBER,
                                     抵用金额          out number) as

  抵用金额0 number(8, 2);

  STR VARCHAR2(1000);

begin

  STR := 'SELECT sum(T.STD_PRICE)
         FROM ANNOUNCED_FARE T,
        (SELECT MAX(A.S_CARQ) AS S_CARQ, SEGMENT_CODE
           FROM ANNOUNCED_FARE A
          WHERE A.CARRIER_CODE = ''FM''
            AND A.SEGMENT_CODE IN (' || SegMent || ')
          GROUP BY A.SEGMENT_CODE) B
  WHERE T.S_CARQ = B.S_CARQ
    AND T.SEGMENT_CODE = B.SEGMENT_CODE
    AND T.CARRIER_CODE = ''FM''';

  /*  SELECT sum(T.STD_PRICE)
   into 抵用金额0
   FROM ANNOUNCED_FARE T,
        (SELECT MAX(A.S_CARQ) AS S_CARQ, SEGMENT_CODE
           FROM ANNOUNCED_FARE A
          WHERE A.CARRIER_CODE = ' FM '
            AND A.SEGMENT_CODE IN (SegMent)
          GROUP BY A.SEGMENT_CODE) B
  WHERE T.S_CARQ = B.S_CARQ
    AND T.SEGMENT_CODE = B.SEGMENT_CODE
    AND T.CARRIER_CODE = ' FM ';*/
/*不可以按照上述红色字体的方式去写,不可执行,执行无结果*/

  execute immediate STR
    into 抵用金额0;

  IF FAVOURABLE_EXTENT <> 0 THEN
    抵用金额 := 抵用金额0 * (1 - FAVOURABLE_EXTENT);
  else
    抵用金额 := 抵用金额0;
  END IF;

end JY2_test;

'SHAPEK','PEKSHA'