SQL> declare
  2      query varchar2(100):='select* from emp where empno=:eno';
  3      emp_record emp%rowtype;
  4      begin
  5       execute immediate query into emp_record using &eno;
  6       dbms_output.put_line('name:'||emp_record.ename||'岗位:'||emp_record.job);
  7   end;
  8  /

name:SMITH岗位:CLERK

PL/SQL procedure successfully completed

declare
dml_stat VARCHAR2(100):='UPDATE EMP SET SAL=SAL+400 WHERE EMPNO=7369 RETURNING ENAME INTO :NAME';
V_NAME EMP.ENAME%TYPE;
BEGIN
 EXECUTE IMMEDIATE DML_STAT returning into v_name;
 dbms_output.put_line(v_name||'加薪');
commit;
end;

declare
type empcur is ref cursor;
emp_cv empcur ;
emp_record emp%ROWTYPE;
SQL_STAT varchar2(100);
begin
 sql_stat:='select * from emp where deptno=:dno';
 open emp_cv for sql_stat using 10;
 loop
 fetch emp_cv into emp_record;
 exit when emp_cv%notfound;
 dbms_output.put_line('name:'||emp_record.ename||' salary:'||emp_record.sal);
end loop;
close emp_cv;

declare
 type ename_table_type is varray(100) of emp.ename%type;
 type hiredate_type is varray(100) of emp.hiredate%type;
 ename_table ename_table_type;
 hiredate hiredate_type;
 sql_stat varchar2(100):='select ename,hiredate from emp where deptno=:dnp';
begin
 execute immediate sql_stat
   bulk collect into ename_table,hiredate using 20;
 for i in 1..ename_table.count loop
   dbms_output.put_line('name:'||ename_table(i)||',work day:'||hiredate(i));
end loop;
end;

 
 create or replace procedure update_sal
(eno number,salary number,name out varchar2) is
begin
 update emp set sal=salary where empno=eno
 returning ename into name;
 end;
 

create or replace  package test is
  procedure test(v_name varchar2);
  end;

create or replace package body test is
  procedure test(v_name varchar2)
is
v_num  number(10):=1000;
begin
  update pp set SALARY=v_num  where name=v_name;
  insert into pp values(v_name,5000);
end;
end;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7728585/viewspace-524496/，如需转载，请注明出处，否则将追究法律责任。