SqlPlus 编程点滴

pl/sql是oracle在标准的sql语言上的扩展。其不仅允许嵌入sql语言，还可以定义变量和常量，允许使用条件语句和循环语句，允许使用例外处理各种错误。

pl/sql可以在数据库中写入存储过程(过程，函数，触发器)。

• 创建过程:create or replace procedure 过程名 is/as

begin
执行部分:
insert into 表名 (字段名) values(字段信息);
end;

create or replace procedure sbly_pro1 is
	begin
		insert into imagination values('sbly','world',20)
	end

查看错误: show error;

• 调用该过程:

1.SQL> exec 过程名(参数1，参数2，……);
2.SQL> call 过程名(参数1，参数2，……); /*注意:exec调用过程时若过程没有参数则可以不写括号，而call必须写括号，即使过程没有参数！*/

编写规范:

1.注释规范单行注释: --;
多行注释: /*……*/;
2.标识符的命名规范
1.当定义变量时，建议用v_作为前缀，如v_sal;
2.当定义常量时，建议用c_作为前缀，如c_rate;
3.当定义游标时，建议用_cursor作为后缀，如emp_cursor;
4.当定义例外时，建议用e_作为前缀，如e_error;

语法

pl/sql由3个部分构成，定义部分，执行部分，例外处理部分，如下:
declear
/*定义部分:定义常量，变量，游标，例外，复杂数据类型*/
begin
/*执行部分:要执行的pl/sql语句和sql语句*/
exception
/*例外处理部分:处理运行的各种错误*/
end;

特别说明:

定义部分是从declear开始的
该部分是可选的
执行部分是从begin开始的
该部分是必需的
例外处理部分是从exception开始的
该部分是可选的

pl/sql分类:
过程:
过程用于执行特定的操作，当建立过程时，既可以指定输入参数(in),也可以指定输出参数(out),通过在过程中使用输入参数，可以将数据传递到执行部分;通过使用输出参数，可以将执行部分的数据传递到应用环境。在sqlplus中可以使用create procedure命令来建立过程。
实例:
1.编写一个过程，可以输入雇员名，新工资，可以修改雇员的工资
2.如何调用过程有两种方法:exec 过程名/call 过程名

create procedure sbly_pro3(name varchar2,newsal number)--创建带有参数的过程
	is
	begin--执行部分，根据用户名修改工资;
	<span style="white-space:pre">	</span>update emp set sal=newsal where ename=name;
	end;

函数:
函数用于返回特定的数据，当建立函数时，在函数头部必须包含return子句，而在函数体内必须包含return语句返回的数据。可以使用create function来建立函数
实际案例:

create function annual_income(name varchar2)
	return number is
	annual_salary number(7,2);
	begin select sal*12+nvl(comm,0) into annual_salary from emp where ename=name;
	return annual_salary;
end;

在sqlplus中调用函数:
SQL> var income number
SQL> call annual_income('SCOTT') into income;
SQL> print income

包:
包用于在逻辑上组合过程和函数，它由包规范和包体两部分组成。
1.我们可以使用create package命令来创建包:
实例:

SQL> create package income_package
is
procedure update_sal(name varchar2,newsal number);
function annual_income(name varchar2) return number;
end;

包的规范只包含了过程和函数的说明，但是没有过程和函数的实现代码，包体用于实现包规范中的过程和函数。
2.建立包体可以用create package body命令
实例:

SQL> create package body income_package
is
purcedure update_sal(name varchar2,newsal number)
is
begin
update emp set sal=newsal where ename=name;
end;
function annual_income(name varchar2) return number
is
begin
select sal*12+nvl*12 into annual_income from emp where ename=name;
return annual_income;
end;
end;

调用包的过程或是函数:
当调用包的过程或是函数时，在过程和函数前需要带有包名，如果要访问其他方案的包，则还需要在包名前加方案名。
如:
SQL> call income_package.update_sal('SCOTT',1500);
特别说明:包在pl/sql中非常重要，在使用过程分页时，将广泛用到包并且体会到其强大

