oracle语法(oracle语法大全)

oracle基本语法

查询语句-select from table;

oracle语法(oracle语法大全)

oracle语法(oracle语法大全)

select from table where 条件1=数值 and 条件2=数值;

select from table where id in (select id from table);两表关联

select a.a,b.b,c.c from table1 a,table2 b,table3 c where a.id1=b.id2;

插入语句-insert into table (字段1，字段2，字段3,……)

values (数值1，数值2，数值3，……);

更新语句-update 表名 set 数值 where=id = 1;

添加列语句-alter table 表名

add (列名1 类型1，列名2 类型2，列名3 类型3,……);

查询随机20条记录-select from( select from emp order by dbms_random.value) where rownum <= 10;

修改列类型-alter table 表名

modify (列名1 类型1，列名2 类型2，列名3 类型3,……);

删除列语句-alter table 表名

drop column 列名s;

显示查询时间-set timing on;

删除表语句-deltet table 表名;

清空表数据-truncate table 表名;

修改列名 - ALTER TABLE emp RENAME COLUMN comm TO newa;

查询(无重复):select from table_name union

select from table_name;

查询(有重复):select from table_name union all

select from table_name;

集 查 询:select from table_name minus

select from table_name;

--------------------------------------------------------------------------------

运行脚本-start d:文件名.sql;

编辑脚本-edit d:文件名.sql;

另存为脚本-spool d:文件.sql;

select from emp;

spool off;

分页显示-set pagesize 页数;

行数显示-set linesize 行数;

创建用户-create user 用户名 identified by 密码;(需要SYS/SYSTEM权限才能建立用户)

赋予权限-grant resource to 用户名;(建表权限)

赋予查询权限-grant select on emp to 用户名;

赋予修改权限-grant update on emp to 用户名;

赋予所有访问权限-grant all on emp to 用户名;

--------------------------------------------------------

收回查询权限-revoke select on emp from 用户名;

传递权限-grant select on emp to 用户名2 with grant option;

账户锁定-

creata profile 名称 limit failed_login_attcmpts 输入次数限制 password_lock_time 锁定天数;

------------------------------DBA权限登录

alter user 想要锁定的用户名 profile 名称;

------------------------------DBA权限登录

解锁用户锁定-alter user 用户名 account unlock;

定期修改密码-create profile 名字 limit password_life_time 天数 password_grace_time 宽限天数;

切换用户-conn /密码;

更改密码-password 用户名;

删除用户-drop user 用户名 cascade(删除用户及用户建立的所有表);

查询同样结构两表中的不同数据-select from emp_tmp where empno not in(select empno from emp);

select from v$session;

select from v$version;

定义函数：

---------函数说明 函数是计算数字平方;

FUNCTION y2

(inx2 number)

return number is

Result number(2);

begin

Result := inx2inx2;

return(Result);

end y2;

---------函数说明 函数是输入汉字然后输出拼音;

FUNCTION HZ

(inputStr in VARCHAR2)

RETURN VARCHAR2 iS

outputStr varchar2(10);

BEGIN

SELECT c_spell INTO outputStr FROM BASE$CHINESE WHERE C_WORD = inputStr;

RETURN outputStr;

END hz;

----------函数说明 函数是计算累加自然月;

FUNCTION month

(inmonth number,

inaddmonth number)

return varchar2 is

Result varchar2(6);

begin

Result :=substr(to_char(add_months(to_date(inmonth,'yyyymm'),inaddmonth),'yyyymmdd'),1,6);

return(Result);

end month;

select to_char(add_months(trunc(sysdate),-1),'yyyymmdd') from dual;--取上个月的日期；

select to_char((sysdate-30),'yyyymmdd') from dual; ---去当前日期前30天日期；

ORACLE 随机数

DBMS_RANDOM.VALUE(low IN NUMBER,high IN NUMBER) RETURN NUMBER;

select round(dbms_random.value(x,x)) from dual;

ORACLE 取当前时间并按毫秒计算

select systimestamp from dual;

oracle存储过程基本语法

一：存储过程

创建存储过程，需要有CREATE PROCEDURE或CREATE ANY PROCEDURE的系统权限

删除存储过程，是过程的创建者或者拥有DROP ANY PROCEDURE系统权限

修改存储过程，则只能是过程的创建者或者拥有ALTER ANY PROCEDURE系统权限的人

执行(或调用)存储过程，是过程的创建者或是拥有EXECUTE ANY PROCEDURE系统权限的人或是被拥有者授予EXECUTE权限的人

1：语法

1）创建

CREATE[ORREPLACE]PROCEDURE存储过程名[(参数[IN|OUT|INOUT] 数据类型...)]

{AS|IS}

[说明部分]

BEGIN

