oracle面试

小咪咪 2021-12-03 13:29 326阅读 0赞

# Oracle：全局数据库orcl #

#### 1.登陆超级管理员用户：system用户（dba） ####

#### 2.创建表空间 ####

–创建表空间  
create tablespace itheima  
datafile ‘c:\\itheima.dbf’–设置表空间存储位置  
size 100m         --设置表空间的大小  
autoextend on     --设置为自动扩展  
next 10m;  
  
–删除表空间  
drop tablespace itheima;

#### 3.创建用户 ####

–创建用户  
create user itheima --创建一个itheima的用户  
identified by itheima --给当前设置密码为itheima  
default tablespace itheima;–当前用户出生地

#### 4.给用户授权 ####

–给用户授权  
–oracle数据库中常用角色  
connect–连接角色，基本角色  
resource–开发者角色  
dba–超级管理员角色  
  
–给itheima用户授予dba角色  
grant dba to itheima;

#### 5.常用的数据类型 ####

1.varchar varchar2 字符串类型  
2.number(n,m) n代指总长度，m代指小数点的位数  
3.date 带有时间戳

#### 6.创建表的命令:操作表的基本单位是用户而不是数据库（mysql是数据库） ####

create table person( id number(10),pname varchar2(20) );

#### 7.表结构的修改 ####

—修改表结构  
—添加一列  
alter table person add (gender number(1));  
—修改列类型  
alter table person modify gender char(1);  
—修改列名称  
alter table person rename column gender to sex;  
—删除一列  
alter table person drop column sex;

#### 8.标准的SQL语句 ####

–删除  
delete from 表名 where 条件  
–修改  
update 表明 set 字段名1=字段值1，字段名2=字段值2…where 条件  
–插入  
–部分插  
insert into 表名(字段1，字段2…)values(字段1的值，字段2的值…)  
–全插  
insert into 表名 values(对应表中字段顺序的字段值);  
–查询  
select \* from 表名 where 条件 （排序 分组）  
单表查询  
多表的查询  
子查询  
虚表

#### 9.序列 ####

\----序列不真的属于任何一张表，但是可以逻辑和表做绑定。  
\----序列：默认从1开始，依次递增，主要用来给主键赋值使用。  
\----dual：虚表，只是为了补全语法，没有任何意义。  
create sequence s\_person;  
select s\_person.nextval from dual;  
  
nextval:查询序列的下一个值  
currval:查询序列当前的值

#### 10.Scott用户,密码默认是tiger ####

\----scott用户，密码tiger。  
–解锁scott用户  
alter user scott account unlock;  
–解锁scott用户的密码【此句也可以用来重置密码】  
alter user scott identified by tiger;  
–切换到scott用户下

#### 11.函数 ####

nvl(v1,v2):当v1为null时使用v2数据，如果v1不为null则继续使用v1数据  
nvl2(x,v1,v2):当x为空时，返回v2,非空时返回v1

#### 12.分组函数 ####

where :select 查询条件  
having：group by分组条件  
having永远不可能在where之前

#### 13.分页rownum ####

—rownum行号：当我们做select操作的时候，  
–每查询出一行记录，就会在该行上加上一个行号，  
–行号从1开始，依次递增，不能跳着走。  
\----emp表工资倒叙排列后，每页五条记录，查询第二页。  
\----rownum行号不能写上大于一个正数。  
select \* from(  
   select rownum rn, tt.\* from(  
         select \* from emp order by sal desc  
  ) tt where rownum<11  
) where rn>5  
  
  
  
–count:5  
–pageNum:1 1-5 2 6-10 3 11-15  
–(pageNum-1)*count+1  
–pageNum*count  
解决监听器启动失败问题：

cmd的命令行窗口下，输入lsnrctl start，回车即启动监听。  
再输入sqlplus /nolog,回车  
再输入 conn / as sysdba;回车  
再输入startup，回车.这步是启动oracle服务。再登录一下oracle就可以  
解决中文乱码问题：

#### 中文乱码问题解决 ####

##### 1.查看服务器端编码 #####

select userenv(‘language’) from dual;  
我实际查到的结果为:AMERICAN\_AMERICA.ZHS16GBK

##### 2.执行语句 select \* from V$NLS\_PARAMETERS #####

查看第一行中PARAMETER项中为NLS\_LANGUAGE 对应的VALUE项中是否和第一步得到的值一样。  
如果不是，需要设置环境变量.  
否则PLSQL客户端使用的编码和服务器端编码不一致,插入中文时就会出现乱码.

##### 3.设置环境变量 #####

计算机->属性->高级系统设置->环境变量->新建  
设置变量名:NLS\_LANG,变量值:第1步查到的值， 我的是 AMERICAN\_AMERICA.ZHS16GBK

##### 4.重新启动PLSQL,插入数据正常 #####

#### 工程中使用Oracle ####

##### 1.添加坐标ojdbc14是oracle10g #####

注意：远程仓库下载坐标失败，需要导入第三方架包到本地仓库  
ojdbc14.jar放在盘符下比如D：  
首先进入当前盘符/执行以下命令：  
mvn install:install-file -DgroupId=com.oracle -DartifactId=ojdbc14 -Dversion=1.10 -Dfile=ojdbc14.jar -Dpackaging=jar

##### 2.打开idea中setting设置，更新本地仓库 #####

##### 3.db.properties文件的配置 #####

jdbc.driver=oracle.jdbc.OracleDriver  
远程ip         端口号：全局数据库名称  
jdbc.url=jdbc:oracle:thin@192.168.181.128:1521:orcl  
jdbc.username=itheima  
jdbc.pwd=itheima

### 1.存储过程和函数 ###

