学习第一季1.Oracle的使用

最新推荐文章于 2025-08-09 13:59:05 发布

孤岛_

最新推荐文章于 2025-08-09 13:59:05 发布

阅读量624

点赞数

CC 4.0 BY-SA版权

文章标签： books oracle date sql 数据库 join

本文链接：https://blog.youkuaiyun.com/yanshaobo0410/article/details/7198803

本文介绍了如何在命令行界面使用SQLPLUS进行Oracle数据库的操作，包括以管理员和普通用户身份登录，实例启动与关闭，执行SQL查询，使用缓冲区、变量、函数等，以及基本的系统函数与聚合函数应用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

doc 下操作oracle:

sqlplus sys/yanshaobo as sysdba   /*以管理员身份登录oracle*/
sqlplus scott/yanshaobo    /*以scott 用户登录oracle*/
sqlplus /nolog    /*实例没有打开时，用nolog 模式来打开*/

startup /*正常启动oracle*/
startup mount /*启动实例时同时启动 “控制文件” 但是不启动 “数据文件”*/
startup nomount /*启动实例时不启动 "控制文件" (此方式用于一个“控制文件” 或者多个 “控制文件” 丢失时使用，以便实例启动后创建“控制文件”)*/
shutdown immediate /*多数工作环境下通常使用的，也是oracle 建议使用的关闭模式 (迫使每个用户执行完SQL语句后，立即断开连接)*/
shutdown /*正常关闭数据库实例，只有等待所有用户都与数据库断开连接后，才关闭数据库实例 */
shutdown /*执行transaction 关闭迫使用户在当前执行完成后断开连接，恢复破坏的文件，数据文件不能同其它的数据库文件保持一致*/
shutdown abort /*一种严厉的手段，不到万不得已，不会使用*/

help index /*查询oracle在sqlplus 中的所有命令*/
? 命令 /*查询命令的使用方法*/
? SET /*查询SET 的使用方法*/
SET SQLBLANKLINES ON /*支持空格行*/
SELECT * FROM dept WHERE deptno = 10;
SELECT * FROM dept WHERE deptno = &tt; /*用替代变量的方式来执行查询(安排一个执行计划)*/
Enter value for tt : /*为变量填写一个值*/

list /*来查看在oracle的缓冲区的命令(简写为: l)*/
l 2 4 /*查看oracle缓冲区中第2行到第4行的命令*/
l 3 /*查看oracle缓冲区中第3行的命令*/

change /*简写(c)修改缓冲区中的内容*/
SELECT * FRON dept;
change /FRON/FROM 简写:c/N/M /*将FRON 修改成 FROM */
/ /*用来执行缓冲区中的内容*/

DEL /*删除缓冲区中的内容*/
DEL 4 /*删除缓冲区中的第四行*/

APPEND 简写 A /**在缓冲区的尾部追加信息**/
SELECT * ;
A FROM dept;
/
/*执行后的完整语句是*/
SELECT * FROM dept;

save d:\text.txt /*对缓冲区中的内容进行保存(保存路径为：d:\text.txt)*/
@d:\text.txt /*执行该路径下的sql语句*/
get d:\text.txt /*获取该路径下保存的sql语句，并不执行*/

edit /*用记事本来编辑sql语句*/
SELECT * FROM dept;
edit /*打开记事本，记事本的名字默认为 afiedt.buf*/

COL deptno HEADING "编号" ; /*给 deptno 添加标签*/

describe 简写desc /*查看表的描述*/

COL dname FORMAT A10 ; /*格式化输出(以字符形式输出)*/
COL deptno FORMAT A10 HEADING "部门编号";

/*重要的报表命令*/
BREAK ON pub /*以 pub 这一列重复的显示一条 */

COMP /*统计命令*/
COMP COUNT LABEL "计数" OF books_name ON pub;

spool d:\text.txt /*保存结果集到该路径下*/
SELECT * FROM dept;
spool off

