1、背景
此处简略的记录一下在 oracle中如何应用plsql语法，记录一些简略的例子，避免当前遗记。
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
-- casedeclare -- 申明一个变量，记录有多少个学生 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_studentis -- 申明一个游标 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)ISbegin -- 如果 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_errorIS 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_01IS -- 定义一个记录类型 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 collectcreate or replace procedure sp_bulk_collect_02IS -- 定义一个游标 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)isbegin 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;declarebegin sp_param_list_02(arr => StudentInfoArr(StudentInfo('S001','张三'),StudentInfo('S002','李四')));end;8、返回多个参数
create or replace procedure sp_return_value(stuInfoList out Sys_Refcursor)ISbegin 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