Home

hogen

Follow Excellence. Success will chase you.

Home About Github Email

plsql 学习第一弹

SQL99是什么

  1. 是操作所有关系型数据库的规则
  2. 是第四代语言
  3. 是一种结构化查询语言
  4. 只需发出合法合理的命令,就有对应的结果显示

SQL 的特点

  1. 交互性强,非过程化
  2. 数据库操纵能力强,只需发送命令,无需关注如何实现
  3. 多表操作时,自动导航简单,例如:select emp.empno,emp.sal,dept.dname from emp,dept where emp.deptno = dept.deptno
  4. 容易调试,错误提示,直接了当
  5. SQL强调结果

PLSQL 是什么

是专用于 Oracle 服务器,在 SQL 基础之上,添加了一些过程化控制语句,叫 PLSQL,过程化包括有:类型定义,判断,循环,游标,异常或例外处理。。。PLSQL强调过程

为什么要用 PLSQL

因为SQL是第四代命令式语言,无法显示处理过程化的业务,所以得用一个过程化程序设计语言来弥补SQL的不足之处,SQL和PLSQL不是替代关系,是弥补关系

PLSQL 程序的完整组成结构如下:

[declare]
     变量声明;
   变量声明;
begin
     DML/TCL操作;
 DML/TCL操作;
[exception]
     例外处理;
 例外处理;
end;
/

注意:在PLSQL程序中,’;’号表示每条语句的结束,/表示整个PLSQL程序结束


写一个PLSQL程序,输出”hello world”字符串,语法:dbms_output.put_line(‘需要输出的字符串’);

begin
    --向SQLPLUS客户端工具输出字符串
    dbms_output.put_line('hello 你好');
end;
/

dbms_output是 oracle 中的一个输出对象,put_line 是上述对象的一个方法,用于输出一个字符串自动换行

设置显示PLSQL程序的执行结果,默认情况下,不显示PLSQL程序的执行结果,语法:set

serveroutput on/off;
set serveroutput on;

使用基本类型变量,常量和注释,求10+100的和

declare
    --定义变量
    mysum number(3) := 0;
    tip varchar2(10) := '结果是';
begin
    /*业务算法*/   
    mysum := 10 + 100;
    /*输出到控制器*/
    dbms_output.put_line(tip || mysum);
end;
/

输出7369号员工姓名和工资,格式如下:7369号员工的姓名是SMITH,薪水是800,语法:使用表名.字段%type

declare
    --定义二个变量,分别装姓名和工资
    pename emp.ename%type;
    psal   emp.sal%type;
begin  
    --SQL语句
    --select ename,sal from emp where empno = 7369;
    --PLSQL语句,将ename的值放入pename变量中,sal的值放入psal变量中    
    select ename,sal into pename,psal from emp where empno = 7369;
    --输出
    dbms_output.put_line('7369号员工的姓名是'||pename||',薪水是'||psal);    
end;
/

输出7788号员工姓名和工资,格式如下:7788号员工的姓名是SMITH,薪水是3000,语法:使用表名%rowtype

declare
    emp_record emp%rowtype;
begin
    select * into emp_record from emp where empno = 7788;
    dbms_output.put_line('7788号员工的姓名是'||emp_record.ename||',薪水是'||emp_record.sal);
end;
/


使用 if-else-end if 显示今天星期几,是”工作日”还是”休息日”

declare
    pday varchar2(10);
begin
    select to_char(sysdate,'day') into pday from dual;
    dbms_output.put_line('今天是'||pday);
    if pday in ('星期六','星期日') then
    dbms_output.put_line('休息日');
    else
    dbms_output.put_line('工作日');
    end if;
end;
/

从键盘接收值,使用 if-elsif-else-end if 显示”age<16”,”age<30”,”age<60”,”age<80”

declare
    age number(3) := &age;
begin
    if age < 16 then
       dbms_output.put_line('你未成人');
    elsif age < 30 then
       dbms_output.put_line('你青年人');
    elsif age < 60 then
       dbms_output.put_line('你奋斗人');
    elsif age < 80 then 
       dbms_output.put_line('你享受人');
    else
       dbms_output.put_line('未完再继');
    end if;
end;
/

使用 loop 循环显示 1-10

declare
    i number(2) := 1;
begin
    loop
        --当i>10时,退出循环
        exit when i>10;
        --输出i的值
        dbms_output.put_line(i);
        --变量自加
        i := i + 1;  
    end loop;
end;
/

使用 while 循环显示 1-10

declare
    i number(2) := 1;
begin
    while i<11 
    loop
        dbms_output.put_line(i);
        i := i + 1;
    end loop;
