的

--使用while循环打印数字的1-10

set serveroutput on

declare

--定义循环变量

pnum number := 1;

begin

 while pnumber <=10 loop

  dbms_out.put_line(pnum);

  pnum := pnum +1;

end loop;

end;

/

--使用loop循环打印1-10

set serveroutput on

declare

--定义循环变量

 pnum number :=1;

begin

  loop

  --退出条件：循环变量大于10

   exit when pnum >10;

   dbms_output.put_line(pnum);

  --循环变量+1

  pnum := pnum +1;

end loop;

end;

/

--使用for循环打印1-10

set servieroutput on

declare 

--定义循环变量

begin

 for pnum in 1..10 loop

     dbms_output.line(pnum);

     pnum :=pnum +1;

end loop;

end;

/

IF 语句

判断用户从键盘上输入的输入数字

如何使用if语句

接受一个键盘输入（字符串）

set serveroutput

--接受一个键盘输入

--num:地址值，含义是：在该地址上保存了输入的值

accept num prompt '请输入一个数字'；

declare

--定义变量保存变量用户从键盘输入的数字

pnum number := &num;

begin

--执行if语句进行条件判断

if pnum = 0 then dbms_output.put_line('您水乳的数字是0')；

  elsif pnum = 1 then dbms_output.put_line('您输入的是1')；

  elsif pnum = 2 then dbms_output.put_line('您输入的是2')；

  else dbms_output.put_line('其他数字')

end;

/

引用型变量

举例：

my_name emp.ename%type

引用型变量

set serveroutput on

declare

    --定义引用类型变量：查询并打印7839的姓名和薪水

--pename varchar2(20)

--psal       number

pename emp.ename%type;

psal emp.sal%type

begin

 select ename,sal into pename,psal from emp where empno = 7839'

打印姓名和薪水

dbms_output.put_line(pename||'的薪水是'||psal);

end;

记录型变量

举例：

emp_rec emp%rowtype

记录型变量分量的引用

emp_rec.ename := 'adams';

--使用记录型变量，查询并打印7839的姓名和薪水

set serveroutput on

declare

--定义记录型变量：注意代表一样

emp_rec emp%rowtype

begin

--得到7839

select *into emp_rec from emp where empno = 7839;

--打印姓名和薪水

dbms_out.put_line(emp_rec.ename||'的薪水是'||emp_rec.sal);

end;

/

declare

        说明部分（变量说明，光标申明，）

begin

        语句序列（DML）

exception

        例外处理语句

end;

/

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

定义基本变量

类型：char,varchar2,date,number,boolean,long

举例：var1   char(15)

    married boolean  :=true

   psal  number(7,2)

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

--使用基本变量类型

declare

 --定义基本变量类型

--基本数据类型

pnumber number(7,2)

--字符串变量

pname varchar2(2);

--日期变量

pdate date

bebin

     pnumber :=1

     dbms_output._line(pname);

     pname := 'Tom'；

     dbms_output.put_line(pname);

     pdate := sysdate;

     dbms_output.putline(pdate);

     --计算明天的日期

‘ dbms_output.put_line(pdate+1);

基本变量类型

操作数据库效率最高

PLSQL程序设计

declare   

 begin

end;

/

/*

SQL语句

1.有哪些部门

  select deptno from dept; -->光标 -->循环 -->退出条件:notfound

2.部门中员工的薪水

  select sal from emp where deptno = ? -->带一个参数的光标-->循环-->退出条件:notfound

变量: 1.初始值 2.如何得到每个段的员工人数

count1 number;

count2 number;

count3 number;

每个部门的工资总额:

saltotal number;

1.SELECT sum(sal) into saltotal from emp where deptno = ???

2.累计  

*/

set serveroutput ON

DECLARE

 --部门的光标

 CURSOR  cdept is SELECT deptno from dept;

 pdeptno  dept.deptno%type;

 --部门中员工的薪水

 CURSOR  cemp(dno NUMBER) is SELECT sal from emp where deptno = dno;

 psal    emp.sal%type;

 --员工人数

 count1 number;

 count2 number;

 count3 number;

 --每个部门的工资总额:

 saltotal number;

BEGIN

 --打开部门光标

 open cdept;

 LOOP

  fetch cdept into pdeptno;

  EXIT when cdept%notfound;

  --初始化工作

  count1 :=0; count2:= 0; count3 := 0;

  --得到部门的工资总额

  --1.SELECT sum(sal) into saltotal from emp where deptno = ???

  --2.saltotal:=0;

  SELECT sum(sal) into saltotal from emp where deptno = pdeptno;

  --取部门中员工的薪水

  open cemp(pdeptno);

   LOOP

    --取一个员工的薪水

