ITPub博客

1.表连接语法：join. . .on eg：select ename,dname from emp join dept on(emp.dno=dept.dno); 2.分页： eg：select ename,sal from ( select ename,sal,rownum r from ( select ename,sal from emp order by sal desc) where r<=10) where r>5 and r<=10; 3.建表语法： create table tablename( 列名1 列类型 完整性约束， 列名2 列类型 完整性约束， . . . ) 4.oracle中的数据类型： varchar2（n）n的最大值为4k； char（n）； number（p，s）整数或小数； date日期类型； long 最大值为2G； 5.select语句： select. . .f

E:photo>dir Volume in drive E is New Volume Volume Serial Number is F816-C39F Directory of E:photo2007-10-31 11:00 <DIR> .2007-10-31 11:00 <DIR> ..2007-08-13 16:19 1,212,681 flower.jpg 1 File(s) 1,212,681 bytes 2 Dir(s) 40,406,614,016 bytes free----------------------------------------------------SQL> conn a/aConnected.grant create any directory to a;grant create any library to a;create or replace directory pho

create table table_a(id number(5),name varchar2(10),score number(2));insert into table_a values(1,'aa',10);insert into table_a values(2,'bb',10);insert into table_a values(3,'cc',10);insert into table_a values(4,'dd',10);create table table_b(id number(5) unique,name varchar2(10),score number(2),batch_id number(10));insert into table_b values(1,'xx',90,1);insert into table_b values(2,'yy',80,1);insert into table_b values(3,'zz',60,2);insert into table_b values(4,'mm',70,2);commit;create bitmap in

写一函数，准确地判断字段是否含有汉字或者提取汉字等 从表里提取汉字, 需要考虑字符集, 不同的字符集汉字的编码有所不同这里以GB2312为例, 写一函数准确地从表里提取简体汉字.假设数据库字符集编码是GB2312, 环境变量(注册表或其它)的字符集也是GB2312编码并且保存到表里的汉字也都是GB2312编码的那么也就是汉字是双字节的，且简体汉字的编码范围是B0A1 - F7FE换算成10进制就是B0 A1 F7 FE176,161 - 247,254我们先看一下asciistr函数的定义Non-ASCII characters are converted to the form xxxx, where xxxx represents a UTF-16 code un

说明一点，带out参数的字定义函数不能用于 SQL语句，只能在PL/SQL程序里如下例子，返回两个3个数字set serveroutput ondeclare v_1 number; v_2 number; v_3 number; function my_f(p_1 in number,p_2 out number,p_3 out number) return number as v_id number(10):=p_1; begin p_2 := 200; p_3 := 300; return v_id; end;begin v_1:=my_f(100,v_2,v_3); dbms_output.put_line('v_1 is '||v_1); dbms_output.

Oralce的一些版本，当在函数里执行了min/max操作，并且这个min/MAX的参数是一个char型的字段将出错，这是一个BUG,BUG号Bug 4458790 我测试了win的9.2.0.7没有问题，但是10.2.0.1由问题SQL> select * from v$version;BANNER----------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProdPL/SQL Release 10.2.0.1.0 - ProductionCORE 10.2.0.1.0 ProductionTNS for 32-bit Windows: Version 10.2.0.1.0 - ProductionNLSRTL Version 10.2.0.1.0 - Producti

原始需求，有2表如下SQL> select * from mas;TO TOOLNAME-- ----------01 包裹02 信函03 挂号信04 中国速递05 EMS06 DHL6 rows selected.SQL> select * from putdt;SENDDT TO GNAME ST CITY-------- -- ---------- -- ----20070101 01 john 12 256620070101 04 david 16 309820070101 05 apple 18 209820070201 04 apple 16 307820070304 05 poly 13 100120070304 04 john 12 256620070101 01 james 12 88007 rows selected.要求输出如下格式的报表senddt toolid1 sum1 toolid2 sum2 toolid3 sum3 toolid4 sum4 toolid5 sum52007010

import java.io.*;import java.text.*;import java.lang.String;import java.util.*;import java.lang.*;import java.io.FileOutputStream;import java.sql.*;import oracle.sql.*;import oracle.jdbc.driver.*;public class zhu{ public static void main(String[] args) throws Exception { String sql = "select id from table where rownum<=20"; String fields = ""; DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); Connection conn = DriverManager.getConnection("jdbc

and we said...There are at least 2 ways to perform this sort of co-related update correctly. I'll show my preferred method (update a join) and then another method that'll work if you cannot put a unique constraint on LOOKUP(keyname) (which is needed for the join update). Here are the test tables: scott@ORA734.WORLD> create table name 2 ( keyname int, 3 columnName varchar2(25) 4 ) 5 / Table created. scott@ORA734.WORLD> create table lookup 2 ( keyname i

参考文档http://download-west.oracle.com/doc...g.htm#NETAG0153假设存在2 instance sales1 and sales2 数据库 salesclient 设置sales.us.acme.com= (DESCRIPTION= (LOAD_BALANCE=on)(FAILOVER=on)(ADDRESS=(PROTOCOL=tcp)(HOST=sales1-server)(PORT=1521))(ADDRESS=(PROTOCOL=tcp)(HOST=sales2-server)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=sales.us.acme.com)))sales1 上 tnsnames.ora内容listener_sales2= (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=sales2-server)(PORT=1521)))sales1 上spfile内容REMOTE_LISTENER=listener_s

MySQL版本5.0.13以上create table aa (id int);delimiter //create procedure test(v_sql varchar(200))beginset @sqltext:=concat('insert into aa ',v_sql);prepare stmt from @sqltext;execute stmt;DEALLOCATE PREPARE stmt;end;//call test('values (1)')//[@more@]

CC =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.210.220.1_vip)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = 192.210.220.2_vip)(PORT = 1521))(LOAD_BALANCE = yes)(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = cc))) [@more@]

