//测试执行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;