苦笔的博客

需要同时rename或drop多个table，在excel中拼sql比较麻烦，用procedure方便。0 创建/执行/删除procedurecreate or replace procedure procedure_name as ......set serveroutput on; --打印输出exec precedure_name; --执行存储过程drop procedure

重复值查找重复值select c1, c2, count(*) cnt from t group by (c1, c2) having count(*) > 1;

-- 查询角色, connect/resource/dba比较常见select * from dba_roles; -- 不存在user_roles和all_rolescreate user one identified by 11;-- 报错：user ONE lacks CREATE SESSION privilege; logon deniedsqlplus one/22 --...

格式化， to_char to_date-- sysdate: 2018-11-06 14:52:31to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') -- 2018-11-06 14:52:31to_char(sysdate, 'hh ddd dy day q w ww')hh 02小时ddd 一年中第310周dy 星期二day 星期...

初始登录-- 用sys用户登录-- ORA-28009: connection as SYS should be as SYSDBA or SYSOPERsqlplus / as sysdbasqlplus sys/pwd@orcl as sysdba-- sqlplus中登录conn / as sysdba-- 显示当前用户show user;创建用户(数据库)、用户授权...

--创建测试表create table test( id int;);-- 一个关键字，括号包含，逗号隔开，重命名列一次只能一列--添加多列alter table test add (c1 int, c2 int); -- 正确alter table test add column (c1 int, c2 int); --错误，标识符无效alter table test ad

--结构：(profile, resource_name, resource_type, limited)desc dba_profiles;--password默认180天过期select * from dba_profiles where profile='DEFAULT' and resource_name='PASSWORD_LIFE_TIME';--设置密码不会过期update db

1 取整和保留小数(floor, ceil, round, trunc)select floor(4.4) from dual; --4select floor('4.5') from dual; --4select ceil(4.4) from dual; --5select ceil('4.5') from dual; --5select round('555.555

1 重命名过程--oracle连接conn / as sysdba--创建用户并授权create user user_name identified by pwd;grant connect,resource to user_name;--尝试修改user_name，报错：选项缺失或无效alter user old_user rename to new_user identified by

1 从objects开始--都是dba/all/userselect * from dba_objects;select * from all_objects; --和dba_objects结构一致，数据少一些select * from user_objects; --比dba_objects少一个owner字段 --dba_object的结构 owner, object_n

1 case-when-then-else-end--创建测试表，插入测试数据create table test( c1 varchar(50), c2 varchar(50));insert into test values('1', 'a');insert into test values('2', 'b');insert into test values('3', '

1.介绍-- sqlldr代表sql loader，sqluldr代表sql unloader-- cmd输入sqlldr/sqluldr可查看帮助，讲的很明白2.sqlldr举例创建控制文件input.ctl-- 写法和mysql的load data命令比较像load data infile 'f:\work\data.csv' append into table table

-- 根据id(有唯一约束的字段)将table2的字段更新到table1update (select a.f1 a1, a.f2 a2, b.f1 b1, b.f2 b2 from table1 a, table2 b where a.id = b.id) set a1 = b1, a2 = b2;