oracle存储过程

最新推荐文章于 2024-06-05 16:17:52 发布

原创最新推荐文章于 2024-06-05 16:17:52 发布 · 316 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#oracle

oracle 专栏收录该内容

10 篇文章

订阅专栏

create or replace procedure SP_TEST(start_date in varchar2,end_date in varchar2,out_return in out sys_refcursor)
 is
 s varchar2(50);
 e varchar2(50);
 o sys_refcursor;
 i number;
begin
    s:=start_date;
    e:=end_date;

open  o for 
SELECT COUNT(ID_NO),USER_NAME,USER_LOGIN_NAME,DOCTOR_IN_CHECK
 FROM (
SELECT GHHZ.ID_NO,US.USER_NAME,US.USER_LOGIN_NAME,GHYS.DOCTOR_IN_CHECK FROM (
SELECT DISTINCT(V.ID_NO),T.PATIENT_ID--, V.NAME  --, U.USER_NAME, U.USER_LOGIN_NAME
  FROM JHOUTPAT_VISIT T
  LEFT JOIN PAT_MASTER_INDEX V
    ON T.PATIENT_ID = V.PATIENT_ID
  LEFT JOIN USERS U 
    ON T.DOCTOR_IN_CHECK = U.USER_ID
 WHERE T.VISIT_DATE >=
       TO_DATE(s, 'YYYY-MM-DD hh24:mi:ss')
   AND T.VISIT_DATE <=
       TO_DATE(e, 'YYYY-MM-DD hh24:mi:ss')
   AND T.VISIT_DEPT = '685'
--GROUP BY V.ID_NO,T.PATIENT_ID
) GHHZ 
LEFT JOIN JHOUTPAT_VISIT GHYS
ON GHHZ.PATIENT_ID=GHYS.PATIENT_ID
LEFT JOIN USERS US
ON US.USER_ID=GHYS.DOCTOR_IN_CHECK
WHERE GHYS.VISIT_DATE >=
       TO_DATE(s, 'YYYY-MM-DD hh24:mi:ss')
   AND GHYS.VISIT_DATE <=
       TO_DATE(e, 'YYYY-MM-DD hh24:mi:ss')
   AND GHYS.VISIT_DEPT = '685')
   GROUP BY USER_NAME,USER_LOGIN_NAME,DOCTOR_IN_CHECK;
    out_return:=o;
end SP_TEST;

基本语法：
1.基本结构
CREATE OR REPLACE PROCEDURE 存储过程名字
(
参数1 IN NUMBER,
参数2 IN NUMBER
) IS
变量1 INTEGER :=0;
变量2 DATE;
BEGIN
do something
END 存储过程名字

if 语句
IF V_TEST=1 THEN
BEGIN
do something
END;
END IF;
执行单个语序：
str_sql := ‘create global temporary table temp_table (
ID_NO number,
USER_NAME varchar2(20),
USER_LOGIN_NAME varchar2(20),
DOCTOR_IN_CHECK varchar2(20))’;
execute immediate str_sql;
while 循环：
while i<7 loop
i:=i+1;
END LOOP;

C:\Users\Liang>sqlplus system/123@TestDB

SQL*Plus: Release 11.1.0.7.0 - Production on 星期四 8月 3 15:51:32 2017

连接到:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> var r refcursor;
SQL> exec sql_test(:r);

PL/SQL 过程已成功完成。

SQL> exec sql_test(:r);

PL/SQL 过程已成功完成。

SQL> print r;