ORACLE数据库的基本语法集锦

ORACLE数据库的基本语法集锦 -- 表

create table test (names varchar2(12), dates date,

num int, dou double); -- 视图

create or replace view vi_test as select * from test; -- 同义词

create or replace synonym aa for dbusrcard001.aa; -- 存储过程

create or replace produce dd(v_id in employee.empoy_id%type) as begin end dd; -- 函数

create or replace function ee(v_id in employee%rowtype) return varchar(15) is

var_test varchar2(15); begin

return var_test;

exception when others then end

-- 三种触发器的定义

create or replace trigger ff alter delete on test for each row declare begin

delete from test;

if sql%rowcount < 0 or sql%rowcount is null then rais_replaction_err(-20004,\错误\ end if end

create or replace trigger gg alter insert on test for each row declare begin

if :old.names = :new.names then raise_replaction_err(-2003,\编码重复\ end if end

create or replace trigger hh for update on test for each row declare begin

if updating then

if :old.names <> :new.names then

reaise_replaction_err(-2002,\关键字不能修改\ end if end if end -- 定义游标 declare cursor aa is

select names,num from test; begin

for bb in aa loop

if bb.names = \ end if end loop; end

-- 速度优化，前一语句不后一语句的速度快几十倍 select names,dates from test,b

where test.names = b.names(+) and b.names is null and

b.dates > date('2003-01-01','yyyy-mm-dd')

select names,dates from test

where names not in ( select names from b

where dates > to_date('2003-01-01','yyyy-mm-dd'))

-- 查找重复记录 select names,num from test

where rowid != (select max(rowid) from test b

where b.names = test.names and b.num = test.num)

-- 查找表TEST中时间最新的前10条记录

select * from (select * from test order by dates desc) where rownum < 11 -- 序列号的产生

create sequence row_id minvalue 1

maxvalue 9999999999999999999999 start with 1 increment by 1

insert into test values(row_id.nextval,....) 存储过程 1.基本结构

CREATE OR REPLACE PROCEDURE 存储过程名字 (

参数1 IN NUMBER, 参数2 IN NUMBER ) IS

变量1 INTEGER :=0; 变量2 DATE; BEGIN

END 存储过程名字

2.SELECT INTO STATEMENT

将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条 记录，否则抛出异常(如果没有记录抛出NO_DATA_FOUND) 例子： BEGIN

SELECT col1,col2 into 变量1,变量2 FROM typestruct where xxx; EXCEPTION

WHEN NO_DATA_FOUND THEN xxxx; END; ... 3.IF 判断

IF V_TEST=1 THEN BEGIN

do something END; END IF; 4.while 循环

WHILE V_TEST=1 LOOP BEGIN XXXX END; END LOOP; 5.变量赋值 V_TEST := 123; 6.用for in 使用cursor ... IS

CURSOR cur IS SELECT * FROM xxx;

本文来源：https://www.bwwdw.com/article/g2hv.html