本文最后更新于：2 年前

PLSQL学习

一、程序结构

declare 
  
begin
  
end;

二、赋值

直接赋值

:=
语句赋值

select x into y from z;

-- 打印人员个人信息，包括：姓名、薪水、地址
declare 
  -- 姓名
  v_name varchar2(20) := '张三';
  -- 薪水
  v_sal number;
  -- 地址
  v_addr varchar2(200);
begin
  -- 直接赋值
  v_sal := 500;

  -- 语句赋值
  select '北京' into v_addr from dual;

  -- 打印输出
  dbms_output.put_line('姓名：' || v_name || ', 薪水：' || v_sal || ', 地址：' || v_addr);

end;

普通变量

-- 打印人员个人信息，包括：姓名、薪水、地址
declare 
  -- 姓名
  v_name varchar2(20) := '张三';
  -- 薪水
  v_sal number;
  -- 地址
  v_addr varchar2(200);
begin

  -- 语句赋值
  SELECT ename, sal into v_name, v_sal from emp where empno = 7839;

  -- 打印输出
  dbms_output.put_line('姓名：' || v_name || ', 薪水：' || v_sal || ', 地址：' || v_addr);

end;

引用型变量

-- 打印人员个人信息，包括：姓名、薪水、地址
declare 
  -- 姓名
  v_name emp.ename%type;
  -- 薪水
  v_sal emp.sal%type;
  -- 地址
  v_addr varchar2(200);
begin

  -- 语句赋值
  SELECT ename, sal into v_name, v_sal from emp where empno = 7839;

  -- 打印输出
  dbms_output.put_line('姓名：' || v_name || ', 薪水：' || v_sal);

end;

记录型变量

-- 打印人员个人信息，包括：姓名、薪水、地址
declare 
  -- 记录型变量
  v_emp emp%rowtype;

begin

  -- 语句赋值
  SELECT * into v_emp from emp where empno = 7839;

  -- 打印输出
  dbms_output.put_line('姓名：' || v_emp.ename || ', 薪水：' || v_emp.sal);

end;

三、流程控制

1. if else

begin
	if 条件 then 执行1
	 elseif 条件2 then 执行2
	 else 执行3
	end if;
end;

-- 判断emp表中记录是否超过20条，10-20之间，或者10条以下
declare
   -- 声明变量接收emp中的数量
   v_count number;

begin
   select count(1) into v_count from emp;
   if v_count > 20 then
      dbms_output.put_line('emp表中的记录超过了20条，为' || v_count || '条');
   elsif v_count >= 10 then
      dbms_output.put_line('emp表中的记录超过了10-20条之间，为' || v_count || '条');
   else
      dbms_output.put_line('emp表中的记录为10条以下，为' || v_count || '条');
   end if;

end;

2. when

-- 打印数字1-10
declare
   -- 声明循环变量
   v_num number := 1;

begin
   loop
   
   exit when v_num > 10;
   
   dbms_output.put_line(v_num);
   
   -- 循环变量的自增
   v_num := v_num + 1;
   
   end loop;

end;

四、游标

1. 概念

用于存储一个查询返回的多行数据（结果集，类似于java的JDBC连接返回的ResultSet集合），通过遍历游标，可以逐行访问处理该结果集的数据。

游标的使用方式：声明–>打开–>读取–>关闭

2. 语法

1) 声明

cursor 游标名[(参数列表)] is 查询语句;

2) 打开

open 游标名;

3) 取值

fetch 游标名 into 变量列表;

4) 关闭

close 游标名;

-- 使用游标查询emp表中所有员工的姓名和工资，并将其依次打印出来。
declare
   -- 声明游标
   cursor c_emp is select ename, sal from emp;
   
   -- 声明变量接收游标中的数据
   v_ename emp.ename%type;
   v_sal emp.sal%type;

begin

   -- 打开游标
   open c_emp;
   
   -- 遍历游标
   loop
   
   -- 获取游标中的数据
   fetch c_emp into v_ename, v_sal;
   
   -- 退出循环条件
   exit when c_emp%notfound;
   
   dbms_output.put_line(v_ename || ' - ' || v_sal);
   
   end loop;
   
   -- 关闭游标
   close c_emp;

end;

带参数的游标

