常用sql语句(2)
end case;
end tr_cha;
/
insert into zm_id values(2,’CINA’);
insert into zm_id(zm) values(’CHINA’);
select * from zm_id;
delete from zm_id;
——————————————————————————-
——————————————————————————-
– trigger用例
——————————————————————————-
– 表element_type的触发器
CREATE TABLE ELEMENT_TYPE (
ELEMENT_TYPE_ID NUMBER not null,
ELEMENT_TYPE_CODE VARCHAR2(6),
ELEMENT_TYPE_NAME VARCHAR2(20),
ELEMENT_TYPE_DESC VARCHAR2(100),
PARENT_TYPE_CODE NUMBER,
ELEMENT_GROUP_ID NUMBER,
CREATED_BY VARCHAR2(20),
CREATION_DATE DATE,
LAST_UPDATE_BY VARCHAR2(20),
LAST_UPDATE_DATE DATE,
START_ACTIVE_DATE DATE,
END_ACTIVE_DATE DATE,
ACTIVE_STATUS VARCHAR2(1),
CONSTRAINT PK_ELEMENT_TYPE PRIMARY KEY (ELEMENT_TYPE_ID)
);
——————————————————————————-
— 1.向表element_type插入一条记录之后，自动将当前系统时间插入到此记录的创建
– 时间(creation_date), 最后更新时间(last_update_date)
— 2.更新时，在更新之后，将当前系统时间插入到更新记录的最后更新时间
– (last_update_date)
— 3.插入或更新时，如果有效状态变为有效(0)，将当前系统时间插入到对应记录的
– 生效时间(start_active_date)；如果变为无效(1)，则将当前系统时间插入到
– 对应记录的失效时间(end_active_date)
CREATE OR REPLACE TRIGGER tr_element_type
BEFORE INSERT OR UPDATE
ON element_type
FOR EACH ROW
BEGIN
CASE
WHEN INSERTING THEN
:new.creation_date := sysdate;
:new.last_update_date := sysdate;
IF :NEW.active_status = ‘1′ THEN — 1 means active
:new.start_active_date := SYSDATE;
ELSIF :NEW.active_status = ‘0′ THEN — 0 means in-active
:new.END_active_date := SYSDATE;
END IF;
WHEN UPDATING THEN
:new.last_update_date := sysdate;
IF :NEW.active_status = ‘1′ THEN — 1 means active
:new.start_active_date := SYSDATE;
ELSIF :NEW.active_status = ‘0′ THEN — 0 means in-active
:new.END_active_date := SYSDATE;
END IF;
END CASE;
END; –end tr_element_type
/
————————————————————-
CREATE OR REPLACE TRIGGER tr_element_type
AFTER INSERT OR DELETE
ON element_type
FOR EACH ROW
BEGIN
raise_application_error(-20001, ‘asdfsdfsadfsa’);
END; –end tr_element_type
— 测试
select * from user_triggers;
drop trigger tr_element_type;
SELECT * FROM element_type;
delete from element_type;
INSERT INTO element_type(element_type_id,start_active_date)
VALUES (element_type_sequence.NEXTVAL,SYSDATE);
INSERT INTO ELEMENT_TYPE
VALUES(element_type_sequence.NEXTVAL, ‘ct0001′,’客户类型’,'列出客户类型，如：普通客户…’,
0, 0,’Administrator’,NULL,’Administrator’,NULL,NULL,NULL,’0′);
INSERT INTO ELEMENT_TYPE
VALUES(element_type_sequence.NEXTVAL, ‘ct0001′,’客户类型’,'列出客户类型，如：普通客户…’,
0, 0,’Administrator’,NULL,’Administrator’,NULL,NULL,NULL,’1′);
update element_type set active_status = ‘0′ where element_type_code = ‘ct0001′;
——————————————————————————-
select table_name from user_tables;
– 更改用户密码
alter user hr identified by hr;
——————————————————————————-
–将用户的表转移到其它的表空间
–(1)按用户或其它方式exp导出所有的数据
EXP OWNER=user1 FILE=user1db.DMP
EXP OWNER=crm FILE=sr2_crm.DMP
–(2)如果在原实例更换表空间，先删除该用户的所有表。
select ‘drop table ‘||user_tables||’;’ from user_tables; 生成删除表的语句 也可用enterprise manager删除所有表
–(3)利用imp创建表结构脚本
IMP FULL=Y FILE=user1db.DMP INDEXFILE=user1.SQL
–此步骤不导入数据，但将user1db.DMP中user1用户的表结构、索引等信息生成为user1.SQL。
–去掉user1.SQL脚本程序中所有的注释和引号，然后将脚本中所有的表空间修改为指定转移的表空间。
–(4)调用执行user1.SQL脚本程序。在新指定的表空间创建所有的表和索引等。
–(5)导入数据。
IMP FULL=Y FILE=user1db.DMP IGNORE=Y
IMP FULL=Y INDEXES=N FILE=user1db.DMP IGNORE=Y 若出现索引导入问题，可用INDEXES=N不导入索引。
imp market/market@orawww160_55 FROMUSER=MARKETSYS INDEXES=N FILE=EXPDAT.DMP IGNORE=Y LOG=Imp1.LOG tables=(doctrack,newsinfo)
select ‘drop table ‘|| TABLE_NAME ||’;’ from user_tables;
——————————————————————————-
– 组内排序
select * from (select row_number() over(order by cust_no ) rn,t.* from t_customerinfo t)
where rn>3 and rn<=5
select * from ( select c导出
ust_name,rn from (select row_number() over(order by cust_no ) rn,t.* from t_customerinfo_1 t)
where rn>(select count(*) from t_customerinfo_1)/2-2 and rn<=(select count(*) from t_customerinfo_1))
where rownum<=5；
create table t_customerinfo_1 as select * from t_customerinfo
— 针对oracle8.1.7.0.0, 用户scott, 表emp， 进行测试
select * from emp;
select rownum from emp;
select deptno,row_number() over(partition by deptno order by sal) from emp order by deptno;
select deptno,rank() over (partition by deptno order by sal) from emp order by deptno;
select deptno,dense_rank() over(partition by deptno order by sal) from emp order by deptno;
select deptno,ename,sal,lag(ename,1,null) over(partition by deptno order by ename) from emp order by deptno;
select deptno,ename,sal,lag(ename,2,’example’) over(partition by deptno order by ename) from emp order by deptno;
———————————————————————————
– 插入日期
–你可以先在你的sql plus中看看你的日期格式是什么样子的
select sysdate from dual;
–然后你插入的时候可以按照你的系统日期格式插入，不需要转换，要不然你就自己指定格式转换插入
select sysdate from dual;
SYSDATE
———
26-AUG-04
create table tmp(rq date);
insert into tmp(rq) values(’26-AUG-04′);
insert into tmp(rq) values(to_date(’2004-08-26′,’yyyy-mm-dd’));
———————————————————————————
select table_name from user_tables;
drop table t_a;
drop table t_b;
drop table t_c;
drop table t_d;
drop table t_e;
DROP SEQUENCE t_a_sequence;
CREATE SEQUENCE sr_call_back_sequence
INCREMENT BY 1 — 每次加几个
START WITH 100001 — 从1开始计数
NOMAXVALUE — 不设置最大值
NOCYCLE — 一直累加，不循环
NOCACHE;
create table t_a(id number,
code varchar2(20),
name varchar2(20),
c_id number,
d_num number
);
create table t_b(code varchar2(20),
name varchar2(20),
name2 varchar2(20),
name3 varchar2(20)
);
create table t_c(c_id number,
name2 varchar2(20)
);
create table t_d(d_num number,
name3 varchar2(20)
);
create table t_e(e_num number,
name varchar2(20)
);
insert into t_b values(’tb_code1′, ‘name1′, ‘aaa’, ‘xxx’);
insert into t_b values(’tb_code2′, ‘name2′, ‘bbb’, ‘yyy’);
insert into t_b values(’tb_code3′, ‘name3′, ‘ccc’, ‘zzz’);
insert into t_b values(’tb_code4′, ‘name4′, ‘ddd’, ‘www’);
insert into t_c values(11, ‘bbb’);
insert into t_c values(22, ‘ccc’);
insert into t_d values(888, ‘zzz’);
insert into t_d values(999, ‘www’);
- 最新评论