Oracle-对象类

一、Oracle数据库对象介绍

数据库详细介绍

二、Oracle创建分区表，以及分区的简单操作

1 查看链接

分区操作链接

2 给已有的表分区

需要先备份表，然后新建这个表，拷贝数据，删除备份表

2.1 重命名

alter table test_part rename to test_part_temp;

2.2 创建 partition table

create table test_part
(
   ID NUMBER(20) not null,
   REMARK VARCHAR2(1000),
   create_time DATE
)
PARTITION BY RANGE (CREATE_TIME) INTERVAL (numtoyminterval(1, 'month'))
(partition part_t1 values less than(to_date('2018-11-01', 'yyyy-mm-dd')));
 

2.3 创建主键

alter table test_part add constraint test_part_pk_1 primary key (ID) using INDEX;

2.4 将 test_part_temp 表里的数据迁移到 test_part 表中

insert into test_part_temp select * from test_part;

2.5 为分区表设置索引

create index test_part_create_time_1 on TEST_PART (create_time); 

2.6 删除老的 test_part_temp 表

drop table test_part_temp purge;

2.7 允许分区表的分区键是可更新。

-- 当某一行更新时，如果更新的是分区列，并且更新后的列植不属于原来的这个分区，
-- 如果开启了这个选项，就会把这行从这个分区中 delete 掉，并加到更新后所属的分区，此时就会发生 rowid 的改变。
-- 相当于一个隐式的 delete + insert ，但是不会触发 insert/delete 触发器。
alter table test_part enable row movement;
 

三、oracle 存储过程使用create table报错

1 错误背景描述

在使用存储过程进行建表时，提示没有权限 ：ORA-01031: insufficient privileges
但是sql命令窗口是能正常执行建表语句的。

2 报错存储过程代码如下

CREATE OR REPLACE PROCEDURE sp_test  IS
   v_sql            VARCHAR2(500);
   ----------备份表
    v_sql := 'create table  t_test  as  select * from user_tables'  ;
    EXECUTE IMMEDIATE v_sql;
    --清空
 END;

3 解决后存储过程–权限不够，增加 Authid Current_User

CREATE OR REPLACE PROCEDURE sp_test Authid Current_User IS
    v_sql VARCHAR2(500);
BEGIN
    ----------备份表
    v_sql := 'create table  t_test  as  select * from user_tables';
    EXECUTE IMMEDIATE v_sql;
    --清空
END;

