plsql存过声明游标_PLSQL(语法--异常处理--游标--存储过程--触发器--oracle分页)

《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;

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测试

分享到：

2011-04-08 20:34

浏览 900

分类:数据库

评论