1. 创建、编译并运行PL/SQL存储过程在SQL Developer中,创建、编译并运行PL/SQL存储过程 1、右击过程,创建过程 2、输入过程名“emp_list” 3、添加参数
Name Type Mode Default Value v_empno VARCHAR2 IN v_ename VARCHAR2 OUT
4、显示指定参数的过程的框架
1 2 3 4 5 6 7 8 CREATE OR REPLACEPROCEDURE EMP_LIST ( v_empno IN VARCHAR2 , v_ename OUT VARCHAR2 ) AS BEGIN NULL ; END EMP_LIST;
5、替换NULL为:
1 select ename into v_ename from emp where empno = v_empno;
单击工具栏的save按钮,编译PL/SQL子程序。
6、运行PL/SQL过程 单击编译图标,过程成功编译。右击emp_list,选择运行。 该操作将调用“运行PL/SQL”对话框。运行PL/SQL对话框允许选择要运行的目标过程或函数(对程序包有用),并显示所选目标的参数列表。PL/SQL块文本区域中包含的SQL Developer用来调用所选程序的生成代码。使用该区域填充要传送到程序单元的参数及处理复杂的返回类型。 将 “V_EMPNO:=NULL;”更改为“V_EMPNO:=7369;”,然后单击确定。 可以看到运行日志:
1 2 3 4 连接到数据库 scott。 V_ENAME = SMITH进程已退出。 从数据库 scott 断开连接。
2. 无参数的存储过程创建一个存储过程,使用游标实现,每输出dept表的一条记录(deptno、dname、loc)后,诉后输出该部门的员工记录(empno、ename、sal)。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 create or replace procedure query_dept_emp is type sp_emp_cursor is ref cursor ; test_cursor1 sp_emp_cursor; test_cursor2 sp_emp_cursor; v_deptno dept.Deptno%type ; v_dname dept.dname%type ; v_loc dept.loc%type ; v_empno emp.empno%type ; v_ename emp.ename%type ; v_sal emp.sal%type ; begin open test_cursor1 for select deptno,dname,loc from dept; loop fetch test_cursor1 into v_deptno, v_dname, v_loc; exit when test_cursor1%notfound; dbms_output.put_line('部门编号:' ||v_deptno||'部门名称:' ||v_dname||'部门位置:' || v_loc); dbms_output.put_line('-----------------------------------------------' ); open test_cursor2 for select empno,ename,sal from emp where deptno = v_deptno; loop fetch test_cursor2 into v_empno, v_ename, v_sal; exit when test_cursor2%notfound; dbms_output.put_line(v_empno||' ' || v_ename||' ' || v_sal); end loop ; end loop ; close test_cursor1; close test_cursor2; end ;exec query_dept_emp;
3. 带in参数的存储过程创建一个PL/SQL块,根据输入的部门编号,用游标实现逐条输出emp表中该部门每位员工的编号(empno)、姓名(ename)和工资(sal)信息。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 create or replace procedure query_by_deptno(v_deptno in emp.Deptno%type ) is type sp_emp_cursor is ref cursor ; test_cursor sp_emp_cursor; v_empno emp.empno%type ; v_ename emp.ename%type ; v_sal emp.sal%type ; begin open test_cursor for select empno,ename,sal from emp where deptno = v_deptno; dbms_output.put_line('员工编号 姓名 工资' ); loop fetch test_cursor into v_empno, v_ename, v_sal; exit when test_cursor%notfound; dbms_output.put_line(v_empno||' ' || v_ename||' ' || v_sal); end loop ; close test_cursor; end ;exec query_by_deptno(10 );
4. 带输入in、输出out参数的存储过程查询emp中给定职工号的姓名、工资和佣金。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 create or replace procedure query_emp (v_emp_no in emp.empno%type , v_emp_name out emp.ename%type , v_emp_sal out emp.sal%type , v_emp_comm out emp.comm%type ) is begin select ename,sal,comm into v_emp_name, v_emp_sal, v_emp_comm from emp where empno = v_emp_no; end query_emp;variable emp_name varchar2(15 ); variable emp_sal number; variable emp_comm number; execute query_emp(7369 ,:emp_name,:emp_sal,:emp_comm);print emp_name;
5. 使用隐式游标SQL%NOTFOUND的存储过程解雇给定职工号的职工。如果职工号7654的职工不存在则出错。为了避免出错可使用隐式游标SQL%NOTFOUND语句。
1 2 3 4 5 6 7 8 9 10 11 12 13 create or replace procedure fire_emp(v_emp_no in emp.empno%type )is begin delete from emp where empno = v_emp_no; if sql %notfound then dbms_output.put_line('雇员号为:' ||v_emp_no||'的员工不存在!' ); else dbms_output.put_line('已删除雇员号为:' || v_emp_no || '的员工。' ); end if ; end fire_emp;execute fire_emp(7654 );
6. 自定义函数用Function查询出EMP中给定职工号的工资。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 create or replace function get_sal (v_emp_no in emp.empno%type ) return number is v_emp_sal emp.sal%type :=0 ; begin select sal into v_emp_sal from emp where empno = v_emp_no; return (v_emp_sal); end get_sal;variable emp_sal number; execute :emp_sal:=get_sal('7900' );print emp_sal;
7. 小结这个实验肯定是和PL/SQL程序设计一伙的!感觉在专业中基本用不到。 本篇是Oracle实验记录的的最后一篇,书上还有一些内容,什么分区表的创建和使用、使用RMAN工具、闪回技术等等,需要时再去学习。