接 oracle-学习笔记-1
-------------------------------------
4. PL/SQL 块
4.1 PL/SQL 块
SQL> declare
2 x varchar2(10);
3 begin
4 x:='This is..';
5 dbms_output.put_line('x的值为：'||x);
6 end;
7 /
PL/SQL 过程已成功完成。
SQL> set serveroutput on size 10000
SQL> l
1 declare
2 x varchar2(10);
3 begin
4 x:='This is..';
5 dbms_output.put_line('x的值为：'||x);
6* end;
SQL> /
x的值为：This is..
PL/SQL 过程已成功完成。
SQL> l
1 declare
2 x varchar2(10):='ABCDE';
3 y string(10):='123';
4 begin
5 --x:='This is..';
6 dbms_output.put_line('x的值为：'||x||'y的值为：'||y);
7 /*
8 dbms_output.put_line('x的值为：'||x);
9 dbms_output.put('x的值为：'||x);
10 */
11* end;
SQL> @plsql_01
x的值为：ABCDEy的值为：123
PL/SQL 过程已成功完成。
4.2分支语句
SQL> l
1 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;
SQL> /
b的值是：B
PL/SQL 过程已成功完成。
SQL> l
1 declare
2 a number;
3 b varchar2(10);
4 begin
5 a:=53;
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;
SQL> /
b的值是：C
PL/SQL 过程已成功完成。
4.3循环语句
SQL> l
1 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('内：x='||x);
11 end loop;
12 dbms_output.put_line('外：x='||x);
13* end;
SQL> /
内：x=1
内：x=2
外：x=3
PL/SQL 过程已成功完成。
SQL> l
1 declare
2 x number;
3 begin
4 x:=0;
5 loop
6 x:=x+1;
7 exit when x>=3;
8 dbms_output.put_line('内：x='||x);
9 end loop;
10 dbms_output.put_line('外：x='||x);
11* end;
SQL> /
内：x=1
内：x=2
外：x=3
PL/SQL 过程已成功完成。
SQL> l
1 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;
SQL> /
内：x=1
内：x=2
内：x=3
内：x=4
外：x=4
PL/SQL 过程已成功完成。
SQL> l
1 begin
2 for i in 1..5 loop
3 dbms_output.put_line('i='||i);
4 end loop;
5 dbms_output.put_line('end of for loop');
6* end;
SQL> /
i=1
i=2
i=3
i=4
i=5
end of for loop
PL/SQL 过程已成功完成。
SQL> l
1 begin
2 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;
SQL> /
i=5
i=4
i=3
i=2
i=1
end of for loop
PL/SQL 过程已成功完成。
SQL> l
1 declare
2 x number;
3 begin
4 x:=0;
5 <<repeat_loop>>
6 x:=x+1;
7 dbms_output.put_line('x='||x);
8 if x<3 then
9 goto repeat_loop;
10 end if;
11* end;
SQL> /
x=1
x=2
x=3
PL/SQL 过程已成功完成。
4.4异常处理
SQL> l
1 declare
2 b varchar2(10);
3 begin
4 select ename into b from emp where empno = '1';
5 dbms_output.put_line(b);
6* end;
SQL> /
declare
*
第 1 行出现错误:
ORA-01403: 未找到数据
ORA-06512: 在 line 4
SQL> l
1 declare
2 b varchar2(10);
3 begin
4 select ename into b from emp where empno = '1';
5 dbms_output.put_line(b);
6* end;
SQL> l
1 declare
2 b varchar2(10);
3 begin
4 select ename into b from emp where empno = '1';
5 dbms_output.put_line(b);
6 exception
7 when no_data_found then
8 dbms_output.put_line('没有找到数据!');
9* end;
SQL> /
没有找到数据!
PL/SQL 过程已成功完成。
SQL> l
1 declare
2 b varchar2(10);
3 e exception;
4 begin
5 select ename into b from emp where empno = '7369';
6 if b<>'张三' then
7 raise e;
8 end if;
9 dbms_output.put_line(b);
10 exception
11 when e then
12 dbms_output.put_line('错误，不是 张三 !');
13* end;
SQL> /
错误，不是 张三 !
PL/SQL 过程已成功完成。
4.5记录 - 复合变量
SQL> l
1 declare
2 type myrecord is record(
3 id number,
4 name varchar2(10));
5 real_record myrecord;
6 begin
7 select empno,ename into real_record from emp where empno='7566';
8 dbms_output.put_line(real_record.id||','||real_record.name);
9* end;
SQL> /
7566,JONES
PL/SQL 过程已成功完成。
SQL> l
1 declare
2 type myrecord is record(
3 id emp.empno%type,
4 name varchar2(10));
5 real_record myrecord;
6 begin
7 select empno,ename into real_record from emp where empno='7566';
8 dbms_output.put_line(real_record.id||','||real_record.name);
9* end;
SQL> /
7566,JONES
PL/SQL 过程已成功完成。
SQL> l
1 declare
2 myrec emp%rowtype;
3 begin
4 select * into myrec from emp where empno='7566';
5 dbms_output.put_line(myrec.empno||','||myrec.ename||','||myrec.job);
6* end;
SQL> /
7566,JONES,MANAGER
PL/SQL 过程已成功完成。
5.PL/SQL高级应用
5.1 游标
使用游标可以充分操纵表的每一行
SQL> l
1 declare
2 cursor mycur is
3 select * from books;
4 myrecord books%rowtype;
5 begin
6 open mycur;
7 fetch mycur into myrecord;
8 while mycur%found loop
9 dbms_output.put_line(myrecord.books_id||','||myrecord.books_name);
10 fetch mycur into myrecord;
11 end loop;
12 close mycur;
13* end;
SQL> /
1,中国文学
2,外国文学
3,英语阅读
4,建筑艺术
5,计算机入门
6,数据结构
7,语文
PL/SQL 过程已成功完成。
上面是显式游标
SQL> l
1 declare
2 cursor cur_para(id number) is
3 select books_name from books where books_id=id;
4 t_name books.books_name%type;
5 begin
6 open cur_para(1);
7 loop
8 fetch cur_para into t_name;
9 exit when cur_para%notfound;
10 dbms_output.put_line(t_name);
11 end loop;
12 close cur_para;
13* end;
SQL> /
中国文学
PL/SQL 过程已成功完成。
SQL> l
1 declare
2 cursor cur_para(id number) is
3 select books_name from books where books_id=id;
4 begin
5 dbms_output.put_line('*******结果集为********');
6 for cur in cur_para(1) loop
7 dbms_output.put_line(cur.books_name);
8 end loop;
9* end;
SQL> /
*******结果集为********
中国文学
PL/SQL 过程已成功完成。
SQL> l
1 declare
2 t_name books.books_name%type;
3 cursor cur(id number) is
4 select books_name from books where books_id=id;
5 begin
6 if cur%isopen then
7 dbms_output.put_line('游标已经被打开!');
8 else
9 open cur(3);
10 end if;
11 fetch cur into t_name;
12 close cur;
13 dbms_output.put_line(t_name);
14* end;
SQL> /
英语阅读
PL/SQL 过程已成功完成。
SQL> l
1 declare
2 t_name varchar2(10);
3 cursor mycur is
4 select ename from emp;
5 begin
6 open mycur;
7 loop
8 exit when mycur%notfound or mycur%notfound is null;
9 dbms_output.put_line('游标mycur的rowcount是：'||mycur%rowcount);
10 end loop;
11 close mycur;
12* end;
SQL> /
PL/SQL 过程已成功完成。
SQL> l
1 declare
2 t_name varchar2(10);
3 cursor mycur is
4 select ename from emp;
5 begin
6 open mycur;
7 loop
8 fetch mycur into t_name;
9 exit when mycur%notfound or mycur%notfound is null;
10 dbms_output.put_line('游标mycur的rowcount是：'||mycur%rowcount);
11 end loop;
12 close mycur;
13* end;
SQL> /
游标mycur的rowcount是：1
游标mycur的rowcount是：2
游标mycur的rowcount是：3
游标mycur的rowcount是：4
游标mycur的rowcount是：5
游标mycur的rowcount是：6
游标mycur的rowcount是：7
游标mycur的rowcount是：8
游标mycur的rowcount是：9
游标mycur的rowcount是：10
游标mycur的rowcount是：11
游标mycur的rowcount是：12
游标mycur的rowcount是：13
游标mycur的rowcount是：14
PL/SQL 过程已成功完成。
SQL> l
1 declare
2 cursor cur is
3 select name from d for update;
4 text varchar2(10);
5 begin
6 open cur;
7 fetch cur into text;
8 while cur%found loop
9 update d set name=name||'_t' where current of cur;
10 fetch cur into text;
11 end loop;
12 close cur;
13* end;
SQL> /
PL/SQL 过程已成功完成。
SQL> commit;
提交完成。
5.2 隐式游标
SQL> l
1 begin
2 for cur in(select name from d) loop
3 dbms_output.put_line(cur.name);
4 end loop;
5* end;
SQL> /
A部门_t
B部门_t
C部门_t
D部门_t
E部门_t
F部门_t
G部门_t
PL/SQL 过程已成功完成。
5.3 存储过程
声明 执行 异常处理
输入参数（in） 输出参数（out）
创建一个存储过程：
SQL> @procedure_01
警告: 创建的过程带有编译错误。
SQL> show errors
PROCEDURE MYPROC 出现错误:
LINE/COL ERROR
-------- -----------------------------------------------------------------
5/1 PL/SQL: SQL Statement ignored
5/45 PL/SQL: ORA-00904: "BOOK_ID": 标识符无效
SQL> l
1 create or replace procedure myproc(id in varchar2)
2 is
3 name varchar2(10);
4 begin
5 select book_name into name from books where book_id=id;
6 dbms_output.put_line(name);
7* end myproc;
SQL> @procedure_01
过程已创建。
SQL> l
1 create or replace procedure myproc(id in varchar2)
2 is
3 name varchar2(10);
4 begin
5 select books_name into name from books where books_id=id;
6 dbms_output.put_line(name);
7* end myproc;
SQL> /
过程已创建。
使用存储过程：
SQL> l
1 declare
2 tid varchar2(10);
3 begin
4 tid:='0001';
5 myproc(tid);
6* end;
SQL> /
中国文学
PL/SQL 过程已成功完成。
SQL> l
1 begin
2 myproc('0001');
3* end;
SQL> /
中国文学
PL/SQL 过程已成功完成。
SQL> execute myproc('0001');
中国文学
PL/SQL 过程已成功完成。
存储过程存在输入参数和输出参数
SQL> l
1 create or replace procedure myproc2(id varchar2,name out varchar2)
2 is
3 select books_name into name from books where books_id=id;
4* end;
SQL> show errors
PROCEDURE MYPROC2 出现错误:
LINE/COL ERROR
-------- -----------------------------------------------------------------
3/1 PLS-00103: 出现符号 "SELECT"在需要下列之一时：
begin function package
pragma procedure subtype type use <an identifier>
<a double-quoted delimited-identifier> form current cursor
external language
SQL> @procedure_04
过程已创建。
SQL> l
1 create or replace procedure myproc2(id varchar2,name out varchar2)
2 is
3 begin
4 select books_name into name from books where books_id=id;
5* end;
SQL> l
1 declare
2 tid varchar2(10);
3 tname varchar2(10);
4 begin
5 tid:='0001';
6 myproc2(tid,tname);
7 dbms_output.put_line(tname);
8* end;
SQL> /
中国文学
PL/SQL 过程已成功完成。
5.4 视图
SQL> @view_01
视图已创建。
SQL> l
1 create or replace view myview
2 as
3* select * from books
SQL> insert into myview(books_id) values ('0008');
已创建 1 行。
SQL> select * from myview;
BOOKS_ID BOOKS_NAME PRICE QTY
---------- ------------------------------ ---------- ----------
PUB
--------------------------------------------------
8
1 中国文学 39 12
人民大学
2 外国文学 12.5 22
人民大学
BOOKS_ID BOOKS_NAME PRICE QTY
---------- ------------------------------ ---------- ----------
PUB
--------------------------------------------------
3 英语阅读 33 21
清华大学
4 建筑艺术 6 33
清华大学
5 计算机入门 44.8 24
电子工业
BOOKS_ID BOOKS_NAME PRICE QTY
---------- ------------------------------ ---------- ----------
PUB
--------------------------------------------------
6 数据结构 54.7 65
电子工业
7 语文 43 5
电子工业
已选择8行。
SQL> commit;
提交完成。
SQL> @view_02
视图已创建。
SQL> l
1 create or replace view myview
2 as
3* select * from books where price>30
SQL> /
视图已创建。
SQL> select * from myview
2 ;
BOOKS_ID BOOKS_NAME PRICE QTY PUB
-------- -------------------- ---------- ---- ---------------
1 中国文学 39 12 人民大学
3 英语阅读 33 21 清华大学
5 计算机入门 44.8 24 电子工业
6 数据结构 54.7 65 电子工业
7 语文 43 5 电子工业
SQL> @view_02
视图已创建。
SQL> l
1 create or replace view myview
2 as
3 select * from books where price>30
4* with check option
SQL> insert into myview values ('0009','aa',20,4,'bb');
insert into myview values ('0009','aa',20,4,'bb')
*
第 1 行出现错误:
ORA-01402: 视图 WITH CHECK OPTIDN where 子句违规
SQL> select empno,ename,job,d.id,d.name from emp e,d where e.empno=d.id;
EMPNO ENAME JOB ID NAME
---------- ---------- --------- ---------- ----------
7782 CLARK MANAGER 7782 组织
7876 ADAMS CLERK 7876 神仙部
SQL> create or replace view v_emp_d
2 as
3 select empno,ename,job,d.id,d.name from emp e,d where e.empno=d.id;
视图已创建。
SQL> save view_03
已创建 file view_03.sql
SQL> l
1 create or replace view v_emp_d
2 as
3* select empno,ename,job,d.id,d.name from emp e,d where e.empno=d.id
SQL> select * from v_emp_d;
EMPNO ENAME JOB ID NAME
---------- ---------- --------- ---------- ----------
7782 CLARK MANAGER 7782 组织
7876 ADAMS CLERK 7876 神仙部
SQL> desc user_views
名称 是否为空? 类型
----------------------------------------------------- -------- ---------------------------------
VIEW_NAME NOT NULL VARCHAR2(30)
TEXT_LENGTH NUMBER
TEXT LONG
TYPE_TEXT_LENGTH NUMBER
TYPE_TEXT VARCHAR2(4000)
OID_TEXT_LENGTH NUMBER
OID_TEXT VARCHAR2(4000)
VIEW_TYPE_OWNER VARCHAR2(30)
VIEW_TYPE VARCHAR2(30)
SUPERVIEW_NAME VARCHAR2(30)
SQL> select text from user_views where view_name = 'v_read';
未选定行
5.5 同义词
SQL> connect sys/ora as sysdba
已连接。
SQL> select user from dual;
USER
------------------------------
SYS
SQL> select * from dept;
select * from dept
*
第 1 行出现错误:
ORA-00942: 表或视图不存在
SQL> select * from scott.dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
公共同义词 私有同义词
默认创建私有同义词
SQL> create synonym dept for scott.dept;
同义词已创建。
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> connect decoration/decoration
已连接。
SQL> select * from dept;
select * from dept
*
第 1 行出现错误:
ORA-00942: 表或视图不存在
删除同义词：
SQL> connect sys/ora as sysdba
已连接。
SQL> drop synonym dept;
同义词已删除。
创建公共同义词：
SQL> create public synonym dept for scott.dept;
同义词已创建。
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> connect decoration/decoration;
已连接。
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
注意：前提是decoration用户要有select any table 的权限。
SQL> connect system/ora
已连接。
SQL> desc dba_synonyms;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
SYNONYM_NAME NOT NULL VARCHAR2(30)
TABLE_OWNER VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
DB_LINK VARCHAR2(128)
SQL> show user;
USER 为 "DECORATION"
SQL> create synonym dep for scott.dept;
同义词已创建。
SQL> select * from dep;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> desc user_synonyms;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
SYNONYM_NAME NOT NULL VARCHAR2(30)
TABLE_OWNER VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
DB_LINK VARCHAR2(128)
SQL> select synonym_name from user_synonyms;
SYNONYM_NAME
------------------------------
DEP
SQL> select * from user_synonyms;
SYNONYM_NA TABLE_OWNER TABLE_NAME DB_LINK
---------- --------------- --------------- ---------------
DEP SCOTT DEPT
5.6 序列
SQL> @sequence_01
序列已创建。
SQL> l
1 create sequence myseq
2 start with 1
3 increment by 1
4 order
5* nocycle
SQL> select myseq.nextval from dual;
NEXTVAL
----------
1
SQL> select myseq.nextval from dual;
NEXTVAL
----------
2
SQL> select myseq.nextval from dual;
NEXTVAL
----------
3
SQL> select myseq.nextval from dual;
NEXTVAL
----------
4
SQL> select myseq.currval from dual;
CURRVAL
----------
4
SQL> show user;
USER 为 "SCOTT"
SQL> create table auto(a number,b varchar2(10));
表已创建。
SQL> insert into auto(a,b) values (myseq.nextval,'abc');
已创建 1 行。
SQL> insert into auto(a,b) values (myseq.nextval,'abc');
已创建 1 行。
SQL> insert into auto(a,b) values (myseq.nextval,'abc');
已创建 1 行。
SQL> select * from auto;
A B
---------- ----------
22 abc
23 abc
24 abc
SQL> commit;
提交完成。
SQL> connect system/ora
已连接。
SQL> desc dba_sequences;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
SEQUENCE_OWNER NOT NULL VARCHAR2(30)
SEQUENCE_NAME NOT NULL VARCHAR2(30)
MIN_VALUE NUMBER
MAX_VALUE NUMBER
INCREMENT_BY NOT NULL NUMBER
CYCLE_FLAG VARCHAR2(1)
ORDER_FLAG VARCHAR2(1)
CACHE_SIZE NOT NULL NUMBER
LAST_NUMBER NOT NULL NUMBER
SQL> desc all_sequences;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
SEQUENCE_OWNER NOT NULL VARCHAR2(30)
SEQUENCE_NAME NOT NULL VARCHAR2(30)
MIN_VALUE NUMBER
MAX_VALUE NUMBER
INCREMENT_BY NOT NULL NUMBER
CYCLE_FLAG VARCHAR2(1)
ORDER_FLAG VARCHAR2(1)
CACHE_SIZE NOT NULL NUMBER
LAST_NUMBER NOT NULL NUMBER
SQL> desc user_sequences;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
SEQUENCE_NAME NOT NULL VARCHAR2(30)
MIN_VALUE NUMBER
MAX_VALUE NUMBER
INCREMENT_BY NOT NULL NUMBER
CYCLE_FLAG VARCHAR2(1)
ORDER_FLAG VARCHAR2(1)
CACHE_SIZE NOT NULL NUMBER
LAST_NUMBER NOT NULL NUMBER
SQL> show user;
USER 为 "SYSTEM"
SQL> select sequence_name,sequence_owner from dba_sequences
2 where sequence_owner = 'SCOTT';
SEQUENCE_NAME SEQUENCE_OWNER
------------------------------ ------------------------------
MYSEQ SCOTT
SQL> connect scott/tiger
已连接。
SQL> alter sequence myseq
2 increment by 3;
序列已更改。
SQL> select myseq.nextval from dual;
NEXTVAL
----------
27
SQL> select myseq.nextval from dual;
NEXTVAL
----------
30
序列可以修改增量，不能修改当前值。
