2020-08-30

1.begin end 代码块可以再嵌套begin end代码块
2.execute immediate into using语句
v_sql := ‘select a from t_1 where b=:1’
execute immediate v_sql into v_a using v_b;
3.要想让plsql碰到错误后继续执行，则需要用exception语句来捕捉错误
因为该表可能不存在，所以需要将这个语句用begin exception end来包起来，这样即使表不存在也不影响下面的语句执行
4.表名不能使用绑定变量
v_sql := ‘drop table :1’;
v_table_name := ‘t_1’;
execute immediate v_sql using v_table_name;
这样是不行的

SQL> declare
	v_sql varchar2(300) ;
	v_table_name varchar2(30);
	v_a varchar2(30);
begin
	v_table_name := 't_888';
	v_sql := 'drop table '||v_table_name;
	begin
		execute immediate v_sql;
	exception
		when others then
			null;
	end;
	v_sql := 'create table '||v_table_name||' (a varchar2(20),b number)';
	execute immediate v_sql;
	v_sql := 'insert into '||v_table_name||' values(:2,:3)';
	for i in 1..10 loop
		execute immediate v_sql using lpad(i,10,'a'),i;
		commit;
	end loop;
	v_sql := 'select a from '||v_table_name||' where b=:1';
	for i in 1..10 loop
		execute immediate v_sql into v_a using i;
		dbms_output.put_line('value of column a is:'||v_a);
	end loop;
	exception
		when others then
			null;
end;
/  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30
value of column a is:aaaaaaaaa1
value of column a is:aaaaaaaaa2
value of column a is:aaaaaaaaa3
value of column a is:aaaaaaaaa4
value of column a is:aaaaaaaaa5
value of column a is:aaaaaaaaa6
value of column a is:aaaaaaaaa7
value of column a is:aaaaaaaaa8
value of column a is:aaaaaaaaa9
value of column a is:aaaaaaaa10

PL/SQL procedure successfully completed.

declare
	v_sql varchar2(300) ;
	v_table_name varchar2(30);
	v_a varchar2(30);
begin
	v_table_name := 't_888';
	v_sql := 'drop table '||v_table_name;
	begin
		execute immediate v_sql;
	exception
		when others then
			null;
	end;
	v_sql := 'create table '||v_table_name||' (a varchar2(20),b number)';
	execute immediate v_sql;
	v_sql := 'insert into '||v_table_name||' values(:2,:3)';
	for i in 1..10 loop
		execute immediate v_sql using lpad(i,10,'a'),i;
		commit;
	end loop;
	v_sql := 'select a from '||v_table_name||' where b=:1';
	for i in 1..10 loop
		execute immediate v_sql into v_a using i;
		dbms_output.put_line('value of column a is:'||v_a);
	end loop;
	exception 
		when others then
			null;
end;
/