第一部分 基本概念
一、查询系统表select * from user_tables 查询当前用户所有表
select * from user_triggers 查询当前用户所有触发器
select * from user_procedures 查询当前用户所有存储过程
二、分组函数的用法max()
min()
avg()
sum()
count()
rollup()
注意：
1)分组函数只能出现在 group by ,having,order by 子句中，并且order by 只能放在最后
2)如果选择列表中有列，表达式和分组函数，那么列，表达式必须要出现在group by 函数中
3)当限制分组显示结果时，必须出现在having子句中，而不能在where子句中
rollup 函数 函数用于形成小合计
Group by 只会生成列相应数据统计
select deptno,job,avg(sal) from emp group by deptno,job;
rollup 会在原来的统计基础上，生成小统计
select deptno,job,avg(sal) from emp group by rollup (deptno,job);
显示每个岗位的平均工资，每个部门的平均工资，和所有雇员的平均工资
cube 提供按多个字段汇总的功能
显示每个部门每个岗位的平均工资，每个岗位的平均工资，每个部门的平均工资，和所有雇员的平均工资
select deptno,job,avg(sal) from emp group by cube (deptno,job);
多种分组数据结果 grouping sets 操作
显示部门平均工资
select deptno,avg(sal) from emp group by emp.deptno,
显示岗位平均工资
select job,avg(sal) from emp group by emp.job
显示部门和岗位平均工资，合并上面两个结果
select deptno,job,avg(sal) from emp group by grouping sets(emp.deptno,emp.job);
等价与:
select deptno,null,avg(sal) from emp group by emp.deptno
union all
select null,job,avg(sal) from emp group by emp.job
三 (+) 连接+ 只能用在where子句中，而且放在显示较少的行那一边，不能和outer join语法同时使用
+ 如果在where 子句中有多个条件，每个条件都必须加上+
+ 不能用于表达式，只能用于列，不能和in or一起使用。
四 字符函数instr() substr() ltrim()
lower('SQL server') sql server 都转换为小写字符
upper('morning') MORNING 都转换为大写字符
select instr('monring','ng') from dual; 子字符串在字符串中的位置 返回6
ltrim() 从左端扫描去掉出现字符set的字符，直到遇到不是set的值返回
select ltrim('Mmoning' 'M') from dual;
返回moning
substr('monring',1,3) 取到子字符串,第二个参数为起始位置，3为长度 结果返回mon
第二部分 pl/sql 基础
一、pl/sql 数据类型boolean 类型定义，
v_bflag boolean not null default false;
常量定义
v_tax_rate constant number(3,2):=0.03
定义数组
type arrays is table of varchar2(200);
使用 %type类型
使用这个属性时候，它会按照数据库列或其它变量来确定新变量的类型和长度
v_name emp.name%type
1)单行多列 使用pl/sql 记录 a.自定义
type emp_record_type is RECORD(
name emp.name%TYPE,--定义成emp表中name的数据类型
sal emp.sal ,
);
emp_record emp_record_type;
使用：
emp_record.name
b.定义行记录
dept_record dept%ROWTYPE
2)为了处理多行单列，多行多列使用pl/sql集合如： 索引表，嵌套表，varray a) pl/sql表(索引表)。只能用在pl/sql中同种类型的一维、无边界的同类元素稀疏集合，且只能是由一列组成。
定义：type ename_table_type is table of varchar2(200) index by binary_integer;
或者type ename_table_type is table of emp.ename%type index by binary_integer;
声明：ename_table ename_table_type;
select ename into arrays(-1) from emp where empno=7788 把员工姓名放入 下标为-1的空间。
b) 嵌套表 也是一维、无边界稀疏集合
定义：type ename_table_type is table of varchar2(200);
或者type arrays is table of emp%name;
嵌套表的下标从1开始，也是属于稀疏集合，和索引表的区别除了开始下标以为，而且嵌套表是可以做为 可以做为表列的数据类型，
而索引表不可以。并且必须使用初始化构造函数初始化
emp_array ename_table_type=:ename_table_type();//构造方法初始化
eg:
type emp_table_type is table of emp.name%type;
emp_table emp_table_type:=emp_table_type();
select ename into emp_table(2) from emp where empno='1779';
dbms_output.putLine(emp_table(2));
结果：scott
--定义数组类型
type arrays is table of varchar2(200);
--初始化
dataList arrays := arrays();
--作为参数传递
procedure pro_name(dataList is arrays) is
begin
end ;
/*
用指定字符将字符串分隔为数组
str 字符串
str_split 分割字符
return 分割后的数组
*/
function splitString(sourceString varchar2, splitFlag varchar2)
return arrays is
dataList arrays := arrays(); --一定要，构造函数初始化
temp varchar2(200); --存储临时数据
tempSource varchar2(1000) := sourceString; --存储需要分割的字符串()
splitIndex number(2); --分隔符在字符串中首次出现的位置
splitLength number(2) := length(splitFlag); --分割符长度
begin
while instr(tempSource, splitFlag) > 0 loop
splitIndex := instr(tempSource, splitFlag);
--取出第一个分割符前的字符串
temp := substr(tempSource, 1, splitIndex - splitLength);
--减去第一个分隔符前的字符串
tempSource := substr(tempSource,
splitIndex + splitLength,
length(tempSource));
if temp is not null then
dataList.extend(1);
dataList(dataList.count) := temp;
end if;
end loop;
if tempSource is not null then
dataList.extend(1);
dataList(dataList.count) := tempSource;
end if;
return dataList;
end splitString;
c) varray 变长数组 做为表列的数据类型，并且必须指定数组的大小
TYPE t_name IS VARRAY(2) OF VARCHAR2 (10); 定义了一个字符类型的数组，并且只能存储两个元素
存储对象类型：
create type article_type as OBJETC(
title varchar2(20),
pubDate DATE
)
create type article_array is varray(2) of article_type
可以当作用户自定义数据类型来引用：
create table author(
id number,
name varchar2,
arti article
)
这些集合内置的一些方法：
COUNT 返回集合中元素的个数
DELETE 删除集合中所有元素
DELETE(x) 删除元素下标为x的元素 对VARRAY非法
DELETE(x,y) 删除元素下标从X到Y的元素 对VARRAY非法
EXIST(x) 如果集合元素x已经初始化，则返回TRUE, 否则返回FALSE
EXTEND 在集合末尾添加一个元素 对Index_by非法
EXTEND(x) 在集合末尾添加x个元素 对Index_by非法
EXTEND(x,n) 在集合末尾添加元素n的x个副本 对Index_by非法
FIRST 返回集合中的第一个元素的下标号，对于VARRAY集合始终返回1。
LAST 返回集合中最后一个元素的下标号, 对于VARRAY返回值始终等于COUNT.
3) 为了处理多行多列 pl/sql 记录表
type emp_table_type is table(
)
二、控制结构 分支，循环，顺序
if then
statement;
else
statement;
end if;
case 在多重分支应用
case op
when exp1 then;
when exp2 then;
循环
基本循环至少执行一次
loop
statement;
exit(when condition) condition为true 退出循环
end loop
只有条件为真时才执行循环体
while condition loop
statement
end loop;
for int i in 1..N loop --循环变量由oracle隐式定义，不需要显示定义
statement
end loop;
三、游标(1) 游标类型分为静态游标和动态游标
1)静态游标
静态游标又分为隐式和显示两种
a、隐式游标(开发人员没有为sql语句显示声明一个游标)是当你在执行DML操作返回单行记录时(update insert delete)
会自动声明一个隐式游标。
隐式游标属性:
SQL%found, sql语句执行是否成功，如果作用行则为true,如果没有则为false
SQL%notfound,
SQL%rowcount sql语句作用的总行数。
SQL%isopen
b、显示游标 返回多行。开发中一般都用显示游标。
带参数的显式游标
cursor name_cur(com_id in number)
is select name from company where company_id = com_id;
使用
for temp_cur in name_cur(0) loop
end loop;
带返回值的显式游标
cursor emp_cur return employee%rowType --游标声明
is select * from employee --游标体
where department_id =10
2)动态游标
(1)动态游标例子
strSql:=' select tvi.gap ';
strSql:=strSql||' from TEMP_VD_RP_AJUST tvi ';
strSql:=strSql||' where tvi.week in('||nWeeks||')';
strSql:=strSql||' group by tvi.ship_to_code, tvi.product_name, tvi.week, tvi.gap ';
open gapCur for strSql;
loop
fetch gapCur into gap;
exit when gapCur%notfound; --没有记录退出
if(gap<0) then
bFlag := true;
exit; --只要存在一个为负值，退出
end if;
end loop;
close gapCur;
(2) 使用游标更改数据
使用显示游标在更改当前游标行的时候必须带for update
cursor cur_name is select * from emp for update(of column)
在插入或者删除子句中必须加where current of cur_name;
eg: 给工资少于2000的员工加100工资
cursor emp_cur is select emp.name,emp.sal form emp for update;
for temp_emp fro emp_cur loop;
if(emp.sal<2000) then
update emp set emp.sal=sal+100 where current of emp_cur;
end if;
end loop;
(3) 游标变量
声明一个引用数据库中游标对象的变量，该变量可以在不同时间指向不同的sql语句，
游标变量是指向多行查游标询的结果集的当前行,游标是静态的,游标变量是动态的
游标变量并不参与与特定的查询绑定,所以可以为任何兼容的查询打开游标变量
还可以将新的值赋予游标变量 , 将它作为参数传递给本地和存储过程。
注 : 游标总是指向相同的查询工作区 , 游标变量能够指向不同的工作区 , 因此游标和游标变量不能互操作
type cur_type is ref cursor return emp%rowType;
emp_cur = cur_type;
open emp_cur for select * from emp;
loop
fetch emp_cur into v_1,v_2;
end loop;
close emp_cur;
emp_cur 可以为不同的sql查询，每次都执行查询结果集合的第一行。
注意 :
REF CURSOR 类型既可以是强类型 , 也可以是弱类型 , 区别是强类型有返回类型 , 弱类型没有
当作为函数参数传递
PROCEDURE open_emp (emp_cur IN OUT cur_type)IS
begin
open emp_cur for select * from emp;
end
定义游标变量的例子：
type rc is ref cursor;
cursor c is select * from dual;
l_cursor rc;
begin
if ( to_char(sysdate,'dd') = 30 ) then
open l_cursor for 'select * from emp';
elsif ( to_char(sysdate,'dd') = 29 ) then
open l_cursor for select * from dept;
else
open l_cursor for select * from dual;
end if;
open c;
end;
rc根据逻辑动态打开；而游标c定义好了只有就无法修改了。
ref cursor可以返回给客户端，cursor则不行。
cursor可以是全局的global ，ref cursor则必须定义在过程或函数中。
ref cursor可以在子程序间传递，cursor则不行。
cursor中定义的静态sql比ref cursor效率高，所以ref cursor通常用在向客户端返回结果集。
(4) 定义游标
显示游标 返回多行。开发中一般都用显示游标
提取游标数据
fetch emp_cur into variable1,variable2
提取所有数据
fetch..bulk collect into
eg:
type emp_table is table of varchar2(10)
fetch emp_cur bulk collect into emp_table
for i in 1.. emp_table.count loop
dbms_output.putLine(emp_table(i));
end loop;
使用游标属性
emp_cur%isopen,
emp_cur%found, --是否从结果集中提取到了数据
emp_cur%notfound
emp_cur%rowcount --当前已经提取到的实际行数
隐式游标,自动做了打开关闭
type cursor emp_cur is select * from emp_cur;
emp_record dept%ROWTYPE
for emp_record in emp_cur loop
dbms_output.putLine(emp_record.name);
end loop;
四、动态sql 注意：静态sql不能处理DDL操作
(1) using 子句 默认的绑定参数类型是in
可以使用参数 using param1,param2
procedure execImmediate is
strSql varchar2(200);
begin
strSql:='select count(*) from emp t where t.ename = :1 ';
execute immediate strSql using 'SCOTT';
end execImmediate;
![龙珠训练营task04[图]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)