【oracle】oracle 静态语句与动态语句

 

——补充 ORACLE动态语句
create or replace procedure prc_test(
iv_brand_code in varchar2,
ov_result out varchar2
)
is
vv_sql varchar2(100);
begin
--动态语句写法1. execute immediate into
vv_sql := 'select brand_name from td_brand where brand_code = upper ('''||iv_brand_code||''')';
execute immediate vv_sql into ov_result;

--动态语句写法2. execute immediate into using ，使用 绑定变量 做预留
vv_sql := 'select brand_name from td_brand where brand_code = upper (:brand_code)'; --注意:使用绑定变量，若使用 ?则该?被认为是非法字符
execute immediate vv_sql into ov_result using iv_brand_code;

commit;
exception 
when others then
rollback;
ov_result := substr(sqlcode||':'||sqlerrm,1,200);
end;
/

set serveroutput on;
declare
vv_result varchar2(200);
begin
prc_test('B01',vv_result);
dbms_output.put_line(vv_result);
end;
/