PL/SQL学习笔记（二）
select * from protype;
select * from product;
---笛卡尔连接查询（交叉连接）
select * from protype,product;
select * from protype cross join product;---标准写法
---内链接查询
select * from protype pt,product pd where pt.pt_id=pd.p_type;
select * from protype pt inner join product pd on pt.pt_id=pd.p_type;---标准写法（inner可省略不写）
---外连接查询(outer均可省略)
--左外连接查询（左边全部显示，右边匹配显示，匹配不到则补空格）
select * from protype pt left outer join product pd on pt.pt_id=pd.p_type;
--右外连接查询（右边全部显示，左边匹配显示，匹配不到则补空格）
select * from protype pt right outer join product pd on pt.pt_id=pd.p_type;
--全外键连接查询（左右两边相互匹配显示，匹配不到相互补空格）
select * from protype pt full outer join product pd on pt.pt_id=pd.p_type;
--在产品信息表中显示产品类别名称
select pd.*,pt.pt_name from product pd left join protype pt on pd.p_type=pt.pt_id;
---创建视图：(创建视图前需要系统管理员对当前用户（模式、方案）进行授权：grant create view to x1;)
create view v_pt
as
select pd.*,pt.pt_name from product pd left join protype pt on pd.p_type=pt.pt_id;
--视图的使用：
select * from v_pt where p_id='137';
--查询当前用户拥有的所有视图：
select * from user_views;
---创建序列（等差数组）
create sequence myseq1
start with 5 ---从5开始
increment by 3 ---每次自增3
nomaxvalue ---不限制最大值
nocycle ---不循环（不回退）
cache 20; ---为了提高效率，预先缓存20个序列值
---------------------------
select myseq1.nextval from dual; ---获取下一个序列值
select myseq1.currval from dual; ---获取当前序列值（最近一次获取的值）
insert into protype values('B'||myseq1.nextval,myseq1.currval||'号产品类型'); ---利用序列向表格中插入不同编号
select * from protype;
alter sequence myseq1 increment by 1; ---修改序列（注意：序列初始值一旦定义则不可修改，其他参数均可修改）
---PL/SQL程序：
---入门示例：利用循环求1~100累加之和
declare --参数定义区
s integer :=0;
i integer;
begin --程序开始
i:=1;
loop --循环开始
if i>100 then --if条件判断（或用when：exit when i>100 ）
exit; --结束循环
end if;
s:=s+i;
i:=i+1;
end loop; --循环结束
dbms_output.put_line('s='||s); --控制台输出语句（如需在命令窗口输出需在程序末尾加''/"并开启服务器输出 ：set serveroutput on）
end; --程序结束
-----while循环-----------
declare
s integer :=0;
i integer;
begin -
i:=1;
while i<100 loop
s:=s+i;
i:=i+1;
end loop;
dbms_output.put_line('s='||s);
end; --程序结束
-----for循环-----------
declare
s integer :=0;
begin
for i in 1..100 loop
s:=s+i;
end loop;
dbms_output.put_line('s='||s);
end;
--------PL/SQL语句查询结果必须赋值（into cnt）否则报错-----------
declare
cnt integer;
begin
select count(*) into cnt from protype; --
dbms_output.put_line(cnt);
end;
----------查询一条记录并打印1---------------
declare
pid varchar2(20);
pname varchar2(20);
begin
select * into pid,pname from protype where pt_id='6666';
dbms_output.put_line(pid||','||pname);
end;
select * from protype;
----------查询一条记录并打印(简化)2---------------
declare
---定义一个记录类型：
type PT is record(
pid varchar2(20),
---取字段类型
pname protype.pt_name%type ---protype.pt_name%type:表示表protype中pt_name字段的类型
);
mypt PT; --定义一个PT类型的变量pt
begin
select * into mypt from protype where pt_id='6666';
dbms_output.put_line(mypt.pid||','||mypt.pname);
end;
------------查询一条记录并打印(简化)3-------------------------
declare
---table（表名）%rowtype:获取表中记录的类型
mypt protype%rowtype; ---定义表中记录（行）类型对象
begin
select * into mypt from protype where pt_id='6666';
dbms_output.put_line(mypt.pt_id||','||mypt.pt_name); ---记录类型对象调用字段名
end;
--------游标（代表一个查询出来的结果（集），其内部有读取记录的指针（默认指向第一行之前））---------
---1：游标是从数据表中提取出来的数据，以临时表的形式存放在内存中
---2：利用 fetch 语句可以移动游标内部的指针，从而对游标中的数据进行各种操作
---分类：
--->显式游标
--->隐式游标
--------------------------------------------显式游标-------------------------------------------------------------------
---------示例1：查询并打印产品类型表protype的所有记录------------
declare
--定义一个游标（常量）
cursor cur
is
select * from protype;
pt protype%rowtype;
begin
if not cur%isopen then ---如果游标未打开，则打开游标（isopen：游标的一个属性）
open cur;
end if;
loop
--将当前游标指向的记录赋给pt
fetch cur into pt; --fetch必须与into配套使用，即每次推动游标都必须将游标对应的记录赋值
-- exit when cur%rowcount >5; (只查询前5条记录，rowcunt:游标的一个属性)
exit when cur%notfound; --如果没找到游标则退出（notfound：游标的一个属性）
dbms_output.put_line(pt.pt_id||','||pt.pt_name);
end loop;
close cur; ---关闭游标
end;
--------示例二：游标(配合for循环)简化使用--------------
declare
cursor cur
is
select * from protype;
begin
for pt in cur loop --将查出来的记录类型自动匹配给pt，并且自动打开、关闭游标
dbms_output.put_line(pt.pt_id||','||pt.pt_name);
end loop;
end;
-----------------示例二另一种写法------------------
declare
begin
for pt in (select * from protype) loop --游标本身就是一个查询语句（的结果）
dbms_output.put_line(pt.pt_id||','||pt.pt_name);
end loop;
end;
-------------------示例三:带参数的游标------------------------------------
declare
cursor cur(pname varchar2) ---此处参数类型varchar2不用表明长度
is
select * from protype where pt_name like '%'||pname||'%';
begin
for pt in cur('2') loop
dbms_output.put_line(pt.pt_id||','||pt.pt_name);
end loop;
end;
-----------------------------------------------隐式游标-----------------------------------------------------------
---sql:隐式游标，表示刚刚执行过的sql查询结果（集）
declare
cnt integer;
begin
select count(*) into cnt from protype;
dbms_output.put_line(sql%rowcount); --此处sql为上行查询语句的执行结果
dbms_output.put_line(cnt);
end;
---------删除产品类型编号p_id为4位的记录方式一
declare
begin
delete from protype where pt_id like '____';
dbms_output.put_line(sql%rowcount); --此处sql为上行查询语句的执行结果
end;
---------删除产品类型编号p_id为4位的记录方式二
declare
begin
delete from protype where length(pt_id)=4;
dbms_output.put_line(sql%rowcount); --此处sql为上行查询语句的执行结果
end;
rollback;
select * from protype;
select * from v_pd;
---开发一个无参的存储过程：打印产品类型表的记录数
create or replace procedure myproc1
is
cnt integer;
begin
select count(*) into cnt from protype;
dbms_output.put_line(cnt);
end;
---测试调用存储过程myproc1
declare
begin
myproc1;
end;
--sqlplus命令窗口测试：
set serveroutput on; --开启控制台输出服务
execute myproc1; --execute执行该存储过程
exec myproc2; --execute 可简写为exec
--开发一个仅有输入参数的存储过程：按编号条件打印员工的记录数
create or replace procedure myproc2
(v_id in varchar2) --v_id 为输入参数
is
cnt integer;
begin
select count(*) into cnt from protype where pt_id like '%'||v_id||'%';
dbms_output.put_line(cnt);
end;
---测试调用存储过程myproc2
declare
begin
myproc2('1');
end;
--开发一个带有输出参数的存储过程：按编号条件查询员工的记录数
create or replace procedure myproc3
(v_id in varchar2,cnt out integer) --cnt 为输出参数
is
begin
select count(*) into cnt from protype where pt_id like '%'||v_id||'%';
end;
--测试带有输出参数的存储过程myproc3
declare
n integer;
begin
myproc3('1',n);
dbms_output.put_line(n);
end;
--开发一个有输出游标参数的存储过程
create or replace procedure myproc4
(cur out sys_refcursor) -- sys_refcursor为游标变量类型
is
begin
open cur for select * from protype;
end;
--测试
declare
cur sys_refcursor;
pt protype%rowtype;
begin
myproc4(cur);
loop
fetch cur into pt;
exit when cur%notfound;
dbms_output.put_line(pt.pt_id||','||pt.pt_name);
end loop;
close cur;
end;
--开发一个有返回游标的函数(函数必须要有返回值 )
create or replace function myfun1
(v_id in varchar2)
return sys_refcursor
is
cur sys_refcursor;
begin
open cur for select * from protype where pt_id like '%'||v_id||'%';
return cur;
end;
--测试
declare
cur sys_refcursor;
pt protype%rowtype;
begin
cur:=myfun1('1');
loop
fetch cur into pt;
exit when cur%notfound;
dbms_output.put_line(pt.pt_id||','||pt.pt_name);
end loop;
close cur;
end;
--触发器测试（利用触发器实现主键自增）
create table mytab(
t_id number primary key,
t_name varchar2(30)
);
create sequence myseq3
start with 100
increment by 1
nomaxvalue
nocycle
cache 20;
create or replace trigger mytab_pkauto
before insert on mytab
for each row
when(new.t_id is null) -- new 为关键字，代表了新记录，old代表旧记录
declare
begin
select myseq3.nextval into :new.t_id from dual;
end;
insert into mytab(t_name) values('kkkk');
select * from mytab;
