Write Oracle stored procedures, ask for help

willerHE · Posted on 9/2/2018 3:58:01 PM

The question is this:
1. Create a log table EXEC_LOG record the execution of stored procedures:
2. Create a stored procedure P_INSERT_EXEC_LOG to add operations to the log table
3. Create a stored procedure P_UPDATE_EXEC_LOG and modify the log table
4. Create a table USER_3G store the NET_TYPE of the DW.DW_F_USER_3G_MON from January 2010 to June 2010 with a of '02' and a USER_TYPE of 1.
5. Create a stored procedure P_USER_3G for the script of FixedTopic 4, and call the P_INSERT_EXEC_LOG and P_UPDATE_EXEC_LOG to record the execution in the log table.

Requirements:
1. The information that needs to be included in the log table:
Process name, data period, such as month, start time, end time, duration, execution status, etc.
2. The process name and data period should be the primary key of the log table, that is, a stored procedure cannot be repeated in a data cycle.

Thank you for helping me as a beginner

Little scum · Posted on 9/4/2018 11:04:56 AM

The following is the sql statement for creating tables and creating stored procedures, refer to yourself, draw inferences from one example, and Baidu if you don't understand.

oracle command to create a primary key foreign key

1. Create a student table
create table t_stu(
  stuid    number(10) primary key,
  stuname varchar2(20) not null,
  stusex varchar2(2) default 'male' check(stusex in('male', 'female')))
);

2. Create a class schedule
create table t_couse(
  couseid    number(10) primary key,
  cousename varchar2(20) not null,
  cousetype varchar2(4)
);

3. Create a student course report sheet (including the main foreign key)
create table t_score(
  scoreid number(10) primary key,
  stuid    number(10) references t_stu(stuid),
  couseid number(10),
  constraint fk_couseid foreign key(couseid)
  references t_couse(couseid)
  on delete cascade
);

Oracle creates stored procedures

1. Basic grammar

Login is visible.

2. Write a simple example to modify the ename field of the emp table

Login is visible.

The call method is as follows:

SQL>exec update_emp('7935','test');

Old people's faces · Posted on 4/30/2019 6:15:54 PM

According to the actual situation, everyone has an SD card

[New Issue] Write Oracle stored procedures, ask for help

Sections viewed