fetch cemp into psal;

EXIT WHEN cemp%notfound;

--saltotal:=saltotal+psal;

--判断薪水范围

if psal < 3000 then count1:=count1+1;

  ELSIF psal >= 3000 and psal < 6000 THEN count2:=count2+1;

  ELSE count3:=count3+1;

end if;

   end loop; 

  --关闭员工光标   

  close cemp;

  --保存当前部门的结果

  insert into msg VALUES( pdeptno,count1,count2,count3,NVL(saltotal,0) );

 end LOOP;

 --关闭部门光标

 close cdept；

 dbms_output.put_line('统计完成');

end;

/

/*

SQL语句

select empno,sal from emp order by sal;

-->光标-->循环-->退出条件:1.工资总额大于5W,2%notfound

变量：1.初始值 2.如何得到

涨工资的人数：

countEmp number := 0;

涨后的工资总量：

salTotal number；

1.select sum(sal) into salTotal from emp;

2.涨后的工资总额=涨前的工资总额+sal*0.1

*/

set serveroutput ON

DECLARE

 --定义光标

 CURSOR cemp is select empno,sal from emp order by sal;

 pempno emp.empno%TYPE;

 psal   emp.sal%TYPE;

 --涨工资的人数

 countEmp NUMBER :=0;

 --涨后的工资总额：

 salTotal number;

BEGIN

 --得到工资总额的

 select sum(sal) into salTotal from emp;

 --打开光标

 OPEN cemp;

 LOOP

  --1.工资总额大于5W

  exit when salTotal > 50000;

  --取一个员工涨工资

  FETCH cemp into pempno,psal;

  --2.notfound

  exit when cemp%notfound;

  --3.如果涨工资后总额超过5W

  exit when salTotal + psal*0.1 > 50000;

  --涨工资

  update emp set sal=sal*1.1 where empno = pempno;

  --人数+1

  countEmp := countEmp + 1;

  --2.涨后的工资总额=涨前的工资总额+sal*0.1

  salTotal := salTotal + psal*0.1;

 end loop;

 --关闭光标

 CLOSE cemp;

end;

/

/*

SQL语句

select to_char(hiredate,'yyyy') from emp;

-->光标-->循环-->退出条件:notfound

变量:1.初始值，2.如何得到每年入职的员工人数

count80 number := 0;

count81 number := 0;

count82 number := 0;

count87 number := 0;

*/

set serveroutput on

declare

 --定义光标

 cursor c_emp is select to_char(hiredate,'yyyy') from emp;

 p_hiredate varchar2(4);

 --每年入职的员工人数

 count80 number := 0;

 count81 number := 0;

 count82 number := 0;

 count87 number := 0;

begin

 --打开光标

 open c_emp;

 loop 

  --取出一个员工的入职年份

  fetch c_emp into p_hiredate;

  exit when c_emp%notfound;

  --判断入职年份

  if p_hiredate = '1980' then count80 :=count80+1;

   elsif p_hiredate = '1981' then count81 :=count81+1;

   elsif p_hiredate = '1982' then count82 :=count82+1;

   else count87 := count87+1;

  end if

 end loop;

 --输出结果

 dbms_output.put_line('Total'||(count80+count81+count82+count87));

 dbms_output.put_line('1980'||count80);

 dbms_output.put_line('1981'||count81);

 dbms_output.put_line('1982'||count82);

 dbms_output.put_line('1987'||count87);

 --关闭光标

 close c_emp;

end;

/

PL/SQL的程序结构

declare 

    说明部分（变量说明、光标申明、例外说明）

begin

    语句序列（DML语句）

exception

    例外处理语句

end;

/

什么是PL/SQL程序

1. PL/SQL(Procedure Language/SQL)

2. PLSQL是Oracle对sql语言的过程化扩展，指在SQL命令语言中增加了过程处理语句（如分支，循环等），使SQL语言具有过程处理能力。
   

打印Hello World

注意：如果要在屏幕上输出信息，需要将serveroutput开关打开

set serveroutput on

declare

--说明部分

begin

--程序

dbms_output.put_line('Hello World');

end;

/

drop table sc;

drop table course;

drop table student;

drop table teacher;

drop table dep;

drop table msg1;

create table dep(

dno number(2),

dname  varchar2(30),

director number(4),

tel varchar(8)

);

