存储过程

创建存储过程pr

create procedure pr is
 begin
    insert into emp(empno,ename) values(1001,'luo');
 end;

调用：exec pr
结果：
SQL> select*from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---

1001 luo
7369 SMITH CLERK 7902 1980/12/17 800.00 20
7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981/4/2 2975.00 20

带参存储过程pr0

SQL> create procedure pr0(in_empno number) is
  2   begin
  3      delete from emp where empno=in_empno;
  4   end;
  5  
  6  /

调用：

SQL> exec pr0(7788);
PL/SQL procedure successfully completed

用rowtype

SQL> create procedure pr11 is
  2  myemp EMP%rowtype;
  3  begin
  4      select * into myemp from emp where empno=7934;
  5      dbms_output.put_line(myemp.ename);
  6  end;
  7  /
Procedure created

SQL> exec pr11;
PL/SQL procedure successfully completed

SQL> set serveroutput on;
SQL> /
MILLER
PL/SQL procedure successfully completed

控制语句

if

1：if then
2 : if then else
3 : if elsif else
示例：

 SQL> create procedure p6(in_empno number) is
  2    v_sal emp.sal%type;
  3    begin
  4      select sal into v_sal from emp where empno=in_empno;
  5      if v_sal >1500 then
  6        update emp set sal=sal+200 where empno=in_empno;
  7      elsif v_sal>900 then
  8        update emp set sal=sal+100 where empno=in_empno;
  9      else
 10        update emp set sal=sal-50 where empno=in_empno;
 11      end if;
 12     end;
 13  /
Procedure created
SQL> exec p6(7369);
PL/SQL procedure successfully completed 

case when

SQL> create procedure p7 is
2 v_grade char(1) :=UPPER(‘&p_grade’);
3 begin
4 case v_grade
5 when ‘A’ then
6 dbms_output.put_line(‘perfect’);
7 when ‘B’ then
8 dbms_output.put_line(‘very good’);
9 when ‘C’ then
10 dbms_output.put_line(‘good’);
11
12 else
13 dbms_output.put_line(‘No such grade’);
14 end case;
15 end;
16
17 /
Procedure created

loop循环

SQL> create or replace procedure p8 is
  2   v_counter number(3) :=0;
  3   v_result  number :=0;
  4   begin
  5     loop
  6       v_counter:=v_counter+1;
  7       v_result:=v_result+v_counter;
  8  
  9       if v_counter >=100 then
 10         exit;
 11       end if;
 12     end loop;
 13     dbms_output.put_line('1+2+3+…+100 的值为：'||v_result);
 14  
 15   end;
 16  /
Procedure created

SQL> exec p8;
1+2+3+…+100 的值为：5050
PL/SQL procedure successfully completed

while循环

SQL> create or replace procedure p9 is
  2   v_counter number(3) :=0;
  3   v_result  number :=0;
  4   begin
  5     while v_counter < 100 loop
  6       v_counter:=v_counter+1;
  7       v_result:=v_result+v_counter;
  8   end loop;
  9     dbms_output.put_line('1+2+3+…+100 的值为：'||v_result);
 10  
 11   end;
 12  /
Procedure created

SQL> exec p9;
1+2+3+…+100 的值为：5050
PL/SQL procedure successfully completed

for循环

SQL> create or replace procedure p10 is
  2   v_counter number(3) :=0;
  3   v_result  number :=0;
  4   begin
  5     for v_counter in 1..100 loop
  6       v_result:=v_result+v_counter;
  7       end loop;
  8  
  9     dbms_output.put_line('1+2+3+…+100 的值为：'||v_result);
 10  
 11   end;
 12  /
Procedure created

SQL> exec p10;
1+2+3+…+100 的值为：5050
PL/SQL procedure successfully completed

goto/null

SQL> DECLARE
  2  sumsal emp.sal%TYPE;
  3  BEGIN
  4  SELECT SUM(sal) INTO sumsal FROM EMP;
  5  IF sumsal>20000 THEN
  6  GOTO first_label;
  7  ELSE
  8  GOTO second_label;
  9  END IF;
 10  <<first_label>>
 11  dbms_output.put_line('ABOVE 20000:' || sumsal);
 12  <<second_label>>
 13  NULL;
 14  END;
 15  /
ABOVE 20000:24375
PL/SQL procedure successfully completed