CREATE OR REPLACE FUNCTION get_num_of_employees (p_loc VARCHAR2, p_job VARCHAR2) 
RETURN NUMBER
IS
  v_query_str VARCHAR2(1000);
  v_num_of_employees NUMBER;
BEGIN
  v_query_str := 'begin SELECT COUNT(*) INTO :into_bind FROM emp_' 
                 || p_loc
                 || ' WHERE job = :bind_job; end;';
  EXECUTE IMMEDIATE v_query_str
    USING out v_num_of_employees, p_job;
  RETURN v_num_of_employees;
END;

May I know why we use USING keyword? Can anyone help me in converting this function to snowflake function?

CodePudding user response：

An example based on Scott's sample schema. I'll fetch number of clerks who work in Dallas.

SQL> SELECT * FROM dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS         --> this location
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

Table which contains employees from Dallas (i.e. department 20) only. Why? So that I could compose its name using dynamic SQL in function:

SQL> CREATE TABLE emp_dallas
  2  AS
  3     SELECT deptno, ename, job
  4       FROM emp
  5      WHERE deptno = 20;

Table created.

SQL> SELECT * FROM emp_dallas;

    DEPTNO ENAME      JOB
---------- ---------- ---------
        20 SMITH      CLERK      --> this
        20 JONES      MANAGER
        20 SCOTT      ANALYST
        20 ADAMS      CLERK      --> this
        20 FORD       ANALYST

Function, slightly rewritten so that it doesn't contain PL/SQL block in dynamic SQL (no need for that), fetches the result directly into a local variable and passes only IN bind value (p_job) to it. If you ask "why isn't p_loc passed as a bind value, well, it can't be - you have to compose table name using concatenation.

PL/SQL Dynamic SQL documentation explains most of it. It says that

If the dynamic SQL statement is a SELECT statement that can return at most one row, put out-bind variables (defines) in the INTO clause and in-bind variables in the USING clause.

That's exactly your case - select returns number of rows (as a single value, that's what count function returns) into local variable and passes p_job as a bind variable with the using clause.

SQL> CREATE OR REPLACE FUNCTION get_num_of_employees (p_loc  IN VARCHAR2,
  2                                                   p_job  IN VARCHAR2)
  3     RETURN NUMBER
  4  IS
  5     v_query_str         VARCHAR2 (1000);
  6     v_num_of_employees  NUMBER;
  7  BEGIN
  8     v_query_str :=
  9        'SELECT COUNT(*) FROM emp_' || p_loc || ' WHERE job = :bind_job';
 10
 11     EXECUTE IMMEDIATE v_query_str
 12        INTO v_num_of_employees
 13        USING p_job;
 14
 15     RETURN v_num_of_employees;
 16  END;
 17  /

Function created.

If we test it:

SQL> SELECT get_num_of_employees ('DALLAS', 'CLERK') FROM DUAL;

GET_NUM_OF_EMPLOYEES('DALLAS','CLERK')
--------------------------------------
                                     2

SQL>

Right; there are two clerks in Dallas.

As of rewriting it to Snowflake, sorry, I wouldn't know how to do that.