orcale学习笔记

 

 //测试执行SQL字符串语句

declare
  s_sql varchar(1000):='update jobs set job_title=''President'' where job_id=''AD_PRES''';
  retCnt number;
  job_id varchar2(100);
  job_title varchar2(300);
  begin
  dbms_output.put_line('----------------记录更新数值-------------------');
  dbms_output.put_line('测试SQL：'||s_sql);
  execute immediate s_sql;
  retCnt:=sql%rowcount;
  dbms_output.put_line(retCnt);
  dbms_output.put_line('----------------记录数-------------------');
  s_sql:='select count(*) from jobs ';
  execute immediate s_sql into retCnt;
  dbms_output.put_line(retCnt);
  dbms_output.put_line('--------------取值---------------------');
  s_sql:='select job_id,job_title from jobs where job_id=''AD_PRES'' ';
  execute immediate s_sql into job_id,job_title;
  dbms_output.put_line(job_id||'====>>>'||job_title);
  rollback;
end;

select *from jobs where
(case when  job_id='AD_PRES' then 0
      when  job_id='AD_VP'   then 1
      else 3
      end) <2

declare a number:=1;
b number;
begin
  if a=1 then
     dbms_output.put_line('a');
  else
    dbms_output.put_line('b');
  end if;
 
  b:=case when a=1 then 3 else 4 end;
   dbms_output.put_line(b);
  
end;

--集合
DECLARE
TYPE Country_name is Table of countries.country_name%type
index by binary_integer;
v_name Country_name;
begin
for v_C in 1..10 loop
    v_name(v_C):=v_C||'==>>        ';
end loop;

for v in 1..10  loop
dbms_output.put_line(v_name(v));
end loop;

end;

--嵌套表
DECLARE
TYPE StudyTab IS TABLE OF VARCHAR2(20);
v_StudyTab StudyTab:=StudyTab('Tom','Jack','Rose');
BEGIN
  FOR v_Count in 1..3 loop
      dbms_output.put_line(v_StudyTab(v_Count));
  end loop;
END;

--可变数组
DECLARE
TYPE Dates IS VARRAY(7) OF VARCHAR2(10);
v_Dates Dates:=Dates('Monday','Tuesday','Wednesday');
BEGIN
  dbms_output.put_line(v_Dates(1));
  dbms_output.put_line(v_Dates(2));
  dbms_output.put_line(v_Dates(3));
  dbms_output.put_line(v_Dates.Count);
END;

DECLARE
Cursor_name Integer;
begin
  Cursor_name:=dbms_sql.open_cursor;
  dbms_sql.parse(Cursor_name,'select *From jobs');
  dbms_sql.close_cursor(Cursor_name);
end;

select *From jobs where (job_id,job_title)=
(select job_id,job_title from jobs where rownum=1)

//游标

declare str varchar(4000):='';
  v_dmz SD_CODE.dmsm1%TYPE;
   cursor c_EMP is select dmsm1 from SD_CODE  WHERE dmlb=4;
begin
   open c_EMP;
           loop
             fetch c_EMP into v_dmz;
             exit when c_EMP%notFound;
           str:=str||','||v_dmz;
           end loop;
       close c_EMP;
dbms_output.put_line(str||',');
end;