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;