Shulou(Shulou.com)06/01 Report--

One: stored procedure (procedure directory on the left side of the picture):

In the actual development process of the database, it is impossible for us to execute every script manually, we need to automatically submit the script to the database for execution, and the database provides objects such as stored procedures. It is convenient for developers to write the logic of dealing with a function or report into the stored procedure.

1) advantages of stored procedures:

a. Faster execution: stored procedure statements saved in the database are compiled

b. Allow modular programming and portability: reuse of similar methods (using stored procedures to separate stored procedure design and coding, as long as you tell the programmer the stored procedure name, parameters, return information, etc.)

c. Improve system security: prevent SQL injection (users who execute stored procedures must have certain permissions to use stored procedures)

d. Reduce network traffic: just transfer the name of the stored procedure (paging queries using stored procedures in large numbers of data queries are much faster than other paging methods)

e. When maintaining and verifying the data between master and slave tables at the same time, it is more convenient to use stored procedures and can effectively make use of the transaction mechanism in SQL.

Create Stora

2) create syntax:

Create or replace procedure p_house_create_data (p_fm_dt date default sysdate-1)

P_to_dt date default sysdate) is

/ *

Author: hf

Created: 2018-08-08

Purpose: data generation process

Parameter value

P_fm_dt 2018-08-01 (yesterday)

P_to_dt 2018-08-02 (same day)

* * /

/ *

Definition interval

* * /

V_sqlstate varchar2 (500)

V_proc_name varchar2 (64): = 'paired housekeeper createcreated data'

V_fm_dt date

V_to_dt date

Begin

/ *

Assignment interval

* * /

V_sqlstate: = 'assignment'

V_fm_dt: = trunc (p_fm_dt, 'DD')

V_to_dt: = trunc (p_to_dt, 'DD')

/ *

Calculation interval

* * /

V_sqlstate: = 'start'

Pkg_rpt_system.sys_log (v_proc_name, v_sqlstate, 'OK', null, null);-- write log

V_sqlstate: = 'Delete data'

Delete t_landlord

Commit

V_sqlstate: = 'generate landlord information data'

Insert into t_landlord

Values

('001',' Zhang Qiang', 'male', '13723870069',' 001', '2010-03-12')

Commit

/ *

End interval

* * /

V_sqlstate: = 'done'

Pkg_rpt_system.sys_log (v_proc_name, v_sqlstate, 'OK', null, null);-- write log

/ *

Abnormal interval

* * /

Exception

When others then

Rollback;-- rollback data

Pkg_rpt_system.sys_log (v_proc_name

V_sqlstate

'ERROR'

Sqlcode

Substr (sqlerrm, 1, 3000);-- write a log

Commit

End p_house_create_data

Two: package (package bodies directory on the left side of the picture):

In fact, packages can be understood as convenient management of stored procedures and functions. If there are too many procedures and functions, it is inconvenient to find and mess, then we can put the related processes together or the business logic-related ones together for maintenance.

1) composition of the package:

a. Packet header: a definition of procedures and functions in a package, related to directories

b. Package body: the implementation of the procedures and functions in the package, and the logical implementation of the specific code.

2) create syntax:

-- Baotou

Create or replace package pkg_abc_create_data is

Procedure p_house_create_data (p_fm_dt date default sysdate-1)

P_to_dt date default sysdate)

End pkg_abc_create_data

-- inclusion body

Create or replace package body pkg_abc_create_data is

Procedure p_house_create_data (p_fm_dt date default sysdate-1)

P_to_dt date default sysdate) is

/ *

Author: hf

Created: 2018-08-08

Purpose: data generation process

Parameter value

P_fm_dt 2018-08-01 (yesterday)

P_to_dt 2018-08-02 (same day)

* * /

/ *

Definition interval

* * /

V_sqlstate varchar2 (500)

V_proc_name varchar2 (64): = 'paired housekeeper createcreated data'

V_fm_dt date

V_to_dt date

Begin

/ *

Assignment interval

* * /

V_sqlstate: = 'assignment'

V_fm_dt: = trunc (p_fm_dt, 'DD')

V_to_dt: = trunc (p_to_dt, 'DD')

/ *

Calculation interval

* * /

V_sqlstate: = 'start'

Pkg_rpt_system.sys_log (v_proc_name, v_sqlstate, 'OK', null, null)

V_sqlstate: = 'Delete data'

Delete t_landlord

Commit

V_sqlstate: = 'generate landlord information data'

Insert into t_landlord

Values

('001',' Zhang Qiang', 'male', '13723870069',' 001', '2010-03-12')

Commit

/ *

End interval

* * /

V_sqlstate: = 'done'

Pkg_rpt_system.sys_log (v_proc_name, v_sqlstate, 'OK', null, null)

/ *

Abnormal interval

* * /

Exception

When others then

Rollback

Pkg_rpt_system.sys_log (v_proc_name

V_sqlstate

'ERROR'

Sqlcode

Substr (sqlerrm, 1, 3000))

Commit

End p_house_create_data

End pkg_abc_create_data

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.