sql编程
约 1571 字大约 5 分钟
2025-04-10
一、PL/SQL 编程语言
1. 输出 hello
BEGIN
dbms_output.put_line('hello !');
END ;2. 定义变量，输出hello
DECLARE
--定义你所需要的所有变量
v_msg varchar2(32) := ' hello '
BEGIN
dbms_output.put_line(v_msg);
END;3. 输出scott的工资
DECLARE
V_SAL EMP.SAL%TYPE;
BEGIN
SELECT SAL INTO V_SAL FROM EMP WHERE ENAME = 'SCOTT';
dbms_output.put_line(V_SAL);
END4. 展示 编号，姓名，职位，薪资
DECLARE
V_EMP_OBJ EMP%ROWTYPE;
BEGIN
SELECT * INTO V_EMP_OBJ FROM EMP WHERE ENAME ='SCOTT';
dbms_output.put_line(v_EMP_OBJ.ENAME);
END5. 接收用户的输入并打印
DECLARE
V_MSG VARCHAR2(32) := &请输入您要打印的话
BEGIN
dbms_output.put_line(V_MSG);
END
--只能接受数字6. 从控制台接收指定输入然后输出
DECLARE
V_NUM number := &NUM
BEGIN
IF V_NUM=1 THEN
dbms_output.put_line('输入正确！');
ELSE
dbms_output.put_line('输入错误！');
END IF;
END7. 循环输出1-10
DECLARE
V_INDEX NUMBER(10) :=1
BEGIN LOOP
EXIT WHEN V_INDEX >10
dbms_output.put_line(v_INDEX);
V_INDEX := V_INDEX+1;
END LOOP;
ENDDECLARE
V_INDEX NUMBER(10) :=1
BEGIN LOOP
WHILE V_INDEX<=10
dbms_output.put_line(v_INDEX);
V_INDEX := V_INDEX+1;
END LOOP;
ENDDECLARE
V_INDEX NUMBER(10) :=1
BEGIN
FOR i IN 1..10 LOOP
dbms_output.put_line(i);
END LOOP;
END8.怎么添加10个班级
--创建序列
CREATE SEQUENCE SEQ_GRADE NOCYCLE NOCACHE;BEGIN
FOR i IN 1..10 LOOP
IF i < 10 THEN
INSERT INTO GRADE
VALUES (SEQ_GRADE.NEXTVAL,'三年一班'||i,'Java');
ELSE
INSERT INTO GRADE
VALUES (SEQ_GRADE.NEXTVAL,'三年二班'||i,'C++');
END IF;
END LOOP;
COMMIT;
END;二、游标
1. 简介
oracle 读取数据的工具，类似java集合
2. 示例
DECLARE
-- 获取emp集合
CURSOR EMPLIST IS SELECT * FROM EMP ;
-- 定义emp 表的对象 进行解析
V_EMPOBJ EMP%ROWTYPE;
BEGIN
-- 解析游标（集合）
-- 1. 打开游标
OPEN EMPLIST;
LOOP
EXIT WHEN EMPLIST%NOTFOUND
FETCH EMPLIST INTO V_EMPOBJ;
dbms_output.put_line(v_empobj.empno||v_empobj.ename);
END LOOP;
-- 2. 关闭游标
CLOSE EMPLIST;
END3. 简化版
DECLARE
CURSOR GLIST IS SELECT * FROM GRADE;
BEGIN
FOR g IN GLIST LOOP
dbms_output.put_line(g.gid);
END LOOP;
END三、异常处理
1. 预定义错误
弹窗 终止程序运行
2. 处理异常
EXCEPTION
WHEN TOO_MANY_ROWS THEN
dbms_output.put_line('返回条数过多，不能赋值');
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('要查找的数据不存在');3. 自定义异常
-- 如果有员工工资小于1000 抛出异常提示
DECLARE
CURSOR emplist IS SELECT * FROM EMP ;
-- 1. 定义异常
SAL_TOO_LOW EXCEPTION;
BEGIN
FOR e IN emplist LOOP
IF e.sal < 1000 THEN
-- 2. 引发异常
RAISE SAL_TOO_LOW;
END IF;
END LOOP;
-- 3. 接收并处理异常
EXCEPTION
WHEN SAL_TOO_LOW THEN
dbms_output.put_line('有员工工资太低');
raise_application_error(-20001，'工资过低，请及时涨工资');
--错误信息标号 必须小于 -20000
END4. 其他异常
4.1. 所有未拦截的异常都应该被others接收
WHEN OTHERS THEN dbms_output.put_line('所有未拦截的异常');4.2. 拦截顺序
先是预定义 再是自定义 最后是others
四、存储过程 (没有返回值)
存储过程是一个有名字的 plsql块
--根据员工编号给指定员工涨工资，并打印相关信息
create or replace procedure updateempbyempno(
eno emp.empno%type --没有分号
) as
--声明变量的地方
a_sal emp.sal%type ; --涨工资之前
b_sal emp.sal%type ; --涨工资之后
begin
select sal into a_sal from emp where empno = eno;
dbms_output.put_line('涨工资之前为'||a_sal);
--涨工资
update emp set sal=sal+100 where empno = eno;
commit;
--查询涨了之后的工资
select sal into b_sal from emp where empno = eno;
dbms_output.put_line('涨工资之后为' || b_sal);
end;
-- 调用存储过程
begin
updateempbyempno(7521);
end;五、存储函数
--根据员工编号查询员工的年薪
create or replace function FINDSALBYEMPNO(eno EMP.SAL%TYPE)
RETURN NUMBER --eno不能和字段名一致
AS
v_nianxin number(11,2);
BEGIN
-- 查询年薪
select sal*12+nvl(comm,0) into v_nianxin from emp where empno=eno;
return v_nianxin;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('没有找到数据');
WHEN TOO_MANY_ROWS THEN
dbms_output.put_line('输出数据过多');
END;
-- 调用存储函数
DECLARE
--定义方法返回值的接受变量
v_sal number(11,2);
BEGIN
v_sal := FINDSALBYEMPNO(7521);
dbms_output.put_line('年薪为'|| v_sal);
END六、多返回值存储过程
1. 示例1
--根据编号返回员工名称和年薪
create or replace procedure findempinfobyempno(
eno in emp.empno%type, --[in 输入模式]
empname out emp.ename%type,--[out 输出模式]
nianxin out number --[不是表中的类型，不需要带长度]
)
as
begin
select ename,sal*12+nvl(comm,0) into empname,nianxin
from emp where empno = eno;
end;
--调用带out的存储过程
DECLARE
ename emp.ename%type,
nianxin number(11,2)
BEGIN
findempinfobyempno(7521,ename,nianxin);
dbms_output.put_line(ename||'的年薪是：'||nianxin);
END;2. 示例2
当存储过程的参数类型是游标类型时，打开游标时设置查询结果进去
因为在定义存储过程时将游标打开了，所以调用存储过程时不能用for循环 而且记得关闭游标
--获取所有人的年薪,参数为游标类型
create or replace procedure findALlSal(allsal out sys_refcursor) as
begin
open allsal for select sal*12+nvl(comm.0) from emp;
end;
-- 调用存储过程
DECLARE
--存储游标类型所有的年薪
ALLSAL SYS_REFCURSOR;
SAL EMP.SAL%TYPE;
BEGIN
--调用
FINDALLSAL(ALLSAL);
--循环
LOOP
--出口
EXIT WHEN ALLSAL%NOTFOUND;
--打印
DBMS_OUTPUT.PUT_LINE(SAL);
--注入
FETCH ALLSAL INTO SAL;
END LOOP;
--结束循环
CLOSE ALLSAL;
--关闭游标
END;七、触发器
1. 触发器的类型
语句级触发器：语句每执行一次就会执行一次
行级的触发器
2. 语句级的触发器
2.1. 插入班级信息以后，打印添加成功
create or replace trigger gradetg1
after insert on grade
declare
begin
dbms_output.put_line("添加成功");
end;2.2. 不能在星期四 添加班级信息
create or replace tirgger gradetg2
before insert on grade
declare
v_week varchar(32);
BEGIN
SELECT TO_CHAR(SYSDATE,'DAY') INTO V_WEEK FROM DUAL;
IF V_WEEK = '星期四' THEN
Raise_application_error(-20001,'星期四不能添加班级!');
END IF;
END;3. 行级的触发器
3.1. 需求1 判断员工是否真正涨工资
--怎么获得涨工资之前或者之后的工资 ( for each row )
--:old 和 :new 代表同一条记录
--:old 表示操作该行之前，这一行的值
--:new 表示操作该行之后，这一行的值
create or replace trigger emptg1
before update on emp for each row
declare
begin
if :new.sal = :old.sal < 0 then
raise_application_error(-20002，'不能降工资');
end;4. 实际应用
4.1. 触发器的主键自增
--创建序列
create sequence seq_userinfo nocycle nocache;
--行级触发器
create or replace trigger usertg1
before insert on user_info for each row
declare
begin
select seq_userinfo.nextval into :new.uid FROM dual;
end;4.2. 日志记录功能
--创建序列
create seqence seq_syslog nocycle nocache;
-- 被班级表进行日志记录
create or replace trigger gradetg3
after update on grade for each row
DECLARE
v_time varchar(128);
v_msg CLOB;
BEGIN
--赋值当前时间
select to_char(SYSDATE,'yyyy-MM-dd hh24:mm:ss') into v_time from dual;
--插入入职表中
v_msg := '更新,发生在'||V_TIME||',操作前['||:old.g_name||']操作后['||:new.g_name||']';
insert into sys_log values (seq_syslog.Nextval,v_msg);
dbms_output.put_line('日志记录成功!');
END;