第2章 sql查询、sql函数与数据库对象

用scott模式的emp表，每次的查询语句都必须如下所写。 SQL > Select * from scott . emp ； //每次在表名前都要加该表的模式名scott。 为了简化，itjob就需要为scott的emp表建立一个同义词，但要建立同义词，首先得让scott用户授权给itjob才行，这样，我们先给itjob用户授权，让他能够访问scott的表emp。怎么做呢？首先以scott用户身份登录数据库，执行以下命令。 SQL > grant all on emp to itjob ； //这样，scott就授给itjob用户权限了。

再以itjob用户身份登录数据库，创建同义词，执行以下命令。 SQL > create synonym emp for scott . emp； //创建属于itjob用户的同义词emp，但该同义词只能被itjob使用，所以是私有同义词。

这样，以后itjob用户想访问scott用户的emp表，就可以执行以下命令。 SQL > Select * from emp ； //表名前不要模式名scott。

2、公有同义词。

公有同义词可以被所有的数据库用户访问。公有同义词可以隐藏表的所有者，并降低sql 语句的复杂性。要创建公有同义词，用户必须具有create public synonym系统权限。 创建公有同义词的语法如下。 Create [or replace] public synonym synonym_name for [schema.]object_name;

比如：scott用户为了想让自己的表emp能被任何其他用户访问到，他就可以创建一个公有模式，其他用户就可以直接访问该表了，而不需要知道该表是哪个用户模式。 在scott登录数据库系统后，执行下列语句。 SQL> create public synonym emp_syn for scott.emp

3、一个系统中，可能存在一些同义词，怎么知道有哪些同义词存在呢，它们又分别是针对哪些数据库对象建立的同义词呢？可以通过查询视图USER_SYNONYMS来查看同义词的详细信息。如： SQL > select * from USER_SYNONYMS

4、删除同义词。 格式： SQL > drop [public] synonym [schema.]synonym_name； 八、序列。

序列非常类似sql server中的标识列。

序列是用来生成唯一、连续的整数的数据库对象，序列通常用来自动生成主键，序列可以按升序排列，也可以按降序排列。 1、创建序列，格式如下。 Create sequence sequence_name [ start with integer ] [ increment by integer ] [ maxvalue integer | nomaxvalue ] [ minvalue integer | nominvalue] [cycle | nocycle ] [ cache integer | nocache]; 其中：

start with：指定要生成的第一个序列号，对于升序序列，其默认值为序列的最小值，对于降

序序列，其默认值为序列的最大值。类似sql server的标识种子。

Increment by：指定序列号之间的间隔，其默认值为1。如果增量为正，则生成的序列按升

序排列，如果增量为负，则按降序排列。类似sql server的标识增量。

Maxvalue：指定序列可以生成的最大值。默认是1027

Minvalue：指定序列的最小值。默认是-10-26 。Minvalue必须小于或等于start_with的值，

并小于maxvalue。

Nomaxvalue：表示是否设置最大值。如果设置了该参数，oracle 将升序序列的最大值设置为

1027 ，将降序序列的最大值设置为-1。,这是默认选项。

Nominvalue：表示是否设置最小值。如果设置该参数，oracle将升序序列的最小值设置为1，

将降序序列的最小值设置为-1026 ，这是默认选项。

Cycle：指定序列在达到最大值或最小值后，将从头开始生成值。对于升序，如果指定

nominvalue参数，就从1开始，否则，就根据minvalue参数从头开始。对于降序，如果指定了nomaxvalue参数，就从-1开始，否则，就根据maxvalue参数从头开始。

Nocycle：指定序列在达到最大值或最小值后，将不能再继续生成值。这是默认选项。 Cache：使用cache选项，系统预先分配一组默认序列号放到缓存中，可以加快速度。 Nocache：不使用缓存。如果没有用cache和nocache，系统默认缓冲20个序列号。

比如：创建一个序列号。 SQL>create sequence toys_seq Start with 10 Increment by 1 Maxvalue 2000 Nocycle Cache 30;

2、使用序列。

序列创建完成后，可以通过currval和nextval伪列来访问该序列的值。可以从伪列中选

择值，但不能操纵它们的值。下面分别说明currval和nextval。

Currval：保存有序列当前的值，即最后一次引用nextval时返回的值。

Nextval：创建序列后第一次使用nextval时，将返回该序列的初始值。以后在引用nextval

时，将使用increment by 子句的值来增加序列值，并返回这个新值。

比如： SQL > insert into toys (toyed,toyname,toyprice) Values (toys_seq.nextval , ?twenty? , 25) ; SQL > insert into toys (toyed,toyname,toyprice) Values (toys_seq.nextval , ?mary? , 125) ;

也可以随时查询当前序列的值。 比如： Select toys_seq.currval from dual ；

3、修改序列。

序列一旦创建，除了不能修改序列的起始值外，其余都可修改。格式如下。 Alter sequence sequence_name [ increment by integer ] [ maxvalue integer | nomaxvalue ] [ minvalue integer | nominvalue] [cycle | nocycle ] [ cache integer | nocache];

比如： SQL>alter sequence toys_seq Maxvalue 5000 Cycle; 可以通过查询USER_SEQUENCES的字典视图，获取用户所创建的序列的详细信息。

4、删除序列。 格式如下： SQL > drop sequence toys_seq;

作业

1、数据定义语言是用于______的方法。

A) 确保数据的准确性。 B)定义和修改数据结构。 C) 查看数据。 D)删除和更新数据。

2、emp表包含下面这些字段 Ename varchar2(35) Salary number(8,2) Hire_date date 管理部门想要一份在公司工作了5年以上的员工名单。可显示所需结果的语句是____。 A) select ename from emp where sysdate – hire_date > 5 ; B) select ename from emp where hire_date – sysdate > 5 ;

C) select ename from emp where (sysdate –hire_date)/365 > 5 ; D) select ename from emp where (sysdate –hire_date)*365 > 5 ;

3、employees表的结构如下： Employee_id number primary key First_name varchar2(25) Last_name varchar2(25) Hire_date date

考虑下面的语句。 1．Create table new_emp (employee_id number , name varchar2(30) ); 2．Insert into new_emp select employee_id, last_name from employees; 3．Savepoint s2; 4．Delete from new_emp; 5．Rollback to s2; 6．Delete from new_emp where employee_id=180; 7．Update new_emp set name=?james?; 8．Rollback to s2; 9．Update new_emp set name=?james? where employee_id=180; 10．Rollback;

完成本次事务处理后，以下说法正确的是_______。 A)表中没有行。

B)有一个姓名为james的行。

C)不能多次回滚到相同的保存点。

D)由于已经删除了员工编号为180的记录，所以最后一次更新操作未更新任何行。

4、能为计算列sal*12生成别名annual salary的语句是________。 A)select ename , sal*12 ?annual salary? from emp;