写oracle数据库存储过程的笔记

曾经终败给现在 2023-10-13 18:40 23阅读 0赞

## 该sql语句可以查出某些没有自动弹出的错误 ##

SELECT * FROM USER_ERRORS WHERE NAME = '包名';

## 该sql语句可以导出包 ##

select dbms_metadata.get_ddl('PACKAGE', '包名') from dual;

create sequence t_r_workbench_city_CITYID
    minvalue 1
    maxvalue 99999999999999999999
    start with 1
    increment by 1
    cache 20
    cycle;
     t_r_workbench_city_CITYID --序列名（自增）
    
    
    t_r_workbench_city_trigger --触发器名
    T_R_WORKBENCH_CITY --表名（大小写严格）
    t_r_workbench_city."CITYID" --表中的主键
    create or replace trigger t_r_workbench_city_trigger
    before insert on "T_R_WORKBENCH_CITY" for each row
    begin
     select t_r_workbench_city_CITYID into :t_r_workbench_city."CITYID" from dual;
    end;

## 我要在oracle数据库的现有记录上,生成唯一标识符 ##

CREATE SEQUENCE seq_name1
    START WITH 1
    INCREMENT BY 1;

![在这里插入图片描述][a2b87ce69c044a3c83abc2d9649831bb.png]  
表上添加一个新的列

UPDATE tmpd_special_supervise_0823_02 t 
    SET t.rid = seq_name1.NEXTVAL;

检查

SELECT * FROM tmpd_special_supervise_0823_02 t;
    select count(t.rid) from  tmpd_special_supervise_0823_02 t  ; 
    --数值相等

完成

## oracle数据库导入excel文件 ##

链接: [利用PL/SQL Developer工具导入excel数据][PL_SQL Developer_excel]

[a2b87ce69c044a3c83abc2d9649831bb.png]: https://img-blog.csdnimg.cn/a2b87ce69c044a3c83abc2d9649831bb.png
[PL_SQL Developer_excel]: https://blog.csdn.net/snow1wolf/article/details/40505615?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522169319244016800182778579%2522%252C%2522scm%2522%253A%252220140713.130102334..%2522%257D&request_id=169319244016800182778579&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2~all~sobaiduend~default-1-40505615-null-null.142%5Ev93%5EchatgptT3_1&utm_term=PLSQL%E5%AF%BC%E5%85%A5excel%E6%95%B0%E6%8D%AE&spm=1018.2226.3001.4187