create or replace procedure create_table as

begin

execute immediate '

create table emp(id number,

name varchar2(10)

salary number; )'; --动态SQL为DDL语句

insert into emp

values (100,'jacky',5600);

insert into emp

values (101,'rose',3000);

insert into emp

values (102,'john',4500);

end create_table;

create or replace procedure find_info(p_id number) as

v_name varchar2(10);

v_salary number;

begin

execute immediate '

select name,salary from emp

where id=:1'

using p_id

returning into v_name,v_salary; --动态SQL为查询语句

dbms_output.put_line(v_name ||'的收入为：'||to_char(v_salary))；

exception

when others then

dbms_output.put_line('找不到相应数据')；

end find_info;

create or replace procedure find_emp(p_salary number) as

r_emp emp%rowtype;

type c_type is ref cursor;

c1 c_type;

begin

open c1 for '

select * from emp

where salary >:1'

using p_salary;

loop

fetch c1 into r_emp;

exit when c1%notfound;

dbms_output.put_line('薪水大于‘||to_char(p_salary)||’的员工为：‘);

dbms_output.put_line('ID为'to_char(r_emp)||' 其姓名为：'||r_emp.name);

end loop;

close c1;

end create_table;