莆田网站建设培训,南沙seo培训,网站的在线支付怎么做,爱站工具下载变量
变量 建议使用V开头 作用: 用来保存一个数据
普通变量
declare
--定义一个变量#xff08;保存一个数据#xff09;
v_email varchar2(20);
--定义变量并且赋值
v_ename varchar2(20) :张三;
beginv_email :553215qq.com;dbms_output.put_line(v_email);dbms_output…变量
变量 建议使用V开头 作用: 用来保存一个数据
普通变量
declare
--定义一个变量保存一个数据
v_email varchar2(20);
--定义变量并且赋值
v_ename varchar2(20) :张三;
beginv_email :553215qq.com;dbms_output.put_line(v_email);dbms_output.put_line(v_ename);
end;引用型变量保存一个数据–引用某张表的某个字段类型来作为变量的类型
declare
ename emp.ename%type : smith;
sal emp.sal%type :9000;
begindbms_output.put_line(ename||:||sal);end;记录型变量用来保存一条数据 --用某张表的一行字段的类型 来 作为变量的类型 declarev_emp emp%rowtype;begindbms_output.put_line(v_emp.empno);dbms_output.put_line(v_emp.ename);dbms_output.put_line(v_emp.job);
end;游标 用来保存多条数据
declare
cursor emp_cursor is select * from emp;
beginfor i in emp_cursor loopdbms_output.put_line(i.empno||:||i.ename);end loop;end;常量 constant --定义常量时必须赋值定义好的常量不能更改
declare
c_name constant varchar2(20):tom;
beginc_name : jack;dbms_output.put_line(c_name);
end;代码块
代码块就是 一段程序一般用来处理数据
例 --查询SMITH的工资
declare
v_sal emp.sal%type;
v_ename varchar2(20):SMITH;
begin-- v_sal:salselect sal into v_sal from emp where ename v_ename;dbms_output.put_line(v_sal);
end;--把7788员工的工资百分之10 并输出姓名和加百分之10后的工资
declare
v_ename emp.ename%type;
v_empno emp.empno%type:7788;
v_sal number(9,2);
beginselect ename,sal*1.1 into v_ename,v_salfrom emp where empno v_empno;dbms_output.put_line(v_ename||:||v_sal);
end;--查出SMITH所有的信息
declare
v_emp emp%rowtype;
beginselect * into v_emp from emp where ename SMITH;dbms_output.put_line(v_emp.empno||:||v_emp.ename||:||v_emp.sal);
end;--输入名字查询 输出姓名和职位工资
declare
v_ename emp.ename%type:输入姓名;
v_job emp.job%type;
v_sal emp.sal%type;
beginselect job,sal into v_job,v_sal from emp where ename v_ename;dbms_output.put(姓名||v_ename);dbms_output.put(职位||v_job);dbms_output.put_line(工资||v_sal);
end;dbms_output.put 输出在内存 而且不换行 dbms_output.put_line 把内存的数据进行输出并且换行
练习
1 查出ALLEN的工资和职位
2 查出scott的入职时间
3 输入员工号 输出员工姓名
4 输入部门号 输出部门名
declare
--1
v_sal emp.sal%type;
v_job emp.job%type;
--2
v_hiredate emp.hiredate%type;
--3
v_empno emp.empno%type : 员工号;
v_ename emp.ename%type;
--4
v_deptno dept.deptno%type : 部门号;
v_dname dept.dname%type;
beginselect sal,job into v_sal,v_job from emp where ename ALLEN;dbms_output.put_line(v_sal);dbms_output.put_line(v_job);--2select hiredate into v_hiredate from emp where ename upper(scott);dbms_output.put_line(v_hiredate);--3select ename into v_ename from emp where empno v_empno;dbms_output.put_line(员工姓名||v_ename);--4select dname into v_dname from dept where deptno v_deptno;dbms_output.put_line(部门名||v_dname);
end;循环结构
for循环
语法 for 循环变量 in 范围 loop开始循环 end loop;结束循环
--输出10个大数据
declare
begin for i in 1..10 loopdbms_output.put(大数据 );end loop;dbms_output.put_line( );
end;--输出所有员工的姓名
beginfor i in (select ename from emp) loopdbms_output.put_line(i.ename);end loop;
end;--计算1-100之间所有单数和
declare
v_sum number : 0;
beginfor i in 1..100 loopif mod(i,2) 1 thenv_sum : v_sumi;end if;end loop;dbms_output.put_line(v_sum);
end; --找到101-999之间的所有水仙花数
declare
g number;
s number;
b number;
beginfor i in 101..999 loopg : substr(i,3,1); -- g mod(i,10)s : substr(i,2,1); -- s mod(floor(i/10),10) s floor(mod(i,100)/10)b : substr(i,1,1); -- b floor(i/100)if power(g,3)power(s,3)power(b,3) i thendbms_output.put_line(i);end if;end loop;
end; --查出10部门 工资大于1500的人数 和总工资 不使用函数
declare
v_sum number : 0;
v_number number : 0;
begin for i in (select sal from emp where deptno 10) loopv_sum : v_sumi.sal;if i.sal1500 thenv_number : v_number 1;end if;end loop;dbms_output.put_line(部门总工资: ||v_sum);dbms_output.put_line(工资大于1500人数: ||v_number);
end;while循环
语法:
while 条件 loopsql
end loop;--有张纸厚度是1mm珠穆朗玛峰海拔8848m请问这张纸对折多少次厚度会超过山的高度
declare
v_count number :0;
v_houdu number : 1;
v_hight number : 8848000;
beginwhile v_houduv_hight loopv_houdu : v_houdu*2;v_count: v_count1;dbms_output.put_line(对折次数||v_count|| 纸张厚度||v_houdu);
end loop;
end;--假如从今天开始存1分钱每天翻倍明天2分后天4分大后天8分请问要多少天才能存够100万元
declare
v_deposit number :0; --存款
v_day number :0; --存款天数
beginwhile v_deposit100000000 loopv_deposit : v_depositpower(2,v_day);v_day : v_day1;dbms_output.put_line(存款天数||v_day|| 存款||v_deposit);end loop;
end;loop循环
语法: loop exit when 条件 end loop;
--条件满足就退出循环--用loop循环解决上述两个问题
declare
v_count number : 0; --对折次数
v_houdu number : 1; --纸张厚度
beginloop exit whenv_houdu 8848000; v_count:v_count1;v_houdu:v_houdu*2;dbms_output.put_line(对折次数||v_count|| 纸张厚度||v_houdu);end loop;end;declare
v_deposit number : 0; --存款
v_day number : 0; --天数
begin loop exit when v_deposit 100000000;v_deposit : v_depositpower(2,v_day);v_day:v_day1;dbms_output.put_line(天数||v_day|| 存款||v_deposit);end loop;end;动态sql
语法 execute immediate sql–DDL 语句在plsql 只能使用动态SQLcreate drop alter –DML 语句在plsql 可以直接执行 updateinsert delete –DQL 语句在plsql 需要使用into select把查询的结果通过into赋值给变量
--输入表名 删除该表
declare
v_table_name varchar2(20) :表名;begin execute immediate drop table ||v_table_name;end;--输入旧表名 输入新表名 复制一张新表
declare
v_old_name varchar2(20) : 旧表名;
v_new_name varchar2(20) : 新表名;
beginexecute immediate create table ||v_new_name|| as select * from ||v_old_name;
end;--动态sql 向部门表插入数据
declare
v_sql varchar2(200) :;
v_deptno number :部门编号;
v_dname varchar2(20) :部门名称;
v_loc varchar2(30) :部门地址;
beginv_sql :insert into dept values(||v_deptno||,||v_dname||,||v_loc||);dbms_output.put_line(v_sql);execute immediate v_sql;end;游标
用来保存一个查询的结果集 静态游标 游标的数据不能发生改变 显示游标自己手动创建 可以看到cursor关键字隐式游标程序执行DML 或者select into 语句会自动创建 看不到cursor关键字 动态游标 游标的数据可以发生改变
静态游标
显式游标
--使用while 循环操作游标
--使用游标显示每个人的姓名和工资
declare
--定义游标
cursor emp_cursor is select * from emp;
--定义记录型变量
v_emp emp%rowtype;
begin--打开游标open emp_cursor;--提取数据fetch emp_cursor into v_emp;while emp_cursor%found loop --判断是否提取到数据dbms_output.put_line(v_emp.ename||:||v_emp.sal);fetch emp_cursor into v_emp;--继续提取游标中的数据确保循环会终止end loop;--关闭游标close emp_cursor;
end;--使用for循环操作游标 for循环会自动打开 提取 关闭 游标
declare
cursor emp_cursor is select * from emp;
beginfor i in emp_cursor loopdbms_output.put_line(i.ename||:||i.sal);end loop;end;select * from dept;练习
1 使用游标修改工资10部门的员工加百分之十 20部门的员工加百分之二十 30部门的员工加百分之三十
declare
cursor emp_cursor is select * from emp;
beginfor i in emp_cursor loopif i.deptno 10 then update emp set sal i.sal*1.1 where empno i.empno ;elsif i.deptno 20 thenupdate emp set sal i.sal *1.2 where empno i.empno ;elsif i.deptno 30 thenupdate emp set sal i.sal*1.3 where empno i.empno;end if;end loop;end;2 使用游标 输出张三的总成绩平均成绩 和姓名 不使用函数
declare
cursor sc_cursor is select a.cno,a.score from sc_a02 astudent_a02 b where sname 张三 and a.sno b.sno;
v_count number :0; --记录科目数
v_sum_score number :0; --总成绩
v_avg_score number;
beginfor i in sc_cursor loopv_count:v_count1;v_sum_score : i.scorev_sum_score;end loop;v_avg_score : round(v_sum_score/v_count);dbms_output.put_line(总成绩||v_sum_score);dbms_output.put_line(平均成绩||v_avg_score);dbms_output.put_line(姓名张三);end;3 使用游标 把平均分大于80的学生信息插入到一张新表 字段姓名年纪性别平均分 先创建该表
beginexecute immediate create table exc_stu_info(sname varchar2(20),sage number(3),sex varchar2(2),avg_sc number(5));
end;
declare
cursor exc_cursor is
select sname,sage,ssex, 平均成绩 from student_a02 a,
(select sno,round(avg(score)) 平均成绩 from sc_a02 group by sno) b
where a.sno b.sno and 平均成绩 80;
beginfor i in exc_cursor loopinsert into exc_stu_info values(i.sname,i.sage,i.ssex,i.平均成绩);end loop;
end;
select * from exc_stu_info;隐式游标
程序执行DML 或者 select into 语句会自动创建 看不到cursor 关键字 默认游标名SQLbegin delete from emp where empno7369;if SQL%found thendbms_output.put_line(删除成功);end if;
end;–SQL%found 判断是否有数据 有就返回True 没有返回False –SQL%notfound 判断是否没有数据 没有返回true 有就返回false –SQL%rowcount –显示游标代表行数 –隐式游标代表影响删除修改的行数
beginupdate emp set sal 10000 where deptno 10;if SQL%found then dbms_output.put_line(成功修改||SQL%rowcount||条数据);end if;
end;动态游标
游标的数据可以发生改变游标可以改变指向 但是不能用for循环只能手动打开游标查询数据语法 要先定义动态游标的类型 再定义动态游标变量declaretype ref_cursor is ref cursor;data_cursor ref_cursor;--定义记录型变量v_dept dept%rowtype;beginopen data_cursor for select * from emp;fetch date_cursor into v_emp; --提取游标数据赋值给记录型变量while data_cursor%found loopdbms_ouput.put_line(v_emp.ename);fetch data_cursor into v_emp;end loop;clos data_cursor;--修改动态游标指向open data_cursor for select * from dept;fetch data_cursor into v_dept;while data_cursor%found loopdbms_output.put_line(v_dept.dname);fetch data_cursor into v_dept;end loop;close data_cursor;
end;–定义两个静态游标一样可以实现
declare
cursor emp_cursor is select * from emp;
cursor dept_cursor is select * from dept;
beginfor i in emp_cursor loopdbms_output.put_line(i.ename);end loop;for i in dept_cursor loopdbms_output.put_line(i.dname);end loop;
end;练习
1 --输入部门号 --如果部门号不存在输出 没有该部门
--如果该部门没有员工 输出该部门没有员工 --如果该部门有员工 输出该部门所有员工姓名
declare
v_deptno emp.deptno%type :部门号;
v_ename varchar2(100);
v_flag number :0; --0 没有该部门 1 有该部门
v_empnum number :0;
beginfor i in (select deptno from dept) loopif v_deptno i.deptno thenV_flag :1;select count(1) into v_empnum from emp where deptno v_deptno; if v_empnum0 thendbms_output.put_line(该部门没有员工);else select LISTAGG(ename, ,) WITHIN GROUP (ORDER BY ename) into v_ename from emp where deptno v_deptno group by deptno;dbms_output.put_line(v_ename);end if;end if;end loop;if v_flag 0 thendbms_output.put_line(没有该部门);end if;end;
2 --使用游标输出公司最高工资不使用函数
declare
cursor emp_cursor is select * from emp;
v_maxsal number:0;
beginfor i in emp_cursor loopif i.sal v_maxsal then v_maxsal : i.sal; end if;end loop;dbms_output.put_line(v_maxsal);end;
3 --使用动态SQL 把所有的分区表删除 select * from user_tab_partitions
select * from user_tab_partitions;
declare
CURSOR c_part_tables IS SELECT distinct table_name FROM user_tab_partitions;
beginfor i in c_part_tables loopexecute immediate drop table ||i.table_name||;execute immediate PURGE TABLE || i.table_name||;end loop;end;
4 --使用动态SQL 把所有E开头的表复制出来一个新表 新表名为 原表名当前时间--如emp 复制出一张 emp20240911 --提示 查看所有表的语句 select * from user_tables;
execute immediate create table ||v_new_name|| as select * from ||v_old_name;select * from user_tables;
declare
cursor tab_cursor is select * from user_tables;
beginfor i in tab_cursor loopif substr(i.table_name,1,1) E thenexecute immediate create table ||i.table_name||to_char(sysdate,yyyymmdd)|| as select * from ||i.table_name;end if;end loop; end; 存储过程
有名字的代码块 为了完成特定的功能
语法 create or replace procedure 名字(参数) as 声明变量begin 代码块 end--创建存储过程添加部门
create or replace procedure add_dept(dno number,dname varchar2,dloc varchar2)
as
begininsert into dept values(dno,dname,dloc);
end;
--调用过程
call add_dept(3,财务部,东莞);
select * from dept;--创建一个过程 计算两个数之和
create or replace procedure plus(num1 number,num2 number, result out number)
as
beginresult:num1num2;
end;--带有返回值的过程要在匿名代码块中调用 否则不能声明变量接收返回值
declare
r_sum number;
beginplus(100,200,r_sum);dbms_output.put_line(r_sum);
end;–in 参数 是一个变量用于接收传入的数据 调用者给它赋值 –out 参数 过程执行完毕得到的数据 理解为函数的返回值
--传入部门号返回部门的最高工资最低工资
create or replace procedure get_max_min_sal(dno in number,maxsal out number,minsal out number)
as
beginselect max(sal),min(sal) into maxsal,minsal from emp where deptno dno;
end;
declare
ma number;
mi number;
beginget_max_min_sal(10,ma,mi);dbms_output.put_line(最高工资||ma);dbms_output.put_line(最低工资||mi);
end;--创建一个过程 传入部门号进行删除数据 返回删除的行数
create or replace procedure delete_date(dno in number,d_row out number)
as
begindelete from emp where deptno dno;d_row : SQL%rowcount;
end;declare
row_d number;
begindelete_date(40,row_d);dbms_output.put_line(删除了||row_d||个员工);
end;--作业
1 创建一个过程返回当前时间年月日时分秒 都要
create or replace procedure get_date(curr_time out timestamp)
as
begincurr_time : systimestamp;
end;
declare
c_time timestamp;
beginget_date(c_time);dbms_output.put_line(当前时间||to_char(c_time,yyyy-mm-dd HH24:mi:ss));
end;2 创建一个过程实现和power函数相同功能返回结果
create or replace procedure v_pow(dishu in number,zhishu in number,result out number)
as
beginif dishu 0 thendbms_output.put_line(底数不能为0);elsif mod(zhishu,1) 0 then --限定整数指数if zhishu 0 thenresult :1;for i in 1..zhishu loopresult : result * dishu;end loop;elsif zhishu 0 then result :1;else --指数小于0result :1;for i in 1..ABS(zhishu) loopresult : result * dishu;end loop;result : 1/result;end if; else result : power(dishu,zhishu);end if;
end;
declare
mi number(10,3);
beginv_pow(2,-2,mi);dbms_output.put_line(mi);
end;3 创建一个过程删除没有员工的部门
create or replace procedure delete_01
as
begindelete from dept where deptno not in (select distinct deptno from emp);
end;call delete_01(); create or replace procedure delete_dept
as
v_count number;
beginfor i in (select deptno from dept) loopselect count(deptno) into v_count from emp where emp.deptno i.deptno;if v_count 0 thendelete from dept where deptno i.deptno;end if;end loop;dbms_output.put_line(sql%rowcount);
end;
select *from dept;
declare
begindelete_dept();
end;4 创建一个过程把30号部门改为33原来30号的员工 部门号也改为33
create or replace procedure up_info(n out number)
as
v_dept dept%rowtype;
beginselect * into v_dept from dept where deptno30;--添加部门号33insert into dept values(33,v_dept.dname,v_dept.loc);--修改员工的部门号update emp set deptno 33 where deptno v_dept.deptno;n:sql%rowcount;--删除30号部门delete from dept where deptno 30;
end;
declare
row_num number;
begin up_info(row_num);dbms_output.put_line(修改了||row_num||名员工的部门号);
end;存储函数
存储过程 procedure(DML) 可以不返回数据要返回数据就用out参数 存储函数 function计算 必须要有一个返回值如果要返回多个值那么也可以用out参数有out参数就不能使用select调用可以用于DML操作有了之后不能用select调用
--计算两个数之和返回结果
create or replace function sum_01(num1 number,num2 number)
return number --定义返回类型
as
begin--返回数据return num1num2;
end;
--select 调用
select sum_01(1,2) from dual;
--declare 调用
declare
n number;
beginn :sum_01(3,4);dbms_output.put_line(n);dbms_output.put_line(sum_01(5,6));end;--使用函数 根据部门号返回部门人数和平均工资create or replace function get_sum_count(vno in number, v_sal out number)return numberasv_count number;beginselect count(1),round(avg(sal)) into v_count,v_sal from emp where deptno vno;return v_count;
end;declare
v_count number;
v_avgsal number;
beginv_avgsal:get_sum_count(33,v_count);dbms_output.put_line(v_count);dbms_output.put_line(v_avgsal);
end; 触发器
--作用 --1.验证数据--2.数据备份--3.记录日志--语法
--create or replace trigger 触发器名
--before --被触发的语句先执行 --after --被触发的语句后执行
--操作deleteupdateinsert
--on 表名
--for each for --行级触发
--begin 被触发sql end----当员工表 有员工离职时 自动把该员工的数据插入到离职表
--emp 在职员工表
--emp_back 离职员工表
--获取触发器 操作 的数据
--old --用于删除代表旧数据 代表修改之前的数据
--new --用于添加代表新数据 代表修改之后的数据
create table emp_back as select * from emp where 12;
select * from emp_back;
select * from emp; create or replace trigger delete_emp_trigger
before delete
on emp
for each row
begininsert into emp_back values(:old.empno,:old.ename,:old.job,:old.mgr,:old.hiredate,:old.sal,:old.comm,:old.deptno);
end;--触发sql
delete from emp where empno 7369;--使用触发器实现订单表和库存表之间的关系
--订单表增加数据库存表自动减少数量
--库存表
create table stock(
sname varchar2(10),
scount number
);
--订单表
create table orders(
oname varchar2(10),
ocount number
);
insert into stock values(IphoneX,100);
insert into stock values(华为P60,100);insert into orders values(IphoneX,0);
insert into orders values(华为P60,0);
select * from stock;
select * from orders;create or replace trigger stock_order_trigger
after update on orders
for each row
beginupdate stock set scount scount-(:new.ocount-:old.ocount) where sname :old.oname;
end;update orders set ocount ocount5 where oname IphoneX; 数据校验
--禁止删除KING 新员工的工资不能超过3000 涨工资不能超过30%
create or replace trigger check_trigger
before delete or update or insert
on emp
for each row
begin if delete thenif :old.ename KING then--手动制造一个错误raise_application_error(-20002,不能删除老板);end if;end if;if insert thenif :new.sal 3000 thenraise_application_error(-20003,新员工工资不能超过3000);end if;end if;if update then if :new.sal :old.sal*1.3 thenraise_application_error(-20003,工资涨幅不能超过百分之30);end if;end if;
end;数据同步--使用触发器 备份 部门表--创建一个备份表create table dept_back as select * from dept;select * from dept;select * from dept_back;
create or replace trigger backups_trigger
before insert or update or delete on dept
for each row
beginif inserting then insert into dept_back values(:new.deptno,:new.dname,:new.loc);end if;if updating thenupdate dept set dname :new.dname, loc :new.loc where deptno :old.deptno;end if;if deleting thendelete from dept_back where deptno :old.deptno;end if;
end;记录日志
--(什么人 什么时候 对表做了什么操作
--创建一个部门日志表
create table dept_log(
deptno number,
dname varchar2(20),
dloc varchar2(20),
ddate varchar2(20),--时间
duser varchar2(20),--操作人
dtype varchar2(20));--操作类型select * from dept_log;create or replace trigger dept_log_trigger
after insert or update or delete on dept
for each row
begin if inserting theninsert into dept_log values(new.deptno,:new.dname,:new.loc,to_char(sysdate,yyyy-mm-dd HH24:mi:ss),user,insert);end if;if deleting theninsert in dept_log values(:old.deptno,:old.dname,:old.loc,to_char(sysdate,yyyy-mm-dd HH24:mi:ss),user,delete);end if;if updating theninsert in dept_logvalues(:old.deptno,:old.dname,:old.loc,to_char(sysdate,yyyy-mm-dd HH24:mi:ss),user,update_before);insert in deot_logvalues(:new.deptno,:new.dname,:new.loc,to_char(sysdate,yyyy-mm-dd HH24:mi:ss),user,update_after);end if;
end;程序包
包 –可以减少名称的定义 –管理相同的表/业务 –不同的程序包函数、过程的名称可以重复
--创建程序包--在包里面声明有哪些过程和函数具体的功能不需要去实现--过程写到参数部分结束 --函数写到返回值部分结束
--语法
create or replace package 包名
as
过程名参数
......
函数名参数返回值类型
end 包名包体 --创建包体--实现过程和函数 写具体代码
create or replace body 对应的包名
asprocedure 过程名参数asbegin代码end;function 函数名参数return 返回值类型asbegin代码return ...;end;
end 包名;create or replace procedure backup_all_tables
as
cursor back_cursor is select * from user_tables;
beginfor i in back_cursor loopexecute immediate create table ||i.table_name||tochar(sysdate,mmdd)|| as select * from ||i.table_name;end loop;end;call backup_all_tables();--作业create or replace function my_random(P_LENGTH number,P_INCLUDE_CHAR number)return varchar2asresult_char varchar2(4000) :;beginif P_INCLUDE_CHAR 0 thenfor i in 1..P_LENGTH loopresult_char : result_char||floor(dbms_random.value(0,10));end loop;elsif P_INCLUDE_CHAR 1 thenfor i in 1..P_LENGTH loopresult_char : result_char||dbms_random.string(x,1);end loop;elseraise_application_error(-20000,只能输入0或1);end if;return result_char;
end;declare
begindbms_output.put_line(my_random(5,1));end;--3 有一个字符串abcdefg需要将这个字符串拆分每个字母占一行来显示--先创建一个表类型这个 is table of 表示它是一个表格这个表格在此处只能有一个字段
create type ty_string is table of varchar2(1);--2创建一个自定义函数这个函数返回的类型就是上边定义的表类型
create or replace function func_split_str(s varchar2)
return ty_string
as
--将表格类型赋值给一个变量得到一个表格
t ty_string : ty_string(); --必须赋初值 赋值的过程就是在内存中创建一个空表的过程
beginfor i in 1..length(s) loop--因为表格没有任何内容甚至连行都没有所有向表格添加数据前要先创建一个空行t.extend();t(i) : substr(s,i,1); --截取字符串end loop;return t;
end;3.运行这个表类型函数
--在 from 后面 通过table的方式来调用这个函数当成一个表格来输出结果
select * from table(func_split_str(abcdefg));
