PL/SQL语句（不断总结）

DECLARE
  score BINARY_INTEGER := 61;
BEGIN
   IF score>=90 THEN
      DBMS_OUTPUT.PUT_LINE('good');
   ELSIF  score >=80 then
      DBMS_OUTPUT.PUT_LINE('lianghao');
   ELSIF  score >=60 THEN
      DBMS_OUTPUT.PUT_LINE('jige');
   ELSE
      DBMS_OUTPUT.PUT_LINE('bujige');
   END IF;

END;

case：简单case表达式

declare
    grade varchar2(4) :='jige';
begin
    case grade
      when 'good' then dbms_output.put_line('good');
      when 'good' then dbms_output.put_line('haihao');
      when 'good' then dbms_output.put_line('jige');
   else dbms_output.put_line('not found!');
end case;
end;

，搜索case表达式（不同之处在case后面有没有语句）

declare
 grade varchar2(4) :='jige';
begin
case 
when grade='good' then dbms_output.put_line('good');
when grade= 'good' then dbms_output.put_line('haihao');
when grade='jige' then dbms_output.put_line('jige');
else dbms_output.put_line('not found!');
end case;
end;

loop循环

declare
i binary_integer:=-1;
begin loop
dbms_output.put_line(i);
i :=i+1;
exit when i>10;
end loop;
end;

while循环

declare
i binary_integer:=-1;
begin
while i<=10
loop 
dbms_output.put_line(i);
i :=i+1;
end loop;
end;

复合变量

set serveroutput on
declare
emp_number constant scott.emp.empno%type := 7900;
emp_name scott.emp.ename%type;
emp_sal scott.emp.sal%type;
begin
select ename,sal
into emp_name,emp_sal
from scott.emp where empno=emp_number;
dbms_output.put_line('empno is:'||emp_number);
dbms_output.put_line('empname is:'||emp_name);
dbms_output.put_line('empsal is :'||emp_sal);
end;
/

实现相同功能的  rowtype 写法

declare
emp_number constant scott.emp.empno%type := 7900;
one_emp scott.emp%rowtype;
begin
select * into one_emp
from scott.emp
where empno=emp_number;
dbms_output.put_line('empno is:'||one_emp.ename);
dbms_output.put_line('empname is:'||one_emp.sal);
end;

记录类型：

DECLARE
TYPE emp_type IS RECORD(
empno  NUMBER(4),
ename VARCHAR2(10),
sal NUMBER(7,2)
);
emp_one emp_type;
BEGIN
   select empno,ename,sal into emp_one
   from scott.emp where empno=7900;
   dbms_output.put_line('employees salary is:' || emp_one.sal);
end;

索引表类型

declare
type ename_table_type is table of scott.emp.ename%type
index by binary_integer;
ename_table ename_table_type;
begin
select ename into ename_table(1) from scott.emp
where empno=7788;
dbms_output.put_line(ename_table(1));
select ename into ename_table(2) from scott.emp
where empno=7369;
dbms_output.put_line(ename_table(2));
end;
/