1、背景
2、变量的声明
declare
-- 声明变量
v_name varchar2(20;
-- 此变量由 select into 赋值
v_man_sex number;
-- v_sex 变量的类型和 student表中的 sex 字段的类型一致
v_sex student.sex%TYPE;
-- v_row 中保存的是 student表中的一整行字段, 也可以是游标中的一整行
v_row student%rowtype;
-- 声明变量并赋值
v_addr varchar2(100 := '湖北省';
-- 声明日期变量
v_date date := sysdate;
-- 定义一个记录类型
type STUDENT_INFO is record
(
student_id student.student_id%TYPE,
student_name student.student_name%TYPE
;
-- 定义基于记录的嵌套表
type nested_student_info is table of STUDENT_INFO;
-- 声明变量
student_list nested_student_info;
begin
-- 直接赋值
v_name := '直接赋值';
v_date := to_date('2023-12-12', 'yyyy-mm-dd';
-- 单个字段语句赋值
select count(* into v_man_sex from student where sex = 1;
-- 多个字段赋值
select student_name,sex into v_name,v_sex from student where student_id = 'S003';
-- 获取一行数据 ( 此处需要查询出所有的字段,否则可能报错
select student_id,student_name,sex,CREATE_TIME into v_row from student where student_id = 'S002';
-- 打印输出
DBMS_OUTPUT.PUT_LINE('日期:' || v_date || '姓名:' || v_name || ',' || v_row.STUDENT_NAME || ' 男生人数:' || v_man_sex || ' 地址:' || v_addr ;
end;
3、if 判断
统计总共有多少个学生,并进行if判断。
declare -- 声明一个变量,记录有多少个学生 v_student_count number; begin -- 给 v_student_count 变量赋值 select count(* into v_student_count from student; -- 执行if判断 if v_student_count > 3 then DBMS_OUTPUT.PUT_LINE('当前学生数为: [' || v_student_count || ']>3'; elsif v_student_count >=2 then DBMS_OUTPUT.PUT_LINE('当前学生数为: [' || v_student_count || '] in [2,3]'; else DBMS_OUTPUT.PUT_LINE('当前学生数为: [' || v_student_count || ']<2'; end if; end;
4、case
-- case declare -- 声明一个变量,记录有多少个学生 v_student_count number; begin -- 给 v_student_count 变量赋值 select count(* into v_student_count from student; -- 执行if判断 case when v_student_count > 3 then DBMS_OUTPUT.PUT_LINE('当前学生数为: [' || v_student_count || ']>3'; when v_student_count >=2 then DBMS_OUTPUT.PUT_LINE('当前学生数为: [' || v_student_count || '] in [2,3]'; else DBMS_OUTPUT.PUT_LINE('当前学生数为: [' || v_student_count || ']<2'; end case; end;
5、循环
输出1到100
1、loop 循环
declare -- 定义一个变量并赋值 v_count number := 1; begin loop -- 提出条件 exit when v_count > 100; DBMS_OUTPUT.PUT_LINE('当前 count = ' || v_count; -- v_count 加1 v_count := v_count + 1; end loop; end;
2、while 循环
-- while 循环 declare -- 定义一个变量并赋值 v_count number := 1; begin while v_count <= 100 loop DBMS_OUTPUT.PUT_LINE('当前 count = ' || v_count; -- v_count 加1 v_count := v_count + 1; end loop; end;
3、for循环
-- for 循环 declare -- 定义一个变量 v_count number; begin for v_count in 1..100 loop DBMS_OUTPUT.PUT_LINE('当前 count = ' || v_count; end loop; end;
6、游标
1、无参数的游标
-- 游标 declare -- 声明一个游标 cursor cur_student is select student_id,student_name,sex from student; -- 声明变量 row_cur_student cur_student%rowtype; begin -- 打开游标 open cur_student; -- 遍历数据 loop -- 获取一行数据 fetch cur_student into row_cur_student; -- 退出 exit when cur_student%NOTFOUND; -- 执行业务逻辑(此句如果移动到exit when上方,则可能会多打印一句) DBMS_OUTPUT.PUT_LINE('studentId:' || row_cur_student.STUDENT_ID || ' studentName:' || row_cur_student.STUDENT_NAME; end loop; -- 关闭游标 close cur_student; end;
2、带参数的游标
declare -- 声明一个游标, 需要传递v_student_id参数 cursor cur_student(v_student_id student.student_id%TYPE is select student_id,student_name,sex from student where student_id = v_student_id; -- 声明变量 row_cur_student cur_student%rowtype; -- 此变量通过查询获取值,然后带到游标中 v_query_student_id student.student_id%TYPE; begin -- 打开游标 --参数传递方式一: open cur_student('S001'; -- 参数传递方式二: select 'S001' into v_query_student_id from dual; open cur_student(v_query_student_id; -- 遍历数据 loop -- 获取一行数据 fetch cur_student into row_cur_student; -- 退出 exit when cur_student%NOTFOUND; -- 执行业务逻辑(此句如果移动到exit when上方,则可能会多打印一句) DBMS_OUTPUT.PUT_LINE('studentId:' || row_cur_student.STUDENT_ID || ' studentName:' || row_cur_student.STUDENT_NAME; end loop; -- 关闭游标 close cur_student; end;
7、执行ddl dml
需要放到
execute immediate
中执行,否则会报错。declare v_table_name varchar2(20 := 'student_bak'; -- 拼接一个动态SQL v_sql varchar2(100; begin execute immediate 'create table student_bak as select * from student'; execute immediate 'alter table student_bak add new_cloumn varchar2(20'; -- 带变量的执行 v_sql := 'drop table ' || v_table_name; execute immediate v_sql; end;
8、存储过程
1、无参数的存储过程
-- 无参数的存储过程 create or replace procedure sp_print_all_student is -- 声明一个游标 cursor c_all_student is select student_id,student_name from student; -- 声明一个变量 row_student c_all_student%rowtype; begin -- 循环游标 for row_student in c_all_student loop DBMS_OUTPUT.PUT_LINE(row_student.STUDENT_ID || ' ' || row_student.STUDENT_NAME; end loop; end; -- 调用 begin SP_PRINT_ALL_STUDENT(; end;
2、有输入输出参数的存储过程
-- 有参数的存储过程 create or replace procedure sp_find_student(/** 输入参数 */ i_student_id in student.student_id%TYPE, /** 输出参数 */ o_student_name out student.student_name%TYPE IS -- 定义变量并赋值 v_student_id varchar2(64 := i_student_id; begin DBMS_OUTPUT.PUT_LINE('v_student_id:' || v_student_id; -- 将查询到的 student_name 赋值到 o_student_name select student_name into o_student_name from student where student_id = i_student_id; end; declare -- 定义一个变量用于接收存储过程的返回值 output_student_name student.student_name%TYPE; begin sp_find_student('S001', output_student_name; -- 输出存储过程的返回值 DBMS_OUTPUT.PUT_LINE(output_student_name; end;
3、merge into 的使用
存在更新,不存在插入。
create or replace procedure sp_merge_into(i_student_id in varchar2 IS begin -- 如果 using 中查询出来的数据,通过 on 条件匹配的话,则更新 student_bak表,否则插入student_bak表 merge into STUDENT_BAK t using (select * from student where student_id = i_student_id s on ( t.student_id = s.student_id when matched then update set -- t.STUDENT_ID = s.STUDENT_ID, on中的条件不可更新 t.STUDENT_NAME = s.STUDENT_NAME, t.SEX = s.SEX, t.CREATE_TIME = s.CREATE_TIME when not matched then insert(student_id, student_name, create_time values ( s.STUDENT_ID, s.STUDENT_NAME, s.CREATE_TIME ; commit ; end;
4、测试异常
create or replace procedure sp_error IS v_num number; begin DBMS_OUTPUT.PUT_LINE('测试异常'; -- 产生异常 v_num := 1 / 0; exception -- 存储过程异常 when too_many_rows then dbms_output.put_line('返回值多于1行'; when others then -- 异常处理方法,可以是打印错误,然后进行回滚等操作,下面操作一样,看自己情况决定 rollback; dbms_output.put_line('错误码:' ||sqlcode; dbms_output.put_line('异常信息:' || substr(sqlerrm, 1, 512; end; begin sp_error(; end;
5、bulk into & record
1、select into 中使用 bulk into & record
create or replace procedure sp_bulk_collect_01 IS -- 定义一个记录类型 type STUDENT_INFO is record ( student_id student.student_id%TYPE, student_name student.student_name%TYPE ; -- 定义基于记录的嵌套表 type nested_student_info is table of STUDENT_INFO; -- 声明变量 student_list nested_student_info; begin -- 使用 bulk collect into 将所获取的结果集一次性绑定到记录变量 student_list 中 select student_id,student_name bulk collect into student_list from student; -- 遍历 for i in student_list.first .. student_list.last loop DBMS_OUTPUT.PUT_LINE('studentId:' || student_list(i.student_id || ' studentName:' || student_list(i.student_name; end loop; end; begin sp_bulk_collect_01; end;
2、fetch into 中使用 bulk into & forall
-- bulk collect create or replace procedure sp_bulk_collect_02 IS -- 定义一个游标 cursor cur_student is select student_id,student_name,sex,create_time from student; -- 定义基于游标的嵌套表 type nested_student_info is table of cur_student%rowtype; -- 声明变量 student_list nested_student_info; begin -- 打开游标 open cur_student; loop -- 一次获取2条数据插入到 student_list 中 fetch cur_student bulk collect into student_list limit 2; -- 退出 --exit when student_list%notfound; 不可使用这种方式 exit when student_list.count = 0; -- 输出 for i in student_list.first .. student_list.last loop DBMS_OUTPUT.PUT_LINE('studentId:' || student_list(i.student_id || ' studentName:' || student_list(i.student_name; end loop; -- 使用 forall 更新数据, 可以将多个dml语句批量发送给SQL引擎,提高执行效率。 forall i in student_list.first .. student_list.last update student set student_name = student_list(i.STUDENT_NAME || '_update' where student_id = student_list(i.STUDENT_ID; commit ; end loop; -- 关闭游标 close cur_student; end; begin sp_bulk_collect_02; end;
6、接收数组参数
-- 创建StudentIdList数组的长度是4,每一项最多存20个字符 create or replace type StudentIdList as varray(4 of varchar2(20; -- 创建存储过程,接收数组参数 create or replace procedure sp_param_list(studentIdList in StudentIdList is begin for i in 1..studentIdList.COUNT loop DBMS_OUTPUT.PUT_LINE('studentId:' || studentIdList(i; end loop; end; declare begin sp_param_list(STUDENTIDLIST('d','c','S001','S0021222222222233'; end;
7、接收数组对象,并将数组对象转换成表使用
-- 创建数据库对象 create or replace type StudentInfo is object( studentId varchar2(64, studentName varchar2(64 ; -- 创建数组对象 create or replace type StudentInfoArr as table of StudentInfo; -- 创建存储过程 create or replace procedure sp_param_list_02(arr in StudentInfoArr is -- 声明一个变量,记录传递进来的arr的数量 v_student_count number := 0; begin -- 传递进来的数组转换成使用 select count(* into v_student_count from table(cast(arr AS StudentInfoArr where studentId like 'S%'; DBMS_OUTPUT.PUT_LINE('传递进来学生学号以S开头的学生有: ' || v_student_count || '个'; -- 输出列表参数 for i in 1..arr.COUNT loop DBMS_OUTPUT.PUT_LINE('studentId:' || arr(i.studentId || ' studentName:' || arr(i.studentName; end loop; end; declare begin sp_param_list_02(arr => StudentInfoArr(StudentInfo('S001','张三',StudentInfo('S002','李四'; end;
8、返回多个参数
create or replace procedure sp_return_value(stuInfoList out Sys_Refcursor IS begin open stuInfoList for select STUDENT_ID,STUDENT_NAME,SEX from STUDENT; end; declare stu Sys_Refcursor; v_student_id STUDENT.STUDENT_ID%TYPE; v_student_name STUDENT.STUDENT_NAME%TYPE; v_sex STUDENT.SEX%TYPE; begin SP_RETURN_VALUE( stu; loop fetch stu into v_student_id,v_student_name,v_sex; exit when stu%notfound; DBMS_OUTPUT.PUT_LINE('studentId:' || v_student_id || ' studentName: ' || v_student_name; end loop; end;
9、程序包 package
1、定义包头
包头
可以简单的理解java
中的接口。create or replace package pkg_huan as v_pkg_name varchar2(30 := 'pkg_huan'; function add(param1 in number, param2 in number return number; procedure sp_pkg_01; procedure sp_pkg_02(param1 in varchar2; end pkg_huan;
2、实现包体
包体
可以简单的理解java
中的实现接口
的类。create or replace package body pkg_huan as -- 实现function function add(param1 in number, param2 in number return number IS begin return param1 + param2; end; -- 实现无参数的存储过程 procedure sp_pkg_01 as begin DBMS_OUTPUT.PUT_LINE('package name:' || v_pkg_name || 'procedure name: sp_pkg_01'; end; -- 实现有参数的存储过程 procedure sp_pkg_02(param1 in varchar2 as begin DBMS_OUTPUT.PUT_LINE('param1:' || param1; end; end;
3、调用包中的方法或存储过程
begin -- 调用方法 DBMS_OUTPUT.PUT_LINE('1+2=' || PKG_HUAN.add(1,2; -- 调用无参数的存储过程 PKG_HUAN.sp_pkg_01(; -- 调用有参数的存储过程 PKG_HUAN.sp_pkg_02(12; end;
10、参考链接
1、http://www.cis.famu.edu/support/10g/Oracle_Database_10g/doc/appdev.102/b14261/objects.htm