Oracle Stored procedure notation

Create or replace procedure Qianfeigl_jiaokuandy (

Cebenh varchar2, Kehuh varchar2, Hetongh varchar2, v_cur out query_pkg. Query_cur

)

Is

Sqlstr VARCHAR2 (3000);

Begin

sqlstr:= ' Select A.S_CH,A.S_CID,C.S_HETONGH,A.D_CAOZUOSJ from Zw_yingyez a left joins Kg_biaokaxx B on A.s_cid=b.s_cid Left joins Kg_zhanghuxx C on C.s_zhanghubh=b.s_zhanghubh where i_xiaozhang=0 ';

If CEBENH is not null and length (CEBENH) >0 Then

sqlstr:=sqlstr| | ' and a.s_ch= ' | | cebenh| | ";

End If;

If Hetongh is not null and length (Hetongh) >0 Then

sqlstr:=sqlstr| | ' and c.s_hetongh= ' | | hetongh| | ";

End If;

If Kehuh is not null and length (Kehuh) >0 Then

sqlstr:=sqlstr| | ' and a.s_cid= ' | | kehuh| | ";

End If;

sqlstr:=sqlstr| | ' ORDER by a.d_caozuosj Desc ';

OPEN v_cur for Sqlstr;

End Qianfeigl_jiaokuandy;

1. A stored procedure with a return data set written in Oracle is to be used with a cursor, and an OPEN v_cur for in front of the query statement

2, no if...else ..., can be used if...end if; form to judge

3, you can declare a variable of type VARCHAR2 in Oracle, use it to hold SQL statement, the middle can be string concatenation (with "| |" ）

Oracle Stored procedure notation