★☆

1)基本赋值
declare
  i varchar(16);
begin
  SELECT COUNT(*)
      INTO i
   FROM t_info_log
    WHERE visit_id ='12156';
   DBMS_OUTPUT.PUT_LINE (
       'hahaa' ||
        i ||
        'hun');
end;
功能:将sql查询结果放入 i中。

2)检测数据库中的表是否存在
select count(*)
into i
from user_tables
where table_name = upper('test2_01');
i=1时,test2_01存在,i=0时,不存在。

3)sql动态执行
 if i = 1 then
        str := 'drop table test2_01 ';
        execute immediate str;
end if;
动态sql和静态sql的主要区别是，静态sql会检查表是否存在，动态sql不会检测。

4)游标的使用
a)
procedure myProcedure(empId in varchar2)
is
  sql_str long;
  temp  varchar2(32);
  cur sys_refcursor;
begin
  sql_str :='select interest_desc from code_interest0136 where emp_id ='|| empId ;
  open cur for sql_str;
  loop
    fetch cur into temp;
    dbms_output.put_line(temp);
    exit when cur%notfound;
  end loop;
  close cur; 
end myProcedure;

b)
cursor cur is select id from info_user;
loop
  open cur;
  fetch cur into my_id;
    //处理.............
  exit when cur%notfound;
end loop;

5)记录RECORD的使用
create or replace procedure table0136 is
TYPE tableRecord IS RECORD (
a1 number,
a2 varchar2(32)
);
v_ret tableRecord;
cursor cur is select * from aa_1;
a varchar2(20);

begin
open cur;
loop
fetch cur into v_ret;
DBMS_OUTPUT.put_line(v_ret.a1||'    '||v_ret.a2);
exit when cur%NOTFOUND;
end loop;
end table0136;