Principles and usage of oracle packages

Principles and usage of oracle packages

Package
Package definition:

A package is an encapsulation package for objects such as related procedures, functions, variables, cursors, and exceptions. It consists of two parts: specification and entity.


Advantages: Modular, easier application design, Information Hiding, new features, and better performance.
The basic operations for creating a packet header are as follows:

Create or replace package 1 -- create a header/specification isaa number: = 1; -- declare the public variable procedure update_student (update_row in student % rowtype) in the header ); -- declare an update process procedure insert_student (insert_row in student % rowtype); -- declare an insert process end pack1; -- end Baotou -- Package createdcreate or replace package body pack1 -- create a Package body/entity isbb number: = 2; -- The private variable procedure insert_student (insert_row in student % rowtype) declared in the package body -- the owner asbeg During the creation process In insert into student (id, name, age) values (insert_rodent id, insert_row.name, insert_row.age); dbms_output.put_line ('bb = '| bb | 'aa =' | aa ); end insert_student; -- end process subject procedure update_student (update_row in student % rowtype) -- create process subject as begin update student s set s. name = 'zhao bei' where s. id = update_row.id; end update_student; -- end process subject end pack1; -- end subject/Package body -- Warning: Package body created With compilation errorsSQL> show error; -- Query error Errors for package body hr. PACK1: LINE/col error ------------------------------------ ---------------------------------------- 5/1 PLS-00103: the symbol "BEGIN" appears when the following is required:; is with authid as cluster order using external deterministic parallel_enable pipelined result_cache symbol ";" is replaced with "BEGIN" and continues. 10/3 PLS-00103: occurrence of the symbol "PROCEDURE" 11/5 PLS-00103: occurrence of the symbol "BEGIN" when the following is required :; is with authid as cluster order using external deterministic parallel_enable pipelined result_cache symbol ";" is replaced with "BEGIN" and then continues. SQL> ed -- Modify the last executed code block SQL>/-- execute the modified code block -- Package body createdSQL> set serverout on; -- enable the output switch SQL> execute dbms_output.put_line (pack1.aa ); -- The public variables in the package are output 1PL/SQL procedure successfully completedSQL> execute extract (pack1.bb); -- the private variables in the package are not output begin dbms_output.put_line (pack1.bb); end; -- ORA-06550: 1st rows, 34th columns: -- PLS-00302: must declare 'bb' components -- ORA-06550: 1st rows, 7th columns: -- PL/SQL: Statement ignored declare row_student student % rowtype; -- declare the row-level variable begin row_student.id: = 5; row_student.name: = 'zhangfe'; row_student.age: = 60; pack1.insert _ student (row_student); -- call the end Process in the package; /bb = 2aa = 1PL/SQL procedure successfully completedSQL> select * from student; id name age ----------- Jun ----------- 1 Zhang San 20 2 Li Si 25 3 Wang Wu 30 4 Ma Zi 30 5 Zhang Fei 60SQL> declare row_student student % rowtype; -- declare the row-level variable begin detail: = 5; row_student.name: = 'guan Yu '; row_student.age: = 60; pack1.update _ student (row_student); -- call the process end in the package;/PL/SQL procedure successfully completedSQL> select * from student; id name age ----------- -------------------- ----------- 1 Zhang San 20 2 Li Si 25 3 Wang Wu 30 4 Ma Zi 30 5 Zhao bei 60