create table teacher(

tno number(4),

tname  varchar2(10),

title  varchar2(20),

hiredate date,

sal number(7,2),

bonus number(7,2),

mgr number(4),

deptno number(2)

);

create table student(

sno number(6),

sname  varchar2(8),

sex  varchar2(2),

birth  date,

passwd varchar2(8),

dno number(2)

);

create table course(

cno varchar2(8),

cname  varchar2(20),

credit  number(1),

ctime  number(2),

quota  number(3)

);

create table sc(

sno number(6),

cno varchar2(8),

grade number(3)

);

-- 创建用于统计数据

create table msg1

(coursename varchar2(20),

dname varchar2(20),

count1 number,

count2 number,

count3 number,

avggrade number);

alter table dep add(constraint pk_deptno primary key(dno));

alter table dep add(constraint dno_number_check check(dno>=10 and dno<=50));

alter table dep modify(tel default 62795032);

alter table student add(constraint pk_sno primary key(sno));

alter table student add(constraint sex_check check(sex='男' or sex='女'));

alter table student modify(birth default sysdate);

alter table course add(constraint pk_cno primary key(cno));

alter table sc add(constraint pk_key primary key(cno,sno));

alter table teacher add(constraint pk_tno primary key(tno));

alter table sc add(foreign key(cno) references course(cno));

alter table sc add(foreign key(sno) references student(sno));

alter table student add(foreign key(dno) references dep(dno));

alter table teacher add(foreign key(deptno) references dep(dno));

insert into dep values(10,'计算机学院',9469,'62785234');

insert into dep values(20,'自动化学院',9581,'62775234');

insert into dep values(30,'无线电学院',9791,'62778932');

insert into dep values(40,'信息管理学院',9611,'62785520');

insert into dep values(50,'微纳电子学院',2031,'62797686');

insert into teacher values(9468,'CHARLES','PROFESSOR','17-12月-17',8000,1000,null,10);

insert into teacher values(9469,'SMITH','PROFESSOR','17-12月-17',5000,1000,9468,10);

insert into teacher values(9470,'ALLEN','ASSOCIATE PROFESSOR','20-2月-16',4200,500,9469,10);

insert into teacher values(9471,'WARD','LECTURER','22-2月-17',3000,300,9469,10);

insert into teacher values(9581,'JONES','PROFESSOR','2-4月-2016',6500,1000,9468,20);

insert into teacher values(9582,'MARTIN','ASSOCIATE PROFESSOR','28-9月-2018',4000,800,9581,20);

insert into teacher values(9583,'BLAKE','LECTURER','1-5月-2019',3000,300,9581,20);

insert into teacher values(9791,'CLAKE','PROFESSOR','9-1月-2016',5500,null,9468,30);

insert into teacher values(9792,'SCOTT','ASSOCIATE PROFESSOR','09-12月-17',4500,null,9791,30);

insert into teacher values(9793,'BAGGY','LECTURER','17-11月-2017',3000,null,9791,30);

insert into teacher values(9611,'TURNER','PROFESSOR','8-9月-2018',6000,1000,9468,40);

insert into teacher values(9612,'ADAMS','ASSOCIATE PROFESSOR','12-1月-17',4800,800,9611,40);

insert into teacher values(9613,'JAMES','LECTURER','3-12月-19',2800,200,9611,40);

insert into teacher values(2031,'FORD','PROFESSOR','3-12月-18',5500,null,9468,50);

insert into teacher values(2032,'MILLER','ASSOCIATE PROFESSOR','23-1月-2018',4300,null,2031,50);

insert into teacher values(2033,'MIGEAL','LECTURER','23-1月-2019',2900,null,2031,50);

insert into teacher values(2034,'PEGGY','LECTURER','23-1月-2020',2800,null,2031,50);

-- 一定要提交，否则sqldeveloper工具查看不到数据，sqlplus可以看到数据

commit;

sqlplus如何导入sql数据的

@D:\mukewang\student.sql

右击scott，刷新，可以看到新建的表

表数据 全选 点叉 点执行，可以删除数据

 create table msg(

 deptno number,

 count1 number,

 count2 number,

 count3 number,

 saltotal number);

set serveroutput on

declare

    --获取所有部门

    cursor c_dept is select deptno from dept;

    --各部门编号

    p_dno dept.deptno%type;

    --各部门总金额

    p_totalsal number;

    --各工资分段人数：

    num1 number;

    num2 number;

    num3 number;

    --定义一个游标存放该部门下所有员工(带参数)

    cursor c_emp(dno number) is select sal from emp where deptno = dno;

    --员工的薪水

    p_sal number;

