版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/zhao1299002788/article/details/101758110
/*
授权命令 grant 权限 to 用户
授权可以授予多个权限
grant connect,resource to baidu
收回权限 revoke 权限 from 用户
revoke dba from baidu
创建用户 分配表空间--指定用户的默认表空间
create table p(..) 建表存在默认表空间
--建表时候指定表空间
create table p(...) talebspace 表空间名
*/
/*
PlSql编程语言 procedure language 过程语言
是在sql语句中加入一些处理过程的语句
常见的条件表达式 if else 还有循环结构
基本结构
declare
--声明部分 理解为定义
--声明使用的变量
begin
--处理逻辑的代码块
end;
*/
--psSql简单示例
declare
v_n number := 1; --声明数值变量 赋值使用符号:=
v_s varchar2(4) :='s'; --声明字符类型变量
emp_ename emp.ename%type ;-- 引用类型变量
emp_row emp%rowtype ;-- 记录类型变量
begin
dbms_output.put_line('v_n====='||v_n); --输出语句相当于sys out
dbms_output.put_line('v_s====='||v_s);
select ename into emp_ename from emp where empno=7499; --使用into关键字赋值
dbms_output.put_line('emp_ename====='||emp_ename);
select * into emp_row from emp where empno = 7499; --赋值记录类型变量
dbms_output.put_line('员工编号=='||emp_row.empno||'员工姓名'||emp_row.ename);
end;
/*
plsql 的条件表达式判断
if .. 处理语句 else if ..处理语句
-------------------------
if .. then
elsif .. then
else
end if;
*/
---使用条件表达式判断员工的工资 使用汉字输出
declare
emp_row emp%rowtype ;-- 记录类型变量
begin
select * into emp_row from emp where empno = 7499; --赋值记录类型变量
--使用表达式判断工资
if emp_row.sal > 3000 then
dbms_output.put_line('员工工资大于3000=='||emp_row.sal);
elsif emp_row.sal < 1000 then
dbms_output.put_line('员工工资小于1000=='||emp_row.sal);
else
dbms_output.put_line('员工工资位于1000到3000之间=='||emp_row.sal);
end if;
end;
/*
循环结构
第一种-----
loop
exit when 条件
end loop;
第二种 ---
while 条件 loop
end loop;
第三种 ---
for 变量 in 范围 loop
end loop;
*/
-------使用循环输出数字 1-----10
/*
第一种
loop
exit when 条件
end loop;
*/
declare
v_n number :=1;
begin
loop
--只是用来判断退出使用的,并不是相当于if()else{}
exit when v_n>10 ; --退出条件
dbms_output.put_line(v_n);
v_n:=v_n+1; --自增
end loop;
end;
/*
第二种
while 条件 loop
end loop;
*/
declare
v_n number :=1;
begin
while v_n<11 loop
dbms_output.put_line(v_n);
v_n:=v_n+1; --自增
end loop;
end;
/*
第三种
for 变量 in 范围 loop 变量的声明和范围的控制是由for循环自动执行
end loop;
*/
declare
begin
for i in 1..10 loop
dbms_output.put_line(i);
end loop;
end;
/*
游标 光标 是用于接收查询的记录结果集 ResultSet 提示记录使用.next()
游标的使用步骤
声明游标 cursor 游标名 is select 语句 指定游标的记录结果集
打开游标 open 游标名
提取游标 fetch 游标名 into 记录类型变量
关闭游标 close cursor
游标的两个属性 游标名%found : 判断它有找到
游标名%notfound : 判断它没有找到
if emp_cursor%found then
dbms_output.put_line('found');
elsif emp_cursor%notfound then
dbms_output.put_line('notfound');
elsif emp_cursor%notfound is null then
dbms_output.put_line('null');
end if;
*/
--使用while循环结构演示游标
declare
--声明游标
cursor emp_cursor is select * from emp;
--声明记录类型变量 用于接收游标提取的记录
emp_row emp%rowtype;
begin
--打开游标
open emp_cursor;
--提取游标(判断下一个是否有值)
fetch emp_cursor into emp_row ;
--有值就执行while循环
while emp_cursor%found loop
dbms_output.put_line(emp_row.empno||'员工姓名'||emp_row.ename);
--继续提取游标(并判断下一个是否有值)
fetch emp_cursor into emp_row ;
end loop;
close emp_cursor;
end;
/*
loop
exit when 游标提取不到
end loop
*/
declare
--声明游标
cursor emp_cursor is select * from emp;
--声明记录类型变量 用于接收游标提取的记录
emp_row emp%rowtype;
begin
--打开游标
open emp_cursor;
loop
fetch emp_cursor into emp_row;
exit when emp_cursor%notfound;
dbms_output.put_line(emp_row.empno||'员工姓名'||emp_row.ename);
end loop;
close emp_cursor;
end;
--使用游标提取某个部门的员工信息
--声明带参数的游标信息
declare
--声明游标
cursor emp_cursor(dno number) is select * from emp where deptno = dno ;
--声明记录类型变量 用于接收游标提取的记录
emp_row emp%rowtype;
begin
--打开游标 时候传入参数
open emp_cursor(10);
loop
fetch emp_cursor into emp_row;
exit when emp_cursor%notfound;
dbms_output.put_line(emp_row.empno||'员工姓名'||emp_row.ename);
end loop;
close emp_cursor;
end;
/*
错误信息开发中的异常
数据库中叫做 例外
异常的分类 1.系统异常 系统定义好的异常
2.自定义的异常
new 自定义类继承Exception 自定义传值（错误代码，提示信息）
使用场景
不满足某些特定业务场景，抛出自定义异常
异常的处理
java try{}catche(IndexOutOfBoundException e){}catche(Exception e){}
java try{}catche(Exception e){} catche(IndexOutOfBoundException e){}--报错
数据库可以捕捉处理异常
exception 关键字捕捉异常
when 异常类型 then 处理语句 判断异常类型 处理异常
*/
--异常的简单示例
/*
--除0的异常 除数为0
--赋值错误
*/
declare
v_n number :=0;
v_m number :=1;
begin
v_m:='s'; --将字符串赋值给数值变量
v_m:= v_m/v_n;
exception
when zero_divide then
dbms_output.put_line('除数不能为0');
when value_error then
dbms_output.put_line('赋值有误');
end;
---处理太多记录数异常
declare
emp_row emp%rowtype ;-- 记录类型变量
begin
select * into emp_row from emp ; --赋值记录类型
exception
when too_many_rows then
dbms_output.put_line('太多记录数');
when others then --others是最大范围的异常 相当于java 的 Exception
dbms_output.put_line('其他异常');
end;
/*
需求 ：使用游标查询部门下的员工信息
如果部门下没有员工 报错提示
需要自定义异常
变量名 exception --声明自定义异常
*/
declare
cursor emp_cursor is select * from emp where deptno= 40; --游标结果集不存在
emp_row emp%rowtype ;-- 记录类型变量
no_dept_emp exception ; --声明自定义异常
begin
open emp_cursor; --打开游标
fetch emp_cursor into emp_row;
if emp_cursor%notfound then
--没有员工 抛出错误异常
raise no_dept_emp;
end if;
close emp_cursor;
exception
when no_dept_emp then
dbms_output.put_line('部门下面没人，快招人吧');
end;
/*
存储过程 是一段封装好的代码块，过程是编译好放在服务器提供开发人员调用
封装的代码块意义： 提升开发效率 可以复用 谁用直接调用
提升运行效率 一调用直接运行
语法：create [or repalce] procedure 过程名称(参数名 out|in 参数类型)
as|is
--声明变量的部分
begin
--处理过程语句代码块
end;
调用存储过程
在begin和end之间使用 过程名传参调用
*/
--存储过程的简单示例 使用存储过程给某个员工增加工资100
create or replace procedure add_sal(eno in number )
as
emp_sal number :=0;
begin
select sal into emp_sal from emp where empno = eno ;
dbms_output.put_line('涨工资之前是===='||emp_sal);
update emp set sal=sal+100 where empno = eno;
select sal into emp_sal from emp where empno = eno ;
dbms_output.put_line('涨工资之后是===='||emp_sal);
commit;
end;
--------调用存储过程
declare
begin
add_sal(7499);
end;
/*
使用存储过程统计某个员工的年薪，年薪需要返回输出打印
in 类型输入参数可以 省略 默认就是输入参数
*/
create or replace procedure count_sal(eno number,year_sal out number)
as
begin
select sal*12+nvl(comm,0) into year_sal from emp where empno=eno; --使用into赋值给输出参数
end;
----调用存储过程计算年薪
declare
v_emp_sal number :=0;
begin
count_sal(7499,v_emp_sal);
dbms_output.put_line('年薪为=='||v_emp_sal);
end;
/*
使用存储过程 查询出某个部门的员工信息
某个部门应该接受一个in类型的输入参数
查询到的部门员工多条记录返回应该使用结果集
声明游标 cursor 游标名 is select 语句指定结果集
系统引用游标
sys_refcursor
声明系统引用游标 变量名 sys_refcursor; --不需要指定结果集
打开游标 open 系统引用游标 for select 语句 --使用for关键字装入数据
*/
create or replace procedure dept_emp(dno number,cusor_emp out sys_refcursor)
as
begin
--根据传进来的部门编号给游标装入结果集数据
open cusor_emp for select * from emp where deptno = dno;
end;
----调用存储过程查询部门下的员工
declare
cursor_emp sys_refcursor; --声明系统引用游标传参使用
emp_row emp%rowtype ;--记录类型变量
begin
dept_emp(10,cursor_emp);
--提取游标中的数据
loop
fetch cursor_emp into emp_row;
exit when cursor_emp%notfound;
dbms_output.put_line('编号'||emp_row.empno||'姓名'||emp_row.ename);
end loop;
close cursor_emp;
end;
/*
存储函数 是一段封装好的代码块，是编译好放在服务器提供开发人员调用
封装的代码块意义： 提升开发效率 可以复用 谁用直接调用
提升运行效率 一调用直接运行
语法：create [or repalce] function 函数名称(参数名 out|in 参数类型) return 数据类型
in 代表传入参数,out 代表传出参数
as|is
--声明变量的部分
begin
--处理过程语句代码块
--return 变量
end;
调用存储函数
在begin和end之间使用 函数名传参调用 函数必须使用变量接收 返回值
*/
--使用存储函数统计某个员工的年薪
create or replace function count_emp_sal(eno number,year_sal out number) return number
as
v_sal number :=0;
begin
select sal*12+nvl(comm,0) into year_sal from emp where empno=eno; --使用into赋值给输出参数
return v_sal;
end;
--不带out类型输出参数统计年薪
create or replace function count_sal_noout(eno number) return number
as
v_sal number :=0;
begin
select sal*12+nvl(comm,0) into v_sal from emp where empno=eno; --使用into赋值给输出参数
return v_sal;
end;
--调用函数统计年薪
declare
emp_sal number:=0;
total_sal number :=0;
begin
--total_sal := count_emp_sal(7499,emp_sal);
total_sal := count_sal_noout(7499);
dbms_output.put_line(emp_sal);--0
dbms_output.put_line(total_sal); --统计后年薪
end;
/*
存储函数和过程的区别
1.创建的关键字 procedure funciton
2.创建函数 必须使用return 声明函数的返回变量数据类型
3.在函数的方法体内 必须使用return 返回一个变量
4.函数的调用 必须有变量接收返回值
5.函数可以用在select 查询语句中 select emp.*,count_sal_noout(empno) from emp;
存储函数和过程使用场景
开发规范 java代码待用过程 过程是用来处理业务逻辑代码
如果逻辑中需要用到一些功能性的封装，可以调用函数
90%情况下 函数和过程通用 过程可以调用函数，函数同样可以调用过程
*/
/*
触发器 是一个监视器，监视对表中数据的操作
如果对数据的操作满足触发器的执行条件，
触发器会自动运行
触发器语法：
create or repalce trigger 触发器名称
after|before --触发器执行时机
insert|update|delete --监视的动作
on 表名 --表级触发器
declare
begin
end;
行级触发器 insert update delete
:new 动作之后的记录 要插入的记录 修改后的记录 空
:old 动作之前的记录 空 原始的记录 原始的记录
*/
--创建触发器监视表，如果表中有数据插入，输出一个欢迎语句
create or replace trigger insert_trigger
after
insert
on p
declare
begin
dbms_output.put_line('欢迎加入！');
end;
----插入数据测试效果
insert into p values(1,'zs');
commit;
--插入数据不能在休息日插入数据
--休息日 周六和周日
/*
raise_application_error(v1,v2) v1错误代码 v2是提示语句
-20000 -20999
*/
create or replace trigger insert_no_work
before
insert
on p
declare
v_day varchar2(10) ;
begin
--获取到当前星期
select to_char(sysdate,'day') into v_day from dual;
--判断星期是否在休息日
if trim(v_day) in ('saturday','sunday') then
--如果休息 错误提示
raise_application_error(-20001,'不能休息日插入数据');
end if;
end;
----插入数据测试效果
insert into p values(1,'zs');
commit;
--使用触发器监视表中数据修改，不能做降低工资的操作
create or replace trigger can_not_low
before
update
on emp
for each row --行级触发器
declare
begin
--获取到原始记录的工资 --获取修改后的工资
if :old.sal > :new.sal then
--谈错误框提示
raise_application_error(-20002,'不能降低工资');
end if;
end;
--修改员工的工资测试触发器
update emp set sal=sal-1 where empno=7499;
/*
触发器实际应用
使用触发器实现 插入数据的id 自增长 面试题
**/
create or replace trigger auto_increment_id
before
insert
on test_trigger
for each row
declare
begin
--补全将要插入记录的id
--补全的id 是自增长的数值 如果没有提前创建序列,需要提前创建序列 --创建序列 create sequence order_sequence
select order_sequence.nextval into :new.pid from dual;
end;
insert into test_trigger(pname,phone) values('zs','1234566');
commit;
package baidu;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import org.junit.Test;
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;
public class TestJdbc {
String driverClass = "oracle.jdbc.driver.OracleDriver";
String url ="jdbc:oracle:thin:@192.168.17.128:1521:orcl";
String user= "baidu_03";
String password = "baidu_03";
/*
*测试jdbc连接数据库
*
* */
@Test
public void querEmp(){
try{
//加载驱动
Class.forName(driverClass);
//获取链接
Connection con = DriverManager.getConnection(url, user,password);
//获取预编译的statement
PreparedStatement pst= con.prepareStatement("select * from emp");
//执行查询
ResultSet rs = pst.executeQuery();
//处理结果
while(rs.next()){
System.out.println(rs.getInt(1)+"员工姓名"+rs.getString("ename"));
}
rs.close();
con.close();
//关闭连接
}catch(Exception e){
e.printStackTrace();
}
}
/*存储过程的调用
* {call <procedure-name>[(<arg1>,<arg2>, ...)]}
add_sal(eno number,addsal number)
* */
@Test
public void callAddSal(){
try{
//加载驱动
Class.forName(driverClass);
//获取链接
Connection con = DriverManager.getConnection(url, user,password);
//获取预编译的statement
CallableStatement pst= con.prepareCall("{call add_sal(?,?)}");
pst.setInt(1, 7499);
pst.setInt(2, 1000);
//执行查询
pst.execute();
con.close();
//关闭连接
}catch(Exception e){
e.printStackTrace();
}
}
/*存储过程的调用
* {call <procedure-name>[(<arg1>,<arg2>, ...)]}
count_yearsal(eno number,total_year_sal out number)
* */
@Test
public void callCountSal(){
try{
//加载驱动
Class.forName(driverClass);
//获取链接
Connection con = DriverManager.getConnection(url, user,password);
//获取预编译的statement
CallableStatement pst= con.prepareCall("{call count_yearsal(?,?)}");
pst.setInt(1, 7499);
//注册输出参数
pst.registerOutParameter(2, OracleTypes.NUMBER);
//执行查询
pst.execute();
int total = pst.getInt(2);
System.out.println(total);
con.close();
//关闭连接
}catch(Exception e){
e.printStackTrace();
}
}
/*
* pro_dept_emp(dno number,dept_emp out sys_refcursor)
* */
@Test
public void callProEmp(){
try{
//加载驱动
Class.forName(driverClass);
//获取链接
Connection con = DriverManager.getConnection(url, user,password);
//获取预编译的statement
CallableStatement pst= con.prepareCall("{call pro_dept_emp(?,?)}");
pst.setInt(1, 10);
//注册输出参数
pst.registerOutParameter(2, OracleTypes.CURSOR);
//执行查询
pst.execute();
OracleCallableStatement ocs = (OracleCallableStatement)pst;
ResultSet rs = ocs.getCursor(2);
while(rs.next()){
System.out.println(rs.getInt(1)+"员工姓名"+rs.getString("ename"));
}
rs.close();
ocs.close();
pst.close();
con.close();
//关闭连接
}catch(Exception e){
e.printStackTrace();
}
}
}
public void show4(){
try {
Class.forName(driverClass);
Connection con = DriverManager.getConnection(url, user,password);
CallableStatement pst= con.prepareCall("{?= call count_sal_noout(?)}");
//给第二个参数赋值
pst.setLong(2, 7499);
// stat2.setLong(2, empno);
//声明第一个参数的类型
pst.registerOutParameter(1, OracleTypes.NUMBER);
pst.execute();
OracleCallableStatement ocs = (OracleCallableStatement)pst;
NUMBER num = ocs.getNUMBER(1);
System.out.println(num);
// long i = pst.getLong(1);
// System.out.println(i);
con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
/* 复制