/*语言分类*/
1.DDL :CREATE, ALTER ,DROP
CREATE TABLE abc(a varchar2(10), b char(10)); /*创建表abc*/
ALTER TABLE abc ADD c NUMBER; /*修改表abc的结构，给abc添加一个新列c*/
ALTER TABLE abc DROP COLUMN c;

2.DCL :GRANT, REVOKE
GRANT SELECT ON dept to tt; /*授权dept表的查询权力给 tt用户*/
REVOKE SELECT ON dept FROM tt; /*从tt 用户收回对dept表的查询权力*/

3 DML :SELECT, INSERT, DELETE, UPDATE
SELECT * FROM dual;
INSERT INTO abc(a,b) VALUES('abc','xy');
DELETE FROM abc;
UPDATE abc SET b = 'ttt' /*设置b这整列的值为ttt*/

/*常用系统函数*/
1、字符
length,ltrim,replace,rtrim,substr,trim
SELECT LENGTH('ABCDEF') FROM dual; /*查询 'ABCDEF' 字符的长度*/
SELECT LTRIM(' ABCEF') FROM dual; /*截掉左空格*/
SELECT RTRIM('ABC   ') FROM dual;   /*截掉右空格*/
SELECT TRIM('   ABC   ') FROM dual; /*截掉左右空格*/
SELECT SUBSTR('ABCDEFG',2,3) FROM dual; /*取子串*/
SELECT SUBSTR('ABCDEFG',LENGTH('ABCDEFG') - 3 + 1,3) FROM dual; /**间接实现右取串**/

2、日期
sysdate,current_date,next_day
SELECT SYSDATE FROM dual; /*查询当前系统时间*/
SELECT CURRENT_DATE FROM dual; /*查询当前系统时间*/
ALTER SESSION SET NLS_DATE_FORMAT = 'dd-mon-yyyy hh:mi:ss'; /*更改会话，设置系统日期，时间的输出格式*/
SELECT NEXT_DAY(sysdate,'星期三') FROM dual;   /*从当前时间来算，星期三的日期*/

3、转换
to_char,to_date,to_number
SELECT TO_CHAR(sysdate,'yyyy-mm-dd hh:mi:ss') FROM dual; /*把日期型转变成字符串*/
SELECT TO_DATE('12-3月-04') FROM dual; /*将字符串转变成日期型*/
SELECT empNo,ename,sal FROM emp WHERE hiredate > to_date('1982-01-01','yyyy-mm-dd');
SELECT TO_NUMBER('333') FROM dual; /*将字符型转变成数值型*/

/*查询出日期大于1982-10-11是的员工的名字，部门编号，部门名称，工资和职位*/
SELECT ename,dept.deptno,dept.dname,sal,job,hiredate
FROM emp INNER JOIN dept ON emp.deptno = dept.deptno
WHERE hiredate > (SELECT TO_DATE('11-10月-82') FROM dual)

/*查询出日期大于1982年的所有员工*/
SELECT * FROM emp WHERE TO_CHAR(hiredate,'yyyy') > '1982'

4、聚集函数
sum,avg,max,min,count
SELECT MAX(price) FROM books; /*从表books中求出price 的最大值*/
SELECT SUM(price) FROM books; /*计算出books表中price 的总和*/
SELECT MIN(price) FROM books; /*从表books中求出price 的最小值*/
SELECT COUNT(price) FROM books; /*查询表books的行数*/
SELECT COUNT(a1) FROM books; /*查询表books a1字段的记录数*/

5、其他
user,decode,nvl
SELECT USER FROM dual; /*查询当前登录的用户*/
SELECT SUM(DECODE(SEX,'男'，1，0)) 男人数,SUM(DECODE(SEX,'女',1,0)) 女人数 FROM dual;
SELECT a1,nvl(a2,'未输入') a2 FROM aa; /*某个字段有空值进用这个函数来填充*/
SELECT * FROM aa WHERE a2 IS NULL;   /*把a2字段中的空值查出来*/
SELECT * FROM aa WHERE a2 IS NOT NULL; /*把a2字段中的未空值查出来*/
SELECT * FROM aa ORDER BY a1 DESC;     /*把a1字段按降序排列*/
SELECT * FROM aa ORDER BY a1 ASC;       /*把a1字段按升序排列*/
SELECT DISTINCT a1 FROM aa;     /*去掉a1列重复的信息*/