触发器:
触发器是指隐含执行的存储过程，当定义触发器时，必须要指定触发的事件和触发的操作，常用的触发事件包括insert,update,delete.触发器事件实际就是一个pl/sql块，可以使用create trigger创建触发器

定义并使用变量:
1.标准类型(scalar)
2.复合类型(composite)
3.参照类型(reference)
4.lob(large object)

标量(scalar)-常用类型
编写时如果要使用变量，需要在定义部分定义变量。定义常量和变量的语法如下:
identifier [constant] datatype [not null] [:=| default expr]
identifier:名称;constant:指定常量，需要指定它的初始值，且其值是不能改变的;datatype:数据类型;not null:指定变量值不能为空;:=给变量或者是常量指定初始值;default:用于指定初始值;expr:指定初始值的pl/sql表达式，可是文本值、其他变量、函数等*
标量的定义案例:
1.定义一个变长字符串:v_ename varchar2(13);
2.定义一个小数，范围在-9999.99~9999.99:v_sal number(6,2);
3.定义一个小数，并给予初值为5.4:v_sal number(6.2):=5.4;
4.定义一个日期:v_hiredate date;
5.定义一个布尔变量，不能为空切初值为false:v_valid boolean not null default false;

举例:下面以输入员工工号，显示雇员姓名，工资，个人所得税(税率为0.03)为例，说明变量的使用:

SQL> declare
--常量:税率
c_tax_rate number(3.2):=0.03;
--变量:
v_ename varchar2(5);
v_sal number(7,2);
v_tax_sal number(7,2);
begin
select ename,sal into v_ename,v_sal from emp where empno=&no;
--计算所得税
v_tax_sal:=v_sal*c_tax_rate;
--输出:
dbms_output.put_line('姓名是:'||v_ename||'工资是:'||v_sal||'交税:'||v_tax_sal);
end;

标量(scalar)-使用%type类型
--/*对于上面的块来说:如果员工姓名超过了规定字符数的话就会有错误，为了降低pl/sql程序的维护工作量，可以使用%type属性定义变量，这样它会按照数据库列来确定你定义的变量的类型和长度*/
举例:

SQL> 标识符名 表名.列名%type;
如上面:
v_ename emp.ename%type;
v_sal emp.sal%type;

复合变量(composite)-介绍:
用于存放多个值的变量，主要包括这几种:
1.pl/sql记录
2.pl/sql表
3.嵌套表
4.varray
复合类型-pl/sql记录:
pl/sql记录类似于高级语言中的结构体，注意:当引用pl/sql记录成员时，必须要加上记录变量作为前缀(记录变量.记录成员)。如下:

SQL> declare
type emp_record_type is record(
name emp.ename%type,
salary emp.sal%type,
title emp.job%type);--以上几行为定义一个emp_record_type类型，其中可以存放name,salary,title三个变量。类似于结构体
myrecord emp_record_type;--定义myrecord变量为emp_record_type类型
begin
select ename,sal,job,into myrecord from emp where empno=7788;--查找表中empno为7788的员工的信息存入myrecord中
dbms_output.put_line('员工名:'||myrecord.name||'薪水:'||myrecord.salary);--从myrecord中取出得到的值进行打印
end;

复合类型-pl/sql表:
pl/sql表类似于高级语言中的数组，但是需要注意:高级语言中数组下标不能为负数，而pl/sql表可以为负数，并且表元素下个书没有限制。如下:

SQL> declare
/*定义了一个pl/sql表类型，名字是my_table_type。该类型是用于存放emp.ename%type类型的元素(数组功能)*/
--index by binary_integer:表示下标是整数(包括正整数，负整数，0)
type my_table_type is table of emp.ename%type index by binary_integer;
my_table my_table_type;--定义了一个my_table变量，类型为my_table_type;
begin
select ename into my_table(0) from emp where empno=7788;--将工号是7788的员工姓名放到my_table变量的0号位置上
dbms_output.put_line('员工名:'||my_table(0));--打印出0号位置的值
end;

