PLSQL语法及存储过程

pl/sql 的程序结构

  declare  
          说明部分（变量说明，光标申明 ， 例外说明 ）
  begin 
          语句序列 （DML语句）
  exception
          例外处理语句
   end;
   /

说明部分

   1,定义基本变量
             类型包括： char，varchar2，date ，number ， boolean ， long 
            * 举例： 
                         var1     char(15);    定义了一个字符  ，长度是15
                         married    boolean : = true ;    布尔值定义 需要 ：=   赋值
                         psal           number(7,2);  定义了一个数字，且有两位小数
   2，   引用型变量

--打印一些简单变量并对日期进行简单加减
declare
-- Local variables here
pnumber number(7,2);
--字符串变量
pname varchar2(20);
--日期
pdate date;
begin
-- Test statements here
pnumber :=1;
pname:= 'Tom';
pdate := sysdate;
dbms_output.put_line(pname);
dbms_output.put_line(pnumber);
dbms_output.put_line(pdate+2);
end;

关于plsql工具的使用：
https://www.cnblogs.com/luzhanshi/p/11017780.html

例子1： 循环打印出表中的数据项（id和name）

--认识游标（光标/结果集）
-- cursor   光标名[( 参数名 ， 参数类型[,参数名，参数类型])]  is    select  （查询语句）
/**
   光标的属性：
          1，%found   取到值为true    %notfound  取到值为fale     
**/
declare
   --定义一个光标 
   cursor cstaff  is select fname,fid from t_human_staff;
   --为光标定义一个变量来取值用   这里引用表中的类型，通过%来引用
   sta_name t_human_staff.fname%type;
   sta_id t_human_staff.fid%type;
begin
  --打开光标
  open cstaff;
  --循环loop
  LOOP
  --取一条记录放到定义好的变量中
  fetch cstaff into sta_name,sta_id;
  --思考 1，循环退出条件 2， fetch 不一定取出数据    when循环是在条件值到达某个值时跳出，下面的例子是大于200时跳出
  exit when sta_id > 200;
  dbms_output.put_line(sta_name||'的id是 ：'||sta_id);
  --结束循环
  end LOOP;
  --关闭光标
  close cstaff;
end;

例子2 : 布尔值打印

-- Created on 2020/9/11 by ADMINISTRATOR 
declare 
  f boolean :=true;
  sh number :=1000;
begin
  sh := sys.diutil.bool_to_int(f);
  dbms_output.put_line(sh);
end;

例子3：结合例子1，打印出游标的布尔属性


declare
   --定义一个光标 
   cursor cstaff  is select fname,fid from t_human_staff;
   --为光标定义一个变量来取值用   这里引用表中的类型，通过%来引用
   sta_name t_human_staff.fname%type;
   sta_id t_human_staff.fid%type;
   flag boolean;
   i number  :=111;
begin
  --打开光标
  open cstaff;
  --循环loop
  LOOP
  --取一条记录放到定义好的变量中
  fetch cstaff into sta_name,sta_id;
  flag := cstaff%found;
  exit when sta_id > 200;
  --对布尔值打印进行处理后
  i := sys.diutil.bool_to_int(flag);
  dbms_output.put_line('的flag是 ：'||i);
  --结束循环
  end LOOP;
  --关闭光标
  close cstaff;
end;

对于查询数据的时候有可能会造成overflow的异常

image.png
因为dbms_output 的默认值是20000

处理方式1：


image.png

处理方式2：


image.png

追加一条命令：
DBMS_OUTPUT.ENABLE (buffer_size=>null);

表示不限制数据大小

PLSQL 更新符合某个条件的数据

/**
   
*/
declare
   --定义一个光标 
   cursor cstaff  is select fname,fid from t_human_staff;
   --为光标定义一个变量来取值用   这里引用表中的类型，通过%来引用
   sta_name t_human_staff.fname%type;
   sta_id t_human_staff.fid%type;
   flag boolean;
   i number  :=111;