begin

    --打开部门游标

    open c_dept;

    loop --部门循环

        fetch c_dept into p_dno;

        exit when c_dept%notfound;

        --初始化变量：

        p_totalsal:=0;

        num1:=0;

        num2:=0;

        num3:=0;

        --获取本部门下所有员工,打开员工游标

        open c_emp(p_dno);

            loop --员工循环

                fetch c_emp into p_sal;

                exit when c_emp%notfound;

                if p_sal <3000 then num1:=num1+1;

                elsif p_sal >=3000 and p_sal<=6000 then num2:=num2+1;

                elsif p_sal>6000 then num3:=num3+1;

                end if;

                --获取总金额

                p_totalsal:=p_totalsal+p_sal;

            end loop;

          close c_emp;

          --保存统计结果到msg

          insert into msg values(p_dno,num1,num2,num3,p_totalsal);

      end loop;

      close c_dept;

      commit;

      dbms_output.put_line('统计完成');

end;

/

set serveroutput on

declare

    --定义光标

    cursor emp_cursor is select empno,sal from emp order by sal;

    --定义变量

    v_empno emp.empno%type;

    v_sal emp.sal%type;

    --涨工资的人数

    v_countemp number := 0;

    --涨工资的总额

    v_totalsal number;

begin

    rollback;

    --为涨工资总额赋值初始值

    select sum(sal) into v_totalsal from emp;

    --打开光标

    open emp_cursor;

    loop

        fetch emp_cursor into v_empno,v_sal;

        exit when emp_cursor%notfound;

        if(v_totalsal + v_sal * 0.1) < 50000 then

            -- 涨工资

            update emp set sal = sal*1.1 where empno = v_empno;

            --计算人数

            v_countemp := v_countemp + 1;

            --计算涨后工资总额

            v_totalsal := v_totalsal + v_sal * 0.1;

        else exit;

        end if;

    end loop;

    --关闭光标

    close emp_cursor;

    commit;

    --输出数据

    dbms_output.put_line('涨工资的人数有:'||v_countemp||'人');

    dbms_output.put_line('涨后工资总额:'||v_totalsal||'元');

end;

/

/**

统计每年入职的员工

*/

set serveroutput on

declare

    -- 定义光标

    cursor cemp is select to_char(hiredate,'yyyy') from emp;

    phiredate varchar2(4);

    --每年入职的员工数

    count80 number := 0;

    count81 number := 0;

    count82 number := 0;

    count87 number := 0;

begin

    -- 打开光标

    open cemp;

    loop

    --取出一个员工的入职年份

    fetch cemp into phiredate;

    exit when cemp%notfound;

        --判断入职年份

        if phiredate = '1980' then count80:=count80+1;

            elsif phiredate = '1981' then count81:=count81+1;

            elsif phiredate = '1982' then count82:=count82+1;

            else count87:=count87+1;

        end if;

    end loop;

    -- 关闭光标

    close cemp;

    dbms_output.put_line('Total:'||(count80+count81+count82+count87));

    dbms_output.put_line('1980:'||count80);

    dbms_output.put_line('1981:'||count81);

    dbms_output.put_line('1982:'||count82);

    dbms_output.put_line('1987:'||count87);

end;

/

declare

    --定义光标，代表50号部门的员工姓名

    cursor cemp is select ename from emp where deptno=50;

    pename emp.ename%type;

    --自定义例外

    no_emp_found exception;

begin

    --打开光标

    open cemp;

    --直接取一个员工的姓名

    fetch cemp into pename;

    if cemp%notfound then

        --抛出例外，用raise抛出自定义例外

        raise no_emp_found;

    end if;

    --关闭光标--if给拦截了，执行不到，

    --但oracle自动启用pmon(process monitor),自动清理系统遗留的垃圾和资源

    close cemp;

exception

    when no_emp_found then dbms_output.put_line('没有找到员工');

    when others then dbms_output.put_line('其他例外');

end;

/

-- 系统例外：value_error算术或转换错误

-- 字符强制赋值给了数字

set serveroutput on

declare

    --定义一个number类型的变量

    pnum number;

begin

    pnum := 'abc';

exception

    when value_error then dbms_output.put_line('算术或转换错误');

    when others then dbms_output.put_line('其他例外');

end;

/

--系统例外：被0除zero_divide

set serveroutput on

