/*procedurallanguage/sql*/
--1、过程、函数、触发器是pl/sql编写的
--2、过程、函数、触发器是在oracle中的
--3、pl/sql是非常强大的数据库过程语言
--4、过程、函数可以在java程序中调用
--提高效率：优化sql语句或写存储过程
--pl/sql移植性不好
--IDE(IntegrationDevelopEnvironment)集成开发环境
--命令规则：
--变量(variable) v_
--常量(constant) c_
--指针、游标(cursor) _cursor
--例外、异常(exception) e_
--可定义的变量和常量：
--标量类型：scalar
--复合类型：composite
--存放记录、表、嵌套表、varray
--参照类型：reference
--lob(largeobject)
《PL/SQL基本语法》
--例：创建存储过程
create or replace procedure pro_add
is
begin
insert into mytestvalues('韩xx','123');
end;
exec pro_add--调用
--查看错误信息
show error;
--调用过程
exec 过程(c1,c2,...);
call 过程(c1,c2,...);
--打开/关闭输出选项
set serveroutput on/off
--输入
&
--块结构示意图
declare--定义部分，定义常量、变量、游标、例外、复杂数据类型
begin--执行部分，执行pl/sql语句和sql语句
exception--例外处理部分，处理运行的各种错误
end;--结束
--《实例演示》
declare v_ivalnumber(4):=100;--声明并初始化变量
--v_dtm date;
v_dtm syslogs.dtm%type;--取表字段类型
v_contentvarchar(512);
begin
v_ival:=v_ival*90;--赋值运算
insert into syslogs values(seq_syslogs.nextval,10,sysdate,'v_ival='||v_ival,user);--数据库存储
dbms_output.put_line('v_ival'||v_ival);
select count(*) into v_ival from syslogs;
--使用select查询赋值
--select ename,salintov_name,v_sal from emp where empno=&aa;
insert into syslogs values(seq_syslogs.nextval,10,sysdate,'日志条数='||v_ival,user);
dbms_output.put_line('日志条数'||v_ival);--获取日志序号==11的日志时间和日志内容
select dtm,contentintov_dtm,v_content from syslogs where logid=14;
insert into syslogs values(seq_syslogs.nextval,'10',sysdate,'v_dtm='||v_dtm||'v_content='||v_content,user);
dbms_output.put_line('v_dtm='||v_dtm||'v_content='||v_content);--修改日志序号=11的日志记录人
update syslogs
set whois='PL/SQL.'||v_ival
where logid=14;
--delete syslogs where logid=15;
--分支流程控制
if v_ival>50 then dbms_output.put_line('日志需要清理了~');
else dbms_output.put_line('日志空间正常！');
end if;
--Loop循环
v_ival:=0;
loop
exit when v_ival>3;--循环体
v_ival:=v_ival+1;
dbms_output.put_line('loop循环：'||v_ival);
end loop;
--While循环
v_ival:=0;
while v_ival<4 loop --循环体
v_ival:=v_ival+1;
dbms_output.put_line('while循环：'||v_ival);
end loop;
--For循环
for v_count in reverse 0..4 loop --reverse递减
dbms_output.put_line('for循环：'||v_count);
endloop;
commit;--提交事物
end;
select * from syslogs;
《PL/SQL异常处理》
--PL/SQL异常处理：
oracle内置异常，oracle用户自定义异常
declare
v_title logtypes.tid%type;
v_ivalnumber(9,2);
--自定义的异常
ex_lesszeroexception;
begin
--select title into v_title
--from logtypes --; too_many_rows
--where tid=30; --NO_DATA_FOUND 异常
v_ival:=12/-3;
if v_ival<0 then
--直接抛出异常
--raiseex_lesszero;
--使用系统存储过程抛出异常
raise_application_error(/*错误代码，-20000~-20999*/-20003,/*异常描述*/'参数不能小于0！');
end if;
commit;
exception--异常处理代码块
when no_data_found then
dbms_output.put_line('发生系统异常：未找到有效的数据！');
when too_many_rows then
dbms_output.put_line('发生系统异常：查询结果超出预期的一行！');
when ex_lesszero then
dbms_output.put_line('发生用户异常：数值不能为负！'||sqlcode||'异常描述：'||sqlerrm);
when others then--other 例如Exception
rollback;
dbms_output.put_line('发生异常！'||sqlcode||'异常的描述：'||sqlerrm);
end;
《PL/SQL游标的使用》
declare
--游标的声明
cursor myCur is
select tid,title from logtypes;
--定义接收游标中的数据变量
v_tid logtypes.tid%type;
v_title logtypes.title%type;
--通过记录来接受数据
v_typercd myCur%rowtype;
begin
--打开游标
open myCur;
--取游标中的数据
loop
--遍历游标中的下一行数据
fetch myCur into v_tid,v_title;
--检测是否已经达到最后一行
exit when myCur%notfound;
--输出游标中的数据
dbms_output.put_line('读取tid='||v_tid||'title='||v_title);
end loop;
--关闭游标
close myCur;
--打开游标
open myCur;
loop
fetch myCur into v_typercd;
exit when myCur%notfound;
dbms_output.put_line('--//读取tid='||v_typercd.tid||'title='||v_typercd.title);
end loop;
--关闭游标
close myCur;
--for循环游标
for tmp_record in myCur
loop
dbms_output.put_line('++//读取tid='||tmp_record.tid||'title='||tmp_record.title);
end loop;
end;
《PL/SQL存储过程★》
--可以声明入参in，out表示出参，但是无返回值。
create or replace procedure prc_writelog(/*日志类型*/tidinnumber,
/*日志内容*/contentinvarchar2,
/*错误码*/i_retoutnumber,
/*错误描述*/s_retoutvarchar2)
is
begin
insert into syslogs values(seq_syslogs.nextval,tid,sysdate,content,user);
commit;
i_ret:=1;
s_ret:='记录日志成功！';
exception
when others then
rollback;
i_ret:=-1;
s_ret:='记录日志失败：'||sqlerrm;
end;
--测试
declare
iRetnumber(4);
sRetvarchar2(128);
begin
prc_writelog(10,'测试存储过程',iRet,sRet);
dbms_output.put_line('iRet:'||iRet||'sRet'||sRet);
end;
select * from syslogs;
《PL/SQL触发器》
--触发器是一种基于数据库特定事件的由数据库自动执行的pl/sql块
--触发的事件源：
database【启动、停止、用户联机...】
表名【insert/update/delete】
--触发时机
before/after
--语句级、行级（需要知道数据,对数据库运行速度有影响）
create or replace trigger tri_logtypes
after insert or update or delete--在所有的表的事件发生后执行
on logtypes for each row--行级（:new,:old）
declare
iretnumber(4);
sretvarchar2(128);
begin
--不要有事物的管理
--:new新数据记录型
--:old原有的数据记录型
--prc_writelog(10,'触发器执行了！',iret,sret);
if inserting then
insert into syslogs values(seq_syslogs.nextval,10,sysdate,'触发器执行添加数据！',user);
elsif updating then
if:new.title<>:old.title then
raise_application_error(-20001,'不允许修改日志类型名称数据！');--抛出异常
end if;
insert into syslogs values(seq_syslogs.nextval,10,sysdate,'触发器执行更新数据！',user);
elsif deleting then
raise_application_error(-20001,'不允许删除表中的数据！');
insert into syslogs values(seq_syslogs.nextval,10,sysdate,'触发器执行删除数据！',user);
end if;
end;
--test!
insert into logtypes values(30,'testlog');
delete from logtypes where tid=30;
update logtypes set title='testlog' where tid=30;
select * from syslogs order by dtm desc;
select * from logtypes;
《案例》
--创建表
create table emp2(name varchar2(30),sal number(8,2));
insert into emp2 values('simple',99999);
insert into emp2 values(&a,&b);
--存储过程案例：
--修改员工工资
create or replace procedure pro_input(t_name in varchar2,t_sal in number)
is
begin
update emp2 set sal=t_sal where name=t_name;
end;
--Test!
declare
begin
pro_input('simple',2000);
end;
select * from emp2;
--函数案例：
create or replace function fun_test(t_name varchar2)
return number is yearSal number(7,2);
begin
select sal*12 into yearSal from emp2 where name=t_name;
return yearSal;
end;
--包案例：
create package pac_test is--创建一个包pac_test
procedure pro_input(t_name varchar2,t_sal number);--声明该包有一个过程pro_input
function fun_test(t_namevarchar2)
return number;--声明该包有一个函数fun_test
end;
--包体案例:
create packagebody pac_test
is
procedure pro_input(t_name in varchar2,t_sal in number)
is
begin
update emp2 set sal=t_sal where name=t_name;
end;
function fun_test(t_namevarchar2)
return number is yearSalnumber(7,2);
begin
select sal*12 into yearSal from emp2 where name=t_name;
return yearSal;
end;
end;
--调用包中的函数或过程
call pac_test.pro_input('summer',1000);
call pac_test.fun_test
select pac_test.fun_test('simple') from dual;
--案例：
select * from emp2;
--下面以输入员工工号，显示雇员姓名、工资、个人所得税--税率（0.03）。
declare
c_tax_rate number(3,2):=0.03;--常量，税率
--v_name varchar2(30);
v_name emp2.name%type;
--v_sal number(8,2);
v_sal emp2.sal%type;
v_tax_sal number(8,2);
begin
--执行
select name,sal into v_name,v_sal from emp2 where name=&na;
--计算所得税
v_tax_sal:=v_sal*c_tax_rate;
--输出
dbms_output.put_line('姓名：'||v_name||'工资'||v_sal||'交税'||v_tax_sal);
end;
--pl/sql记录实例
declare
--定义一个pl/sql记录类型emp_record_type，类型包含2个数据，t_name,t_sal
type emp_record_type is record(t_nameemp2.name%type,t_salemp2.sal%type);
--定义一个record_test变量，类型是emp_record_type
record_test emp_record_type;
begin
select name,sal into record_test from emp2 where name='simple';
dbms_output.put_line('员工工资:'||record_test.t_sal);
end;
--pl/sql表实例
declare
--定义了一个pl/sql表类型emp_table_type该类型是用于存放emp.name%type元素类型的数组
--index by binary_integer 下标是整数
type emp_table_type is table of emp2.name%type index by binary_integer;
--定义一个table_test变量
table_test emp_table_type;
begin
--table_test(0)下标为0的元素
select name into table_test(0) from emp2 where name='summer';
dbms_output.put_line('员工:'||table_test(0));
end;
--案例
--显示该部门的所有员工和工资
declare
--定义游标类型emp_cursor
type emp_cursor is ref cursor;
--定义一个游标变量
cursor_test emp_cursor;
--定义变量
v_name emp2.name%type;
v_salemp2.sal%type;
begin
--执行
--把cursor_test和一个select结合
open cursor_test for
select name,sal from emp2;--循环取出loop--fetch取出游标给v_name,v_sal
fetch cursor_test into v_name,v_sal;
--判断工资
if v_sal<1000 then
update emp2 set sal=v_sal+1000 where sal=v_sal;
end if;
--判断cursor_test是否为空
exit when cursor_test%notfound;
dbms_output.put_line('姓名：'||v_name||'薪水：'||v_sal);
end loop;
end;
select * from emp2;
--《分页》案例：
--建表
drop table book;
create table book(bookId number(5),
bookName varchar2(50),
publishHouse varchar2(50));
--编写过程
create or replace procedure pro_pagination(t_bookId in number,
t_bookName in varchar2,
t_publishHouse in varchar2)
is
begin
insert into book values(t_bookId,t_bookName,t_publishHouse);
end;
--在java中调用
--select * from book;
--insert into book values(11,'流星','蝴蝶');
--commit;
--有输入和输出的存储过程
create or replace procedure pro_pagination2(i_id in number,
o_name out varchar2,
o_publishHouse out varchar2)
is
begin
select bookName,publishHouse into o_name,o_publishHouse from book where bookId=i_id;
end;
--Test!
declare
err book.bookname%type;
err2 book.publishhouse%type;
begin
pro_pagination2(10,err,err2);
dbms_output.put_line(err||''||err2);
end;
--返回结果集的过程
--1、创建一个包
create or replace package testpackage
as
type cursor_test is ref cursor;
end testpackage;
--2、建立存储过程
create or replace procedure pro_pagination3(o_cursor out testpackage.cursor_test)
is
begin
open o_cursor for
select * from book;
end;
--3、如何在java中调用
--Test!
declare
err testpackage.cursor;
begin
pro_pagination2(10,err);
dbms_output.put_line(err);
end;
<Oracle的分页>
select t1.*,rownum rn from(select * from emp)t1;
select t1.*,rownum rn from(select * from emp)t1 where rownum<=10;
--在分页的时候，可以把下面的sql语句当做一个模板使用
select * from(select t1.*,rownum rn from(select * from emp)t1 where rownum<=10)where rn>=6;
--开发一个包
--1、创建一个包
create or replace package testpackage
as
type cursor_test is ref cursor;
end testpackage;
--开始编写分页的过程
create or replace procedure fenye(tableName in varchar2,
pageSize in number,--每页显示记录数
pageNowinnumber,
myRowsoutnumber,--总记录数
myPageCountoutnumber,--总页数
p_cursor out testpackage.cursor_test)
is
--定义sql语句字符串
v_sqlvarchar2(1000);
--定义2个整数
v_beginnumber:=(pageNow-1)*pageSize+1;
v_endnumber:=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/pageSize;
else myPageCount:=myRows/pageSize+1;
end if;
--关闭游标
close p_cursor;
end;
--使用java测试