--注意事项
```sql
需要注意的是，如果涉及到嵌套调用，另外一个存过调用包含create table的存过时也需要添加 Authid Current_User。
不然同样不能正常调用。
还发现拥有 SELECT_CATALOG_ROLE 角色可以不用添加 Authid Current_User。

四、Oracle 同义词详解（synonym）

1 概述

同义词–Oracle官网

2 语法

2.1 创建

create [public] synonym <synonym_name> for [schema.] object[@db_link];
-- 示例
create public synonym jobs for hr.jobs;
-- 用 scott 用户访问
select * from jobs;
-- 删除
drop synonym jobs;
-- 说明
当原对象被删除时，同义词依旧保留
但又存在原对象时，同义词依旧可以使用

2.2 创建、授权、查询

1. 相关权限语句
   create synonym		 ：创建私有同义词权限
   create public synonym ：创建公有同义词权限
   create any synonym    ：上述都有
   drop any synonym
   drop public synonym
   -- 亦可通过下列查询语句知晓
   select distinct t.privilege
     from dba_sys_privs t
    where t.privilege like '%SYNONYM%'
    order by t.privilege;
2. 授权&回收
   grant create any synonym to scott; -- 授权
   revoke create any synonym to scott; -- 回收
3. 查询同义词对象
   select * from dba_synonyms; -- 仅 DBA 用户
   select * from all_synonyms;
   select * from user_synonyms;

五、oracle-存储过程定时执行任务

定时任务示例操作

六、游标应用

1 概述

1.1 说明

游标的作用就相当于指针，通过游标PL/SQL程序可以一次处理查询结果集中的一行，并可以对该行数据执行特定操作

1.2 含义

--声明游标
cursor cur_name[(input_parameter1[,input_parameter2]…)]
[return ret_type]
is select_sentence;

cur_name：表示所声明的游标名称。
input_parameter1：作为游标的“输入参数”，可以有多个，这是一个可选项。它指定用户在打开游标后向游标中传递的值。
ret_type：表示执行游标操作后的返回值类型，这是一个可选项。
select_sentence：游标所使用的SELECT语句，它为游标的反复读取提供了结果集。
--打开游标
open cur_name[(para_value1[,para_value2]…)];
cur_name：要打开的游标名称。
para_value1：指定“输入参数”的值。
--读取游标
fetch cur_name into {variable};
cur_name：要读取的游标名称。
variable：表示一个变量列表或“记录”变量（RECORD类型），Oracle使用“记录”变量来存储游标中的数据，要比使用变量列表方便得多。

--关闭游标
close cur_name;
cur_name：表示要关闭的游标名称。

1.3 游标的属性

（1）%found：布尔型属性，如果SQL语句至少影响到一行数据，则该属性为true，否则为fasle。

（2）%notfound：布尔型属性，与%found属性的功能相反。

（3）%rowcount：数字型属性，返回受SQL语句影响的行数。

（4）%isopen：布尔型属性，当游标已经打开时返回true，游标关闭时则为false。

2 应用实例

2.1 显式游标

步骤包括：声明游标、打开游标、读取游标和关闭游标4个步骤
声明一个检索emp表中雇员信息的游标，然后打开游标，并指定检索职务是“MANAGER”的雇员信息，接着使用fetch…into语句和while循环读取游标中的所有雇员信息，并输出读取的雇员信息，最后使用close语句关闭游标。

2.1.1 造数据

create table emp(empno number,ename varchar2(500),sal number(10,2),job varchar2(500));

insert into emp (empno,ename,sal,job)
select 1,'liudehua',10000,'SALESMAN' from dual
union all
select 2,'liming',10000,'MANAGER' from dual
;

2.1.2 例子

DECLARE
  /*声明游标，检索雇员信息*/
  CURSOR CUR_EMP(VAR_JOB IN VARCHAR2:='SALESMAN')
  IS SELECT EMPNO,ENAME,SAL
     FROM EMP
     WHERE JOB=VAR_JOB;
     
  TYPE RECORD_EMP IS RECORD       --声明一个记录类型（RECORD类型）
  (
     /*定义当前记录的成员变量*/
     VAR_EMPNO EMP.EMPNO%TYPE,
     VAR_ENAME EMP.ENAME%TYPE,
     VAR_SAL EMP.SAL%TYPE 
  );
  
  EMP_ROW RECORD_EMP;             --声明一个RECORD_EMP类型的变量
BEGIN
  OPEN CUR_EMP('MANAGER');        --打开游标
  FETCH CUR_EMP INTO EMP_ROW;     --先让指针指向结果集中的第一行，并将值保存到EMP_ROW中
  WHILE CUR_EMP%FOUND LOOP
    DBMS_OUTPUT.PUT_LINE(EMP_ROW.VAR_ENAME||'的编号是'||EMP_ROW.VAR_EMPNO||'，工资是'||EMP_ROW.VAR_SAL);
    FETCH CUR_EMP INTO EMP_ROW;   --让指针指向结果集中的下一行，并将值保存到EMP_ROW中
  END LOOP;
  CLOSE CUR_EMP;                  --关闭游标 
END;

2.1.3 执行结果

liming的编号是2，工资是10000

2.2 隐式游标

在SCOTT模式下，把emp表中的销售员（即SALESMAN）的工资上调20%，然后使用隐式游标sql的%rowcount属性输出上调工资的员工数量。

begin 
  update emp
  set sal=sal*(1+0.2)
  where job='SALESMAN';     --把销售员的工资上调20%
  if sql%notfound then      --若update语句没有影响到任何一行数据
    dbms_output.put_line('没有雇员需要上调工资');
  else                      --若update语句至少影响到一行数据        
    dbms_output.put_line('有'||sql%rowcount||'个雇员工资上调20%');
  end if;
end;

–执行结果

有1个雇员工资上调20%

2.3 for语句循环游标

2.3.1 使用隐式游标和for语句检索出职务是销售员的雇员信息并输出。

begin
  for emp_record in (select empno,ename,sal from emp where job='SALESMAN')
  loop
    dbms_output.put_line('雇员编号：'||emp_record.empno);       --输出雇员编号
    dbms_output.put_line('；雇员名称：'||emp_record.ename);     --输出雇员名称
    dbms_output.put_line('；雇员工资：'||emp_record.sal);  --输出雇员工资
   end loop;
end;

–执行结果

雇员编号：1
；雇员名称：liudehua
；雇员工资：12000

2.3.2 使用显式游标和for语句检索出部门编号是30的雇员信息并输出。

declare
  cursor cur_emp is
  select * from emp
  where empno=2;              --检索部门编号为30的雇员信息
begin
  for emp_record in cur_emp     --遍历雇员信息  
  loop
    dbms_output.put_line('雇员编号：'||emp_record.empno);       --输出雇员编号
    dbms_output.put_line('；雇员名称：'||emp_record.ename);     --输出雇员名称
    dbms_output.put_line('；雇员工资：'||emp_record.sal);  --输出雇员工资
  end loop;
end;

–执行结果

雇员编号：2
；雇员名称：liming
；雇员工资：10000

3 参考别的资料

其它学习资料