6、分组语句
聚集函数不能出现在WHERE 子名后面，如果要使用聚集函数，首先要想到的就是 HAVING 并且 GROUP BY 必须出现在 HAVING 之前
SELECT pub, SUM(price*num) FROM books GROUP BY pub HAVING SUM(price)>50; /*按pub 这个字段分组求出价格和个数相乘得到的总收入，并且是当价格的总和大于50时*/
SELECT a1,COUNT(a1) FROM aa GROUP BY a1 HAVING COUNT(a1) >1; /*先根据a1这一列分组查询出a这列的总记录数，然后当查询出a1这列中有某条记录大于1时，会查询出有重复的记录的列的信息 */

7、模糊查询
SELECT * FROM aa WHERE a1 LIKE 'a_'; /*从aa表的a1字段中模糊查询出以 a 开头以单个任意字符结尾的信息*/
SELECT * FROM aa WHERE a1 LIKE 'a__'; /*从aa表的a1字段中模糊查询出以 a 开头以多个任意字符结尾的信息*/
SELECT * FROM aa WHERE a1 LIKE 'a%'; /*从aa表的a1字段中模糊查询出以 a 开头以一个(或多个任意) 字符结尾的信息*(最经典的用法)*/
SELECT * FROM aa WHERE a1 LIKE '_a'; /*以aa表的a1字段中模糊查询出以单个任意字符开头以a结尾的信息*/
SELECT * FROM aa WHERE a1 LIKE '__a'; /*以aa表的a1字段中模糊查询出以多个任意字符开头以a结尾的信息*/
SELECT * FROM aa WHERE a1 LIKE '%a'; /*以aa表的a1字段中模糊查询出以一个(或多个任意字符)开头以a结尾的信息*/
SELECT * FROM aa WHERE a1 LIKE '%a%'; /*以aa表的a1字段中模糊查询所有包含a 字符的信息*/

8、表的连接
SELECT eid 编号,ename 姓名,sex 性别,dname 部门名称 FROM dept JOIN emp ON dept.deptNo = emp.deptNo; /*标准写法，属于内联查询*/
SELECT eid 编号,ename 姓名,sex 性别,dname 部门名称 FROM dept LEFT OUTER JOIN emp ON dept.deptNo = emp.deptNo /*左外联查询，以左表为准，显示出左表的所有行*/
SELECT eid 编号,eanme 姓名,sex 性别,dname 部门名称 FROM dept RIGHT OUTER JOIN emp ON dept.deptNo = emp.deptNo /*右外联查询，以右表为准，显示出右表的所有行*/
9、子查询
SELECT * FROM dept WHERE deptNo IN (SELECT deptNo FROM emp WHERE SAL > 3000); /*查询出员工的工资大于3000的那个部门*/
SELECT * FROM dept WHERE deptNo EXISTS (SELECT deptNo FROM emp WHERE SAL > 3000); /*当员工表里有数据时显示出部门表中的信息*/
SELECT * FROM dept WHERE EXISTS (SELECT deptNo FROM emp WHERE empNo = 7934); /*当员工表里有数据时显示出部门表中的信息*/

10、UNION /*多张表的行数的连接显示*/
SELECT deptNo,dname FROM dept UNION SELECT empNo,ename FROM emp; /*查询时将两张表的数据行连接起来显示出来，在连接时两张表要连接的列数一定要相同，负责要出错*/

11、INTERSECT /*返回sql语句中都出现的行*/
SELECT deptNo FROM dept INTERSECT SELECT empNo FROM emp; /*返回sql语句中都出现的行*/

12、多行数据的插入
INSERT INTO emp(empNo,ename) SELECT deptNo,dname FROM dept; /*插入多行数据，把部门表中查询到的列插入到员工表的对应字段中去*/

13、依赖已有的表来创建新表的方式
CREATE TABLE ttt AS (SELECT * FROM emp); /*按照员工表来创建一个新表*/

