PL/SQL执行动态SQL（一）
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;