可执行部分

[EXCEPTION

错误处理部分]

END[过程名];

说明：

OR REPLACE表示如果存在就覆盖存储过程

参数有三种形式：IN、OUT和IN OUT。则默认为IN。

关键字AS也可以写成IS，后跟过程的说明部分，可以在此定义过程的局部变量。

2）删除存储过程：

DROP PROCEDURE 存储过程名；

3）修改存储过程：

ALTER PROCEDURE 存储过程名 COMPILE；

4）执行存储过程

EXECUTE 模式名.存储过程名[(参数...)];

或

BEGIN

模式名.存储过程名[(参数...)];

END;

另外：参数可以是变量、常量或表达式

要其它用户执行存储过程须要给其它用户授权

GRANT EXECUTE ON 存储过程名 TO 用户名

2：参数说明

IN 定义一个输入参数变量，用于传递参数给存储过程

OUT 定义一个输出参数变量，用于从存储过程获取数据

IN OUT 定义一个输入、输出参数变量，兼有以上两者的功能

1）参数名 IN 数据类型 DEFAULT 值；

定义一个输入参数变量，用于传递参数给存储过程。

可以是常量、有值变量或表达式

DEFAULT 关键字为可选项，用来设定参数的默认值。如果在调用存储过程时不指明参数，则参数变量取默认值

在存储过程中，输入变量接收主程序传递的值，但不能对其进行赋值。

2）参数名 OUT 数据类型；

定义一个输出参数变量，用于从存储过程获取数据，即变量从存储过程中返回值给主程序。

在调用存储过程时，主程序的实际参数只能是一个变量，而不能是常量或表达式。

在存储过程中，参数变量只能被赋值而不能将其用于赋值，而且必须给输出变量至少赋值一次。

3）参数名 IN OUT 数据类型 DEFAULT 值；

定义一个输入、输出参数变量，兼有以上两者的功能。

在调用存储过程时，主程序的实际参数只能是一个变量，而不能是常量或表达式。

DEFAULT 关键字为可选项，用来设定参数的默认值。

在存储过程中，变量接收主程序传递的值，同时可以参加赋值运算，也可以对其进行赋值。在存储过程中必须给变量至少赋值一次。

补充：如果省略IN、OUT或IN OUT，则默认模式是IN。

调用它时参数个数与位置可以不一致，用以下形式调用：

EXECUTE CHANGE_SALARY(P_RAISE=>80,P_EMPNO=>7788); //=>运算符左侧是参数名，右侧是参数表达式

二：函数

创建函数，需要有CREATE PROCEDURE或CREATE ANY PROCEDURE的系统权限

删除函数，需要是函数的创建者或者是拥有DROP ANY PROCEDURE系统权限的人

修改函数，需要是函数的创建者或者拥有ALTER ANY PROCEDURE系统权限的人

执行函数，需要是函数的创建者或拥有EXECUTE ANY PROCEDURE系统权限的人

1：语法

1）创建：

CREATE[ORREPLACE]FUNCTION函数名[(参数[IN] 数据类型...)]

RETURN数据类型

{AS|IS}

[说明部分]

BEGIN

可执行部分

RETURN(表达式)

[EXCEPTION

错误处理部分]

END[函数名];

说明：

参数是可选的，但只能是IN类型(IN关键字可以省略)。

在定义部分的RETURN 数据类型，用来表示函数的数据类型，也就是返回值的类型，不可省略。

在可执行部分的RETURN(表达式)，用来生成函数的返回值，其表达式的类型应该和定义部分说明的函数返回值的数据类型一致。在函数的执行部分可以有多个RETURN语句，但只有一个RETURN语句会被执行，一旦执行了RETURN语句，则函数结束并返回调用环境。

2）删除

DROP FUNCTION 函数名；

3）修改

ALTER PROCEDURE 函数名 COMPILE；

4）执行

变量名:=函数名(...)

三：对存储过程 和 函数的查看（可以通过对数据字典【USER_SOURCE】的访问来查询存储过程或函数的有关信息）

1：查询某个存储过程序内容

select TEXT from user_source WHERE NAME='存储过程名';

2：查看数据字殿

DESCRIBE USER_SOURCE ; //命令行中

3：查看存储过程的参数

DESCRIBE say_hello; //后面是过程名

4：查看发生编辑错误

SHOW ERRORS ;

5：查询一个存储过程或函数是否是有效状态(即编译成功)

SELECT STATUS FROM USER_OBJECTS WHERE OBJECT_NAME='过程名';//注意大小写

VALID表示该存储过程有效(即通过编译)，INVALID表示存储过程无效或需要重新编译。它的状态会改变，这与它依赖外部表（表删除修改等作）有关系