循环语句 (要想用DBMS_OUTPUT.PUT_LINE 输出打印的信息，
必须用SET 设置 SERVEROUTPUT.默认情况下SERVEROUTPUT是OFF的.
设置方式为 SET SERVEROUTPUT ON [SIZE 10000] [] 中的可选，
设置缓冲区中字符容量的多少)

一、循环语句
=====================================================================
DECLARE
x number;
BEGIN
x:=0;
LOOP
x:=x+1;
IF x>=3 THEN
EXIT;
END IF;
DBMS_OUTPUT.PUT_LINE('inner: x='||x);
END LOOP;
DBMS_OUTPUT.PUT_LINE('outer: x='||x);
END

DECLARE
x number;
BEGIN
x:=0;
LOOP
x:=x+1;
EXIT WHEN x>=3
DBMS_OUTPUT.PUT_LINE('inner: x='||x);
END LOOP;
DBMS_OUTPUT.PUT_LINE('outer: x='||x);
END;
上面两种写法类似，执行后达到同样的效果。
=====================================================================

2、FOR 循环：(在oracle 中FOR 循环中的变量是不能人为的操纵的，只能是加1或者是减1)
/*打印出1到5*/
FOR i IN 1..5 LOOP
DBMS_OUTPUT.PUT_LINE('i='||i);
END LOOP
DBMS_OUTPUT.PUT_LINE('END OF FOR LOOP');
END;

/*打印出5到1(用REVERSE 反向打印出1到5)*/
FOR i IN REVERSE 1..5 LOOP
DBMS_OUTPUT.PUT_LINE('i='||i);
END LOOP;
DBMS_OUTPUT.PUT_LINE('i='||i);
END;

3、goto语句
DECLARE
x number;
BEGIN
x:=0;
<<repeat_loop>>
x:=x+1;
DBMS_OUTPUT.PUT_LINE(x);
IF x<3 THEN
GOTO repeat_loop;
END IF;
END;
=====================================================================

二、复合变量：记录
什么是记录：记录是由几个相关构成的复合变量，常用于支持SELECT 语句的返回值。使用记录可以将一行数据看成一个单元进行处理，面不必将每列单独处理。

记录的声明：
TYPE type_name IS RECORD(
Variable_name datatype[],
Variable_name datatype[],
...

);
Real_name type_name;

例子1。
DECLARE
TYPE myrecord IS RECORD(
id varchar2(10),
name varchar2(10));
real_record myrecord;
BEGIN
SELECT empNo,eName INTO real_record FROM emp WHERE empNo='7369';
DBMS_OUTPUT.PUT_LINE(real_record.id||','||real_record.name);
END;

例子2、
DECLARE
TYPE myrecord IS RECORD(
id emp.empNo%TYPE, /*用%TYPE可以定义记录中的 id 这个字段与 emp 中的empNo 的类型和字段长度完全一致*/
name varchar2(10));
real_record myrecord;
BEGIN
SELECT empNo,eName INTO real_record FROM emp WHERE empNo='7369';
DBMS_OUTPUT.PUT_LINE(real_record.id||','||real_record.name);
END;

例子3、
DECLARE
myrec emp%ROWTYPE; /*(用emp%ROWTYPE 的方式定义，表示myrec这个记录与emp表中的字段的类型，字段长度以及字段的名字都一致)*/
BEGIN
SELECT * INTO myrec FROM emp WHERE empNo='7369';
DBMS_OUTPUT.PUT_LINE(myrec.empNo||','||myrec.ename);
END;

三、游标
1、什么是游标：游标是一种PL/SQL控制结构：可以对SQL语句的处理进行显式控制，便于对表的行数据逐条进行处理。
2、游标的分类：显式、隐式
3、游标的属性：
%FOUND ：游标里面有数据可取，就可以用它来判断，是布尔型
%ISOPEN：游标已经打开时，%ISOPEN 为true,否则为false
%NOTFOUND：游标中没有数据时，%NOTFOUND 为true,否则为false
%ROWCOUNT：用来返回迄今为止，已经从游标中取出的记录的数目。在游标没有打开或者没有执行FETCH提取数据之前，%ROWCOUNT的值为0，从这以后，我们每次取出一条记录它都自动加1，相当一个记数器，一直把所有的行取完，取到最大的行数。

