包实例
?
?
SQL> col object_name format a20;SQL> col object_type format a20;SQL> select object_name,object_type from user_objects 2 order by object_type asc;OBJECT_NAME OBJECT_TYPE-------------------- --------------------TAX FUNCTIONBIN$kHmZTqomReyTGf6x INDEXGpa9GQ==$2BIN$gtl8hEtDTwyJSJRw INDEXzqxFjQ==$0BIN$lBwhR7ckQVmBoQJC INDEXjyJTxg==$2AVGSALARYFORDEPT PROCEDUREOBJECT_NAME OBJECT_TYPE-------------------- --------------------RAISEDSALARYBYEMPNOP PROCEDUREROCADD_DEPT_PROC PROCEDUREEMPCOPY TABLETMP TABLEDEPT TABLESALGRADE TABLEBONUS TABLEEMP TABLEEMP_VW VIEW已选择14行。SQL> set linesize 200;SQL> /OBJECT_NAME OBJECT_TYPE-------------------- --------------------TAX FUNCTIONBIN$kHmZTqomReyTGf6x INDEXGpa9GQ==$2BIN$gtl8hEtDTwyJSJRw INDEXzqxFjQ==$0BIN$lBwhR7ckQVmBoQJC INDEXjyJTxg==$2AVGSALARYFORDEPT PROCEDUREOBJECT_NAME OBJECT_TYPE-------------------- --------------------RAISEDSALARYBYEMPNOP PROCEDUREROCADD_DEPT_PROC PROCEDUREEMPCOPY TABLETMP TABLEDEPT TABLESALGRADE TABLEBONUS TABLEEMP TABLEEMP_VW VIEW已选择14行。SQL> set pagesize 100;SQL> /OBJECT_NAME OBJECT_TYPE-------------------- --------------------TAX FUNCTIONBIN$kHmZTqomReyTGf6x INDEXGpa9GQ==$2BIN$gtl8hEtDTwyJSJRw INDEXzqxFjQ==$0BIN$lBwhR7ckQVmBoQJC INDEXjyJTxg==$2AVGSALARYFORDEPT PROCEDURERAISEDSALARYBYEMPNOP PROCEDUREROCADD_DEPT_PROC PROCEDUREEMPCOPY TABLETMP TABLEDEPT TABLESALGRADE TABLEBONUS TABLEEMP TABLEEMP_VW VIEW已选择14行。SQL> create or replace package employee_pkg is 2 3 -- Author : ADMINISTRATOR 4 -- Created : 2010-6-17 上午 09:19:27 5 -- Purpose : This is a demo. 6 7 end employee_pkg; 8 /程序包已创建。SQL> create or replace package body employee_pkg is 2 3 4 end employee_pkg; 5 /程序包体已创建。SQL> select object_name,object_type from user_objects 2 order by object_type asc;OBJECT_NAME OBJECT_TYPE-------------------- --------------------TAX FUNCTIONBIN$gtl8hEtDTwyJSJRw INDEXzqxFjQ==$0BIN$lBwhR7ckQVmBoQJC INDEXjyJTxg==$2BIN$kHmZTqomReyTGf6x INDEXGpa9GQ==$2EMPLOYEE_PKG PACKAGEEMPLOYEE_PKG PACKAGE BODYRAISEDSALARYBYEMPNOP PROCEDUREROCAVGSALARYFORDEPT PROCEDUREADD_DEPT_PROC PROCEDUREEMPCOPY TABLESALGRADE TABLEEMP TABLEBONUS TABLETMP TABLEDEPT TABLEEMP_VW VIEW已选择16行。SQL> create or replace package employee_pkg is 2 3 -- Author : ADMINISTRATOR 4 -- Created : 2010-6-17 上午 09:19:27 5 -- Purpose : This is a demo. 6 7 procedure print_ename(p_empno emp.empno%type); 8 9 --编写一个函数，用户校验输入的用户编号是否有效 10 function isValiad(p_empno emp.empno%type) return boolean; 11 12 end employee_pkg; 13 /程序包已创建。SQL> create or replace package body employee_pkg is 2 3 procedure print_ename(p_empno emp.empno%type) 4 is 5 begin 6 null; 7 end print_ename; 8 9 --编写一个函数，用户校验输入的用户编号是否有效 10 function isValiad(p_empno emp.empno%type) return boolean 11 is 12 begin 13 14 return false; 15 16 end isValiad; 17 18 end employee_pkg; 19 /程序包体已创建。SQL> select object_name,object_type from user_objects 2 order by object_type asc;OBJECT_NAME OBJECT_TYPE-------------------- --------------------TAX FUNCTIONBIN$gtl8hEtDTwyJSJRw INDEXzqxFjQ==$0BIN$lBwhR7ckQVmBoQJC INDEXjyJTxg==$2BIN$kHmZTqomReyTGf6x INDEXGpa9GQ==$2EMPLOYEE_PKG PACKAGEEMPLOYEE_PKG PACKAGE BODYRAISEDSALARYBYEMPNOP PROCEDUREROCAVGSALARYFORDEPT PROCEDUREADD_DEPT_PROC PROCEDUREEMPCOPY TABLESALGRADE TABLEEMP TABLEBONUS TABLETMP TABLEDEPT TABLEEMP_VW VIEW已选择16行。SQL> show user;USER 为 "SCOTT"SQL> create or replace package body employee_pkg is 2 3 procedure print_ename(p_empno emp.empno%type) 4 is 5 v_ename varchar2(30); 6 begin 7 8 --调用函数校验编号是否有效 9 if(isValiad(p_empno)) then 10 11 12 select ename into v_ename 13 from emp 14 where empno=p_empno; 15 dbms_output.put_line(p_empno|| ' 号雇员的姓名为： '||v_ename); 16 else 17 dbms_output.put_line(p_empno||' 的雇员不存在...'); 18 end if; 19 20 end print_ename; 21 22 --编写一个函数，用户校验输入的用户编号是否有效 23 function isValiad(p_empno emp.empno%type) return boolean 24 is 25 v_count number:=-1; 26 begin 27 28 select count(empno) into v_count 29 from emp 30 where empno=p_empno; 31 if(v_count>0) then 32 return true; 33 elsif 34 return false; 35 end if; 36 37 end isValiad; 38 39 end employee_pkg; 40 /警告: 创建的包体带有编译错误。SQL> show errors;PACKAGE BODY EMPLOYEE_PKG 出现错误:LINE/COL ERROR-------- -----------------------------34/16 PLS-00103: 出现符号 "FALSE"在需要下列之一时： . ( * @ % & = - + < / > at in is mod remainder not rem then <an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_ LIKE4_ LIKEC_ between || multiset member SUBMULTISET_ 符号 "then" 被替换为 "FALSE" 后继续。SQL> create or replace package body employee_pkg is 2 3 procedure print_ename(p_empno emp.empno%type) 4 is 5 v_ename varchar2(30); 6 begin 7 8 --调用函数校验编号是否有效 9 if(isValiad(p_empno)) then 10 11 12 select ename into v_ename 13 from emp 14 where empno=p_empno; 15 dbms_output.put_line(p_empno|| ' 号雇员的姓名为： '||v_ename); 16 else 17 dbms_output.put_line(p_empno||' 的雇员不存在...'); 18 end if; 19 20 end print_ename; 21 22 --编写一个函数，用户校验输入的用户编号是否有效 23 function isValiad(p_empno emp.empno%type) return boolean 24 is 25 v_count number:=-1; 26 begin 27 28 select count(empno) into v_count 29 from emp 30 where empno=p_empno; 31 if(v_count>0) then 32 return true; 33 else 34 return false; 35 end if; 36 37 end isValiad; 38 39 end employee_pkg; 40 /程序包体已创建。SQL> exec employee_pkg.print_ename(7782);PL/SQL 过程已成功完成。SQL> set serveroutput on;SQL> exec employee_pkg.print_ename(7782);7782 号雇员的姓名为： CLARKPL/SQL 过程已成功完成。SQL> exec employee_pkg.print_ename(7781);7781 的雇员不存在...PL/SQL 过程已成功完成。SQL> create or replace package employee_pkg is 2 3 -- Author : ADMINISTRATOR 4 -- Created : 2010-6-17 上午 09:19:27 5 -- Purpose : This is a demo. 6 7 procedure print_ename(p_empno emp.empno%type); 8 9 --编写一个函数，用户校验输入的用户编号是否有效 10 function isValiad(p_empno emp.empno%type) return boolean; 11 12 --编写一个过程，按照给定的部门编号，输出雇员的一些基本信息：雇员编号，姓名，工种，工资等等 13 procedure displayEmpInfo(p_deptno emp.deptno%type); 14 15 end employee_pkg; 16 /程序包已创建。SQL> select * from emp where deptno=10; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- 7782 CLARK MANAGER 7839 09-6月 -81 2450 10 7839 KING PRESIDENT 17-11月-81 5000 10 7934 MILLER CLERK 7782 23-1月 -82 1300 10SQL> create or replace package body employee_pkg is 2 3 procedure print_ename(p_empno emp.empno%type) 4 is 5 v_ename varchar2(30); 6 begin 7 8 --调用函数校验编号是否有效 9 if(isValiad(p_empno)) then 10 11 12 select ename into v_ename 13 from emp 14 where empno=p_empno; 15 dbms_output.put_line(p_empno|| ' 号雇员的姓名为： '||v_ename); 16 else 17 dbms_output.put_line(p_empno||' 的雇员不存在...'); 18 end if; 19 20 end print_ename; 21 22 --编写一个函数，用户校验输入的用户编号是否有效 23 function isValiad(p_empno emp.empno%type) return boolean 24 is 25 v_count number:=-1; 26 begin 27 28 select count(empno) into v_count 29 from emp 30 where empno=p_empno; 31 if(v_count>0) then 32 return true; 33 else 34 return false; 35 end if; 36 37 end isValiad; 38 39 --对包头中增加的内容作出具体实现 40 --由于按照部门编号查询，会产生多行的情况，所以需要使用 41 --PLSQL表，也叫做Index-by表处理结果。 42 procedure displayEmpInfo(p_deptno emp.deptno%type) 43 is 44 --定义PLSQL表，或者Index-by表 45 type emp_table_type is table of emp%rowtype 46 index by binary_integer; 47 --具体使用时，需要使用type声明变量 48 emp_table emp_table_type; 49 begin 50 select * bulk collect into emp_table /* bulk collect 表示批量处理*/ 51 from emp where deptno=p_deptno; 52 for i in 1.. emp_table.count loop 53 54 dbms_output.put_line('雇员编号：'||emp_table(i).empno||' 雇员姓名：'|| 55 emp_table(i).ename||' 雇员工种：'||emp_table(i).job||' 雇员工资：'|| 56 emp_table(i).sal); 57 58 end loop; 59 60 end displayEmpInfo; 61 62 63 64 end employee_pkg; 65 /程序包体已创建。SQL> exec employee_pkg.displayEmpInfo(10);雇员编号：7782 雇员姓名：CLARK 雇员工种：MANAGER 雇员工资：2450雇员编号：7839 雇员姓名：KING 雇员工种：PRESIDENT 雇员工资：5000雇员编号：7934 雇员姓名：MILLER 雇员工种：CLERK 雇员工资：1300PL/SQL 过程已成功完成。SQL> exec employee_pkg.displayEmpInfo(20);雇员编号：7369 雇员姓名：SMITH 雇员工种：CLERK 雇员工资：13176.9雇员编号：7566 雇员姓名：JONES 雇员工种：MANAGER 雇员工资：9000雇员编号：7788 雇员姓名：SCOTT 雇员工种：ANALYST 雇员工资：10890雇员编号：7876 雇员姓名：ADAMS 雇员工种：CLERK 雇员工资：9000雇员编号：7902 雇员姓名：FORD 雇员工种：ANALYST 雇员工资：9900PL/SQL 过程已成功完成。