首页
网站首页
公司简介
资讯中心
推荐内容
返回顶部
Oracle关于时间,使用游标
发布时间:2019-12-25 16:24
浏览次数:

1、安装glibc兼容包,找到以下rpm文件安装

此函数在写一些触发器和函数的时候非常有用处。

代码如下:create or replace PROCEDURE proceudre_name AS BEGIN DECLARE sPara VARCHAR(50); sPara2 VARCHAR(50); CURSOR c_cursor IS SELECT ID,Name FROM Temp_table; BEGIN OPEN c_cursor; LOOP FETCH c_cursor INTO sPara, sPara2; EXIT WHEN c_cursor%NOTFOUND; BEGIN --just do what you want. END; END LOOP; CLOSE c_cursor; END; END;

在oracle中有很多关于日期的函数,如:

compat-glibc-6.2-2.1.3.2.rpm

用法:

1、add_months()用于从一个日期值增加或减少一些月份

compat-libs-6.2-3.rpm

SELECT sys_context(USERENV, ) FROM dual;

date_value:=add_months(date_value,number_of_months)

compat-egcs-6.2-1.1.2.14.rpm

第二个参数的可选值:

例:

2、修改你的.bash_profile加入以下两行

AUTHENTICATION_DATAData being used to authenticate the login user. For X.503 certificate authenticated sessions, this field returns the context of the certificate in HEX2 format.Note: You can change the return value of the AUTHENTICATION_DATA attribute using the length parameter of the syntax. Values of up to 4000 are accepted. This is the only attribute of USERENV for which Oracle implements such a change.AUTHENTICATION_TYPEHow the user was authenticated:DATABASE: username/password authenticationOS: operating system external user authenticationNETWORK: network protocol or ANO authenticationPROXY: OCI proxy connection authenticationBG_JOB_IDJob ID of the current session if it was established by an Oracle background process. Null if the session was not established by a background process.CLIENT_INFOReturns up to 64 bytes of user session information that can be stored by an application using the DBMS_APPLICATION_INFO package.CURRENT_SCHEMAName of the default schema being used in the current schema. This value can be changed during the session with an ALTER SESSION SET CURRENT_SCHEMA statement.CURRENT_SCHEMAIDIdentifier of the default schema being used in the current session.CURRENT_USERThe name of the user whose privilege the current session is under.CURRENT_USERIDUser ID of the user whose privilege the current session is under.DB_DOMAINDomain of the database as specified in the DB_DOMAIN initialization parameter.DB_NAMEName of the database as specified in the DB_NAME initialization parameter.ENTRYIDThe available auditing entry identifier. You cannot use this option in distributed SQL statements. To use this keyword in USERENV, the initialization parameter AUDIT_TRAIL must be set to true.EXTERNAL_NAMEExternal name of the database user. For SSL authenticated sessions using v.503 certificates, this field returns the distinguished name (DN) stored in the user certificate.FG_JOB_IDJob ID of the current session if it was established by a client foreground process. Null if the session was not established by a foreground process.HOSTName of the host machine from which the client has connected.INSTANCEThe instance identification number of the current instance.IP_ADDRESSIP address of the machine from which the client is connected.ISDBATRUE if you are logged on as SYS.LANGThe ISO abbreviation for the language name, a shorter form than the existing LANGUAGE parameter.LANGUAGEThe language and territory currently used by your session, along with the database character set, in the form:language_territory.characterset.NETWORK_PROTOCOLNetwork protocol being used for communication, as specified in the PROTOCOL=protocol portion of the connect string.NLS_CALENDARThe current calendar of the current session.NLS_CURRENCYThe currency of the current session.NLS_DATE_FORMATThe date format for the session.NLS_DATE_LANGUAGEThe language used for expressing dates.NLS_SORT BINARYor the linguistic sort basis.NLS_TERRITORYThe territory of the current session.OS_USEROperating system username of the client process that initiated the database session.PROXY_USERName of the database user who opened the current session on behalf of SESSION_USER.PROXY_USERIDIdentifier of the database user who opened the current session on behalf of SESSION_USER.SESSION_USERDatabase user name by which the current user is authenticated. This value remains the same throughout the duration of the session.SESSION_USERIDIdentifier of the database user name by which the current user is authenticated.SESSIONIDThe auditing session identifier. You cannot use this option in distributed SQL statements.TERMINALThe operating system identifier for the client of the current session. In distributed SQL statements, this option returns the identifier for your local session. In a distributed environment, this is supported only for remote SELECT statements, not for remote INSERT, UPDATE, or DELETE operations.(The return length of this parameter may vary by operating system.)

SQL select add_months(sysdate,12) "Next Year" from dual;

export LD_ASSUME_KERNEL=2.2.5

Next Year

. /usr/i386-glibc21-linux/bin/i386-glibc21-linux-env.sh


3、本文介绍的方法适用于其他glibc不兼容的问题。

13-11月-04

如果您在安装Oracle的过程中还有其他问题,请和作者联系。

SQL select add_months(sysdate,112) "Last Year" from dual;

作者Email:fancao0515@0451.com

Last Year


13-3月 -13

SQL

2、current_date()返回当前会放时区中的当前日期

date_value:=current_date

SQL column sessiontimezone for a15

SQL select sessiontimezone,current_date from dual;

SESSIONTIMEZONE CURRENT_DA


+08:00 13-11月-03

SQL alter session set time_zone='-11:00'

2 /

会话已更改。

SQL select sessiontimezone,current_timestamp from dual;

SESSIONTIMEZONE CURRENT_TIMESTAMP


-11:00 12-11月-03 04.59.13.668000 下午 -11:

00

SQL

3、current_timestamp()以timestamp with time zone数据类型返回当前会放时区中的当前日期

timestamp_with_time_zone_value:=current_timestamp([timestamp_precision])

SQL column sessiontimezone for a15

SQL column current_timestamp format a36

SQL select sessiontimezone,current_timestamp from dual;

SESSIONTIMEZONE CURRENT_TIMESTAMP


+08:00 13-11月-03 11.56.28.160000 上午 +08:

00

SQL alter session set time_zone='-11:00'

2 /

会话已更改。

SQL select sessiontimezone,current_timestamp from dual;

SESSIONTIMEZONE CURRENT_TIMESTAMP


-11:00 12-11月-03 04.58.00.243000 下午 -11:

00

SQL

4、dbtimezone()返回时区

varchar_value:=dbtimezone

SQL select dbtimezone from dual;

DBTIME

友情链接: 网站地图
Copyright © 2015-2019 http://www.nflfreepicks.net. 新葡萄京娱乐场网址有限公司 版权所有