ORACLE相关：表空间、序列、索引、分区、游标、存储过程、分区等创建

创建表空间 create tablespace text datafile F db ordata hibernate text dbf size 10m AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO 创建用户 create user text identified by text default tablespace text 为用户分配权限 grant connect resource dba to text 用户登录表的结构 create table street idNUMBER 5 primary key not null p id NUMBER 5 nameVARCHAR2 18 not null constraint s fk p id foreign key p id references district id 创建表 create table district idNUMBER 5 primary key not null nameVARCHAR2 18 not null 创建序列 create sequence seq street start with 1 increment by 1 nomaxvalue cache 10 create sequence seq district start with 1 increment by 1 nomaxvalue cache 10 drop table street drop table district delete district delete street select from street select from district delete district where id 6 PL SQL 插入数据 DECLARE v id NUMBER 5 7 v name VARCHAR2 18 汉阳区 BEGIN insert into district values v id v name EXCEPTION When others then DBMS OUTPUT PUT LINE 插入数据失败 END IF THEN语句 DECLARE num1 number 5 num2 number 1 num3 number 3 result varchar2 20 BEGIN IF num1 num2 THEN result num1 is big ELSIF num1 10 THEN EXIT END IF DBMS OUTPUT PUT LINE num4 END LOOP END WHILE LOOP循环 DECLARE num5 number 1 BEGIN WHILE num5 1 THEN RAISE temp ex END IF DBMS OUTPUT PUT LINE 用户不存在 EXCEPTION WHEN temp ex THEN DBMS OUTPUT PUT LINE 用户已存在 END 游标 DECLARE id1 number 5 name1 varchar2 18 声明一个游标 CURSOR c district is select from district where id 6 BEGIN 打开游标 OPEN c district 判断游标是否返回记录 IF c district NOTFOUND THEN DBMS OUTPUT PUT LINE 没有找到相应的数据 ELSE 从游标中读取数据 FETCH c district into id1 name1 DBMS OUTPUT PUT LINE id1 name1 END IF 关闭游标 CLOSE c district END FOR循环操作游标 DECLARE id1 number 5 name1 varchar2 18 CURSOR c dis is select from district BEGIN FOR c dis1 IN c dis LOOP id1 c dis1 id name1 c dis1 name DBMS OUTPUT PUT LINE id1 name1 END LOOP END 存储过程 创建过程 CREATE OR REPLACE PROCEDURE proc showInfo 声明一个输入参数 name1 IN varchar2 as 声明一个游标 在查询语句中使用输入参数作为查询条件 CURSOR c dist IS select id name from district where name name1 BEGIN FOR c diss IN c dist LOOP DBMS OUTPUT PUT LINE c diss id c diss name END LOOP END 调用过程 BEGIN proc showInfo 123 END 删除过程 DROP PROCEDURE proc showInfo 创建函数 CREATE OR REPLACE FUNCTION getCount 声明输入参数 id1 IN number 声明返回类型 return number AS f count number BEGIN 使用INTO语句将结果赋值给变量 select count into f count from district where id id1 使用RETURN语句返回 return f count END 调用函数 DECLARE 声明变量接收函数的返回值 v count number BEGIN v count getCount 6 DBMS OUTPUT PUT LINE v count END 删除函数 DROP FUNCTION GETCOUNT 创建包头 CREATE OR REPLACE PACKAGE emp package AS 声明存储过程 用于插入一条记录 PROCEDURE my proc id1 number name1 varchar2 END emp package 创建包体 CREATE OR REPLACE PACKAGE BODY emp package AS 存储过程的实现 PROCEDURE my proc id1 number name1 varchar2 AS BEGIN INSERT INTO district VALUES id1 name1 END my proc END emp package 包的调用 BEGIN emp package my proc 9 123 END 创建视图 CREATE OR REPLACE VIEW v district AS select from district 查询视图里的数据 SELECT FROM v district 删除视图 DROP VIEW v district 创建数据库链 CREATE DATABASE LINK link goods connect to text identified by text USING DESCRIPTION ADDRESS LIST ADDRESS PROTOCOL TCP HOST 10 0 0 11 PORT 1521 CONNECT DATA SERVICE NAME PRD32 访问数据链 SELECT FROM goods link goods 提高数据库性能 查看库高速缓存在执行调用阶段的不命中数 select sum pins 请求数 sum reloads 不命中数 from v librarycache 查看库缓存的使用率 select sum pins reloads sum pins library cache from v librarycache 查看数据字典高速缓存不命中数 select sum gets 请求数 sum getmisses 不命中数 from v rowcache 查看数据字典高速缓存的使用率 select sum gets getmisses usage fixed sum gets 数据字典使用率 from v rowcache 查看数据库缓存的命中率 select 1 select value from v sysstat where name physical reads select value from v sysstat where name consistent gets select value from v sysstat where name db block gets from select name value from v sysstat where name in db block gets consistent gets physical reads 创建索引 create unique index d name on street name 修改索引 alter index d name rebuild storage initial 1m next 512k 删除索引 drop index d name 创建范围分区 create table rang active id number name varchar2 20 partition by range id partition p 1 values less than 6 tablespace space1 partition p 2 values less than 7 tablespace space2 partition p 3 values less than maxvalue tablespace space3 创建列表分区 create table list active id number address varchar2 20 partition by list address partition l 1 values 北京 tablespace space1 partition l 1 values 广州 tablespace space1 partition l 1 values 上海 tablespace space1 创建散列分区 create table hash active id number name varchar2 20 partition by hash id partition h 1 tablespace space1 partition h 2 tablespace space2 本地索引与分区表的创建 create table dept id number name varchar2 20 partition by range id partition d p1 values less than 10 tablespace dp1 partition d p2 values less than 20 tablespace dp2 partition d p3 value less than maxvalue tablespace dp3 根据表分区创建本地索引分区 create index d index on dept id local partition d p1