pl/spl编程
1.pl/sql的特点:
– 1).结构化,模块化;
– 2).预加载大量sql指令,提高效率;
– 3).逻辑结构: 顺序 分支 循环
– 4).方便进行数据库移植;
– 5).可以在plsql中使用函数 存储过程 序列 DML等
2.pl/sql中变量
-- 1).数据类型 --> 与数据库表的字段(列)的数据类型有区别
-- ①: 标量类型
-- varchar2
-- number(m,n)
-- boolean
-- date
-- timestamp
-- ②: LOB large Object 大对象类型
-- CLOB
-- BLOB
-- BFILE
-- NCLOB
-- ③: 属性类型
-- [schema].table_name.column_name%type --> 表示声明的变量与指定用户的指定表的指定列的数据类型一致;
-- [schema].table_name%rowtype --> 表示声明的变量与指定数据表的一行的数据类型一致;
-- 声明变量的基本语法: --> 匿名块
-- declare
-- --声明的内容: 变量 常量 游标
-- begin
-- --逻辑代码
-- end;
– psl/sql中的运算符:
– 变量的命名规则:
– 不能超过30个字符
– 不能以字母开头
– 可以包含字母 数字 下划线 $ #
– 不能有空格
– 不能是oracle的关键字和保留字
– 声明一个number类型的值
```sql
conn li/li;
declare
numd number(10,0):=123;
xm varchar2(100):='dre';
--Nclob类型
test nclob:='fwrw2342sfwr';
--声明属性数据类型的变量
--myName li.table_li.li_name%type;
--声明一个行属性数据类型的变量
-- myRow table_li%rowtype;
begin
--显示变量num的值
dbms_output.put_line('变量num的值是：'||numd||'xm的值是：'||xm);
dbms_output.put_line(test);
end;
select * from SALES;
select * from auto_part;
-- alter session set nls_date_format='yyyy/mm/dd';
alter session set nls_date_format='yyyy-mm-dd';
create or replace function calSun (
beginValue in number,endValue in number
) return number
is
--声明部分
i number(10,0):=0;
result number(10,0):=0;
begin
i:=beginValue;
while i<=endValue loop
result :=result+i;
i:=i+1;
end loop;
return result;
end;
drop function calSun;
create or replace function calSum (
beginValue in number,endValue in number
) return number
is
--声明部分
i number(10,0):=0;
result number(10,0):=0;
begin
i:=beginValue;--i=90;
while
i<=endValue loop --ifi<=2
result :=result+i;--循环
i:=i+1;
end loop;
return result;
end;
select calSum(1,100) as 和的值是 from dual;
--创建一个函数方法用于计算次方
create or replace function calsud(
ne in number ,ci in number
) return number
is
--声明
i number(10,0):=0;
result number(10,0):=ne;
begin
while i<ci loop
result:=result*ne;
i:=i+1;
end loop;
return result;
end;
select 2*2*2 from dual;
select calsud(2,3) from dual;
--改变文档日期格式
alter session set nls_date_format='yyyy/mm/dd';
-- 创建一个函数去查询每个科目最近一次考试的平均分最高科目编号
conn li/li;
select * from result;
--获取每个科目的平均分
select * from subjcet;
select subjectNO as 科目 ,avg(studentresult) from RESULT group by SUBJECTNO;
--获取最近一次考试
select max(examDate) from result ;
--创建函数
create or replace function getSub
return number--无参就不用括号什么的
--声明
is
subjectNO li.result.subjectNO%type;
begin
select subjectNo into subjectNo from RESULT where STUDENTNO in (
select sNO from
(
select sNO ,max(f.fe) as 平均分 from (
select subjectNO as sNo ,avg(studentresult) as fe from RESULT group by SUBJECTNO
)f group by f.sNo
)
) and EXAMDATE=(
select max(examDate) from result
);
return subjectNO;
end;
select * from RESULT where STUDENTNO=getsub();
--oracle中的函数
-- 字符串函数
-- ASCII(X): 返回字符X的ASCII码
select ascii(SUBJECTNO) from SUBJCET;
select ascii('a')from dual;
select ascii('A')from dual;
-- CONCAT(X,Y): 连接字符串X和Y
select concat('erewr','234g') from dual;
-- INSTR(X,STR,[START]): 从X中查找str，可以指定从start开始
select instr('safdsgdsaf','s',4) from dual;
-- LENGTH(X): 返回X的长度
select length('fdsssssssssssss') from dual;
-- LOWER(X): X转换成小写
select lower('sdFJIe') from dual;
-- UPPER(X): X转换成大写
select upper('erewd')from dual;
-- LTRIM(X[,TRIM_STR]): 把X的左边截去trim_str字符串，缺省截去空格
select Ltrim(' rer ')from dual;
-- RTRIM(X[,TRIM_STR]): 把X的右边截去trim_str字符串，缺省截去空格
select rtrim(' rewr ')from dual;
-- TRIM([TRIM_STR FROM]X): 把X的两边截去trim_str字符串，缺省截去空格
select trim(' re fs ')from dual;
-- REPLACE(X,old,new): 在X中查找old，并替换成new
select replace('grtegdsgs','gds','urowureo') from dual;
-- SUBSTR(X,start[,length]): 返回X的字串，从start处开始，截取length个字符，缺省length，默认到结尾
select substr('jfowjflsjf',2,3)from dual;
-- 数学函数
-- ABS(X): X的绝对值
select abs(-243) from dual;
-- CEIL(X): 大于或等于X的最小值
select ceil(-12.9) from dual;
-- FLOOR(X): 小于或等于X的最大值
select floor(-12.9)from dual;
-- MOD(X,Y): X除以Y的余数
select mod(3,4)from dual;
alter session set nls_date_format='yyyy-mm-dd';
-- POWER(X,Y): X的Y次幂
select power(3,6)from dual;
-- ROUND(X[,Y]): X在第Y位四舍五入
select round(123.234,2)from dual;
-- SQRT(X): X的平方根
select sqrt(8)from dual;
-- TRUNC(X[,Y]): X在第Y位截断,直接截断,不进行四舍五入
select trunc(2.324)from dual;
-- 日期函数--dual双重的
-- ADD_MONTHS(d,n): 在某一个日期 d 上，加上指定的月数 n，返回计算后的新日期
select add_months(sysdate,12)from dual;
-- LAST_DAY(d): 返回指定日期当月的最后一天
select last_day(sysdate)from dual;
-- ROUND(d[,fmt])，返回一个以 fmt 为格式的四舍五入日期值，d 是日期,fmt 是格式模型
select round(sysdate,'year')from dual;
-- 默认 fmt 为 DDD，即月中的某一天。
-- ① 如果 fmt 为“YEAR”则舍入到某年的 1 月 1 日，即前半年舍去，后半年作为下一年。
-- ② 如果 fmt 为“MONTH”则舍入到某月的 1 日，即前月舍去，后半月作为下一月。
-- ③ 默认为“DDD”，即月中的某一天，最靠近的天，前半天舍去，后半天作为第二天。
-- ④ 如果 fmt 为“DAY”则舍入到最近的周的周日，即上半周舍去，下半周作为下一周周日。
-- eg：SELECT SYSDATE,ROUND(SYSDATE),ROUND(SYSDATE,'day'),ROUND(SYSDATE,'month'),ROUND(SYSDATE,'year') FROM dual;
--
-- EXTRACT(fmt FROM d)，提取日期中的特定部分。
-- fmt 为：YEAR、MONTH、DAY、HOUR、MINUTE、SECOND。其中 YEAR、MONTH、DAY可以为 DATE 类型匹配，也可以与 TIMESTAMP 类型匹配;
-- 但是 HOUR、MINUTE、SECOND 必须与 TIMESTAMP 类型匹配。HOUR 匹配的结果中没有加上时区，因此在中国运行的结果小 8 小时。
-- eg：SELECT SYSDATE "date",
-- EXTRACT(YEAR FROM SYSDATE)"year",
-- EXTRACT(MONTH FROM SYSDATE)"month",
-- EXTRACT(DAY FROM SYSDATE)"day",
-- EXTRACT(HOUR FROM SYSTIMESTAMP)"hour",
-- EXTRACT(MINUTE FROM SYSTIMESTAMP)"minute",
-- EXTRACT(SECOND FROM SYSTIMESTAMP)"second"
-- FROM dual; extract/ik'str(ae爱)kt/提取
select extract(year from sysdate) from dual;
-- 转换函数
--to_char(data,format)
--data: 数字 日期 从数据库表中查询出来的列值 format: yyyy-mm-dd
select to_char(borndate,'yyyy-mm-dd') as 生日 from student;
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as 当前日期时间 from dual;
select to_char(sysdate,'yyyy') as 当前日期的年份 from dual;
select to_char(sysdate,'mm') as 当前日期的月份 from dual;
select to_char(sysdate,'dd') as 当前日期的日 from dual;
select to_char(sysdate,'hh24') as 当前时间的小时 from dual;
select to_char(sysdate,'mi') as 当前时间的分钟 from dual;
select to_char(sysdate,'ss') as 当前时间的秒钟 from dual;
select to_char(1234.0558,'$9999999.99') as 货币字符串 from dual;
select to_char(1234.0558,'C9999999.99') as 货币字符串 from dual;
select to_char(1234,'xxxx') as 十六进制数据 from dual;
conn system/orcl;
conn Scott/tiger;
--分析函数
--row_number
--dense_rank
--rank
/*
基本语法：函数名【（参数）】 over ([partition by 用于分区的列 order by 用于排序的列 asc|desc])
函数名称() over (分组 排序);
*/
--查询员工表
select * from emp;
select empno,ename,deptno,sal
,row_number() over (partition by deptno order by sal asc) as row_number分区排序,
dense_rank() over (partition by deptno order by sal asc) as dense_rank分区排序,
rank() over(partition by deptno order by sal asc)as rank分区排序
from emp;
--总结：
--row_number()不管数据的大小否相同都会按序号排序，即如果有相同的数据则根据出现的次序依次递增。
--dense_rank()如果遇见相同的数据则会出现并列的排名。并且不会空出下一个排名
--rank()如果遇见相同的数据会出现并列的排名，但是会空出下一个排名
alter session set nls_date_format='yyyy-mm-dd';
--查询员工表中每个部门工资前三名的员工信息(工资前三名)
select * from emp where empNO in
(
select empNO from (
--前三名
select empNO,money from (
select empNO,emp.JOB,sal,row_number() over (partition by deptno order by sal asc) as money from emp order by JOB
) bv where money<=3
)
)order by sal desc;
select job from emp;
select sal from emp;
--工资最高: 如果工资相同 --> 只能算一个
select empNO,emp.JOB,sal,dense_rank() over (partition by deptno order by sal asc) as money from emp;
--工资最高: 如果工资相同 --> 算多个
select empNO,emp.JOB,sal,row_number() over (partition by deptno order by sal asc) as money from emp order by JOB;
/*
pl/sql编程中的异常: Exception
基本语法
declare
-- 声明变量 游标 异常等
begin
--sql_statement;
exception
when exception_name then
--异常1
when exception_name then
--异常2
...
when others then
--其他异常
end;
*/
--异常
conn li/li;
select * from student;
declare
num number:=19;
res number:=12;
sName li.Student.StudentName%type;
var varchar2(100):=null;
--no_data_found
--声明一个自定义异常
data_is_null Exception;
begin
--计算结果
res:=num/0;
select StudentName into sName from Student where studetNo='101';
--判断var变量的值，如果该值为null人为的抛出一个新的异常
-- if var is null then
---- raise data_is_null;
-- --调用系统函数抛出异常
-- raise_application_error(-20111,'使用系统函数抛出的异常!');
-- end if;
-- exception
-- when zero_divide then
-- dbms_output.put_line('异常编码: '||sqlcode||'异常信息: '||sqlerrm);
-- when no_data_found then
-- dbms_output.put_line('异常编码: '||sqlcode||'异常信息: '||sqlerrm);
-- when data_is_null then
-- dbms_output.put_line('数据为null');
-- when others
-- dbms_output.put_line('发生了其他异常!'||sqlcode||sqlerrm);
--end;
select * from student;
DECLARE
sNo LI.STUDENT.STUDENTNO%type;
SName LI.STUDENT.STUDENTNAME%type;
--声明一个游标
cursor one(idea VARCHAR2) is
select studentNo,studentName from student where studentNO <idea;
begin
--打开游标 游标属性：%isopen-->游标是否打开
if not one%isopen THEN
open one('110');
end if;
--循环提取游标中的数据
loop
--将游标中的数据提取出来设置给指定的变量
fetch one into sNo,SName;
--判断什么时候退出循环--》游标属性：%notfound
exit when one%notfound;
--显示从游标中提出的数据 游标属性：%rowcount;
dbms_output.put_line('第'||one%rowcount||'行数据'||sNo||'<==>'||SName);
end loop;
end;
DECLARE
sNo LI.STUDENT.STUDENTNO%type;
sName LI.STUDENT.STUDENTNAME%type;
cursor twod(ide varchar2) IS
select studentNo,studentName from student where studentNO < ide;
BEGIN
--打开游标
IF twod%isopen THEN
open twod('110');
end if;
--提取游标
fetch twod into sNo,sName;
dbms_output.put_line('第'||twod%rowcount||'行数据'||sNo||'<==>'||sName);
while twod%found loop
fetch twod into sNo,sName;
dbms_output.put_line('第'||twod%rowcount||'行数据'||sNo||'<==>'||sName);
end loop;
end;
--关闭游标
--close twod;
--EXCEPTION
-- when others then
-- dbms_output.put_line(sqlcode || sqlerrm);
declare
sNo LI.STUDENT.STUDENTNO%type;
sName LI.STUDENT.STUDENTNAME%type;
--声明一个游标
cursor myCursor(xh varchar2) is select studentNo,studentName from Student where sNo < xh;
begin
--打开游标 游标属性: %isopen --> 判断游标是否打开
if not myCursor%isopen then
open myCursor('1023');
end if;
--循环提取游标中的数据
--loop
--将游标中的数据提取出来设置给指定的变量
-- fetch myCursor into studentno,studentName;
--判断什么时候退出循环 --> 游标属性: %notfound;
-- exit when myCursor%notfound;
--显示从游标中提出的数据 游标属性: %rowcount;
-- dbms_output.put_line('第'||myCursor%rowcount || '行数据: ' ||studentNo || '<==>' || studentName);
-- end loop;
--使用while循环迭代游标中的数据 --> 游标属性: %found --> 如果游标中有数据返回true
fetch myCursor into sNo, sName;
dbms_output.put_line('第'||myCursor%rowcount || '行数据: ' ||sNo || '<==>' || sName);
while myCursor%found loop
--提取
fetch myCursor into sNo, sName;
dbms_output.put_line('第'||myCursor%rowcount || '行数据: ' ||sNo || '<==>' || sName);
end loop;
--关闭游标
close myCursor;
exception
when others then
dbms_output.put_line(sqlcode || sqlerrm);
end;
--用forin做一个无参的
declare
--声明游标
cursor my is select studentNo,studentName from student where studentNO < '1023';
BEGIN
--forin自动打开游标
for itme in my loop
dbms_output.put_line(item.studentNo || '<==>' || item.studentName);
end loop;
end;
declare
--备注: 如果游标需要参数,参数的数据类型只能有类型本身不能指定数据类型的范围
cursor myCursor is select studentNo,studentName from student where studentNo < '1032';
begin
--for循环 item 必须是: %rowtypes 自动开启游标(隐式打开游标) 自动关闭
for item in myCursor loop
dbms_output.put_line(item.studentNo || '<==>' || item.studentName);
end loop;
end;
565
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
