ORACLE学习笔记

pl/sql自我学习

 

--打开输出
set serveroutput on
declare
--说明部分 声明变量
a NUMBER :=111;
begin
--程序体
dbms_output.put_line(a);
end;
/

 

 

***PL/SQL学习***
--使用基本变量类型
declare
--定义基本的变量类型
--基本数据类型
pnumber NUMBER(7,2);
--字符串变量
pname VARCHAR2(20);
--日期变量
pdate date;
begin
pnumber:=1;
DBMS_OUTPUT.PUT_LINE(pnumber);

pname:='TOM';
DBMS_OUTPUT.PUT_LINE(pname);

pdate:=sysdate;
DBMS_OUTPUT.PUT_LINE(pdate);

--计算明天的日期
DBMS_OUTPUT.PUT_LINE(pdate+1);
end;

/

 

 

/*
判断用户从键盘输入的数字
1.如何使用if语句
2.接收一个键盘输入（字符串）
*/


set SERVEROUTPUT on
--接收一个键盘输入
--num：地址值，含义是：在该地址上保存了输入的值


accept num PROMPT '输入一个数字';


DECLARE 
--定义变量保存用户从键盘输入的数字
pnum number :=#
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 if;
end;

/

 

 

--查询并打印员工的姓名和薪水
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 cemp%notfound;
--打印
dbms_output.put_line(pename||'的薪水是'||psal);
end loop;
--关闭光标
CLOSE cemp;
end;
/

 

 

--给员工涨工资 总裁1000，经理800，其他400
set SERVEROUTPUT ON
DECLARE
--定义光标代表给哪些员工涨工资
--alter table 'SCOTT'.'EMP' rename column 'JOB' to empjob
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;

/

 

 

 

--总结光标就是一个结果集


--查询某个部门中员工的姓名
set SERVEROUTPUT ON
--定义带光标的参数
DECLARE 
CURSOR cemp(dno number) is select ename from emp where deptno=dno; 
pename emp.ename%type;
begin
--打开光标
open cemp(20);


loop
--取出每个员工的姓名
FETCH cemp into pename;
exit when cemp%notfound;
dbms_output.put_line(pename);
end loop;


--关闭游标
close cemp;
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;
/
--系统例外：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;
/
--自定义例外 查询50号部门的员工姓名
set SERVEROUTPUT ON
DECLARE
--定义光标，代表50号部门的员工姓名
pename emp.ename%type;
CURSOR cemp is select ename from emp where deptno=50;
--自定义例外
no_emp_found EXCEPTION;
begin
--打开光标
open cemp;
--直接取一个员工的姓名
fetch cemp into pename;
if cemp%notfound then 
--抛出例外
raise no_emp_found;
end if;
--关闭光标
--oracle自动启动pmon进程自动关闭
close cemp;
EXCEPTION
when no_emp_found then DBMS_OUTPUT.PUT_LINE('没有找到员工');
when others then DBMS_OUTPUT.PUT_LINE('其他例外');
end;

/

 

 

 

**********存储过程*********




--创建一个带阐述的存储过程
--给指定的员工涨100块钱的工资，并且打印涨前后的工资
/*
如何调用
begin
raisesalary(7839);
commit;
end;
/






*/
create or replace procedure raisesalary(eno in number)
as
--定义一个变量保存涨前的薪水
psal emp.sal%type;


begin
--得到员工涨前的薪水
select sal into psal from emp where empno=eno;
--给员工涨100
update emp set sal=sal+100 where empno =eno;
--需不需要commit 
--注意：一般不在存储过程或者存储函数里面commit或者rollback
--打印
DBMS_OUTPUT.PUT_LINE('涨前的工资：'||psal||' 涨后的工资：'||(psal+100));
end;
/


 

 

 

 

 

 

************存储函数*******
--存储函数：查询某个员工的年收入
create or replace function queryempincome(eno in number)
return number
as
--定义变量保存员工的薪水和奖金
psal emp.sal%type;
pcomm emp.comm%type;
begin
--得到该员工的月薪和奖金
select sal,comm into psal,pcomm from emp where empno=eno;
--直接返回年收入
return psal*12+nvl(pcomm,0);
end;








--out参数：查询某个员工姓名，月薪和职位
create or replace procedure queryempinform(eno in number,
pename out VARCHAR2,
psal out number,
pjob out varchar2) 
as
begin
--得到该员工的姓名，月薪和职位
select ename,sal,empjob into pename ,psal,pjob from emp where empno=eno;


end;

 

 

 

 

/

 

 

 

 

程序包里的存储过程

CREATE OR REPLACE 
PACKAGE MYPACKAGE AS 


  /* TODO enter package declarations (types, exceptions, methods etc) here */ 
    TYPE empcursor is ref cursor;
    procedure queryEmpList(dno in number,empList out empcursor);

END MYPACKAGE;

 

 

CREATE OR REPLACE
PACKAGE BODY MYPACKAGE AS


  procedure queryEmpList(dno in number,empList out empcursor) AS
  BEGIN
    -- TODO: procedure MYPACKAGE.queryEmpList所需的实施
    --打开光标
    open empList for select *from emp where deptno=dno;
  END queryEmpList;

 

END MYPACKAGE;