begin
  --打开光标
  open cstaff;
  --循环loop
  LOOP
  --取一条记录放到定义好的变量中
  fetch cstaff into sta_name,sta_id;
  flag := cstaff%found;
  exit when sta_id > 200  ;
       
  if sta_name = '111' then
       dbms_output.put_line('修改数据行是 ：'||sta_id);
       update t_human_staff set fname = '赵爱民' where fid = sta_id;
  else dbms_output.put_line('正常数据：'||sta_name);
  end if;
  
  --DBMS_OUTPUT.ENABLE (buffer_size=>null);
  --结束循环
  end LOOP;
  --关闭光标
  close cstaff;
  commit;
end;

针对游标的属性笔记

--光标属性笔记
declare 
  --定义一个游标  接收select查询语句查出的结果集，并定义两个变量来在plsql中逻辑处理
 cursor cc  is select fname,fid from t_human_staff;
 sta_name t_human_staff.fname%type;
 sta_id t_human_staff.fid%type;
begin
 --打开光标
 open cc;
 if cc%isopen then dbms_output.put_line('游标是开启的');
 elsif not cc%isopen then dbms_output.put_line('游标是关闭的');
 end if;
 --关闭游标
 close cc; 
 if cc%isopen then dbms_output.put_line('游标是开启1的');
 elsif not cc%isopen then dbms_output.put_line('游标是关闭1的');
 end if;
end;

%ROWCOUNT
　　这个属性记录了游标取过记录的行数，也可以理解为当前游标所在的行数。这个属性在循环的时候十分有效.

无论是显式游标还是隐式游标都会有四个属性分别是：%ISOPEN、%FOUND、%NOTFOUND、%ROWCOUNT 下

默认情况下，oracle数据库只允许在一个会话中，打开300个游标

image.png

异常处理

--常见异常处理
declare 
  --定义一个带参数的光标
 cursor cc(cno number)  is select fname ,fid from t_human_staff where fid=cno;
 sta_name t_human_staff.fname%type;
 sta_id t_human_staff.fid%type;
 num number;
 --自定义异常
 no_data exception;
begin
   --数据库中没有的数据查询并绑定到定义的属性中  
   --select fname into sta_name from t_human_staff where fe
   --多数据匹配异常
   --select fname into sta_name from t_human_staff t where t.fdepartmentid = 234;
   --被0除异常   
   --num := 2/0;
   --系统例外
   --num := 'abc';
   
   
   --自定义异常
   --打开游标
   open cc(123123);
   fetch cc into sta_name,sta_id;
   if cc%notfound then 
     --抛出异常
     raise no_data;
   end if;
   --关闭游标
   close cc;
 exception
   --未找到结果异常      no_data_found
   when no_data_found then  dbms_output.put_line('没找到数据');
    --多数据匹配异常     too_many_rows
   when too_many_rows then  dbms_output.put_line(' 多数据匹配异常');
   --被0除异常             zero_divide
   when zero_divide then  dbms_output.put_line('被0除异常');
   --系统异常：数据转换
   when value_error then  dbms_output.put_line('数据类型转换异常');  
    --自定义异常
   when no_data then  dbms_output.put_line('自定义异常：无结果');
   when others then dbms_output.put_line('其他异常');
   --系统遇到异常后跳到exception语句后    而close 游标的指令却没有执行，这个时候oracle会自动启动pemon（process monitor）进程   关闭游标，清理垃圾
end;

针对循环语句 wheil和when 小例子：

declare
n number:=0;

nn number:=0;
begin
while n<=5 loop
n:=n+1;
dbms_output.put_line('n='||n);
end loop;
dbms_output.put_line('---------');

loop
  exit when nn>5;
  nn :=nn+1;
  dbms_output.put_line('nn='||nn);
  end loop;

end;

/**
   loop循环  ： while   条件为false跳出循环    true继续loop循环     
                when    条件为true跳出循环     false继续循环
*/

存储过程

