数据库之Oracle-第4篇-plsql

最新推荐文章于 2020-08-17 16:56:04 发布

baoits

最新推荐文章于 2020-08-17 16:56:04 发布

阅读量185

点赞数 2

分类专栏： Oracle 文章标签： plsql

本文链接：https://blog.csdn.net/qq_29946229/article/details/89531513

版权

Oracle 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

程序结构
	DECLARE
	    <<声明部分>>
	BEGIN
	    <<逻辑部分>>
	    COMMIT;
	    EXCEPTION
	        <<异常处理>>
		ROLLBACK;
	END;
1.数据类型
	a)NUMBER(p,s)
	b)VARCHAR2(l)
	c)DATE
	d)RAW
	e)LOB
2.变量
	变量名 类型:=初始值;
3.运算符表达式
	a.算术运算符
		+ - * / mod()
		注意: + 只做加法运算
	b.比较运算符
		> < >= <= = <>
		注意: = 只做判断
	c.逻辑运算符
		and or not
	d.赋值运算符
		:=
	e.连接运算符
		||
4.逻辑结构
	a.顺序
		输出 dbms_output.put_line();
		输入 变量:='&提示';
		注意: & 接受用户键盘输入 
		     提示语句不要涉及特殊符号 
	b.分支
		注意:Oracle沿用Basic语法
		IF 条件1 THEN
			...
		ELSIF 条件2 THEN
			...
		ELSE
			...
		END IF;
		注意:CASE语句一般用于查询与SELECT关键字一并使用
		CASE
			WHEN 条件1 THEN 结果1
			WHEN 条件2 THEN 结果2
			...
			ELSE ...
		END;
	c.循环
		--特点:循环次数不确定
		LOOP
			...
			EXIT WHEN 退出循环条件;
		END LOOP;
	
		--特点:循环次数确定
		注意:循环变量可以直接使用
		FOR 循环变量 IN 起始值..结束值
		LOOP
		END LOOP;
5.表类型
	%type	 -> 直接关联表字段的数据类型
	%rowtype -> 直接关联表(实体)类型

--HelloWorld
BEGIN
    --输出
    dbms_output.put_line('Hello World');
END;

--注意：set serveroutput on; 在PLSQL环境中默认不显示后台信息

--输入两个数字求和
DECLARE
    --声明变量
    p_num1 NUMBER:=1;
    p_num2 NUMBER:=2;
BEGIN
    --输入 & 接受用户键盘输入 
    --注意:提示语句不要涉及任何符号
    --ORA-06502 格式异常 value_error
    p_num1:='&请输入第一个数字';
    p_num2:='&请输入第二个数字';
    --输出
    dbms_output.put_line(p_num1||'+'||p_num2||'='||(p_num1+p_num2));
    EXCEPTION
        WHEN value_error THEN
             dbms_output.put_line('输入数据格式错误');
             
END;

--判断一个字 是否不三不四
DECLARE
    p_num NUMBER;
BEGIN
    p_num:='&请输入一个数字';
    IF p_num = 3 THEN
       dbms_output.put_line('这个数是三');
    ELSIF p_num=4 THEN
       dbms_output.put_line('这个数是四');
    ELSE
       dbms_output.put_line('这个数不三不四');
    END IF;
    EXCEPTION
        WHEN value_error THEN
             dbms_output.put_line('输入数据格式错误');
END;

--接受输入年份 判断该年份是否为闰年
--步步为赢 欲速则不达
DECLARE
    p_year NUMBER;
BEGIN
    p_year:='&请输入年份';
    --dbms_output.put_line(p_year);
    
    IF (MOD(p_year,4)=0 and MOD(p_year,100)<>0) or MOD(p_year,400)=0 THEN
        dbms_output.put_line(p_year||'是闰年');
    ELSE
        dbms_output.put_line(p_year||'是平年');
    END IF;
    EXCEPTION
        WHEN value_error THEN
             dbms_output.put_line('输入数据格式错误');
END;

/*
CASE
  WHEN ... THEN ...
  WHEN ... THEN ...
  ELSE ...
END  
注意:CASE语句一般用于查询与SELECT关键字一并使用
*/
--需求：底薪分类 1000以下 低 1000~2000 中 ...

select empno,ename,sal,
case
  when sal<1000 then '低'
  when sal<2000 then '中'
  when sal<3000 then '高'
  else '很高'
end lvl
from emp;
     
---------------------------------------------------

--打印1 2 3 ..100 数列
DECLARE
      p_index NUMBER:=1;
BEGIN
      LOOP
          dbms_output.put_line(p_index);
          p_index:=p_index+1;
          EXIT WHEN p_index=101;
      END LOOP;
END;

--计算1+2+...+100=?
DECLARE
   p_sum NUMBER:=0;
BEGIN
   FOR p_index IN 1..100
   LOOP
       p_sum:=p_sum+p_index;
       --dbms_output.put_line(p_index);
   END LOOP;
   dbms_output.put_line(p_sum);
END;

---------------------------------------------------
--数据库：业务操作
--1.通过数据管理而表达，
--2.通过逻辑脚本而实现。
CREATE TABLE tb_account
(
       accno VARCHAR2(12) PRIMARY KEY, --编号 开户行+开户日期+开户类型+开户顺序
       accpwd VARCHAR(6) DEFAULT('123456'),
       accname VARCHAR2(50),
       accmoney NUMBER(12,2)
);

CREATE TABLE tb_account_log
(
       logid NUMBER(10) PRIMARY KEY,
       logaccno VARCHAR2(12) REFERENCES tb_account(accno),
       logtype NUMBER(4),
       logmoney NUMBER(12,2),
       logdate DATE,
       logaccto VARCHAR2(12)
);

--序列 
CREATE SEQUENCE seq_account START WITH 1001 INCREMENT BY 1 MINVALUE 1001 MAXVALUE 1010 CYCLE CACHE 5;
CREATE SEQUENCE seq_account_log START WITH 1001 INCREMENT BY 1;
SELECT seq_account.Nextval FROM DUAL;

/*
说明:
1111 开户
2222 存款
3333 取款
4444 转出
5555 转入

111122223333 银行账户
*/

--请实现开户
DECLARE
    p_name tb_account.accname%type; --直接关联该列的数据类型 保持一致
BEGIN
    p_name:='&请输入开户人姓名';
    --添加记录
    INSERT INTO tb_account VALUES(to_char(sysdate,'yyyymmdd')||seq_account.Nextval,default,p_name,0);
    --添加日志
    INSERT INTO Tb_Account_Log VALUES(seq_account_log.nextval,
           to_char(sysdate,'yyyymmdd')||seq_account.currval,1111,0,sysdate,'111122223333');
    --提交
    COMMIT;
    dbms_output.put_line('开户成功');
    EXCEPTION
        WHEN value_error THEN
            dbms_output.put_line('格式错误');
        WHEN others THEN
            ROLLBACK;
            dbms_output.put_line('系统异常 请重试');
END;

--存款
DECLARE
    p_accno tb_account.accno%type;
    p_money tb_account.accmoney%type;
    p_count NUMBER:=0;
BEGIN
    p_accno:='&请输入目标卡号';
    p_money:='&请输入存款金额';
    --查询结果赋值 select 列 into 变量 from ...
    --注意:如果查询结果没有 会产生 no_data_found 异常
    --注意:如果查询结果不只一行 会阐述 too_many_rows 异常
    select count(accno) into p_count from tb_account where accno=p_accno;
    IF p_count = 1 THEN
       --存款
       UPDATE tb_account SET accmoney=accmoney+p_money WHERE accno=p_accno;
       --日志
       INSERT INTO Tb_Account_Log VALUES(seq_account_log.nextval,
           p_accno,2222,p_money,sysdate,'111122223333'); 
       COMMIT;
       dbms_output.put_line('存款成功');
    ELSE
       dbms_output.put_line('没有匹配的目标卡号');
    END IF;
    EXCEPTION
        WHEN value_error THEN
            dbms_output.put_line('格式错误');
        WHEN others THEN
            ROLLBACK;
            dbms_output.put_line('系统异常 请重试'); 
END;

--取款
DECLARE
    p_accno tb_account.accno%type;
    p_accpwd tb_account.accpwd%type;
    p_money tb_account.accmoney%type;
    --实体类型
    p_account tb_account%rowtype;
