SQL Procedure Pass-Through Facility


The SQL Procedure Pass-Through Facility is an extension of the SQL procedure that enables you to send DBMS-specific statements to a DBMS and to retrieve DBMS data. You specify DBMS SQL syntax instead of SAS SQL syntax when you use the Pass-Through Facility. You can use Pass-Through Facility statements in a PROC SQL query or store them in a PROC SQL view.

The Pass-Through Facility consists of three statements and one component:

  • The CONNECT statement establishes a connection to the DBMS.

  • The EXECUTE statement sends dynamic, non-query DBMS-specific SQL statements to the DBMS.

  • The CONNECTION TO component in the FROM clause of a PROC SQL SELECT statement retrieves data directly from a DBMS.

  • The DISCONNECT statement terminates the connection to the DBMS.

The following Pass-Through Facility example sends a query to an ORACLE database for processing:

 proc sql;     connect to oracle as myconn (user=smith password=secret        path='myoracleserver');     select *        from connection to myconn           (select empid, lastname, firstname, salary              from employees              where salary>75000);     disconnect from myconn;  quit; 

The example uses the Pass-Through CONNECT statement to establish a connection with an ORACLE database with the specified values for the USER=, PASSWORD=, and PATH= arguments. The CONNECTION TO component in the FROM clause of the SELECT statement allows data to be retrieved from the database. The DBMS-specific statement that is sent to ORACLE is enclosed in parentheses. The DISCONNECT statement terminates the connection to ORACLE.

To store the same query in a PROC SQL view, use the CREATE VIEW statement:

 libname viewlib  'SAS-data-library'  ;  proc sql;     connect to oracle as myconn (user=smith password=secret        path='myoracleserver');     create view viewlib.salary as        select *           from connection to myconn              (select empid, lastname, firstname, salary                 from employees                 where salary>75000);     disconnect from myconn;  quit; 



SAS 9.1 Language Reference. Concepts
SAS 9.1 Language Reference Concepts
ISBN: 1590471989
EAN: 2147483647
Year: 2004
Pages: 255

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net