-- 用create  procedure  创建存储过程和存储函数、
/*
 创建或者替换一个存储过程  
 create [or replace]  procedure   过程名（参数列表）   as  PLSQL子程序体；
 
 as相当于plsql中的 declare
  
*/

create or replace procedure  sayhello
--说明部分
as 
begin 
    dbms_output.put_line('hello plsqls');
end;

/*
    执行后就创建成功了，可以再plsql工具中的Procedures文件夹下找到
*/


/*
   调用存储过程两种方式： 
   1,execute(缩写为： exec)      sayhello();   进行调用，格式为：    exec  函数名 ；  --注意分号结束
   2,在另外的plsql中调用   
            begin
                  --调用存储过程
                  sayhello();
                  sayhello();
                  sayhello();
              end;
*/

存储函数

--查询某个员工的年收入
create or replace function cxempsals(eno in number)
return number
as
  --月薪和奖金
  psal   emp.sal%type;
  pcomm  emp.comm%type;
begin
  select sal,comm into psal,pcomm from emp where empno=eno;
  --返回年收入
  return psal*12+nvl(pcomm,0);
end;
/*
存储过程和函数中输入和输出参数   in 和 out
一般来讲，存储过程：无返回值
          存储函数：有返回值
          二者都可以通过out 指定返回一个或者多个值
          存储过程本身不可以有返回值，但有了out之后就可以有返回值
*/

java调用 存储过程

package com.ysu.drgs.proxy.stafftest;

import java.sql.*;

import org.junit.Test;

import oracle.jdbc.driver.OracleTypes;
import oracle.jdbc.oracore.OracleType;




public class OracleTestProcedure {
    private static String driver = "oracle.jdbc.OracleDriver";
    private static String url = "jdbc:oracle:thin:@localhost:1521:orcl";
    private static String username ="";
    private static String pwd ="";  
    
    /**
     * 获取数据库驱动
     */
    static{
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }   
    }
    
    /**
     * 获取数据库连接
     * @return 
     */
    public static Connection getConn (){
        
        try {
            return DriverManager.getConnection(url, username, pwd);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    
    /**
     * 释放资源
     */
    public static void closeAll(Connection conn , Statement st ,ResultSet rs ){
        if(rs!=null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }finally{
                rs=null;
            }
        }
        if(st!=null){
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }finally{
                st=null;
            }
        }
        if(conn!= null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }finally{
                conn=null;
            }
        }
    }
    /**
     * java调用存储过程
     * @param args
     */
    
    @Test
    public  void test() {
        //Connection conn = getConn();
        //System.out.println(conn == null);
        Connection conn  = null;
        CallableStatement call = null; //访问存储过程
        try {
            //? 占位符        {call 存储过程的名字(?,?,?,?)}
            String sql = "{call cxempinfo(?,?,?,?)}";
            conn = getConn();
            call= conn.prepareCall(sql);
            //对in赋值
            call.setInt(1, 7839);
            //out输出参数需要声明   
            call.registerOutParameter(2, OracleTypes.VARCHAR);
            call.registerOutParameter(3, OracleTypes.NUMBER);
            call.registerOutParameter(4, OracleTypes.VARCHAR);
            //调用存储过程
            call.execute();
            
            String name = call.getString(2);
            double sal = call.getDouble(3);
            String job = call.getString(4);
            System.out.println(name);
            System.out.println(sal);
            System.out.println(job);

        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            closeAll(conn, call, null);
        }
    }
    
}
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 212,222评论 6赞 493
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 90,455评论 3赞 385
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 157,720评论 0赞 348
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 56,568评论 1赞 284
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 65,696评论 6赞 386
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 49,879评论 1赞 290
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 39,028评论 3赞 409
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 37,773评论 0赞 268
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 44,220评论 1赞 303
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 36,550评论 2赞 327
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,697评论 1赞 341
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 34,360评论 4赞 332
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 40,002评论 3赞 315
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,782评论 0赞 21
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,010评论 1赞 266
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 46,433评论 2赞 360
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 43,587评论 2赞 350