declare

    --定义一个基本变量

    pnum number;

begin

    pnum := 1/0;

exception

    when zero_divide then dbms_output.put_line('1:0不能做除数');

                        dbms_output.put_line('2:0不能做除数');

                        -- 为了演示上面的处理语句可以写多行，这里多写了一行

    when others then dbms_output.put_line('其他例外');

end;

/

-- 系统例外：too_many_rows

set serveroutput on

declare

    --定义变量

    pename emp.ename%type;

begin

    --查询所有10号部门的员工姓名

    select ename into pename from emp where deptno=10;

exception

    when too_many_rows then dbms_output.put_line('select into 匹配了多行--非法操作');

    when others then dbms_output.put_line('其他例外');

end;

/

-- 系统例外：no_data_found

set serveroutput on

declare

    pename emp.ename%type;    

begin

    --查询员工号1234员工姓名

    select ename into pename from emp where empno=1234;

    -- 例外程序捕获，如果抛给数据库，可能导致数据库出错

exception

    when no_data_found then dbms_output.put_line('没有找到数据');

    when others then dbms_output.put_line('其他例外');

end;

/

-- 查询某个部门中的员工姓名

set serveroutput on

declare

    --定义带参数的光标

    cursor cemp(dno number) is select ename from emp where deptno=dno;

    pename emp.ename%type;

begin

    --打开光标

    open cemp(10);

    loop

    -- 取出每个员工的姓名

    fetch cemp into pename;

    exit when cemp%notfound;

    dbms_output.put_line(pename);

    end loop;

    --关闭光标

    close cemp;

end;

/

/**

1.光标的属性

 %found %notfound

 %isopen 判断光标是否打开

 %rowcount 影响行数---而非总行数

 2.光标数的限制--光标打开了一定要关闭-

  光标打开超出配置极限会报错；

  默认情况下，oracel一个会话中最多可以打开300个光标

*/

set serveroutput on

declare

    --定义光标

    cursor cemp is select empno,empjob from emp;

    pempno emp.empno%type;

    pjob emp.empjob%type;

begin

    --打开光标

    open cemp;

    loop

        --取出一条记录

        fetch cemp into pempno,pjob;

        exit when cemp%notfound;

        --打印rowcount的值

        dbms_output.put_line('rowcount:'||cemp%rowcount);

    end loop;

    --关闭光标

    close cemp;

end;

/

连接超级管理，查看cursor相关的参数

open_cursors 一个会话中光标的最多数量，300是不够使用的；

alter system set open_cursors=400 scope=both;

scope的取值 

memory,----只更改当前示例，不更改参数文件

spfile--只更改参数文件，不更改当前示例，需要重启数据库

both---包含以上2种更改；

SQL> conn sys/123456 as sysdba

已连接。

SQL> show user

USER 为 "SYS"

SQL> show parameter %cursor%

NAME                                 TYPE        VALUE

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

cursor_sharing                       string      EXACT

cursor_space_for_time                boolean     FALSE

open_cursors                         integer     300

session_cached_cursors               integer     50

-- 给员工涨工资，总裁1000，经理800，其他400

set serveroutput on

declare

    --定义光标代表给哪些员工涨工资

    --alter table "SCOTT"."EMP" rename column "JOB" to empjob

    --表中job提示蓝色，触碰到了关键字

    cursor cemp is select empno,empjob from emp;

    pempno emp.empno%type;

    pjob emp.empjob%type;

begin

    rollback;

    --打开光标

    open cemp;

    loop

        fetch cemp into pempno,pjob;

        exit when cemp%notfound;

        -- 判断员工的职位

        if pjob = 'PRESIDENT' 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;

    --关闭光标

    close cemp;

    -- 对于oracle，默认的事务隔离级别是read committed

    -- 事务的acid

    commit;

    dbms_output.put_line('涨工资成功');

end;

/

--查询并打印员工姓名和薪水

/**

1.光标的属性--4个属性，本节仅介绍2个

 %found  %not found

*/

set serveroutput on

declare

    --定义一个光标

    cursor cemp is select ename,sal from emp;

    -- 为光标定义对应的变量

    pename emp.ename%type;

    psal emp.sal%type;

begin

    --打开光标

    open cemp;

    loop

    -- 取一条记录

    fetch cemp into pename,psal;

    -- exit when 没有取到记录

    exit when cemp%notfound;

    -- 打印

    dbms_output.put_line(pename||'的薪水'||psal);

    end loop;

    -- 关闭光标

    close cemp;

end;

/