109---oracle的oracle的PL/SQL，异常处理， 游标（CURSOR）， 子程序，存储过程

1.      表空间

  创建一个自动增长的表空间tp_orders

CREATE TABLESPACE tp_orders

DATAFILE 'E:\Data\WORKTBS01.DBF'

SIZE 10M AUTOEXTEND ON;

 

  创建tp_hr表空间

 

--推荐创建表空间示例

CREATE TABLESPACE tp_hr

  DATAFILE

  'd:\data\tp_hr01.dbf' SIZE 60M;

       

 

 

--查看表空间

SELECTfile_name,tablespace_name,bytes,autoextensible

 FROM dba_data_files

 WHERE tablespace_name='TP_HR';     

 

  调整tp_hr表空间大小

--方法一：更改数据文件的大小

ALTER DATABASE DATAFILE

  'D:\DATA\tp_hr01.dbf'

  RESIZE 80M;

      

--方法二：向表空间内添加数据文件

ALTER TABLESPACE tp_hr

  ADD DATAFILE

  'E:\DATA\tp_hr02.DBF' SIZE 20M

  AUTOEXTEND ON;

 

  删除tp_hr表空间

 

DROP TABLESPACE tp_hr;

DROP TABLESPACE tp_hr INCLUDING CONTENTS;

 

          小

 

 

 

          小

 

 

 

 

2.      用户

  创建用户A_oe

CREATE USER A_oe

IDENTIFIED BY bdqn

DEFAULT TABLESPACE tp_orders

TEMPORARY TABLESPACE TEMP;

 

  将A_hr. employee表的访问权限授予A_oe用户

GRANT connect, resource TO A_oe;  --授予CONNECT和RESOURCE两个角色

GRANT SELECT ON A_hr.employee TO A_oe;

 

 

  创建A_hr用户

 

CREATE USER A_hr IDENTIFIED BY bdqn

  DEFAULT TABLESPACE tp_hr

  TEMPORARY TABLESPACE temp

  QUOTA UNLIMITED ON tp_hr

  QUOTA 10M ON tp_bak             

  PASSWORD EXPIRE;

 

          查询A_hr用户

--查询A_hr用户

SELECT *

 FROM dba_users

 WHERE username='A_HR';

 

          查看表空间限额

 

SELECT *

 FROM dba_ts_quotas

 WHERE username='A_HR';

  更改表空间中的用户限额

ALTER USER A_hr QUOTA 20M ON tp_bak;

  删除用户

DROP USER A_hr CASCADE;

  给户A_hr用户授权，回收权限

GRANT connect, resource TO A_hr;  --授予CONNECT和RESOURCE两个角色

REVOKE connect, resource FROM A_hr; --撤销CONNECT和RESOURCE两个角色

GRANT SELECT ON SCOTT.emp TO A_hr; --允许用户查看 EMP 表中的记录

GRANT UPDATE ON SCOTT.emp TO A_hr; --允许用户更新 EMP 表中的记录

 

 

 

 

 

 

3.      序列

  使用序列生成部门表中部门编号的值

CREATE SEQUENCE dept_seq

START WITH 60

INCREMENT BY 10

MAXVALUE 10000

NOCYCLE

CACHE 30;

 

 

INSERT INTO deptVALUES(dept_seq.nextval,'PERSONNEL','BEIJING');

INSERT INTO deptVALUES(dept_seq.nextval,'MARKET','SHANGHAI');

 

 

SELECT * FROM dept;

 

  创建序列

CREATE TABLE Stock_Received

(

     STOCK_ID VARCHAR2 (5),

     STOCK_DATE DATE NOT NULL,

     COST NUMBER (10)

)

 

CREATE SEQUENCE myseq

START WITH 1000

INCREMENT BY 10

MAXVALUE 1100

CYCLE

CACHE 30;

 

  使用

 

INSERT INTO Stock_Received VALUES(myseq.nextVal,sysdate,1000);

 

          小

 

 

 

                  小

 

 

 

                  小

 

 

 

4.      数据库迁移

  数据库迁移前准备工作

DROP SEQUENCE dept_seq;

 

 

 

 

  数据库迁移恢复工作

 

 

--1.恢复序列对象

CREATE SEQUENCE dept_seq

START WITH 80

INCREMENT BY 10

MAXVALUE 10000

NOCYCLE

CACHE 30;

--2.恢复部门表

CREATE TABLE deptBak AS SELECT * FROM dept;

--3.正常操作，插入数据

INSERT INTO deptVALUES(dept_seq.nextval,'LAB','LONDON');

 

 

   

 

 

 

5.      同义词

  在A_hr模式下创建dept表的公有同义词 p_sy_dept

 

--使用SYSTEM用户登录，A_hr获得创建公有同义词权限

GRANT CREATE PUBLIC SYNONYM TO A_hr;

 

 

--在A_hr模式下创建公有同义词public_sy_dept作为A_hr用户dept表的别名

CREATE PUBLIC SYNONYM public_sy_dept FORdept;

--将查询dept的权限授予pulic角色。

GRANT SELECT ON dept TO PUBLIC;

 

 

