Oracle PL/SQL Tutorial/Function Procedure Packages/Utility Package
Содержание
Audit package
<source lang="sql">
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></source>
Check the code version
<source lang="sql">
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></source>
Counter package
<source lang="sql">
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></source>
Create a package to handle display, update, delete and insert operations
<source lang="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> 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.</source>
Get full name package
<source lang="sql">
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></source>
Match a date format
<source lang="sql">
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.</source>
Process date value
<source lang="sql">
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.</source>
Product check
<source lang="sql">
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.</source>
Timer package
<source lang="sql">
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></source>
Validate all objects in a schema
<source lang="sql">
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 --</source>
