数据字典
select * from dba_data_files ;--数据文件。一般来说和表空间是绑定的。
select * from v$controlfile;--控制文件
select * from v$logfile;--日志文件
--常用数据字典
select * from dba_tablespaces;--全部的表空间
select * from user_tablespaces;--由于system具有最高权限，查看的也是全部表空间
select count(1) from dba_objects; --查看所有的逻辑对象【所有用户的+sys用户的】
-- sysdba【sys用户】 > dba【system用户】 >普通用户【其他用户名】
select count(1) from all_objects; --查询本人权限下能看的所有逻辑对象及公有对象，不包含sys用户创建的非公有。
select count(1) from user_objects;--本人私有的【创建】逻辑对象
select * from user_role_privs; --查看当前用户角色
select * from dba_users; --查询所有的用户
select * from user_users; --查询当前用户信息
select * from user_objects;
select * from all_objects where OWNER='SCOTT'; --相当于上一句
select * from all_objects where OWNER='SYSTEM' --SYS,SYSTEM,public，scott
alter user scott account unlock --激活账号
alter user scott identified by orcl123456 --修改密码
create user testuser identified by orcl123456 --创建普通用户
grant resource,connect to testuser; --赋予它登录，和操作常用逻辑对象的权限
create table test_t(
id number primary key,
name varchar2(20) not null
);
--默认开启事务，即不开启自动提交
insert into testuser.test_t values('1','老白');
insert into testuser.test_t values('2','小白');
--查询所有的表
select * from dba_tables;-- 所有的表，包含公有及sys全部表
select * from all_tables; --权限范围内的表，包含sys公有表
select * from user_tables; --私有的表
--查询所有权限下能看的视图 ？？？
select * from all_views;
--查询所有权限下能看的存储过程 ？？？
select * from all_procedures;
--查看运行时数据V$xxx
select * from v$session;--连接状态
select * from v$instance;--本实例状态
索引
--给dept表的depname字段建立唯一索引
create unique index INX_DEPNAME on dept(dname);
--该索引实现dname的唯一约束
insert into depet value('50','RESEARCH','CHINA NANCHANG');--违反唯一约束，无法添加
--删除索引
drop index INX_DEPNAME;
--手动重建索引
alter index INX_DEPNAME rebuild;
--只有deptno索引生效
select * from dept where deptno>'20';
--只有INX_DEPNAME索引生效
select * from dept where dname like 'S%';
select * from dept where dname like '%S';--左模糊查询，索引失效（未证明）
select /*+index(dept INX_DEPNAME)*/
* from dept where deptno>'20' and dname like 'S%';
同义词
同义词：对“表对象”取同义词，更容易被理解及调用
私有同义词：
create synonym 同义词名 for 表对象
公有同义词:
create public synonym 同义词名 for 表对象
system登录，给testuser用户分配创建同义词的权限
grant create synonym to testuser; --私有同义词权限
grant create public synonym to testuser; --公有同义词权限
--testuser登录
select * from scott.emp;
--创建私有同义词
create synonym myemp for scott.emp;
--使用私有同义词
select * from myemp;
--创建公有同义词
create public synonym pub_scott_emp for scott.emp;
select * from pub_scott_emp --使用公有同义词
序列
oracle使用序列实现自增或自减。
create sequence 序列名
start with 起点 increment by 步长
[maxvalue 5000]|[nomaxvalue] cycle
--创建序列 从1-10.每次增长1 ，到10以后回到1.缓存5个。
create sequence myse
start with 1 maxvalue 10 increment by 1
cache 5 cycle 。
select myse.nextval from dual; --获得序列的下一个值
select myse.currval from dual; --获得序列的当前值
select myse.nextval,myse.nextval from dual; --一条select语句只能走1步。
create sequence myse2
start with 100 minvalue 0 maxvalue 100 increment by -10
cache 5 cycle
select myse2.nextval from dual; --dual是虚拟表，只是为了sql语句的结构的完整
select sysdate from dual; --当前时间
--通过序列实现记录的插入时自增
create table test_t(
id number primary key,
name varchar2(20) not null
)
create sequence mytestse
start with 1 nomaxvalue increment by 1
cache 20
--使用序列进行主键插入
insert into test_t values(mytestse.nextval,'姓名'||mytestse.nextval);
用户管理
--创建表空间 ，给bai用户专用
create tablespace bai_ts
datafile 'c:\bai.dbf' size 10M
--增加容量
alter tablespace bai_ts
add datafile 'c:\bai2.dbf' size 5M
--创建用户白，绑定上面的表空间
create user bai identified by orcl123456
default tablespace bai_ts
temporary tablespace temp;
-- 授予两个基本角色才能登录。
grant connect,resource to bai;
select * from dba_users where USERNAME='BAI'
select * from dba_tablespaces where tablespace_name='BAI_TS' --查询表空间信息
select * from v$datafile
select * from dba_data_files where tablespace_name='BAI_TS' --查询表空间对应的数据文件
--修改用户信息
--禁用账号
alter user bai account lock
alter user bai account unlock
--- 查看本用户的角色
select * from user_role_privs;
--查看其他用户的角色
select * from dba_role_privs where grantee='BAI'
--查看指定角色的行为权限
select * from role_sys_privs where role='DBA' --
--需要用sysdb身份登录才能看到
select * from role_sys_privs where role='RESOURCE' --
select * from role_sys_privs where role='CONNECT'
select * from user_role_privs; --查看本用户具有的角色
select * from user_sys_privs;--查看本用户的行为权限。
select * from user_tab_privs; --查看本用户的表对象权限。
select * from testuser.test_t;
-- 使用system登录，授予testuser 创建视图权限
grant create view to testuser;
--testuser登录，创建视图
create or replace view test_t_count_views
as select count(1) 记录数 from test_t
select * from testuser.test_t_count_views;
-- testuser登录，无法查询非系统，非公有的私人表
select * from scott.emp; --失败
-- scott登录，将 emp表的查询和删除权限赋给testuser;
grant select,delete on emp to testuser
-- testuser登录,再次查询emp表
select * from scott.emp;
--删除。允许
delete from scott.emp where empno='7888'
--修改。不允许
update scott.emp set ename = '白' where empno='7888'
--用system登录，创建角色
create role myrole
--让此角色继承两个角色
grant resource,connect to myrole
--让此角色增加 创建视图和创建包的两个系统行为权限
grant create view to myrole
select * from role_role_privs where role ='MYROLE' --查看角色所继承的角色。
select * from role_sys_privs where role ='MYROLE' --查看角色所具有的系统行为
--创建新用户 myroleuser
create user myroleuser identified by orcl123456
grant myrole to myroleuser; --赋给它myrole角色。
--使用myroleuser登录
--建表
create table test_t(
id number primary key,
rname varchar2(20) not null,
age number check(age>=0) --加入check约束，保证参照完整性
)
-- 【resource角色无法完全继承】，还需要system登录后授权
grant resource to myroleuser;
--用myroleuser登录，此时可以操作表了。
insert into test_t values('1','白','2');
insert into test_t values('2','白2','22');
create or replace view test_t_getname_view
as
select rname as 姓名 from test_t;
select * from test_t_getname_view;
create package mypack
as
begin
end;
内置函数
https://www.cnblogs.com/lukelook/p/11218479.html
--数学类
select abs(-10) from dual;
select floor(10.6) from dual; --往小取整
select floor(-10.6) from dual; --往小取整
select ceil(10.6) from dual; --往大取整
select ceil(-10.6) from dual; --往大取整
select round(10.6) from dual; --四舍五入
select round(-10.6) from dual; --四舍五入
select sqrt(4) from dual;
select power(4,2) from dual;
select power(27,1/3) from dual;
select trunc('1234.5678',3) from dual;--小数点后3位截掉
select trunc('1234.5678',-2) from dual;--小数点左边第2位开始后面数字用0填充
--123,默认截去小数点后面的部分;
select trunc(123.567) from dual;
-- 2 类型转换
select sysdate from dual;--获得当前的时间日期（datetime类型)
--转成字符串
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual
--现在星期几？
select to_char(sysdate,'day') from dual;
--显示公元yyyy年 mm月 dd日
select to_char(sysdate,'"公元"yyyy"年"mm"月"dd"日"')
from dual;
--将给定的字符串转成date类型
select to_date('2013-1-1','yyyy-mm-dd') from dual;
--将字符串进行格式化
--不满4位用0补齐
--设计一个方法，使得 学号满足 stu0012。要求引用序列。
create sequence stu_se ;
drop table student;
create table student
(
stuid varchar2(8) primary key,
stuname varchar2(256) not null
)
insert into student values('stu'||to_char(stu_se.nextval,'0999'),'xxx');
select * from student;
select length(to_char('1234','0999')) from dual;
-- 3 字符串转换
select length('abc') from dual;
select length('a白c') from dual;--字符数
select instr('abc','xxx') from dual; --起点是1.找不到得到0.
select trim(' i am a boy! ') from dual;--去头尾
select replace(' i am a boy! ','boy','girl') from dual;
select replace(' i am a boy! ',' ','') from dual;
select * from scott.emp;
select empno,ename,job,nvl(comm,'0') from scott.emp;
select empno,ename,job,nvl2(comm,'有福利','没福利') from scott.emp;
存储过程
as
v_ename scott.emp.ename%type;
v_dname scott.dept.dname%type;
begin
select scott.emp.ename,scott.dept.dname into v_ename,v_dname from scott.emp, scott.dept
where empno=v_empno and scott.emp.deptno = scott.dept.deptno;
dbms_output.put_line(v_empno||'对应的员工名'||v_ename||',所在部门'||v_dname);
exception
when sys.standard.no_data_found then
dbms_output.put_line('找不到'||v_empno||'对应的员工');
when others then
dbms_output.put_line('查找过程出现其他异常！');
end;
--有入参，有出参的情况
create or replace procedure get_add_result(
i number,j number,v_result out number --result为出参变量，使用者可以读取
)
as
begin
v_result := i+j;
end;
--plsql调用含出参的存储过程
declare
v_result number; --声明局部变量,作为出参
begin
get_add_result('10','20',v_result);
dbms_output.put_line('计算结果:'||v_result);
end;
--有两个出参的存储过程
--有入参，显示表记录的存储。输入工号，显示该工号对应的员工名及部门名
create or replace procedure get_emp_by_eno_proc
(
v_empno scott.emp.empno%type, --和emp表的empno字段类型一致
v_ename out scott.emp.ename%type,
v_dname out scott.dept.dname%type
)
as
begin
select scott.emp.ename,scott.dept.dname into v_ename,v_dname from scott.emp, scott.dept
where empno=v_empno and scott.emp.deptno = scott.dept.deptno;
exception
when sys.standard.no_data_found then
v_ename:='查无员工';
when others then
v_ename:='其他异常';
end;
select * from scott.emp;
函数
--没有入参的函数
create or replace function get_random_func
return number --返回number类型
as
v_result number;
begin
select dbms_random.random into v_result from dual;
return v_result;
end;
--1个入参的函数,指定返回的随机数 [1,n]
create or replace function get_random_n_func
(n number)
return number --返回number类型
as
v_result number;
begin
if (n<1) then
dbms_output.put_line('入参非法!');
return -1; ---1代表入参异常
end if;
select mod(abs(dbms_random.random),n)+1 into v_result from dual;
return v_result;
end;
--找出指定工号的员工的部门所在的位置
create or replace function get_deptloc_by_eno
(v_empno scott.emp.empno%type) return varchar2
as
v_result scott.dept.loc%type; --返回值的局部变量
begin
select scott.dept.loc into v_result from scott.emp,scott.dept
where scott.emp.deptno = scott.dept.deptno and scott.emp.empno = v_empno;
return v_result;
exception
when no_data_found then
return '查无'||v_empno||'对应的员工';
when others then
return '其他异常';
end;
--找出RESEARCH部门的员工总数
declare
--v_ename varchar(20);
begin
update scott.emp set sal = sal*1.2 where empno = '73669';
--select ename into v_ename from scott.emp where empno='7369';
dbms_output.put_line('影响行数 ：'||sql%rowcount);
end;
select * from scott.salgrade;
grant update on emp to system;
1 编写存储过程 raise_sal_by_eno，给指定工号的员工的薪资提升20% （commit),提示加薪成功
找不到则控制台输出错误。
create or replace procedure raise_sal_by_eno(v_eno number)
as
begin
update scott.emp set sal = sal*1.2 where empno = v_eno;
if (sql%rowcount=1) then
dbms_output.put_line('提薪成功');
elsif then
dbms_output.put_line('提薪失败，'||v_eno||'对应的员工不存在');
end if;
commit;
end;
select * from scott.emp where empno='7369'
2 编写函数get_deptempcount_by_eno，返回指定工号的员工的部门的人数。
create or replace function get_deptempcount_by_eno(v_eno number)return number
as
v_result number;
--v_deptno number;
begin
--select deptno into v_deptno from scott.emp where empno=v_eno; --先查部门号
--select count(1) into v_result from scott.emp where deptno = v_deptno; --再查员工人数
select count(1) into v_result from scott.emp e1,scott.emp e2
where e2.empno='7369' and e1.deptno = e2.deptno;
return v_result;
exception
when no_data_found then
dbms_output.put_line('查找失败!');
return -1;
end;
-- 函数可以一条select中调用
select get_deptempcount_by_eno('7369')部门人数 from dual;
select e1.* from scott.emp e1,scott.emp e2 where e2.empno='7369' and e1.deptno = e2.deptno
3 编写函数get_higersal_com，入参为两个员工的工号，获得两个员工中，薪资最高的那个员工的福利 （comm）字段。如果福利为空，则返回0.
create or replace function get_higersal_com
(v_eno1 number,v_eno2 number)
return number
as
v_sal1 number;
v_sal2 number;
v_higher_eno number;--薪资高的员工的工号
v_comm number; --薪资最高的福利
v number;
begin
--各自找出对应的工资
select sal into v_sal1 from scott.emp where empno = v_eno1;
select sal into v_sal2 from scott.emp where empno = v_eno2;
if (v_sal1>v_sal2) then
v_higher_eno:=v_eno1;
else
v_higher_eno:=v_eno2;
end if;
select nvl(comm,'0') into v_comm from scott.emp where empno=v_higher_eno;
return v_comm;
exception
when no_data_found then
dbms_output.put_line('查询失败!');
return -1;
end;
--测试
select system.get_higersal_com('7369','8888') 高薪员工的福利 from dual
select nvl(comm,'0') from scott.emp where sal =
(select max(sal) from scott.emp where empno in('7369','7499')
)
流程控制
-- plsql语法
--流程控制if的用法
--输入年龄，如果小于12，显示儿童，高于12小于18，显示少年，其它显示成年
declare
v_age number;
v_sex varchar(20):='男性';
begin
v_age:= &你的年龄;
if (v_age<=12) then
dbms_output.put_line('儿童');
if(v_sex='男性') then
dbms_output.put_line('小男孩');
end if;
elsif (v_age>12 and v_age<18) then
dbms_output.put_line('少年');
else
dbms_output.put_line('成年');
end if;
end;
--输入一个整数，显示偶数或奇数
--输入一个年，显示平年还是闰年
declare
n number;
y number;
begin
n:=&输入一个整数;
y:=&输入一个年份;
if (mod(n,2)=0) then
dbms_output.put_line(n||'是偶数');
else
dbms_output.put_line(n||'是奇数');
end if;
if (mod(y,4)=0 and mod(y,100)!=0) then
dbms_output.put_line(y||'是闰年');
else
dbms_output.put_line(y||'是平年');
end if;
end;
--case
--全条件法
--查询员工的工资等级，
declare
v_salgrade number; --薪资等级
v_salary number; --薪资
begin
v_salary:=&请输入薪资;
select grade into v_salgrade from scott.salgrade where v_salary between losal and hisal;
case
when v_salgrade='1' then
dbms_output.put_line(v_salary||'属于最低等级');
when v_salgrade in (2,3,4) then
dbms_output.put_line(v_salary||'属于中等级');
when v_salgrade='5' then
dbms_output.put_line(v_salary||'属于最高等级');
else
dbms_output.put_line(v_salary||'属于其他等级');
end case;
exception
when no_data_found then
dbms_output.put_line('找不到'||v_salary||'对应等级');
end;
--等值条件法
declare
v_sleeptime int;
begin
v_sleeptime:=&请输入睡眠小时;
case v_sleeptime
when '0' then
dbms_output.put_line('神仙');
when '1' then
dbms_output.put_line('半仙');
when '2' then
dbms_output.put_line('偷鸡贼');
else
dbms_output.put_line('普通人');
end case;
end;
--等值条件法的重要应用
select grade,losal,hisal ,
case to_char(grade)
when '1' then '低级 '
when '5' then '高级'
else '普通' end as 描述 from scott.salgrade
select * from scott.dept
--- 创建视图 emp_dept_info，利用case的转换，显示以下信息
工号，员工名,部门名,部门描述(ACCOUNTING-->财务工作,RESEARCH-->研发工作,SALES-->销售工作,PERATIONS-->操作工，其他工作)
select scott.emp.empno 工号,scott.emp.ename 员工名,scott.dept.dname 部门名,
case scott.dept.dname
when 'ACCOUNTING' then '财务工作'
when 'RESEARCH' then '研发工作'
when 'SALES' then '销售工作'
when 'OPERATIIONS' then '操作工' end
as 岗位描述 from scott.emp,scott.dept
where scott.emp.deptno = scott.dept.deptno
-- for循环，输入n，显示n的斜线
*
*
*
declare
n number;
i number;
j number;
begin
n:=&请输入n;
for i in 1..n loop --i代表行
for j in 1..n loop --j代表列
dbms_output.put(' ');
exit when j=i; -- 相当于 if(xxx) break;
end loop;
dbms_output.put_line('*');
end loop;
/*
for i in 1..n loop --i代表行
for j in 1..i loop --j代表列
dbms_output.put(' ');
end loop;
dbms_output.put_line('*');
end loop;
*/
end;
--- while循环 ，计算1---100之间满足是偶数且整除于7的数的和。
declare
i number:=1;
v_sum number:=0;
begin
while(i<=100) loop
if (mod(i,2)=0 and mod(i,7)=0) then
v_sum :=v_sum+i;
end if;
i:=i+1;
end loop;
dbms_output.put_line('结果是:'||v_sum);
end;
--loop 循环.先执行再判断
declare
i number:=1;
v_sum number:=0;
begin
loop
if (mod(i,2)=0 and mod(i,7)=0) then
v_sum :=v_sum+i;
end if;
i:=i+1;
exit when i=101; --退出的条件
end loop;
dbms_output.put_line('结果是:'||v_sum);
end;
游标
https://www.cnblogs.com/guohu/p/11007352.html
https://blog.csdn.net/qq_34745941/article/details/81294166
概念和作用
- 游标是 sql 的一个内存工作区，由系统或用户以变量的形式定义
- 游标的作用就是用于临时存储从数据库中提取的数据块（结果集）。
- 它有一个
指针，从上往下移动(fetch)，从而能够遍历每条记录。 - 用
牺牲内存来提升 SQL 执行效率，适用于 大数据处理。
游标的属性有四种：
隐式游标
例子：
CREATE TABLE stu_info (
id NUMBER(3),
name VARCHAR2(30),
sex varchar2(6)
);
-- 增加主键约束
ALTER TABLE stu_info ADD CONSTRAINT pk_stu_info_id PRIMARY KEY (id) ;
-- 插入 2 条测试数据
INSERT INTO stu_info(id, name, sex) VALUES (1, '小游子', '女');
INSERT INTO stu_info(id, name, sex) VALUES (2, '小优子', '男');
COMMIT;
select * from stu_info
begin
INSERT INTO stu_info (id, NAME, sex) VALUES (3, '瑶瑶', '女');
IF SQL%FOUND THEN
dbms_output.put_line('插入成功!');
END IF;
end;
显示游标
情况1：不带参数：
DECLARE
v_stu_info stu_info%ROWTYPE;
CURSOR cur_stu_info IS
SELECT * FROM stu_info; -- 步骤1: 声明游标
BEGIN
OPEN cur_stu_info; -- 步骤2: 打开游标
-- fetch 一次只能获取一条记录，
-- 要想获取多条记录可用 循环语句 或者 集合，后面介绍
FETCH cur_stu_info
INTO v_stu_info; -- 步骤3: 提取数据
dbms_output.put_line(v_stu_info.id || ' : ' || v_stu_info.name || ' : ' ||
v_stu_info.sex);
CLOSE cur_stu_info; -- 步骤4: 关闭游标
END;
情况2：带参数（声明参数值和类型）：
DECLARE
v_stu_info stu_info%ROWTYPE;
CURSOR cur_stu_info(v_id stu_info.id%TYPE) IS
SELECT * FROM stu_info t WHERE t.id = v_id; -- 步骤1: 声明游标
BEGIN
OPEN cur_stu_info(1); -- 步骤2: 打开游标
-- fetch 一次只能获取一条记录，
-- 要想获取多条记录可用 循环语句 或者 集合，后面介绍
FETCH cur_stu_info
INTO v_stu_info; -- 步骤3: 提取数据
dbms_output.put_line(v_stu_info.id || ' : ' || v_stu_info.name || ' : ' ||
v_stu_info.sex);
CLOSE cur_stu_info; -- 步骤4: 关闭游标
END;
结果：
1 : 小游子 : 女
动态游标
情况1：弱类型，无 return（常用）
DECLARE
v_sql VARCHAR(2000);
v_b1 NUMBER(3) := 3;
v_id system.stu_info.id%TYPE;
v_name system.stu_info.name%TYPE;
TYPE cur_stu_type IS REF CURSOR; -- 无 return
cur_stu_info cur_stu_type;
BEGIN
v_sql := 'SELECT t.id, t.name
FROM stu_info t
WHERE t.id <= :b1';
OPEN cur_stu_info FOR v_sql
USING v_b1; -- 绑定变量 : 大数据处理常用优化手段
LOOP
FETCH cur_stu_info
INTO v_id, v_name;
EXIT WHEN cur_stu_info%NOTFOUND;
dbms_output.put_line('序号：' || v_id || chr(10) || '姓名：' || v_name);
END LOOP;
CLOSE cur_stu_info;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLCODE || ' : ' || SQLERRM);
dbms_output.put_line(dbms_utility.format_error_backtrace);
END;
select * from dba_objects;
--隐式游标sql
begin
update scott.emp set sal = sal*1.1 where deptno='10';
dbms_output.put_line('提取行数:'||sql%rowcount);
if(sql%isopen) then
dbms_output.put_line('游标打开');
else
dbms_output.put_line('游标已关闭');
end if;
dbms_output.put_line(case (sql%isopen) when true then '打开' else '关闭' end);
end;
-- 显式游标的经典定义
-- 使用游标遍历 scott.emp表
declare
cursor v_cur
is select empno,ename from scott.emp; -- 1定义一个游标，指向emp表记录集
v_eno number;--工号
v_ename varchar2(20); --姓名
begin
--2 打开游标
if (not v_cur%isopen) then
open v_cur;
end if;
--3 提取记录
loop
fetch v_cur into v_eno,v_ename ;
if(v_cur%notfound) then --已经到达记录的末尾
exit;
end if;
dbms_output.put_line('第'||v_cur%rowcount||'条记录:'||v_eno||','||v_ename);
end loop;
--4 关闭游标
close v_cur;
end;
-- 带参数的游标
-- -使用游标遍历指定部门编号scott.emp表
declare
cursor v_cur(v_deptno number)
is select empno,ename from scott.emp where deptno=v_deptno; -- 1定义一个带参数游标，指向emp表记录集
v_eno number;--工号
v_ename varchar2(20); --姓名
v_deptno number;--部门号
begin
--2 打开游标
v_deptno:=&请输入员工的部门编号;
if (not v_cur%isopen) then
open v_cur(v_deptno); --打开游标时，传入参数
end if;
--3 提取记录
loop
fetch v_cur into v_eno,v_ename ;
if(v_cur%notfound) then --已经到达记录的末尾
exit;
end if;
dbms_output.put_line(v_deptno||'部门的第'||v_cur%rowcount||'条记录:'||v_eno||','||v_ename);
end loop;
--4 关闭游标
close v_cur;
end;
--可修改的游标
---修改员工的common字段。如果工资低于2000,则common-200.如果工资高于3000，则common+200.
select * from scott.emp;
declare
cursor v_cur --定义一个可改游标
is
select sal,nvl(comm,'0') from scott.emp
for update of comm nowait;
v_sal number;
v_comm number;
v_count number:=0;--统计被修改的记录条数
begin
if(not v_cur%isopen) then
open v_cur;
end if;
-- 提取记录
loop
fetch v_cur into v_sal,v_comm;
if(v_cur%notfound) then
exit;
else
case
when v_sal<'2000' then
if(v_comm>200) then
update scott.emp set comm=v_comm-200 where current of v_cur;
v_count:=v_count+1;
end if;
when v_sal>='3000' then
update scott.emp set comm=v_comm+200 where current of v_cur;
v_count:=v_count+1;
else
continue;
end case;
end if;
end loop;
dbms_output.put_line('累计有'||v_count||'员工发生改变');
close v_cur;
end;
-- 游标的简化方式1
-- 无需定义 变量来接提取的字段值
--显示部门表的所有信息
select * from scott.dept
declare
cursor v_cur
is select * from scott.dept;
begin
for rec in v_cur loop
dbms_output.put_line('第'||v_cur%rowcount||'条记录:'||rec.deptno||','||rec.dname||','||rec.loc);
end loop;
end;
-- 游标的简化方式2
-- 无需定义游标变量
--显示部门表的所有信息
declare
begin
for rec in (select * from scott.dept) loop
dbms_output.put_line(rec.deptno||','||rec.dname||','||rec.loc);
end loop;
end;
--返回游标的函数
--调用者无需关注查询的细节即可遍历数据
-- 返回指向 “入职时间最长的前5个员工信息及它们的入职天数，按倒序排序”的结果的游标
create or replace function get_longesthired_emp
return sys_refcursor
as
v_cur sys_refcursor; --声明1个未初始化的游标变量
begin
--前置准备。
open v_cur for
select * from
(select scott.emp.ename,to_date(sysdate)-hiredate as 入职天数 from scott.emp order by 入职天数 desc
)where rownum<=5;
return v_cur; --返回该游标变量
end;
--调用函数
declare
v_cur sys_refcursor;
v_ename varchar2(20);
v_day number;
begin
v_cur:=get_longesthired_emp();
loop
fetch v_cur into v_ename,v_day;
if(v_cur%notfound) then
exit;
end if;
dbms_output.put_line(v_ename||'入职天数:'||v_day);
end loop;
close v_cur;--记得关闭
end;
/*
select * from
(select scott.emp.*,to_date(to_char(sysdate,'yyyy-mm-dd'))-hiredate as 入职天数 from scott.emp order by 入职天数 desc
)where rownum<=5;
*/
触发器
主键自增例子
--最简单的触发器
drop table test_t;
create table test_t
(
id number primary key,
tname varchar(20) not null
);
insert into test_t values('1','张三');
insert into test_t values('2','李四');
commit;
--创建1个insert语句前触发器
create or replace trigger test_t_tri
before insert on test_t
declare
begin
if (to_char(sysdate,'day')='星期五') then
raise_application_error(-20000,'周四无法插入数据'); --抛异常，取消插入
end if;
dbms_output.put_line('插入记录成功-语句前1');
end;
create or replace trigger test_t_tri2
before insert on test_t
declare
begin
if (to_char(sysdate,'day')='星期五') then
raise_application_error(-20000,'周四无法插入数据'); --抛异常，取消插入
end if;
dbms_output.put_line('插入记录成功-语句前2');
end;
insert into test_t values('3','王五');
insert into test_t values('5','马六');
select * from test_t;
---行触发器例子1
---插入记录的name含有 sb话，自动删除sb
create or replace trigger test_t_checkname_tri
before insert on test_t for each row --insert的行前触发器。行触发器中可以读取新插入的字段值 :new
declare
begin
if instr(:new.tname,'sb')>0 then
:new.tname := replace(:new.tname,'sb','');
dbms_output.put_line('发生替换！');
end if;
end;
insert into test_t values('6','王五');
insert into test_t values('7','basbi');
select * from test_t;
---行触发器例子2
---添加名字的长度，不能高于现存记录的最高长度，否则禁止修改
select max(length(tname)) from test_t;
create or replace trigger test_t_checkupdatename_tri
before insert on test_t for each row
declare
v_maxlen number;
begin
select max(length(tname)) into v_maxlen from test_t;
if (length(:new.tname)>v_maxlen) then
raise_application_error(-20000,'长度不能超过'||v_maxlen);
end if;
dbms_output.put_line('插入成功');
end;
insert into test_t values('10','张三糁洒洒你');
--行触发器例子3
create table test_t_bk --只赋值数据和表结构，没有赋值主键约束。
as (select * from test_t)
select * from test_t_bk;
truncate table test_t_bk;
--删除记录的备份。 :old来访问删除前的记录。将删除的每一条记录，都备份到备份表中
create or replace trigger test_t_deletebk_tri
after delete on test_t for each row
declare
begin
insert into test_t_bk values(:old.id,:old.tname);
dbms_output.put_line('备份：'||:old.id||','||:old.tname);
end;
delete from test_t where id>1
--行触发器例子4
--insert 行前触发器，实现免序列引用的主键自增
create sequence myse start with 1 nomaxvalue cache 20;
truncate table test_t;
insert into test_t values(myse.nextval,'xxx');
-->简化成
insert into test_t (tname) values('xxx');
--思路 创建行前触发器 before insert on test_t for each row
如果 :new.id 为 空，则将其替换为 myse.nextval的值。
create or replace trigger test_t_autoincrement_tri
before insert on test_t for each row
declare
begin
if (:new.id is null) then
select myse.nextval into :new.id from dual;
end if;
end;
insert into test_t (tname) values('xx');
insert into test_t values('10','xx');
insert into test_t values('15','xx');
insert into test_t values('23','xx');
select * from test_t;
--如何实现可以设置不冲突主键的触发器
create or replace trigger test_t_autoincrement_tri2
before insert on test_t for each row
declare
v_maxid number;
v_nextval number;
begin
select max(id) into v_maxid from test_t;
if (:new.id is null) then
loop
select myse.nextval into v_nextval from dual;
exit when v_nextval>v_maxid; --一直走到当前最大的id
end loop;
select myse.currval into :new.id from dual;
end if;
end;
create or replace trigger test_t_autoincrement_tri3
before insert on test_t for each row
declare
v_id number;
v_nextval number;
begin
if (:new.id is null) then
loop
select myse.nextval into v_nextval from dual;
select id into v_id from test_t where id=v_nextval; --找不到则抛异常
end loop;
end if;
exception
when no_data_found then --说明没有重复，可以使用
select myse.currval into :new.id from dual;
end;
insert into test_t (tname) values('xx');
insert into test_t values('25','xx');
select * from test_t;
包
例子
create or replace package mycalculator
is
--声明公有变量
version varchar2(20):='0.1版';
--声明公有函数
function add(i number,j number) return number;
function sub(i number,j number) return number;
function multi(i number,j number) return number;
function divide(i number,j number) return number;
--声明公有存储过程
procedure showinfo;
end;
--创建包体
create or replace package body mycalculator
is
--声明私有变量
x number:=100;
--实现公有函数
function add(i number,j number) return number
as
begin
return i+j;
end;
function sub(i number,j number) return number
as
begin
return i-j;
end;
function multi(i number,j number) return number
as
begin
return i*j;
end;
function divide(i number,j number) return number
as
r number;
begin
r:= i/j;
return r;
exception
when sys.standard.ZERO_DIVIDE then
dbms_output.put_line('0不能做除数!');
return -10000;
end;
function add_one(x number) --私有的方法！
return number
as
r number;
begin
r:=x+1;
return r;
end;
procedure showinfo --公有的存储过程
as
begin
dbms_output.put_line('这是要显示的信息!!! x='||x||',addone(x)的结果:'
||add_one(x));
end;
end;
declare
v_result number;
begin
v_result:= system.mycalculator.add(1,2);
dbms_output.put_line('result='||v_result);
dbms_output.put_line('公有变量:'||system.mycalculator.version);
system.mycalculator.showinfo(); --访问包的公有存储过程
end;
扫一扫
2224
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