BEGIN
    p_accno:='&请输入目标卡号';
    p_accpwd:='&请输入交易密码';
    p_money:='&请输入取款金额';
    --获取对象信息
    --查询结果赋值 select * into 对象 from ...
    --注意:如果查询结果没有 会产生 no_data_found 异常 ORC-01403
    --注意:如果查询结果不只一行 会阐述 too_many_rows 异常 ORC-01422
    select * into p_account from tb_account where accno=p_accno;
    --对象.属性
    --dbms_output.put_line(p_account.accno || ' '|| p_account.accpwd || ' '||p_account.accname);
    --密码判断
    IF p_account.accpwd = p_accpwd THEN
        IF p_account.accmoney > p_money THEN
           --业务
           UPDATE tb_account SET accmoney=accmoney-p_money WHERE accno=p_accno;
           --日志
           INSERT INTO Tb_Account_Log VALUES(seq_account_log.nextval,
                  p_accno,3333,p_money,sysdate,'111122223333');
           COMMIT;
           dbms_output.put_line('取款成功');
        ELSE
           dbms_output.put_line('余额不足');
        END IF;
    ELSE
       dbms_output.put_line('密码错误 请重试');
    END IF;
    
    EXCEPTION
        WHEN value_error THEN
            dbms_output.put_line('格式错误');
        WHEN no_data_found THEN
            dbms_output.put_line('没有匹配的目标卡号');
        WHEN others THEN
            ROLLBACK;
            dbms_output.put_line('系统异常 请重试'); 
END;

--------------------------------------------------------------------

DECLARE
    p_accno tb_account.accno%type;
    p_accpwd tb_account.accpwd%type;
    p_money tb_account.accmoney%type;
    --实体类型
    p_account tb_account%rowtype;
    
CREATE PROCEDURE pro_getmoney(p_accno tb_account.accno%type,
                              p_accpwd tb_account.accpwd%type,
                              p_money tb_account.accmoney%type)
AS
    p_account tb_account%rowtype;                                    
BEGIN
    --获取对象信息
    --查询结果赋值 select * into 对象 from ...
    --注意:如果查询结果没有 会产生 no_data_found 异常 ORC-01403
    --注意:如果查询结果不只一行 会阐述 too_many_rows 异常 ORC-01422
    select * into p_account from tb_account where accno=p_accno;
    --对象.属性
    --dbms_output.put_line(p_account.accno || ' '|| p_account.accpwd || ' '||p_account.accname);
    --密码判断
    IF p_account.accpwd = p_accpwd THEN
        IF p_account.accmoney > p_money THEN
           --业务
           UPDATE tb_account SET accmoney=accmoney-p_money WHERE accno=p_accno;
           --日志
           INSERT INTO Tb_Account_Log VALUES(seq_account_log.nextval,
                  p_accno,3333,p_money,sysdate,'111122223333');
           COMMIT;
           dbms_output.put_line('取款成功');
        ELSE
           dbms_output.put_line('余额不足');
        END IF;
    ELSE
       dbms_output.put_line('密码错误 请重试');
    END IF;
    
    EXCEPTION
        WHEN value_error THEN
            dbms_output.put_line('格式错误');
        WHEN no_data_found THEN
            dbms_output.put_line('没有匹配的目标卡号');
        WHEN others THEN
            ROLLBACK;
            dbms_output.put_line('系统异常 请重试'); 
END;


BEGIN
    pro_getmoney('201803091009','123456',200);
END;

select * from tb_account;
select * from tb_account_log;

select * from emp;

--数据库存在的价值:将所有的业务操作和数据量化

--转账
DECLARE
    --转出卡号
    p_accno tb_account.accno%type;
    --转出卡号密码
    p_accpwd tb_account.accpwd%type;
    --转账金额
    p_money tb_account.accmoney%type;
    --转入卡号
    p_accto tb_account.accno%type;
    --临时存储 账户对象
    p_account tb_account%rowtype;
    --统计数量
    p_count NUMBER:=0;
