oracle笔记(2021.06.14-2021.06.29)
一.创建表空间、创建用户并授权
1、建立表空间
--建立临时表空间
CREATE TEMPORARY TABLESPACE temp_name --创建名为temp_name的临时表空间
TEMPFILE 'D:\app\super\oradata\oracle\temp_name.DBF' -- 临时文件
SIZE 50M -- 其初始大小为50M
AUTOEXTEND ON -- 支持自动扩展
NEXT 50M MAXSIZE 20480M -- 每次增量为50M ,最大2048M
EXTENT MANAGEMENT LOCAL;
--建立表空间
CREATE TABLESPACE test_name --创建名为"test_name"的表空间
LOGGING
DATAFILE 'D:\app\super\oradata\oracle\test_name.DBF'
SIZE 50M -- 其初始大小为50M
AUTOEXTEND ON -- 支持自动扩展
NEXT 50M MAXSIZE 20480M -- 每次增量为50M ,最大2048M
2、创建用户并关联表空间
当我使用Oracle19c下面方式创建用户的时候，出现了ORA-65096: 公用用户名或角色名无效的错误，
我的理解应该是12c之后需要在用户名前加上C##或者c##。
CREATE USER myuser IDENTIFIED BY password -- 12c之后的版本，可以试试在用户名前加上c##
DEFAULT TABLESPACE test_name --表空间
TEMPORARY TABLESPACE temp_name; -- 临时表空间，关联任意一个都行
-- 创建表的时候没有关联表空间
CREATE USER myuser IDENTIFIED BY PASSWORD;
CREATE USER C##userName IDENTIFIED BY PASSWORD; -- 应该是12c之后都需要
--修改用户表空间
ALTER USER myuser DEFAULT TABLESPACE test_name;
3、用户授权\撤销授权
Oracle提供三种标准角色(role):connect/resource/dba。
a、connect role(连接角色)
临时用户，特指不需要建表的用户，通常只赋予他们connect role.
connect是使用oracle简单权限，这种权限只对其他用户的表有访问权限，包括select/insert/update和delete等。 拥有connect role 的用户还能够创建表、视图、序列（sequence）、簇（cluster）、同义词(synonym)、回话（session）和其他 数据的链（link)。
b、resource role(资源角色)
更可靠和正式的数据库用户可以授予resource role。
resource提供给用户另外的权限以创建他们自己的表、序列、过程(procedure)、触发器(trigger)、索引(index)和簇(cluster)。
c、dba role(数据库管理员角色)
dba role拥有所有的系统权限
--给用户授权
GRANT CONNECT/RESOURCE/DBA TO myuser;
--撤销用户权限
REVOKE CONNECT,RESOURCE FROM myuser;
4、删除表空间
--删除表空间
DROP TABLESPACE test_name INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS
二.创建表
1、数据类型
1、字符型
CHAR(size) ：固定长度，例如手机号等VARCHAR2(size)：可变长度，例如备注等 CLOB：可变长度字符数据，最大可存储4G数据
2、数值类型（数值型）
NUMBER：数值型，既可以表示整数，也可以表示小数 NUMBER(n) ：整数 NUMBER(P.S) ：数值型，总长度p，小数位最大为s位
3、日期型
DATE：包括年月日时分秒 TIMESTAMP：精度比DATE高一些，可以准确到毫秒
4、图片类型
BLOB：可以存放声音、图片、文件，最大可存储4G二进制的数据
二、创建表
1.一般语法
CREATE TABLE 表名(列名 列类型[长度] DEFAULT 默认值);
1.创建表person,并指定以下列的信息：
列名 列数据类型 长度 精度 默认值
id number 4
cname varchar2 20
birthday date
height number 3
weight number 5 2
country_code char 2 '01' --一个中文占2个字节 英文与数字分别占1个字节
--创建表
create table person(
id number(4),
cname varchar2(20),
birthday date,
height number(3),
weight number(5,2),
country_code char(2) default '01'
);
三、修改表（添加列、修改列、删除列）
1.添加列：
ALTER TABLE 表名 ADD (列名 列类型[长度] [default 默认值]);
2.修改列：
ALTER TABLE 表名 MODIFY (列名 列类型[长度] [default 默认值]);
3.删除列(删除单列)：
ALTER TABLE 表名 DROP COLUMN 列名;
4.删除列(删除多列)：
ALTER TABLE 表名 DROP (列名1,列名2.....);
5.删除表：
DROP TABLE 表名;
6.重命名表名：
RENAME 旧表名 TO 新表名;
7.截断表：
TRUNCATE TABLE 表名;
8.修改列名：
ALTER TABLE 表名 RENAME COLUMN 原列名 TO 新列名;
三.序列
Oracle序列：序列(Sequence)创建、使用、修改、删除，序列(Sequence)是用来生成连续的整数数据的对象。序列常常用来作为主键中增长列，序列中的可以升序生成，也可以降序生成。
1.创建
CREATE SEQUENCE 序列名称 SEQ_NAME
START WITH N
--初始序号 递增：默认MINVALUE 递减：默认MAXVALUE
INCREMENT BY N
--增长幅度 N为正 递增 N为负 递减
MINVALUE N | NOMINVALUE
--最小值 N | 无最小值设置
MAXVALUE N | NOMAXVALUE
--最大值 N | 无最大值设置
CACHE N | NOCACHE
--缓存 N个序号 | 无缓存 默认缓存20个序号 --尽量设置大一点的值
CYCLE | NOCYCLE ;--循环 达到极值时是否从新循环生成序号
2.修改
ALTER SEQUENCE SEQ_NAME CYCLE; --修改循环
ALTER SEQUENCE SEQ_NAME INCREMENT BY 3; --修改涨幅
3.删除
DROP SEQUENCE SEQ_NAME;
四.存储过程和存储函数
一、存储过程
1.定义
存储过程是存储在数据库中提供所有用户程序调用的子程序，定义存储过程的关键字为procedure。
2.创建存储过程
create [or replace] procedure 存储过程名
[(参数1 类型，参数2 out 类型……)]
as
变量名 类型；
begin
程序代码体
end;
3.实例
----------示例一：无参无返------------------
create or replace procedure p1
or replace代表创建该存储过程时，若存储名存在，则替换原存储过程，重新创建
--无参数列表时，不需要写()
as
begin
dbms_output.put_line('hello world');
end;
--执行存储过程方式1
set serveroutput on;
begin
p1();
end;
--执行存储过程方式2
set serveroutput on;
execute p1();
-------示例二：有参有返-------------------------
create or replace procedure p2
(name in varchar2,age int,msg out varchar2)
--参数列表中，声明变量类型时切记不能写大小，只写类型名即可，例如参数列表中的name变量的声明
--参数列表中，输入参数用in表示，输出参数用out表示，不写时默认为输入参数。
------------输入参数不能携带值出去，输出参数不能携带值进来，当既想携带值进来，又想携带值出去，可以用in out
as
begin
msg:='姓名'||name||',年龄'||age;
--赋值时除了可以使用：=，还可以用into来实现
--上面子句等价于select '姓名'||name||',年龄'||age into msg from dual;
end;
--执行存储过程
set serveroutput on;
declare
msg varchar2(100);
begin
p2('张三',23,msg);
dbms_output.put_line(msg);
end;
-------示例三：参数列表中有in out参数---------------
create or replace procedure p3
(msg in out varchar2)
--当既想携带值进来，又想携带值出去，可以用in out
as
begin
dbms_output.put_line(msg); --输出的为携带进来的值
msg:='我是从存储过程中携带出来的值';
end;
--执行存储过程
set serveroutput on;
declare
msg varchar2(100):='我是从携带进去的值';
begin
p3(msg);
dbms_output.put_line(msg);
end;
-------- 示例三：参数列表中有in out参数-----------------------------
create or replace procedure p3
(msg in out varchar2)
--当既想携带值进来，又想携带值出去，可以用in out
as
begin
dbms_output.put_line(msg); --输出的为携带进来的值
msg:='我是从存储过程中携带出来的值';
end;
--执行存储过程
set serveroutput on;
declare
msg varchar2(100):='我是从携带进去的值';
begin
p3(msg);
dbms_output.put_line(msg);
end;
4.总结：
1.创建存储过程的关键字为procedure。
2.传参列表中的参数可以用in，out，in out修饰，参数类型一定不能写大小。列表中可以有多个输入输出参数。
3.存储过程中定义的参数列表不需要用declare声明，声明参数类型时需要写大小的一定要带上大小。
4.as可以用is替换。
5.调用带输出参数的过程必须要声明变量来接收输出参数值。
6.执行存储过程有两种方式，一种是使用execute，另一种是用begin和end包住。
存储过程虽然有很多优点，但是它却不能使用return返回值。当需要使用return返回值时，我们可以使用函数
二、存储函数
1.函数与存储过程的结构类似，但是函数必须有一个return子句，用于返回函数值。
create or replace function f1
return varchar--必须有返回值，且声明返回值类型时不需要加大小
as
msg varchar(50);
begin
msg := 'hello world';
return msg;
end;
--执行函数方式1
select f1() from dual;
--执行函数方式2
set serveroutput on;
begin
dbms_output.put_line(f1());
end;
三、存储过程与存储函数的区别和联系
相同点：1.创建语法结构相似，都可以携带多个传入参数和传出参数。
2.都是一次编译，多次执行。
不同点：1.存储过程定义关键字用procedure，函数定义用function。
2.存储过程中不能用return返回值，但函数中可以，而且函数中必须有return子句。
3.执行方式略有不同，存储过程的执行方式有两种（1.使用execute2.使用begin和end），函数除了存储过程的两种方式外，还可以当做表达式使用，例如放在select中（select f1() form dual;）。
总结：如果只有一个返回值，用存储函数，否则，一般用存储过程。
五.触发器
触发器是一种在事件发生时隐式地自动执行的PL/SQL块，不能接受参数，不能被显式调用
语法：
CREATE OR REPLACE TRIGGER trigger_name
< before | after | instead of > < insert | update | delete> ON table_name
[FOR EACH ROW]
WHEN (condition)
DECLARE
BEGIN
END;
语法解释：
trigger_name：触发器名称
before | after : 指定触发器是在触发事件发生之前触发还暗示发生之后触发
trigger_event：触发事件，在DML触发器中主要为insert、update、delete等
table_name：表名，表示发生触发器作用的对象
for each row：指定创建的是行级触发器，若没有该子句则创建的是语句级触发器
when trigger_condition：添加的触发条件
trigger_body：触发体，是标准的PL/SQL语句块
