sqlplus常用操作和语句

日常登陆：

• 每次启动前要先打开服务！ 要是只用Oracle自带的sql*plus的话，只要启动OracleServiceORCL即可，

• 要是使用PL/SQL Developer等第三方工具的话，OracleOraDb11g_home1TNSListener也要开启

--普通账户登陆,@orcl可以不加，代表要访问的数据库。当只有一个时可以不输入
conn scott/tiger@orcl   
--sys正常登陆,也必须要加 as sysdba（这里账密输错也能登陆）,操作系统验证
--如果dba组中删除sys用户，这时就要用密码登陆，就是密码验证
--在oracle数据库没有启动时，sys就能连接上，所以sys用户不是数据库验证
conn sys/orcl as sysdba
--匿名sys登陆
conn / as sysdba  	
disc		--退出登陆
复制

• 可以直接右键sqlplus图标属性中的路径中加上 账/密，可跳过登陆操作

设置权限：

alter user scott account lock;			--加锁
alter user scott account unlock;		--解锁
alter user scott identified by tiger;	--设置密码（要有管理员权限）
grant sysdba to XXX		--授予权限
revoke sysdba from XXX		--收回权限
passw XXX	                --修改密码
复制

忘记密码：

alter user sys identified by newpass 		--修改密码（需要有权限）

--当没有用户登录时可以在cmd中输入sqlplus /nolog,然后再用sys匿名登陆获得权限
复制

缓冲区

• 缩写： L[IST]、A[PPEND]、I[NPUT]、CL[EAR] BUFF[ER]、C[HANGE]

ed f:\sql\test.sql
--编辑指定的文件
--如果没有文件会打开缓冲区
--最后的/也不能删除
r							--r、run、/都可以运行
list						--查看缓冲区内容
2							--切换到第二行（*在哪一行当前就在哪一行）
C/old/new					--更改当前行
i new						--在最后一行后添加一行
2 test						--将第二行修改成test
a new						--在当前行最后添加
c/old/new/					--将当前行中的old替换成new
c/text						--从当前行删除text
cl buff						--清除缓冲区中的所有行
del n						--删除第n行
del							--删除当前行
del n last					--删除第n行到最后一行
r							--运行
save f:\sql\test.sql		--保存缓存区内容到test.sql
get	f:\sql\test.sql			--将test.sql加载到缓冲区
start f:\sql\test.sql		--将test.sql加载到缓冲区，并运行
@ f:\sql\test.sql			--将test.sql加载到缓冲区，并运行
--缓冲区里不能有分号（get导入之后），脚本里可以有分号（直接start）
spool f:\sql\test.sql		--将直至	spool off之间的语句存到指定文件		
spool off					--假脱机的指令，相当于一个范围
复制

控制环境变量：

• 用show set 控制环境变量

show all					--得到所有的环境变量
show user					--得到单个环境变量
set pagesize				--设置环境变量
set echo on					--设置当载入脚本时，查看脚本中的sql语句（默认off）
set feedback on|6			--查看表中有多少行信息（默认为开，6行，可以on改为数字表示设置为几行）
set hea[ding] on			--设置数据是否显示列标题
set array[size] 20|n		--限制每次从数据库中获取的行数最多5000
set long 80|n				--设置显示LONG\CLOB\NCLOB值时的最长字节宽度最大为2GB				
复制

特别说下col[umn]	用于设置获取数据的格式，下面列举格式规范：
	a[n]	：为varchar类型的列的列内容设置宽度，如果设置为5实际为6则会换行输入
	9		：设置数字类型的位数		如：999 999
	$		：浮动货币符号					如：$99 
	L		：本地货币符号					如：L99
	.		：小数点的位置					如：999.9
	,		：千位分隔符						如：999,999
复制

col[umn] name for[mat] a5		--设置name列的字符长度为5
col money for [$]999,999		--设置money列的数字位数
col money hea[ding] '工资'		--将money列名改为工资
col money 						--查看你对这个列设置了什么格式
col money cle[ar]				--格式清空
复制

举例：

• 在F盘的sql文件夹下创建date.sql，内容如下：

conn scott/tiger
set line 120
set pagesize 10000
set heading off
set feedback off
spool f:\sql\a.txt			
select deptno||','||dname||','||loc from dept;
--||表示将连接符，将三列与 '，'连接起来
spool off
exit
复制

• 如果你的老板在你不在的情况下让你导出数据，老板又不会导入脚本，
• 这个时候需要写一个批处理文件，实现自动获取数据，如下：

sqlplus /nolog @c.sql		
exit
复制

• 登陆信息不能写在批处理文件中会被看到，所以只能写在脚本的开头
  exit可以使窗口一闪而过，显得更加专业，也可以发送快捷方式到桌面，并更改图标
• 如果导出的数据需要再导入excel中使用，可以将||连接符去掉，获得三列数据便于编辑
• 设置feedback为on，就可以统计获取了多少数据

————————————————

版权声明：本文为CSDN博主「今天一定」的原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/weixin_41852068/article/details/105304116