--注意:若将select语句中的where子句去掉，则会报错，报"实际返回的行数超出请求行数"，既对应数组一个位置只能放置一个值。

参照变量:
用于存放数值指针的变量，通过使用参照变量，可以是的应用程序共享相同的对象，从而降低占用空间。在编写pl/sql程序时，可以使用游标变量(ref cursor)和对象类型的变量(ref obj_type)两种参照变量
参照变量-ref cursor游标变量
使用游标时，当定义游标时不需要指定相应的select语句，但是当使用游标时(open时)需要指定select语句，这样一个游标就与一个select语句结合了。如下:
1.请使用pl/sql编写一个块，可以输入部门好，并显示该部门所有员工姓名和工资

SQL> declare
--定义
--定义一个游标
type my_emp_cursor is ref cursor;
--定义一个游标变量
test_cursor my_emp_cursor
--定义变量
v_name emp.ename%type;
v_sal emp.sal%type;
--执行
begin
--把test_cursor和一个select结合
open test_cursor for select ename,sal from emp where deptno=&no;
--循环取出
loop
fetch test_cursor into v_name,v_sal;
--判断是否退出,既游标test_cursor为空
exit when test_cursor%notfound;
dbms_output.put_line('名字:'||v_name||'工资'||v_sal);
end loop;
end;

控制结构:
1.使用if语句
2.使用循环语句
3.使用控制语句——goto,null;
条件分支:
1.if——then
2.if——then——else
3.if——then——elsif——else
案例:
1.if——then语句
编写一个过程，可以输入一个雇员名，如果该雇员的工资低于2000，就给该雇员工资增加%10；

SQL> create or replace procedure sbly_pro4(name varchar2) is
--定义
v_sal emp.sal%type
begin
select sal into v_sal from emp where ename=name;
--判断
if v_sal<2000
then
update emp set sal=sal+sal*0.1;
end if;
end;

2.二重分支条件  if——then——else
编写一个过程，可以输入一个雇员名，如果该雇员的补助不是0则增加10%，如果是0就将补助改为200

SQL> create or replace procedure sbly_pro5(name varchar2) is
--定义
v_comm emp.comm%type;
begin
select sal into v_comm from emp where ename=name;
if v_comm=0
then
update emp set comm=200 where ename=name;
else
update emp set comm=comm+comm*0.1 where ename=name;
end if;
end;

3.多重分支条件 if——then——elsif——then——else
编写一个过程，可以输入一个雇员编号，如果该雇员的职位是'king'则工资上涨13，如果是该雇员的职位是'queen'则工资上涨130，其他人的工资涨1300

SQL> create or replace procedure sbly_pro6(num number)
is
v_job emp.job.%type;
begin
select job into v_job from emp where empno=num;
if
v_job='king'
then
update emp set sal=sal+13 where empno=num;
elsif
v_job='queen'
then
update emp set sal=sal+130 where empno=num;
else
update emp set sal=sal+1300 where empno=num;
end if;
end;

循环语句-loop
loop是pl/sql最简单的循环语句，以loop开头，以end loop结尾。
/*注意:这种循环至少被执行一次*/
案例:现有一张users表，表结构:
用户id   |  用户名
编写一个过程，可规定用户名，并循环添加10个人到user表中，用户编号从1开始

SQL> create or replace procedure sbly_pro7(name varchar2)
is
--定义:
v_num number:=1;
begin
loop
insert into users values(v_num,name);
--判断退出循环条件
exit when v_num=10;
--自增:
v_num=v_num+1;
end loop;
end;

循环语句-while循环
只有条件为true才会执行循环体语句，既:可能一次都不循环
以while...loop开始，以end loop结束
案例:
用上一个案例的表结构
编写一个过程，可规定用户名，并循环添加10个人到user表中，用户编号从11开始