--在A_oe模式下访问公有同义词

SELECT * FROM public_sy_dept;

  创建同义词

--使用SYSTEM用户登录，A_hr获得创建公有同义词权限

GRANT CREATE PUBLIC SYNONYM TO A_hr;

 

 

--在A_hr模式下创建公有同义词p_Stock_Received作为A_hr用户Stock_Received表的别名

CREATE PUBLIC SYNONYM p_Stock_Received FORStock_Received;

--将查询dept的权限授予pulic角色。

GRANT SELECT ON Stock_Received TO PUBLIC;

 

 

--在A_oe模式下访问公有同义词

SELECT * FROM p_Stock_Received;

 

 

  私有同义词

--使用SYSTEM用户登录，授予A_oe用户获得访问A_hr模式下的employee表的权限

GRANT SELECT ON A_hr.employee TO A_oe;

GRANT CREATE SYNONYM TO A_oe;

--连接A_oe用户

--创建同义词SY_EMP

CREATE SYNONYM SY_EMP FOR A_hr.employee;

--访问同义词

SELECT * FROM SY_EMP;

  公有同义词

--使用SYSTEM用户登录，A_hr获得创建公有同义词权限

GRANT CREATE PUBLIC SYNONYM TO A_hr;

 

 

--在A_hr模式下创建公有同义词public_sy_emp作为A_hr用户employee表的别名

CREATE PUBLIC SYNONYM public_sy_emp FORemployee;

 

 

--在A_oe模式下访问公有同义词

SELECT * FROM public_sy_emp;

  删除同义词

--删除私有同义词

DROP SYNONYM A_oe.sy_emp;

--删除公有同义词

DROP PUBLIC SYNONYM A_hr.public_sy_emp;

 

 

 

 

 

6.      索引

  创建索引

CREATE UNIQUE INDEXindex_unique_customer_id ON customers(customer_id);

或者

CREATE INDEX index_reverse_customer_id ONcustomers(customer_id) REVERSE;

 

CREATE BITMAP INDEX index_bit_nls_territoryON customers(nls_territory);

 

CREATE INDEX index_ename ONcustomers(cust_first_name,cust_last_name);

 

  创建唯一索引

 

CREATE UNIQUE INDEX index_unique_Stock_IDON Stock_Received (Stock_ID);

 

  在玩具表中，需要标识列toyid作为标识，不需要有任何含义，可以作为主键

 

--创建toys表

CREATE TABLE toys(

toyid NUMBER NOT NULL,

toyname VARCHAR2(20),

toyprice NUMBER

);

 

 

--插入数据

INSERT INTO toys (toyid, toyname, toyprice)

VALUES (seq1.NEXTVAL, 'TWENTY', 25);

INSERT INTO toys (toyid, toyname, toyprice)

VALUES (seq1.NEXTVAL,'MAGIC PENCIL',75);

 

 

--查询数据

SELECT * FROM toys;

 

 

SELECT seq1.CURRVAL FROM dual;

 

 

--修改序列

ALTER SEQUENCE seq1

MAXVALUE 5000

CYCLE;

 

 

--删除序列

DROP SEQUENCE seq1;

 

 

--使用SYS_GUID函数

SELECT sys_guid() FROM dual;

  在薪水级别（salgrade）表中，为级别编号（grade）列创建唯一索引

CREATE UNIQUE INDEX index_unique_grade ONsalgrade(grade);

  在员工（employee）表中，为员工编号（empno）列创建反向键索引

CREATE INDEX index_reverse_empno ON employee(empno) REVERSE;

  在员工（employee）表中，为工种（job）列创建位图索引

CREATE BITMAP INDEX index_bit_job ONemployee(job);

  在员工（employee）表中，为员工名称（ename）列创建大写函数索引

CREATE INDEX index_ename ON employee(UPPER(ename));

  删除索引

DROP INDEX index_bit_job;

  反向键索引更改为正常B树索引

ALTER INDEX index_reverse_empno REBUILDNOREVERSE;

  将索引移到指定表空间

ALTER INDEX index_name REBUILD TABLESPACEtablespace_name;

 

 

 

 

 

 

 

7.      分区

  范围分区

 

CREATE TABLE rangeOrders

(

     order_id           NUMBER(12)

    ,order_date         DATE NOT NULL

    ,order_mode         VARCHAR2(8)

    ,customer_id        NUMBER(6) NOT NULL

    ,order_status       NUMBER(2)

    ,order_total        NUMBER(8,2)

    ,sales_rep_id       NUMBER(6)

    ,promotion_id       NUMBER(6)

)

PARTITION BY RANGE (order_date)

(

  PARTITION Part1 VALUES LESS THAN (to_date('2005-01-01', 'yyyy-mm-dd')),

   PARTITION Part2 VALUES LESS THAN(to_date('2006-01-01', 'yyyy-mm-dd')),

  PARTITION Part3 VALUES LESS THAN (to_date('2007-01-01', 'yyyy-mm-dd')),

  PARTITION Part4 VALUES LESS THAN (to_date('2008-01-01', 'yyyy-mm-dd')),

  PARTITION Part5 VALUES LESS THAN (to_date('2009-01-01', 'yyyy-mm-dd'))

);

 

 

