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 isbegininsert 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)--创建带有参数的过程isbegin--执行部分，根据用户名修改工资;<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 isannual_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 numberSQL> call annual_income('SCOTT') into income;SQL> print income

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

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

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

SQL> create package body income_packageispurcedure update_sal(name varchar2,newsal number)isbeginupdate emp set sal=newsal where ename=name;end;function annual_income(name varchar2) return numberisbeginselect 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);beginselect 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> declaretype 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类型beginselect 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;beginselect 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;--循环取出loopfetch 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%typebeginselect sal into v_sal from emp where ename=name;--判断if v_sal<2000thenupdate 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;beginselect sal into v_comm from emp where ename=name;if v_comm=0thenupdate emp set comm=200 where ename=name;elseupdate 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)isv_job emp.job.%type;beginselect job into v_job from emp where empno=num;ifv_job='king'thenupdate emp set sal=sal+13 where empno=num;elsifv_job='queen'thenupdate emp set sal=sal+130 where empno=num;elseupdate 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;beginloopinsert 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)isv_num:=11;beginwhile v_num<=20loopinsert into users values(v_num,name);v_num=v_num+1;end loop;end;

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

beginfor i in reverse 1..10 loopinsert into users values(i,'字符串');end loop;end;

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

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

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

SQL> declarev_sal emp.sal%type;v_name emp.ename%type;beginselect ename sal into v_name v_sal from emp where ename=&no;if v_sal<3000thenupdate emp set comm=sal*0.1 where ename=&no;elsenull;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表示是一个输出变量\isbegininsert into book values(ID,name,publish);end;

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

SQL> create or replace procedure sbly_pro9(num in number,name out varchar2)isbeginselect 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)isbeginselect ename,sal,job into name,salary,empjob from emp where empno=num;end;

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

create or replace package testpackageASTYPE test_cursor is ref cursor;end testpackage;

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

create or replace procedure sbly_pro10(num in number,cur out testpackage.test_cursor)isbeginopen 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_package2astype page_cursor is ref cursor;end sbly_package2;

案例

SQL> create or replaceprocedure sbly_pro11(tablename varchar2,--表名pagesize number,--每页显示的记录数pagenow number,--显示的页码myrows out number,--总记录数pagecount out number,--总页数p_cursor out sbly_package2.page_cursor--返回的记录集)isv_sql varchar2(1001);--定义两个整数/*注意:以下两个变量为分页算法!切记*//*注:分页算法在该课程第12讲有涉及，且在servlet第4讲中有讲解*/v_begin number:=(pagenow-1)*pagesize+1;v_end number:=pagenow*pagesize;beginv_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语句并把返回值赋给rowsexecute immediate v_sql into myrows;--计算pagecountif--注意:pl/sql中没有'%'取模的写法，既:rows%pagecount为错误语句。此处需要用mod函数mod(myrows,pagesize)=0thenpagecount:=myrows/pagesize;elsepagecount:=myrows/pagesize+1;end if;--close p_cursor;end;

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

例外传递:案例:编写一个过程，接受雇员的编号，先是雇员姓名declarev_name emp.ename%typebeginselect ename into v_name from emp where empno=&no;dbms_output.put_line('该员工是:'||v_name);--例外处理exceptionwhen no_data_foundthendbms_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)isv_sal emp.sal%type;beginselect sal into v_sal where empno=num;casewhenv_sal<1000thenupdate emp set sal=sal+100 where empno=num;whenthenv_sal<2000update emp set sal=sal+200 where empno=num;end case;exceptionwhencase_not_foundthendbms_output.put_line('case语句没有与'||v_sal||'相匹配的条件');end;///问题:如果v_sal>2000则不会进入任何一个分支，此时出发该例外预定义例外cursor_already_open:当重新打开已经打开的游标时，会隐含地触发该例外案例:SQL> declarecursor emp_cursor is select ename,sal from emp;beginopen emmp_cursor;for emp_record1 in emp_cursorloopdbms_output.put_line(emp_record1.ename);end loop;exceptionwhencursor_already_openthendbms_output.put_line('游标已打开');end;/*注意:此处open语句已经打开游标，而for emp_record1 in emp_cursor又打开一次游标，则会触发此异常*/预定义例外dup_val_on_index:在唯一索引所对应的列表上插入重复的值，会隐含触发该例外案例:begininsert into dept values(10,'公关部','北京');exceptionwhendup_val_on_indexthendbms_output.put_line('在deptno列上不能出现重复值');end;/*注意:假设此处10号部门已经存在且不能重复*/预定义例外invaild_cursor:当试图在不合法的游标上执行操作时(无该游标存在或者游标未打开之类称为非法游标)，会触发该例外例如:视图从没有打开的游标提取数据，或是关闭没有打开的游标SQL> declarecursor emp_cursor is select ename,sal from emp;emp_record emp_cursor%rowtype;beginopen emp_cursor;--打开游标被注释fetch emp_cursor into emp_record;dbms_output.put_line(enp_record.ename);close emp_cursor;exceptionwheninvaild_cursorthendbms_output.put_line('游标未打开');end;/*注:将开启游标一句注释之后，查询游标中的值与关闭游标就为非法操作*/预定义例外:invalid_number:当输入的数据有错误则会触发该例外案例:beginupdate emp set sal=sal+'2b';exceptionwheninvalid_numberthendbms_output.put_line('输入数据有误');end;预定义例外:no_data_found:之前使用过，不再作解释与演示预定义例外:too_many_rows:当执行select into 语句时，如果返回值超过一行，则会触发该例外案例:SQL> declarev_ename emp.ename%type;beginselect ename into v_ename from emp;exceptionwhentoo_many_rows;thendbms_output.put_line('返回多个值');end;/*注意:v_ename只能接受1个值*/预定义例外:zero_divide:除以0的例外预定义例外:value_error:当执行赋值操作时，如果变量的长度不足以容纳实际数据，则触发该例外案例:SQL> declarev_ename varchar2(5);beginselect ename into v_ename from emp where empno=&no;dbms_output.put_line(v_ename);exceptionwhenvalue_errorthendbms_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;beginupdate emp sat sal=sal+130 where empno=num;ifsql%notfound--该语句表示没有更新操作thenraise sblyex1;--该语句表示触发该例外end if;exceptionwhensblyex1thendbms_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)