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;
页:
[1]