一個PostgreSQL存儲過程的例子
需求:
給出如下條件進行批處理編排
- 開始日期時間
- 重復間隔(分鍾)
- 重復次數
要求在檔期內重復安排節目播出, 比如: 2003.01.01 08:00 開始每隔240分鍾播出一次, 一共播出100次
資料庫表格(CO_SCHEDULE)
------------------------------
N_PROGID INT
DT_STARTTIME TIMESTAMP
DT_ENDTIME TIMESTAMP
存儲過程的實現：
create table co_schele(n_progid int,dt_starttime timestamp,dt_endtime timestamp);
//創建函數：
create function add_program_time(int4,timestamp,int4,int4,int4) returns bool as '
declare
prog_id alias for $1;
ration_min alias for $3;
period_min alias for $4;
repeat_times alias for $5;
i int;
starttime timestamp;
ins_starttime timestamp;
ins_endtime timestamp;
begin
starttime :=$2;
i := 0;
while i<repeat_times loop
ins_starttime := starttime;
ins_endtime := timestamp_pl_span(ins_starttime,ration_min || ''mins'');
starttime := timestamp_pl_span(ins_starttime,period_min || ''mins'');
insert into co_schele values(prog_id,ins_starttime,ins_endtime);
i := i+1;
end loop;
if i<repeat_times then
return false;
else
return true;
end if;
end;
'language 'plpgsql';
//執行函數：
select add_program_time(1,'2002-10-20 0:0:0','5','60','5');
//查看結果：select * from co_schele;
n_progid | dt_starttime | dt_endtime
----------+------------------------+------------------------
1 | 2002-10-20 00:00:00+08 | 2002-10-20 00:05:00+08
1 | 2002-10-20 01:00:00+08 | 2002-10-20 01:05:00+08
1 | 2002-10-20 02:00:00+08 | 2002-10-20 02:05:00+08
1 | 2002-10-20 03:00:00+08 | 2002-10-20 03:05:00+08
1 | 2002-10-20 04:00:00+08 | 2002-10-20 04:05:00+08
ps:
1.資料庫一載入 plpgsql語言。如沒有，
su - postgres
createlang plpgsql dbname
2.至於返回類型為bool,是因為我不知道如何讓函數不返回值。等待改進。
『貳』 oracle 如何在包里創建一個簡單的函數和存儲過程
25、包
（1）定義包頭
語法：
CREATE [OR REPLACE] PACKAGE [schame_name.]<包名>
IS | AS
包描述
包描述：可以是變數、常量及數據類型定義和游標定義，也可以是存儲過程、函數定義和參數列表返回值類型。
例：
CREATE OR REPLACE PACKAGE employeePackage
AS
--類型定義
Type empRecType is record(
Empno number(4),
Salary number
)
--變數定義
p1 varchar2(20);
type t_departmentNo table is table of dept.deptno%type
index by binary_integer;
--游標定義
Cursor order_sal retrurn EmpRecType;
--聘用員工過程
Procere HireEmployee(p_EmpNo emp.empno%type);
End employeePackage;
例：
CREATE OR REPLACE PACKAGE scott.pkg_displayproct
IS
procere prc_getproctinfo
(
pid scott.proct.prodid%type,
pname out scott.proct.prodname%type,
ctname out scott.proct.CATEGORYID%type,
dscp out scott.proct.description%type
);
function fun_getstock
(
pid scott.proct.prodid%type
)
return number;
END pkg_displayproct;
（2）定義包體
語法：
CREATE OR REPLACE PACKAGE BODY [schame_name.]<包名>
IS | AS
包體描述；
END 包名;
包名：將要創建的包名稱，該名稱可以和包頭所在的包名相同，也可以不同。
包體描述：游標、存儲過程或者函數的定義
包體是可選的，如果在包頭中沒有聲明任何存儲過程或者函數，則包體就不存在，即使在包頭有變數、游標或者類型的聲明
（3）引用包中對象
BEGIN
[schame_name.][包名.]對象名;
END;
『叄』 用SQL語句創建存儲過程
--1、創建存儲過程--
if
exists
(select
*
from
sysobjects
where
name='info1')
drop
procere
info1
go
create
procere
info1
@sname
varcher（20）,
as
begin
declear
@xinxi
varcher（20）
set
@xinxi='select
學號，姓名，出身日期，系別(注，列名自己設置)
from
student
where
姓名=@sname'
print'@xinxi';
end
--調用存儲過程1--
exec
info1
@sname=姓名
後面的自己參考，可以寫出來
『肆』 存儲過程調用存儲過程的語法
執行存儲過程proc2，調用了存儲過程proc1
create prco proc1
as
begin
print '被執行的存儲過程proc1'
end
create proc proc2
as
begin
exec proc1
end
exec proc2
『伍』 在naviat直接調用存儲過程call OA.INS_USER(171201); 運行結果正確，在PHP中調用存儲過程結果不正確
關於這個問題，我不是怎麼很明白，你既然找不到解答，你去後盾逛一下吧！不管是小白，還是轉行在那裡都可以解決，任何問題在那裡都不是問題，不介意可以去看看/
『陸』 oracle資料庫的游標和存儲過程怎麼寫
--創建存儲
程
CREATE
OR
REPLACE
PROCEDURE
xxxxxxxxxxx_p
(
--參數IN表示輸入參數
OUT表示輸入參數
類型
使用任意Oracle
合
類型
is_ym
IN
CHAR
)
AS
--定義變數
vs_msg
VARCHAR2(4000);
--錯誤信息變數
vs_ym_beg
CHAR(6);
--起始月份
vs_ym_end
CHAR(6);
--終止月份
vs_ym_sn_beg
CHAR(6);
--同期起始月份
vs_ym_sn_end
CHAR(6);
--同期終止月份
--定義游標(簡單
說
遍歷
結
集)
CURSOR
cur_1
IS
SELECT
area_code,CMCODE,SUM(rmb_amt)/10000
rmb_amt_sn,SUM(usd_amt)/10000
usd_amt_sn
FROM
BGD_AREA_CM_M_BASE_T
WHERE
ym
>=
vs_ym_sn_beg
AND
ym
<=
vs_ym_sn_end
GROUP
BY
area_code,CMCODE;BEGIN
--用輸入參數給變數賦初值
用
Oralce
SUBSTR
TO_CHAR
ADD_MONTHS
TO_DATE
等
用
函數
vs_ym_beg
:=
SUBSTR(is_ym,1,6);
vs_ym_end
:=
SUBSTR(is_ym,7,6);
vs_ym_sn_beg
:=
TO_CHAR(ADD_MONTHS(TO_DATE(vs_ym_beg,'yyyymm'),
-12),'yyyymm');
vs_ym_sn_end
:=
TO_CHAR(ADD_MONTHS(TO_DATE(vs_ym_end,'yyyymm'),
-12),'yyyymm');
--先刪除表
特定條件
數據
DELETE
FROM
xxxxxxxxxxx_T
WHERE
ym
=
is_ym;
--
用內置
DBMS_OUTPUT
象
put_line
列印
影響
記錄行數
其
用
系統變數SQL%rowcount
DBMS_OUTPUT.put_line('del
月記錄='||SQL%rowcount||'條');
INSERT
INTO
xxxxxxxxxxx_T(area_code,ym,CMCODE,rmb_amt,usd_amt)
SELECT
area_code,is_ym,CMCODE,SUM(rmb_amt)/10000,SUM(usd_amt)/10000
FROM
BGD_AREA_CM_M_BASE_T
WHERE
ym
>=
vs_ym_beg
AND
ym
<=
vs_ym_end
GROUP
BY
area_code,CMCODE;
DBMS_OUTPUT.put_line('ins
月記錄='||SQL%rowcount||'條');
--遍歷游標處理
更新
表
遍歷游標
幾種
用for語句
其
比較直觀
種
FOR
rec
IN
cur_1
LOOP
UPDATE
xxxxxxxxxxx_T
SET
rmb_amt_sn
=
rec.rmb_amt_sn,usd_amt_sn
=
rec.usd_amt_sn
WHERE
area_code
=
rec.area_code
AND
CMCODE
=
rec.CMCODE
AND
ym
=
is_ym;
END
LOOP;
COMMIT;
--錯誤處理部
OTHERS表示除
聲明外
任意錯誤
SQLERRM
系統內置變數保存
前錯誤
詳細信息
EXCEPTION
WHEN
OTHERS
THEN
vs_msg
:=
'ERROR
IN
xxxxxxxxxxx_p('||is_ym||'):'||SUBSTR(SQLERRM,1,500);
ROLLBACK;
--
前錯誤記錄進
志表
INSERT
INTO
LOG_INFO(proc_name,error_info,op_date)
VALUES('xxxxxxxxxxx_p',vs_msg,SYSDATE);
COMMIT;
RETURN;
END;
『柒』 oracle存儲過程並且怎麼寫
存儲過程(procere)：是一個命名了的語句塊，可以有0個或多個參數
語法：
create or replace procere HelloWorld
as
begin
dbms_output.put_line('HelloWorld');
end;
調用存儲過程
* 命令調用 exec helloworld
* 語句塊調用
begin
helloworld;
end;
/
DELETE:
create or replace procere del_emp01
is
begin
delete from emp01 where empno=7369;
end;
exec del_emp01;
create or replace procere 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 procere 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 procere 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 procere 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;
帶有輸出參數的存儲過程不能使用命令直接調用
只能由語句塊或程序調用(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;
調用：
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用戶登錄
統計某個部門的員工的工資總和，員工的人數，平均工資，創建存儲過程
create or replace procere 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;
調用：
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;
//輸出參數
create or replace procere 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;
//既是輸入參數又是輸出參數
create or replace procere 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生成insert語句的存儲過程怎麼寫
你可以用oracle的UTL_FILE包,直接將文件生成到伺服器上。
下面給你個例子。測試環境windows2008 + oracle11g。
Blob，Clob類型不能導出，導出的文本適用於sqlserver.
createtableTest_Gsh(
aCharvarchar2(20),
bNumnumber,
cDatedate);
insertintoTest_Gsh(aChar,bNum,cDate)
select'這',1,sysdatefromal
unionall
select'是',2,sysdatefromal
unionall
select'測',3,sysdatefromal
unionall
select'試',4,sysdatefromal
unionall
select'UTL_FILE',5,sysdatefromal
unionall
select'的',to_Number(null),Nullfromal
unionall
select'例子',to_Number(null),Nullfromal;
--建立目錄
createdirectoryUTL_FILE_GAOas'c:';
--建立過程
createorreplaceprocereout_File_Testas
vFileNamevarchar2(50);
vFilePathvarchar2(30);
out_fileutl_file.file_type;
vBackupTimevarchar2(30);
begin
vBackupTime:=to_Char(sysdate,'yyyymmddhh24miss');
dbms_output.put_line(vBackupTime);
vFilePath:='UTL_FILE_GAO';
vFileName:='Test_Gsh'||vBackupTime||'.sql';
dbms_output.put_line(vFileName);
out_file:=utl_file.fopen(vFilePath,vFileName,'w');
utl_file.put_line(out_file,'--備份表:Test_Gsh');
utl_file.put_line(out_file,'--備份時間:'||vBackupTime);
forrin(select'insertintoTest_Gsh(aChar,bNum,cDate)values('||
casewhenaCharisnullthen'Null'elseChr(39)||aChar||Chr(39)end||','||
casewhenbNumisnullthen'Null'elseto_Char(bNum)end||','||
casewhencDateisnullthen'Null'else'cast('||Chr(39)||to_Char(cDate,'yyyymmddhh24miss')||Chr(39)||'asdateTime)'end||
');'insData
fromTest_Gsh)loop
utl_file.put_line(out_file,r.InsData);
endloop;
utl_file.FCLOSE(out_file);
exception
whenothersthen
dbms_output.put_line(sqlerrm);
utl_file.FCLOSE(out_file);
end;
/
『玖』 sql中執行存儲過程參數如何取表中查詢值
沒懂你要問什麼。
如果你你要給存儲過程傳遞參數，用IN 關鍵字，例如：
CREATE DEFINER=`vemdm`@`%` PROCEDURE `pr_insert_softlogday`(IN ins_DNname VARCHAR(20),IN sel_DNname VARCHAR(20),IN starttime VARCHAR(20),IN endtime VARCHAR(20))
BEGIN
SET @sqlcmd = CONCAT("INSERT INTO ",ins_DNname,"(softname,softversion,citycode,s_work,usetimes,s_day)
SELECT softname,softversion,citycode,workcustom s_work,SUM(usetimes) usetimes,create_time s_day FROM (
SELECT a.mac,a.citycode,a.workcustom,b.softname,b.softversion,b.usetimes,b.create_time FROM
(SELECT * FROM ",sel_DNname," a WHERE create_time BETWEEN '",starttime,"' AND '",endtime,"')b JOIN
(SELECT mac,citycode,workcustom FROM `terminal_unit`) a ON
a.mac = b.mac)c GROUP BY softname,softversion,citycode,workcustom");
PREPARE temp FROM @sqlcmd;
EXECUTE temp;
END$$
DELIMITER ;
這個我傳遞了3個參數。
如果你你要用存儲過程查結果，建議改為用視圖或者函數，存儲過程默認是沒有返回值的。所以你拿不到結果