建站
咨詢
售后
建站咨詢
新聞中心
oracle中 大于0整數(shù)，且不能為空 應(yīng)該怎么寫
create table t(test number not null check(test 10))
創(chuàng)新新互聯(lián)，憑借10多年的做網(wǎng)站、網(wǎng)站設(shè)計經(jīng)驗，本著真心·誠心服務(wù)的企業(yè)理念服務(wù)于成都中小企業(yè)設(shè)計網(wǎng)站有成百上千家案例。做網(wǎng)站建設(shè)，選成都創(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ù)，兩張表結(jié)構(gòu)相同且分布在不同數(shù)據(jù)庫，具體場景如下，如何寫SQL語句？
異構(gòu)數(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語句
URL分享：http://www.ef60e0e.cn/article/phgsjc.html