--要查看每一分区的数据

SELECT *  FROM rangeOrderspartition(Part1);

SELECT *  FROM rangeOrderspartition(Part2);

SELECT *  FROM rangeOrderspartition(Part3);

SELECT *  FROM rangeOrderspartition(Part4);

SELECT *  FROM rangeOrderspartition(Part5);

 

 

 

--要查看在第三季度的数据

SELECT *  FROM SALES1 partition(P3);

 

 

--要删除第三季度的数据

DELETE FROM SALES1 partition(P3);

 

--插入'2013/01/01'数据

insert into rangeOrders values(1001,to_date('2013-01-01','yyyy-mm-dd'),'direct',101,0,1000,153,null);

--报错后，修正

DROP TABLE rangeOrders;

 

 

CREATE TABLE rangeOrders

(

     order_id           NUMBER(12)

    ,order_date         DATE NOT NULL

    ,order_mode         VARCHAR2(8)

    ,customer_id        NUMBER(6) NOT NULL

    ,order_status       NUMBER(2)

    ,order_total        NUMBER(8,2)

    ,sales_rep_id       NUMBER(6)

    ,promotion_id       NUMBER(6)

)

PARTITION BY RANGE (order_date)

(

  PARTITION Part1 VALUES LESS THAN (to_date('2005-01-01', 'yyyy-mm-dd')),

  PARTITION Part2 VALUES LESS THAN (to_date('2006-01-01', 'yyyy-mm-dd')),

  PARTITION Part3 VALUES LESS THAN (to_date('2007-01-01', 'yyyy-mm-dd')),

  PARTITION Part4 VALUES LESS THAN (to_date('2008-01-01', 'yyyy-mm-dd')),

  PARTITION Part5 VALUES LESS THAN (MAXVALUE)

);

 

 

--要删除第三季度的数据

DELETE FROM rangeOrders partition(Part3);

  利用间隔分区将开始创建时没有分区的表创建为新的间隔分区表

--1.利用现有表Orders创建间隔分区表intervalOrders

CREATE TABLE intervalOrders

                 PARTITIONBY RANGE(order_date)

                 INTERVAL(NUMTOYMINTERVAL(1,'YEAR'))

                 (PARTITIONP1 VALUES LESS THAN (to_date('2005-01-01','yyyy/mm/dd')))

       AS SELECT * FROM Orders;

 

 

--2.查询分区情况

        SELECTtable_name,partition_name

       FROMuser_tab_partitions

         WHERE table_name=UPPER('intervalOrders');

--3.向表插入'2013/01/01'数据

insert into intervalOrders values(1001,to_date('2013-01-01','yyyy-mm-dd'),'direct',101,0,1000,153,null);

 

--插入数据

        INSERTINTO sales2 VALUES (1,'a',to_date('2013-08-1'),10,'1');

--获得分区情况

        SELECTtable_name,partition_name

       FROMuser_tab_partitions

         WHERE table_name=UPPER('sales2');

--查询输出结果，系统自动根据输入数据情况创建新分区“SYS_P82”

     TABLE_NAME  PARTITION_NAME

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

        SALES2        P1

        SALES2        SYS_P82

--查询分区数据

        SELECT* FROM sales2 PARTITION(sys_P82);

--4.再次查询某一分区数据

 

 

  创建范围分区表

CREATE TABLE Stock_Received

(

     STOCK_ID VARCHAR2 (5),

     STOCK_DATE DATE NOT NULL,

     COST NUMBER (10)

)

PARTITION BY RANGE (STOCK_DATE)

(

     PARTITION S1 VALUES LESS THAN (TO_DATE ('01/04/2001',  'DD/MM/YYYY')),

     PARTITION S2 VALUES LESS THAN (TO_DATE ('01/07/2001',   'DD/MM/YYYY')),

     PARTITION S3 VALUES LESS THAN (MAXVALUE)

);

  利用间隔分区将开始创建时没有分区的表创建为新的间隔分区表

/*准备工作*/

--1.创建普通SALES表

CREATE TABLE SALES

(

  SALES_ID NUMBER,

  PRODUCT_ID VARCHAR2(5),

  SALES_DATE DATE NOT NULL

);

 

--2.自行向SALES表插入数据

 

/*实施步骤*/

--1.创建间隔分区表SALES3

CREATE TABLE SALES3

                 PARTITIONBY RANGE(SALES_DATE)

                 INTERVAL(NUMTOYMINTERVAL(3,'MONTH'))

                 (PARTITIONP1 VALUES LESS THAN (to_date('2013-04-1','yyyy/mm/dd')))

       AS SELECT * FROM SALES; --SALES表为已经创建的表

 

--2.查询分区情况

        SELECTtable_name,partition_name

       FROMuser_tab_partitions

         WHERE table_name=UPPER('sales3');

--3.查询某一分区数据

--4.自行向SALES3表插入数据

--5.再次查询某一分区数据