随笔分类 -  PROC&&数据库

摘要：1、insert into mc_message_his_0205 select * from mc_message_his_0201 where infoid='3392'; 复制一张表中的数据到另外一张表中2、update dmpush_message_info set sys_flag=0 where requestid='1354778645234690'; 更新表中字段3、delete from mc_file_num where requestid='93058029135'; 删除表中某条记录4、drop table XXXXX ; 阅读全文

摘要：1、SELECT k.imei,p.imsi,n.userid, m.report_type,m.report_time,k.result_code FROM MC_MESSAGE_INFO t,MC_MESSAGE_HIS_1221 k, MC_PHONE_INFO p, MC_MESSAGE_REPORT_1221 m,MC_DEVICETOKEN_MAP_p99999999 n WHERE t.infoid=k.infoid AND k.msgid=m.msgid AND k.imei=p.imei AND p.imei=n.imei AND t.requestid = ‘6766875 阅读全文

摘要：1、xxx.txt文本内容：XXXXXXXXXXXX XXXXX XXXXXXXXX X X X X XXXXXXXXXXXXXXXX XXXXX XXXXXXXXX X X X X XXXX2、XXX.ctl....................................INTO TABLE XXX_XXX_XXX -- 要插入记录的表Fields terminated by " " -- 数据中每行记录用 空格 分隔trailing nullcols --表的字段没有对应的值时允许为空( REQUEST_ID, REQUEST_TIME DATE 'YY 阅读全文

摘要：1、无返回值的存储过程create or replace procedure test_procisbegin insert into student values('003','mark',27,'s2012090503','15000778382'); commit;end test_proc;2、有单个数据值返回的存储过程create or replace procedure test_proc_return(temp_name in varchar2,temp_age out number) isage_1 number; 阅读全文

摘要：1、建立两张表进行存储过程深入学习create table STUDENT(USER_ID VARCHAR2(64),STUDENT_NAME VARCHAR2(64),AGE NUMBER(2),STUDENT_ID VARCHAR2(64),MOBILE VARCHAR2(64));create table STUDENT_INFO(STUDENT_ID VARCHAR2(64),HOBBY VARCHAR2(64),HEIGHT NUMBER(3,2),WEIGHT NUMBER(4,2));STUDENT_INFO表中STUDENT_ID字段关联STUDENT表2、往两个表中分别插入数 阅读全文

摘要：软件：PL/SQL Developer1、新建SQL窗口，输入如下：CREATE OR REPLACE PROCEDURE skeletonISBEGINNULL;END;保存为skeleton.sql；2、新建命令窗口，输入并执行：SQL> EXECUTE skeleton PL/SQL procedure successfully completed一个最简单的存储过程被创建成功；3、修改存储过程，打开skeleton.sql，修改保存结果如下：CREATE OR REPLACE PROCEDURE skeletonISBEGINDBMS_OUTPUT.PUT_LINE('H 阅读全文

摘要：1、ORA-02287:此处不允许序号 INSERT INTO ecm_interface_log k VALUES(SELECT seq_interface_log.nextval FROM dual,'%s',3,0,'成功',sysdate) 改成：INSERT INTO ecm_interface_log k VALUES(seq_interface_log.nextval,'%s',3,0,'成功',sysdate)2、UPDATE .....SET 与INSERT INTO.....VALUES INSERT INTO 阅读全文

摘要：1、按条件查找：select * from OA_GROUP_H t WHERE TAXIS between 1000 and 1015select * from OA_GROUP_H t WHERE TAXIS=‘1000’2、查找按降序进行排列：select * from OA_GROUP_H t order by TAXIS DESC3、插入数据：insert into OA_GROUP_H(GROUP_ID,NAME,PARENT_ID,TAXIS,GROUP_TYPE,GROUP_LEVEL,GROUP_TAG,FULL_NAME)SELECT * FROM OA_GROU... 阅读全文

摘要：EXEC SQL INCLUDE SQLCA;//通讯区 EXEC SQL WHENEVER SQLERROR GOTO sqlerr;//错误情况下，处理 //说明段，包括：类型说明语句 EXEC SQL BEGIN DECLARE SECTION; VARCHAR vcConnStr[512]; /*数据库连接串 user/pwd@sid*/ VARCHAR vcLinkName[64]; /*数据库连接别名*/ sql_context ctx = NULL; /*数据库连接上下文*/ EXEC SQL ... 阅读全文