Oracle变量、输入输出、SELECT INTO、%TYPE与%ROWTYPE详解_Oracle

一、变量

1.什么是变量

在 oracle 数据库里，变量发挥着重要作用，它能够存储中间值、传递数据以及增强 sql 和 pl/sql 代码的灵活性。

2.定义变量的语法

declare
 
  --声明的地方(声明变量，常量，游标等等)，这一块不是必须的。
begin

  --代码的执行的逻辑块(就是要做什么) 
end;

begin
  dbms_output.put_line('你好');
end;

begin
  dbms_output.put_line('hello world');
  dbms_output.put_line('你好');
end;

3.变量的赋值

declare
  v1 varchar2(20);
  v2 date;
begin
  v1 := 'hello';
  dbms_output.put_line(v1);
  v2 := to_date('20220101', 'yyyymmdd');
  dbms_output.put_line(v2);
end;

日期格式是按照国外的格式输出的

4.变量可以被多次赋值，可以被覆盖

declare
  v1 varchar2(20);
begin
  v1 := 'hello';
  -- dbms_output.put_line(v1);
  v1 := '你好';
  dbms_output.put_line(v1);
end;

dbms_output包主要用于调试pl/sql程序

一个变量的赋值，可以在 declare 中赋初始值，也可以在 begin end中进行赋值，一个变量也可以重复赋值。

练习：打印数字 10 以及打印当前年月日 'yyyy-mm-dd'

-- 方法一：
declare
  v1 number;
  v2 varchar2(20);
begin
  v1 := 10;
  dbms_output.put_line(v1);
  v2 := to_char(sysdate, 'yyyy-mm-dd');
  dbms_output.put_line(v2);
end;

-- 方法二：
declare
  v1 number := 10;
  v2 varchar2(20) := to_char(sysdate, 'yyyy-mm-dd');
begin
  dbms_output.put_line(v1 || chr(10) || v2);
end;

chr(10)表示换行

二、输入输出

1.&符号表示输入

变量来源于用户输入值
&后面接变量值名称

示例：用户输入一个数字，输出它的三次幂(例如：用户输入2，返回8)

declare
  v1 number := &input; -- 变量值名称可自定义
  v2 number;
begin
  v2 := power(v1, 3);
  -- v2:=v1*v1*v1;
  dbms_output.put_line(v2);
end;

2.关于赋值的数据类型说明

如果赋值的变量是字符串类型，那么在弹出的小窗口上传值的时候，记得添加单引号
如果赋值的变量是date类型，那么在弹出的小窗口上传值的时候，要用 to_date 这个函数转换数据类型

示例：用户输入两个变量并打印出来，一个字符串类型，一个是日期型

declare
  v1 varchar2(20) := '&input1';
  v2 date := &input2;
begin
  dbms_output.put_line(v1 || chr(10) || v2);
end;

练习：打印用户输入的两个值的加减乘除对应的值(6,3)

declare
  v1 number := &input1;
  v2 number := &input2;
begin
  dbms_output.put_line(v1 + v2);
  dbms_output.put_line(v1 - v2);
  dbms_output.put_line(v1 * v2);
  dbms_output.put_line(v1 / v2);
end;

三、变量赋值 select into

注意：select后面有几个值，into后面就要有相对应的变量，注意数据类型要一致。

示例：打印某个员工的薪资(用户输入工号，打印他的薪资)

declare
  v1    number := &input;
  v_sal number;
begin
  select sal into v_sal from emp where empno = v1;
  dbms_output.put_line(v_sal);
end;

示例：接收一个员工的编号，将员工的姓名，工资，入职日期打印出来

declare
  v1         number := &input;
  v_ename    varchar2(20);
  v_sal      number;
  v_hiredate date;
begin
  select ename, sal, hiredate
    into v_ename, v_sal, v_hiredate
    from emp
   where empno = v1;
  dbms_output.put_line(v_ename || ',' || v_sal || ',' || to_char(v_hiredate, 'yyyy-mm-dd'));
end;

练习1：输入一个部门编号，打印这个部门的平均薪资(保留两位小数)

输出样式：10号部门的平均薪资为：2916.67

declare
  v1        number := &input;
  v_avg_sal number;
begin
  select round(avg(sal + nvl(comm, 0)),2) avg_sal
    into v_avg_sal  
    from emp
   where deptno = v1
   group by deptno;
  dbms_output.put_line(v1 || '号部门的平均薪资为：' || v_avg_sal);
end;

练习2：写一个代码块，传入一个员工姓名，将这个员工的工号，岗位打印到 output 窗口来

declare
  v1      varchar2(20) := '&input';
  v_empno number;
  v_job   varchar2(20);
begin
  select empno, job into v_empno, v_job from emp where ename = v1;
  dbms_output.put_line(v_empno || ',' || v_job);
end;

四、使用%type与%rowtype无需写变量数据类型

%type：引用数据库中的某张表的某列的数据类型。
%rowtype：引用数据库中的某张表的一行（所有字段）作为数据类型。

1.%type

示例：接收一个员工的编号，将员工的姓名，工资，入职日期打印出来

declare
  v_empno    emp.empno%type := &input;
  v_ename    emp.ename%type;
  v_sal      emp.sal%type;
  v_hiredate emp.hiredate%type;
begin
  select ename, sal, hiredate
    into v_ename, v_sal, v_hiredate
    from emp
   where empno = v_empno;
  dbms_output.put_line(v_ename || '的薪资是：' || v_sal || '入职时间：' ||
                       to_char(v_hiredate,'yyyy-mm-dd'));
end;

2.%rowtype

declare
  v_emp emp%rowtype;
begin
  v_emp.empno := &input;
  select ename, sal, hiredate
    into v_emp.ename, v_emp.sal, v_emp.hiredate
    from emp
   where empno = v_emp.empno;
  dbms_output.put_line(v_emp.ename || '的薪资是：' || v_emp.sal || '入职时间：' ||
                       to_char(v_emp.hiredate, 'yyyy-mm-dd'));
end;

练习：写一个代码块，传入一个员工姓名，将这个员工的工号，岗位打印到 output 窗口来

将刚才写的两个课堂练习用 %type 和 %rowtype 实现

-- %type
declare
  v_ename emp.ename%type := '&input';
  v_empno emp.empno%type;
  v_job   emp.job%type;
begin
  select empno, job into v_empno, v_job from emp where ename = v_ename;
  dbms_output.put_line(v_ename || '的工号是：' || v_empno || '岗位是：' || v_job);
end;


-- %rowtype
declare
  v_emp emp%rowtype;
begin
  v_emp.ename := '&input';
  select empno, job
    into v_emp.empno, v_emp.job
    from emp
   where ename = v_emp.ename;
  dbms_output.put_line(v_emp.ename || '的工号是：' || v_emp.empno || '岗位是：' ||
                       v_emp.job);
end;