标签：存储 name dbms put oracle id select

oracle基础sql

docker安装oracle

#拉取oracle镜像
docker pull registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g
#创建容器，并绑定容器到1521端口
docker run -d -p 49160:22 -p 49161:1521  registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g
#查看对应的容器
docker ps
#以命令的方式进入容器
docker exec -it <container_id> /bin/bash
#进入容器后切换为root用户，密码为helowin
参考连接 https://blog.csdn.net/chy555chy/article/details/124345973
#创建软连接
ln -s $ORACLE_HOME/bin/sqlplus /usr/bin
#修改配置文件
vi /home/oracle/.bashrc

export ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_2
export ORACLE_SID=helowin
export PATH=$ORACLE_HOME/bin:$PATH

su - oracle
sqlplus /nolog
conn / as sysdba
select instance_name from v$instance;
show user;
#创建自己的用户
CREATE USER cxk IDENTIFIED BY caoxuekun;
GRANT CONNECT, RESOURCE TO cxk;
#登录
1.本地登录 sqlplus / as sysdba
2.账号密码登录 sqlplus user/passwd

#查看是否允许远程连接
SELECT value FROM V$parameter where name = 'CXK'; #目前暂不做考虑
ALTER SYSTEM SET CXK = shared SCOPE = both ;

#授权
SQL > grant dba to xxx; //给用户分配操作数据库的权限
SQL> grant create session to  xxx ;   //授予新登陆的用户创建权限
 GRANT CREATE USER,DROP USER,ALTER USER ,CREATE ANY VIEW , DROP ANY VIEW,EXP_FULL_DATABASE,IMP_FULL_DATABASE, DBA,CONNECT,RESOURCE,CREATE SESSION TO CXK;

#监听器
输入 lsnrctl start 命令来启动监听器。
等待一段时间，从控制台输出中可以看出监听器是否已成功启动。如果您看到 "The command completed successfully" 或类似的提示信息，则监听器已经成功启动。
若要确保监听器已正确启动，请使用以下命令检查Oracle网服务是否正在运行：
lsnrctl status
如果您想停止监听器，请使用以下命令：
lsnrctl stop

oracle中对应的mysql的语句

创建表

CREATE TABLE employees (
	id number(10) primary key,
	name varchar2(50),
	department varchar2(50)
);

oracle的数据类型

Oracle 数据库内置的数据类型可分为6类：字符类型、数值类型、日期类型、 LOB 类型、二进制类型和行类型。

字符类型：

CHAR：定长的字符数据类型，最大长度为 127，可存储任何字符，包括 ASCII 字符、汉字、日文平假名、片假名等。
VARCHAR：可变长度的字符数据类型，最大长度为 8000，可存储多种字符，包括 ASCII 字符、汉字、日文平假名、片假名等。
CLOB：定长的二进制大对象数据类型，最大长度为 2 
8
 （2的8次方），存储二进制数据，可存储多个字符或表达式，因此也被称为 BLOB。
数值类型：

NUMBER：定长的数值数据类型，最大值为 99,999,999，可存储任何有效的数字，包括整数和浮点数。
FLOAT：定长的单精度浮点数数据类型，最大值为 32.00，可存储双精度浮点数，包括小数部分和指数部分。
日期类型：

DATE：标准的日期数据类型，包括年、月、日，格式为 YYYY-MM-DD。
TIME：标准的时间数据类型，包括小时、分钟、秒，格式为 HH:MM:SS。
TIMESTAMP：标准的日期时间数据类型，包括年、月、日、小时、分钟、秒，精确到秒，格式为 YYYY-MM-DD HH:MM:SS。
LOB类型：

LBCLOB（Variable Large Char not Currently 21 bits）：变长的二进制大对象数据类型，存储二进制数据，可存储多个字符或表达式，因此也被称为 BLOB。
CLOB：与 LOB 类似，但是存储的是二进制数据，因此也称为 CLOB。
二进制类型：

