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

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