Oracle存储过程

1.打印hello world：

1 begin
2 -- 打印输出语句：dbms_output.put_line()
3 dbms_output.put_line('hello word');
4 end;

2.普通变量：

 1 eg：打印姓名、薪水、地址
 2 declare 
 3 -- 赋值使用--->:=
 4 -- 定义时直接赋值
 5 name varchar(20) := '张三';
 6 sal number;
 7 addr varchar(200);
 8 begin
 9 -- 直接赋值
10 sal := 1500;
11 -- 语句赋值
12 select '上海传智播客' into addr from dual;
13 dbms_output.put_line('姓名：'||name||',薪水：'||sal||',地址：'||addr);
14 end;

3.引用型变量：取决于表中字段的类型和长度：
　　语法：表名.列名%type

 1 eg：查询student表中01号学生的个人信息，打印姓名和薪水
 2 declare
 3 -- 普通类型变量
 4 -- name varchar(20);
 5 -- sal number;
 6 -- 引用型变量
 7 name student.sname%type;
 8 sal student.sid%type;
 9 begin
10 select sname,sid into name,sal from student where sid = 01;
11 dbms_output.put_line('姓名：'||name||',薪水：'||sal);
12 end;

4.记录型变量：接受表中的一整行变量：
　　语法：变量名称 表名%rowtype

1 eg：查询student表中01号学生的个人信息，打印所有信息
2 declare
3 -- 记录型变量
4 emp student%rowtype;
5 begin
6 select * into emp from student where sid = 01;
7 dbms_output.put_line('姓名：'||emp.sname||',学号：'||emp.sid);
8 end;

5.流程控制：
　　条件分支：
　　　语法：
　　　　begin
　　　　　if 条件1 then 执行1
　　　　　　elsif 条件2 then 执行2
　　　　　　else 执行3
　　　　　end if；
　　　　end；

 1 eg：判断student表中记录是否超过20条。10-20之间，或者10条一下
 2 declare
 3 -- 声明变量接受student表中的数量
 4 v_count number;
 5 begin
 6 select count(1) into v_count from student ;
 7 if v_count > 20 then
 8 dbms_output.put_line('student表中的记录超过20条：'||v_count);
 9 elsif v_count >= 10 then
10 dbms_output.put_line('student表中的记录在10-20条：'||v_count);
11 else
12 dbms_output.put_line('student表中的记录在10条内：'||v_count);
13 end if;
14 end;

　　循环：
　　　语法：
　　　　begin
　　　　　loop
　　　　　　exit when 退出循环条件
　　　　　end loop；
　　　　end；

 1 eg：打印数字1-10
 2 declare
 3 -- 声明循环变量
 4 num number := 1;
 5 begin
 6 loop
 7 exit when num > 10;
 8 dbms_output.put_line(num);
 9 -- 循环变量自增
10 num := num + 1;
11 end loop;
12 end;

6.游标：临时存储一个查询返回的多行数据，使用方式-->声明、打开、读取、关闭
　　语法：
　　　游标声明；
　　　　cursor 游标名[(参数列表)] is 查询语句；
　　　游标打开：
　　　　open 游标名;
　　　游标取值：
　　　　fetch 游标名 into 变量列表;
　　　游标关闭：
　　　　close 游标名;
　　　游标属性：
　　　　%rowcount、%found、%notfound、%isopen
　　注意：%notfound属性默认值为flase，所以在循环中要注意判断条件的位置

 1 eg：使用游标查询student表中所有学生信息，并依次打印
 2 declare
 3 -- 声明游标
 4 cursor c_emp is select sid,sname from student;
 5 -- 声明变量，接受游标中的元素
 6 sid student.sid%type;
 7 sname student.sname%type;
 8 begin
 9 -- 打开游标
10 open c_emp;
11 -- 遍历游标
12 loop
13 -- 获取游标中的数据
14 fetch c_emp into sid,sname;
15 exit when c_emp%notfound;
16 dbms_output.put_line(sid||' - '||sname);
17 end loop;
18 -- 关闭游标
19 close c_emp;
20 end;
21 
22 eg：使用游标查询并打印某年龄的学生的姓名和学号，年龄为运行时手动输入
23 declare
24 -- 有参游标
25 cursor c_emp(v_sage student.sage%type) is select sid,sname from student where sage = v_sage;
26 sid student.sid%type;
27 sname student.sname%type;
28 begin
29 -- 打开游标，传入参数
30 open c_emp(20);
31 loop
32 fetch c_emp into sid,sname;
33 exit when c_emp%notfound;
34 dbms_output.put_line(sid||' - '||sname);
35 end loop;
36 close c_emp;
37 end;

View Code

7.存储过程：
　　语法：
　　　create or replace procedure 过程名称[(参数列表)] is/as
　　　begin
　　　　end [过程名称];
　　无参存储

 1 eg：通过调用存储过程打印hello world
 2 create or replace procedure p_hello is
 3 -- 声明变量
 4 begin
 5 dbms_output.put_line('hello world');
 6 end p_hello;
 7 
 8 -- 在测试窗口中调用存储过程
 9 begin
10 -- 调用存储过程
11 p_hello;
12 end;

　　带参存储过程

 1 eg：查询并打印某同学的姓名和学号
 2 create or replace procedure p_stu(i_sid in student.sid%type) is
 3 v_name student.sname%type;
 4 v_sid student.sid%type;
 5 begin
 6 -- 查询student表中某个学生的姓名和学号并赋给变量
 7 select sid,sname into v_sid,v_name from student where sid = i_sid;
 8 dbms_output.put_line(v_sid||' - '||v_name);
 9 end;
10 
11 -- 调用存储过程
12 begin
13 p_stu(2);
14 end;

　　带输出存储过程

 1 eg：输入学号查询某个学生信息
 2 create or replace procedure p_stu_out(i_sid in student.sid%type,o_name out student.sname%type) is
 3 begin
 4 -- 查询student表中某个学生的姓名和学号并赋给变量
 5 select sname into o_name from student where sid = i_sid;
 6 end;
 7 
 8 -- 调用存储过程
 9 declare
10 -- 声明变量接收存储过程中的输出参数
11 name student.sname%type;
12 begin
13 p_stu_out(2,name);
14 dbms_output.put_line(name);
15 end;

View Code