oracle基础知识

Plsql数据库

增加数据的时候，要在plsql里面帮助-----支持信息-----tns调试增加数据库信息。

D:\app\oracle\product\11.2.0\dbhome_1\NETWORK\ADMIN下的tnsnames.ora文件，一样增加相同的语句

8.1  数值型函数

abs();求绝对值

sign();大于0为1，小于0为-1，等于0为0

ceil(x):返回>=x的最小整数，向上取整

floor(x):向下取整

mod(x):取余

round(x,y):四舍五入，y限定了值的格式。Y先四舍五入取整，y>0：x为y位小数，

y<0:小数点想左第y位四舍五入。Y可省略不写,默认为0。

Trunc(x,y):跟round一样，不同是直接截断

8.2 字符型函数

字符串得拿单引号了引起来

ASCII（’xyyyy’）:返回首字符的ASCII码

Chr(ascii值)：返回ASCII码代表的字符

Concat(‘a’,’b’):拼接A和B，等同于a||b 字符串用单引号连接

Initcap(‘字符串1        字符串2                     字符串n’):可以将多个字符串的首字母大写，字符串之间用空格隔开

Lower（）转小写

Upper（）转大写

NLS函数没有深入理解。里面的nls_sort可以对数据库表进行排序。格式

Nlssort(表列名，’nls_sort=排序方式’)这样就可以对数据库表以某列进行排序了

方式有三种SCHINESE_RADICAL_M（部首、笔画）SCHINESE_STROKE_M（笔画、部首）SCHINESE_PINYIN_M（拼音）

Instr(c1,c2,[a,b]) :汉字，全角符按1个字符算

c1被搜索字符串,c2想搜索的字符串,a想搜索的字符串开始位置,b想搜索的字符串第b次出现的位置)

instrb(c1,c2,[a,b]) 汉字，全角符按2个字符算。其余与上面instr一致

length(c): 汉字，全角符按1个字符算

lengthb(c): 汉字，全角符按2个字符算

substr(c,start,n): 汉字，全角符按1个字符算

截取字符串c，start开始位置，共计n个字符

substrb(c,start,n): 汉字，全角符按2个字符算

to_char(date[,’format’]):format为字符串格式，具体格式届时看要求

to_date(‘字符串’【,’format’】) :format为字符串格式，具体格式届时看要求

avg()sum()count()这三个函数里面参数都可以写成[distinct|all]

 

8.2 日期型函数

Add_month(d,i)返回日期D加上i个月以后的结果，i为任意整数

8.3 数据库操作

8.3.1 插入数据

Insert into tablename (列名，列名。。。) values (值1，值2。。。)   字符串用’ ’扣起来。修改以后记得提交或者按F10

8.3.2 更新数据

Update tablename set 列名1=表达式1[,列名2=表达式2.。。。]  

8.3.3 删除数据

 

Delete 

数据可以回滚，即可以rollback

Delete from tablename

Where 条件

 

Truncate

相当于没有条件限制的delete，不可回滚，还有reuse storage 和 drop storage两种

reuse storage：保留记录所占空间

drop storage：删除数据后立即回收占用空间

Truncate  table  tablename [reuse storage |drop storage]

8.3.4查询数据

Select 字段

From A

LEFT JOIN B

ON 关联条件，这里还可以写对B表的限制条件

Where A表的限制条件

对A表的限制条件如果ON里面得到的数据是不正确的，没有起到限制A表的作用

 

8.4 特殊数据类型

%type

声明var

声明一个与指定列相同的数据类型，语句如下

声明的列名  表名.指定的列名%type

 

%rowtype

 

变量声明

变量名 数据类型 【长度：=初始值】

 

常量声明

常量名  constant数据类型：=常量值

8.5 表名、列相关增删改

Create  table  tablename{

            列名 数据类型；

            列名 数据类型；

            列名 数据类型；

列名 数据类型

}

表重命名：

Alter table tablename rename to newtablename；

列重命名

Alter table tablename rename column oldcolumnname to newcolumnname

复制表结构

Create table tablename as select * from 另一个表名 where 1=2;

