SELECT SF_GET_CASE_SENSITIVE_FLAG();
--1 为大小写敏感,0 为大小写不敏感
sp_create_system_packages(1);
SELECT SF_GET_CASE_SENSITIVE_FLAG();
--1
create table test3(ID int,name char(20));
select dbms_metadata.get_ddl('TABLE','test3','SYSDBA')from dual;
--未找到对象或不允许查询系统定义的内部索引
select dbms_metadata.get_ddl('TABLE','TEST3','SYSDBA')from dual;
/*
CREATE TABLE "SYSDBA"."TEST3"
(
"ID" INT,
"NAME" CHAR(20)) STORAGE(ON "MAIN",CLUSTERBTR);
*/
----------------------------------
create table test6(id int,"name" chat(20));
select dbms_metadata.get_ddl('TABLE','TEST6','SYSDBA') from dual;
/*
CREATE TABLE "SYSDBA"."TEST6"
(
"ID" INT,
"name" CHAR(20)) STORAGE(ON "MAIN",CLUSTERBTR;
*/
create table "test1"(id int);
create table test1("id" int);
create table test2("id" int,id int);
select dbms_metadata.get_ddl('TABLE','test1','SYSDBA')from dual;
/*
CREATE TABLE "SYSDBA"."test1"
(
"ID" INT) STORAGE(ON "MAIN",CLUSTERBTR);
*/
select dbms_metadata.get_ddl('TABLE','TEST1','SYSDBA')from dual;
/*
CREATE TABLE "SYSDBA"."TEST1"
(
"id" INT) STORAGE(ON "MAIN",CLUSTERBTR);
*/
select dbms_metadata.get_ddl('TABLE','TEST2','SYSDBA')from dual;
/*
CREATE TABLE "SYSDBA"."TEST2"
(
"id" INT,
"id" INT) STORAGE(ON "MAIN",CLUSTERBTR);
*/
大小写敏感的数据库中,创建表时:
drop table test1;
create table test1(id int,name char(20));
--导出元数据
select dbms_metadata.get_ddl('TABLE','TEST1','SYSDBA')from dual;
/*
CREATE TABLE "SYSDBA"."TEST1"
(
"ID" INT,
"NAME" CHAR(20)) STORAGE(ON "MAIN",CLUSTERBTR);
*/
--进行 DML 操作
insert into sysdba.test1 values(1,'an');
update sysdba.test1 set id=100 where id=1;
delete from sysdba.test1 where id=100;
commit;
--4 条语句执行成功
insert into sysdba.test1("id","name") values(1,'an');
--无效的列名[id]
update sysdba.test1 set id=100 where "id"=1;
--无效的列名[id]
delete from sysdba.test1 where "id"=100;
--无效的列名[id]
大小写敏感的数据库中,DML 或 DDL 操作时:
sp_create_system_packages(1);
SELECT SF_GET_CASE_SENSITIVE_FLAG();
--0
create table test3(ID int,name char(20));
select dbms_metadata.get_ddl('TABLE','test3','SYSDBA')from dual;
/*
CREATE TABLE "SYSDBA"."test3"(
"ID" INT,"name" CHAR(20)) STORAGE(ON "MAIN",CLUSTERBTR);
*/
select dbms_metadata.get_ddl('TABLE','TEST3','SYSDBA')from dual;
/*
CREATE TABLE "SYSDBA"."test3"(
"ID" INT,
"name" CHAR(20)) STORAGE(ON "MAIN",CLUSTERBTR);
*/
create table test6(id int,"name" char(20));
select dbms_metadata.get_ddl('TABLE','TEST6','SYSDBA')from dual;
/*
CREATE TABLE "SYSDBA"."test3"("id" INT,"name" CHAR(20))
STORAGE(ON "MAIN",CLUSTERBTR);
*/
create table "test1"(id int);
--执行成功
create table test1("id" int);
--执行失败 对象[test1]已存在
create table TEST1("id" int);
--对象[test1]已存在
create table test2("id" int,id int);
--执行失败 列[id]已存在
select dbms_metadata.get_ddl('TABLE','test1','SYSDBA')from dual;
/*
CREATE TABLE "SYSDBA"."test1"
(
"id" INT) STORAGE(ON "MAIN", CLUSTERBTR);
*/
select dbms_metadata.get_ddl('TABLE','TEST1','SYSDBA')from dual;
/*
CREATE TABLE "SYSDBA"."test1"
(
"id" INT) STORAGE(ON "MAIN", CLUSTERBTR);
*/
大小写不敏感的数据库中,创建表时:
drop table test1;
create table test1(id int,name char(20));
--导出元数据
select dbms_metadata.get_ddl('TABLE','TEST1','SYSDBA')from dual;
/*
CREATE TABLE "SYSDBA"."test1"
(
"id" INT,
"name" CHAR(20)) STORAGE(ON "MAIN", CLUSTERBTR);
*/
--进行 DML 操作
insert into sysdba.test1 values(1,'an');
update sysdba.test1 set id=100 ehere id=1;
delete from sysdba.test1 where id=100;
commit;
--4 条语句执行成功
insert into sysdba.test1("id","name") values(1,'an');
--执行成功
update sysdba.test1 set id=100 where ID=1;
--执行成功
select * from TEST1 where NAME='AN';
/*
id name
100 an
*/
delete from sysdba.test1 where NAME='AN';
--执行成功
select * from TEST1;
-null
大小写不敏感的数据库中,DML 或 DDL 操作时:
文章
阅读量
获赞