欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

PL/SQL执行动态SQL(一) 博客分类: PL/SQL OraclePL/SQL动态SQLEXECUTE IMMEDIATE 

程序员文章站 2024-03-22 21:08:58
...
PL/SQL动态SQL

依据:
1.使用EXECUTE IMMEDIATE可以来执行动态SQL
2.open cursor可以打开一个sql的查询,fetch cursor可以挨个获取查询记录

示例
1.使用EXECUTE IMMEDIATE查询一张数据表的记录数
DECLARE 
  get_count_sql varchar2(100);
  row_count INT;
BEGIN
    get_count_sql:='select count(*) from bizbusinessbaseinfo';
    EXECUTE IMMEDIATE get_count_sql INTO row_count;
    dbms_output.put_line(row_count);
END;

2.使用EXECUTE IMMEDIATE执行动态删除语句
DECLARE 
  delete_sql varchar2(100);
BEGIN
    delete_sql:='delete from bizbusinessbaseinfo where bizname=''1''';
    execute immediate delete_sql;
END;

3.执行动态DDL语句
DECLARE 
  drop_sql varchar2(100);
BEGIN
    drop_sql:='DROP TABLE logrec_action';
    execute immediate drop_sql;
END;

4.使用open cursor进行简单的动态查询
DECLARE 
  query_sql varchar2(100);
  queryresult varchar2(50);
  bizcursor SYS_REFCURSOR;
BEGIN
    query_sql:='select bizname from bizbusinessbaseinfo';
    OPEN bizcursor FOR query_sql;
    loop
      fetch bizcursor into queryresult;
      --判读是否提取到值,没取到值就退出
      --取到值c_job%notfound 是false 
      --取不到值c_job%notfound 是true
      exit when bizcursor%notfound;
      dbms_output.put_line(queryresult);
    end loop;
    --关闭游标
    close bizcursor;
END;


5.动态查询出多个字段(需要先定义好输出结果的类型,不方便用在动态查询中)
DECLARE 
  query_sql varchar2(100);
  queryresult varchar2(50);
  bizcursor SYS_REFCURSOR;
  type my_record is record(
       bizname varchar2(64),
       bizstatus char(1));
  my_rec my_record;
BEGIN
    query_sql:='select bizname,bizstatus from bizbusinessbaseinfo';
    OPEN bizcursor FOR query_sql;
    loop
      fetch bizcursor into my_rec;
      --判读是否提取到值,没取到值就退出
      --取到值c_job%notfound 是false 
      --取不到值c_job%notfound 是true
      exit when bizcursor%notfound;
      dbms_output.put_line(my_rec.bizname|| '    ' ||my_rec.bizstatus);
    end loop;
    --关闭游标
    close bizcursor;
END;