【oracle】【demo】利用dbms_sql包执行动态SQL

【oracle】【demo】利用dbms_sql包执行动态SQL

【建表】

--drop table t_employee cascade constraints;

--drop table t_employee_salary cascade constraints;

create table t_employee(

id integer,

name varchar2(100),

age integer,

gender char check (gender in ('F','M'))

)

 

create table t_employee_salary(

id integer,

salary number(10,2)

)

 

inert into t_employee values(1,'小a',27,'F');

inert into t_employee values(2,'小b',23'M');

inert into t_employee values(3,'小c',25,'M');

inert into t_employee values(4,'小d',26,'F');

 

inert into t_employee_salary values(1,6000);

inert into t_employee_salary values(2,3000);

inert into t_employee_salary values(3,4500);

inert into t_employee_salary values(4,4300);

commit;

 

【建存储过程】

create or replace procedure prc_update_salary(

iv_salary_top in number

)

as

cur_sql number;

exec_sql varchar2(1000);

v_stat number;

v_age int := 25;

v_sql varchar2(2000) := 'select count(*) from t_employee where age >= :bind_age';

cursor cur_update is

select 'update t_employee_salary set salary=salary+1000 where salary <='||iv_salary_top||' and id='||t.id update_sql from t_employee t where t.age >= v_age;

cursor cur_select is

select t1.id,t1.name,t1.age,t2.salary from t_employee t1,t_employee_salary t2 where t2.id=t1.id;

 

begin

--利用dbms_sql包执行动态SQL（动态SQL也可以用execute immediate 执行）

cur_sql := dbms_sql.open_cursor;

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

dbms_sql.bind_variable(cur_sql,':bind_age',v_age);

v_stat := dbms_sql.execute(cur_sql);

dbms_sql.close_cursor(cur_sql);

dbms_output.put_line('v_stat='||v_stat);

 

--利用dbms_sql包执行游标里的sql语句

cur_sql := dbms_sql.open_cursor;

for rec  in cur_update loop

    exec_sql := rec.update_sql;

    dbms_sql.parse(cur_sql,exec_sql,dbms_sql.v7);

    v_stat := dbms_sql.execute(cur_sql);

end loop;

commit;

dbms_sql.close_cursor(cur_sql);

dbms_output.put_line('v_stat='||v_stat);

 

open cur_select;

--利用dbms_sql包 打印游标内容

loop

    exit when dbms_sql.fetch_row(cur_select) <= 0;  --fetch_row 在结果集中移动游标，如果未达到末尾，则返回1

dbms_sql.column_value(cur_select,1,id);

dbms_sql.column_value(cur_select,2,name);

dbms_sql.column_value(cur_select,3,age);

dbms_sql.column_value(cur_select,4,salary);

end loop;

close cur_select;

 

end;

/

 

set serveroutput on;

select t1.id,t1.name,t1.age,t2.salary from t_employee t1,t_employee_salary t2 where t2.id=t1.id;

 

exec prc_update_salary(5000);

 

 

【FAQ】

Test存储过程，debug时报错：

ORA-0131:Insufficient privilege

Note:Debugging requires the DEBUG CONNECT SESSION system privilege.

措施：

grant debug any procedure to testdb;

grant debug connect session to testdb;