-
编辑 删除
ORACLE RETURNING 语句的使用方法
1.The RETURNING INTO clause allows us to return column values for rows affected by DML statements. The following test table is used to demonstrate this clause. DROP TABLE t1;DROP SEQUENCE t1_seq; CREATE TABLE t1 (ID NUMBER(10),DESCRIPTION VARCHAR2(50),CONSTRAINT t1_pk PRIMARY KEY (id)); CREATE SEQUENCE t1_seq; INSERT INTO t1 VALUES (t1_seq.nextval, 'ONE');INSERT INTO t1 VALUES (t1_seq.nextval, 'TWO');INSERT INTO t1 VALUES (t1_seq.nextval, 'THREE');COMMIT; 2.When we insert data using a sequence t
-
编辑 删除
oracle常用函数
1,decode: 其实是一个if函数 例: select decode(6-5,1,'a','b') from dual; --如果6-5=1那么返回a,否则返回b.2,nvl 如果exp1为null则返回exp2,否则返回exp1.例子:select nvl(null,'a') from dual; select nvl ('dd','a') from dual;3,rollup:汇总函数:把汇总出来的数据再汇总,如果两个值4,cube:把各个子项汇总5,trunc: 用来取整数，如果是时间的话就取时间的整点 例1：SQL> select trunc(sysdate) from dual; TRUNC(SYSDATE) ------------------- 2007-04-11 00:00:00 例2： SQL> select trunc(22
-
编辑 删除
显示数据库属性的函数
selectSYS_CONTEXT('USERENV','TERMINAL') terminal,SYS_CONTEXT('USERENV','LANGUAGE') language,SYS_CONTEXT('USERENV','SESSIONID') sessionid,SYS_CONTEXT('USERENV','INSTANCE') instance,SYS_CONTEXT('USERENV','ENTRYID') entryid,SYS_CONTEXT('USERENV','ISDBA') isdba,SYS_CONTEXT('USERENV','NLS_TERRITORY') nls_territory,SYS_CONTEXT('USERENV','NLS_CURRENCY') nls_currency,SYS_CONTEXT('USERENV','NLS_CALENDAR') nls_calendar,SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') nls_date_format,SYS_CONTEXT('USERENV','NLS_DAT
