qlsql 学习实例代码1

准备工作：
create table DEPT
(
DEPTNO NUMBER(2) not null,
DNAME VARCHAR2(14),
LOC VARCHAR2(13)
)

alter table DEPT add constraint PK_DEPT primary key (DEPTNO);
insert into DEPT (DEPTNO, DNAME, LOC)
values (10, 'ACCOUNTING', 'NEW YORK');
insert into DEPT (DEPTNO, DNAME, LOC)
values (20, 'RESEARCH', 'DALLAS');
insert into DEPT (DEPTNO, DNAME, LOC)
values (30, 'SALES', 'CHICAGO');
insert into DEPT (DEPTNO, DNAME, LOC)
values (40, 'OPERATIONS', 'BOSTON');
commit;
create table EMP
(
EMPNO NUMBER(4) not null,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
)

alter table EMP add constraint PK_EMP primary key (EMPNO);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7369, 'SMITH', 'CLERK', 7902, to_date('17-12-1980', 'dd-mm-yyyy'), 800, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7499, 'ALLEN', 'SALESMAN', 7698, to_date('20-02-1981', 'dd-mm-yyyy'), 1600, 300, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7521, 'WARD', 'SALESMAN', 7698, to_date('22-02-1981', 'dd-mm-yyyy'), 1250, 500, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7566, 'JONES', 'MANAGER', 7839, to_date('02-04-1981', 'dd-mm-yyyy'), 2975, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7654, 'MARTIN', 'SALESMAN', 7698, to_date('28-09-1981', 'dd-mm-yyyy'), 1250, 1400, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7698, 'BLAKE', 'MANAGER', 7839, to_date('01-05-1981', 'dd-mm-yyyy'), 2850, null, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7782, 'CLARK', 'MANAGER', 7839, to_date('09-06-1981', 'dd-mm-yyyy'), 2450, null, 10);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7788, 'SCOTT', 'ANALYST', 7566, to_date('19-04-1987', 'dd-mm-yyyy'), 3000, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7839, 'KING', 'PRESIDENT', null, to_date('17-11-1981', 'dd-mm-yyyy'), 5000, null, 10);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7844, 'TURNER', 'SALESMAN', 7698, to_date('08-09-1981', 'dd-mm-yyyy'), 1500, 0, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7876, 'ADAMS', 'CLERK', 7788, to_date('23-05-1987', 'dd-mm-yyyy'), 1100, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7900, 'JAMES', 'CLERK', 7698, to_date('03-12-1981', 'dd-mm-yyyy'), 950, null, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7902, 'FORD', 'ANALYST', 7566, to_date('03-12-1981', 'dd-mm-yyyy'), 3000, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7934, 'MILLER', 'CLERK', 7782, to_date('23-01-1982', 'dd-mm-yyyy'), 1300, null, 10);
======================以上为准备工作

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as system
------------------------------------ 简单的plsql块执行的过程
SQL> declare
2 x varchar2(10); --定义变量 可以附初值。例子: x varchar2(10) := 'abcd';
3 begin
4 x:= 'this is ..';
5 dbms_output.put_line('x的值为:'||x);
6 end;
7 /

PL/SQL procedure successfully completed
--执行成功，没有结果显示是因为默认的serveroutput 是关闭的
SQL> ?set --查看set 命令
SQL> set serveroutput on size 10000 --打开显示结果开关 并设置显示的字节数 最多是一百万 其中的size 10000 可省略

SQL> declare --重新执行
2 x varchar2(10);
3 begin
4 x := 'this is';
5 dbms_output.put_line('x的值为:'|| x);
6 end;
7 /

x的值为:this is

PL/SQL procedure successfully completed

-------------------------------------------if 语句
SQL> declare
2 a number;
3 b varchar2(10);
4 begin
5 a:=2;
6 if a=1 then
7 b:='A';
8 elsif a=2 then
9 b:='B';
10 else
11 b:='C';
12 end if;
13 dbms_output.put_line('b='||b);
14 end;
15 /

b=B

PL/SQL procedure successfully completed
---------------------------------------------case语句
SQL> declare
2 a number;
3 b varchar2(10);
4 begin
5 a:=2;
6 case
7 when a=1 then b:='A';
8 when a=2 then b:='B';
9 when a=3 then b:='C';
10 else
11 b:='others';
12 end case;
13 dbms_output.put_line('b='||b);
14 end;
15 /

b=B

