转载： http://blog.163.com/lyq_163_2009/blog/static/1340826962010712104242953/
//table变量类型
declare
type type_table_emp_empno is table of emp.empno%type index by binary_integer;
v_empnos type_table_emp_empno;
begin
v_empnos(0) := 7369;
v_empnos(1) := 7839;
v_empnos(-1) := 9999;
dbms_output.put_line(v_empnos(-1));
end;
/
//Record变量类型
declare
type type_record_dept is record
(
deptno dept.deptno%type,
dname dept.dname%type,
loc dept.loc%type
);
v_temp type_record_dept;
begin
v_temp.deptno := 50;
v_temp.dname := 'dname';
v_temp.loc := 'BJ';
dbms_output.put_line(v_temp.deptno || ' ' || v_temp.dname);
end;
/
//使用%rowtype 声明record变量
declare
v_temp dept%rowtype;
begin
v_temp.deptno := 50;
v_temp.dname := 'dname';
v_temp.loc :='BJ';
dbms_output.put_line(v_temp.deptno || ' ' || v_temp.dname);
end;
/
//SQL语句应用
declare
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
select ename,sal into v_ename,v_sal from emp where empno=7900;
--select ename,sal into v_ename,v_sal from emp where deptno=30;
dbms_output.put_line(v_ename || ' ' || v_sal);
end;
/
//SQL语句应用
declare
v_emp emp%rowtype;
begin
select * into v_emp from emp where empno=7369;
dbms_output.put_line(v_emp.ename);
end;
/
//SQL语句应用
create table dept2(
deptno number,
dname varchar2(50),
loc varchar2(50)
);
declare
v_deptno dept.deptno%type := 50;
v_dname dept.dname%type :='My Name';
v_loc dept.loc%type := 'BJ';
begin
insert into dept2 values(v_deptno,v_dname,v_loc);
end;
/
select * from dept2;
--drop table dept2;
//SQL语句应用
declare
v_deptno dept2.deptno%type := 30;
v_count number;
begin
select deptno into v_deptno from dept2 where deptno=50;
select count(*) into v_count from dept2;
dbms_output.put_line('deptno:' || v_deptno);
dbms_output.put_line('count:' || v_count);
dbms_output.put_line(sql%rowcount || '条记录被影响...');
end;
/
//使用DDL语句
begin
execute immediate 'create table T(nnn varchar2(20))';
end;
/
drop table T;
//选择语句
declare
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno=7369;
if(v_sal < 1200)then
dbms_output.put_line('low');
elsif(v_sal < 2000) then
dbms_output.put_line('middle');
else
dbms_output.put_line('high');
end if;
end;
/
//循环语句
declare
i binary_integer := 1;
begin
loop
dbms_output.put_line('==' || i);
i := i+1;
exit when(i>=11);
end loop;
end;
/
//循环语句
declare
j binary_integer := 1;
begin
while j<11 loop
dbms_output.put_line('==' || j);
j := j+1;
end loop;
end;
/
//循环语句
begin
for k in 1..10 loop
dbms_output.put_line('==' || k);
end loop;
for k in reverse 1..10 loop
dbms_output.put_line('==' || k);
end loop;
end;
/
