oracle且如何寫,oracle怎么寫sql語句

oracle中 大于0整數(shù)，且不能為空 應該怎么寫

create table t(test number not null check(test 10))

創(chuàng)新新互聯(lián)，憑借10多年的做網(wǎng)站、網(wǎng)站設計經(jīng)驗，本著真心·誠心服務的企業(yè)理念服務于成都中小企業(yè)設計網(wǎng)站有成百上千家案例。做網(wǎng)站建設，選成都創(chuàng)新互聯(lián)公司。

如果表已建好、

alter table t

alter column test number not null

然后

alter table t

add constraint check_t check (test0)

oracle同步數(shù)據(jù)，兩張表結構相同且分布在不同數(shù)據(jù)庫，具體場景如下，如何寫SQL語句？

異構數(shù)據(jù)庫的話，用db_link 和 Merge 吧

語法如下 :

MERGE INTO [your table-name] [rename your table here]

USING ( [write your query here] )[rename your query-sql and using just like a table]

ON ([conditional expression here] AND [...]...)

WHEN MATHED THEN [here you can execute some update sql or something else ]

WHEN NOT MATHED THEN [execute something else here ! ]

大概如下：

merge into B.a@db_link_name t2 using A.a t1 on (t1.xxxx = t2.xxxx)

when not matched then

insert values(t1.xxxx, t1.yyy, t1.zzz) l

oracle存儲過程并且怎么寫

存儲過程(procedure)：是一個命名了的語句塊，可以有0個或多個參數(shù)

語法：

create or replace procedure HelloWorld

as

begin

dbms_output.put_line('HelloWorld');

end;

調(diào)用存儲過程

* 命令調(diào)用 exec helloworld

* 語句塊調(diào)用

begin

helloworld;

end;

/

DELETE:

create or replace procedure del_emp01

is

begin

delete from emp01 where empno=7369;

end;

exec del_emp01;

create or replace procedure del_emp01(v_empno in emp01.empno%type)

is

begin

delete from emp01 wherer empno=v_empno;

end;

exec del_emp01(7521);

INSERT

create or replace procedure ins_emp01

(v_empno emp01.empno%type,v_ename emp01.ename%type)

as

begin

insert into emp01(empno,ename) values(v_empno,v_ename);

end;

exec ins_emp01(1000,'李四');

UPDATE

create or replace procedure upd_emp01

(v_empno emp01.empno%type,v_ename emp01.ename%type)

as

begin

update emp01 set ename=v_ename where empno=v_empno;

end;

exec upd_emp01(1000,'張三');

SELECT

create or replace procedure sel_emp01

(v_empno emp01.empno%type,v_emp01_data out emp01%rowtype)

as

begin

select * into v_emp01_data from emp where empno=v_empno;

end;

帶有輸出參數(shù)的存儲過程不能使用命令直接調(diào)用

只能由語句塊或程序調(diào)用(JAVA) ****************************************************************

declare

v_emp_data emp01%rowtype;

begin

sel_emp01(7499,v_emp_data);

dbms_output.put_line(v_emp_data.ename||' '||v_emp_data.sal);

end;

調(diào)用：

declare

v_emp_data emp01%rowtype;

begin

sel_emp01(7499,v_emp_data);

dbms_output.put_line(v_emp_data.ename||' '||v_emp_data.sal);

end;

使用scott用戶登錄

統(tǒng)計某個部門的員工的工資總和，員工的人數(shù)，平均工資，創(chuàng)建存儲過程

create or replace procedure deptcount

(v_deptno emp.deptno%type,v_sal_sum out number,v_recordes out number,v_avg_sal out number,errorMsg out varchar2)

is

begin

select sum(sal) into v_sal_sum from emp group by deptno having deptno=v_deptno;

select count(*) into v_recordes from emp group by deptno having deptno=v_deptno;

select avg(sal) into v_avg_sal from emp group by deptno having deptno=v_deptno;

exception

when no_data_found then

errorMsg:='沒有該部門';

end;

調(diào)用：

declare

v_sal_sum number;

v_recordes number;

v_avg_sal number(8,2);

v_errormsg varchar2(20);

begin

deptcount(90,v_sal_sum,v_recordes,v_avg_sal,v_errormsg);

dbms_output.put_line(v_sal_sum||' '||v_recordes||' '||v_avg_sal);

dbms_output.put_line(v_errormsg);

end;

//輸出參數(shù)

create or replace procedure my_pro(v_num in number,v_result out number)

is

v_temp number;

begin

v_temp:=0;

for i in 1..v_num

loop

v_temp:=v_temp+i;

end loop;

v_result:=v_temp;

end;

declare

v_recieve number;

begin

my_pro(100,v_recieve);

dbms_output.put_line(v_recieve);

end;

//既是輸入?yún)?shù)又是輸出參數(shù)

create or replace procedure my_pro1(v_i in out number)

is

v_j number;

begin

v_j:=30;

v_i:=v_i*v_j;

end;

declare

v_t number;

begin

v_t:=20;

my_pro1(v_t);

dbms_output.put_line(v_t);

end;

當前題目：oracle且如何寫,oracle怎么寫sql語句
文章來源：http://muchs.cn/article22/phgsjc.html

成都網(wǎng)站建設公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站排名、外貿(mào)建站、網(wǎng)站策劃、響應式網(wǎng)站、企業(yè)建站、服務器托管

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉載內(nèi)容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉載，或轉載時需注明來源： 創(chuàng)新互聯(lián)