end;
/

使用 while 循环,向 emp 表中插入 999 条记录

declare
    i number(4) := 1;
begin 
    while( i < 1000 )
    loop
        insert into emp(empno,ename) values(i,'哈哈');
        i := i + 1;
    end loop;   
end;
/

使用 while 循环,从emp表中删除 999 条记录

declare
    i number(4) := 1;
begin 
    while i<1000
    loop
        delete from emp where empno = i;
        i := i + 1;
    end loop;
end;
/

使用 for 循环显示 20-30

declare
    i number(2) := 20;
begin
    for i in 20 .. 30
    loop
        dbms_output.put_line(i);
    end loop;
end;
/

什么是光标/游标/cursor

类似于JDBC 中的 ResultSet 对象的功能,从上向下依次获取每一记录的内容

使用无参光标 cursor,查询所有员工的姓名和工资【如果需要遍历多条记录时,使用光标 cursor,无记录找到使用 cemp%notfound】

declare
    --定义游标
    cursor cemp is select ename,sal from emp;
    --定义变量
    vename emp.ename%type;
    vsal   emp.sal%type;
begin
    --打开游标,这时游标位于第一条记录之前
    open cemp;
    --循环
    loop
       --向下移动游标一次
       fetch cemp into vename,vsal; 
       --退出循环,当游标下移一次后,找不到记录时,则退出循环
       exit when cemp%notfound;
       --输出结果
       dbms_output.put_line(vename||'--------'||vsal);
    end loop;
    --关闭游标
    close cemp;
end;
/

使用带参光标 cursor,查询10号部门的员工姓名和工资

declare
    cursor cemp(pdeptno emp.deptno%type) is select ename,sal from emp where deptno=pdeptno;
    pename emp.ename%type;
    psal emp.sal%type; 
begin 
    open cemp(&deptno);
    loop
        fetch cemp into pename,psal;     
        exit when cemp%notfound;
        dbms_output.put_line(pename||'的薪水是'||psal);
    end loop;
    close cemp;
end;
/

使用无参光标 cursor,真正给员工涨工资,ANALYST 涨1000,MANAGER 涨800,其它涨400,要求显示编号,姓名,职位,薪水

declare
    cursor cemp is select empno,ename,job,sal from emp;
    pempno emp.empno%type;
    pename emp.ename%type;
    pjob   emp.job%type;
    psal   emp.sal%type;
begin
    open cemp;
    loop
        fetch cemp into pempno,pename,pjob,psal;
        --循环退出条件一定要写
        exit when cemp%notfound;
        if pjob='ANALYST' then
            update emp set sal = sal + 1000 where empno = pempno;
        elsif pjob='MANAGER' then
            update emp set sal = sal + 800 where empno = pempno;
        else 
        update emp set sal = sal + 400 where empno = pempno;
        end if;
    end loop;
    commit;
    close cemp;
end;
/

使用 oracle 系统内置例外,演示除0例外【zero_divide】

declare
    myresult number;
begin
    myresult := 1/0;
    dbms_output.put_line(myresult);
exception
    when zero_divide then 
     dbms_output.put_line('除数不能为0');
     delete from emp;  
end;
/

使用 oracle 系统内置例外,查询100号部门的员工姓名,演示没有找到数据 【no_data_found】

declare
    pename varchar2(20);
begin
    select ename into pename from emp where deptno = 100;
    dbms_output.put_line(pename);
exception
    when NO_DATA_FOUND then 
     dbms_output.put_line('查无该部门员工');
     insert into emp(empno,ename) values(1111,'ERROR');
end;
/

使用用户自定义例外,使用光标 cursor,查询10/20/30/100 号部门的员工姓名,演示没有找到数据【nohave_emp_found】

declare
    cursor cemp(pdeptno number) is select ename from emp where deptno = pdeptno;
    pename emp.ename%type;
    nohave_emp_found exception;--自定义例外
begin
    --打开游标,这时游标位于第一条记录之前
    open cemp(10);
    --向下移动游标一次,指向第一条记录
    fetch cemp into pename;
    --判断
    if cemp%notfound then
        --抛异常
        raise nohave_emp_found;
    else
        --输出变量pename的值
        dbms_output.put_line(pename);
        loop
            --向下移动游标一次,指向第二条记录
            fetch cemp into pename;
            --如果找不到记录的话就退出
            exit when cemp%notfound;
            --输出变量Pename的值
            dbms_output.put_line(pename);
        end loop;
    end if;
    close cemp;
exception
    when nohave_emp_found then dbms_output.put_line('查无此部门门员工');
end;
/

hogen

2017-09-27
Home About Github Email