Cursors in the package: The definition of q cursor is divided into two parts: the cursor specification and the cursor subject. When q declares the cursor specification in the packet specification, the RETURN clause must be used to specify the RETURN type of the cursor. The data type specified by the qRETURN clause can be: q uses the % ROWTYPE attribute to reference the record type defined by the table q programmer, for example, type emprectyp is record (emp_id INTEGER, salaryREAL. Q cannot be of the number, varchar2, or % TYPE.

Create a display cursor in the package --------------- create or replace package 2 -- create a Baotou iscursor student_cursor return student % rowtype; -- declare the display cursor, but cannot follow the is select clause procedure student_pro; -- declare the process end pack2; create or replace package body pack2 -- create the package body iscursor student_cursor return student % rowtype is select * from student; -- specify the selectprocedure student_pro associated with the cursor is student_row student % rowtype; begin open student_cursor; fetch student_cursor into student_row; while student_cursor % found loop evaluate ('student id = '| student | 'name =' | student); fetch student_cursor into student_row; end loop; close student_cursor; end student_pro; end pack2;/SQL> execute pack2.student _ pro; student ID = 1 name = student ID = 2 name = student ID = 3 name = student ID = 4 name = student ID = 5 name = Zhao bei PL/SQL procedure successfully completedSQL> explain in the package create ref cursor --------------- create or replace package pack3istype ref_cursor is ref cursor; -- declare a ref cursor type procedure ref_student_pro; end pack3; -- Package createdcreate or replace package body pack3isprocedure ref_student_pro is student_row student % rowtype; then ref_cursor; -- declare a ref cursor type variable begin open student_ref_cursor for select * from student; fetch student_ref_cursor into student_row; while student % found loop student ('student id = '| student_roentid | 'name =' | student_roentname); fetch into student_row; end loop; close future; end ref_student_pro; end pack3; -- Package body createdSQL> execute pack3.ref _ student_pro; student ID = 1 name = student ID = 2 name = student ID = 3 name = student ID = 4 name = student ID = 5 name = Zhao bei PL/SQL procedure successfully completedSQL>

Built-in system package:

Usage of mongodbms_job package: using create table test_job (date_sign date); create or replace procedure pro_testisbegin insert into test_job values (sysdate); end; SQL> variable job1 number; SQL> begin 2 dbms_job.submit (: job1, 'Pro _ test; ', sysdate, 'sysdate + 100'); -- the Submit () process is properly planned. 3 end; 4/PL/SQL procedure successfully completedjob1 --------- 23SQL> SQL> begin 2 dbms_job.run (: job1); -- the Run () process is used to immediately execute a specified job. This process only receives one parameter. 3 end; 4/PL/SQL procedure successfully completedjob1 --------- 23SQL> select * from test_job; DATE_SIGN-----------19-January-15 23SQL> select * from test_job; DATE_SIGN-----------19-January-15 23SQL> SQL> begin 2 dbms_job.remove (: job1); -- process to delete a scheduled job. This process receives a parameter. 3 end; 4/PL/SQL procedure successfully completedjob1 --------- 23SQL> using the Export UTL_FILE package: Using create directory dir_utl_file as '/u01/app/oracle/pl_ SQL _pacakge/test_utl_file '; -- create the directory grant read, write on directory dir_utl_file to hr; -- grant the user the permission create or replace procedure pro_utl_file (path_file in varchar2, name_file in varchar2) isutl_file_contents varchar2 (2000 ); -- Define the memory variable utl_file_type utl_file.file_type; -- Define the file type variable begin utl_file_type: = utl_file.fopen (path_file, name_file, 'R', 2000); -- open the file loop handler (utl_file_type, plugin ); -- read the file content to the memory variable dbms_output.put_line (utl_file_contents); -- and print the end loop; exception -- exception Processing part when no_data_found then utl_file.fclose (utl_file_type); end; procedure createdSQL> set serverout onSQL> execute pro_utl_file ('dir _ UTL_FILE ', 'utl _ file'); DECLARE V1 VARCHAR2 (32767); F1 UTL_FILE.FILE_TYPE; BEGIN -- In this example MAX_LINESIZE is less than GET_LINE's length request -- so the number of bytes returned will be 256 or less if a line terminator is seen. f1: = UTL_FILE.FOPEN ('mydir', 'myfile', 'R', 256); UTL_FILE.GET_LINE (F1, V1, 32767); UTL_FILE.FCLOSE (F1); -- In this example, FOPEN's MAX_LINESIZE is NULL and defaults to 1024, -- so the number of bytes returned will be 1024 or less if a line terminator is seen. f1: = UTL_FILE.FOPEN ('mydir', 'myfile', 'R'); UTL_FILE.GET_LINE (F1, V1, 32767); UTL_FILE.FCLOSE (F1); -- In this example, GET_LINE doesn't specify a number of bytes, so it defaults to -- the same value as FOPEN's MAX_LINESIZE which is NULL in this case and defaults to 1024. -- So the number of bytes returned will be 1024 or less if a line terminator is seen. f1: = UTL_FILE.FOPEN ('mydir', 'myfile', 'R'); UTL_FILE.GET_LINE (F1, V1); UTL_FILE.FCLOSE (F1); END; PL/SQL procedure successfully completedSQL> Use of dbms_random: return the random date of a year, in two steps: 1, SELECT TO_CHAR (TO_DATE ('2017/03/03 ', 'Mm/dd/yy'), 'J') from dual; 2, SELECT TO_DATE (TRUNC (DBMS_RANDOM.VALUE (2452641,245 2641 + 364), 'J') from dual;