VARBINARY（Variable Binary Large Object）：变长的二进制大对象数据类型，存储二进制数据，最大长度为 8G（2的40次方），因此也被称为 BLOB。
行类型：

ROWID（RowID）：唯一标识一行的整数类型。

oracle的关键字

标识符: rowid(oracle内部使用，用于存储行的物理位置)
行号: rownum,在sql查询得到结果之后,给给每一行添加一个行号
合并列的连接操作:  select name || sex from 表;
空值: 在oracle中空值并不是空字符串,利用 where name is NULL进行判断
      在oracle中利用NVL区分空值和空字符串，比喻 select NVL(phone,'unknown phone number')，如果对应的phone字段是空值，那么将其转换为后面的字符串
distinct: 去除重复的行
any: 将一个值与某个列表中的值进行比较,eg： select xxx from table where age > any(2,56,7) 。 只要age大于any列表中其中一个就显示对应行
all: 将一个值与某个列表中所有值进行比较，eg: select xxx from  table where age >  all(1,34,5)
and,or:如果and和or同时存在，那么and的优先级大于or
连接： select * from t1,t2就是将t1和t2做笛卡尔积，也就是t1的一行和t2的每一行做一个连接，注意这里没有限制连接的条件
      其余的连接就是利用join的内连和外连
exists,not exists:注意exists比in的效率要高，应该尽可能使用exists而不是in
			exists并不关心有多少行返回，只是关心对应的子查询是否有返回
			eg: select employee_id from employees_outer outer where exists (select employee_id from employees_inner inner where inner = outer.employee_id)
集合操作符: union,union all,intersect,minus
		  union:将两个查询组合起来，剔除重复的行
		  union all:将两个查询组合起来
		  intersect: 返回两个查询共有的行
		  minus: 第一个查询的结果剔除第二个查询出结果的剩余行
		  集合操作符的组合使用，优先级是从上往下的，但是为了防止版本变化，一般在写sql的时候，回主动添加括号

decode函数: select decode(id,
                        1,'book',
                        2,'video',
                        'bad') from 表， 如果对应id是1，那么返回book,如果对应的id是2，那么返回video,如果id既不是1，也不是2，返回bad
                        
case表达式
	select	
	   case id
		   when 1 then 'book'
		   when 2 then 'video'
		   else 'bad'
	   end
	from 表;

    select
	   case id
		   when id = 1 then 'book'
		   when id = 2 then 'video'
		   else 'bad'
	   end
	from 表;


#merge关键字-->我们需要找到对应mysql中的对应操作
	merge into 字句 指明合并操作的目标表(即行要合并到的表)
	using... on 字句指定表连接
	when matched then 字句指定当一行满足using...on字句的条件时要执行的操作
	when not match  then 字句指定当一行不满足using...on字句条件时要执行的操作

案例：
create table T1
(
  id   NUMBER,
  name VARCHAR2(200)
)
create table T2
(
  id   NUMBER,
  name VARCHAR2(20)
)

insert into t1 (ID, NAME) values (1, '和平');
insert into t1 (ID, NAME) values (2, '中国');
insert into t1 (ID, NAME) values (3, '朝鲜');
insert into t1 (ID, NAME) values (4, '俄罗斯');


insert into t2 (ID, NAME) values (2, '改天');
insert into t2 (ID, NAME) values (5, '河南');

merge合并语句的案例: using...on的作用就是将t2中的每一行的id和t1中的id进行比较，如果相等进行相关的处理，如果不相等进行相关的处理
merge into t1 using t2 on (t1.id = t2.id)
when matched then   相等情况的一个处理
  update set t1.name = t2.name
when not matched then  不相等情况的一个处理
  insert (id,name) values(t2.id,t2.name);

对应mysql中的替代品

oracle关键函数