BEGIN
    p_accno:='&请输入卡号';
    p_accpwd:='&请输入密码';
    p_money:='&请输入金额';
    p_accto:='&请输入目标卡号';
    --卡号是否存在
    select * into p_account from tb_account where accno = p_accno;
    --密码是否正确
    IF p_account.accpwd = p_accpwd THEN
        --余额是否充足
        IF p_account.accmoney>p_money THEN
            --目标卡号是否存在
            select count(accno) into p_count from tb_account where accno = p_accto;
            IF p_count=1 THEN
                --细节 不允许自己转账
                IF p_accno <> p_accto THEN
                    --流程
                    --转出
                    update tb_account set accmoney = accmoney-p_money where accno = p_accno;
                    --日志
                    insert into tb_account_log values(SEQ_ACCOUNT_LOG.Nextval,p_accno,'4444',
                           p_money,SYSDATE,p_accto);
                    --转入
                    update tb_account set accmoney = accmoney+p_money where accno = p_accto;
                    --日志
                    insert into tb_account_log values(SEQ_ACCOUNT_LOG.Nextval,p_accto,'5555',
                           p_money,SYSDATE,p_accno);
                    COMMIT;
                    dbms_output.put_line('转账成功');
                ELSE
                    dbms_output.put_line('同卡之间不允许转账');
                END IF;
            ELSE
                dbms_output.put_line('目标卡号不存在');
            END IF;
        ELSE
            dbms_output.put_line('余额不足');
        END IF;
    ELSE
        dbms_output.put_line('密码输入错误');
    END IF;
    EXCEPTION
        WHEN value_error THEN
            dbms_output.put_line('输入信息格式错误');
        WHEN no_data_found THEN
            dbms_output.put_line('卡号不存在');
        WHEN others THEN
            ROLLBACK;
            dbms_output.put_line('系统异常 请重试');
END;

SELECT * FROM TB_ACCOUNT;

SELECT * FROM TB_ACCOUNT_LOG;

--需求:打印流水
--查询脚本 不允许 直接在PL/SQL块中执行SELECT ... FROM ...
--只允许使用 SELECT ... INTO ... FROM  注意:no_date_found too_many_rows
--解决方案 ： Oracle游标
--小结：在PL/SQL中 如果要操作多行数据 必须使用游标实现
/*
BEGIN
    SELECT * FROM TB_ACCOUNT_LOG WHERE LOGACCNO='201803091009';
END;
*/
s
-----------------------------------------------------------------------
/*
案例 emp -- 输入员工编号和调整的底薪 完成员工的底薪调整
	    要求:
		1.调整底薪的幅度不能大于原有底薪的10%
		2.半年之内只允许调整一次 (创建底薪调整日志表)
*/
CREATE TABLE tb_sal_log
(
       logid NUMBER PRIMARY KEY,
       logempno NUMBER(4),
       logmoney NUMBER(7,2),
       logdate DATE
);

--序列
CREATE SEQUENCE SEQ_LOGID START WITH 1001 INCREMENT BY 1;

DECLARE
    --工号
    p_empno emp.empno%type;
    --涨幅
    p_money emp.sal%type;
    --临时存储 员工对象 日志对象
    p_emp emp%rowtype;
    p_date tb_sal_log.logdate%type;
    p_count number:=0;
BEGIN
    p_empno:='&请输入工号';
    p_money:='&请输入涨幅';
    --工号是否存在
    select * into p_emp from emp where empno= p_empno;
    --涨幅范围
    IF p_emp.sal*0.1>=p_money THEN
         --调整时间
         --错误1 -> 没有记录 第一次涨工资  -> no_date_found
         --错误2 -> 有多条数据 第三次涨工资 -> too_many_rows
         select count(logid) into p_count from tb_sal_log where logempno = p_empno;
         IF p_count = 0 THEN
             --第一次涨工资 直接操作
             update emp SET sal=sal+p_money where empno = p_empno;
             --日志
             insert into tb_sal_log VALUES(SEQ_LOGID.NEXTVAL,p_empno,p_money,SYSDATE);
             COMMIT;
             dbms_output.put_line('恭喜了');
         ELSE
             --获取最近一次调整数据
             select max(logdate) into p_date from tb_sal_log where logempno = p_empno;
             IF months_between(sysdate,p_date)>=6 THEN
                  update emp SET sal=sal+p_money where empno = p_empno;
                  --日志
                  insert into tb_sal_log VALUES(SEQ_LOGID.NEXTVAL,p_empno,p_money,SYSDATE); 
                  COMMIT;  
                  dbms_output.put_line('恭喜了');                             
             ELSE
                 dbms_output.put_line('时间间隔没有半年');
             END IF;
         END IF;
    ELSE
         dbms_output.put_line('涨幅不能超过10%');
    END IF;
    EXCEPTION
        WHEN value_error THEN
            dbms_output.put_line('输入信息格式错误');
        WHEN no_data_found THEN
            dbms_output.put_line('工号不存在');
        WHEN others THEN
            ROLLBACK;
            dbms_output.put_line('系统异常 请重试');
END;


SELECT * FROM emp;
SELECT * FROM tb_sal_log;