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'); --一般字符和日期类型要加单引号