PL/SQL procedure successfully completed
--------------------------------------------loop循环
SQL> declare
2 x number;
3 begin
4 x:=0;
5 loop
6 x:=x+1;
7 if x >= 3 then
8 exit;
9 end if;
10 dbms_output.put_line('inner: x='||x);
11 end loop;
12 dbms_output.put_line('outer: x='||x);
13 end;
14 /

x=1
x=2
outer: x=3

PL/SQL procedure successfully completed

SQL> declare
2 x number;
3 begin
4 x:=0;
5 loop
6 x:=x+1;
7 exit when x >= 3 ; ----替换上面的if语句
10 dbms_output.put_line('inner: x='||x);
11 end loop;
12 dbms_output.put_line('outer: x='||x);
13 end;
14 /

x=1
x=2
outer: x=3

PL/SQL procedure successfully completed
--------------------------------------------- while....loop 语句
SQL> declare
2 x number;
3 begin
4 x:=0;
5 while x<=3 loop
6 x:=x+1;
7 dbms_output.put_line('内:x='||x);
8 end loop;
9 dbms_output.put_line('外:x='||x);
10 end;
11 /

内:x=1
内:x=2
内:x=3
内:x=4
外:x=4

PL/SQL procedure successfully completed
--------------------------------------------- for循环
SQL> begin
2 for i in 1..5 loop --for i in reverse 1..5 loop 这样输出结果是从大到小
3 dbms_output.put_line('i='||i);
4 end loop;
5 dbms_output.put_line('end of for loop');
6 end;
7 /

i=1
i=2
i=3
i=4
i=5
end of for loop

PL/SQL procedure successfully completed
---------------------------------------------goto跳转 的循环
SQL> declare
2 x number;
3 begin
4 x:=0;
5 <<repeat_loop>> --做一个跳转的标签
6 x:=x+1;
7 dbms_output.put_line(x);
8 if x<3 then
9 goto repeat_loop; --goto 跳转
10 end if;
11 end;
12 /

1
2
3

PL/SQL procedure successfully completed
--------------------------------------------------exception 处理
SQL> declare
2 test varchar2(10);
3 begin
4 select dname INTO test from dept where deptno = 5;
5 dbms_output.put_line(test);
6 end;
7 /

declare
test varchar2(10);
begin
select dname INTO test from dept where deptno = 5;
dbms_output.put_line(test);
end;

ORA-01403: no data found -----数据库提示的错误
ORA-06512: at line 5

SQL> declare
2 test varchar2(10);
3 begin
4 select dname INTO test from dept where deptno = 5;
5 dbms_output.put_line(test);
6 exception -----进行异常捕获
7 when no_data_found then
8 dbms_output.put_line('没有找到'); -----输出自己的提示
9 end;
10 /

没有找到

PL/SQL procedure successfully completed
-------------------常见系统异常
dup_val_on_index 向有唯一约束的表中插入重复行
no_data_found 在一个select into 语句中无返回值
too_many_rows select into 语句返回了多行
value_error 一个算法 转换 截断 或大小约束发生错误
zero_divide 发生被零除
----------------------------------------------------自定义异常
SQL> declare
2 tname varchar2(10);
3 e exception;
4 begin
5 select dname into tname from dept where deptno = 30;
6 if tname <> 'SALESs' then
7 raise e; ------抛出异常
8 end if;
9 dbms_output.put_line(tname);
10 exception
11 when e then ------捕获自定义的异常
12 dbms_output.put_line('错误，不是需要的名字');
13 end;
14 /

错误，不是需要的名字

PL/SQL procedure successfully completed
---------------------------------------------------
记录：

也叫复合变量，是有几个相关值构成的复合变量，常用于支持select语句的返回值，
使用记录可以将一行数据看成一个单元进行处理，而不必将每一列单独处理。

SQL> declare
2 type myrecord is record( -----------定义记录
3 id number, == id emp.empno%type
4 name varchar2(10)); == name emp.ename%type
5 real_record myrecord; -------引用定义的记录
6 begin
7 select empno,ename into real_record from emp where empno = 7369;
8 dbms_output.put_line(real_record.id||','||real_record.name);
9 end;
10 /

7369,SMITH

PL/SQL procedure successfully completed

SQL> declare
2 myrec emp%rowtype; -------------定义一个记录里面变量和表的字段，字段类型都一样
3 begin
4 select * into myrec from emp where empno = 7369;
5 dbms_output.put_line(myrec.empno||','||myrec.ename);
6 end;
7 /

7369,SMITH

PL/SQL procedure successfully completed

-------------------------------------------------------