PL/SQL

--匿名块例子，定义变量，并赋值为当前日期

--打印变量值

SQL> set serveroutput on; //创建一个命令窗口
SQL> declare //声明一个变量
2 l_now date;
3 begin
4 l_now :=sysdate; //变量赋值为当前日期
5 dbms_output.put_line(l_now); //打印变量
6 end;
7 /

--查询王刚的empno，并打印出来

SQL> declare
2 v_empno varchar2(10);
3 begin
4 select e.empno into v_empno from emp1 e where e.ename='王刚';
5 dbms_output.put_line(v_empno);
6 end;
7 /

 

--创建一个存储过程根据输入参数新增部门

create or replace procedure p_insert_dept
(in_dname in dept.dname%TYPE)
is
begin
INSERT INTO dept(deptno,dname)
VALUES(deptno_seq.nextval,in_dname);
COMMIT;
end p_insert_dept;

--调试

方法一：又击P_INSERT_DEPT->>添加调试信息->>测试
方法二：通过匿名块

SQL> set serveroutput on;
SQL> begin
2 p_insert_dept('综合管理部');
3 end;
4 /

--简单循环LOOP

--简单循环测试

declare
v_num number;
begin
v_num :=1;
loop
exit when v_num>10;
v_num := v_num+1;
end loop;
dbms_output.put_line('v_num:'||v_num);
end;

--函数

CREATE OR REPLACE FUNCTION f_get_name(i_empno IN VARCHAR2)
RETURN VARCHAR2 IS 
v_emp_name emp.ename%TYPE;
BEGIN
SELECT t.ename INTO v_emp_name
FROM emp t WHERE t.empno=i_empno;
RETURN (v_emp_name);
EXCEPTION 
WHEN no_data_found THEN
dbms_output.put_line('你需要的数据不存在');
WHEN OTHERS THEN 
dbms_output.put_line('发生其他错误');
END f_get_name;

 

SELECT f_get_name('7698') FROM dual

 

SELECT e.ename,f_get_name(NVL(e.mgr,'7839')) FROM emp e

--创建序列

CREATE SEQUENCE deptno_seq START WITH 1 INCREMENT BY 1 MAXVALUE 99 CACHE 10;

--查询deptno_seq的下一个值

select deptno_seq.nextval from dual;

--查询deptno_seq的当前值

select deptno_seq.currval from dual

--在插入语句中使用序列3

insert into emp1(num,empno)values(99,deptno_seq.nextval)

--创建一个存储过程
--该存储过程的含义：
--将人员表中经理职位
--并且年薪大于7000的人员信息
--插入到MANAGER表中，输出参数时插入的条数

create or replace procedure p_manager_update (o_ename_num out number) is
begin 
select to_number(count(1)) into o_ename_num
from emp e
where nvl(e.sal,0)*12+nvl(e.comm,0)>70000
and e.job='MANAGER';
insert into MANAGER(INCODE,ENAME,CREATE_DATE)
select deptno_seq.nextval,e.ename,sysdate
from emp e
where nvl(e.sal,0)*12+nvl(e.comm,0)>70000
and e.job='MANAGER'；
commit;
end p_manager_update;

--创建一个存储过程
--该存储过程的含义：
--将人员表中经理职位
--并且年薪大于7000的人员信息,加两个参数，设为动态的
--插入到MANAGER表中，输出参数时插入的条数

CREATE PROCEDURE p_manager_update
(o_num OUT NUMBER,i_job VARCHAR2,i_salcomm NUMBER)
IS
BEGIN
INSERT INTO manager(incode,ename,create_date)
SELECT deptno_seq.nextval,e.ename,SYSDATE
FROM emp e
WHERE e.job = i_job
AND (NVL(e.sal,0)*12+NVL(comm,0))>i_salcomm;

SELECTcount(1) INTO o_num
FROM emp e
WHERE e.job = i_job
AND (NVL(e.sal,0)*12+NVL(comm,0))>i_salcomm;
COMMIT;
END p_manager_update; 

--如何使用游标

--向MANAGER表中插入职位为经理年薪大于7万的数据
--如果MANAGER表里已经讯在此员工，则不插入
--如果不存在，再插入，输出参数是插入的记录数

create or replace procedure p_manager_update2(o_ename_num out number) is

begin

declare

--向MANAGER插入一条记录就+1，最终值就是输出参数

v_num number(3);

v_empno varchar2(20);

v_ename varchar2(20);

v_emp_num number(3);

begin

v_num:=0;

declare

cursor zb_cursor is

select e.empno,e.ename

from emp e

where nvl(e.sal,0)*12+nvl(e.comm,0) > 70000

and e.job='MANAGER';

zb_record zb_cursor%rowtype;

begin

open zb_cursor;

fetch zb_cursor into zb_record;

while zb_cursor%found loop

--从record中取出员工编号及员工姓名

v_empno:=zb_record.empno;

v_ename:=zb_record.ename;

 

select count(*) into v_emp_num

from manager m where m.empno=v_empno;

 

if v_emp_num=0 then

insert into MANAGER(INCODE,ENAME,CREATE_DATE,empno)

values(deptno_seq.nextval,v_ename,sysdate,v_empno);

v_num := v_num+1;

end if;

 

fetch zb_cursor into zb_record;

end loop;

close zb_cursor;

end;

o_ename_num :=v_num;

commit;

end;

end p_manager_update2;

 

--用存储过程实现decode（job）功能

--如果JOB=CLERK，修改为办事员
--如果JOB=MANAGER，修改为经理
--如果JOB=SALESMAN，修改为销售员
--否则修改为其它

create or replace procedure p_update_job(o_num out number) IS
begin
DECLARE 
vjob emp1.job%TYPE;
CURSOR c_job IS
SELECT job,empno FROM emp1;
BEGIN
o_num :=0;
FOR v_job IN c_job
LOOP
IF v_job.job='CLERK' THEN
vjob:='办事员';
ELSIF v_job.job='MANAGER' THEN
vjob:='经理';
ELSIF v_job.job='SALESMAN' THEN
vjob:='销售员';
ELSE
vjob:='其它';
END IF;
UPDATE emp1
SET job=vjob
WHERE empno=v_job.empno;
o_num :=o_num+1;
END LOOP;
COMMIT;
END;

--用函数实现根据员工编号获取所在部门名称
--输入参数是员工编号，返回值是部门名称

create or replace function f_get_deptname(i_empno in varchar2) 
return varchar2 is
v_deptname dept.dname%TYPE;
BEGIN
IF i_empno is not null then
select t.dname into v_deptname
from emp e,dept t
where t.deptno=e.deptno
and empno=i_empno;
ELSE 
v_deptname :='无部门';
END IF;
return(v_deptname);
exception
when no_data_found then
dbms_output.put_line('你需要的数据不存在!');
when others then
dbms_output.put_line('发生其它错误!');

--使用自定义函数f_get_deptname

select e.ename, f_get_deptname(e.deptno),
f_get_name(nvl(e.mgr,'7839')),
f_get_deptname(nvl(e.mgr,'7839')),
from emp e

 

select e.ename, f_get_deptname(e.deptno),
f_get_name(e.mgr),
f_get_deptname(e.mgr),
from emp e

--效果相同

select e.ename,d.dname
from emp e ,dept d where e.deptno=d.deptno

--动态SQL

CREATE OR REPLACE PROCEDURE P_INSERT_TEMP(
I_EMPNO IN EMP.EMPNO%TYPE,I_SEL IN VARCHAR2) authid CURRENT_USER
is
V_SQL VARCHAR2(128);
BEGIN
V_SQL :='CREATE TABLE TEMP AS SELECT * FROM EMP WHERE 1=2';
EXECUTE IMMEDIATE V_SQL;
V_SQL :='INSERT INTO TEMP('||I_SEL||') SELECT'
||I_SEL||'FROM EMP WHERE EMPNO='||I_EMPNO;
EXECUTE IMMEDIATE V_SQL;
COMMIT;
END P_INSERT_TEMP;

--可以给demo用户授权可以创建表
--或者在存储过程后添加authid CURRENT_USER
grant create table to demo;
--实现存储过程
--根据指定的查询条件，查询人员姓名
--人员姓名中间以，（逗号）分隔
--输入参数为查询条件，输出参数为人员列表
--新建表存储临时数据

CREATE OR REPLACE PROCEDURE P_GET_NAMES(
in_sql IN VARCHAR2,out_names OUT VARCHAR2)
authid CURRENT_USER
is 
V_SQL VARCGAR2(128);
BEGIN
V_SQL := 'INSERT INTO TEMP_FLAG(incode,V_1,N_1)'
||'SELECT DEPTNO_SEQ.nextval,ename,1234 FROM emp'
||'WHERE||in_sql';
EXECUTE IMMEDIATE V_SQL;
DECLARE
CURSOR c_emp IS
SELECT V_1 FROM TEMP_FLAG WHERE N_1=1234;
BEGIN
FOR v_emp IN c_emp
LOOP
out_names := out_names||v_emp.V_1||',';
END LOOP;
out_names := RTRIM(out_names,',');
DELETE FROM TEMP_FLAG WHERE N_1=1234;
COMMIT;
END P_GET_NAMES;