6：查看存储过程与表的依赖关系

SELECT REFERENCED_NAME,REFERENCED_TYPE FROM USER_DEPENDENCIES WHERE NAME='SAY_HELLO';

说明

NAME为实体名，TYPE为实体类型，REFERENCED_OWNER为涉及到的实体拥有者账户，REFERENCED_NAME为涉及到的实体名，REFERENCED_TYPE 为涉及到的实体类型。

问题：

如果一个用户A被授予执行属于用户B的一个存储过程的权限，在用户B的存储过程中，访问到用户C的表，用户B被授予访问用户C的表的权限，但用户A没有被授予访问用户C表的权限，那么用户A调用用户B的存储过程是失败的还是成功的呢？是成功的。

Oracle sql语法

参考网上资料，整理Oracle sql语法：

DDL:

1、创建表

create tabletabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)

根据已有的表创建新表：

create table tab_new as select col1,col2… from tab_old

2、删除表

drop tabletabname

3、重命名表

说明：alter table表名rename to新表名

eg：alter table tablenamerename to newtablename

4、增加字段

说明：alter table表名add(字段名 字段类型 默认值 是否为空);

例：alter table tablename add (ID int);

eg：alter table tablenameadd (ID varchar2(30) default '空' not null);

5、修改字段

说明：alter table表名modify(字段名 字段类型 默认值 是否为空);

eg：alter table tablenamemodify (ID number(4));

6、重名字段

说明：alter table表名rename column列名to新列名 （其中：column是关键字）

eg：alter table tablenamerename column ID to newID;

7、删除字段

说明：alter table表名drop column字段名;

eg：alter table tablenamedrop column ID;

8、添加主键

alter tabletabnameadd primary key(col)

9、删除主键

alter tabletabnamedrop primary key(col)

10、创建索引

create[unique]indexidxname on tabname(col….)

11、删除索引

drop indexidxname

注：索引是不可更改的，想更改必须删除重新建。

12、创建视图

create viewviewnameas select 语句

13、删除视图

drop viewviewname

14.创建表空间

create tablespace schooltbs datafile ‘D:oracledatasourceschooltbs.dbf’ size 1000M autoextend on;

15.删除表空间

drop tablespace schooltbs[including contents and datafiles];

注：查询表空间和表空间存储路径

SELECT FROM dba_data_files WHERE tablespace_name = 表空间名;

DML:

1、数据查询

select<列名>from<表名> [where<查询条件表达试>] [order by<排序的列名>[asc或desc]]

2、插入数据

insert into表名values(所有列的值);

insert into test values(1,'zhangsan',20);

insert into表名(列)values(对应的值);

insert into test(id,name) values(2,'lisi');

3、更新数据

update表set列=新的值 [where条件] -->更新满足条件的记录

update test set name='zhangsan2' where name='zhangsan'

update表set列=新的值 -->更新所有的数据

update test set age =20;

4、删除数据

delete from表名where条件 -->删除满足条件的记录

delete from test where id = 1;

delete from test -->删除所有

commit; -->提交数据

rollback; -->回滚数据

delete方式可以恢复删除的数据，但是提交了，就没办法了 delete删除的时候，会记录日志 -->删除会很慢很慢

truncate table表名

删除所有数据，不会影响表结构，不会记录日志，数据不能恢复 -->删除很快

drop table表名

删除所有数据，包括表结构一并删除，不会记录日志，数据不能恢复-->删除很快

5、数据

表数据

insert intotable1 (selectfromtable2);

表结构

create tabletable1select fromtable2where1>1;

表结构和数据

create tabletable1select fromtable2;

指定字段

create tabletable1as selectid, namefromtable2where1>1;

oracle 基本语法

DECLARE

X NUMBER := 10;

Y VARCHAR(20) := '10';

BEGIN

FOR I IN 1 .. 10 LOOP

INSERT INTO T1_TEST2 VALUES (X, '数据');

X := X + I;

END LOOP;

--为什么加了y varchar(20):='10';就报错呢

END;

肯定不会报错，不过oracle中一般用varchar2，而不是varchar，当然这样用不会报错。

只不过insert后应该有commit才严谨。

oracle语法nvl，sign与round什么意思

这些都是oracle内置函数

nvl带两个参数 nvl(n1,n2):当n1为‘null’时 返回n2;

sign 带一个参数 sign(n),当n<0 ,返回-1，当n=0返回0，当n>0返回1；

round带一个参数（或者两个） round（n,m）/round(n)

当m>0四舍五入为m位小数，如果m小于0则四舍五入到小数点向左第m位(第二个参数m如果不为整数如2.1，则截取m整数部分)

这些函数你可以用这样的语句测试下，印象会更深刻

select 函数 from dual;