-- 字符函数
   将字符转换为ascii:  ascii(x)
   将ascii转换为字符:  chr(x)
   连接两个字符串:	   concat(x,y)
   获取字符串的长度： length(字符串)
   大小写的转换: upper(x),lower(x)
   nvl:如果是空值，转换为指定的值，eg nvl(字段，转换的值)
-- 数值函数

-- 转换函数

-- 正则函数

-- 聚合函数
	avg,max,count,min,sum
-- 日期函数
	 to_char(时间字段,'yyyy-MM-dd')
	 to_date：将字符串转换为时间 select to_date('2021-12-10','yyyy-MM-dd')as k from dual;
	 时间戳，这里暂放
	 时间这一块应该有比较多的东西，但是目前我们暂放，因为我们主体还是mysql的操作

select

# 排序
select * from EMPLOYEES order by id asc;

# 去重
select distinct  name from EMPLOYEES;
# group by 
select name,count(1) from employees group by name;
# between and 
select * from employees where id between 1 and 4;
#in 多个字段
select * from employees where  name in ('a','c');
select * from employees where ( name , department) in (('a','b'),('a','d'));

#分页的写法
select * from (
	select a.*,ROWNUM r__ from (
		select * from EMPLOYEES order by id asc
	)a where rownum <= (2*3)
) where r__ > 3;	
#对应mysql的分页的写法 5是开始的记录，3是往后3条
select * from account limit 5,3;

#mysql中的with rollup  主要的功能就是在利用group up的sum之后，在对应列做一个统计
SELECT coalesce(name, '总金额'), SUM(money) as money FROM  test GROUP BY name WITH ROLLUP;

#对应oracle的用法
CREATE TABLE "CXK"."TEST" (
  "ID" VARCHAR2(10 BYTE) NOT NULL,
  "TITLE" VARCHAR2(25 BYTE),
  "UUID" VARCHAR2(10 BYTE),
  "MONEY" NUMBER(10,0),
  "NAME" VARCHAR2(25 BYTE)
);

INSERT INTO "CXK"."TEST" VALUES ('1', 'a', 'a', '12', '周伯通');
INSERT INTO "CXK"."TEST" VALUES ('2', 'b', 'b', '23', '老顽童');
INSERT INTO "CXK"."TEST" VALUES ('3', 'c', 'c', '45', '周伯通');
INSERT INTO "CXK"."TEST" VALUES ('4', 'd', 'd', '56', '老顽童');
INSERT INTO "CXK"."TEST" VALUES ('5', 'e', 'e', '52', '小顽童');

#oracle的rollup的用法
select coalesce(name,'统计')as t,sum(money)as money from test group by  rollup(name);

join

mysql其余的关键字

distinct 
group having 
between and 
in 
not in
union
union all
with rollup
exists
on duplicate key update

特殊的sql

#查询所有的表名
SELECT table_name FROM user_tables;

视图

#创建Oracle视图非常简单，可以使用以下SQL语句：
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
其中：
view_name 是你想要为视图命名的名称。
column1, column2, ... 是视图中包括的列的名称。
table_name 是从中创建视图的基础表的名称。
condition 是一个可选的条件，在视图中过滤数据时使用。


#查看所有的视图
select * from user_views;

函数

函数和过程唯一的区别就是函数有返回值


create or replace function get_hello_msg 
return varchar2 as
begin 
  return 'hello world';
end get_hello_msg;

select get_hello_msg() from dual;

存储过程

#创建存储过程
头部区域
用于编写最基本的存储过程头部标记，定义是否要创建一个替代原有存储过程的存储过程；决定是否定义参数；定义参数的类型（in out inout）；定义执行权限（Schema）。

声明区域
用于声明变量（要定义长度）包括cursor；

执行区域
用于执行业务逻辑代码，可以使用条件语句（选择、判断、循环。。。）来进行一些业务逻辑CRUD的处理；


#案例如下: 
create or replace procedure 存储过程名
as
begin
  ----------------------------
end;

#一个无参数的存储过程
create or replace procedure tk
as
begin
 dbms_output.put_line('hello world');