-- 使用游标查询并打印某部分的员工的姓名和薪资，部门编号为运行时手动输入。
declare
   -- 声明游标
   cursor c_emp(v_deptno emp.deptno%type) is select ename, sal from emp where deptno = v_deptno;
   
   -- 声明变量接收游标中的数据
   v_ename emp.ename%type;
   v_sal emp.sal%type;

begin

   -- 打开游标
   open c_emp(10);
   
   -- 遍历游标
   loop
   
   -- 获取游标中的数据
   fetch c_emp into v_ename, v_sal;
   
   -- 退出循环条件
   exit when c_emp%notfound;
   
   dbms_output.put_line(v_ename || ' - ' || v_sal);
   
   end loop;
   
   -- 关闭游标
   close c_emp;

end;

五、存储过程

1. 概念

2. 语法

create or replace procedure 过程名称[(参数列表)] is
begin

end [过程名称]

3. 无参存储过程

create or replace procedure p_hello as -- is和as可以互用
-- 声明变量
begin
dbms_output.put_line('hello, world');
end p_hello;

-- 调用
begin
  -- plsql调用存储过程
  p_hello;
end;

-- sqlplus窗口中调用
exex p_hello;

4. 带输入参数的存储过程

-- 查询并打印某个员工(如7839号员工)的姓名和薪水
-- 存储过程：要求，调用的时候传入员工编号，自动控制台打印
create or replace procedure p_querynameandsal(i_empno in emp.empno%type) as
-- 声明变量
v_name emp.ename%type;
v_sal emp.sal%type;
begin
  -- 查询
  select ename, sal into v_name, v_sal from emp where empno = i_empno;
  
  dbms_output.put_line(v_name || '_' || v_sal);

end p_querynameandsal;

-- 调用
-- Created on 2022/08/17 by CHRIS 
declare 
  -- Local variables here
  i integer;
begin
  -- Test statements here
  p_querynameandsal(7839);
end;

5. 带输出参数的存储过程

-- 查询并打印某个员工(如7839号员工)的姓名和薪水
-- 存储过程：要求，调用的时候传入员工编号，自动控制台打印
create or replace procedure p_querysal_out(i_empno in emp.empno%type, o_sal out emp.sal%type) as
-- 声明变量
v_name emp.ename%type;
v_sal emp.sal%type;
begin
  -- 查询
  select sal into v_sal from emp where empno = i_empno;

end p_querysal_out;

1
2

-- 调用

-- 输入员工号查询某个员工(7839号员工)的信息，要求：将薪水作为返回值输出，给调用的程序使用。
create or replace procedure p_querysal_out(i_empno in emp.empno%type, o_sal out emp.sal%type) as
begin
  -- 查询
  select sal into o_sal from emp where empno = i_empno;

end p_querysal_out;

-- 调用
-- Created on 2022/08/17 by CHRIS 
declare 
  -- 声明变量接收存储过程中输出的参数
  v_sal emp.sal%type;
begin
  -- Test statements here
  p_querysal_out(7839, v_sal);
  dbms_output.put_line(v_sal);
end;

备份


create or replace procedure sptDatePro( --存储过程名称
                                       v_spt_date_st in test02.spt_date%TYPE, --起始日期
                                       v_spt_date_ed in test02.spt_date%TYPE, --终止日期
                                       v_spt_date    out test02.spt_date%TYPE,
                                       v_code1       in out test02.code1%TYPE,
                                       v_code2       in out test02.code2%TYPE,
                                       v_code3       in out test02.code3%TYPE,
                                       v_num         out test02.num%TYPE) is
-- 申明变量
declare  
   v_currdate date; --当前日期
   v_ymd      date; --开始日期
   v_endymd   date; --结束日期
begin
  /*****程序变量******/

  /*****程序变量******/
  -- set v_currdat = to_date(I_DATE, 'YYYYMMDD');  --数据日期
  v_ymd    := to_date(v_spt_date_st, 'YYYYMMDD'); --开始日期
  v_endymd := to_date(v_spt_date_ed, 'YYYYMMDD'); --结束日期

  print v_ymd;
  print v_endymd;

  /*****执行体******/
  while v_ymd <= v_endymd insert into test02( --表名
               spt_date,
               code1,
               code2,
               code3,
               num)
    select v_ymd, v_code1, v_code2, v_code3, int(rand() * 100)
           from sysibm.dual; set v_ymd = v_ymd + 1; 
   end loop;
   commit;
    /*****执行逻辑结束******/
  -- set s_stepds = '结束';
  -- call SCM_ITF.sp_pb_log('done', s_procds, s_datadt, null, s_stepds, s_steno);
  -- commit;
