oracle存储过程语句

1、创建一个a存储过程

create or replace procedure a (number1 in number ,number2 out number)         申明参数、参数类型、参数输入in\参数输出out
is
number3 number(2);                                                            申明变量以及变量类型
begin
   number2 := number1;
   dbms_output.put_line(number2);                                             控制台输出值
end a;

2、if判断语句的使用

CREATE OR REPLACE PROCEDURE proc_if(credit IN NUMBER)
IS
str1 VARCHAR(300);
Begin
   IF(1=2) THEN
      Begin
          str1:='SELECT * FROM cog_schedule cs WHERE cs.CREDIT>'||credit;
          dbms_output.put_line(str1);
      END;
   ELSE 
      BEGIN
          str1:='SELECT * FROM cog_schedule cs WHERE cs.CREDIT<'||credit;
          dbms_output.put_line(str1); 
      END;
   END IF;
END proc_if; 

注意：带参数的sql语句尽量拼接成字符串，不然很容易出诡异的错误， “||”拼接连接符

3、创建一个包mypackage，并自定义一个ref cursor游标my_cursor

create or replace package mypackage as
  type my_cursor is ref cursor;
end mypackage;

4、列表返回于游标，游标是一个指针，指向oracle的私有的SQL区域，这个私有的SQL区域存储着查询select或者数据操纵语言（DML）语句像（INSERT\UPDATE\DELETE\OR MERGE）

CREATE OR REPLACE PROCEDURE C(number1 IN NUMBER,p_cursor OUT mypackage.my_cursor)
IS
BEGIN
 OPEN p_cursor FOR                打开游标
  SELECT * FROM cog_schedule cs WHERE cs.credit > number1;
END C;

5、使用for in 用cursor游标,遍历游标列表

CREATE OR REPLACE PROCEDURE B(number1 OUT NUMBER)   (显式的游标for循环)
IS
v_sum NUMBER;
CURSOR my_cursor IS SELECT * FROM cog_schedule;
BEGIN
 FOR cur_result IN my_cursor LOOP
  BEGIN
   v_sum := cur_result.credit + cur_result.class_hour;
   number1 := v_sum;
   dbms_output.put_line(v_sum);
  END;
 END LOOP;
END B;

6.while循环语句

CREATE OR REPLACE PROCEDURE B(number1 IN NUMBER,number2 OUT NUMBER)
IS
number3 NUMBER;
BEGIN
 number3 := 0;  初始化变量
 WHILE (number3 < number1) LOOP
  BEGIN
   number2 := number1;
   NUMBER3 := NUMBER3 + 1 ;
   dbms_output.put_line(number2);
  END;
 END LOOP;
END B;

7、带参数的cursor

CREATE OR REPLACE PROCEDURE A(number1 IN NUMBER)
IS
CURSOR p_cursor(cred NUMBER) IS SELECT * FROM cog_schedule cs WHERE cs.credit = cred;
number2 cog_schedule%ROWTYPE;
BEGIN
 OPEN p_cursor(number1);
 LOOP
    FETCH p_cursor INTO number2;
    EXIT WHEN p_cursor%NOTFOUND;
    dbms_output.put_line(number2.name);
 END LOOP;
 CLOSE p_cursor;
END A;