end;

#调用存储过程
call tk();

#删除存储过程
DROP PROCEDURE 存储过程的名称;

#查询存储过程
select * from dba_source where (type='PROCEDURE' OR type='PACKAGE' ) and owner = 'CXK';

/*
	下面是存储过程中为解决的问题
		存储过程中的select相关的操作
		存储过程查询的数据写入变量中
		存储过程中变量的定义
		存储过程中in ， out参数的处理
		
*/

存储过程插入数据到库中

create or replace procedure cd is
begin 
  insert into test(id,title,uuid,money,name) values(9,'d','d',93,'周伯通');
end ;

call cd();

存储过程中定义变量

create or replace procedure autoUpdateName is
  i number :=0;
  s number :=1000;
  t varchar2(200);
  v_time DATE;
  v_char_time varchar2(200);
  v_float float;
begin 
 loop
   i:=i+1;
   s:=s+i;
   exit when i=100;
 end loop;
   v_time := SYSDATE;
   t := '字符串的输出xxxx';
   select to_char(sysdate,'yyyy/mm/dd hh:mm:ss') as k into v_char_time from dual;
   v_float := 2.24;
  dbms_output.put_line(t);
  dbms_output.put_line(v_time);
  dbms_output.put_line(v_char_time);  
  dbms_output.put_line(v_float);    
end;


call autoUpdateName();

存储过程中输入-输出变量

在存储过程中使用变量，我们需要注意的是，in类型的变量不能够赋值，这是同时实验得出来得结论

create or replace procedure autoUpdateName( v_name in VARCHAR2) is
begin   
  dbms_output.put_line(v_name);    
end;

#触发存储过程的第一种方式
begin
  autoUpdateName('sdsdf');
  commit;
end;  

#触发存储过程的第二种方式
call autoUpdateName('xxxxdfsdf');




create or replace procedure autoUpdateName
(
       v_name in varchar2,
       age in number,
       kk in number,
       bc out number,
       bb out varchar2
) is
t varchar2(200);
begin
    t :=  concat('dddddd','ooppp') ;
    -- 传入的变量不能够修改，只能赋值给其他在存储过程中的变量，让其进行运算
    --   v_name := 'ddd';
    dbms_output.put_line('修改前的变量的值: ' ||t);
    bc := age + kk;
    bb := t;
    dbms_output.put_line( v_name);
    dbms_output.put_line(t);
    dbms_output.put_line(age);    
end;


declare
 bcb number := 10 ;
 bb varchar2(200);
begin
   autoUpdateName('23',20,10,bcb,bb);
   dbms_output.put_line('--------------'); 
   dbms_output.put_line(bcb); 
   dbms_output.put_line(bb);    
  commit;
end;

参考

参考：
https://blog.csdn.net/weixin_44740485/article/details/120995693

变量类型

Oracle 数据库内置的数据类型可分为6类：字符类型、数值类型、日期类型、 LOB 类型、二进制类型和行类型。

字符类型：

CHAR：定长的字符数据类型，最大长度为 127，可存储任何字符，包括 ASCII 字符、汉字、日文平假名、片假名等。
VARCHAR：可变长度的字符数据类型，最大长度为 8000，可存储多种字符，包括 ASCII 字符、汉字、日文平假名、片假名等。
CLOB：定长的二进制大对象数据类型，最大长度为 2 
8
 （2的8次方），存储二进制数据，可存储多个字符或表达式，因此也被称为 BLOB。
数值类型：

NUMBER：定长的数值数据类型，最大值为 99,999,999，可存储任何有效的数字，包括整数和浮点数。
FLOAT：定长的单精度浮点数数据类型，最大值为 32.00，可存储双精度浮点数，包括小数部分和指数部分。
日期类型：

