动态SQL

1.使用动态SQL包来创建表:

declare

    v_cur binary_integer;

    v_sql varchar2(200);

begin

    --为执行动态sql语句打开一个游标

    v_cur:=dbms_sql.open_cursor;

    v_sql:='create table mytest_zhanghb(id number(5) primary key,name varchar2(10))';

    --解析动态sql语句：

    --参数1：打开游标

    --参数2：执行的sql语句

    --参数3：oracle数据库类型版本，native表示使用本地数据库的版本

    dbms_sql.parse(v_cur,v_sql,dbms_sql.native);

    --关闭游标

    dbms_sql.close_cursor(v_cur);

end;

/

注意:在匿名语句块中调用procedure,如果此过程中使用了DDL语句,那么该用户必须直接赋予权限.

oracle用户的权限：

create user jd0712 identified by jd0712.

grant connect,resource to jd0712;

grant create table to jd0712;

2.使用动态SQL包来查询数据库的记录

create or replace procedure pro_select_zhanghb(v_table varchar2,v_num number)

is

    v_result binary_integer;

    v_id    number(5);

    v_name    varchar2(10);

    v_sql    varchar2(200);

    --打开游标

    v_sql:='select id,name from '||v_table||' where rownum<'||v_num;

    --解析动态sql

    --指定取出数据的类型和约束

    --指定取出数据的类型，必须用变量表示（注意：字符串类型要指定约束）

    dbms_sql.define_column(v_cur,1,v_id);

    dbms_sql.define_column(v_cur,2,v_name,10);

    --执行sql

    v_result:=dbms_sql.execute(v_cur);

    --获取数据

    while dbms_sql.fetch_rows(v_cur)>0 loop

        --定义变量存放取出的数据

        dbms_sql.column_value(v_cur,1,v_id);

        dbms_sql.column_value(v_cur,2,v_name);

        dbms_output.put_line('id:'||v_id||' name:'||v_name);

    end loop;

    --关闭游标

    dbms_sql.close_cursor(v_cur);

    pro_select_zhanghb('tbl_zhanghb',5);

3.使用动态SQL包插入数据

create or replace procedure pro_insert_zhanghb(v_talbe varchar2,v_id number,v_name varchar2)

    v_cur    binary_integer;

    v_result    binary_integer;

    v_sql    varchar2(200);

    --打开游标

    v_cur:=dbms_sql.open_cursor;

    v_sql:='insert into '||v_talbe||' values(:id,:name)';

    --解析动态sql语句

    dbms_sql.parse(v_cur,v_sql,dbms_sql.native);

    --绑定变量

    dbms_sql.bind_variable(v_cur,':id',v_id);

    dbms_sql.bind_variable(v_cur,':name',v_name);

    --执行动态sql

    v_result:=dbms_sql.execute(v_cur);

    commit;

    pro_insert_zhanghb('tbl_zhanghb',589,'seaside');

4.定时调用

--定时调用,一共四个参数，1,脚本号，2、操作(varchar)3、系统时间（date），4、每隔x时间执行一次（varchar)

create table job(s_date date);

    code number;

        DBMS_JOB.SUBMIT(code,'insert into job values(sysdate);',SYSDATE,'SYSDATE+1/(24*60)');

--查询脚本号，在数据字典中查询

USER_JOBS;

select JOB,what from USER_JOBS;

--删除脚本

DBMS_JOB.REMOVE(21);