PL/SQL执行动态SQL（一） 博客分类： PL/SQL OraclePL/SQL动态SQLEXECUTE IMMEDIATE

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;