PLSQL Developer 16 (64 bit) 新建自增主键表、存储过程、定时任务、添加新数据库链接

--在oracal里没有commit的语句是不会落地的，所以从navicat查不出来
INSERT INTO "C##TESTDB"."JY_TABLE_BASE" ( "TIME", "PROCESS", "PRODUCTION_LINE", "CLASSIFY_ONE", "CLASSIFY_TWO", "CLASSIFY_THREE", "CLASSIFY_FOUR", "DAY_BENCHMARK_PRICE", "DAY_BENCHMARK_CONSUMPTION", "DAY_COST_PRICE", "DAY_COST_CONSUME") VALUES ( TO_DATE('2026-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'), '炼铁厂', '2#高炉', '1. 主要原材料', '1. 入炉净矿', '02. 自产球团矿', NULL, '', '', '', '');
COMMIT;

--执行存储过程

　　begin
　　　　--执行存储过程(无参)
　　　　C##TESTDB.JY_INSERT_MOUDLE;
　　　　--执行存储过程(有参)
　　　　--C##TESTDB.JY_INSERT_MOUDLE(参数1,参数2,....);
　　end;

-----------------------------------------------------------新建存储过程：--------------------------------------------------------------------------------------------------------------

2.需要先保存文件

3.保存后，再重新执行编译，会报错，如下，因为在存储过程里，我们什么都没写

4.我们添加了一句打印，还是保存，看起来还是不满足要求

错误写法：end C##TESTDB.JY_INSERT_MOUDLE;（包含了模式名前缀）

正确写法：END JY_INSERT_MOUDLE;（只需过程名）

6。编译成功的样子

7.此时在文件夹下刷新，就会出来新增的存储过程了

8.可执行的存储过程进行嵌套调用，公共参数统一由外部传入

注意：当修改存储过程的时候，要先进行保存，再执行编译，之后再调用查看结果。注意如果结果不如预期，可按此步骤操作

JY_INSERT_MAIN

create or replace procedure c##testdb.JY_INSERT_MAIN is
  --获取当前日期(去掉时间部分)
  v_today DATE := TRUNC(SYSDATE);
begin
  --调用子存储过程
  jy_insert_moudle(v_today);
  
end JY_INSERT_MAIN;

JY_INSERT_MOUDLE

create or replace procedure c##testdb.JY_INSERT_MOUDLE(v_today DATE) is
 -- 定义VARRAY类型(最大5个元素)
  TYPE line_array IS VARRAY(5) OF VARCHAR2(20); 
  -- 声明并初始化数组变量
  v_line line_array := line_array('1#高炉', '2#高炉', '3#高炉');

begin
  DBMS_OUTPUT.PUT_LINE('开始数据插入：');

  
  FOR i IN 1..v_line.COUNT LOOP
    --DBMS_OUTPUT.PUT_LINE('颜色' || i || ': ' || v_line(i));
    INSERT INTO "C##TESTDB"."JY_TABLE_BASE" ( "TIME", "PROCESS", "PRODUCTION_LINE", "CLASSIFY_ONE", "CLASSIFY_TWO", "CLASSIFY_THREE", "CLASSIFY_FOUR", "DAY_BENCHMARK_PRICE", "DAY_BENCHMARK_CONSUMPTION", "DAY_COST_PRICE", "DAY_COST_CONSUME") 
                                     VALUES ( v_today, '炼铁厂', v_line(i), '1. 主要原材料', '1. 入炉净矿', '02. 自产球团矿', NULL, NULL, NULL, NULL, NULL);
  
  END LOOP;
  
  
  
  COMMIT;--提交当前事务
    DBMS_OUTPUT.PUT_LINE('成功插入数据: ');
  EXCEPTION
    WHEN OTHERS THEN --抛出异常
      DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
end JY_INSERT_MOUDLE;

-------------------------------------------------------------------------------添加其他Oracle库的监听配置------------------------------------------------------------------------------------------------------

修改配置文件

listener.ora

# listener.ora Network Configuration File: D:\WINDOWS.X64_193000_db_home\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = D:\WINDOWS.X64_193000_db_home)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:D:\WINDOWS.X64_193000_db_home\bin\oraclr19.dll")
    )
　　　--以下新添加部分不是必须
    (SID_DESC =
      (SID_NAME = ORCLPDB)
      (ORACLE_HOME = D:\WINDOWS.X64_193000_db_home)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:D:\WINDOWS.X64_193000_db_home\bin\oraclr19.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

tnsnames.ora

# tnsnames.ora Network Configuration File: D:\WINDOWS.X64_193000_db_home\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
  
# 新增使用 xxx.xxx.xxx.xxx 的连接配置 新增链接只需要添加如下模块，改变地址就行了。其他不用动
ORCL_43 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xxx.xxx)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )