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;
