Records

SQL> drop table myoracle;

  

表已删除。

  

SQL> create table zuser (id number(4) primary key, fn char(10), sn char(10));

  

表已创建。

  

SQL> create sequence seq_zid

2 minvalue 1

3 nomaxvalue

4 start with 1

5 increment by 1

6 nocycle

7 nocache;

  

序列已创建。

  

SQL> create or replace trigger tg_zid

2 before insert on zuser for each row when (new.id is null)

3 begin select seq_zid.nextval into:new.id from dual;

4 end;

5 /

  

触发器已创建

  

SQL> show err;

  

SQL> select seq_zid.nextval from dual;

  

NEXTVAL

----------

1

  

SQL> select seq_zid.currval from dual;

  

CURRVAL

----------

1

  

SQL> desc dual

名称 是否为空? 类型

----------------------------------------- -------- ----------------------------

  

DUMMY VARCHAR2(1)

  

SQL> select * from all_sequences where lower(SEQUENCE_NAME) like '%seq_zid%';

  

SQL> select * from dba_objects where lower(object_name) = 'dual';

  

开始使用SQL DEVELOPER

  

ALTER SEQUENCE seq_zid increment by -1;

ALTER SEQUENCE seq_zid MINVALUE 0;

SELECT seq_zid.nextval FROM dual;

ALTER SEQUENCE seq_zid increment by 1;

ALTER SEQUENCE seq_zid MINVALUE 1;

  

INSERT INTO zuser VALUES (null,'Zheng','Yan');

  

ID FN SN

---------------------- ---------- ----------

1 Zheng Yan

  

alter table zuser drop constraint SYS_C009773;

  

alter table zuser add constraint pk_id primary key (id);

  

select * from dba_constraints where lower(table_name) = 'zuser';

  

alter table zuser add (salary number);

update zuser set salary = 1000+ mod(id,3)*100;

select avg(salary) avg, sum(salary) sum, max(salary) max, min(salary) min from zuser;

select fn,salary, count(*) "# Cost" from zuser group by fn, salary;

  

define a = 'Zheng';

select * from zuser where upper(fn) = upper('&a'); --一般字符和日期类型要加单引号