《PL/SQL编程》?????????????????????????????
?/*procedural language/sql*/
--1、过程、函数、触发器是pl/sql编写的
--2、过程、函数、触发器是在oracle中的
--3、pl/sql是非常强大的数据库过程语言
--4、过程、函数可以在java程序中调用
--提高效率：优化sql语句或写存储过程
--pl/sql移植性不好
--IDE(Integration Develop Environment)集成开发环境
--命令规则：
--变量(variable)?????????? v_
--常量(constant)?????????? c_
--指针、游标(cursor)???????? _cursor
--例外、异常(exception)??? e_
--可定义的变量和常量：
? --标量类型：scalar
? --复合类型：composite??? --存放记录、表、嵌套表、varray
? --参照类型：reference
? --lob(large object)
?
《PL/SQL 基本语法》
--例：创建存储过程
create or replace procedure pro_add
is
begin
? insert into mytest values('韩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_ival number(4) :=100; --声明并初始化变量
? --v_dtm date;
? v_dtm syslogs.dtm%type; --取表字段类型
? v_content varchar(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,sal into v_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 , content
? into v_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);??
? end loop;
? commit;--提交事物
end;
select * from syslogs;
?
?
《PL/SQL 异常处理》
--PL/SQL异常处理：oracle内置异常，oracle用户自定义异常
declare
?? v_title logtypes.tid%type;
?? v_ival number(9,2);
?? --自定义的异常
?? ex_lesszero exception ;
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
??? --直接抛出异常
??? --raise ex_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(/*日志类型*/ tid in number ,
????????????????????????????? /*日志内容*/ content in varchar2 ,
????????????????????????????? /*错误码? */ i_ret out number ,
????????????????????????????? /*错误描述*/ s_ret out varchar2 )
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
? iRet number(4) ;
? sRet varchar2(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
??? iret number(4);
??? sret varchar2(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,'test log');
delete from logtypes where tid = 30;
update logtypes set title = 'test log' 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_name varchar2) return number;? --声明该包有一个函数 fun_test
end;
--包体案例:
create package body 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_name varchar2)
? return number is yearSal number(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_name emp2.name%type,t_sal emp2.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_sal emp2.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, --每页显示记录数
????????????????????????????????? pageNow in number,
????????????????????????????????? myRows out number,--总记录数
????????????????????????????????? myPageCount out number,--总页数
????????????????????????????????? p_cursor out testpackage.cursor_test)
is
? --定义sql语句 字符串
? v_sql varchar2(1000);
? --定义2个整数
? 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/pageSize;
? else
??? myPageCount:=myRows/pageSize+1;
? end if;
? --关闭游标
? --close p_cursor;
end;
--使用java测试
代码迷推荐解决方案：oracle存储过程,http://www.daimami.com/oracle-develop/177537.html