存储过程例子和oracle中execute_immediate的使用

存储过程例子 里边的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 ) is v_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关键...