包实例
SQL> col object_name format a20;SQL> col object_type format a20;SQL> select object_name,object_type from user_objects2order 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 is2 3 -- Author: ADMINISTRATOR4 -- Created : 2010-6-17 上午 09:19:275 -- Purpose : This is a demo.6 7end employee_pkg;8/程序包已创建。SQL> create or replace package body employee_pkg is2 3 4end employee_pkg;5/程序包体已创建。SQL> select object_name,object_type from user_objects2order 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 is2 3 -- Author: ADMINISTRATOR4 -- Created : 2010-6-17 上午 09:19:275 -- 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) returnboolean; 11 12end employee_pkg; 13/程序包已创建。SQL> create or replace package body employee_pkg is2 3 procedure print_ename(p_empno emp.empno%type)4 is5 begin6 null;7 end print_ename;8 9 --编写一个函数，用户校验输入的用户编号是否有效 10 function isValiad(p_empno emp.empno%type) returnboolean 11 is12 begin 13 14 return false; 15 16 end isValiad; 17 18end employee_pkg; 19/程序包体已创建。SQL>select object_name,object_type from user_objects2 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 is2 3 procedure print_ename(p_empno emp.empno%type)4 is5 v_enamevarchar2(30);6 begin7 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) returnboolean 24 is25 v_count number:=-1; 26 begin 27 28 selectcount(empno) into v_count29 from emp30 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 39end 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 is2 3 procedure print_ename(p_empno emp.empno%type)4 is5 v_enamevarchar2(30);6 begin7 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) returnboolean 24 is25 v_count number:=-1; 26 begin 27 28 selectcount(empno) into v_count29 from emp30 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 39end 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 is2 3 -- Author: ADMINISTRATOR4 -- Created : 2010-6-17 上午 09:19:275 -- 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) returnboolean; 11 12 --编写一个过程，按照给定的部门编号，输出雇员的一些基本信息：雇员编号，姓名，工种，工资等等 13 procedure displayEmpInfo(p_deptno emp.deptno%type); 14 15end 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 is2 3 procedure print_ename(p_empno emp.empno%type)4 is5 v_enamevarchar2(30);6 begin7 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) returnboolean 24 is25 v_count number:=-1; 26 begin 27 28 selectcount(empno) into v_count29 from emp30 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 ofemp%rowtype 46 index by binary_integer; 47 --具体使用时，需要使用type声明变量 48 emp_tableemp_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 64end 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 过程已成功完成。
页:
[1]