存储过程例子 和 oracle中cute_immediate的使用.doc

存储过程例子 里边的sql语句 需要拼写成 字符串的格式：create or replace procedure Prc_Update_Record(LICENSE_NUMBER in varchar2,LICENSE_START in varchar2,LICENSE_END in varchar2,REGIONSTR in varchar2,SPESTR in varchar2,YEARSTR in varchar2,NUM in varchar2,ID in varchar2,tp in varchar2 ) isv_sql varchar2(4000);BEGIN if tp=01 then -更新表T_LICE_PUBLIC_PLACE v_sql:=Update adminlice.T_LICE_PUBLIC_PLACE t set t.license_number=|LICENSE_NUMBER|,t.license_start=|LICENSE_START|,t.license_end=|LICENSE_END |,t.regionstr=|REGIONSTR|,t.spestr=|SPESTR|,t.yearstr=|YEARSTR|,t.num=|NUM| where t.id=|ID|; DBMS_OUTPUT.PUT_LINE( Number of employees with higher salary: | ID |LICENSE_NUMBER: |LICENSE_NUMBER ); execute immediate v_sql; commit; end if; END Prc_Update_Record;execute immediate的语法如下：execute immediate sql;execute immediate sql_select into var_1, var_2;execute immediate sql using in|out|in out bind_var_1, in|out|in out bind_var_2;execute immediate sql_select into var_1, var_2 using in|out|in out bind_var_1, in|out|in out bind_var_2;execute immediate sql returning into var_1;execute immediate sql bulk collect into indexed_var;用法实例：Declare v_table varchar2(20); v_sql Varchar2(100); Begin v_table:=hqf.testtable; v_sql:=select * from |v_table; dbms_output.put_line(v_table); dbms_output.put_line(v_sql); Execute Immediate v_sql; End;Declare v_sid Integer:=20020101; v_sql Varchar2(100); v_result Varchar2(50);Begin -v_sid:=12; v_sql:=Select loc into v_result from scott.dept d where d.deptno=:1 ; dbms_output.put_line(v_sql); -dbms_output.put_line(v_result); Execute Immediate v_sql Using 10 ; Commit;End;-动态命令的实用参考一（ select .into）-Execute immediate 命令在使用into关键字时与静态的plsql块中-into关键字使用方法上的区别。declarev_result varchar2(20);beginexecute immediate select dname from scott.dept d where d.deptno=:1 Into v_result -:1这是一个绑定变量Using 70;commit;dbms_output.put_line(结果为：|v_result);end;-绑定变量，通俗的讲就是一个占位的参数，而后用using中的常量或着变量去找它的相应Declarev_result Varchar2(20);Begin Select dname Into v_result From scott.dept d Where d.deptno=10; dbms_output.put_line(v_result);End;declarev_result varchar2(20);beginexecute immediate select dname from scott.dept d where d.deptno=:1 Into v_resultUsing 70;commit;dbms_output.put_line(结果为：|v_result);end;-动态命令的实用参考一（ insert .into）-Begin Execute Immediate insert into scott.dept values (:1,:2,:3) Using 70,IT,Beijing; Commit;End;declare l_depnam varchar2(20) := testing; l_loc varchar2(10) := Dubai; begin execute immediate insert into scott.dept values (:1, :2, :3) using 60, l_depnam, l_loc; commit;end;-尽享动态执行sql的execute immediate的命令使用的魅力。-对比静态的测试，和动态测试。Declarev_result Varchar2(20);Beginv_result:=mypackage.myfun1(10);dbms_output.put_line(v_result);mypackage.mypro1(20020101,v_result);dbms_output.put_line(v_result);End;-Select * From student s Where s.student_id=20020101;-Select * From testtable Where recordnumber=10;Declarev_result Varchar2(20);Begin -动态执行函数 Execute Immediate begin :1:=mypackage.myfun1(:2); end; Using Out v_result,In 10; Commit; dbms_output.put_line(v_result); -动态执行存储过程 Execute Immediate begin mypro1(:1,:2); end; Using In 20020101,Out v_result; Commit; dbms_output.put_line(v_result);End;-动态执行update语句实例：create or replace function f_testweekdayreturn integeris v_sql varchar(2000); i_sendcount integer;begin-_ | f_getweekday() | v_sql := update t_push_smstemp_inform set sendcount=sendcount+1 returning sendcount into :1; execute immediate v_sql using out i_sendcount;return 0;exception when others then rollback; return 0;end f_testweekday;oracle中execute immediate的使用文章分类:数据库 转至：/blog/354521EXECUTE IMMEDIATE代替了以前Oracle8i中DBMS_SQL package包.它解析并马上执行动态的SQL语句或非运行时创建的PL/SQL块.动态创建和执行SQL语句性能超前，EXECUTE IMMEDIATE的目标在于减小企业费用并获得较高的性能，较之以前它相当轻易编码.尽管DBMS_SQL仍然可用， 但是推荐使用EXECUTE IMMEDIATE,因为它获的收益在包之上。使用技巧1. EXECUTE IMMEDIATE将不会提交一个DML事务执行，应该显式提交假如通过EXECUTE IMMEDIATE处理DML命令，那么在完成以前需要显式提交或者作为EXECUTE IMMEDIATE自己的一部分. 假如通过EXECUTE IMMEDIATE处理DDL命令,它提交所有以前改变的数据2. 不支持返回多行的查询,这种交互将用临时表来存储记录(参照例子如下)或者用REF cursors.3. 当执行SQL语句时，不要用分号，当执行PL/SQL块时，在其尾部用分号.4. 在Oracle手册中，未具体覆盖这些功能。下面的例子展示了所有用到Execute immediate的可能方面.希望能给你带来方便.5. 对于Forms开发者,当在PL/SQL .版本中，Forms 6i不能使用此功能.EXECUTE IMMEDIATE用法例子1. 在PL/SQL运行DDL语句beginexecute immediate set role all;end;2. 给动态语句传值(USING 子句)declarel_depnam varchar2(20) := testing;l_locvarchar2(10) := Dubai;beginexecute immediate insert into dept values(:1, :2, :3)using 50, l_depnam, l_loc;commit;end;3. 从动态语句检索值(INTO子句)declarel_cntvarchar2(20);beginexecute immediate select count(1) from empinto l_cnt;dbms_output.put_line(l_cnt);end;4. 动态调用例程.例程中用到的绑定变量参数必须指定参数类型.黓认为IN类型,其它类型必须显式指定declarel_routin varchar2(100) := gen2161.get_rowcnt;l_tblnam varchar2(20) := emp;l_cntnumber;l_status varchar2(200);beginexecute immediate begin l_routin (:2, :3, :4); end;using in l_tblnam, out l_cnt, in out l_status;if l_status != OK thendbms_output.put_line(error);end if;end;5. 将返回值传递到PL/SQL记录类型;同样也可用%rowtype变量declaretype empdtlrec is record (empnonumber(4),enamevarchar2(20),deptnonumber(2);empdtl empdtlrec;beginexecute immediate select empno, ename, deptno from emp where empno = 7934into empdtl;end;6. 传递并检索值.INTO子句用在USING子句前declarel_deptpls_integer := 20;l_nam varchar2(20);l_loc varchar2(20);beginexecute immediate select dname, loc from dept where deptno = :1into l_nam, l_locusing l_dept ;end;7. 多行查询选项.对此选项用ins