Oracle 学习
(一) 基本创建类
-
创建表空间
--创建表空间 create tablespace waterboss datafile 'c:\warerboss.dbf' size 100m autoextend on next 10m;
-
创建用户
--创建用户 create user wateruser identified by itcast default tablespace waterboss;
wateruser
为创建的用户名identified by
用于设置用户的密码default tablespaces
用于指定默认表空间名称 -
赋权
--给wateruser赋予DBA权限 grant dba to wateruser
给用户
wateruser
赋予DBA
权限后即可登录
(二) 表的创建,修改和删除
(1) 创建表
语法:
CREATE TABLE 表名称{
字段名 类型(长度) primary key;
字段名 类型(长度);
……
};
数据类型:
-
字符型:
(1)CHAR: 固定长度的字符类型,最多存储2000个字节。
(2)VARCHAR2:可变长度的字符类型,最多存储4000个字节。
(3)LONG:大文本类型。最多可以存储2个G
-
数值型:
NUMBER:数值类型
例如:NUMBER(5) 最大可以存的数为99999
NUMBER(5,2) 最大可以存的书为999.99
-
日期型
(1)DATE:日期时间型,精确到秒
(2)TIMESTAMP:精确到秒的小数点后9位
-
二进制型(大数据类型)
(1)CLOB:存储字符,最大可以存4个G
(2)BLOB:存储图像、声音、视频等二进制数据,最多可以存4个G
Example:
--创建业主表
CREATE TABLE T_OWNERS(
ID NUMBER PRIMARY KEY,
NAME VARCHAR2(20),
ADDRESSID NUMBER,
HOUSENUMBER VARCHAR2(30),
WATERMETER VARCHAR2(30),
ADDDATE DATE,
OWNERTYPEID NUMBER
)
(2) 修改表
-
增加字段
ALTER TABLE 表名称 ADD(列名 | 类型 [DEFAULT 默认值],列名 | 类型 [DEFAULT 默认值...] )
语句:
--追加字段 ALTER TABLE T_OWNERS ADD ( REMARK VARCHAR2(20), OUTDATE DATE )
-
修改字段
ALTER TABLE 表名称 MODIFY(列名 | 类型 [DEFAULT 默认值],列名 | 类型 [DEFAULT 默认值...] )
语句:
--修改字段 ALTER TABLE T_OWNERS MODIFY ( REMARK CHAR(20), OUTDATE TIMESTAMP )
-
修改字段名
ALTER TABLE 表名称 RENAME COLUMN 原列名 TO 新列名
语句:
ALTER TABLE T_OWNERS RENAME COLUMN OUTDATE TO EXIDATE
-
删除字段名
ALTER TABLE 表名称 DROP COLUMN 列名1,列名2;
语句:
--删除字段 ALTER TABLE T_OWNERS DROP COLUMN REMARK
(3) 删除表
DROP TABLE 表名称
(三) 数据增删改
(1) 插入数据
语法:
INSERT INTO 表名(列名1,列名2,...)]VALUES(值1,值2,...)
执行INSERT后一定要再执行commit提交事务
向业主表插入数据:
INSERT INTO T_OWNERS VALUES (1,'张三丰',1,'2-2','5678',sysdate,1;
sysdate
获取当前日期
(2) 修改日期
语法:
UPDATE T_OWNERS SET ADDDATE = ADDDATE-3 where id=1;
commit;
(3) 删除语句
语法1:
delete from T_OWNERS where id=1;
commit;
语法2:
TRUNCATE TABLE 表名称
语句:
truncate table T_OWNERTYPE
比较truncat与delete实现数据删除
- delete删除的数据可以rollback
- delete是你出可能产生碎片,并且不释放空间
- truncat是先摧毁表结构,再重构表结构
(四) Oracle体系结构操作
(1) 查询Oracle标准数据块的大小
col name format a30
col value format a20
select name,value from v$parameter where name = 'db_block_size';
(2) 设置运行环境变量
set pagesize 8
show pagesize
set newpage 1
show newpage
set linesize 150
show linesize
set pause on
set pause off
set time on
set time off
set numformat $999,999,999.00
(3) 常用sqlplus命令
-
help
help startup help index
-
describe–查询相应对象各个列的名称,是否为空以及类型等属性
还可以查询过程,函数和程序包等PL/SQL对象的规范
desc dba_tablespaces; select empno,ename 2 #desc scott.emp Name Null? Type ----------------------------------------------------- -------- ------------------------------------ EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2) 2 sal from scott.emp where job='SALESMAN'; EMPNO SAL ---------------- -------------------- 7499 ALLEN 7521 WARD 7654 MARTIN 7844 TURNER
-
spool–将查询结果输出到指定文件中
spool d:\emp.txt select empno,ename,job,sal from scott.emp where job='SALESMAN'; EMPNO ENAME JOB SAL ------ -------------------- ------------------ ------ 7499 ALLEN SALESMAN 1600 7521 WARD SALESMAN 1250 7654 MARTIN SALESMAN 1250 7844 TURNER SALESMAN 1500 spool off
-
define-- 用来定义一个用户变量并且可以分配给它一个char值
define vjob='SALESMAN' define vjob DEFINE VJOB = "SALESMAN" (CHAR)
-
show–用来显示系统变量的值或环境变量的值
show parameters db_block_size; NAME TYPE VALUE -------------------- ---------------------- ------------------------------ db_block_size integer 8192
-
edit --要编辑的磁盘文件名
edit [file_name[.ext]]
-
save --保存文件
save file_name
-
get --实现将一个脚本放进缓冲区
get file_name
-
start & @
(4) 格式化查询结果
-
format–修改数据输出格式
col sal format $999,999.00
-
heading–修改标题名称
col empno heading 员工编号 col enam heading 员工姓名 col sal heading 员工工资
-
NULL
col comm mull '空值' select empno,ename,comm from scott.emp where comm is null; EMPNO ENAME COMM ------ -------------------- ------ 7369 SMITH 7566 JONES 7698 BLAKE 7782 CLARK 7788 SCOTT 7839 KING 7876 ADAMS 7900 JAMES 7902 FORD 7934 MILLER
-
on/off
col sal format $999,999.00 col sal off select empno,ename,sal from scott.emp; EMPNO ENAME SAL ------ -------------------- ------ 7369 SMITH 800 7499 ALLEN 1600 7521 WARD 1250 7566 JONES 2975 7654 MARTIN 1250 7698 BLAKE 2850 7782 CLARK 2450 7788 SCOTT 3000 7839 KING 5000 7844 TURNER 1500 7876 ADAMS 1100 7900 JAMES 950 7902 FORD 3000 7934 MILLER 1300
-
wrapped/word_wrapped–实行折行
(5) 处理NULL表:
select ename,sal ,comm,sal+comm from emp;
ENAME SAL COMM SAL+COMM
---------------------------------------- ------ ------ --------
MARTIN 1250 1400 2650
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
FORD 3000
MILLER 1300
7 rows selected.
select ename,sal ,comm,sal+nvl(comm,0) from emp;
ENAME SAL COMM SAL+NVL(COMM,0)
---------------------------------------- ------ ------ ---------------
MARTIN 1250 1400 2650
BLAKE 2850 2850
CLARK 2450 2450
SCOTT 3000 3000
KING 5000 5000
FORD 3000 3000
MILLER 1300 1300
7 rows selected.
(6) distinct 去重
(7) 连接查询:
select ename ||''''||'s job is'||job from emp;
ENAME||''''||'SJOBIS'||JOB
----------------------------------------------------------------------------
MARTIN's job isSALESMAN
BLAKE's job isMANAGER
CLARK's job isMANAGER
SCOTT's job isANALYST
KING's job isPRESIDENT
FORD's job isANALYST
MILLER's job isCLERK
7 rows selected.
CONCAT连接:
select concat(concat(ename,'''s salary is '),sal) from emp;
CONCAT(CONCAT(ENAME,'''SSALARYIS'),SAL)
----------------------------------------------------------------------------------------------------
MARTIN's salary is 1250
BLAKE's salary is 2850
CLARK's salary is 2450
SCOTT's salary is 3000
KING's salary is 5000
FORD's salary is 3000
MILLER's salary is 1300
(8) 筛选查询:
select empno, ename,sal from emp where sal>1500;
EMPNO ENAME SAL
------ ---------------------------------------- ------
7698 BLAKE 2850
7782 CLARK 2450
7788 SCOTT 3000
7839 KING 5000
7902 FORD 3000
- 查询不等
select empno, ename,sal from emp where sal<>all(3000,950,800);
EMPNO ENAME SAL
------ ---------------------------------------- ------
7654 MARTIN 1250
7698 BLAKE 2850
7782 CLARK 2450
7839 KING 5000
7934 MILLER 1300
- 关键字查询:
①表内关键字:
select empno, ename,job from emp where ename like 'S%';
EMPNO ENAME JOB
------ ---------------------------------------- ------------------
7788 SCOTT ANALYST
select empno, ename,job from emp where job like 'S_L_S%';
EMPNO ENAME JOB
------ ---------------------------------------- ------------------
7654 MARTIN SALESMAN
select empno,ename,sal,hiredate
from emp
where hiredate like '%81';
EMPNO ENAME SAL HIREDATE
------ ---------------------------------------- ------ ------------
7654 MARTIN 1250 28-SEP-81
7698 BLAKE 2850 01-MAY-81
7782 CLARK 2450 09-JUN-81
7839 KING 5000 17-NOV-81
7902 FORD 3000 03-DEC-81
②IN关键字:
select empno,ename,job from emp where job in('PRESIDENT','MANNAGER','ANALIST');
EMPNO ENAME JOB
------ ---------------------------------------- ------------------
7839 KING PRESIDENT
select empno,ename,job from emp where job not in('PRESIDENT','MANNAGER','ANALIST');
EMPNO ENAME JOB
------ ---------------------------------------- ------------------
7654 MARTIN SALESMAN
7698 BLAKE MANAGER
7782 CLARK MANAGER
7788 SCOTT ANALYST
7902 FORD ANALYST
7934 MILLER CLERK
③BETWEEN关键字:
select empno,ename,sal from emp where sal between 2000 and 3000;
EMPNO ENAME SAL
------ ---------------------------------------- ------
7698 BLAKE 2850
7782 CLARK 2450
7788 SCOTT 3000
7902 FORD 3000
select empno,ename,sal from emp where sal not between 2000 and 3000;
EMPNO ENAME SAL
------ ---------------------------------------- ------
7654 MARTIN 1250
7839 KING 5000
7934 MILLER 1300
④ISNULL关键字:
select empno,ename,sal,comm from emp where comm is null;
EMPNO ENAME SAL COMM
------ ---------------------------------------- ------ ------
7698 BLAKE 2850
7782 CLARK 2450
7788 SCOTT 3000
7839 KING 5000
7902 FORD 3000
7934 MILLER 1300
(9) 逻辑筛选:
select empno,ename,sal from emp where sal >= 2000 and sal <= 3000;
select empno,ename,sal from emp where sal < 2000 or sal> 3000;
(10) 分组查询:
-
分组查询
select deptno,job from emp group by job,deptno order by deptno; DEPTNO JOB ------ ------------------ 10 CLERK 10 MANAGER 10 PRESIDENT 20 ANALYST 20 CLERK 20 MANAGER 30 CLERK 30 MANAGER 30 SALESMAN
-
分组查询结合函数:
select job,avg(sal),sum(sal),max(sal),count(job) from emp group by job; JOB AVG(SAL) SUM(SAL) MAX(SAL) COUNT(JOB) ------------------ -------- -------- -------- ---------- CLERK 1038 4150 1300 4 SALESMAN 1400 5600 1600 4 ANALYST 3000 6000 3000 2 MANAGER 2758 8275 2975 3 PRESIDENT 5000 5000 5000 1
-
多列分组:
select deptno,job,avg(sal),max(sal) from emp group by deptno,job; DEPTNO JOB AVG(SAL) MAX(SAL) ------ ------------------ -------- -------- 20 MANAGER 2975 2975 20 ANALYST 3000 3000 10 PRESIDENT 5000 5000 10 CLERK 1300 1300 30 SALESMAN 1400 1600 10 MANAGER 2450 2450 20 CLERK 950 1100 30 MANAGER 2850 2850 30 CLERK 950 950 9 rows selected.
-
使用order by改变分组排序结果:
select deptno,sum(sal) from emp group by deptno order by sum(sal) desc; DEPTNO SUM(SAL) ------ -------- 20 10875 30 9400 10 8750
-
having从句:
select deptno as 部门编号,avg(sal) as 平均工资 from emp group by deptno having avg(sal) > 2000; 部门编号 平均工资 -------- -------- 10 2917 20 2175
-
rollup&cube:
rollup执行数据统计:
在保留原结果的同时会生成横向小计和总计
select deptno as 部门编号,job as 岗位,avg(sal) as 平均工资 from emp group by rollup(deptno,job); 部门编号 岗位 平均工资 -------- ------------------ -------- 10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 10 2917 20 CLERK 950 20 ANALYST 3000 20 MANAGER 2975 20 2175 30 CLERK 950 30 MANAGER 2850 30 SALESMAN 1400 30 1567 2073 13 rows selected.
cube执行数据统计:
为了生成数据统计、横向小计、纵向小计以及总计统计,可以用CUBE
select deptno as 部门编号,job as 岗位,avg(sal) as 平均工资 from emp group by cube(deptno,job); 部门编号 岗位 平均工资 -------- ------------------ -------- 2073 CLERK 1038 ANALYST 3000 MANAGER 2758 SALESMAN 1400 PRESIDENT 5000 10 2917 10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 20 2175 20 CLERK 950 20 ANALYST 3000 20 MANAGER 2975 30 1567 部门编号 岗位 平均工资 -------- ------------------ -------- 30 CLERK 950 30 MANAGER 2850 30 SALESMAN 1400
(11) 排序查询:
order by xx (desc)
(12) 多表关联
①内连接:
select e.empno as 员工编号,e.ename as 员工名称,d.dname as 部门
from emp e inner join dept d
on e.deptno=d.deptno;
员工编号 员工名称 部门
-------- -------------------- ----------------------------
7782 CLARK ACCOUNTING
7839 KING ACCOUNTING
7934 MILLER ACCOUNTING
7566 JONES RESEARCH
7902 FORD RESEARCH
7876 ADAMS RESEARCH
7369 SMITH RESEARCH
7788 SCOTT RESEARCH
7521 WARD SALES
7844 TURNER SALES
7499 ALLEN SALES
7900 JAMES SALES
7698 BLAKE SALES
7654 MARTIN SALES
②外连接:
-
左外连接:
select e.empno,e.ename,e.job,d.deptno,d.dname from emp e left join dept d on e.deptno=d.deptno; EMPNO ENAME JOB DEPTNO DNAME ------ -------------------- ------------------ ------ ---------------------------- 7782 CLARK MANAGER 10 ACCOUNTING 7839 KING PRESIDENT 10 ACCOUNTING 7934 MILLER CLERK 10 ACCOUNTING 7369 SMITH CLERK 20 RESEARCH 7566 JONES MANAGER 20 RESEARCH 7788 SCOTT ANALYST 20 RESEARCH 7876 ADAMS CLERK 20 RESEARCH 7902 FORD ANALYST 20 RESEARCH 7499 ALLEN SALESMAN 30 SALES 7521 WARD SALESMAN 30 SALES 7654 MARTIN SALESMAN 30 SALES 7698 BLAKE MANAGER 30 SALES 7844 TURNER SALESMAN 30 SALES 7900 JAMES CLERK 30 SALES
-
右外连接:
select e.empno,e.ename,e.job,d.deptno,d.dname from emp e right join dept d on e.deptno=d.deptno; EMPNO ENAME JOB DEPTNO DNAME ------ -------------------- ------------------ ------ ---------------------------- 7782 CLARK MANAGER 10 ACCOUNTING 7839 KING PRESIDENT 10 ACCOUNTING 7934 MILLER CLERK 10 ACCOUNTING 7566 JONES MANAGER 20 RESEARCH 7902 FORD ANALYST 20 RESEARCH 7876 ADAMS CLERK 20 RESEARCH 7369 SMITH CLERK 20 RESEARCH 7788 SCOTT ANALYST 20 RESEARCH 7521 WARD SALESMAN 30 SALES 7844 TURNER SALESMAN 30 SALES 7499 ALLEN SALESMAN 30 SALES 7900 JAMES CLERK 30 SALES 7698 BLAKE MANAGER 30 SALES 7654 MARTIN SALESMAN 30 SALES 40 OPERATIONS
-
完全外连接:
select e.empno,e.ename,e.job,d.deptno,d.dname from emp e full join dept d on e.deptno=d.deptno; EMPNO ENAME JOB DEPTNO DNAME ------ -------------------- ------------------ ------ ---------------------------- 7369 SMITH CLERK 20 RESEARCH 7499 ALLEN SALESMAN 30 SALES 7521 WARD SALESMAN 30 SALES 7566 JONES MANAGER 20 RESEARCH 7654 MARTIN SALESMAN 30 SALES 7698 BLAKE MANAGER 30 SALES 7782 CLARK MANAGER 10 ACCOUNTING 7788 SCOTT ANALYST 20 RESEARCH 7839 KING PRESIDENT 10 ACCOUNTING 7844 TURNER SALESMAN 30 SALES 7876 ADAMS CLERK 20 RESEARCH 7900 JAMES CLERK 30 SALES 7902 FORD ANALYST 20 RESEARCH 7934 MILLER CLERK 10 ACCOUNTING 40 OPERATIONS
-
自然连接:
select empno,ename,job,dname from emp natural join dept where sal >2000; EMPNO ENAME JOB DNAME ------ -------------------- ------------------ ------------------------- 7782 CLARK MANAGER ACCOUNTING 7839 KING PRESIDENT ACCOUNTING 7902 FORD ANALYST RESEARCH 7788 SCOTT ANALYST RESEARCH 7566 JONES MANAGER RESEARCH 7698 BLAKE MANAGER SALES
-
自连接
-
交叉连接
(13) 常用系统函数
- 字符类函数:
- ASCII© & CHAR©
- CONCAT(s1,s2):连接s1和s2
- INTCAP(s):首字母大写
- INSTR(s1,s2[,i] [,j]):返回s2在s1中第j次出现时的位置
- LENGTH(s):返回字符串s的长度
- LOWER(s) & UPPER(s)
- LTRIM(s1,s2):删除字符串s1左边字符串s2
- RTRIM(s1,s2):删除字符串s1右边字符串s2
- TRIM(s1,s2):删除字符串s1左右两端字符串s2
- REPLACE(s1,s2[,s3]):使用s3替换出现在s1中所有s2字符串
- SUBSTR(s,i,[j]):从s中第i段截取长度为j的字符串
- 数字类函数:
- CEIL(n):返回大于或等于数值n的最小整数
- ROUND(n1,n2):返回舍入小数点右边n2位的n1的值
- POWER(n1,n2):返回n1的n2次方
- 日期和时间类函数:
- SYSDATE():返回系统当前日期
- ADD_MONTHS(d,i):返回日期d加上i个月的结果
- 转换类函数:
- TO_CHAR():转换到相应的格式,eg.
to_char(sysdate,'yyyy-mm-dd')
- TO_NUMBER(s[,format[lan]]):返回字符串s代表的数字 eg.
to_number('18f','xxx')
- TO_CHAR():转换到相应的格式,eg.
- 聚集函数:
- COUNT()
- AVG()
- MAX()
- MIN()
- SUM()
- VARIANCE():统计方差
- STDDEV():统计偏差
(14) 编写脚本插入数据:
新建loademp.sql:
accept no prompt'请输入员工编号:'
accept name prompt'请输入员工名称:'
accept title prompt'请输入员工岗位:'
accept d_no prompt'请输入员工部门编号:'
INSERT INTO emp (empno,ename,job,hiredate,deptno)
values(&no,'&name','&title',SYSDATE,&d_no);
(五) PL/SQL编程
eg.定义PL/SQL 编程块计算两个整数的差的商
--在服务端显示执行结果
set serveroutput on
--主要代码块
declare
a int:=100;
b int:=200;
c number;
begin
c:=(a+b)/(a-b);
dbms_output.put_line(c);
exception
when zero_divide then
dbms_output.put_line('除数不许为0!');
end;
/
eg.查询empno=7369的工资
set serveroutput on;
declare
Num_sal number;
Var_ename varchar2(20);
begin
select ename,sal into Var_ename,Num_sal from emp
where empno = 7369;
dbms_output.put_line(Var_ename||'工资是'||Num_sal);
end;
/
SMITH工资是800
(1) 基本数据类型
-
数值类型:
Num_Money NUMBER(9,2);
-
字符类型:
VARCHAR2(maxlength); CHAR(maxlength);
-
日期类型
-
布尔类型
(2) 特殊数据类型
-
%TYPE:声明一个与指定序列相同的数据类型
set serveroutput on declare var_ename emp.ename%type; --声明与ename列类型相同的变量 var_job emp.job%type; --声明与job列类型相同的变量 begin select ename,job into var_ename,var_job from emp where empno=7369; --检索数据,并保存在变量中 dbms_output.put_line(var_ename||'的职务是'||var_job); --输出变量的值 end; /
-
RECORD类型:记录
set serveroutput on declare type emp_type is record --声明record类型emp_type ( var_ename varchar2(20),--定义字段 var_job varchar2(20), var_sal number ); empinfo emp_type; --定义变量 begin select ename,job,sal into empinfo from emp where empno=7369;--检索数据 --输出雇员信息 dbms_output.put_line('雇员'||empinfo.var_ename||'的职务是'||empinfo.var_job||'、工资是'||empinfo.var_sal); end; /
-
%ROWTYPE:结合了%TYPE和RECORD的优点,定义一种特殊的变量
set serveroutput on declare rowVar_emp emp%rowtype; --定义能够存储emp表中一行数据的变量rowVar_emp begin select * into rowVar_emp from emp where empno=7369;--检索数据 /*输出雇员信息*/ dbms_output.put_line('雇员'||rowVar_emp.ename||'的编号是'||rowVar_emp.empno||',职务是'||rowVar_emp.job); end; /
(3) 流程控制语句
-
选择语句:
- IF…THEN
- IF…THEN…ELSE
- IF…THEN…ELSIF
- CASE
-
循环语句:
- LOOP
- WHILE
- FOR
- GOTO
-
游标cursor
-
显式游标:
-
声明游标:
declare cursor cur_emp(var_job in varchar2:='SALESMAN') is select empno,ename,sal from emp where job = var_job;
-
打开游标:
open cur_emp('MANAGER');
-
读取游标:
fetch cur_name into {variable};
-
关闭游标:
close cur_name;
set serveroutput on declare /*声明游标,检索雇员信息*/ cursor cur_emp (var_job in varchar2:='SALESMAN') is select empno,ename,sal from emp where job=var_job; type record_emp is record --声明一个记录类型(RECORD类型) ( /*定义当前记录的成员变量*/ var_empno emp.empno%type, var_ename emp.ename%type, var_sal emp.sal%type ); emp_row record_emp;--声明一个record_emp类型的变量 begin open cur_emp('MANAGER'); fetch cur_emp into emp_row;--先让指针指向结果集中的第一行 while cur_emp%found loop dbms_output.put_line(emp_row.var_ename||'的编号是'||emp_row.var_empno||',工资是'||emp_row.var_sal); fetch cur_emp into emp_row;--让指针指向结果集中的下一行 end loop; close cur_emp; end; /
-
-
隐式游标:
set serveroutput on begin update emp set sal=sal*(1+0.2) where job='SALESMAN'; if sql%notfound then dbms_output.put_line('没有雇员需要上调工资'); else dbms_output.put_line('有'||sql%rowcount||'个雇员工资上调20%'); end if; end; /
-
游标的属性:
-
%FOUND:是否找到游标
-
%NOTFOUND:是否没找到游标
-
%ROWCOUNT:游标抽取过的记录行数
-
%ISOPEN:游标是否打开
-
参数化游标:
set serveroutput on declare var_ename varchar2(50);--声明变量,用来存储雇员名称 var_job varchar2(50);--声明变量,用来存储雇员的职务 /*声明游标,检索指定员工编号的雇员信息*/ cursor cur_emp is select ename,job from emp where empno=7499; begin open cur_emp;--打开游标 fetch cur_emp into var_ename,var_job;--读取游标,并存储雇员名和职务 if cur_emp%found then --若检索到数据记录,则输出雇员信息 dbms_output.put_line('编号是7499的雇员名称为:'||var_ename||',职务是:'||var_job); else dbms_output.put_line('无数据记录');--提示无记录信息 end if; end; /
-
-
(4) 异常处理:
-
声明异常:
exception_name EXCEPTION;
-
为内部异常命名:
PRAGE EXCEPTION_INIT(exception_name,ORA_erroninumber);
-
异常定义:
declare exception_name EXCEPTION; begin if condition then raise exception_name; end if; exception when exception_name then statement; end;
-
使用SQLCODE 和 SQLERRM 函数定义提示信息
DBMS_OUTPUT.PUT_LINE('错误号:'||SQLCODE); DBMS_OUTPUT.PUT_LINE('错误号:'||SQLERRM);
set serveroutput on
declare
var_empno number;--定义变量,存储雇员编号
var_ename varchar2(50);--定义变量,存储雇员名称
begin
select empno,ename into var_empno,var_ename
from emp
where deptno=10;--检索部门编号为10的雇员信息
if sql%found then --若检索成功,则输出雇员信息
dbms_output.put_line('雇员编号:'||var_empno||';雇员名称'||var_ename);
end if;
exception --捕获异常
when too_many_rows then --若SELECT INTO语句的返回记录超过一行
dbms_output.put_line('返回记录超过一行');
when no_data_found then --若SELECT INTO语句的返回记录为0行
dbms_output.put_line('无数据记录');
end;
/