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 FUNCTION
BIN$kHmZTqomReyTGf6x INDEX
Gpa9GQ==$2
BIN$gtl8hEtDTwyJSJRw INDEX
zqxFjQ==$0
BIN$lBwhR7ckQVmBoQJC INDEX
jyJTxg==$2
AVGSALARYFORDEPT PROCEDURE
OBJECT_NAME OBJECT_TYPE
-------------------- --------------------
RAISEDSALARYBYEMPNOP PROCEDURE
ROC
ADD_DEPT_PROC PROCEDURE
EMPCOPY TABLE
TMP TABLE
DEPT TABLE
SALGRADE TABLE
BONUS TABLE
EMP TABLE
EMP_VW VIEW
已选择14行。
SQL> set linesize 200;
SQL> /
OBJECT_NAME OBJECT_TYPE
-------------------- --------------------
TAX FUNCTION
BIN$kHmZTqomReyTGf6x INDEX
Gpa9GQ==$2
BIN$gtl8hEtDTwyJSJRw INDEX
zqxFjQ==$0
BIN$lBwhR7ckQVmBoQJC INDEX
jyJTxg==$2
AVGSALARYFORDEPT PROCEDURE
OBJECT_NAME OBJECT_TYPE
-------------------- --------------------
RAISEDSALARYBYEMPNOP PROCEDURE
ROC
ADD_DEPT_PROC PROCEDURE
EMPCOPY TABLE
TMP TABLE
DEPT TABLE
SALGRADE TABLE
BONUS TABLE
EMP TABLE
EMP_VW VIEW
已选择14行。
SQL> set pagesize 100;
SQL> /
OBJECT_NAME OBJECT_TYPE
-------------------- --------------------
TAX FUNCTION
BIN$kHmZTqomReyTGf6x INDEX
Gpa9GQ==$2
BIN$gtl8hEtDTwyJSJRw INDEX
zqxFjQ==$0
BIN$lBwhR7ckQVmBoQJC INDEX
jyJTxg==$2
AVGSALARYFORDEPT PROCEDURE
RAISEDSALARYBYEMPNOP PROCEDURE
ROC
ADD_DEPT_PROC PROCEDURE
EMPCOPY TABLE
TMP TABLE
DEPT TABLE
SALGRADE TABLE
BONUS TABLE
EMP TABLE
EMP_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 FUNCTION
BIN$gtl8hEtDTwyJSJRw INDEX
zqxFjQ==$0
BIN$lBwhR7ckQVmBoQJC INDEX
jyJTxg==$2
BIN$kHmZTqomReyTGf6x INDEX
Gpa9GQ==$2
EMPLOYEE_PKG PACKAGE
EMPLOYEE_PKG PACKAGE BODY
RAISEDSALARYBYEMPNOP PROCEDURE
ROC
AVGSALARYFORDEPT PROCEDURE
ADD_DEPT_PROC PROCEDURE
EMPCOPY TABLE
SALGRADE TABLE
EMP TABLE
BONUS TABLE
TMP TABLE
DEPT TABLE
EMP_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 FUNCTION
BIN$gtl8hEtDTwyJSJRw INDEX
zqxFjQ==$0
BIN$lBwhR7ckQVmBoQJC INDEX
jyJTxg==$2
BIN$kHmZTqomReyTGf6x INDEX
Gpa9GQ==$2
EMPLOYEE_PKG PACKAGE
EMPLOYEE_PKG PACKAGE BODY
RAISEDSALARYBYEMPNOP PROCEDURE
ROC
AVGSALARYFORDEPT PROCEDURE
ADD_DEPT_PROC PROCEDURE
EMPCOPY TABLE
SALGRADE TABLE
EMP TABLE
BONUS TABLE
TMP TABLE
DEPT TABLE
EMP_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 号雇员的姓名为: CLARK
PL/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 10
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 --对包头中增加的内容作出具体实现
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 雇员工资:1300
PL/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 雇员工资:9900
PL/SQL 过程已成功完成。