PLSQL总结——18.动态SQL

最新推荐文章于 2022-07-17 20:15:00 发布

heiwangzi

最新推荐文章于 2022-07-17 20:15:00 发布

阅读量379

点赞数

分类专栏： PLSQL 文章标签： sql table date

本文链接：https://blog.csdn.net/heiwangzi/article/details/7261799

版权

PLSQL 专栏收录该内容

22 篇文章 0 订阅

订阅专栏

/*
create table EMPLOYEE
(
  employee_number NUMBER,
  employee_name   VARCHAR2(30),
  salary          NUMBER(7,2),
  department_id   NUMBER
)
*/

declare
  v_name   employee.employee_name%type;
  v_salary employee.salary%type;
  v_where1 varchar2(50) := 'and employee_number = :employee_number'; --条件1
  v_number varchar2(10) := '110';

  /*系统自带的游标变量*/
  employee_cur sys_refcursor;
  rec          employee%rowtype;
begin

  /*动态SQL查询单行记录*/
  execute immediate 'select employee_name,salary from employee where 1 = 1 ' ||
                    v_where1
    into v_name, v_salary
    using in v_number;

  dbms_output.put_line(v_name || '''s salary is ' || v_salary);
  dbms_output.put_line('');

  /*动态SQL查询多行记录*/
  open employee_cur for 'select * from employee where 1 = 1 ' || v_where1
    using in v_number;
  loop
    fetch employee_cur
      into rec;
    exit when employee_cur%notfound;
    /*假如第一条，输出头*/
    if employee_cur%rowcount = 1
    then
      dbms_output.put_line('the detial date as follow:');
      dbms_output.put_line('--------------------------------------------------');
      dbms_output.put_line('employee_number|employee_name|salary|department_id');
    end if;
    dbms_output.put_line(rpad(rec.employee_number, 15, ' ') || '|' ||
                         rpad(rec.employee_name, 13, ' ') || '|' ||
                         rpad(rec.salary, 6, ' ') || '|' ||
                         rpad(rec.department_id, 13, ' '));
  
  end loop;
  close employee_cur;

end;