学习pl/sql之一
--使用pl/sql语句打印一个hello world
begin
dbms_output.put_line('hello,world');
end;
但是在sqlplus里面就不一样了
首先输入
begin
dbms_output.put_line('hello,world');
end;
/
通过set serveroutput on
/
来调用输出语句
可以通过edit命令对过程语句进行修改
name [constrant]datatype[notnull]:=|default value
--pl/sql语句中的if语句
declare
sal1 number(6,2);
begin
select sal into sal1 from emp where lower(ename)=lower('&ename');
if (sal1<2000) then
dbms_output.put_line('less 2000');
end if;
end;
--pl/sql 里面的 if else 语句
declare
sal1 number(6,2);
begin
select sal into sal1 from emp where lower(ename)=lower('&ename');
if (sal1<2000) then
dbms_output.put_line('less 2000');
else
dbms_output.put_line('more 2000');
end if;
end;
--pl/sql语句中的if elsif语句
declare
sal1 number(6,2);
begin
select sal into sal1 from emp where lower(ename)=lower('&ename');
if (sal1<2000) then
dbms_output.put_line('less 2000');
elsif (sal1>2000 and sal1<400) then
dbms_output.put_line('between 2000 and 4000');
elsif (sal1>400) then
dbms_output.put_line('more 4000');
else
dbms_output.put_line('other...........');
end if;
end;
--pl/sql语句里面的case语句
declare
a number(2):=0;
begin
a:=&a;
case a
when 10 then
dbms_output.put_line('aaaaaaaaaaa');
when 20 then
dbms_output.put_line('bbbbbbbbbbbb');
else
dbms_output.put_line('other.....');
end case;
end;
--pl/sql语句里面的loop语句
declare
q number :=0;
begin
loop
q:=q+1;
dbms_output.put_line('q='||q);
exit when q=10;
end loop;
end;
--使用if 结束的
declare
q number :=0;
begin
loop
q:=q+1;
dbms_output.put_line('q='||q);
if q=10 then
exit;
end if;
end loop;
end;
--使用 while 循环
declare
q number :=0;
begin
while(q<10)loop
q:=q+1;
dbms_output.put_line('q='||q);
end loop;
end;
--使用 for循环 反向遍历 reverse
declare
begin
for i in reverse 1..10 loop
dbms_output.put_line(i);
end loop;
end;
PLS_INTEGER和BINARY_INTENER唯一区别是在计算当中发生溢出时，BINARY_INTENER型的变量会被自动指派给一个NUMBER型而不会出错，PLS_INTEGER型的变量将会发生错误。
record 定义类型
集合容器
index_by table
嵌套表
varray
type num_array is table of number(5) index by binary_integer;
单行单列 变量 varchar2 %type
单行多列 record
单列多行 集合 （type）
多行多列 集合（rowtype）
索引表的格式
type name is table of element_type index by key_type;
declare
v_ename emp.ename%type;
v_emp emp%rowtype;
begin
select ename into v_ename from emp where empno=7369;
select * into v_emp from emp where empno=7369;
dbms_output.put_line(v_ename);
dbms_output.put_line(v_emp.sal||' '||v_emp.ename);
end;
--仅输出特殊的结构 使用 record来定义
declare
type emp_record is record(
v_ename emp.ename%type,
v_sal emp.sal%type,
v_deptno emp.deptno%type
);
v_emp_record emp_record;
begin
select ename,sal,deptno into v_emp_record from emp where empno=7369;
--我也可以通过赋值语句修改里面的值
v_emp_record.v_ename:='zhaan';
dbms_output.put_line(v_emp_record.v_ename);
dbms_output.put_line(v_emp_record.v_sal);
dbms_output.put_line(v_emp_record.v_deptno);
end;
--
declare
type num_array is table of number(5) index by binary_integer;
a num_array;
begin
for i in 1..10 loop
a(i):=i;
end loop;
for i in 1..10 loop
dbms_output.put_line(a(i));
end loop;
end;
--例子一
declare
type emp_array is table of emp%rowtype index by binary_integer;
a emp_array;
begin
select * bulk collect into a from emp;
for i in a.first..a.last loop
dbms_output.put_line(a(i).ename||' '||a(i).job);
end loop;
end;
--集合中装有集合
declare
type record1 is record(
vempno emp.empno%type,
vename emp.ename%type,
vsal emp.sal%type
);
type record2 is record(
vdeptno emp.deptno%type,
vrecord record1
);
a record1;
b record2;
begin
select empno,ename,sal into a from emp where empno=7369;
b.vrecord:=a;
dbms_output.put_line(b.vrecord.vempno);
dbms_output.put_line(b.vrecord.vename);
dbms_output.put_line(b.vrecord.vsal);
end;
--
declare
type cc is table of varchar2(20) index by varchar2(20);
a cc;
begin
a('beijing'):='china';
a('dongjing'):='japan';
a('huashengdun'):='usa';
dbms_output.put_line(a('beijing'));
end;
