【Oracle】Oracle基础知识1

最新推荐文章于 2023-02-03 17:05:31 发布

elephant_tee

最新推荐文章于 2023-02-03 17:05:31 发布

阅读量428

点赞数

分类专栏： Sql

Sql 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

一、数据库：

基础知识

1. Oracle数据库中varchar2和char区别。

varchar和varchar2的区别在与后者把所有字符都占两字节，前者只对汉字和全角等字符占两字节。

1.char

char的长度是固定的，比如说，你定义了char(20),即使你你插入abc，不足二十个字节，数据库也会在abc后面自动加上17个空格，以补足二十个字节；

char是区分中英文的，中文在char中占两个字节，而英文占一个，所以char(20)你只能存20个字母或10个汉字。

char适用于长度比较固定的，一般不含中文的情况

2.varchar/varchar2

varchar是长度不固定的，比如说，你定义了varchar(20),当你插入abc，则在数据库中只占3个字节。

varchar同样区分中英文，这点同char。

varchar2基本上等同于varchar，它是oracle自己定义的一个非工业标准varchar，不同在于，varchar2用null代替varchar的空字符串

varchar/varchar2适用于长度不固定的，一般不含中文的情况

3.nvarchar/nvarchar2

nvarchar和nvarchar2是长度不固定的

nvarchar不区分中英文，比如说：你定义了nvarchar(20),你可以存入20个英文字母/汉字或中英文组合，这个20定义的是字符数而不是字节数

nvarchar2基本上等同于nvarchar，不同在于nvarchar2中存的英文字母也占两个字节

nvarchar/nvarchar2适用于存放中文

2. Oracle数据库 ORA-01555“快照过旧”是如何引起？怎么解决？

用户user1对表进行了更新操作,用户user2在user1还没有进行提交前读表中数据,而且是大批量的读取(打个比方:耗时3分钟)而在这3分钟内user1进行了提交操作,那又会产生什么影响呢?这个时候怎么保证读写一致性呢?这个时候DBMS就要保证有足够大的undo表空间来存放修改前的数值,,以保证user2读取的数据是修改前的一致数据.然后下次再读取时候就是更新后的数据了.
ora-01555快照过旧就是因为undo空间不够大，其中一部分undo数据被覆盖了，用户无法获得修改前的数据。
undo数据分为三种：
活动的undo：未提交事务的undo数据，这些undo数据永远不能覆盖，用于回滚rollback事务。
过期的undo：已提交事务的undo数据，这些undo数据可以覆盖。
未过期的undo：事务已提交，但事务提交前，有些查询正在进行，它要读取的是提交前的数据，这部分数据就是未过期数据。如果这部分undo数据被覆盖了，就会发生ora-01555错误。

一个解决方法是，指定undo表空间参数UNDO_TABLESPACE，并将undo空间管理方法设置成自动扩展：UNDO_MANAGEMENT=AUTO。
这种方法可能产生的结果是：
因为undo表空间装了太多未过期（unexpired）的undo数据，新的transaction无法向其中写入undo数据，这时transaction就会发生ORA-30036错误。

3. truncate表，delete表，drop表之间的区别？

相同点：

1.truncate和不带where子句的delete、以及drop都会删除表内的数据。

2.drop、truncate都是DDL语句(数据定义语言),执行后会自动提交。

不同点：

1. truncate 和 delete 只删除数据不删除表的结构(定义)
drop 语句将删除表的结构被依赖的约束(constrain)、触发器(trigger)、索引(index)；依赖于该表的存储过程/函数将保留,但是变为 invalid 状态。

2. delete 语句是数据库操作语言(dml)，这个操作会放到 rollback segement 中，事务提交之后才生效；如果有相应的 trigger，执行的时候将被触发。
truncate、drop 是数据库定义语言(ddl)，操作立即生效，原数据不放到 rollback segment 中，不能回滚，操作不触发 trigger。

3. TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。

1.DELETE
　・DML语言
　・可以回退
　・可以有条件的删除

     DELETE FROM 表名
　  WHERE 条件

2.TRUNCATE TABLE
　・DDL语言
　・无法回退
　・默认所有的表内容都删除
　・删除速度比delete快。

   TRUNCATE TABLE 表名

4. not exists; not in 应用上的主要区别？

1、in和exists

in是把外表和内表作hash连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询，一直以来认为exists比in效率高的说法是不准确的。如果查询的两个表大小相当，那么用in和exists差别不大；如果两个表中一个较小一个较大，则子查询表大的用exists，子查询表小的用in；

例如：表A(小表)，表B(大表)

select * from A where cc in(select cc from B)　　-->效率低，用到了A表上cc列的索引；

select * from A where exists(select cc from B where cc=A.cc)　　-->效率高，用到了B表上cc列的索引。

