oracle基本
来源:互联网 发布:北京 java培训完 工资 编辑:程序博客网 时间:2024/06/05 06:37
1.oracle中创建序列:
CREATE SEQUENCE "CONFIGUSER"."AALARM_DATA_AALARM_SEQUENC_SEQ"
MINVALUE 1
MAXVALUE 999999999999999999999999
INCREMENT BY 1
START WITH 1
CACHE 20
NOORDER
NOCYCLE ;
2.oracle11g 中创建触发器:知道:new 和 :old变量的用法 标准的触发器
create or replace
TRIGGER aalarm_data_AALARM_SEQUEN_TRIG BEFORE INSERT OR UPDATE ON aalarm_data
FOR EACH ROW
DECLARE
v_newVal NUMBER(12) := 0;
v_incval NUMBER(12) := 0;
BEGIN
IF INSERTING AND :new.AALARM_SEQUENCE IS NULL THEN
SELECT aalarm_data_AALARM_SEQUENC_SEQ.NEXTVAL INTO v_newVal FROM DUAL;
-- If this is the first time this table have been inserted into (sequence == 1)
IF v_newVal = 1 THEN
--get the max indentity value from the table
SELECT NVL(max(AALARM_SEQUENCE),0) INTO v_newVal FROM aalarm_data;
v_newVal := v_newVal + 1;
--set the sequence to that value
LOOP
EXIT WHEN v_incval>=v_newVal;
SELECT aalarm_data_AALARM_SEQUENC_SEQ.nextval INTO v_incval FROM dual;
END LOOP;
END IF;
-- assign the value from the sequence to emulate the identity column
:new.AALARM_SEQUENCE := v_newVal;
END IF;
ENDcreate or replace
trigger DELETE_DEVICE_TRIGGER
before delete on device for each row
begin
Delete from device where PAR_DEVICE_ID = :OLD.DEVICE_ID;
Delete from data_object where DEVICE_ID_PARENT = :OLD.DEVICE_ID;
end;
3.oracle中创建存储过程:
create or replace
PROCEDURE move_alarm_to_history as
BEGIN
INSERT INTO HISTORYUSER.alarm_history SELECT * FROM AALARM_DATA WHERE sysdate-COALESCE(ALARM_END,sysdate)>2;
DELETE FROM AALARM_DATA WHERE sysdate-COALESCE(ALARM_END,sysdate) > 2;
commit;END move_alarm_to_history;
create or replace
procedure get_alarm_count as
v_newVal NUMBER(12) := 0;
begin
SELECT max(AALARM_SEQUENCE) INTO v_newVal FROM aalarm_data;
end get_alarm_count;
4.oracle表空间创建:
create tablespace t_spacename
datafile 'D:\janely\spaceName.dbf'--默认大写文件名:SPACENAME.DBF
size 100M AutoExtend On Next 10M Maxsize 2048M
extent management localsegment space management auto
5.oracle批量删除表
BEGIN FOR T IN (SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME LIKE 'MD_%' ) LOOP execute immediate 'drop table '||T.TABLE_NAME||' purge'; end loop;END;/查询出来要删除的表然后执行drop记录集。
- Oracle基本
- oracle基本
- Oracle 基本
- oracle基本
- oracle基本操作 oracle常用命令
- Oracle 的基本命令
- Oracle基本操作
- oracle的基本命令
- ORACLE的基本语法
- Oracle基本操作
- oracle基本操作语句
- oracle DBA 基本语句
- oracle基本操作语句
- Oracle中的基本SQL
- Oracle基本操作SQL
- Oracle 的基本命令
- Oracle基本术语
- ORACLE基本配置
- adriod HAL层 内核空间与用户空间内存共享
- oracle11g更改字符集AL32UTF8为ZHS16GBK
- 子串出现的个数
- 三个配置文件 listener.ora、sqlnet.ora、tnsnames.ora
- c# Winform 保存修改后的配置
- oracle基本
- 一个关于静态局部变量以及全局变量的例子
- 对'Consistent Gets',''Physical Reads'和'DB Block Gets'的理解和解释
- NDK的文档和例子
- 让人少奋斗10年的经验
- C++栈的实现
- Socket控件
- 如何在两台ubuntu系统电脑之间共享资料
- 查看operator new/delete() 函数, 在ubuntu下执行cat /usr/include/c++/4.4/new > new