Oracle PL/SQL Tutorial/Function Procedure Packages/Utility Package
Содержание
Audit package
SQL>
SQL> create table audit_trail
2 ( username varchar2(30),
3 pk number,
4 attribute varchar2(30),
5 dataum varchar2(255),
6 timestamp date
7 )
8 /
Table created.
SQL>
SQL>
SQL> create or replace package audit_trail_pkg
2 as
3 function record( p_pk in number,
4 p_attr in varchar2,
5 p_dataum in number ) return number;
6 function record( p_pk in number,
7 p_attr in varchar2,
8 p_dataum in varchar2 ) return varchar2;
9 function record( p_pk in number,
10 p_attr in varchar2,
11 p_dataum in date ) return date;
12 end;
13 /
Package created.
SQL>
SQL> create or replace package body audit_trail_pkg
2 as
3
4 procedure log( p_pk in number,
5 p_attr in varchar2,
6 p_dataum in varchar2 )
7 as
8 pragma autonomous_transaction;
9 begin
10 insert into audit_trail values
11 ( user, p_pk, p_attr, p_dataum, sysdate );
12 commit;
13 end;
14
15 function record( p_pk in number,
16 p_attr in varchar2,
17 p_dataum in number ) return number
18 is
19 begin
20 log( p_pk, p_attr, p_dataum );
21 return p_dataum;
22 end;
23
24 function record( p_pk in number,
25 p_attr in varchar2,
26 p_dataum in varchar2 ) return varchar2
27 is
28 begin
29 log( p_pk, p_attr, p_dataum );
30 return p_dataum;
31 end;
32
33 function record( p_pk in number,
34 p_attr in varchar2,
35 p_dataum in date ) return date
36 is
37 begin
38 log( p_pk, p_attr,
39 to_char(p_dataum,"dd-mon-yyyy hh24:mi:ss") );
40 return p_dataum;
41 end;
42
43 end;
44 /
Package body created.
SQL>
SQL>
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10),
3 JOB VARCHAR2(9),
4 MGR NUMBER(4),
5 HIREDATE DATE,
6 SAL NUMBER(7, 2),
7 COMM NUMBER(7, 2),
8 DEPTNO NUMBER(2));
Table created.
SQL>
SQL>
SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL>
SQL>
SQL> create or replace view emp_v
2 as
3 select empno , ename, job,mgr,
4 audit_trail_pkg.record( empno, "sal", sal ) sal,
5 audit_trail_pkg.record( empno, "comm", comm ) comm,
6 audit_trail_pkg.record( empno, "hiredate", hiredate ) hiredate,
7 deptno
8 from emp
9 /
View created.
SQL> select empno, ename, hiredate, sal, comm, job from emp_v where ename = "KING";
EMPNO ENAME HIREDATE SAL COMM JOB
---------- ---------- --------- ---------- ---------- ---------
7839 KING 17-NOV-81 5000 PRESIDENT
SQL>
SQL> drop table audit_trail;
Table dropped.
SQL> drop table emp;
Table dropped.
SQL>
SQL>
Check the code version
SQL>
SQL> CREATE OR REPLACE PACKAGE globals IS
2 FUNCTION what_version RETURN VARCHAR2;
3 PRAGMA RESTRICT_REFERENCES(what_version, WNDS, WNPS, RNDS);
4 END globals;
5 /
Package created.
SQL>
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY globals IS
2 pv_version_txt VARCHAR2(30) := "19980519.1";
3 FUNCTION what_version RETURN VARCHAR2 IS
4 BEGIN
5 RETURN pv_version_txt;
6 END; -- what_version
7 END globals;
8 /
Package body created.
SQL>
SQL>
SQL> SET SERVEROUTPUT ON SIZE 1000000
SQL> DECLARE
2 lv_dml_statement_txt VARCHAR2(100);
3 lv_package_version_txt VARCHAR2(100);
4 lv_record_count_num PLS_INTEGER;
5 lv_version_cursor_num PLS_INTEGER;
6 CURSOR cur_source IS
7 SELECT DISTINCT name
8 FROM user_source
9 WHERE type = "PACKAGE BODY";
10 BEGIN
11 FOR cur_source_rec IN cur_source LOOP
12 lv_version_cursor_num := DBMS_SQL.OPEN_CURSOR;
13 lv_dml_statement_txt := "SELECT " || cur_source_rec.name || ".what_version FROM DUAL";
14 BEGIN
15 DBMS_SQL.PARSE(lv_version_cursor_num, lv_dml_statement_txt, DBMS_SQL.NATIVE);
16 DBMS_SQL.DEFINE_COLUMN(lv_version_cursor_num, 1, lv_package_version_txt, 100);
17 lv_record_count_num := DBMS_SQL.EXECUTE(lv_version_cursor_num);
18 IF DBMS_SQL.FETCH_ROWS(lv_version_cursor_num) > 0 THEN
19 DBMS_SQL.COLUMN_VALUE(lv_version_cursor_num, 1, lv_package_version_txt);
20 ELSE
21 lv_package_version_txt := "Version Reporting Failed";
22 END IF;
23 EXCEPTION
24 WHEN OTHERS THEN
25 lv_package_version_txt := "Version Reporting Not " || "Supported";
26 END;
27 DBMS_OUTPUT.PUT_LINE(LOWER(cur_source_rec.name) || ": " || lv_package_version_txt);
28 DBMS_SQL.CLOSE_CURSOR(lv_version_cursor_num);
29 END LOOP;
30 END;
31 /
gender_ts: Version Reporting Not Supported
demo_pkg: Version Reporting Not Supported
emp_coll_pkg: Version Reporting Not Supported
book_info: Version Reporting Not Supported
hr_app: Version Reporting Not Supported
onecur: Version Reporting Not Supported
empinfo: Version Reporting Not Supported
employees_pkg: Version Reporting Not Supported
dates: Version Reporting Not Supported
classpackage: Version Reporting Not Supported
plw5000: Version Reporting Not Supported
grp: Version Reporting Not Supported
desccols: Version Reporting Not Supported
fixer: Version Reporting Not Supported
process_vacations: Version Reporting Not Supported
xbuff: Version Reporting Not Supported
timer: Version Reporting Not Supported
srpkg1: Version Reporting Not Supported
bt: Version Reporting Not Supported
oracle_error_info: Version Reporting Not Supported
test_pack: Version Reporting Not Supported
order_entry: Version Reporting Not Supported
procesorders: Version Reporting Not Supported
pipe_output: Version Reporting Not Supported
overload: Version Reporting Not Supported
flog: Version Reporting Not Supported
audit_trail_pkg: Version Reporting Not Supported
dyn_demo: Version Reporting Not Supported
p1: Version Reporting Not Supported
workplace_type_ts: Version Reporting Not Supported
scopes: Version Reporting Not Supported
rfpkg2: Version Reporting Not Supported
srpkg2: Version Reporting Not Supported
valstd: Version Reporting Not Supported
nocopy_test: Version Reporting Not Supported
favorites_pkg: Version Reporting Not Supported
pack1: Version Reporting Not Supported
dowpack: Version Reporting Not Supported
pack2: Version Reporting Not Supported
globals: 19980519.1
mypackage1: Version Reporting Not Supported
guestbook: Version Reporting Not Supported
worker_ts: Version Reporting Not Supported
worker_type_ts: Version Reporting Not Supported
valerr: Version Reporting Not Supported
mydate: Version Reporting Not Supported
orgmaster: Version Reporting Not Supported
nocopypkg: Version Reporting Not Supported
calc_pkg: Version Reporting Not Supported
thisuser: Version Reporting Not Supported
my_package: Version Reporting Not Supported
employee_pkg: Version Reporting Not Supported
diana_size: Version Reporting Not Supported
process_emps: Version Reporting Not Supported
demo2: Version Reporting Not Supported
employee_types: Version Reporting Not Supported
salespkg: Version Reporting Not Supported
orgmaster2: Version Reporting Not Supported
authors_pkg: Version Reporting Not Supported
anynums_pkg: Version Reporting Not Supported
bidir: Version Reporting Not Supported
fullname_pkg: Version Reporting Not Supported
clean_schema: Version Reporting Not Supported
sessval: Version Reporting Not Supported
datecalc: Version Reporting Not Supported
my_date: Version Reporting Not Supported
genders: Version Reporting Not Supported
dynsql: Version Reporting Not Supported
multdim: Version Reporting Not Supported
inline_pkg: Version Reporting Not Supported
parameters: Version Reporting Not Supported
rfpkg: Version Reporting Not Supported
dynvar: Version Reporting Not Supported
name_pkg: Version Reporting Not Supported
pkg_vars: Version Reporting Not Supported
my_pkg: Version Reporting Not Supported
not_mypackage2: Version Reporting Not Supported
outputpackage: Version Reporting Not Supported
pizza_pkg: Version Reporting Not Supported
PL/SQL procedure successfully completed.
SQL>
Counter package
SQL>
SQL>
SQL>
SQL> create or replace package counter is
2 procedure reset;
3 procedure inc;
4 procedure show;
5 end;
6 /
Package created.
SQL> create or replace package body counter is
2 cnt pls_integer := 0;
3 procedure reset is
4 begin
5 cnt := 0;
6 end;
7
8 procedure inc is
9 begin
10 cnt := cnt + 1;
11 end;
12
13 procedure show is
14 begin
15 dbms_output.put_line("Execution Count: "||cnt);
16 end;
17 end;
18 /
Package body created.
SQL>
SQL>
Create a package to handle display, update, delete and insert operations
SQL>
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10),
3 JOB VARCHAR2(9),
4 MGR NUMBER(4),
5 HIREDATE DATE,
6 SAL NUMBER(7, 2),
7 COMM NUMBER(7, 2),
8 DEPTNO NUMBER(2));
Table created.
SQL>
SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL>
SQL> CREATE TABLE DEPT (DEPTNO NUMBER(2),DNAME VARCHAR2(14),LOC VARCHAR2(13) );
Table created.
SQL>
SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK");
1 row created.
SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS");
1 row created.
SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO");
1 row created.
SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON");
1 row created.
SQL>
SQL> create or replace package hr_app
2 as
3 procedure listEmps;
4 procedure updateSal;
5 procedure deleteAll;
6 procedure insertNew( p_deptno in number );
7 end;
8 /
Package created.
SQL>
SQL> create or replace package body hr_app as
2 procedure listEmps
3 as
4 l_cnt number default 0;
5 begin
6 for x in ( select ename, sal, dname, mgr, emp.deptno from emp, dept
7 where emp.deptno = dept.deptno )
8 loop
9 dbms_output.put_line( rpad(nvl(x.ename,"(null)"),10) ||
10 to_char(x.sal,"9,999") || " " ||
11 rpad(x.dname,10) ||
12 to_char(x.mgr,"9999") || " " ||
13 to_char(x.deptno,"99") );
14 l_cnt := l_cnt + 1;
15 end loop;
16 dbms_output.put_line( l_cnt || " rows selected" );
17 end;
18 procedure updateSal is
19 begin
20 update emp set sal = 9999;
21 dbms_output.put_line( sql%rowcount || " rows updated" );
22 end;
23
24 procedure deleteAll
25 is
26 begin
27 delete from emp where empno <> sys_context("Your_CTX","EMPNO" );
28 dbms_output.put_line( sql%rowcount || " rows deleted" );
29 end;
30
31 procedure insertNew( p_deptno in number )
32 as
33 begin
34 insert into emp (empno, deptno, sal) values (123, p_deptno, 1111);
35 end;
36
37 end hr_app;
38 /
Package body created.
SQL>
SQL> grant execute on hr_app to public
2 /
Grant succeeded.
Get full name package
SQL>
SQL> CREATE OR REPLACE PACKAGE errpkg
2 IS
3 PROCEDURE record_and_stop;
4
5 END errpkg;
6 /
Package created.
SQL>
SQL>
SQL> CREATE TABLE employee (
2 employee_id NUMBER(38,0)
3 ,deptno NUMBER(3,0) NOT NULL
4 ,first_name VARCHAR2(95) NOT NULL
5 ,last_name VARCHAR2(95) NOT NULL
6 ,salary NUMBER(11,2)
7 );
Table created.
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE PACKAGE employee_pkg
2 AS
3 SUBTYPE fullname_t IS VARCHAR2 (200);
4
5 FUNCTION fullname (l employee.last_name%TYPE,f employee.first_name%TYPE)
6 RETURN fullname_t;
7
8 FUNCTION fullname (employee_id_in IN employee.employee_id%TYPE)
9 RETURN fullname_t;
10 END employee_pkg;
11 /
Package created.
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY employee_pkg
2 AS
3 FUNCTION fullname (l employee.last_name%TYPE,f employee.first_name%TYPE)
4 RETURN fullname_t
5 IS
6 BEGIN
7 RETURN l || "," || f;
8 END;
9
10 FUNCTION fullname (employee_id_in IN employee.employee_id%TYPE)
11 RETURN fullname_t
12 IS
13 retval fullname_t;
14 BEGIN
15 SELECT fullname (last_name, first_name) INTO retval
16 FROM employee
17 WHERE employee_id = employee_id_in;
18
19 RETURN retval;
20 EXCEPTION
21 WHEN NO_DATA_FOUND THEN RETURN NULL;
22
23 WHEN TOO_MANY_ROWS THEN errpkg.record_and_stop;
24 END;
25 END employee_pkg;
26 /
SP2-0810: Package Body created with compilation warnings
SQL>
SQL>
SQL>
SQL>
SQL> DECLARE
2 l_name employee_pkg.fullname_t;
3 employee_id_in CONSTANT PLS_INTEGER := 1;
4 BEGIN
5 l_name := employee_pkg.fullname (employee_id_in);
6
7 END;
8 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
Match a date format
SQL>
SQL> CREATE OR REPLACE PACKAGE my_date
2 IS
3 FUNCTION get_date (dt_str IN VARCHAR2)
4 RETURN DATE;
5 END;
6 /
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY my_date
2 IS
3 TYPE date_fmt_t IS TABLE OF VARCHAR2 (30) INDEX BY BINARY_INTEGER;
4 target_format date_fmt_t;
5
6 FUNCTION test_date (dt_str IN VARCHAR2,target IN VARCHAR2) RETURN DATE
7 IS
8 d DATE;
9 BEGIN
10 RETURN TO_DATE (dt_str, target);
11 EXCEPTION
12 WHEN OTHERS
13 THEN RETURN NULL;
14 END;
15
16 FUNCTION get_date (dt_str IN VARCHAR2)RETURN DATE IS
17 ret_val DATE DEFAULT NULL;
18 BEGIN
19 FOR i IN target_format.FIRST .. target_format.LAST
20 LOOP
21 ret_val := test_date (dt_str, target_format (i));
22 EXIT WHEN ret_val IS NOT NULL;
23 END LOOP;
24 RETURN ret_val;
25 END;
26 BEGIN
27 target_format (1) := "MM/DD";
28 target_format (2) := "MM/DD/YY";
29 target_format (3) := "MM/DD/YYYY";
30 target_format (4) := "DD-MON";
31 target_format (5) := "MON DD, YYYY";
32 target_format (6) := "MONTH DD, YYYY";
33 target_format (7) := "DD-MON-YY";
34 target_format (8) := "DD-MON-YYYY";
35 target_format (9) := "MON-DD-YYYY";
36 END;
37 /
Package body created.
Process date value
SQL>
SQL> CREATE OR REPLACE PACKAGE sessval IS
2 PROCEDURE set_mydate (date_in IN DATE);
3 FUNCTION mydate RETURN DATE;
4 END sessval;
5 /
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY sessval IS
2 g_mydate DATE;
3 PROCEDURE set_mydate (date_in IN DATE) IS
4 BEGIN
5 IF date_in IS NULL OR date_in > SYSDATE
6 THEN DBMS_OUTPUT.PUT_LINE ("Sessval.mydate cannot be set into the future.");
7 ELSE DBMS_OUTPUT.PUT_LINE ("Current value of sessval.mydate: " || g_mydate);
8 DBMS_OUTPUT.PUT_LINE ("New value of sessval.mydate: " || date_in);
9 DBMS_OUTPUT.PUT_LINE (DBMS_UTILITY.FORMAT_CALL_STACK);
10 g_mydate := date_in;
11 END IF;
12 END;
13
14 FUNCTION mydate RETURN DATE IS
15 BEGIN RETURN g_mydate;
16 END;
17 END sessval;
18 /
Package body created.
SQL> CREATE OR REPLACE PROCEDURE test_trace
2 IS
3 BEGIN
4 sessval.set_mydate ( NVL (sessval.mydate, SYSDATE) -100);
5 END;
6 /
Procedure created.
Product check
SQL>
SQL>
SQL> CREATE TABLE product
2 (product_id NUMBER(7),
3 product_name VARCHAR2(50),
4 short_desc VARCHAR2(255),
5 longtext_id NUMBER(7),
6 image_id NUMBER(7),
7 suggested_wholesale_price NUMBER(11, 2),
8 wholesale_units VARCHAR2(25)
9 );
Table created.
SQL>
SQL>
SQL> INSERT INTO product VALUES (10011, "Product A", "BOOT", 518, 1001, 150, NULL);
1 row created.
SQL> INSERT INTO product VALUES (10012, "Product B", "SKI", 519, 1002, 200, NULL);
1 row created.
SQL> INSERT INTO product VALUES (10013, "Product C", "SKI BOOT", 520, 1003, 410, NULL);
1 row created.
SQL> INSERT INTO product VALUES (10021, "Product D", "POLE", 528, 1011, 16.25, NULL);
1 row created.
SQL> INSERT INTO product VALUES (10022, "Product E", "Cat", 529, 1012, 21.95, NULL);
1 row created.
SQL> INSERT INTO product VALUES (10023, "Product F", "Dog", 530, 1013, 40.95, NULL);
1 row created.
SQL> INSERT INTO product VALUES (20106, "Product G", "Bear", 613, NULL, 11, NULL);
1 row created.
SQL> INSERT INTO product VALUES (20108, "Product H", "BALL", 615, NULL, 28, NULL);
1 row created.
SQL> INSERT INTO product VALUES (20201, "Product I", "NET", 708, NULL, 123, NULL);
1 row created.
SQL> INSERT INTO product VALUES (20510, "Product J", "PADS, PAIR", 1017, NULL, 9, NULL);
1 row created.
SQL> INSERT INTO product VALUES (20512, "Product K", "PADS, PAIR", 1019, NULL, 8, NULL);
1 row created.
SQL>
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE PACKAGE procesproducts IS
2 TYPE type_prod_table IS TABLE OF product%ROWTYPE
3 INDEX BY BINARY_INTEGER;
4 pvg_prod_table type_prod_table;
5 PROCEDURE populate_prod_table;
6 PROCEDURE check_product_id (p_prod_id_num product.product_id%TYPE);
7 PROCEDURE check_product_name (p_prod_name_txt product.
8 product_name%TYPE);
9 END procesproducts;
10 /
Package created.
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY procesproducts IS
2 PROCEDURE populate_prod_table IS
3 CURSOR cur_product IS
4 SELECT *
5 FROM product;
6 BEGIN
7 pvg_prod_table.DELETE;
8 FOR lv_prod_rec IN cur_product LOOP
9 pvg_prod_table(lv_prod_rec.product_id).product_id := lv_prod_rec.product_id;
10 pvg_prod_table(lv_prod_rec.product_id).product_name := lv_prod_rec.product_name;
11 pvg_prod_table(lv_prod_rec.product_id).short_desc := lv_prod_rec.short_desc;
12 pvg_prod_table(lv_prod_rec.product_id).suggested_wholesale_price := lv_prod_rec.suggested_wholesale_price;
13 END LOOP;
14 EXCEPTION
15 WHEN OTHERS THEN
16 RAISE_APPLICATION_ERROR(-20100,
17 "Error in procedure POPULATE_PROD_TABLE.", FALSE);
18 END populate_prod_table;
19
20 PROCEDURE check_product_id(p_prod_id_num product.product_id%TYPE) IS
21 BEGIN
22 IF pvg_prod_table.EXISTS(p_prod_id_num) THEN
23 DBMS_OUTPUT.PUT_LINE("Product ID: " ||
24 pvg_prod_table(p_prod_id_num).product_id );
25 DBMS_OUTPUT.PUT_LINE("Product Name: " ||
26 pvg_prod_table(p_prod_id_num).product_name );
27 DBMS_OUTPUT.PUT_LINE("Description: " ||
28 pvg_prod_table(p_prod_id_num).short_desc );
29 DBMS_OUTPUT.PUT_LINE("Wholesale Price: " ||
30 TO_CHAR(pvg_prod_table(p_prod_id_num).
31 suggested_wholesale_price, "$9999.00"));
32 DBMS_OUTPUT.PUT_LINE(CHR(10));
33 ELSE
34 DBMS_OUTPUT.PUT_LINE(TO_CHAR(p_prod_id_num) || " is invalid.");
35 END IF;
36 EXCEPTION
37 WHEN OTHERS THEN
38 RAISE_APPLICATION_ERROR(-20102,
39 "Error in procedure CHECK_PRODUCT_ID.", FALSE);
40 END check_product_id;
41 PROCEDURE check_product_name
42 (p_prod_name_txt product.product_name%TYPE) IS
43 lv_index_num NUMBER;
44 lv_match_bln BOOLEAN := FALSE;
45 BEGIN
46 IF pvg_prod_table.COUNT <> 0 THEN
47 lv_index_num := pvg_prod_table.FIRST;
48 LOOP
49 IF (INSTR(UPPER(pvg_prod_table(lv_index_num).product_name),UPPER(p_prod_name_txt)) > 0) THEN
50 lv_match_bln := TRUE;
51 DBMS_OUTPUT.PUT_LINE("Product ID: " ||
52 pvg_prod_table(lv_index_num).product_id );
53 DBMS_OUTPUT.PUT_LINE("Product Name: " ||
54 pvg_prod_table(lv_index_num).product_name );
55 DBMS_OUTPUT.PUT_LINE("Description: " ||
56 pvg_prod_table(lv_index_num).short_desc );
57 DBMS_OUTPUT.PUT_LINE("Wholesale Price: " ||
58 TO_CHAR(pvg_prod_table(lv_index_num).
59 suggested_wholesale_price, "$9999.00"));
60 DBMS_OUTPUT.PUT_LINE(CHR(10));
61 END IF;
62 EXIT WHEN (lv_index_num = pvg_prod_table.LAST) OR
63 lv_match_bln;
64 lv_index_num := pvg_prod_table.NEXT(lv_index_num);
65 END LOOP;
66 IF NOT lv_match_bln THEN
67 DBMS_OUTPUT.PUT_LINE("Product: " || p_prod_name_txt ||
68 " is invalid.");
69 END IF;
70 ELSE
71 DBMS_OUTPUT.PUT_LINE("There are no products in the table.");
72 END IF;
73 EXCEPTION
74 WHEN OTHERS THEN
75 RAISE_APPLICATION_ERROR(-20102,
76 "Error in procedure CHECK_PRODUCT_NAME.", FALSE);
77 END check_product_name;
78
79 end procesproducts;
80 /
Package body created.
SQL> show error
No errors.
SQL>
SQL>
SQL> drop table product;
Table dropped.
SQL> drop package procesproducts;
Package dropped.
Timer package
SQL>
SQL> CREATE OR REPLACE PACKAGE timer
2 IS
3 PROCEDURE capture;
4 PROCEDURE show_elapsed;
5 END timer;
6 /
Package created.
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY timer
2 IS
3 last_timing INTEGER := NULL;
4
5 PROCEDURE capture IS
6 BEGIN
7 last_timing := DBMS_UTILITY.GET_TIME;
8 END;
9
10 PROCEDURE show_elapsed IS
11 BEGIN
12 DBMS_OUTPUT.PUT_LINE ("Elapsed time: "||(DBMS_UTILITY.GET_TIME - last_timing)/100);
13 END;
14 END timer;
15 /
Package body created.
SQL>
SQL> DECLARE
2 myname VARCHAR2(100);
3 BEGIN
4 timer.capture;
5 FOR i IN 1 .. 1000
6 loop
7 myname := USER;
8 end loop;
9 timer.show_elapsed;
10 end;
11 /
Elapsed time: .09
PL/SQL procedure successfully completed.
SQL>
Validate all objects in a schema
SQL>
SQL> CREATE OR REPLACE PROCEDURE validate_structure (i_schema IN VARCHAR2) IS
2 v_sql VARCHAR2 (2000);
3 CURSOR cur_objects (p_schema VARCHAR2) IS
4 SELECT object_name, object_type FROM sys.dba_objects
5 WHERE owner = p_schema AND object_type IN ("CLUSTER", "TABLE")
6 ORDER BY object_name;
7 BEGIN
8 FOR co IN cur_objects (i_schema)
9 LOOP
10 v_sql := "ANALYZE " ||
11 co.object_type ||
12 " " ||
13 i_schema ||
14 "." ||
15 co.object_name ||
16 " VALIDATE STRUCTURE CASCADE";
17 DBMS_UTILITY.exec_ddl_statement (v_sql);
18 DBMS_OUTPUT.put_line (v_sql);
19 END LOOP;
20 END;
21
22
23 --