sql笔记

mysql
查询表结构：
SELECT COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME='TABLE_NAME'
查询建表语句:
SHOW CREATE TABLE TABLE_NAMEoracle

查询表结构：
SELECT A.COLUMN_NAME,A.DATA_TYPE,B.COMMENTS 
FROM USER_TAB_COLS A LEFT JOIN USER_COL_COMMENTS B 
ON A.TABLE_NAME=B.TABLE_NAME AND A.COLUMN_NAME=B.COLUMN_NAME
WHERE A.TABLE_NAME=''
查询建表语句:
SELECT DBMS_METADATA.GET_DDL('TABLE','TABLE_NAME') FROM DUAL
查询视图语句：
SELECT VIEW_NAME,TEXT FROM USER_VIEWS WHERE VIEW_NAME=''
查询索引：
SELECT * FROM ALL_INDEXES WHERE TABLE_NAME=''
查询索引列：
SELECT * FROM ALL_IND_COLUMNS WHERE TABLE_NAME=''
查询存储过程语句
SELECT NAME,TXT FROM DBA_SOURCE WHERE OWNER='' AND NAME=''
批量插入：
INSERT ALL
INTO TABLE_NAME VALUES(1,'NAME1')
INTO TABLE_NAME VALUES(2,'NAME2')
....
SELECT 1 FROM DUAL
MERGE合并修改插入语句：
MERGE INTO TABLE_A A
USING(SELECT ID FROM TABLE_B) B
ON (A.ID=B.ID)
WHEN MATCHED THEN UPDATE SET A.NAME=B.NAMEWHEN 
NOT MATCHED THEN INSERT (A.ID,A.NAME) VALUES (B.ID,B.NAME)
触发器：
例1：
CREATE OR REPLACE TRIGGER U_MAX_AGG
BEFORE INSERT ON USERS
FOR EACH ROW --行级触发器，每行受影响，触发器都执行
BEGIN
IF ADD_MONTHS(:NEW.BIRTHDAY,18*12)  >  SYSDATE --:new. 取到插入的值 new. old. oracle 关键字
THEN RAISE_APPLICATION_ERROR( - 20001 ,  '年龄不可小于于18岁' ); --返回到客户端的信息
END IF;
END;
例2：
CREATE OR REPLACE TRIGGER U_pw_def 
BEFORE INSERT ON USERS
FOR EACH ROW 
BEGIN :NEW.PASSWORD := 'ADMIN';
END;
异常处理：
建日志主表：
create table log_main(
logid char(32) primary key,
package_name varchar2(100),
procedure_name varchar2(100),
start_time date not null enable,
end_time date not null enable,
flag varchar2(1)
)；
日志附表：
create table log_sub(
logid varchar2(32) primary key,
datetime date,
error varchar2(620)
)；
包及包体
create or replace package model_one
as  
　　ERR_STAT varchar2(620);  
　　exe_id varchar2(32);
procedure pro_table_one(exe_date IN varchar2);
end;
create or replace package body model_one
as
　　procedure pro_table_one(exe_date IN varchar2)
　　as  
　　begin    
　　　　exe_id:=sys_guid();    
　　　　insert into log_main values(
　　　　　　exe_id,
　　　　　　'model_one',
　　　　　　'pro_table_one',
　　　　　　sysdate,
　　　　　　to_date('1991-01-01','YYYY-MM-DD'),
　　　　　　'N'
　　　　);    
　　　　COMMIT;     
　　　　INSERT INTO USERS(ID,USERNAME) VALUES(exe_id,exe_date);    
　　　　COMMIT;        
　　　　UPDATE LOG_MAIN A SET A.END_TIME=SYSDATE,A.FLAG='Y' WHERE A.LOGID=exe_id;    
　　　　COMMIT;     
　　　　EXCEPTION      
　　　　　　WHEN OTHERS THEN        
　　　　　　ROLLBACK;        
　　　　　　ERR_STAT:=SUBSTR(SQLERRM,1,200)||'----'||SUBSTR(DBMS_UTILITY.format_error_backtrace(),1,400);        
　　　　　　UPDATE LOG_MAIN A SET A.FLAG='E' WHERE A.LOGID=exe_id;        
　　　　　　INSERT INTO LOG_SUB VALUES(exe_id,SYSDATE,ERR_STAT);        
　　　　　　COMMIT;        
　　　　　　RAISE_APPLICATION_ERROR('-20001','过程执行失败');
　　end;
end;