DATE：标准的日期数据类型，包括年、月、日，格式为 YYYY-MM-DD。
TIME：标准的时间数据类型，包括小时、分钟、秒，格式为 HH:MM:SS。
TIMESTAMP：标准的日期时间数据类型，包括年、月、日、小时、分钟、秒，精确到秒，格式为 YYYY-MM-DD HH:MM:SS。
LOB类型：

LBCLOB（Variable Large Char not Currently 21 bits）：变长的二进制大对象数据类型，存储二进制数据，可存储多个字符或表达式，因此也被称为 BLOB。
CLOB：与 LOB 类似，但是存储的是二进制数据，因此也称为 CLOB。
二进制类型：

VARBINARY（Variable Binary Large Object）：变长的二进制大对象数据类型，存储二进制数据，最大长度为 8G（2的40次方），因此也被称为 BLOB。
行类型：

ROWID（RowID）：唯一标识一行的整数类型。


在块结构中有一个特殊的变量的类型,表示将cc这个变量定义同test表的id字段类型一致
declare
 cc test.id%TYPE :=10;
begin
   dbms_output.put_line('-----ss---------'); 
   cc := cc + 100;
   dbms_output.put_line(cc); 
  commit;
end;

程序块中变量的案例

#变量循环计算的一个案例
declare
  i number :=0;
  s number :=1000;
  t varchar2(200);
  v_time DATE;
  v_char_time varchar2(200);
  v_float float;
begin 
 loop
   i:=i+1;
   s:=s+i;
   exit when i=100; --这里不是赋值用“=”
 end loop;
   v_time := SYSDATE;
   t := '字符串的输出';
   select to_char(sysdate,'yyyy/mm/dd hh:mm:ss') as k into v_char_time from dual;
   v_float := 2.23;
  dbms_output.put_line(t);
  dbms_output.put_line(v_time);
  dbms_output.put_line(v_char_time);  
  dbms_output.put_line(v_float);    
end;

数组

条件

#格式：
IF condition THEN
 statement;
ELSIF condition THEN
 statement;
ELSE
 statement;
END IF;

#案例:
create or replace procedure autoUpdateName(
       a in number,
       b in varchar
) is
begin
  
     if a = 1 then
       dbms_output.put_line('a is 1'); 
     end if;
     
     if a = 1 then
       dbms_output.put_line('a is 1'); 
     elsif a = 2 then
       dbms_output.put_line('a is 2');             
     else
       dbms_output.put_line('a is other');        
     end if;

     if b = 'a' then
       dbms_output.put_line('b is a');               
     end if;
end;

call autoUpdateName(1,'a');

循环

#简单循环格式
LOOP
	statement;
	EXIT WHEN 变量=5;
END LOOP;	

#while循环格式
WHILE condition LOOP
	statement;
END LOOP;	

#for循环格式
FOR 变量 IN 1.。5 LOP
 statement;
 END LOOP;
 
#for循环得案例:
create or replace procedure autoUpdateName is
begin
  FOR v_num in 1.. 10 LOOP
      dbms_output.put_line(v_num);
  END LOOP;     
end;

call autoUpdateName();

游标

sql版本的迭代器

游标的步骤:
	1.申明一些变量，用于保存一行的列值
	2.申明游标，包含一个查询
	3.打开游标
	4.一次从游标中获取一行，将列值存储到步骤一声明的变量中，对变量进行相关的操作，比喻显示，运算
	5.关闭游标
	
select * from test;
1	1	a	a	12	周伯通
2	2	b	b	23	老顽童
3	3	c	c	45	周伯通
4	4	d	d	56	老顽童
5	5	e	e	52	小顽童
6	6	f	f	234	老顽童
7	7	d	d	23	周伯通
8	8	d	d	23	周伯通
9	9	d	d	93	周伯通
10	10	d	d	93	周伯通





-- 步骤一声明变量
declare
  v_id test.id%TYPE;  -- 将v_id定义为test表的id字段类型
  v_name test.name%TYPE;
  -- 步骤二 申明游标，包含一个sql查询
  cursor tt is select id,name from test;