SQL> create or replace procedure sbly_pro7(name varchar2)
is
v_num:=11;
begin
while v_num<=20
loop
insert into users values(v_num,name);
v_num=v_num+1;
end loop;
end;

循环语句-for 循环
基本for循环的基本结构如下:

begin
for i in reverse 1..10 loop
insert into users values(i,'字符串');
end loop;
end;

顺序控制语句:-goto null
1.goto语句用于跳转到指定的标号去执行语句
/*注:使用goto会增加程序的复杂性，使程序可读性变差。一般情况下不建议使用*/
基本语法如下:goto label;其中lable是已经定义好的标号

SQL> 
declare
i int:=1;
begin
loop
dbms_output.put_line('输出i='||i);
if i=10
then
goto end_loop;
end if;
i:=i+1;
end loop;
<<end_loop>>
dbms_output.put_line('循环结束');
end;

2.null
null语句不会执行任何操作，且会直接将控制传递到下一条语句。使用的好处是可以提高程序可读性
举例:

SQL> declare
v_sal emp.sal%type;
v_name emp.ename%type;
begin
select ename sal into v_name v_sal from emp where ename=&no;
if v_sal<3000
then
update emp set comm=sal*0.1 where ename=&no;
else
null;
end if;
end;

编写一个过程，可以向book表添加书，要求通过java程序调用该过程
--创建表:
SQL> create table book(bookId number,bookName varchar2(52),publishHouse varchar2(52));
--编写过程

SQL> create or replace procedure sbly_pro8(ID in number,name in varchar2,publish in varchar2)
--in表示是一个输入变量
/*注意:如果不写in则默认是in.此处是因为之后有输出变量*/
--out表示是一个输出变量\
is
begin
insert into book values(ID,name,publish);
end;

有返回值的存储过程(非列表)
案例:
编写一个过程，可以输入雇员的编号，返回雇员姓名。

SQL> create or replace procedure sbly_pro9(num in number,name out varchar2)
is
begin
select ename into name from emp where empno=num;
end;

//注意:基本案例已被一下扩展案例替代，若要改回请重新执行上面代码
案例扩张:编写一个过程，可以输入雇员的编号，返回该雇员的姓名工资和岗位。

SQL> create or replace procedure sbly_pro9(num in number,name out varchar2,salary out number,empjob out varchar2)
is
begin
select ename,sal,job into name,salary,empjob from emp where empno=num;
end;

/*注意:name是一个输出变量*/
有返回值的存储过程(列表[结果集])
案例:
编写一个过程，输入部门号，返回该部门所有雇员信息。 对该题分析如下:
由于oracle存储过程没有返回值，它的所有返回值都是通过out参数来代替的，但是因为列表是集合，所以不能用一般的参数，必须用到package,所以要分2部分:
1.建立一个包，如下:

create or replace package testpackage
AS
TYPE test_cursor is ref cursor;
end testpackage;

2.建立一个存储过程，如下:

create or replace procedure sbly_pro10(num in number,cur out testpackage.test_cursor)
is
begin
open cur for select * from emp where deptno=num;
end;

Oracle 的分页:
举例:从emp表中查找第3，4个信息:
SQL> select * from (select a1.*,rownum rn from (select * from emp) a1 where rownum<=4) where rn>=3;
/*注意:此处语句可作为一个模板使用。解释如下:*/
/*解释:先查找出emp表中所有信息，将此表取别名:a1；继而通过自嵌套查询a1中所有信息，并且将分页信息rownum显示出来，末尾条件判断为:小于(等于)某一分页的所有内容；将以上查询结果再次看作一张整体表，查找其全部信息，并限定分页别名为大于(等于)某一分页的全部记录……到此，分页查询完毕*/
编写分页需要用到的包:

SQL> create or replace package sbly_package2
as
type page_cursor is ref cursor;
end sbly_package2;

