黑马程序员 OraclePL-SQL编程

1.过程，函数，触发器是pl/sql编写
2.过程，函数，触发器在oracle中。
3.过程，函数可以在java程序中调用
(1)学习pl-sql的必要性
1.提高应用程序的运行性能
2.模块化的设计思想（分页的过程、订单的过程、转账的过程....）
3.减少网络的传输量
4.提高安全性
(2)pl-sql的缺点
1.移植性不好
创建或替换过程：
replace:表示如果有sp过程，就替换
sql>create or replace procedure sp is
begin 
insert into mytest values('韩顺平','m1234');
end;
调用存储过程:
(1)
sql>exec 过程名(参数1，参数2,...);
(2)
sql>call 过程名(参数1，参数2,...);
显示错误信息：
sql>show error;
编写规范：
(1)注释
单行注释 --
多行注释 /*.....*/
(2)标示符的命名规范
1.变量，v_作为前缀
2.常量，c_作为前缀
3.游标，_cursor作为后缀
4.例外，e_作为前缀
pl-sql块由三个部分组成：定义部分，执行部分，例外处理部分
如下所示：
declare(可选)
/*定义部分---定义常量、变量、游标、例外、复杂数据类型*/
begin
/*执行部分---要执行的pl-sql语句和sql语句*/
exception(可选)
/*例外处理部分---处理运行的各种错误*/
end;
打开或关闭输出选项：
sql>set serveroutput off;--关闭
sql>set serveroutput on;--打开
存储过程：
--输出员工姓名
eg:sql>declare 
v_enane varchar2(20);
begin
select ename into v_ename from emp where empno=&aa;
dbms_output.put_line('用户名和四：'||v_ename);
end;
/
--输出员工姓名和工资
sql>declare 
v_enane varchar2(20);
v_sal number(7,2);
begin
select ename,sal into v_ename,v_sal from emp where empno=&aa;
dbms_output.put_line('用户名四：'||v_ename||'工资：'||v_sal);
end;
/
--编号输错，处理异常
sql>declare 
v_enane varchar2(20);
v_sal number(7,2);
begin
select ename,sal into v_ename,v_sal from emp where empno=&aa;
dbms_output.put_line('用户名和是：'||v_ename||'工资：'||v_sal);
--异常处理
exception
when no_data_found then
dbms_output.put_line('朋友，你输入的编号有错');
end;
/
函数：
sql>create function sp_fun2(spName vharchar2) return number is yearSal number(7,2);
begin 
select sal*12+nvl(comm,0)*12 into yearSal from emp where ename=spName;
return yearSal;
end;
/
--在sqlplus中调用函数
sql> var income number;
sql>call sp_fun2('SCOTT') into:income;
sql>print income;
--在java程序中调用该函数
select sp_fun2('SCOTT') from dual;这样可以通过rs.getInt(1);的到返回结果。
包：
(1)创建包
--创建一个包sp_package
--声明该包有一个过程update_sal
--声明该包有一个函数annu_income
sql>create package sp_package is 
procedure update_sal(name varchar2,newsal number);
function annual_income(name varchar2) return number;
end;
/
(2)实现包体
sql>create or replace package body sp_package is
procedure 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
annual_salary number;
begin
select sal*12)nvl(comm,0) into annual_salary from emp;
where ename=name;
return annual_salary;
end;
end;
/
定义并使用变量：
(1)标量类型（scalar）存放单个数据
eg:--定义一个布尔变量，不能为空，初始值是false
declare v_valid boolean not null default false;
eg:--使用%type类型
语法：
变量名 表名.列名%type;
(2)复合类型(composite)存放多个值的变量
1.pl-spl记录(类似高级语言中的结构体)
eg:
sql>declare 
type emp_record_type is record(name emp.ename%type,salary emp.sal%type,title emp.job%type);
--定义一个sp_record变量,这个变量的类型是emp_record_type;
sp_record emp_record_type;
begin
select ename,sal,job into sp_resord from emp where empno=7788;
dbms_output.put_line('员工名：'||sp_resord.name);
end;
/
2.pl-sql表(类似高级语言中的数组，下标可以为负数，并且表元素的下标没有限制)
sql>declare 
--index by binary_integer 表示下标是整数
type sp_table_type is table of emp.ename%type index by binary_integer;
--定义了一个sp_table变量，变量的类型是sp_table_type;
sp_table sp_table_type;
begin
select ename into sp_table(-1) from emp where empno=7788;
dbms_output.put_line('员工名：'||sp_table(-1));
end;
/
3.嵌套表
4.varray
(3)参照类型(reference)
1.游标变量（ref cursor）
--输入部门好，并显示该部门所有员工姓名和他的工资
sql>declare
--定义游标类型
type sp_emp_cursor is ref cursor;
--定义一个游标变量
test_cursor sp_emp_cursor;
--定义变量
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
--执行
--把test_cursor 和一个select结合
open test_cursor for select ename,sal from emp;
--循环取出
loop
fetch test_cursor into e_ename,v_sal;
--判断test_cursor为空
exit when test_cursor%notfound;
dbms_output.put_line('名字:'||v_ename||'工资：'||v_sal);
end loop;
--关闭游标
close test_cursor;
end;
/
2.对象类型变量(ref obj_type)
(4)lob(large object)
标量的使用：
sql>declare 
c_tax_rate number(3,2):=0.03;
v_ename emp.ename%type;
v_sal emp.sal%type;
v_tax_sal number(7,2);
begin
--执行
select ename,sal into v_ename,v_sal from emp where enpno=&no;
--计算所得税
v_tax_sal:=v_sal*c_tax_rate;
--输出
dbms_output.put_line('姓名是：'||v_ename||'工资:'||v_sal||'交税：'|| v_tax_sal);
end;
/
循环控制语句null：
sql>declare
v_sal emp.sal%type;
v_ename emp.ename%type;
begin
select ename,sal into v_ename,v_sal from emp where empno=&no;
if v_sal<3000 then
update emp set comm=sal*0.1 where ename=v_ename;
else
null;
end if;
end;
java程序中调用有输入参数存储过程：
(1)定义存储过程
--in:表示这是一个输入参数，默认为in
--out:表示一个输出参数
create or replace procedure sp_pro(
spBookId in number,spbookName in varchar2,sppublishHouse in varchar2) is
begin
insert into book values(spBookId,spookName,sppublishHouse);
end;
/
)
(2)java程序中调用存储过程
CallableStatement cs=conn.prepareCall("{call sp_pro(?,?,?)}");
cs.setInt(1,10);
cs.setString(2,"笑傲江湖");
cs.setString(3,"人民出版社"); 
java程序中调用有输入和输出参数存储过程：
(1)定义存储过程
create or replace procedure sp_pro2(
spno in number,spName out varchar2
) is
begin
select ename into spName from emp where empno=spno;
end;
/
(2)java程序中调用有输出的存储过程
CallableStatement cs=conn.prepareCall("{call sp_pro(?,?)}");
//给第一?赋值
cs.setInt(1,7788);
//给第二个?赋值
cs.registerOutparameter(2,oracle.jdbc.OracleTypes.VARCHAR);
//执行
cs.exectue();
//取出返回值，要注意?顺序
String name=cs.getString(2);
System.out.println("7788的名字"+name);
有返回值的存储过程（列表[结果集]）
案例：编写一个过程，输入部门编号，返回该部门所有雇员信息。对该题分析如下：
由于oacle存储过程没有返回值，他的返回值都是通过out参数来替代的，列表同样也不例外，但由于是集合，所以不能用一
般的参数，必须要用pagkage了，所以要分两部分：
(1)建一个包。如下：
sql>create or replace package testpackage testpackage AS TYPE test_cursor is ref cursor;
end testpackage;
(2)创建存储过程
sql>create or replace procedure sp_pro
(spNo in number,p_cursor out testpackage.test_cursor) is
begin
open p_cursor for select * from emp where deptno=spNo;
end;
(3)Java程序调用
CallableStatement cs=ct.prepareCall("{call sp_pro(?,?)}");
//给?赋值
cs.setInt(1,10);
cs.registerOutParemeter(2,oracle.jdbc.OracleTeyps.CURSOR);
//执行
cs.execute();
//得到结果集
ResultSet rs=(ResultSet)cs.getObject(2);
while(rs.next()){
System.out.println(rs.getInt(1),"\t"+rs.getString(2));

}
自定义例外：
sql>create or replace procedure ex_test(spno number) is
--定义一个例外
myex exception;
begin
update emp set sal=sal+1000 empno=spNo;
--sql%notfound这是表示没有update
--raise myex;触发myex
if sql%notfound then
raise myex;
end if;
exception
when myex then
dbms_output.put_line(没有更新任何用户");
end;
视图：
--创建视图
sql>create or replace view myview as select emp.empno,emp.ename,dept.dname from emp,dept [with read only];
--查询
sql>select * from myview;
--删除视图
sql>drop view myview;

分页:
sql>select t1.*,rownum rn from (select * from emp) t1;
--返回第6条到10条记录
sql>select * from
(select t1.*,rownum rn from (select * from emp) t1 where rownum<=10)
where rn>=6;
--编写分页的存储过程
1.创建一个包,定义一个游标类型test_cursor
sql>create or replace package testpackage as type test_cursor is ref cursor;
end testpackage;
2.创建分页的过程
sql>create or replace produre fenye
(
tableName in varchar2,
pagesize in number,--一页显示几条
pageNow in number,--当前第几页
myrows out number,--总记录数
myPageCount out number,--总页数
p_cursor out testpackage.test_cursor--返回的记录集
) is
--定义部分
--定义sql语句，字符串
v_sql varchar2(10000);
--定义两个整数
v_begin number:=(pageNow-1)*Pagesize+1;
v_end number:=pageNow*Pagesize;
begin
--执行部分
v_sql:='select * from
(select t1.*,rownum rn from (select * from '||tableName||order by sal') t1 where rownum<='||v_end||')
where rn>='||v_begin;
--把游标和sql关联
open p_cursor for v_sql;
--计算myrows和myPageCount
--组织一个sql
v_sql:='select count(*) from'||tableName;
--执行sql,并把返回的值赋给myrows
execute immediate v_sql into myrows;
--计算myPageCount
if mod(myrows,Pagesize)=0 then
myPageCount:=myrows/Pagesizes;
else
myPageCount:=myrows/Pagesizes+1;
end if;
--关闭游标
close p_curosr;
end;
分页的三种方式：
1.根据ROWID来分
sql>select * from t_xiaoxi where rowid in(select rid from (select rownum rn,rid 
from (select rowid rid,cid from t_xiaoxi order by cid desc) where rownum<1000) where rn>9980) order by cid desc;
执行时间0.03秒
2.按分析函数来分：
sql>select * from (select t.*),row_number() over(order by cid desc) rk from
t_xiaoxi t) where rk<10000 and rk>9980;
执行时间1.01秒
3.按ROWNUM来分
sql>select * from(select t.*,rownum rn from (select * from t_xiaoxi order by cid desc) t where number<10000) where rn>9980;
执行时间0.1秒