创建表空间

Create tablespace tablespace_name  datafile’路径（写到.dbf文件’ size 多大autoextend on next 多大 maxsize 多大

增加列

Alter table tablename add 列名 数据类型；

修改一列

alter table tablename modify 列名 数据类型;

删除一列：

alter table tablename drop column 列名;

 

8.5 循环语句

1、loop会先执行后判断True退出循环

Loop

Plsql语句；

Exit when 循环条件

End loop；

 

2、while 先判断后执行 true执行循环

While 循环条件

            Plsql语句

End loop；

3、dbms_output.put_pine

示例：

declare

  i  number := 1;

  dt char(8);

begin

  while (i < 30) loop

    dt := to_char(sysdate - i, 'yyyymmdd');

    dbms_output.put_line('date:' || dt);

    i := i + 1;

  end loop;

end;

8.5 游标cursor

游标一般存放的是select语句

8.5.1 游标使用步骤

Set serveroutput on ：oracle自带的输出语句，从而使plsql程序能够在sqlplus里面输出结果。将dbms_output.put_pine

1、处理步骤:

声明--àà打开-à读取--à关闭

2、声明

Cursor  cursorname 【参数名 in 数据类型：=初始化值[default]】

【return 返回类型】

Is select语句；

举例如下

Cursor  cur_emp （var_job  in  varchar2：=‘saleman’）

            Is select empno,empname

                        From emp

                        Where job = var_job;

 

3、打开

格式：open cursorname (传入的参数值)；

Open cur_emp (传入的参数值)；

如果不写参数值，则默认为声明时候的初始值

显示

隐式

4、读取：

逐行将结果集中地数据保存到变量中；用fetch。。。into语句

Fetch cursorname into{变量列表或者record类型变量}

 

5、关闭

Close cursorname

8.5.2 游标与游标变量

处理运行时动态执行的 SQL 查询。

Ref就是声明游标变量

游标====静态的

游标变量====动态的

声明ref游标类型格式

Type <游标名> is ref cursor

            Return 返回类型

<游标变量名>

 

8.5 存储过程procedure

这个存储过程最重要就是SQL语句之间的关联关系，找到业务之间的逻辑关系。需要多些多练，看看别人写存储过程，自己尝试写写。

 

Create or replace procedurename（

参数名 in  数据类型；--传入参数

参数名 out 数据类型 ---传出参数

）

As

自定义变量；

Begin

            SQL语句；

Exception

异常处理语句；

End procedurename；

 

8.5.1 赋权

给表赋权：

Grant select /update on tablename to 用户

Grant 权限 on 表名to用户

给存储噢过程赋权限：

Grant execute on 过程、包、方法 to user；

一次性把权限给完：

Grant  execute any procedure to user

 

 

8.6 oracle里面as和is的区别

名称	As	Is
存储过程	√	√
方法	√	√
游标	不可以	√
视图	√	不可以

 

 

 

 

8.6 函数

函数主体部分必须有返回值，且必须与参数数据类型一致

8.6.1 定义

Create or replace function functionname [(参数 参数数据类型)] return 参数数据类型 is

            [函数内部变量]

Begin

            SQL语句；

[exception]

[异常处理代码；]

End functionname；

8.6.2  调用

调用过程中要声明一个变量来保存函数的返回值

举例：记得调用的时候把serveroutput 打开

声明了一个函数:

Create or replace function  get_avg_pay(num_deptno number ) return number is

          Number_avg_pay number;

Begin

          Select avg(sal ) into number_avg_pay from emp where deptno=num_deptno;

          Return(round(number_avg_pay,2));

Exception

          When no_data_found then

          Dbms_output.put_line(‘该部门编号不存在’);

End get_avg_pay;

 

声明变量：declare

                                    Avg_pay number;

                          Begin

                                    Avg_name:=get_avg_pay(10);

                                    Dbms_output.put_line(‘平均工资是：||’avg_pay);

                          End;

 

8.6.2  删除

Drop function functionname

8.7 表分区

8.7.0  创建表空间及用户、用户赋权

 