begin  
  -- 步骤三 打开游标
  open tt;
  loop
    -- 步骤四 从游标中取得行
    fetch tt into v_id,v_name;
    --退出循环
    exit when tt%NOTFOUND;
    -- 进行相关的逻辑操作
    dbms_output.put_line(v_name);
  end loop;
  -- 步骤五 关闭游标
  close tt;
end;

游标与for循环

# 游标与for循环
declare
  v_id test.id%TYPE;
  v_name test.name%TYPE;
  -- 步骤二 申明游标，包含一个sql查询
  cursor tt is select id,name from test;
begin  
  -- for循环可以不用显示的打开和关闭游标
  for kk in tt loop
     dbms_output.put_line(kk.name);
     dbms_output.put_line(kk.id);
  end loop;
end;

open-for语句-游标

可以让一个游标重复使用

declare
   type t_product_cursor is ref cursor; -- 指向游标的指针
   v_product_cursor t_product_cursor;
   v_name test.name%TYPE;
begin
  open v_product_cursor for select name from test;
  loop
    fetch v_product_cursor into v_name;
    exit when v_product_cursor%NOTFOUND;
     dbms_output.put_line(v_name);
  end loop;
  
   open v_product_cursor for select name from t1;
  loop
    fetch v_product_cursor into v_name;
    exit when v_product_cursor%NOTFOUND;
     dbms_output.put_line(v_name);
  end loop;
end;

包

包可以包装函数和存储过程，对外以一个包的新式暴露

参考：
https://www.cnblogs.com/hoaprox/p/5316444.html

-- 包有两部分构成，一个是规范，一个包体

#步骤一： 创建pkk包的规范
create or replace package pkk is
  -- 定义过程1
  procedure tk;
  -- 定义函数1
  function get_hello_msg return varchar2;
end pkk;


# 步骤二： 创建pkk包的包体
参考： https://www.cnblogs.com/hoaprox/p/5316444.html  自己目前并不需要会写oracle的包体

#步骤三 调用pkk的函数和存储过程


#查看库中定义了的包
select *  from user_objectS where object_type= 'PACKAGE BODY';

通过https://it.cha138.com/tech/show-2963058.html 查看存储过程的包体

oracle用户

创建用户

# 创建用户
create user user_name identified by password


#对应的用户登录
connect user_name/password

#修改用户密码
alter user user_name identified by password;

#删除用户
drop user user_name;

# 注意后面的就是给对应的用户授权，比喻创建表，执行crud等，然后还有一个角色，角色就是多个特权的组合，然后将角色付给一个用户，那么这个用户就有对应角色的所有特权

序列

Oracle的序列是一种数据库对象，主要作用是用来产生唯一值
select invoice_seq.NEXTVAL from dual; # 返回下一个序列值
参考文档   https://www.yingsoo.com/news/database/71709.html

索引

#查看表的索引

触发器

DML语句：含有insert, update,delete的语句
行级触发器：每一行发生DML语句的时候，会触发
语级触发器：一个sql只会触发一次，无论这条sql修改了多少行

#触发器的创建
# 当test表发生删除时间的时候，会将数据插入到test_copy表中
create or replace trigger kk 
    before delete --指定触发机制为删除操作
    on test
    for each row -- 指定为行级触发

begin
  insert into test_copy(id,title,uuid,money,name) values( :old.id, :old.title, :old.uuid, :old.money, :old.name);
end;    

#触发器的禁用

#触发器的删除

#触发器的查找
查看库中是否有触发器：
	SELECT * FROM user_triggers
查看对应表上是否有触发器:
	SELECT trigger_name, trigger_type, triggering_event  FROM user_triggers WHERE table_name = 'TEST'; -- 在oracle中表名需要大写才能查的出来
-- 触发器的内容可以通过pl/sql查看得到

标签：存储,name,dbms,put,oracle,id,select
From： https://www.cnblogs.com/caoxuekun/p/17418457.html

oracle