---PLSQL 调试授权
GRANT debug any procedure, debug connect session TO scott;
--定义变量
declare
part_number number(6); --SQL类型
part_name varchar2(20); --SQL类型
in_stock boolean; --plsql类型
part_price pls_integer; --plsql类型
part_description varchar2(50);
hours_worked integer:=40;
employee_count integer not null:=0;
hire_date date default sysdate; --使用DEFAULT设置初始值
begin
part_number:=38;
part_name:='水管';
in_stock:=True;
hours_worked:=50;
part_price:=100/part_number;
part_description:='镀银软管';
DBMS_OUTPUT.put_line(part_number||CHR(13)||part_name||chr(13)||hire_date||' '||hours_worked);
end;
--使用select into 语句为变量赋值
declare
v_sal emp.sal%type;
v_empno emp.empno%type;
v_deptno emp.deptno%type;
begin
select empno,sal,deptno
into v_sal, v_empno, v_deptno
from emp
where empno =7369;
dbms_output.put_line(v_sal ||' '||v_empno ||' '||v_deptno);
exception
when too_many_rows then
dbms_output.put_line('返回了多行数据');
end;
--定义常量
declare
credit_limit constant real:=5000.00; --指定信用额度
begin
dbms_output.put_line('信用额度'||credit_limit );
end;
--if ..elseif
declare
v_sal number;
v_comm number;
v_ename varchar2(20) :='SCOTT';
begin
select sal,comm into v_sal,v_comm from emp
where ename = v_ename;
if v_sal+v_comm <3000 then
update emp set sal=sal*1.12 where ename =v_ename;
elsif v_sal+v_comm>3000 and v_sal+v_comm<4000 then
update emp set sal=sal*1.1 where ename =v_ename;
elsif v_sal+v_comm>4000 and v_sal+v_comm<5000 then
update emp set sal=sal*1.05 where ename =v_ename;
end if;
exception
when no_data_found then
dbms_output.put_line('对史密斯调薪的操作失败,错误信息为:'||SQLERRM);
end;
--case选择器的用例
declare
grade char(1);
begin
grade :='';
case grade
when 'A' then
dbms_output.put_line('优秀');
when 'B' then
dbms_output.put_line('良好');
when 'C' then
dbms_output.put_line('好');
when 'D' then
dbms_output.put_line('一般');
else
dbms_output.put_line('无此等级');
end case;
end;
select mod(100,13) from dual;
--1简单循环
declare
x number:=0;
y number:=100;
begin
loop
dbms_output.put_line('循环中变量的值:x='|| to_char(x));
x:=x+1;
if x>20 then --如果x的值大于20, 则使用Exit退出循环
exit;
end if;
exit when y mod x>5; --y除以x的余数大于5,则退出循环
dbms_output.put_line('结果'|| y mod x);
end loop;
dbms_output.put_line('循环结束变量的值:x='||to_char(x));
end;
--2 continue when使用实例
declare
x number:=0;
begin
loop
dbms_output.put_line('循环计数变量:x='||to_char(x));
x:=x+1;
continue when x=4;
dbms_output.put_line('循环计数变量,在continue之后');
exit when x=5;
dbms_output.put_line('退出本循环变量的值:x='||to_char(x));
end loop;
end;
--简单的数字式For循环使用示例
create table books(bookid number(10),bname varchar2(40),price number(4));
alter table books modify bname varchar2(40);
begin
for i in 1..3 loop
insert into books values(1020+i,'矛盾文学第'||to_char(i)||'册',3);
dbms_output.put_line('插入了矛盾文学第'||to_char(i)||'册');
end loop;
end;
select * from books;
--反向for循环
begin
for i in reverse 1..5 loop
dbms_output.put_line('循环计数器为'||to_char(i));
end loop;
end;
--wile loop循环
declare
v_count pls_integer:=1;
begin
while v_count<=10 loop
dbms_output.put_line('当前循环计数器的值为:'||v_count);
v_count:=v_count+1;
end loop;
end;
select round(sqrt(37)) from dual;
declare
p varchar2(30);
n pls_integer:=37;
begin
for j in 2..round(sqrt(n)) loop
if n mod j=0 then
p:='不是一个素数';
goto print_now;
end if;
end loop;
p:='是一个素数';
<<print_now>>
dbms_output.put_line(to_char(n)||p);
end;
1.goto语句不能跳转到嵌套块内部的命名标记位置
2.不能在if子句的内部使用goto语句跳转到另一个if,case和loop语句内部
的命名标签
3.不能从一个exception块中使用goto跳转到块的其他区域
4.不能在exception外部使用goto语句跳转到exception内部;
--null 表示创建一个什么也不做的
declare
done boolean:=true;
begin
for i in 1..50 loop
if done then
goto end_loop;
end if;
<<end_loop>>
null;
end loop;
end;
--集合
--定义关联数组
declare
--雇佣日期索引集合
type hiredate_idxt is table of date index by pls_integer;
--部门编号集合
type deotno_idsxt is table of dept.deptno%type not null
index by pls_integer;
--记录类型的索引表, 这个结构允许在PL/SQL程序中创建一个本地副本
type emp_idxt is table of emp%rowtype
index by natural;
type deptname_idxt is table of dept%rowtype
index by dept.dname%type;
type private_collection_tt is table of deptname_idxt
index by varchar2(100);
begin
null;
end;
--定义与使用关联数组
Declare
--定义关联数组,元素类型varchar2(12),下标为PLS_INTEGER
type idx_table is table of varchar2(12)
index by pls_integer;
v_emp idx_table; --定义关联数组变量
v_idx PLS_INTEGER;
begin
v_emp (1):='史密斯';
v_emp (20):='克拉克';
v_emp (-10):='杰瑞';
v_idx:=v_emp.first; --获取关联数组中第一个元素的下标
while v_idx is not null
loop
dbms_output.put_line('关联数组'|| v_idx||'所在的值是'||v_emp(v_idx));
v_idx:=v_emp.next(v_idx);
end loop;
end;
--使用字符串下标的关联数组
declare
--定义以varchar2作为索引键的关联数组
type idx_empsal_table is table of number(8)
index by varchar2(20);
v_empsal idx_empsal_table;
begin
v_empsal('史密斯'):=5000;
v_empsal('李维二'):=8000;
v_empsal('张大千'):=3000;
dbms_output.put_line('工资为:'|| v_empsal('李维二'));
end;
嵌套表是对关联数组的扩展,
1 可以在方案级别创建,可以直接 存储在数据库表中作为表的一个字段
2 需要调用构造函数进行初始化
--使用嵌套表
declare
type emp_name_table is table of varchar2 (20);
type deptno_table is table of number(2);
deptno_info deptno_table;
emp_name_info emp_name_table:=emp_name_table('张老三','李斯特');
begin
dbms_output.put_line('员工1:'||emp_name_info(1));
dbms_output.put_line('员工2:'||emp_name_info(2));
if deptno_info is null
then
deptno_info:=deptno_table();
end if;
deptno_info.extend(5); --扩充元素的个数
for i in 1..5 --循环遍历元素个数
loop
deptno_info(i):=i*10;
end loop;
dbms_output.put_line('部门个数:'||deptno_info.count);
end;
--定义一个方案级别的嵌套表
create or replace type t_deptno_type is table of number;
/
create or replace procedure print_deptno(nt t_deptno_type)
is i number;
begin
i:=nt.first; --获取第1个元素的下标,如果不存在则返回null
if i is null then
dbms_output.put_line('嵌套表中未分配任何元素');
else
while i is not null loop
dbms_output.put('下标.('||i||')的部门编号是:');
dbms_output.put_line(nt(i));
i:=nt.next(i);
end loop;
end if;
dbms_output.put_line('---');
end print_deptno;
/
declare
nt t_deptno_type:=t_deptno_type(); --初始化一个空的嵌套表
begin
print_deptno(nt); --输出嵌套表的信息
nt:=t_deptno_type(90,9,29,58); --重新初始化嵌套表,使之具有4个元素
print_deptno(nt);
nt.delete(1); --删除嵌套表中下标为1的元素
--dbms_output.put_line(nt(1)); --01403异常
nt(1):=10;
end;
select * from dept_add_emp
--创建嵌套表列
create or replace type tbl_emp_name as table of varchar2(20);
create table dept_add_emp(
deptno number(2) primary key,
dname varchar2(14),
loc varchar2(13),
emp tbl_emp_name --object
)
nested table emp store as emps_nt; --嵌套表存储位置
--对包含嵌套表列的表执行DML语句
declare
emp_list tbl_emp_name
:=tbl_emp_name('史密斯','杰克','马丁','斯大林','*');
begin
insert into dept_add_emp
values (10,'行政部','北京',emp_list);
insert into dept_add_emp
values(20,'财务部','上海',tbl_emp_name('李林','张杰','蔡文'));
--对嵌套表进行更新,然后使用update语句将嵌套表更新回数据库
emp_list(1):='张三';
emp_list(2):='李四';
emp_list(3):='王五';
update dept_add_emp set emp=emp_list
where deptno=10;
--从数据库表中查询出嵌套表的实例
select emp into emp_list from dept_add_emp where deptno=10;
for v_index in 1..emp_list.count loop
dbms_output.put_line(emp_list(v_index));
end loop;
dbms_output.put_line('演示如何从其他表中插入嵌套表列的值');
--清除表中的数据
delete from dept_add_emp;
--使用inert select语句,将插入dept表中所有的记录,使用cast和multiset强转
--把emp表中的ename作为嵌套表的元素
insert into dept_add_emp
select dept.*,cast(multiset
(select ename from emp where emp.deptno=dept.deptno)
as tbl_emp_name) from dept;
select emp into emp_list from dept_add_emp where deptno=10;
for v_index in 1..emp_list.count loop
dbms_output.put_line(emp_list(v_index));
end loop;
end;
select * from dept_add_emp where deptno=20;
--取消集合嵌套,把它当做一个表来处理
select d.deptno,d.dname,emp.* from dept_add_emp d,
table(d.emp) emp where d.deptno=10;
--操作变长数组
declare
--定义变长数组类型
type t_dept_name is varray(10) of varchar2(20);
type t_dept_no is varray(8) of number;
--声明变长数组类型
varray_deptname_tab t_dept_name:=t_dept_name('行政部','管理部');
varray_deptno_tab t_dept_no;
begin
if varray_deptno_tab is null then
varray_deptno_tab:=t_dept_no(10,20,30,null,null,null);
end if;
varray_deptname_tab.extend(3); --在原有的基础上扩充三个元素
dbms_output.put_line('当前varray_deptname_tab个数:'||varray_deptname_tab.count);
varray_deptname_tab.trim; --删除一个
dbms_output.put_line('当前varray_deptname_tab个数:'||varray_deptname_tab.count);
varray_deptname_tab.extend;
varray_deptname_tab(5):='发展部';
dbms_output.put_line(varray_deptname_tab(5));
--这行代码超过了变长数组最大长度,抛出06533异常
--varray_deptno_tab.extend(5)
end;
--数据库中的变长数组
create or replace type empname_varray_type is varray (10) of varchar2 (20);
create table dept_varray(
deptno number(2),
dname varchar2(20),
emplist empname_varray_type
);
declare
emp_list empname_varray_type:=
empname_varray_type('诸葛亮','司马懿','郭嘉','庞统','许攸');
begin
insert into dept_varray
values(20,'军师',emp_list);
insert into dept_varray
values(30,'武将',
empname_varray_type('关羽','张飞','赵云','魏延','黄忠'));
--从表中取出变长数组的数据
select emplist into emp_list from dept_varray where deptno=20;
emp_list(1):='姜维';
update dept_varray
set emplist=emp_list
where deptno =20;
--删除记录同时删除变长数组
--delete from dept_varray where deptno=30;
end;
select * from dept_varray;
--布尔类型使用示例
declare
v_condition boolean;
begin
v_condition:=true;
if v_condition then
dbms_output.put_line('值为True');
else
dbms_output.put_line('值为false');
end if;
end;
--子类型定义,数值检查
declare
type empnamelist is table of varchar2(20);
subtype namelist is empnamelist; --定义表类型的子类型
type emprec is record(
empno number(4),
ename varchar(20)
);
subtype emprecord is emprec; --定义员工记录子类型
subtype numtype is number(1,0);
x_value numtype;
y_value numtype;
begin
x_value:=3;
y_value:=9;
end;
--数据类型转换
--显示转换示例
declare
v_startdate date;
v_enddate date;
v_resultdate number;
begin
v_startdate :=to_date('2007-10-11','yyyy-mm-dd');
v_enddate:=trunc(sysdate);
v_resultdate:=v_enddate-v_startdate;
dbms_output.put_line( ' 起始'||v_startdate||'日期: '||
to_char(v_startdate,'yyyy-mm-dd')
||chr(13)||chr(10)||'结束'||v_enddate||'日期: '||
to_char(v_enddate,'yyyy-MM-dd')
||chr(13)
||chr(10)
||' 相差天数: '
|| to_char(v_resultdate));
end;
--隐式转换示例
declare
v_startdate char(10);
v_enddate char(10);
v_result number(5);
begin
select min(hiredate) into v_startdate from emp;
select trunc(sysdate) into v_enddate from dual;
dbms_output.put_line( ' 起始日期: '
|| v_startdate||chr(13)||chr(10) ---chr(10)换行chr(13)回车
||' 结束日期: '||v_enddate );
dbms_output.put('chr'||chr(13));
v_startdate:='';
v_enddate:='';
v_result:=v_enddate-v_startdate;
end;
--检索case语句
declare
v_sal number(10,2);
v_empno number(10) :=&empno;
begin
select sal into v_sal from emp
where empno=v_empno;
case
when v_sal between 1000 and 1500
then
dbms_output.put_line('员工级别:初级职员');
when v_sal between 1500 and 3000
then
dbms_output.put_line('员工级别:中级管理');
when v_sal between 3000 and 5000
then
dbms_output.put_line('员工级别:高级管理');
else
dbms_output.put_line('不在级别范围之内');
end case;
end;
--case语句后面的值是可选的 True和 False ,默认值为True
--使用continue重新开始循环
declare
x number:=0;
begin
loop
dbms_output.put_line('内部循环值:x+'||to_char(x));
x:=x+1;
if x<3
then
continue;
end if;
dbms_output.put_line('countiue之后的值:x='|| to_char(x));
exit when x=5;
end loop;
dbms_output.put_line('循环体结束后的值:x='||to_char(x));
end;
loop-end-loop循环有一个特色, 无论循环退出条件是否满足
先进入循环体,再执行代码,直到遇上exit或exit when子句才
判断并退出循环 代码至少有机会被执行一次称为
出口值守循环, 见图1
while-loop循环在执行体中代码之前先判断一个条件,如果
一开始就为假, 那么一次也不执行代码,这种类型的循环称为
入口值守循环
循环的功能特性与使用时机 图2
--GOTO语句模拟循环语句
declare
v_counter int:=0;
begin
<<outer>>
DBMS_OUTPUT.put_line('循环计数器:'|| v_counter);
if v_counter<5
then
v_counter:=v_counter+1;
goto outer; --向上跳转到标签位置
end if;
end;
--NULL 语句
declare
v_counter INT:=&counter;
begin
if v_counter>5
then
dbms_output.put_line('v_counter>5');
else
null;
end if;
end;
--在异常语句块中使用null
declare
v_result int:=0;
begin
v_result :=16/0;
dbms_output.put_line('现在时间是:'||
to_char(sysdate,'yyyy-MM-dd HH24:MI:SS'));
exception
when others
then
null;
end;
1 有时候希望代码在遇到异常时继续执行,
并不处理异常,此时可以使用NUll语句
2 使用NULL来创建存根代码,有需要的时候再使用
创建一个存根过程,不包含任何程序代码,以便处理程序调试
create or replace procedure getleveledbom(bomlevel INT)
AS
BEGIN
NULL;
END;
--count方法示例
declare
type emp_name_table is table of varchar2(20);
type deptno_table is table of number(2);
deptno_info deptno_table;
emp_name_info emp_name_table :=emp_name_table('张三','李斯特');
begin
deptno_info:=deptno_table();
deptno_info.extend(5); --扩充5个元素
dbms_output.put_line('deptno_info的元素个数为:'||deptno_info.count);
dbms_output.put_line('emp_name_info的元素个数为:'||emp_name_info.count);
end;
--LIMIT方法示例
declare
type projectlist is varray(50) of varchar2(16);
project_list projectlist:=projectlist('网站','ERP','CRM','CMS');
begin
dbms_output.put_line('变长数组的上限值为:' || project_list.LIMIT);
project_list.extend(8);
dbms_output.put_line('变长数组的当前个数为:' || project_list.count);
end;
--first和last示例
declare
type projectlist is varray (50) of varchar2(16);
project_list projectlist :=projectlist('网站','ERP','CRM','CMS');
begin
dbms_output.put_line('project_list的第一个元素:'||project_list.first);
project_list.extend(8);
dbms_output.put_line('project_list的最后一个元素的下标:'||project_list.last);
end;
--prior和next示例
declare
type idx_table is table of varchar(12) index by pls_integer;
v_emp idx_table;
i pls_integer;
begin
v_emp(1) :='史密斯1';
v_emp(20) :='克拉克2';
v_emp(40):='史瑞克3';
v_emp(-10):='杰瑞4';
dbms_output.put_line('第-10个元素的下一个值:'||v_emp(v_emp.next(-10)));
dbms_output.put_line('第40个元素的上一个值:'||v_emp(v_emp.PRiOR(40)));
i :=v_emp.FIRST;
while i is not null
loop
dbms_output.put_line('v_emp('||i||')='||v_emp(i));
i :=v_emp.next(i); --遍历
end loop;
end;
--extend使用示例
declare
type courselist is table of varchar2(10);
courses courselist;
i pls_integer;
begin
courses:= courselist('生物','物理','化学');
courses.delete(3); --删除第三个元素
courses.extend; --追加一个空元素
courses(4):='英语';
courses.extend(5,1); --把第一个元素复制5份加到末尾
i:=courses.First;
while i is not null loop
dbms_output.put_line('courses('||i||')=' ||courses(i));
i:=courses.next(i);
end loop;
end;
--TRIM示例
declare
type courselist is table of varchar2(10);
courses courselist;
i PLS_INTEGER;
begin
courses := courselist('生物','物理','化学','音乐','数学','地理');
courses.trim(2); --删除集合末尾的两个元素
dbms_output.put_line('当前的元素个数:'||courses.count);
courses.extend;
courses(courses.count):='语文';
courses.trim;--删除最后一个 (语文)
i:=courses.first;
while i is not null loop
dbms_output.put_line('courses('||i||')='||courses(i));
i:=courses.next(i);
end loop;
end;
--delete使用示例
declare
type courselist is table of varchar2(10);
courses courselist;
i pls_integer;
begin
courses :=courselist('生物','物理','化学','音乐','数学','地理');
courses.delete(2); --删除第二个元素
dbms_output.put_line('当前的元素个数:'||courses.count);
courses.extend;
dbms_output.put_line('当前的元素个数:'||courses.count);
courses(courses.last):='语文'; --为最后一个元素赋值
courses.delete(4);
i:=courses.FIRST;
while i is not null loop
dbms_output.put_line('courses('||i||')='||courses(i));
i:=courses.next(i);
end loop;
end;
使用批量绑定
编写PLSQL代码,PLSQL引擎与SQL引擎频繁交互会大大降低效率
--forall语句示例
--将集合中所有元素批量地绑定,以便一次性将多个绑定到SQL语句的变量发给SQL引擎
一次性发送给SQL引擎.
declare
type dept_type is varray (20) of number;
depts dept_type :=dept_type(10,30,70);
begin
forall i in depts.first..depts.last
delete from emp where deptno=depts(i);
for i in 1..depts.count loop
dbms_output.put_line('部门编号'||depts(i)
||'的删除操作受影响的行: '||sql%bulk_rowcount(i));
end loop;
end;
-bulk collect关键字可以批量从SQL引擎中批量接受数据到一个集合
declare
type numtab is table of emp.empno%type;
type nametab is table of emp.ename%type;
nums numtab;
names nametab;
begin
select empno,ename
bulk collect into nums,names from emp;
for i in 1..nums.count
loop
dbms_output.put('num('||i||')='||nums(i)||' ');
dbms_output.put_line('names('||i||')='||names(i));
end loop;
end;
--使用游标的属性isopen
declare
cursor emp_cursor(p_deptno in number)
is select * from emp where deptno =p_deptno;
begin
if not emp_cursor%isopen then
open emp_cursor (20);
end if;
if emp_cursor%isopen then
dbms_output.put_line('游标已经被打开!');
else
dbms_output.put_line('游标还没有被打开!');
end if;
close emp_cursor;
end;
--%not found
declare
emp_row emp%rowtype;
cursor emp_cursor(p_deptno in number)
is select * from emp where deptno=p_deptno;
begin
open emp_cursor(20);--给一个游标传递参数
if emp_cursor%notfound is null
then
dbms_output.put_line('%notfound属性为null');
end if;
loop
fetch emp_cursor
into emp_row;
--每循环一次判断%found属性值,如果该值为False,表示提取完成将退出循环
exit when emp_cursor%notfound;
end loop;
close emp_cursor;
end;
--%rowcount 属性
declare
emp_row emp%rowtype;
cursor emp_cursor(p_deptno in number)
is select * from emp where deptno=p_deptno;
begin
open emp_cursor(20);
loop
fetch emp_cursor into emp_row;
exit when emp_cursor%notfound;
dbms_output.put_line('当前提取的的行数为: '||emp_cursor%rowcount
||'行!');
end loop;
end;