`
simple1024
  • 浏览: 74225 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

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;


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

 

分享到:
评论
14 楼 likekelly 2011-04-19  
收藏一下,等闲下来看。
13 楼 hua472597443 2011-04-18  
标记,不错。
12 楼 simple1024 2011-04-14  
恩,有部分是那,看来你是看过视频了,魔乐和韩顺平的我都看了。
11 楼 Summerisy 2011-04-13  
韩顺平oracle的那个吗?
10 楼 alexandrae2008 2011-04-12  
收录下。。。感谢楼主
9 楼 竹隐江南 2011-04-11  
好,回去复习下。
8 楼 simple1024 2011-04-11  
呵呵,代码看起来是有点费力。我也是从我的笔记中抽出的给发上来了。
7 楼 coconut_zhang 2011-04-10  
太很给力。。。。。。
6 楼 ddl1st 2011-04-09  
你代码应该格式化一下。
5 楼 simple1024 2011-04-09  
再有总结我还会给大家分享的
4 楼 77tt77 2011-04-09  
总结的不错。
3 楼 angel243fly 2011-04-08  
收藏,这东西迟早有用
2 楼 hamber 2011-04-08  
mark ,有用哦。记下先!
1 楼 lwf2510 2011-04-07  
LZ还挺细心的,总结很详细。

相关推荐

    Oracle-PLSQL-编程语法详解-触发器.docx

    触发器与存储过程相似,都是包含PL/SQL代码的逻辑单元,但触发器的执行是隐式的,由特定的数据库操作(如INSERT、UPDATE或DELETE)触发,而不是由用户直接调用。 在Oracle中,触发器分为以下几种类型: 1. **DML...

    福建省电力公司oracle培训教材--PLSQL之游标与异常处理

    福建省电力公司oracle培训教材--PLSQL之游标与异常处理

    plsql---语法

    PL/SQL,全称为Procedural Language/Structured Query Language,是Oracle数据库提供的一个编程环境,主要用于管理和操作数据库。它结合了SQL(结构化查询语言)的数据库查询功能和过程式编程语言的特点,使得开发者...

    plsql-vf-dbfodbc.rar

    标题 "plsql-vf-dbfodbc.rar" 暗示了这个压缩包与PL/SQL(Oracle数据库的编程环境)以及Visual FoxPro(VFP)数据库系统有关,特别是涉及到在PL/SQL环境中通过ODBC(Open Database Connectivity)驱动程序访问DBF...

    PLSQL教材--语法手册.doc

    在PL/SQL中,你可以定义变量、控制流程、处理异常,以及通过存储过程和函数来执行复杂逻辑。存储过程和函数可以提高性能,因为它们只需编译一次,而执行多次。PACKAGE则提供了更好的代码管理和复用性,而触发器允许...

    plsql-oracle-instantclient-x86-64-11.2.0.1.0

    1. "plsql":这是对PL/SQL的简称,Oracle数据库的一种过程式语言,用于编写存储过程、函数、触发器等。 2. "x86-64":表明该版本是为64位(AMD64或Intel 64)架构的计算机设计的。 3. "11.2.0.1.0":这是Oracle...

    PLSQL--ORACLE连接工具

    2. **对象浏览器**:该功能允许用户以树形结构查看数据库中的所有对象,包括表、视图、存储过程、函数、触发器、包等,便于管理和操作。 3. **SQL编辑器**:提供了一个强大的SQL编辑器,支持智能提示、语法高亮、...

    PLSQL笔记-从hello word到触发器,包,游标高级应用

    PLSQL,全称为Procedural Language/SQL,是Oracle数据库提供的结构化查询语言扩展,它结合了SQL(Structured Query Language)的查询功能和过程性编程语言的控制结构,用于开发在Oracle环境中运行的应用程序。...

    plsql教程大全(语法入门、操作plsql、存储过程、函数、触发器、游标、包、返回类型)

    语法入门、操作plsql、存储过程、函数、触发器、游标、包、返回类型;一个execle表轻松搞定。

    plsql资料--plsql资料

    这个压缩包“plsql.rar”包含了多个文档,详细讲述了PLSQL的相关知识点,包括其书写规则、语法、存储过程以及异常处理。 1. **PLSQL的书写及语法**: PLSQL的基本结构包括声明部分(DECLARE)、执行部分(BEGIN......

    PLSQL程序设计-存储过程函数触发器

    ### PL/SQL程序设计——存储过程、函数与触发器 #### 概述 PL/SQL是一种专门为Oracle数据库设计的过程化语言,它结合了SQL的数据操纵功能和传统编程语言的流程控制功能,使得开发者能够在数据库环境中编写更为复杂...

    plsql7-oracle含注册码

    在Oracle数据库环境中,PL/SQL是一种过程化语言,结合了SQL和传统的编程语言元素,如变量、条件语句、循环和异常处理。它主要用于创建存储过程、函数、触发器等数据库对象,以实现复杂的业务逻辑。 PL/SQL ...

    Oracle+PLSQL+语法手册 全面 详细 chm

    PLSQL由声明部分、执行部分和异常处理部分组成,支持变量、常量、过程、函数、游标等编程元素。 三、PLSQL语法 1. 声明段:声明变量、常量、游标、记录类型等。 2. 执行段:包含SQL语句和PLSQL控制结构,如循环...

    oracle函数大全与存储过程语法-中文.rar

    `oracle存储过程.chm`和`存储过程语法.doc`可能详细解释了创建、修改和执行存储过程的语法,以及如何处理输入/输出参数和异常。 3. **Oracle 8i9i数据库基础**: - `Oracle8i9i数据库基础.doc`可能包含了Oracle 8i...

    oracle触发器调用存储过程

    ### Oracle触发器调用存储过程 #### 知识点概览 1. **触发器基本概念** 2. **存储过程简介** 3. **触发器如何调用存储过程** 4. **示例代码详解** 5. **注意事项** 6. **自主事务(Autonomous Transaction)** 7. ...

    PLSQL教程-从入门到精通

    开发者可以使用EXCEPTION部分来捕获和处理异常。 **5.4 在PL/SQL中使用SQLCODE, SQLERRM** 这两个伪列分别用于获取异常代码和异常消息。 #### 六、存储函数和过程 存储过程和函数是PL/SQL中最常用的对象类型之一...

    PLSQL 存储过程 游标使用

    PLSQL 存储过程 语法

    Oracle PLSQL语法大全及实例讲解.pdf

    它扩展了标准SQL,提供了声明变量、控制流程结构(如条件语句和循环)、处理异常以及与数据交互的能力。 1. **语句块**: - 语句块是PL/SQL的基本执行单元,由`DECLARE`、`BEGIN`、`EXCEPTION`和`END`关键字构成。...

    Oracle PLSQL编程-经典试题

    里面有orcale plsql经典试题,视图,存储过程,游标,包,异常处理。

    plsql--编程进阶

    PL/SQL (Procedural Language for SQL) 是Oracle为解决SQL语言在处理复杂逻辑时的局限性而推出的一种过程化编程语言。它将SQL的强大数据处理能力和传统过程化编程语言的控制结构结合起来,使得开发人员能够编写出...

Global site tag (gtag.js) - Google Analytics