end;

CREATE OR REPLACE PROCEDURE query_emp (                                
  v_emp_no          IN      emp.empno%TYPE,              
  v_emp_name          OUT      emp.ename%TYPE,              
  v_emp_sal          OUT      emp.sal%TYPE,              
  v_emp_comm          OUT      emp.comm%TYPE)              
IS                                                         
BEGIN                                
  SELECT ename,sal,comm INTO v_emp_name,v_emp_sal,v_emp_comm FROM emp WHERE empno = v_emp_no;                              
END query_emp;                                


SELECT t.* FROM  emp t;


begin
  -- Call the procedure
  query_emp(v_emp_no => :v_emp_no,
            v_emp_name => :v_emp_name,
            v_emp_sal => :v_emp_sal,
            v_emp_comm => :v_emp_comm);
end;


declare
  I integer;
begin
  Dbms_Output.put_line('hello, world');
end;

create table test02(
 spt_date varchar2(10),
 code1 varchar2(2),
 code2 varchar2(2),
 code3 varchar2(2),
 num number(10,0)
);

create or replace procedure SCM_ITF.dataPro ( --存储过程名称
    IN "I_DATE" VARCHAR(8) --起始日期
) 
is
begin
    /*****程序变量******/
    declare v_currdate date; --当前日期
    declare v_ymd date; --开始日期
    declare v_endymd date;  --结束日期
    /*****程序变量******/
    set v_currdat = to_date(I_DATE, 'YYYYMMDD');  --数据日期
    set v_ymd = to_date(year(v_currdate)||'-01-01', 'YYYYMMDD');  --开始日期
    set v_endymd = to_date(year(v_currdate)||'-12-31', 'YYYYMMDD');  --结束日期

    /*****执行体******/
    while v_ymd <= v_endymd
    do
        insert into scm_app.t_tabname ( --表名
            a
            , b
            , c
            , d
        )
        select 
            '001'
            , 'usd'
            , v_ymd
            , 0
            , null
        from sysibm.dual
        ;
        set v_ymd = v_ymd + 1 day;
    end while;
    commit;
    /*****执行逻辑结束******/
    set s_stepds = '结束';
    call SCM_ITF.sp_pb_log('done', s_procds, s_datadt, null, s_stepds, s_steno);
    commit;
end@


2022-01-01


create or replace procedure sptDatePro ( --存储过程名称
    v_spt_date_st in test02.spt_date%TYPE, --起始日期
    v_spt_date_ed in test02.spt_date%TYPE, --终止日期
    v_spt_date out test02.spt_date%TYPE,
    v_code1 in out test02.code1%TYPE,
    v_code2 in out test02.code2%TYPE,
    v_code3 in out test02.code3%TYPE,
    v_num out test02.num%TYPE
) 
is
declare 
    v_currdate date; --当前日期
    v_ymd date; --开始日期
    v_endymd date;  --结束日期
begin
    /*****程序变量******/

    /*****程序变量******/
    -- set v_currdat = to_date(I_DATE, 'YYYYMMDD');  --数据日期
    v_ymd := to_date(v_spt_date_st, 'YYYYMMDD');  --开始日期
    v_endymd := to_date(v_spt_date_ed, 'YYYYMMDD');  --结束日期

    print v_ymd;
    print v_endymd;

    /*****执行体******/
    while v_ymd <= v_endymd
        insert into test02 ( --表名
            spt_date
            , code1
            , code2
            , code3
            , num
        )
        select 
            v_ymd
            , v_code1
            , v_code2
            , v_code3
            , int(rand()*100)
        from sysibm.dual
        ;
        set v_ymd = v_ymd + 1;
    end loop;
    commit;
    /*****执行逻辑结束******/
    -- set s_stepds = '结束';
    -- call SCM_ITF.sp_pb_log('done', s_procds, s_datadt, null, s_stepds, s_steno);
    -- commit;
end sptDatePro;


select * from test02;

PLSQL

#技术 #数据库

PLSQL学习

https://chris-z-su.github.io/2022/09/27/数据库/PLSQL学习/

作者

Chris

发布于

2022年9月27日

许可协议

下载Youtube视频和字幕上一篇

DB2学习下一篇