存储过程重在处理数据，函数可以返回值。  
（1）存储过程是procedure用户定义的一系列sql语句的集合，涉及特定表或其他对象的任务，用户可以调用存储过程。  
（2）函数通常是数据库已定义的方法，它接收参数并返回某种类型的值并且不涉及特定用户表。  
（3）可以理解函数是存储过程的一种，都是预编译的（块语句每次运行都会编译 存储过程块 一次编译多次运行 效率更高）  
plsql块语句：  
Begin  
End  
存储过程块  
Create procedure prg\_add()  
As  
Begin  
End；  
（4）函数可以没有参数，但是一定要有一个返回值，存储过程可以没有参数，不需要返回值。  
（5）函数return返回值没有返回参数模式，存储过程通过out参数返回值，如果需要返回多个参数则建议使用存储过程（函数oracle 在函数可以使用in和out mysql不能使用out）  
（6）在sql数据操纵（DML）语句中只能调用函数而不能调用存储过程

### 2.存储过程的概念，优点（或特点），写一个简单的存储过程 ###

存储过程：是一组为了完成特定功能的SQL语句集,利用SQL Server所提供的T-SQL语言所编写的程序,经编译后存储在数据库中。  
优点：  
（1）执行速度快，存储过程只在创建时进行编译，以后每次执行不需要再重新编译，一般sql语句每执行一次就编译一次  
（2）存储过程可重复使用  
（3）安全性高（可设定只有某些用户才具有对指定存储过程的使用权）  
（4）当对数据库进行复杂操作时，可完成复杂的判断和比较复杂的运算，可用存储过程封装起来  
（5）易于维护和集中控制，当企业规则变化时在服务器中改变存储过程即可。无需修改 应用程序。  
简单的存储过程：  
create proc select\_query @year int  
as  
select \* from tmp where year=@year

### 3.触发器 ###

（1）触发器：触发器可以看成是一个特殊的存储过程，存储过程是要显示调用去完成，而触发器可以自动完成。比如：当数据库中的表发生增删改操作时，对应的触发器就可以执行对应的PL/SQL语句块  
（2）作用：维护表的完整性，记录表的修改来审计表的相关信息  
分为：  
DML触发器：当数据库服务器中发生数据操作语言事件时执行的存储过程，分为：After触发器和instead of触发器  
DDL触发器：特殊的触发器，在响应数据定义语言（DDL）语句时触发，一般用于数据库中执行管理任务。DDL触发器是响应create、after、或drop开头的语句而激活  
触发器用处还是很多的，比如校内网、开心网、Facebook，你发一个日志，自动通知好友，其实就是在增加日志时做一个后触发，再向通知表中写入条目。因为触发器效率高

### 4.触发器和存储过程的优缺点 ###

#### (一) ####

缺点：  
1、可移植性是存储过程和触发器最大的缺点。2、占用服务器端太多的资源，对服务器造成很大的压力3、不能做DDL。4、触发器排错困难，而且数据容易造成不一致，后期维护不方便。  
优点：1、预编译，已优化，效率较高。避免了SQL语句在网络传输然后再解释的低效率。2、存储过程可以重复使用，减少开发人员的工作量。3、业务逻辑封装性好，修改方便。4、安全。不会有SQL语句注入问题存在。

#### （二） ####

#### 优点： ####

1.由于应用程序随着时间推移会不断更改，增删功能，T－SQL过程代码会变得更复杂，StoredProcedure为封装此代码提供了一个替换位置。

2.执行计划（存储过程在首次运行时将被编译，这将产生一个执行计划－－ 实际上是 Microsoft SQL Server为在存储过程中获取由 T-SQL 指定的结果而必须采取的步骤的记录。）缓存改善性能。  
…但sql server新版本，执行计划已针对所有 T-SQL 批处理进行了缓存，而不管它们是否在存储过程中，所以没比较优势了。  
3.存储过程可以用于降低网络流量，存储过程代码直接存储于数据库中，所以不会产生大量T-sql语句的代码流量。

4.使用存储过程使您能够增强对执行计划的重复使用，由此可以通过使用远程过程调用 (RPC) 处理服务器上的存储过程而提高性能。RPC 封装参数和调用服务器端过程的方式使引擎能够轻松地找到匹配的执行计划，并只需插入更新的参数值。

5.可维护性高，更新存储过程通常比更改、测试以及重新部署程序集需要较少的时间和精力。

6.代码精简一致，一个存储过程可以用于应用程序代码的不同位置。

7.更好的版本控制，通过使用 Microsoft Visual SourceSafe 或某个其他源代码控制工具，您可以轻松地恢复到或引用旧版本的存储过程。

8.增强安全性：  
a、通过向用户授予对存储过程（而不是基于表）的访问权限，它们可以提供对特定数据的访问；

b、提高代码安全，防止 SQL注入（但未彻底解决，例如，将数据操作语言－－DML，附加到输入参数）；

c、SqlParameter 类指定存储过程参数的数据类型，作为深层次防御性策略的一部分，可以验证用户提供的值类型（但也不是万无一失，还是应该传递至数据库前得到附加验证）。

#### 缺点： ####

1.如果更改范围大到需要对输入存储过程的参数进行更改，或者要更改由其返回的数据，则您仍需要更新程序集中的代码以添加参数、更新 GetValue() 调用，等等，这时候估计比较繁琐了。

2.可移植性差  
由于存储过程将应用程序绑定到 SQL Server，因此使用存储过程封装业务逻辑将限制应用程序的可移植性。如果应用程序的可移植性在您的环境中非常重要，则将业务逻辑封装在不特定于 RDBMS 的中间层中可能是一个更佳的选择。