相反的：

select * from B where cc in(select cc from A)　　-->效率高，用到了B表上cc列的索引

select * from B where exists(select cc from A where cc=B.cc)　　-->效率低，用到了A表上cc列的索引。

2、not in 和not exists

not in 逻辑上不完全等同于not exists，如果你误用了not in，小心你的程序存在致命的BUG，请看下面的例子：

truncate table ljn_t

truncate table ljn_test2;

insert into ljn_test1 values(1);

insert into ljn_test1 values(2);

insert into ljn_test1 values(3);

insert into ljn_test2 values(2);

insert into ljn_test2 values(null);

commit;

然后再次执行两个SQL：

select * from ljn_test1 where not exists (select 1 from ljn_test2 where ljn_test1.col = ljn_test2.col);

COL

----------

select * from ljn_test1 where col not in (select col from ljn_test2);

no rows selected

这回not in的原形暴露了，竟然得到的是空集。来仔细分解一下原因：

A. select * from ljn_test1 where col not in (select col from ljn_test2);

A在这个例子中可以转化为下面的B：

B. select * from ljn_test1 where col not in (2,null);

B可以进一步转化为下面的C：

C. select * from ljn_test1 where col <> 2 and col <> null;

因为col <> null是一个永假式，所以最终查出的结果肯定也就是空了。

由此可以得出结论：只要not in的子查询中包含空值，那么最终的结果就为空！

not exists语句不会出现这种情况，因为not exists子句中写的是ljn_test1与ljn_test2的关联，null是不参与等值关联的，所以ljn_test2的col存在空值对最终的查询结果没有任何影响。

我在这里暂且把ljn_test1叫做外表，ljn_test2叫做内表。

只要稍做归纳，就可以得到更详细的结论：

1、对于not exists查询，内表存在空值对查询结果没有影响；对于not in查询，内表存在空值将导致最终的查询结果为空。

2、对于not exists查询，外表存在空值，存在空值的那条记录最终会输出；对于not in查询，外表存在空值，存在空值的那条记录最终将被过滤，其他数据不受影响。

正如所看到的，not in出现了不期望的结果集，存在逻辑错误。如果看一下上述两个select 语句的执行计划，也会不同，后者使用了hash_aj，所以，请尽量不要使用not in(它会调用子查询)，而尽量使用not exists（它会调用关联子查询）。如果子查询中返回的任意一条记录含有空值，则查询将不返回任何记录。如果子查询字段有非空限制，这时可以使用not in，并且可以通过提示让它用hasg_aj或merge_aj连接。

如果查询语句使用了not in，那么对内外表都进行全表扫描，没有用到索引；而not exists的子查询依然能用到表上的索引。所以无论哪个表大，用not exists都比not in 要快。

3、in 与 = 的区别

select name from student where name in('zhang','wang','zhao');

与

select name from student where name='zhang' or name='wang' or name='zhao'

的结果是相同的。

5. 表A有三个字段name,varchar2(32); school, varchar2(32); achievement, number(10,1), A表有10000条记录，B表有500000条记录，若B与A关联查询，用not exists 还是 not in？解释原因？

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

编程

6. 有四张表：

①产品信息（产品代号数值型 4位主键、产品名称字符型 40位中文字符、产品特性字符型 100位中文字符）

②用户信息（用户代号数值型 8位主键、用户名称字符型 40位中文字符、性别数值型（1男2女）、生日字符型8位、身份证号字符型18位）

③用户订购信息（用户代号数值型8位、产品代号数值型4位、定购时间日期型）用户代号和产品代号构成索引。

④产品使用信息（用户代号数值型8位、产品代号数值型4位、使用时间日期型、是否成功使用数值型）用户代号、产品代号和使用时间构成索引。

A> 创建4个表（主键、索引），表名、字段名、索引名自取。

B> 2005.3.1——2005.8.31用户定购的产品信息，创建一个视图，视图如下：

用户代号

用户名称

产品代号

产品名称

定购时间

C> 若4个表都录入数据，统计2005.3.1——2005.8.31之间，各产品被使用的次数，使用用户数（被使用的产品，用户必须是2005.3月前定购，不含3月）

产品代号	产品名称	被使用次数	使用用户数

a) 针对这个报表，创建一个数据库表，存放统计信息。

b) 编写一个存储过程，从用户信息、产品信息、用户定购信息、产品使用信息4个表中统计数据，把结果放入（a）中创建的统计信息表。

7.PL/SQL之record、nested table

7.1.题目

网站上的广告位是轮播的，每天某一广告位最多可轮播的广告数量是有限制的，比如A广告位，每天只能轮播三个广告，但销售人员在销售广告位时并不考虑此限制，要求查询出合同表中，超过广告位轮播数量的合同。