创建表空间：在Windows系统里面在cmd命令下输入下面图形中用红框的语句，查看当前数据文件：Linux在终端里面输入su – orcl输入下面语句

 

创建表空间

Create tablespace name datafile ‘路径\表空间.dbf’ size XXXX;

创建用户：

Create user 用户名 identified  by 密码 default tablespace 表空间名；

创建用户后赋权

Grant connect ，resource，（dba）to 用户名

注：如果想创建dba用户要把dba权限给用户。富登工作的时候是在xshell里面先新建相应IP的工作，然后连接sysdba在进行创建用户。

 

没有分区的表不能建好了之后再分区。已经分区表可以在建表之后再增加、减少、拆分或合并分区。

表分区分为四类：范围分区、散列分区、列表分区、复合分区

8.7.1  范围分区   range

它是以列的值的范围来做为分区的划分条件，将记录存放到列值所在的range分区中。可以指定maxvalue，所有不在指定分区列表的数据都可以放在maxvalue分区里面。

Partition  by  range (range _key_column就是要分区的数据项)

{

            Partition 分区名字1  VALUES  LESS  THAN(to_date(‘某个具体日期’,’yyyy-mm-dd’)),

            Partition 分区名字2  VALUES  LESS  THAN(to_date(‘某个具体日期’,’yyyy-mm-dd’)),

            ……

Partition 分区名字N  values less than(maxvalue)

}

8.7.2  hash分区 又叫散列分区  hash

根据字段的hash值尽可能均匀的把值分散到各个分区

Partition  by hash (要分区的字段)

{

            Partition 分区名1 【tablespace 表空间名字1】，

            Partition 分区名2 【tablespace 表空间名字2】，

。。。。。。

}

8.7.2  列表分区   list

根据某个字段的具体值进行分区，如果值不匹配就会更新或插入失败，因此建议加入default数值，将没有指定数值的字段放在里面，增加容错

Partition  by list (要分区的字段)

{

            Partition 分区名字1 values（‘值1’），

            Partition 分区名字1 values（‘值1’），

            Partition 分区名字 N values （default）

}

8.7.3  复合分区  

10g复合分区分为两种：range-list  range –hash 格式为分区-子分区

在Oracle 11g中，组合分区功能这块有所增强，又增加了range-range,list-range,list-list,list-hash，并且 11g里面还支持Interval分区和虚拟列分区。

range –hash语句：

Partition by range( 范围分区字段) subpartition by hash(子哈希分区字段)

{           ----先写子分区语句，再写分区语句

            Sbupartition by hash(字段)

subpartitions 3 store in (tablespace01,tablespace02,tablespace03)

或者是

            subpartition template

(subpartition a tablespace ts1,

subpartition b tablespace ts2,

subpartition c tablespace ts3,

subpartition d tablespace ts4          

)  ----这结尾没有标点符号

然后写范围分区语句

}

range-list语句

partition by range (txn_date) subpartition by list (state)