日志定期清理
删除日志存储过程：

create or replace package package_clear
as
exe_id varchar2(32);
clear_time date;
ERR_STAT varchar2(620);
procedure pro_clear_log;
end;

create or replace package body package_clear
as
procedure pro_clear_log
as
begin

exe_id:=sys_guid();
clear_time:=add_months(sysdate,-6);
insert into log_main values(
exe_id,
'package_clear',
'pro_clear_log',
sysdate,
to_date('1991-01-01','YYYY-MM-DD'),
'N'
);
COMMIT;
DELETE FROM LOG_MAIN WHERE START_TIME<=clear_time;
DELETE FROM LOG_SUB A WHERE A.DATETIME<=clear_time;
COMMIT;
UPDATE LOG_MAIN A SET A.END_TIME=SYSDATE,A.FLAG='Y'WHERE A.LOGID=exe_id;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
ERR_STAT:=SUBSTR(SQLERRM,1,200)||'----'||SUBSTR(DBMS_UTILITY.format_error_backtrace(),1,400);
UPDATE LOG_MAIN A SET A.FLAG='E' WHERE A.LOGID=exe_id;
INSERT INTO LOG_SUB VALUES(exe_id,SYSDATE,ERR_STAT);
COMMIT;
RAISE_APPLICATION_ERROR('-20001','清理日志执行失败');
end;
end;

定时器：

declare
clear_job number;
begin
dbms_job.submit(
clear_job,/*自动生成JOB_ID*/
'package_clear.pro_clear_log;',/*需要执行的存储过程名称或SQL语句*/
sysdate,/*初次执行时间-立即执行*/
'TRUNC(LAST_DAY(SYSDATE)+1)' /*每月第一天晚12点*/
);
end;

查看定时器：

select * from dba_jobs;

执行定时器：

begin
dbms_job.run(23);
end;

每天午夜12点 ''TRUNC(SYSDATE + 1)''
每天早上8点30分 ''TRUNC(SYSDATE + 1) + （8*60+30）/(24*60)''
每星期二中午12点 ''NEXT_DAY(TRUNC(SYSDATE ), ''''TUESDAY'''' ) + 12/24''
每个月第一天的午夜12点 ''TRUNC(LAST_DAY(SYSDATE ) + 1)''
每个季度最后一天的晚上11点 ''TRUNC(ADD_MONTHS(SYSDATE + 2/24, 3 ), ''Q'' ) -1/24''
每星期六和日早上6点10分 ''TRUNC(LEAST(NEXT_DAY(SYSDATE, ''''SATURDAY"), NEXT_DAY(SYSDATE, "SUNDAY"))) + （6×60+10）/（24×60）''
每3秒钟执行一次 'sysdate+3/(24*60*60)'
每2分钟执行一次 'sysdate+2/(24*60)'
 
游标示例：

DECLARE
 CURSOR emp_users IS SELECT username,password,sex FROM users;
 v_empno users.username%TYPE;
 v_name users.password%TYPE;
 v_job users.sex%TYPE;
BEGIN
 OPEN emp_users;
 LOOP
  FETCH emp_users INTO v_empno,v_name,v_job;
  DBMS_OUTPUT.put_line(v_empno||v_name||v_job);
  EXIT WHEN emp_users%NOTFOUND;
 END LOOP;
 IF emp_users%ISOPEN THEN
 DBMS_OUTPUT.PUT_LINE('游标已打开');
 DBMS_OUTPUT.PUT_LINE('读取了'||emp_users%ROWCOUNT||'行');
 ELSE
 DBMS_OUTPUT.PUT_LINE('游标没有打开');
 END IF; 
 CLOSE emp_users;
END;
PL/SQL程序设计里使用DDL语句及系统控制语句：
EXECUTE IMMEDIATE 'truncate table users';