案例

SQL> create or replace
procedure sbly_pro11
(
tablename varchar2,--表名
pagesize number,--每页显示的记录数
pagenow number,--显示的页码
myrows out number,--总记录数
pagecount out number,--总页数
p_cursor out sbly_package2.page_cursor--返回的记录集
)
is
v_sql varchar2(1001);
--定义两个整数
/*注意:以下两个变量为分页算法!切记*/
/*注:分页算法在该课程第12讲有涉及，且在servlet第4讲中有讲解*/
v_begin number:=(pagenow-1)*pagesize+1;
v_end number:=pagenow*pagesize;
begin
v_sql:='select * from (select a1.*,rownum rn from (select * from '||tablename||') a1 where rownum<='||v_end||') where rn>'||v_begin;
--把游标和sql语句关联起来
open p_cursor for v_sql;
--计算rows和pagecount
--组织一个sql语句
v_sql:='select count(*) from '||tablename;
--执行sql语句并把返回值赋给rows
execute immediate v_sql into myrows;
--计算pagecount
if
--注意:pl/sql中没有'%'取模的写法，既:rows%pagecount为错误语句。此处需要用mod函数
mod(myrows,pagesize)=0
then
pagecount:=myrows/pagesize;
else
pagecount:=myrows/pagesize+1;
end if;
--close p_cursor;
end;

例外处理:
分为预定义例外，非预定义例外和自定义例外
预定义例外:用于处理常见oracle错误
非预定义例外:用于处理预定义例外不能处理的例外
自定义例外:用于处理与oracle错误无关的其他情况

例外传递:
案例:编写一个过程，接受雇员的编号，先是雇员姓名
declare
v_name emp.ename%type
begin
select ename into v_name from emp where empno=&no;
dbms_output.put_line('该员工是:'||v_name);
--例外处理
exception
when no_data_found
then
dbms_output.put_line('未找到数据');
end;
/
问题:输入雇员编号不存在时会出现例外
/*注:已在程序中处理*/


处理预定义例外:
预定义例外是由pl/sql提供的系统例外。当pl/sql应用程序违反了oracle规定的限制时，会隐含出发一个内部例外。pl/sql提供了二十多个预定义例外
case_not_found:在编写case语句时如果在when子句中没有包含必须的条件分支就会触发该例外
案例:
SQL> create or replace procedure test_pro(num number)
is
v_sal emp.sal%type;
begin
select sal into v_sal where empno=num;
case
when
v_sal<1000
then
update emp set sal=sal+100 where empno=num;
when
then
v_sal<2000
update emp set sal=sal+200 where empno=num;
end case;
exception
when
case_not_found
then
dbms_output.put_line('case语句没有与'||v_sal||'相匹配的条件');
end;
/
//问题:如果v_sal>2000则不会进入任何一个分支，此时出发该例外


预定义例外
cursor_already_open:当重新打开已经打开的游标时，会隐含地触发该例外
案例:
SQL> declare
cursor emp_cursor is select ename,sal from emp;
begin
open emmp_cursor;
for emp_record1 in emp_cursor
loop
dbms_output.put_line(emp_record1.ename);
end loop;
exception
when
cursor_already_open
then
dbms_output.put_line('游标已打开');
end;
/*注意:此处open语句已经打开游标，而for emp_record1 in emp_cursor又打开一次游标，则会触发此异常*/

预定义例外
dup_val_on_index:在唯一索引所对应的列表上插入重复的值，会隐含触发该例外
案例:
begin
insert into dept values(10,'公关部','北京');
exception
when
dup_val_on_index
then
dbms_output.put_line('在deptno列上不能出现重复值');
end;

/*注意:假设此处10号部门已经存在且不能重复*/

预定义例外
invaild_cursor:当试图在不合法的游标上执行操作时(无该游标存在或者游标未打开之类称为非法游标)，会触发该例外
例如:视图从没有打开的游标提取数据，或是关闭没有打开的游标
SQL> declare
cursor emp_cursor is select ename,sal from emp;
emp_record emp_cursor%rowtype;
begin
open emp_cursor;--打开游标被注释
fetch emp_cursor into emp_record;
dbms_output.put_line(enp_record.ename);
close emp_cursor;
exception
when
invaild_cursor
then
dbms_output.put_line('游标未打开');
end;

/*注:将开启游标一句注释之后，查询游标中的值与关闭游标就为非法操作*/


预定义例外:
invalid_number:当输入的数据有错误则会触发该例外
案例:
begin
update emp set sal=sal+'2b';
exception
when
invalid_number
then
dbms_output.put_line('输入数据有误');
end;

预定义例外:
no_data_found:之前使用过，不再作解释与演示
预定义例外:
too_many_rows:当执行select into 语句时，如果返回值超过一行，则会触发该例外
案例:
SQL> declare
v_ename emp.ename%type;
begin
select ename into v_ename from emp;
exception
when
too_many_rows;
then
dbms_output.put_line('返回多个值');
end;
/*注意:v_ename只能接受1个值*/
预定义例外:
zero_divide:除以0的例外

预定义例外:
value_error:当执行赋值操作时，如果变量的长度不足以容纳实际数据，则触发该例外
案例:
SQL> declare
v_ename varchar2(5);
begin
select ename into v_ename from emp where empno=&no;
dbms_output.put_line(v_ename);
exception
when
value_error
then
dbms_output.put_line('超出变量尺寸');
end;
/*注意:当输入工号比对后返回的姓名大于5个字符则会引发异常*/
其他预定义例外:
login_denide:当用户非法登陆时，会触发该例外
not_logged_on:如果用户没有登陆就执行dml操作，就会触发该例外
storage_error:如果超出了内存空间或者内存被损坏就会触发该例外
timeout_on_resource:如果oracle在等待资源时出现超时就会触发该例外

非预定义例外:
用于处理与预定义例外无关的oracle错误。比如pl/sql块中执行dml语句时违反约束规定等等，在这样的情况下也可以处理各种例外
略
处理自定义例外:
预定义例外和非预定义例外都与oracle错误相关，并且出现的oracle错误会隐含的处罚相关例外。而自定义例外与oracle错误没有任何关联，它是由开发人员为特定的情况所定义的例外
案例:
编写一个pl/sql块，接受一个雇员的编号，并给该雇员工资增加130.若该雇员不存在则提示
/*注意:当语句为update语句时系统不会报no_data_found例外。若此时确实要认定例外则使用自定义例外*/
SQL> create or replace procedure sbly_pro12(num number)
is
--定义一个例外:
sblyex1 exception;
begin
update emp sat sal=sal+130 where empno=num;
if
sql%notfound--该语句表示没有更新操作
then
raise sblyex1;--该语句表示触发该例外
end if;
exception
when
sblyex1
then
dbms_output.put_line('没有更新，因为没有该雇员');
end;

视图:
试图使一个虚拟表，内容有查询定义。同真实的表一样，试图包含一系列带有名称的列和数据。但是视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成


表和视图的区别:
1.表需要占用磁盘空间，视图不需要
2.视图不能添加索引
3.使用试图可以简化复杂查询
4.视图利于提高安全性
创建视图:
SQL> create view 视图名 as select语句[with read only];
案例:创建一个视图显示雇员的编号，姓名，部门名称 (多表)
SQL> create view sblyv1 as select emp.empno,emp.ename,dept.dname from emp,dept where emp.deptno=dept.deptno;

创建或修改视图:
SQL> create or replace view 视图名 as select语句 [with read only];

删除视图:
drop view 视图名;
//注意:多张试图可以再次作为多表进行多表查询进而再次降低复杂度
//注意:末尾的with read only表示视图只是可读而不可改

注：本文学习自韩顺平老师的Oracle视频(ITCAST)