引言
/*
使用Oracle数据 在使用第三方工具时需要先启动两个服务
监听器服务：lsnrctl start
数据库服务：net start OracleServiceOrcl
登录方式：
dos窗口：sqlplus system/accp
第三方工具(pl/sql developer):
很久未使用 忘记密码怎么办？
sqlplus / as sysdba;
alter user system identified by accp;
scott普通用户 一般安装默认锁定状态locked 密码为tiger
alter user scott account unlock;
提供了两张常用示范表 emp、dept
*/
一、表空间
/*1.创建表空间*/
create tablespace worktbs --表空间名称
datafile 'D:\T146\WORDTBS01.dbf' --文件路径
size 10M --表空间大小
autoextend on next 10M --自动扩展10M
/*2.查看当前用户下的表空间*/
select * from dba_data_files;
--3.扩展表空间
/*3.1.修改表空间数据文件大小*/
alter database
datafile 'D:\T146\WORDTBS01.dbf'
resize 20M;
/*3.2.向表空间内添加数据文件*/
alter tablespace worktbs
add datafile
'D:\T146\WORDTBS02.dbf'
size 10M
autoextend on;
/*4.更改表空间为只读*/
ALTER TABLESPACE worktbs READ ONLY;
/*5.删除表空间*/
drop tablespace worktbs including contents and datafiles;
二、用户权限管理
--1.创建用户
CREATE USER king --用户名
IDENTIFIED BY 123456 --密码
DEFAULT TABLESPACE worktbs --表空间
[TEMPORARY TABLESPACE tablespace] --指定临时表空间 可省略
--2.对用户进行权限管理
grant connect,resource to king; --授予权限
revoke connect,resource from king;--回收权限
grant create session to king;
revoke create session from king;
grant insert,delete,update,select on scott.dept to king;--授予对表操作的权限
grant all on scott.emp to king;
--3.修改用户密码
alter user king identified by accp;
--4.删除用户
DROP USER king CASCADE; --当用户 模式下有模式对象时需要添加CASCADE关键字进行级联删除
--删除表
drop table depts;
--创建表(不要表数据 只要表结构)
create table depts
as
select * from scott.dept where 1>1;
--创建表(复制表结构以及表数据)
create table depts
as
select * from scott.dept ;
--查看表
select * from depts;
三、序列
--1.创建序列
create sequence seq_dept --指定序列名称
start with 10 --指定序列起始值
increment by 10 --指定增长量
maxvalue 50 --指定序列最大值
nocycle --不循环
cache 10; --缓存10个序列值
create sequence seq1;
/*
序列中有两个非常重要的属性
1.nextval 下一个值
2.currval 当前值
在序列没有使用过nextval之前，不能使用currval获取序列的当前值，因为序列尚未初始化
*/
--2.修改序列
alter sequence seq1
increment by 14
maxvalue 100
minvalue 10
cycle
cache 7;
--3.使用序列
select seq_dept.nextval from dual;
select seq_dept.currval from dual;
insert into depts values(seq_dept.nextval,'hr','shenzhen');
select * from depts;
select seq1.nextval from dual;
select seq1.currval from dual;
--4.删除序列
drop sequence seq_dept;
--5.使用GUID 生成唯一标识符 推荐在并行环境下使用
select sys_guid() from dual;
四、同义词
/*创建员工表*/
create table employee(
empon number(4) not null,
ename varchar(10),
job varchar(9),
mgr number(4),
hiredate date,
sal number(9,2),
comm number(7,2),
deptno number(2)
);
select * from employee
--将访问员工表的权限授予A_oe用户
GRANT SELECT ON employee TO A_oe;
--创建私有同义词
CREATE OR REPLACE SYNONYM emp FOR employee;
--以A_oe用户登录
SELECT * FROM emp;
--创建公有同义词
CREATE PUBLIC SYNONYM public_emp FOR A_hr.employee;
---以A_oe用户登录
select * from public_emp
--删除私有同义词
DROP SYNONYM emp;
--删除公有同义词
DROP PUBLIC SYNONYM public_emp;
五、索引
/*1.B树索引*/
create unique index index_empno on employee(empno);
/*2.反向键索引*/
create index index_reverse_empno on employee(empno) reverse;
/*3.位图索引*/
create bitmap index index_bit_job on employee(job);
/*4.重建索引*/
alter index index_reverse_empno rebuild noreverse;
/*5.删除索引*/
drop index index_bit_job;
六、分区表
--1.创建分区表 范围分区
create table sales1(
sales_id NUMBER NOT NULL,
product_id varchar2(5),
SALES_DATE DATE,
SALES_COST NUMBER(10),
AREACODE VARCHAR2(5)
)
partition BY RANGE(SALES_DATE)
(
partition part1 values less than(to_date('2013/04/1','yyyy/mm/dd')),
partition part2 values less than(to_date('2013/07/1','yyyy/mm/dd')),
partition part3 values less than(to_date('2013/10/1','yyyy/mm/dd')),
partition part4 values less than(to_date('2014/01/1','yyyy/mm/dd')),
partition part5 values less than(maxvalue)
)
--查询分区情况
select table_name,partition_name
from user_tab_partitions
where table_name=UPPER('sales1');
--插入数据
insert into sales1 values(1000,'p1',to_date('2013/07/1','yyyy/mm/dd'),1000,'A1')
insert into sales1 values(1001,'p2',to_date('2013/11/13','yyyy/mm/dd'),1001,'A2')
--使用分区查询
select * from sales1 partition(part2)
--删除分区
alter table sales1 drop partition(P1);
--2.创建分区表 间隔分区
create table sales2(
sales_id NUMBER NOT NULL,
product_id varchar2(5),
SALES_DATE DATE,
SALES_COST NUMBER(10),
AREACODE VARCHAR2(5)
)
partition by range(sales_date)
interval(numtoyminterval(3,'Month'))
(partition p1 values less than (to_date('2013/07/1','yyyy/mm/dd')));
--插入数据
insert into sales2 values(1000,'p1',to_date('2013/08/1','yyyy/mm/dd'),1000,'A1')
insert into sales1 values(1001,'p2',to_date('2013/11/13','yyyy/mm/dd'),1001,'A2')
--获取分区情况
select table_name,partition_name
from user_tab_partitions
where table_name=UPPER('sales2');
--查看分区数据
select * from sales2 partition(sys_P41)
七、PL/SQL基础知识
/*1.PL/SQL组成部分
[DECLARE]
--声明部分，在此声明PL/SQL用到的变量、类型及游标，以及局部的存储过程和函数
BEGIN
--执行部分：过程及SQL语句，即程序的主要部分
[EXCEPTION]
--异常处理部分：错误处理
END;
--执行部分不可省略
*/
/*2.PL/SQL变量与常量语法
变量名 数据类型[（范围大小）][:= 值]; --变量声明赋值语法
常量名 CONSTANT 数据类型 := 值; --常量声明赋值语法
*/
--例：
DECLARE
v_ename VARCHAR2(20);
v_rate NUMBER(7,2)；
c_rate_incr CONTRANT NUMBER(7,2) := 1.10;
BEGIN
--赋值方式一，通过SELECT INFO
SELECT ename,sal* c_rate_incr INTO v_ename, v_rate
FROM employee
WHERE empno = '7788';
--赋值方式二,通过赋值操作符“：=”
v_ename := 'SCOTT';
END;
/*3.PL/SQL控制语句*/
--3.1条件控制
--3.1.1
BEGIN
IF false THEN
DBMS_OUTPUT.PUT_LINE('t142');
ELSE
DBMS_OUTPUT.PUT_LINE('AAAAAA');
END IF;
END;
--3.1.2
DECLARE
v_num NUMBER(2,0) := 10;
BEGIN
IF v_num>10 THEN
DBMS_OUTPUT.PUT_LINE('100');
ELSIF v_num=10 THEN
DBMS_OUTPUT.PUT_LINE('10');
ELSE
DBMS_OUTPUT.PUT_LINE('0');
END IF;
END;
--3.2.1循环控制-LOOP
DECLARE
v_num NUMBER(2,0) := 1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE(v_num);
v_num+:=1;
EXIT WHEN v_num=11;
END LOOP;
END;
--3.2.2循环控制-FOR
DECLARE
v_num NUMBER(2,0) := 1;
v_num2 NUMBER(2,0);
BEGIN
FOR v_num2 IN REVERSE 1 .. 5 LOOP
DBMS_OUTPUT.PUT_LINE(v_num2);
END LOOP;
END;
八、异常处理
--1.预定义异常
create table employee as select * from scott.emp;
select * from employee;
--未进行异常处理
DECLARE
v_ename employee.ename%TYPE;
BEGIN
SELECT ename INTO v_ename
FROM employee
WHERE empno=1234;
dbms_output.put_line('雇员名:'||v_ename);
END;
--进行异常处理
DECLARE
v_ename employee.ename%TYPE;
BEGIN
SELECT ename INTO v_ename
FROM employee
WHERE empno=1234;
dbms_output.put_line('雇员名:'||v_ename);
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('雇员号不正确');
WHEN TOO_MANY_ROWS THEN
dbms_output.put_line('查询只能返回单行');
WHEN OTHERS THEN
dbms_output.put_line('错误号：'||SQLCODE||'错误描述：'||SQLERRM);
END;
--2.定义自定义异常
--查询编号为7788的雇员的福利补助（comm列）。
DECLARE
v_comm employee.comm%TYPE;
e_comm_is_null EXCEPTION; --定义异常类型变量
BEGIN
SELECT comm INTO v_comm FROM employee WHERE empno=7788;
IF v_comm IS NULL THEN
RAISE e_comm_is_null;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('雇员不存在！错误为:'||SQLCODE||SQLERRM);
WHEN e_comm_is_null THEN
dbms_output.put_line('该雇员无补助');
WHEN others THEN
dbms_output.put_line('出现其他异常');
END;
九、游标
/*显式游标使用步骤*/
declare
v_ename employee.ename%type;
v_sal employee.sal%type;
--1.声明游标
cursor cursor_emp is
select ename,sal from employee
for update of sal;
begin
--2.打开游标
open cursor_emp;
--3.提取游标 读取游标中存储数据 使用fetch...into 还可以使用for循环读取
/*loop
fetch cursor_emp into v_ename,v_sal;
exit when cursor_emp%notfound;
dbms_output.put_line('雇员姓名：'||v_ename||' 雇员薪水：'||v_sal);
end loop;*/
--提取游标 进行修改
loop
fetch cursor_emp into v_ename,v_sal;
exit when cursor_emp%notfound;
update employee set sal = sal+200
where current of cursor_emp;
end loop;
--4.关闭游标
close cursor_emp;
end;
十、存储过程
/*1.创建存储过程*/
CREATE OR REPLACE PROCEDURE add_employee(
eno NUMBER, --输入参数,雇员编号
name VARCHAR2, --输入参数,雇员名称
salary NUMBER, --输入参数,雇员薪水
job VARCHAR2 DEFAULT 'CLERK', --输入参数,雇员工种默认'CLERK'
dno NUMBER --输入参数,雇员部门编号
)
IS
BEGIN
INSERT INTO employee
(empno,ename,sal,job,deptno)VALUES (eno,name,salary,job, dno);
END;
/*2.sql*plus下调用存储过程*/
--EXEC add_employee(1111,'MARY',2000,'MANAGER',10);
--EXEC add_employee(dno=>10,name=>'MARY',salary=>2000,eno=>1112, job=>'MANAGER');
--EXEC add_employee(1113,dno=>10,name=>'MARY',salary=>2000,job=>'MANAGER');
--EXEC add_employee(1114,dno=>10,name=>'MARY',salary=>2000);
/*3.PL/SQL下调用存储过程*/
BEGIN
--按位置传递参数
add_employee(2111,'MARY',2000,'MANAGER',10);
--按名字传递参数
add_employee(dno=>10,name=>'MARY',salary=>2000,eno=>2112, job=>'MANAGER');
--混合方法传递参数
add_employee(3111,dno=>10,name=>'MARY',salary=>2000,job=>'MANAGER');
--默认值法
add_employee(4111,dno=>10,name=>'MARY',salary=>2000);
END;