翻遍了MYSQL手册都找不到说明哪个系统变量保存错误码以下异常处理可以捕获异常,但是不知道是什么异常,但是可以在log里找到当然,对于特定的SQL语句,也可以指定比如主键冲突,就rollback;DECLARE exit HANDLER FOR SQLSTATE '23000'delimiter // CREATE PROCEDURE TEST()BEGIN DECLARE exit HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND begin rollback; insert into bb values('error'); end; START TRANSACTION; INSERT INTO aa VALUES (1); INSERT INTO aa VALUES (2); COMMIT; END;//CALL test()//[@more@]M

1. 先看一下这个目录[oracle@ocean ~]$ pwd/home/oracle[oracle@ocean ~]$ ls -ltotal 8-rw-r--r-- 1 oracle orains 604 Dec 26 2006 bash2. 连接到test用户SQL> conn test/testConnected.3. 授于test用户文件读写和执行命令的权限SQL>exec dbms_java.grant_permission('TEST','SYS:java.io.FilePermission','<<ALL FILES>>','read,write,execute,delete');PL/SQL procedure successfully completed.SQL>exec dbms_java.grant_permission('TEST','java.lang.RuntimePermission','*','writeFileDescriptor' );PL/SQ

SQL> desc aa Name Null? Type ----------------------------------------- -------- ---------------------------- ID VARCHAR2(2)SQL> select * from aa;ID--123456789101111 rows selected.---------------建立PACKAGE---------------create or replace package pkg_testas type pagination is ref cursor; procedure page_test ( p_count in out pls_integer, p_page in out pagination, p_beg

TEST.DAT内容如下----------------13436002514|CQ|D|Jun 28 2006 9:54:16:420AM||Jun 28 2006 9:54:16:420AM|0||0.00|13436003898|CQ|D|May 17 2006 8:58:53:356PM||May 17 2006 8:58:53:356PM|0||0.00|13436005288|CQ|D|Mar 20 2007 9:24:33:796AM||Mar 29 2007 1:51:51:153PM|0||0.00|13436014844|CQ|U|May 2 2006 9:35:20:296AM||Sep 1 2006 9:12:51:830PM|0||0.00|TEST.CTL内容如下----------------由于DATE型没有毫秒表示，所以你的文本里得毫秒被去掉了LOAD DATAINFILE 'TEST.DAT'TRUNCATEINTO TABLE USERSFIELDS TERMINATED BY '|'

我的归纳1、裸设备定义：一块没有分区的硬盘，称为原始设备(RAW DEVICE)或者是一个分区，但是没有用EXT3,OCFS等文件系统格式化,称为原始分区(RAW PARTITION)以上两者都是裸设备2、裸设备的绑定有文件系统的分区是采用mount的方式挂载到某一个挂载点的(目录)而裸设备不能mount，只能绑定到/dev/raw/下的某一个设备名比如/dev/raw/raw13、裸设备的绑定方法有两种方法，这里介绍一种，另一种可以google搜索到修改/etc/sysconfig/rawdevices，添加以下内容,这里sdd1和sdd2是原始分区名或者原始设备(硬盘)名，raw1和raw2是/dev目录下的原始设备

By Alexander Barkov and Peter GulutzanMySQL version 5.1.5 has functions for searching and changing XML documents. This article has examples.Let's make a database and put two XML documents in it.CREATE TABLE x (doc VARCHAR(150));INSERT INTO x VALUES('<book><title>A guide to the SQL standard</title><author><initial>CJ</initial><surname>Date</surname></author></book>');INSERT INTO x VALUES('<book><title>SQL:1999</title>&l

[PHP]1、concat()函数 1.1 MySQL的concat函数可以连接一个或者多个字符串,如 mysql> select concat('10'); +--------------+ | concat('10') | +--------------+ | 10 | +--------------+ 1 row in set (0.00 sec) mysql> select concat('11','22','33'); +------------------------+ | concat('11','22','33') | +------------------------+ | 112233 | +------------------------+ 1 row in set (0.00 sec)

open(F1,">test.dat");open(F,"test.txt");while(<F>){ /^(S{1})/i; $hash_segment{$1}++; print F1;}close(F);close(F1);open(F,"test.dat");while(<F>){ /^(S{1})/i; if ($hash_segment{$1}<=2) { if (!exists($hash_new{$1})) { $filename='valid'.$1.'.dat'; open (FO,">$filename"); } $hash_new{$1}=''; print FO; } else { $hash_rest{$1}=''; }}close(F);undef %hash_new;undef %hash_segment;[@more@]sdfds