存储过程

创建存储过程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 createdSQL> exec pr11;PL/SQL procedure successfully completedSQL> set serveroutput on;SQL> /MILLERPL/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 createdSQL> 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 createdSQL> exec p8;1+2+3+…+100 的值为：5050PL/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 createdSQL> exec p9;1+2+3+…+100 的值为：5050PL/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 createdSQL> exec p10;1+2+3+…+100 的值为：5050PL/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:24375PL/SQL procedure successfully completed