写存储过程的总结

 1、 各种时间的获取sql：

SELECT TO_CHAR(SYSDATE, 'yyyy-mm-dd'),
         TO_CHAR(SYSDATE - 1, 'YYYY-MM-DD'),--昨天的日期
         TO_CHAR(TRUNC(SYSDATE, 'MM'), 'yyyy-mm-dd'),--本月的1号是几月1号
         TO_CHAR(TRUNC(NEXT_DAY(SYSDATE - 8, 1) + 1), 'yyyy-mm-dd'),--本周的周一是几号
         TO_CHAR(SYSDATE, 'mm-dd'), --几月几号
         TO_CHAR(SYSDATE, 'yyyy') --是哪年的日期
    INTO V_SYSDATE,
         V_TIME,
         V_TIME_MM,
         V_TIME_WW,
         V_TIME_SYSDATE,
         V_TIME_YYYY
    FROM DUAL;

 

 

 

2、创建临时表：

-- Create table
create global temporary table T_OPERATION_BEANAVIOR_TMP
(
  KEYID       VARCHAR2(11) not null,
  CURDATE     VARCHAR2(10),
  CURTIME     VARCHAR2(20),
  SERVICES_ID VARCHAR2(50),
  OBCODE      VARCHAR2(4),
  OBTIMES     NUMBER,
  UP_FROM     CHAR(1),
  OBDATE      VARCHAR2(10)
)
on commit delete rows;
-- Create/Recreate indexes
create index UN_T_O_B_TMP_OBDATE on T_OPERATION_BEANAVIOR_TMP (OBDATE);

 

 

3、存储过程中的insert语句：INSERT INTO  TMP
    SELECT * FROM   REPORT    WHERE OBDATE <= V_TIME;

 

4、存储过程中开隐式游标：FOR    V_TIME_TYPE    IN    (SELECT OBDATE, COUNT(1)
                        FROM T_OPERATION_BEANAVIOR_TMP
                       GROUP BY OBDATE) LOOP

                         END LOOP;

V_TIME_TYPE 为隐式游标。

 

5、存储过程中执行sql语句：

    EXECUTE IMMEDIATE 'SELECT TO_CHAR(SYSDATE,''' || V_TIME_TYPE ||
                    '''),TO_CHAR(SYSDATE,''' || V_TIME_TYPE ||
                    ''')-1 FROM DUAL'
    INTO V_TIME, V_TIME_TMP;

 

6、存储过程中的swith方法：

V_TIME_TMP := CASE
                  WHEN V_TIME_TMP < 10 THEN
                   '0' || V_TIME_TMP
                  ELSE
                   V_TIME_TMP
                END;

 

7、存储过程中有输出值时：

CREATE OR REPLACE PROCEDURE   过程名(     

                                            O_CURSOR       OUT TYPES.CURSORTYPE) IS

BEGIN

   V_SQL1 ：='select * from A';

   OPEN O_CURSOR FOR  V_SQL1;

END;

 

 

8、在oracle中赋初值：

SELECT 0 AS  COUNT_NUMBER ,0 AS OIL,(SELECT NAME FROM a ) AS  NAME FROM DUAL;

运行结果：COUNT_NUMBER     OIL      NAME

                                 0                          0           小红