PLSQL编程

2022-07-12 13:58:37

PLSQL编程

一、变量

声名变量

变量名 变量类型（变量长度） 例如：v_name varchar2(20);

变量赋值

2.1 直接赋值

v_name varchar2(20) := '张三'

2.2 语句赋值，使用select...into...赋值

-- 打印人员个人信息，包括：姓名、薪水、地址
DECLARE
 --姓名,直接赋值
 v_name VARCHAR2(20) := '张三'; 
 --薪水
 v_sal NUMBER;
 --地址
 v_addr ARCHAR2(200);

BEGIN
 v_sal := 1580;
 --语句赋值
 SELECT '字段名' INTO v_addr FROM dual;
 --打印输出
 dbms_output.put_line('姓名：'||v_name||',薪水：'||v_sal||',地址：'||v_addr);
END;

引用型变量

变量的类型和长度取决于表中字段的类型和长度

通过表明.字段名%TYPE指定变量的类型和长度，例如：v_name emp.name%TYPE;

-- 查询emp表中7839号员工的个人呢信息，包括姓名和薪水
DECLARE
 --姓名
 v_name emp.name%TYPE := '张三'; --声名变量直接赋值
 --薪水
 v_sal emp.sal%TYPE;

BEGIN
 select ename,sal into v_name,v_sal from emp where eno = 7839;
 --打印信息
 dbms_output.put_line('姓名：'||v_name||',薪水：'||v_sal);
END;

记录型变量

4.1 接受表中的一整行记录，相当于Java中的一个对象

-- 查询emp表中7839号员工的个人呢信息，包括姓名和薪水
DECLARE
 --记录型变量
 v_emp emp%ROWTYPE;

BEGIN
 select * into v_emp from emp where eno = 7839;
 --打印信息
 dbms_output.put_line('姓名：'||v_emp.name||',薪水：'||v_emp.sal);
END;

4.2 接受表中一行的多列

declare
  --定义一个pl/sql记录类型emp_record_type,
  --类型包含3个数据
  type emp_record_type is record(
       vname emp.ename%type,
       salary emp.sal%type,
       title emp.job%type);
  --定义emp_record_type的变量
  sp_record emp_record_type;
begin
  select ename,sal,job into sp_record from emp where empno=7788;
  dbms_output.put_line(sp_record.vname||'-'||sp_record.salary||'-'||sp_record.title);
end;

二、流程控制

语法：

BEGIN

    IF 条件1 then 执行1
    ELSIF 条件2 then 执行2
    ELSE 执行3
    END IF;
    
END;

三、循环

语法：

BEGIN
    LOOP
        EXIT WHEN 退出循环条件
    END LOOP;
END;

举例

-- 打印数字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;

四、游标

使用方式：

声明->打开->读取->关闭

游标声明：CURSOR 游标名（参数列表） IS 查询语句;

游标打开：OPEN 游标名

游标取值：FETCH 游标名 INTO 变量列表;

游标关闭：CLOSE 游标名

游标属性

游标属性	返回值类型	说明
%ROWCOUNT	整型	获得FETCH语句放回的数据行数
%FOUND	布尔型	最近的FETCH语句一行数据则为真，否则为假
%NOTFOUND	布尔型	与%FOUND相反
%ISOPEN	布尔型	游标打开时返回真，否则为假

举例1

-- 使用游标查询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;

带参数的游标

举例

-- 使用游标查询EMP表中某部门员工的姓名和工资，部门编号为运行时输入
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;

五、存储过程

语法

CREATE OR REPLACE PROCEDURE 过程名称(参数列表) IS
BEGIN

END [过程名称];

IS和AS可以混用

无参存储过程

申明

create or replace procedure p_hello is
--声明变量位置
begin
  dbms_output.put_line('hello world');
end;

调用

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

带输入参数的存储过程

【示例】创建

create or replace procedure p_querynameandsal(i_empno in emp.empno%type) is
   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;

plsql调用

declare 
begin
  -- Test statements here
  p_querynameandsal(7839);
end;

命令行调用

SQL> exec p_querynameandsal(7839);

带输出参数的存储过程

【示例】

create or replace procedure p_querysal_out(i_empno in emp.empno%type,o_sal out emp.sal%type) is
begin
  select sal into o_sal from emp where empno = i_empno;

end p_querysal_out;

调用

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 batch_insert_table_test(insertNo in integer) is
       MAX_NO integer;
       i integer;
begin
  if insertNo is not null then
     MAX_NO :=insertNo;
  else
     MAX_NO :=0;
  end if;
  for i in 1 ..MAX_NO loop
    insert into test(userid,username,password,phone,relname,email,age,sex)
           --i,'user_i','123','12345678910',
           values(i||'','user_'||i,'123','12345678910','李'||i,'12345678@qq.com',11,1);
           if mod(i,100)=0 then
             commit;
           end if;
  end loop;
end batch_insert_table_test;

存储过程之批量修改数据

--修改userid能被5整除的行的sex字段值为3
create or replace procedure p_update_table_test is

  v_userid test.userid%type;
  cursor c_userid is
    select userid from test;
begin
  open c_userid;
  dbms_output.put_line('批量更新开始');
  loop
    fetch c_userid into v_userid;
    exit when c_userid%NOTFOUND;
    if mod(v_userid,5)=0 then
      dbms_output.put_line('useid - '||v_userid);
      update test set sex=3 where userid = v_userid;
    end if;
  end loop;
  commit; --必须提交，否则表数据不会更新
  close c_userid;
  dbms_output.put_line('批量更新结束');
end p_update_table_test;

六、分页查询

七、数组

定义固定长度的数组

type type_array is varray(10) of varchar2(20);

定义可变长数组

type type_array is table of varchar2(20) index by binary_integer;  
-- table表示可变长度
-- index by binary_integer 表示以符号整数为索引

一维数组初始化

-- 初始化为空数组
var_array type_array := type_array();
-- 初始化为六个元素数组
var_array type_array := type_array('ggs','jjh','wsb','csl','dd','bb'); 
--直接对各个元素进行赋值
var_array.extend(3);
var_array(1) = '1';
var_array(2) = '2';
var_array(3) = '3';
-- 通过遍历数组元素方式进行初始化操作
for i in 1..var_array.count loop
 var_array(i) = to_char(i);
end loop;

八、其他

开启控制台输出时间 set timing on;
开启控制台输出 set serveroutput on;
Oracle中的select for update 用法 https://www.cnblogs.com/suliang/articles/3570842.html

码农公寓

PLSQL编程

一、变量

二、流程控制

三、循环

四、游标

五、存储过程

六、分页查询

七、数组

八、其他

相关文章