例子1：
DECLARE
CURSOR mycur IS
SELECT * FROM dept;
myrecord dept%ROWTYPE;
BEGIN
OPEN mycur; /*打开结果集*/
FETCH mycur INTO myrecord;
WHILE mycur%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(myrecord.deptNo||','||myrecord.dName)
FETCH mycur INTO myrecord;
END LOOP;
CLOSE mycur;
END;

例子2、

DECLARE
CURSOR cur_para(id number) IS
SELECT dName FROM dept WHERE deptNo = id;
t_name dept.dname%TYPE;
BEGIN
OPEN cur_para(10);
LOOP
FETCH cur_para INTO t_name;
EXIT WHEN cur_para%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(t_name);
END LOOP;
CLOSE cur_para;
END;
/

例子3、
DECLARE
CURSOR cur_para(id number) IS
SELECT dname FROM dept WHERE deptNo = id;
BEGIN
DBMS_OUTPUT.PUT_LINE('*******RESULT SET IS :*********');
FOR cur IN cur_para(10) LOOP
DBMS_OUTPUT.PUT_LINE(cur.dname);
END LOOP;
END;
/

例子4、
DECLARE
t_name dept.dname%TYPE;
CURSOR cur(id number) IS
SELECT dname FROM dept WHERE deptNo = id;
BEGIN
IF cur%ISOPEN THEN
DBMS_OUTPUT.PUT_LINE('CURSOR IS OPEN');
ELSE
OPEN cur(20);
END IF;
FETCH cur INTO t_name;
CLOSE cur;
DBMS_OUTPUT.PUT_LINE(t_name);
END;
/

例子5、
DECLARE
t_name varchar2(10);
CURSOR mycur IS
SELECT dname FROM dept;
BEGIN
OPEN
mycur;
LOOP
FETCH mycur INTO t_name;
EXIT WHEN mycur%NOTFOUND OR mycur%NOTFOUND IS NULL;
DBMS_OUTPUT.PUT_LINE('ROWCOUNT OF mycur is : '||mycur%ROWCOUNT);
END LOOP;
CLOSE mycur;
END;
/

例子5、用游标修改数据

DECLARE
CURSOR cur IS
SELECT dname FROM dept FOR UPDATE; /*要用游标修改表中的数据必须写 FOR UPDATE*/
text varchar2(10);
BEGIN
OPEN cur;
FETCH cur INTO text;
WHILE cur%FOUND LOOP
UPDATE dept SET dname=dname||'_t' WHERE CURRENT OF cur;
FETCH cur INTO text;
END LOOP;
CLOSE cur;
END
/

例子6、隐式游标的使用

BEGIN
FOR cur IN(SELECT dname FROM dept) LOOP
DBMS_OUTPUT.PUT_LINE(cur.dname);
END LOOP;
END;
/

四、存储过程

CREATE OR REPLACE PROCEDURE myproc(id number) /*带参数的存储过程(参数默认为输入参数)*/
AS
tname varchar2(10);
BEGIN
SELECT dname INTO tname FROM dept WHERE deptNo = id;
DBMS_OUTPUT.PUT_LINE(tname);
END [myproc];
/

/*一种调用带参数的存储过程的方式*/
DECLARE
tid number;
BEGIN
tid:=10;
myproc(tid);
END;
/

/*参数为常量时，可用这种方式调用*/
BEGIN
myproc(10);
END;

/*参数为常量时，也可用这种方式调用*/
EXECUTE myproc(10);

/*带有输入和输出参数的存储过程*/
CREATE OR REPLACE PROCEDURE myproc2(id IN number,name OUT varchar2)
AS
BEGIN
SELECT dname INTO name FROM dept WHERE deptNo=id;
END;
/

DECLARE
tid number;
tname varchar2(10);
BEGIN
tid:=10;
myproc2(tid,tname);
END;
/