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/,如需转载,请注明出处,否则将追究法律责任。