合同表 Orders

OrderID	Positioncode	Startdate	Enddate
1	A	2006-11-01	2006-11-03
2	C	2006-11-02	2006-11-03
3	B	2006-11-01	2006-11-04
4	A	2006-11-03	2006-11-04
5	C	2006-11-01	2006-11-02
6	B	2006-11-02	2006-11-05
7	A	2006-11-02	2006-11-03
8	A	2006-11-04	2006-11-05
9	C	2006-11-03	2006-11-04
10	C	2006-11-02	2006-11-04

广告位表 Product

Positioncode	Showcount
A	2
B	1
C	3

说明：对于广告位A来讲，轮播情况如下表				A	2
OrderID	2006/11/1	2006/11/2	2006/11/3	2006/11/4	2006/11/5
1	V	V	V
4			V	V
7		V	V
8				V	V

说明：对于广告位B来讲，轮播情况如下表				B	1
OrderID	2006/11/1	2006/11/2	2006/11/3	2006/11/4	2006/11/5
3	V	V	V	V
6		V	V	V	V

说明：对于广告位B来讲，轮播情况如下表				C	3
OrderID	2006/11/1	2006/11/2	2006/11/3	2006/11/4	2006/11/5
2		V	V
5	V	V
9			V	V
10		V	V	V

广告位A每天最多可轮播2个广告，但合同表中在2006-11-03这天有三个广告（1、4、7），对于广告位A，1、4、7则是最终需要得到的结果。如需要可使用临时表、存储过程等。

7.2.实现代码

declare
  type t_order_stats is record(
    order_date date,
    order_count number(3),
    order_info varchar2(100)
  );
  type t_order_statses is table of t_order_stats;

  v_minDate Date;
  v_maxDate Date; 
  v_positionCode varchar2(3);
  i integer;
  
  v_product product%ROWTYPE;
  v_order orders%ROWTYPE; 
  v_order_statses t_order_statses:=t_order_statses();

  cursor v_cur_orders is select * from orders where positioncode=v_positionCode order by orderid;
  cursor v_cur_product is select positioncode,showcount from product;

begin

  select min(startdate),max(endDate) into v_minDate,v_maxDate from orders;
  
  --初始化数组v_order_statses
  v_order_statses.extend(v_maxDate-v_minDate + 1);

  --打开product表的游标
  open v_cur_product;
  loop
    
    --复位数组
    for i in 1..(v_maxDate-v_minDate + 1) loop
      v_order_statses(i).order_date := v_minDate + i -1;
      v_order_statses(i).order_count := 0;
      v_order_statses(i).order_info := ' ';
    end loop;
    
    --遍历每个广告位,取出广告允许的数量
    fetch v_cur_product into v_product;
    if v_cur_product%NOTFOUND then
      exit;
    end if;
    
    v_positionCode:=v_product.positionCode;
    --取出该广告位的所有定单
    open v_cur_orders;
    
    loop

      fetch v_cur_orders into v_order;
      if v_cur_orders%NOTFOUND then
        exit;
      end if;

      --将定单拆分到每一天,信息存入以天为单位的数组v_order_statses      
      for i in (v_order.startDate - v_minDate + 1)..(v_order.endDate - v_minDate + 1) loop
        --广告数量加1
        v_order_statses(i).order_count := v_order_statses(i).order_count + 1;
        --追加定单号
        v_order_statses(i).order_info := v_order_statses(i).order_info ||to_char(v_order.orderid)||',';
      end loop;
      
    end loop;
    close v_cur_orders;
    
    --遍历v_order_statses,找到超出广告数量的那几天,并打印orderId
    i:=v_order_statses.first;
    while i <= v_order_statses.last loop
      if v_order_statses(i).order_count> v_product.showCount then
        dbms_output.put('position['||v_product.positionCode||']');
        dbms_output.put('['||to_char(v_order_statses(i).order_date,'yyyy-MM-dd')||']');
        dbms_output.put_line(v_order_statses(i).order_info);
      end if;
      i:=v_order_statses.next(i);
    end loop;
    
  end loop;
  
  close v_cur_product;
  
end;

7.3.执行结果

position[A][2006-11-03] 1,4,7,
position[B][2006-11-02] 3,6,
position[B][2006-11-03] 3,6,
position[B][2006-11-04] 3,6,

elephant_tee

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
【Oracle】Oracle基础知识1

一、数据库：基础知识1. Oracle数据库中varchar2和char区别。varchar和varchar2的区别在与后者把所有字符都占两字节，前者只对汉字和全角等字符占两字节。1.char char的长度是固定的，比如说，你定义了char(20),即使你你插入abc，不足二十个字节，数据库也会在abc后面自动加上17个空格，以补足二十个字节；
复制链接

扫一扫