Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
Connected as system@ORCL
SQL> $imp system/test tables=(kc,xs_kc) file=c:\xskc.dmp
SQL> select * from kc;
KCH KCM KKXQ XS XF
--- ---------------- ---- --- --
001 001 1 11 1
001 001 1 11 1
SQL> select * from xs_kc;
XH KCH CJ XF
------ ------ --------------------------------------- ---
061101 101 80
061101 102 78
061101 206 76
061103 101 82
061103 102 82
061103 206 83
061104 101 90
061107 101 98
061107 102 80
9 rows selected
SQL> select xh,xm,zxf from xs;
select xh,xm,zxf from xs
ORA-00942: 表或视图不存在
SQL> $imp system/test tables=(xskc,xs_kc) file=c:\xskc.dmpConnected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
Connected as system@ORCL
SQL> $imp system/test tables=(xs,kc,xs_kc) file=c:\xskc.dmp
SQL> select * from xs;
XH XM ZYM XB CSSJ ZXF BZ
------ ------ ------ -- ----------- --- --------------------
061101 王林 计算机 男 1986/2/10 50
101112 李明 计算机 男 1986/1/30 36
001 张琼 计算机 45 三好学生
121112 王小二 计算机 男 1986/1/30 36
SQL> select * from kc;
KCH KCM KKXQ XS XF
--- ---------------- ---- --- --
001 001 1 11 1
001 001 1 11 1
SQL> select * from xs_kc;
XH KCH CJ XF
------ ------ --------------------------------------- ---
061101 101 80
061101 102 78
061101 206 76
061103 101 82
061103 102 82
061103 206 83
061104 101 90
061107 101 98
061107 102 80
9 rows selected
SQL> select xh,xm,zxf from xs;
XH XM ZXF
------ ------ ---
061101 王林 50
101112 李明 36
001 张琼 45
121112 王小二 36
SQL> select xh,xm,zxf as 总学分 from xs;
XH XM 总学分
------ ------ ------
061101 王林 50
101112 李明 36
001 张琼 45
121112 王小二 36
SQL> select xh,xm,zxf 总学分 from xs;
XH XM 总学分
------ ------ ------
061101 王林 50
101112 李明 36
001 张琼 45
121112 王小二 36
SQL>
SQL> select xh,
2 xm,
3 zxf,
4 (case
5 when zxf >= 50 then
6 'gao'
7 when zxf >= 40 then
8 'zhong'
9 else
10 '学分不够，需继续'
11 end) as 获得学分
12 from xs;
XH XM ZXF 获得学分
------ ------ --- ----------------
061101 王林 50 gao
101112 李明 36 学分不够，需继续
001 张琼 45 zhong
121112 王小二 36 学分不够，需继续
SQL>
SQL> create table t01 as
2 select xh,xm,zxf,(
3 case
4 when zxf>=50 then 'gao'
5 when zxf>=40 then 'zhong'
6 else '学分不够，需继续'
7 end
8 )as 获得的学分 from xs;
Table created
SQL> select * from t01;
XH XM ZXF 获得的学分
------ ------ --- ----------------
061101 王林 50 gao
101112 李明 36 学分不够，需继续
001 张琼 45 zhong
121112 王小二 36 学分不够，需继续
SQL> select Months_Between(sysdate,to_date('20151001','yyyymmdd')) from dual;
MONTHS_BETWEEN(SYSDATE,TO_DATE
------------------------------
97.3126355286738
SQL> select trunc(sysdate-to_date('20181001','yyyymmdd')) 天数 from dual;
天数
----------
1866
SQL> 图书馆借书
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
Connected as system@ORCL
SQL> $imp system/test tables=(xs,kc,xs_kc) file=c:\xskc.dmp
SQL> select * from xs;
XH XM ZYM XB CSSJ ZXF BZ
------ ------ ------ -- ----------- --- --------------------
061101 王林 计算机 男 1986/2/10 50
101112 李明 计算机 男 1986/1/30 36
001 张琼 计算机 45 三好学生
121112 王小二 计算机 男 1986/1/30 36
SQL> select * from kc;
KCH KCM KKXQ XS XF
--- ---------------- ---- --- --
001 001 1 11 1
001 001 1 11 1
SQL> select * from xs_kc;
XH KCH CJ XF
------ ------ --------------------------------------- ---
061101 101 80
061101 102 78
061101 206 76
061103 101 82
061103 102 82
061103 206 83
061104 101 90
061107 101 98
061107 102 80
9 rows selected
SQL> select xh,xm,zxf from xs;
XH XM ZXF
------ ------ ---
061101 王林 50
101112 李明 36
001 张琼 45
121112 王小二 36
SQL> select xh,xm,zxf as 总学分 from xs;
XH XM 总学分
------ ------ ------
061101 王林 50
101112 李明 36
001 张琼 45
121112 王小二 36
SQL> select xh,xm,zxf 总学分 from xs;
XH XM 总学分
------ ------ ------
061101 王林 50
101112 李明 36
001 张琼 45
121112 王小二 36
SQL>
SQL> select xh,
2 xm,
3 zxf,
4 (case
5 when zxf >= 50 then
6 'gao'
7 when zxf >= 40 then
8 'zhong'
9 else
10 '学分不够，需继续'
11 end) as 获得学分
12 from xs;
XH XM ZXF 获得学分
------ ------ --- ----------------
061101 王林 50 gao
101112 李明 36 学分不够，需继续
001 张琼 45 zhong
121112 王小二 36 学分不够，需继续
SQL>
SQL> create table t01 as
2 select xh,xm,zxf,(
3 case
4 when zxf>=50 then 'gao'
5 when zxf>=40 then 'zhong'
6 else '学分不够，需继续'
7 end
8 )as 获得的学分 from xs;
Table created
SQL> select * from t01;
XH XM ZXF 获得的学分
------ ------ --- ----------------
061101 王林 50 gao
101112 李明 36 学分不够，需继续
001 张琼 45 zhong
121112 王小二 36 学分不够，需继续
SQL> select Months_Between(sysdate,to_date('20151001','yyyymmdd')) from dual;
MONTHS_BETWEEN(SYSDATE,TO_DATE
------------------------------
97.3126355286738
SQL> select trunc(sysdate-to_date('20181001','yyyymmdd')) 天数 from dual;
天数
----------
1866
SQL>
SQL> select empno,
2 ename,
3 job,
4 hiredate,
5 (case
6 when trunc(sysdate - hiredate) > 360 then
7 '过期'
8 when hiredate is null then
9 '没借书'
10 else
11 '没过期'
12 end) as 是否过期
13 from scott.emp;
EMPNO ENAME JOB HIREDATE 是否过期
----- ---------- --------- ----------- --------
7369 SMITH CLERK 1980/12/17 过期
7499 ALLEN SALESMAN 1981/2/20 过期
7521 WARD SALESMAN 1981/2/22 过期
7566 JONES MANAGER 1981/4/2 过期
7654 MARTIN SALESMAN 1981/9/28 过期
7698 BLAKE MANAGER 1981/5/1 过期
7782 CLARK MANAGER 1981/6/9 过期
7788 SCOTT ANALYST 1987/4/19 过期
7839 KING PRESIDENT 1981/11/17 过期
7844 TURNER SALESMAN 1981/9/8 过期
7876 ADAMS CLERK 1987/5/23 过期
7900 JAMES CLERK 1981/12/3 过期
7902 FORD ANALYST 1981/12/3 过期
7934 MILLER CLERK 1982/1/23 过期
14 rows selected
SQL>
SQL> select empno,
2 ename,
3 job,
4 hiredate,
5 (case
6 when trunc(sysdate - hiredate) > 360 then
7 '过期'
8 when hiredate is null then
9 '没借书'
10 else
11 '没过期'
12 end) as 是否过期(case
13 when trunc(sysdate - hiredate) - 360 > 0 then
14 0.1 * trunc(sysdate - hiredate)
15 else
16 0
17 end) 代缴金额
18 from scott.emp;
select empno,
ename,
job,
hiredate,
(case
when trunc(sysdate - hiredate) > 360 then
'过期'
when hiredate is null then
'没借书'
else
'没过期'
end) as 是否过期(case
when trunc(sysdate - hiredate) - 360 > 0 then
0.1 * trunc(sysdate - hiredate)
else
0
end) 代缴金额
from scott.emp
ORA-00923: 未找到要求的 FROM 关键字
SQL>
SQL> select empno,
2 ename,
3 job,
4 hiredate,
5 (case
6 when trunc(sysdate - hiredate) > 360 then
7 '过期'
8 when hiredate is null then
9 '没借书'
10 else
11 '没过期'
12 end) as 是否过期,
13 (case
14 when trunc(sysdate - hiredate) - 360 > 0 then
15 1 * trunc(sysdate - hiredate) - 360
16 else
17 0
18 end) 代缴金额
19 from scott.emp;
EMPNO ENAME JOB HIREDATE 是否过期 代缴金额
----- ---------- --------- ----------- -------- ----------
7369 SMITH CLERK 1980/12/17 过期 15308
7499 ALLEN SALESMAN 1981/2/20 过期 15243
7521 WARD SALESMAN 1981/2/22 过期 15241
7566 JONES MANAGER 1981/4/2 过期 15202
7654 MARTIN SALESMAN 1981/9/28 过期 15023
7698 BLAKE MANAGER 1981/5/1 过期 15173
7782 CLARK MANAGER 1981/6/9 过期 15134
7788 SCOTT ANALYST 1987/4/19 过期 12994
7839 KING PRESIDENT 1981/11/17 过期 14973
7844 TURNER SALESMAN 1981/9/8 过期 15043
7876 ADAMS CLERK 1987/5/23 过期 12960
7900 JAMES CLERK 1981/12/3 过期 14957
7902 FORD ANALYST 1981/12/3 过期 14957
7934 MILLER CLERK 1982/1/23 过期 14906
14 rows selected
SQL>
SQL> select empno,
2 ename,
3 job,
4 hiredate,
5 (case
6 when trunc(sysdate - hiredate) > 360 then
7 '过期'
8 when hiredate is null then
9 '没借书'
10 else
11 '没过期'
12 end) as 是否过期,
13 (case
14 when trunc(sysdate - hiredate) - 360 > 0 then
15 0.1 * trunc(sysdate - hiredate)
16 else
17 0
18 end) 代缴金额
19 from scott.emp;
EMPNO ENAME JOB HIREDATE 是否过期 代缴金额
----- ---------- --------- ----------- -------- ----------
7369 SMITH CLERK 1980/12/17 过期 1566.8
7499 ALLEN SALESMAN 1981/2/20 过期 1560.3
7521 WARD SALESMAN 1981/2/22 过期 1560.1
7566 JONES MANAGER 1981/4/2 过期 1556.2
7654 MARTIN SALESMAN 1981/9/28 过期 1538.3
7698 BLAKE MANAGER 1981/5/1 过期 1553.3
7782 CLARK MANAGER 1981/6/9 过期 1549.4
7788 SCOTT ANALYST 1987/4/19 过期 1335.4
7839 KING PRESIDENT 1981/11/17 过期 1533.3
7844 TURNER SALESMAN 1981/9/8 过期 1540.3
7876 ADAMS CLERK 1987/5/23 过期 1332
7900 JAMES CLERK 1981/12/3 过期 1531.7
7902 FORD ANALYST 1981/12/3 过期 1531.7
7934 MILLER CLERK 1982/1/23 过期 1526.6
14 rows selected
SQL>
SQL> declare
2 cursor c_1 is
3 select xh, xm from xs; -- 游标声明
4 v_xm xs.xh%type;
5 v_xm xs.xm%type;
6 begin
7 open c_1; -- 打开游标
8 fetch c_1
9 into v_xh, v_xm;
10 dbms_output.put_line(v_xh || '' || v_xm);
11 close c_1; --关闭游标
12 end;
13 、
14 、
15 /
declare
cursor c_1 is
select xh, xm from xs; -- 游标声明
v_xm xs.xh%type;
v_xm xs.xm%type;
begin
open c_1; -- 打开游标
fetch c_1
into v_xh, v_xm;
dbms_output.put_line(v_xh || '' || v_xm);
close c_1; --关闭游标
end;
、
、
ORA-06550: 第 14 行, 第 2 列:
PLS-00103: 出现符号 "、"
SQL>
SQL> declare
2 cursor c_1 is
3 select xh, xm from xs; -- 游标声明
4 v_xm xs.xh%type;
5 v_xm xs.xm%type;
6 begin
7 open c_1; -- 打开游标
8 fetch c_1
9 into v_xh, v_xm;
10 dbms_output.put_line(v_xh || '' || v_xm);
11 close c_1; --关闭游标
12 end;
13 /
declare
cursor c_1 is
select xh, xm from xs; -- 游标声明
v_xm xs.xh%type;
v_xm xs.xm%type;
begin
open c_1; -- 打开游标
fetch c_1
into v_xh, v_xm;
dbms_output.put_line(v_xh || '' || v_xm);
close c_1; --关闭游标
end;
ORA-06550: 第 10 行, 第 10 列:
PLS-00201: 必须声明标识符 'V_XH'
ORA-06550: 第 9 行, 第 3 列:
PL/SQL: SQL Statement ignored
ORA-06550: 第 11 行, 第 24 列:
PLS-00201: 必须声明标识符 'V_XH'
ORA-06550: 第 11 行, 第 3 列:
PL/SQL: Statement ignored
SQL>
SQL> declare
2 cursor c_1 is
3 select xh, xm from xs; -- 游标声明
4 v_xh xs.xh%type;
5 v_xm xs.xm%type;
6 begin
7 open c_1; -- 打开游标
8 fetch c_1
9 into v_xh, v_xm;
10 dbms_output.put_line(v_xh || '' || v_xm);
11 close c_1; --关闭游标
12 end;
13 /
PL/SQL procedure successfully completed
SQL> declare
cursor c_1 is
select xh, xm from xs; -- 游标声明
v_xh xs.xh%type;
v_xm xs.xm%type;
begin
open c_1; -- 打开游标
fetch c_1
into v_xh, v_xm;
dbms_output.put_line(v_xh || '' || v_xm);
close c_1; --关闭游标
end;
2023-11-10实验
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
Connected as system@ORCL
SQL>
SQL> declare
2 grade varchar2(4):='良好';
3 begin
4 case grade
5 when '优秀' then dbms_output.put_line('大于等于90');
6 when '良好' then dbms_output.put_line('大于等于80，小于90');
7 when '及格' then dbms_output.put_line('大于等于60，小于80');
8 else dbms_output.put_line('不及格');
9 end case;
10 end;
11 /
PL/SQL procedure successfully completed
SQL> set serveroutput on;
SQL>
SQL> declare
2 grade varchar2(4):='良好';
3 begin
4 case grade
5 when '优秀' then dbms_output.put_line('大于等于90');
6 when '良好' then dbms_output.put_line('大于等于80，小于90');
7 when '及格' then dbms_output.put_line('大于等于60，小于80');
8 else dbms_output.put_line('不及格');
9 end case;
10 end;
11 /
大于等于80，小于90
PL/SQL procedure successfully completed
SQL>
SQL> declare
2 v_deptno emp.deptno%type;
3 v_increment Number(4);
4 v_empno emp.empno%type;
5 begin
6 v_empno:=&x;
7 select deptno into v_deptno from emp where
8 empno = v_empno;
9 case v_deptno
10 WHEN 10 THEN v_increment:=100;
11 WHEN 20 THEN v_increment:=150;
12 WHEN 30 THEN v_increment:=200;
13 ELSE v_increment:=300;
14 end case;
15 update emp set sal=sal+v_increment where empno=v_empno;
16 end;
17 /
declare
v_deptno emp.deptno%type;
v_increment Number(4);
v_empno emp.empno%type;
begin
v_empno:=1;
select deptno into v_deptno from emp where
empno = v_empno;
case v_deptno
WHEN 10 THEN v_increment:=100;
WHEN 20 THEN v_increment:=150;
WHEN 30 THEN v_increment:=200;
ELSE v_increment:=300;
end case;
update emp set sal=sal+v_increment where empno=v_empno;
end;
ORA-06550: 第 3 行, 第 13 列:
PLS-00201: 必须声明标识符 'EMP.DEPTNO'
ORA-06550: 第 3 行, 第 13 列:
PL/SQL: Item ignored
ORA-06550: 第 5 行, 第 12 列:
PLS-00201: 必须声明标识符 'EMP.EMPNO'
ORA-06550: 第 5 行, 第 12 列:
PL/SQL: Item ignored
ORA-06550: 第 7 行, 第 3 列:
PLS-00320: 此表达式的类型声明不完整或格式不正确
ORA-06550: 第 7 行, 第 3 列:
PL/SQL: Statement ignored
ORA-06550: 第 8 行, 第 36 列:
PL/SQL: ORA-00942: 表或视图不存在
ORA-06550: 第 8 行, 第 3 列:
PL/SQL: SQL Statement ignored
ORA-06550: 第 10 行, 第 8 列:
PLS-00320: 此表达式的类型声明不完整或格式不正确
ORA-06550: 第 10 行, 第 3 列:
PL/SQL: Statement ignored
ORA-06550: 第 16 行, 第 10 列:
PL/SQL: ORA-00942: 表或视图不存在
ORA-06550: 第 16 行, 第 3 列:
PL/SQL: SQL Statement ignored
SQL> select * from emp;
select * from emp
ORA-00942: 表或视图不存在
SQL> select tables;
select tables
ORA-00923: 未找到要求的 FROM 关键字
SQL> select * from tables;
select * from tables
ORA-00942: 表或视图不存在
SQL> create table emp();
create table emp()
ORA-00904: : 标识符无效
SQL>
SQL>
SQL> create table emp(
2 empno int primary key ,
3 deptno int
4 );
Table created
SQL>
SQL> declare
2 v_deptno emp.deptno%type;
3 v_increment Number(4);
4 v_empno emp.empno%type;
5 begin
6 v_empno:=&x;
7 select deptno into v_deptno from emp where
8 empno = v_empno;
9 case v_deptno
10 WHEN 10 THEN v_increment:=100;
11 WHEN 20 THEN v_increment:=150;
12 WHEN 30 THEN v_increment:=200;
13 ELSE v_increment:=300;
14 end case;
15 update emp set sal=sal+v_increment where empno=v_empno;
16 end;
17 /
declare
v_deptno emp.deptno%type;
v_increment Number(4);
v_empno emp.empno%type;
begin
v_empno:=1;
select deptno into v_deptno from emp where
empno = v_empno;
case v_deptno
WHEN 10 THEN v_increment:=100;
WHEN 20 THEN v_increment:=150;
WHEN 30 THEN v_increment:=200;
ELSE v_increment:=300;
end case;
update emp set sal=sal+v_increment where empno=v_empno;
end;
ORA-06550: 第 16 行, 第 22 列:
PL/SQL: ORA-00904: "SAL": 标识符无效
ORA-06550: 第 16 行, 第 3 列:
PL/SQL: SQL Statement ignored
SQL> drop table emp;
SQL>
Table dropped
SQL>
SQL> create table emp(
2 empno int primary key ,
3 sal number(4),
4 deptno int
5 );
Table created
SQL>
SQL> declare
2 v_deptno emp.deptno%type;
3 v_increment Number(4);
4 v_empno emp.empno%type;
5 begin
6 v_empno:=&x;
7 select deptno into v_deptno from emp where
8 empno = v_empno;
9 case v_deptno
10 WHEN 10 THEN v_increment:=100;
11 WHEN 20 THEN v_increment:=150;
12 WHEN 30 THEN v_increment:=200;
13 ELSE v_increment:=300;
14 end case;
15 update emp set sal=sal+v_increment where empno=v_empno;
16 end;
17 /
declare
v_deptno emp.deptno%type;
v_increment Number(4);
v_empno emp.empno%type;
begin
v_empno:=1;
select deptno into v_deptno from emp where
empno = v_empno;
case v_deptno
WHEN 10 THEN v_increment:=100;
WHEN 20 THEN v_increment:=150;
WHEN 30 THEN v_increment:=200;
ELSE v_increment:=300;
end case;
update emp set sal=sal+v_increment where empno=v_empno;
end;
ORA-01403: 未找到任何数据
ORA-06512: 在 line 8
SQL> insert into emp values(1,100,1);
1 row inserted
SQL>
SQL> declare
2 v_deptno emp.deptno%type;
3 v_increment Number(4);
4 v_empno emp.empno%type;
5 begin
6 v_empno:=&x;
7 select deptno into v_deptno from emp where
8 empno = v_empno;
9 case v_deptno
10 WHEN 10 THEN v_increment:=100;
11 WHEN 20 THEN v_increment:=150;
12 WHEN 30 THEN v_increment:=200;
13 ELSE v_increment:=300;
14 end case;
15 update emp set sal=sal+v_increment where empno=v_empno;
16 end;
17 /
PL/SQL procedure successfully completed
SQL> select * from emp;
EMPNO SAL DEPTNO
--------------------------------------- ----- ---------------------------------------
1 400 1
SQL>
SQL> declare
2 v_deptno emp.deptno%type;
3 v_increment Number(4);
4 v_empno emp.empno%type;
5 begin
6 v_empno:=&x;
7 select deptno into v_deptno from emp where
8 empno = v_empno;
9 case v_deptno
10 WHEN 10 THEN v_increment:=100;
11 WHEN 20 THEN v_increment:=150;
12 WHEN 30 THEN v_increment:=200;
13 ELSE v_increment:=300;
14 end case;
15 update emp set sal=sal+v_increment where empno=v_empno;
16 if SQL%FOUND then
17 dbms_output.put_line('更改成功');
18 select sal into v_sal from scott.emp where empno='7788';
19 dbms_output.put_line(v_sal);
20 end if;
21 end;
22 /
declare
v_deptno emp.deptno%type;
v_increment Number(4);
v_empno emp.empno%type;
begin
v_empno:=1;
select deptno into v_deptno from emp where
empno = v_empno;
case v_deptno
WHEN 10 THEN v_increment:=100;
WHEN 20 THEN v_increment:=150;
WHEN 30 THEN v_increment:=200;
ELSE v_increment:=300;
end case;
update emp set sal=sal+v_increment where empno=v_empno;
if SQL%FOUND then
dbms_output.put_line('更改成功');
select sal into v_sal from scott.emp where empno='7788';
dbms_output.put_line(v_sal);
end if;
end;
ORA-06550: 第 19 行, 第 22 列:
PLS-00201: 必须声明标识符 'V_SAL'
ORA-06550: 第 19 行, 第 28 列:
PL/SQL: ORA-00904: : 标识符无效
ORA-06550: 第 19 行, 第 6 列:
PL/SQL: SQL Statement ignored
ORA-06550: 第 20 行, 第 27 列:
PLS-00201: 必须声明标识符 'V_SAL'
ORA-06550: 第 20 行, 第 6 列:
PL/SQL: Statement ignored
SQL> select * from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980/12/17 800.00 20
7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981/4/2 2975.00 20
7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30
7782 CLARK MANAGER 7839 1981/6/9 2450.00 10
7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20
7839 KING PRESIDENT 1981/11/17 5000.00 10
7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987/5/23 1100.00 20
7900 JAMES CLERK 7698 1981/12/3 950.00 30
7902 FORD ANALYST 7566 1981/12/3 3000.00 20
7934 MILLER CLERK 7782 1982/1/23 1300.00 10
14 rows selected
SQL>
SQL> declare
2 v_deptno scott.emp.deptno%type;
3 v_increment Number(4);
4 v_empno scott.emp.empno%type;
5 v_sal scott.emp.sal%type;
6 begin
7 v_empno:=&x;
8 select deptno into v_deptno from scott.emp where
9 empno = v_empno;
10 case v_deptno
11 WHEN 10 THEN v_increment:=100;
12 WHEN 20 THEN v_increment:=150;
13 WHEN 30 THEN v_increment:=200;
14 ELSE v_increment:=300;
15 end case;
16 update scott.emp set sal=sal+v_increment where empno=v_empno;
17 if SQL%FOUND then
18 dbms_output.put_line('更改成功');
19 select sal into v_sal from scott.emp where empno='7788';
20 dbms_output.put_line(v_sal);
21 end if;
22 end;
23 /
declare
v_deptno scott.emp.deptno%type;
v_increment Number(4);
v_empno scott.emp.empno%type;
v_sal scott.emp.sal%type;
begin
v_empno:=1;
select deptno into v_deptno from scott.emp where
empno = v_empno;
case v_deptno
WHEN 10 THEN v_increment:=100;
WHEN 20 THEN v_increment:=150;
WHEN 30 THEN v_increment:=200;
ELSE v_increment:=300;
end case;
update scott.emp set sal=sal+v_increment where empno=v_empno;
if SQL%FOUND then
dbms_output.put_line('更改成功');
select sal into v_sal from scott.emp where empno='7788';
dbms_output.put_line(v_sal);
end if;
end;
ORA-01403: 未找到任何数据
ORA-06512: 在 line 9
SQL>
SQL> declare
2 v_deptno scott.emp.deptno%type;
3 v_increment Number(4);
4 v_empno scott.emp.empno%type;
5 v_sal scott.emp.sal%type;
6 begin
7 v_empno:=&x;
8 select deptno into v_deptno from scott.emp where
9 empno = v_empno;
10 case v_deptno
11 WHEN 10 THEN v_increment:=100;
12 WHEN 20 THEN v_increment:=150;
13 WHEN 30 THEN v_increment:=200;
14 ELSE v_increment:=300;
15 end case;
16 update scott.emp set sal=sal+v_increment where empno=v_empno;
17 if SQL%FOUND then
18 dbms_output.put_line('更改成功');
19 select sal into v_sal from scott.emp where empno='7788';
20 dbms_output.put_line(v_sal);
21 end if;
22 end;
23 /
declare
v_deptno scott.emp.deptno%type;
v_increment Number(4);
v_empno scott.emp.empno%type;
v_sal scott.emp.sal%type;
begin
v_empno:=7639;
select deptno into v_deptno from scott.emp where
empno = v_empno;
case v_deptno
WHEN 10 THEN v_increment:=100;
WHEN 20 THEN v_increment:=150;
WHEN 30 THEN v_increment:=200;
ELSE v_increment:=300;
end case;
update scott.emp set sal=sal+v_increment where empno=v_empno;
if SQL%FOUND then
dbms_output.put_line('更改成功');
select sal into v_sal from scott.emp where empno='7788';
dbms_output.put_line(v_sal);
end if;
end;
ORA-01403: 未找到任何数据
ORA-06512: 在 line 9
SQL>
SQL> declare
2 v_deptno scott.emp.deptno%type;
3 v_increment Number(4);
4 v_empno scott.emp.empno%type;
5 v_sal scott.emp.sal%type;
6 begin
7 v_empno:=&x;
8 select deptno into v_deptno from scott.emp where
9 empno = v_empno;
10 case v_deptno
11 WHEN 10 THEN v_increment:=100;
12 WHEN 20 THEN v_increment:=150;
13 WHEN 30 THEN v_increment:=200;
14 ELSE v_increment:=300;
15 end case;
16 update scott.emp set sal=sal+v_increment where empno=v_empno;
17 if SQL%FOUND then
18 dbms_output.put_line('更改成功');
19 select sal into v_sal from scott.emp where empno='7788';
20 dbms_output.put_line(v_sal);
21 end if;
22 end;
23 /
更改成功
3000
PL/SQL procedure successfully completed
SQL>
SQL> declare
2 score int:=91;
3 begin
4 case
5 when score>=90 then dbms_output.put_line('优秀');
6 when score>=80 then dbms_output.put_line('良好');
7 when score>=60 then dbms_output.put_line('及格');
8 else dbms_output.put_line('不及格');
9 end case;
10 end;
11 /
优秀
PL/SQL procedure successfully completed
SQL> select * from xs;
select * from xs
ORA-00942: 表或视图不存在
SQL> select * from scott.xs;
select * from scott.xs
ORA-00942: 表或视图不存在
SQL> create table xs();
create table xs()
ORA-00904: : 标识符无效
SQL>
SQL> create table xs(
2 xh number(4) primary key,
3 xm varchar2(25),
4 zxf int
5 );
Table created
SQL> insert into xs values(1111,'毛晨阳',300);
1 row inserted
SQL>
SQL> select xh,xm,zxf,
2 (
3 case
4 when zxf>50 then '高'
5 when zxf>=40 then '中'
6 else '学分不够'
7 end;
select xh,xm,zxf,
(
case
when zxf>50 then '高'
when zxf>=40 then '中'
else '学分不够'
end
ORA-00907: 缺失右括号
SQL> )as
2 获得学分情况 from xs;
)as
获得学分情况 from xs
ORA-00900: 无效 SQL 语句
SQL>
SQL> select xh,xm,zxf,
2 (
3 case
4 when zxf>50 then '高'
5 when zxf>=40 then '中'
6 else '学分不够'
7 end case;
select xh,xm,zxf,
(
case
when zxf>50 then '高'
when zxf>=40 then '中'
else '学分不够'
end case
ORA-00907: 缺失右括号
SQL> )as
2 获得学分情况 from xs;
)as
获得学分情况 from xs
ORA-00900: 无效 SQL 语句
SQL>
SQL> select xh,xm,zxf,
2 (case
3 when zxf>50 then 'gao'
4 when zxf>=40 then 'zhong'
5 else '学分不够，需继续'
6 end) as
7 获得学分情况 from xs;
XH XM ZXF 获得学分情况
----- ------------------------- --------------------------------------- ----------------
1111 毛晨阳 300 gao
SQL>
SQL> select xh,xm,zxf,
2 (
3 case
4 when zxf>50 then '高'
5 when zxf>=40 then '中'
6 else '学分不够'
7 end case)as
8 获得学分情况 from xs;
select xh,xm,zxf,
(
case
when zxf>50 then '高'
when zxf>=40 then '中'
else '学分不够'
end case)as
获得学分情况 from xs
ORA-00907: 缺失右括号
SQL>
SQL> select xh,xm,zxf,
2 (case
3 when zxf>50 then '高'
4 when zxf>=40 then '中'
5 else '学分不够'
6 end) as
7 获得学分情况 from xs;
XH XM ZXF 获得学分情况
----- ------------------------- --------------------------------------- ------------
1111 毛晨阳 300 高
SQL> select * from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980/12/17 950.00 20
7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981/4/2 2975.00 20
7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30
7782 CLARK MANAGER 7839 1981/6/9 2450.00 10
7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20
7839 KING PRESIDENT 1981/11/17 5000.00 10
7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987/5/23 1100.00 20
7900 JAMES CLERK 7698 1981/12/3 950.00 30
7902 FORD ANALYST 7566 1981/12/3 3000.00 20
7934 MILLER CLERK 7782 1982/1/23 1300.00 10
14 rows selected
SQL>
SQL> declare
2 V_HIREDATE scott.emp%type;
3 v_empno emp.empno%type;
4 v_decrement Number(4);
5 begin
6 v_empno:=&x;
7 select HIREDATE into V_HIREDATE from scott.emp where
8 empno=v_empno;
9 case
10 when sysdate-V_HIREDATE>300 then v_decrement:=(sysdate-V_HIREDATE-300)*0.1;
11 end case;
12 update scott.emp set sal:=sal-v_decrement where empno=v_empno;
13 end;
14 /
declare
V_HIREDATE scott.emp%type;
v_empno emp.empno%type;
v_decrement Number(4);
begin
v_empno:=7369;
select HIREDATE into V_HIREDATE from scott.emp where
empno=v_empno;
case
when sysdate-V_HIREDATE>300 then v_decrement:=(sysdate-V_HIREDATE-300)*0.1;
end case;
update scott.emp set sal:=sal-v_decrement where empno=v_empno;
end;
ORA-06550: 第 13 行, 第 27 列:
PL/SQL: ORA-00927: 缺失等号
ORA-06550: 第 13 行, 第 3 列:
PL/SQL: SQL Statement ignored
SQL>
SQL> declare
2 V_HIREDATE scott.emp%type;
3 v_empno emp.empno%type;
4 v_decrement Number(4);
5 begin
6 v_empno:=&x;
7 select HIREDATE into V_HIREDATE from scott.emp where
8 empno=v_empno;
9 case
10 when sysdate-V_HIREDATE>300 then v_decrement:=(sysdate-V_HIREDATE-300)*0.1;
11 end case;
12 update scott.emp set sal=sal-v_decrement where empno=v_empno;
13 end;
14 /
declare
V_HIREDATE scott.emp%type;
v_empno emp.empno%type;
v_decrement Number(4);
begin
v_empno:=7369;
select HIREDATE into V_HIREDATE from scott.emp where
empno=v_empno;
case
when sysdate-V_HIREDATE>300 then v_decrement:=(sysdate-V_HIREDATE-300)*0.1;
end case;
update scott.emp set sal=sal-v_decrement where empno=v_empno;
end;
ORA-06550: 第 3 行, 第 15 列:
PLS-00206: %TYPE 必须用于变量, 列, 字段或属性, 而不是 'SCOTT.EMP'
ORA-06550: 第 3 行, 第 15 列:
PL/SQL: Item ignored
ORA-06550: 第 8 行, 第 24 列:
PLS-00320: 此表达式的类型声明不完整或格式不正确
ORA-06550: 第 8 行, 第 35 列:
PL/SQL: ORA-00904: : 标识符无效
ORA-06550: 第 8 行, 第 3 列:
PL/SQL: SQL Statement ignored
ORA-06550: 第 11 行, 第 18 列:
PLS-00320: 此表达式的类型声明不完整或格式不正确
ORA-06550: 第 10 行, 第 3 列:
PL/SQL: Statement ignored
SQL>
SQL> declare
2 V_HIREDATE scott.emp.hiredate%type;
3 v_empno scott.emp.empno%type;
4 v_decrement Number(4);
5 begin
6 v_empno:=&x;
7 select HIREDATE into V_HIREDATE from scott.emp where
8 empno=v_empno;
9 case
10 when sysdate-V_HIREDATE>300 then v_decrement:=(sysdate-V_HIREDATE-300)*0.1;
11 end case;
12 update scott.emp set sal=sal-v_decrement where empno=v_empno;
13 end;
14 /
PL/SQL procedure successfully completed
SQL> select * from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980/12/17 -587.00 20
7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981/4/2 2975.00 20
7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30
7782 CLARK MANAGER 7839 1981/6/9 2450.00 10
7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20
7839 KING PRESIDENT 1981/11/17 5000.00 10
7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987/5/23 1100.00 20
7900 JAMES CLERK 7698 1981/12/3 950.00 30
7902 FORD ANALYST 7566 1981/12/3 3000.00 20
7934 MILLER CLERK 7782 1982/1/23 1300.00 10
14 rows selected
SQL>
SQL> declare
2 V_HIREDATE scott.emp.hiredate%type;
3 v_empno scott.emp.empno%type;
4 v_decrement Number(4);
5 begin
6 v_empno:=&x;
7 select HIREDATE into V_HIREDATE from scott.emp where
8 empno=v_empno;
9 select empno,sal,HIREDATE,
10 (case
11 when sysdate-V_HIREDATE>300 then v_decrement:=(sysdate-V_HIREDATE-300)*0.1;
12 end)as
13 罚款数 from xs;
14 end;
15 /
declare
V_HIREDATE scott.emp.hiredate%type;
v_empno scott.emp.empno%type;
v_decrement Number(4);
begin
v_empno:=7369;
select HIREDATE into V_HIREDATE from scott.emp where
empno=v_empno;
select empno,sal,HIREDATE,
(case
when sysdate-V_HIREDATE>300 then v_decrement:=(sysdate-V_HIREDATE-300)*0.1;
end)as
罚款数 from xs;
end;
ORA-06550: 第 12 行, 第 49 列:
PL/SQL: ORA-00905: 缺失关键字
ORA-06550: 第 10 行, 第 3 列:
PL/SQL: SQL Statement ignored
ORA-06550: 第 13 行, 第 6 列:
PLS-00103: 出现符号 ")"在需要下列之一时：
; <an identifier>
<a double-quoted delimited-identifier>
SQL>
1.学分情况
create table t1 as select xh,xm,zym,
(
case
when zxf>50 then 'gao'
when zxf>40 then 'zhong'
else '学分不够，请继续'
end
)as 获得学分情况 from xs;
2.图书过期
create table t2 as select empno,ename,job,hiredate,
(case
when trunc(sysdate-hiredate)>360 then '过期了'
when hiredate is null then '没借书'
else '没过期'
end
)as 是否过期 from scott.emp;
3.求10的阶乘
------------------when
declare
i NUMBER:=2;
n NUMBER:=1;
begin
LOOP
n:=n*i;
i:=i+1;
exit when i>10;
END LOOP;
dbms_output.put_line(to_char(n));
end;
-----------------------------while
SQL> declare
2 i NUMBER:=2;
3 n NUMBER:=1;
4 begin
5 while i<=10
6 LOOP
7 n:=n*i;
8 i:=i+1;
9
10 END LOOP;
11 dbms_output.put_line(to_char(n));
12 end;
13
14 /
PL/SQL procedure successfully completed
-----------------------------for
SQL> declare
2 i NUMBER:=2;
3 N NUMBER:=1;
4 begin
5 for i in 2..10
6 LOOP
7 n:=n*i;
8 END LOOP;
9
10 dbms_output.put_line(to_char(n));
11 end;
12 /
PL/SQL procedure successfully completed
显示学生成绩所属级别
create table t3 as select xh,kch,cj,
(
case
when cj>90 then '优秀'
when cj>60 then '良好'
else '差'
end
) as 级别 from xs_kc;
游标----while
declare
cursor cursor_dep is select department_id,avg(salary) avgsal from hr.employees
group by department_id;
v_dep cursor_dep%rowtype;
begin
open cursor_dep;
fetch cursor_dep into v_dep;
while cursor_dep%found
LOOP
DBMS_OUTPUT.PUTLINE(v_dep.department_id||' '||v_dep.avgsal);
fetch cursor_dep into v_dep;
END LOOP;
close cursor_dep;
end;
游标—for
declare
cursor cursor_dep is select department_id,avg(salary) avgsal from hr.employees
group by department_id;
v_dep cursor_dep%rowtype;
begin
for v_dep in cursor_dep
LOOP
DBMS_OUTPUT.PUTLINE(v_dep.department_id||' '||v_dep.avgsal);
END LOOP;
end;
-----------------for2
begin
for v_dep in （select department_id,avg(salary) avgsal from hr.employees
group by department_id）
LOOP
DBMS_OUTPUT.PUTLINE(v_dep.department_id||' '||v_dep.avgsal);
END LOOP;
end;
SQL> /*简单型case*/
2 declare
3 grade varchar2(4) := '良好';
4 begin
5 case grade
6 when '优秀' then dbms_output.put_line('大于等于90');
7 when '良好' then dbms_output.put_line('大于等于80，小于90');
8 when '及格' then dbms_output.put_line('大于等于60，小于80');
9 else dbms_output.put_line('不及格');
10 end case;
11 end;
12 /
大于等于80，小于90
PL/SQL procedure successfully completed
SQL> select * from emp where empno='7788';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20
SQL> select * from emp where empno='7788';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7788 SCOTT ANALYST 7566 1987/4/19 3150.00 20
SQL>
SQL> declare
2 v_deptno emp.deptno%type;
3 v_increment number(4);
4 v_empno emp.empno%type;
5 v_record emp%rowtype;
6 begin
7 v_empno := '7788';
8 select deptno into v_deptno from emp where empno=v_empno;
9 case v_deptno
10 when 10 then v_increment := 100;
11 when 20 then v_increment := 150;
12 when 30 then v_increment := 200;
13 else v_increment := 300;
14 end case;
15 update emp set sal=sal+v_increment where empno=v_empno;
16 if SQL%found then
17 dbms_output.put_line('更改成功');
18 select * into v_record from emp where empno=v_empno;
19 dbms_output.put_line(v_record.empno||' '||v_record.ename||' '||v_record.job||' '||v_record.mgr||' '||v_record.hiredate||' '||v_record.sal||' '||v_record.comm||' '||v_record.deptno);
20 end if;
21 end;
22 /
更改成功
7788 SCOTT ANALYST 7566 19-4月 -87 3300 20
PL/SQL procedure successfully completed
SQL> /*搜索case*/
2 declare
3 score int := 91;
4 begin
5 case
6 when score>=90 then dbms_output.put_line('优秀');
7 when score>=80 then dbms_output.put_line('良好');
8 when score>=60 then dbms_output.put_line('及格');
9 else dbms_output.put_line('不及格');
10 end case;
11 end;
12 /
优秀
PL/SQL procedure successfully completed
扫一扫
705
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