---先写范围分区，在每个范围分区里面嵌套列表分区

 (Partition 范围分区名字1  valuses less than (to_date(‘具体某日期1’，‘yyyy-mm-dd’))

          ( subpartition 列表分区名字1  values （‘ 值1’），

            subpartition 列表分区名字 2 values （‘ 值2’），

            。。。

subpartition 列表分区名字 n  values （‘ 值n’）

),

…  …

(Partition 范围分区名字n  valuses less than (to_date(‘具体某日期n’，‘yyyy-mm-dd’))

          ( subpartition 列表分区名字1  values （‘ 值1’），

            subpartition 列表分区名字 2 values （‘ 值2’），

            。。。

subpartition 列表分区名字 n  values （‘ 值n’）

)

8.8 视图

8.8.1  视图定义

简单视图:基于单个表建立的，不包含任何函数、表达式、分组数据的视图。

语句

Create or replace view viewname

As

Select语句

【with check option,】作用：该句是对之后对视图进行的dml语句进行条件限制，要符合上面select语句中where的限制条件，否则就会报错。如果不写这句话，进行视图操作的时候就不会进行where语句检查。

【with read only】；

8.8.2  视图操作  

可以进行查询、删除、更新、插入操作

Update viewname set 字段=‘吧啦吧啦’ where 限制条件

 

attention：

1、 视图的DML操作对基表是有影响的，视图的insert,update,delete相关的基表也会被insert,update,delete

2、 Drop视图对基表没有影响，删除基表后视图会没有数据，因为视图是不包含数据的（物化视图除外）。数据字典中还有删除基表的视图记录，就有了ORA-04063报错

 

复杂视图一般只进行查询操作，不进行insert,update,delete操作

8.9 索引

CREATE [UNIQUE] | [BITMAP] INDEX index_name     --unique表示唯一索引

ON table_name([column1 [ASC|DESC],column2      --bitmap，创建位图索引

[ASC|DESC],…] | [express])

[TABLESPACE tablespace_name]

[PCTFREE n1]                                 --指定索引在数据块中空闲空间

[STORAGE (INITIAL n2)]

[NOLOGGING]                                  --表示创建和重建索引时允许对表做DML操作，默认情况下不应该使用

[NOLINE]

[NOSORT];                                    --表示创建索引时不进行排序，默认不适用，如果数据已经是按照该索引顺序排列的可以使用

8.9 序列

Create sequence name

Start with 初始值

Increase by  增长值

Maxvalue 最大值

Minvalue  最小值

Nocucle  是否循环

Cache 缓存值  -- 使用的缓存

级联序列

select  *                //要查询的字段

from table              //具有子接点ID与父接点ID的表 

start with selfid=id      //给定一个startid(字段名为子接点ID，及开始的ID号)

connect by prior selfid=parentid       //联接条件为子接点等于父接点

 

8.10 事务处理

8.10.1、概念

   Savepoint 在事务中建立一个存储的点.当事务处理发生异常而回滚事务时,可指定事务回滚到某存储点.然后从该存储点重新执行。

   Release  savepoint  删除存储点

   Rollback 回滚事务 取消对数据库所作的任何操作

   Commit 提交事务 对数据库的操作做持久的保存。

8.10.2、事务的几个重要操作

1.设置保存点 savepoint a
2.取消部分事务 rollback to a
3.取消全部事务 rollback

 

8.10.3、幻想读、不可重复读、读脏

两个事务并发访问数据库数据时可能存在的问题

1. 幻想读：

事务T1读取一条指定where条件的语句，返回结果集。此时事务T2插入一行新记录并commit，恰好满足T1的where条件。然后T1使用相同的条件再次查询，结果集中可以看到T2插入的记录，这条新纪录就是幻想。

2. 不可重复读取：

事务T1读取一行记录，紧接着事务T2修改了T1刚刚读取的记录并commit，然后T1再次查询，发现与第一次读取的记录不同，这称为不可重复读。

3. 脏读：

事务T1更新了一行记录，还未提交所做的修改，这个T2读取了更新后的数据，然后T1执行回滚操作，取消刚才的修改，所以T2所读取的行就无效，也就是脏数据。

8.10.3、事务锁

Oracle提供的两种类型的锁机制：

•  独占锁：防止相关资源被共享，主要用来修改数据，只有在独占资源的事务释放独占锁后，其他事务才能对其资源进行操作。
• 共享锁：允许相关资源的共享，依赖于所包含的操作，多个用户读数据可以使用共享锁

 

 

8.10 SQL优化

几大原则

1. 用not exist 代替not in 。尽量避免使用in

对于in 和 exists的性能区别:  
  如果子查询得出的结果集记录较少主查询中的表较大且又有索引时应该用in,反之如果外层的主
查询记录较少子查询中的表大又有索引时使用exists。  
  其实我们区分in和exists主要是造成了驱动顺序的改变这是性能变化的关键如果是exists
那么以外层表为驱动表先被访问如果是IN那么先执行子查询所以我们会以驱动表的快速返
回为目标那么就会考虑到索引及结果集的关系了  
另外IN是不对NULL进行处理

1. Is null 和is not null 都是不可以使用索引的，所以建议表is not null 替换为 > ‘  ‘(这么写保留疑义，不知道是否可行)