(1)普通变量
declare
-- 名字
V_NAME VARCHAR2(20) :='张三';
--薪水
V_sale Number;
--地址
V_address VARCHAR2(200);
begin
-- 直接赋值
V_sale :=1580;
--语句赋值
--打印结果
DBMS_OUTPUT.PUT_LINE('姓名:' || V_NAME || ',薪水' || V_sale || ',地址:' || V_address); 输出变量
end;
(2)引用变量
declare
V_id student.id%TYPE;
-- 名字
V_NAME student.name%TYPE;
--薪水
V_sale student.age%TYPE;
--地址
--V_address VARCHAR2(200);
begin
select name,age INTO V_NAME,V_sale FROM student WHERE ID=1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('捕获到异常');
DBMS_OUTPUT.PUT_LINE('姓名:' || V_NAME || ',薪水' || V_sale);
end;
(3)记录型变量
declare
--记录型变量
V_student STUDENT%Rowtype;
BEGIN
select * into V_student from STUDENT where id = 1 ;
--打印结果
DBMS_OUTPUT.PUT_LINE('姓名:' || V_student.Name || '年龄:' || V_student.age);
END;
(4)条件
declare
-- Local variables here
v_student student%Rowtype;
v_count number;
begin
-- Test statements here
select count(1) into v_count from student;
if v_count >1 then
dbms_output.put_line('数据大于1条');
elsif v_count <=1 then
dbms_output.put_line('数据小于等于1条');
end if;
end;
(5)循环
-- Created on 2019/3/8 by SX
declare
declare-- Local variables here
v_student student%Rowtype;
v_count number := 1;
begin
-- Test statements here
loop
exit when v_count > 10;
dbms_output.put_line('数字大于10,'|| v_count);
v_count := v_count + 1;
insert into student values(v_count,'张三',22);
end loop;
end;
(6)游标(无参游标)
-- Created on 2019/3/8 by SX
declare
--定义游标
cursor v_student is select name,age from student;
--定义变量和变量类型,空间大小
v_name student.name%type;
v_age student.age%type;
begin
--打开游标
open v_student;
--遍历游标
loop
--获取游标中的数据
fetch v_student into v_name,v_age;
exit when v_student%notfound;
dbms_output.put_line('姓名:' || v_name || '年龄:' || v_age);
--关闭遍历游标
end loop;
--关闭游标
close v_student;
end;
(7)游标(有参):和无参基本相同,唯一不同就是,有参针对的是可以传进来的参数,类似于sql中的where后面的条件
和无参的不同:
1、cursor v_student(v_id student.id%type) is select name,age from student where id=v_id;
2、open v_student(1)
(8)无参存储过程
顺序:
1 创建存储过程
create or replace procedure p_hello as
begin
DBMS_OUTPUT.put_line('hello world');
end p_hello;
2.创建第二个存储过程(Test Windows)
-- Created on 2019/3/10 by SX
begin
p_hello; --调用之前的存储过程
end;
(9)有参数存储过程
1.和无参存储过程一样
create or replace procedure p_hello(i_id in student.id%type) as
v_name student.name%Type;
v_age student.age%Type;
begin
select name,age into v_name,v_age from student where id=i_id;
DBMS_OUTPUT.put_line(v_name || '-' || v_age);
end p_hello;
2.创建第二个存储过程(Test Windows)
-- Created on 2019/3/10 by SX
begin
p_hello(1); --调用之前的存储过程
end;
(10)带有输入、输出参数的存储过程
1.
create or replace procedure p_hello(i_id in student.id%type,i_age out student.age%type) as
begin
select age into i_age from student where id=i_id;
end p_hello;
2.
declare
v_age student.age%type;
begin
p_hello(1,v_age);
dbms_output.put_line(v_age);
end;