plsqldeveloper - How can implement a procedure in ESQL (an internal procedure) from Oracle database -

i create store procedure in oracle db insert customer table

this code :

create or replace procedure thp.insert_customer(       p_custname          in       varchar2,      p_custlast          in       varchar2,      p_custfather        in       varchar2,      p_nationno          in       number,      p_birthday          in       varchar2,      p_birhtplace        in       varchar2,      p_email             in       varchar2,      p_custename         in       varchar2,      p_custelast         in       varchar2,      p_ownid             in       number,      p_custtypeid        in       number,      p_genderid          in       number,      p_billstid          in       number,      p_billspid          in       number,      p_idno              in       varchar2,         result out integer)      cnt number;  begin      result := 1;      cnt := 0;      select count(1) cnt thp.tbcustomer nationno=p_nationno ;       if cnt=1             commit;          result := 1;  --if  record exist       else         begin               insert tbcustomer(custid,custname,custlast,custfather,nationno,birthday,birhtplace,email,custename,custelast,ownid,custtypeid,genderid,billstid,billspid,idno)             values(custid_seq.nextval,p_custname,p_custlast,p_custfather,p_nationno,p_birthday,p_birhtplace,p_email,p_custename,p_custelast,p_ownid,p_custtypeid,p_genderid,p_billstid,p_billspid,p_idno);          commit;          result :=0;  --if insert new column          end;        end if; end insert_customer; / 

now want use procedure in esql , create directly in esql not call oracle database or other db

would please guide me bout it...

general comments, not answer ...

count(1) 

count(1) = count(*), standard form "count number of rows". count(1) has no advantages, best use count(*).

result := 1 

is redundant @ beginning of procedure

cnt := 0 

... redundant. variable name not meaningful, , might make people think of rude word, perhaps change rows_found.

prefixing arguments p_ not required. if use 1 of them in sql statement , need deconflict database object name prefix procedure name, have:

where nationno= insert_customer.nationno 

is nationno constrained unique in customer table? if not, use:

select count(*)   cnt   thp.tbcustomer  nationno=insert_customer.nationno ,        rownum = 1; 

(12g introduce limit sql syntax, way).

commiting in procedure held bad practice, procedure becomes part of longer business transaction (eg. inserting new customer , address) , commit should controlled application code.

upper case code harder read lower case -- that's why direction signs on motorways not uppercase.

the begin-end block insert not required @ all.

"birhtplace" spelled wrong.

so i'd suggest want convert esql actually:

create or replace procedure thp.insert_customer(     custname   in  varchar2,     custlast   in  varchar2,     custfather in  varchar2,     nationno   in  number  ,     birthday   in  varchar2,     birhtplace in  varchar2,     email      in  varchar2,     custename  in  varchar2,     custelast  in  varchar2,     ownid      in  number  ,     custtypeid in  number  ,     genderid   in  number  ,     billstid   in  number  ,     billspid   in  number  ,     idno       in  varchar2,     result     out integer) rows_found number; begin     select count(*)       rows_found       thp.tbcustomer      nationno=insert_customer.nationno;      if rows_found = 1         result := 1;     else         insert         tbcustomer(             custid    ,             custname  ,             custlast  ,             custfather,             nationno  ,             birthday  ,             birthplace,             email     ,             custename ,             custelast ,             ownid     ,             custtypeid,             genderid  ,             billstid  ,             billspid  ,             idno)         values(             custid_seq.nextval,             custname  ,             custlast  ,             custfather,             nationno  ,             birthday  ,             birthplace,             email     ,             custename ,             custelast ,             ownid     ,             custtypeid,             genderid  ,             billstid  ,             billspid  ,             idno);          result :=0;      end if; end insert_customer; /