Hướng dẫn call sql stored procedure from javascript - gọi thủ tục được lưu trữ sql từ javascript

Loại của

Bạn có thể tạo một điểm cuối là một trình bao bọc cho (các) quy trình được lưu trữ lấy tên thủ tục làm tham số, cũng như các tham số cho thủ tục.

Khi bạn có một cơ chế như vậy, bạn có thể tạo các điểm cuối hiển thị các thủ tục một cách tự động.

http://yourserver/services/yourprocname?prm1=val,prm2=val,etc

Nếu bạn cảm thấy thực sự tham vọng, bạn có thể thử SQL 2016 và trả lại JSON trực tiếp từ các thủ tục đó. Sau đó, bạn có thể làm tổ dữ liệu bằng cách sử dụng các nhóm con và trả lại JSON trong một tải trọng duy nhất. Không tuần tự hóa, không có đối tượng, chỉ cần đọc dữ liệu và trả về nó.

< 2016 you could put the results into a Dictionary and use NewtonSoft to serialize it. Assuming you are returning flat data you'd be good to go. Just use a reader and get the meta data from the column names for the key, and the value as object. NewtonSoft will convert that into JSON for you.

Nếu bạn đang trả lại phân cấp, bạn có thể (theo quy ước) tạo ra một loạt các vận động viên đưa người đọc và bơm nó vào một từ điển trong đó đối tượng là một từ điển khác, công cụ Newtonoft sẽ giúp bạn tuần tự hóa.

Hy vọng điều này sẽ giúp, chúng tôi đang sử dụng phương pháp này với năm 2016 và thật tuyệt khi có thể tạo một quy trình được lưu trữ và gọi nó mà không cần bất kỳ mã trung bình nào, triển khai, v.v. Nó chỉ hoạt động.

Hi vọng điêu nay co ich.

Các bước để gọi một thủ tục được lưu trữ tương tự như các bước để thực hiện truy vấn như sau:.

Ghi chú

Mặc dù bạn có thể đặt một biến phiên bên trong một quy trình được lưu trữ và để nó được đặt sau khi kết thúc thủ tục, nhưng Blonflake không khuyên bạn nên làm điều này.CALL (with Anonymous Procedure). Creating and calling an anonymous procedure does not require a role with CREATE PROCEDURE schema privileges.

Chủ sở hữu Quyền được lưu trữ quyền

Ví dụ sau đây cho thấy một thủ tục được lưu trữ quyền của chủ sở hữu.

Tạo quy trình được lưu trữ quyền của chủ sở hữu sử dụng biến phiên:

Gọi thủ tục (nó sẽ thất bại):

• Tạo quy trình được lưu trữ quyền của chủ sở hữu, cố gắng đặt biến phiên:

• Xử lý sự cố¶

• Kỹ thuật khắc phục sự cố chung là sử dụng khối thử/bắt JavaScript để bắt lỗi và hiển thị thông tin lỗi. Đối tượng lỗi chứa:

Mã lỗi.

• Thông báo lỗi.

• Trạng thái lỗi.

• CREATE OR REPLACE PROCEDURE right_bind(TIMESTAMP_VALUE VARCHAR)
  RETURNS BOOLEAN
  LANGUAGE JAVASCRIPT
  AS
  $$
  var cmd = "SELECT CURRENT_DATE() > TO_TIMESTAMP(:1, 'YYYY-MM-DD HH24:MI:SS')";
  var stmt = snowflake.createStatement(
            {
            sqlText: cmd,
            binds: [TIMESTAMP_VALUE]
            }
            );
  var result1 = stmt.execute();
  result1.next();
  return result1.getColumnValue(1);
  $$
  ;
  

  3, giữ kết quả của một truy vấn (ví dụ: các hàng dữ liệu được truy xuất cho một câu lệnh Chọn).

• CREATE OR REPLACE PROCEDURE right_bind(TIMESTAMP_VALUE VARCHAR)
  RETURNS BOOLEAN
  LANGUAGE JAVASCRIPT
  AS
  $$
  var cmd = "SELECT CURRENT_DATE() > TO_TIMESTAMP(:1, 'YYYY-MM-DD HH24:MI:SS')";
  var stmt = snowflake.createStatement(
            {
            sqlText: cmd,
            binds: [TIMESTAMP_VALUE]
            }
            );
  var result1 = stmt.execute();
  result1.next();
  return result1.getColumnValue(1);
  $$
  ;
  

  4, là một phần mở rộng của ngày JavaScript (với các phương thức bổ sung) và đóng vai trò là loại trả lại cho các loại dữ liệu SQL SQL SQL Timestamp_ltz, Timestamp_ntz và Timestamp_tz.

Các đối tượng này được mô tả chi tiết trong API quy trình lưu trữ JavaScript.JavaScript Stored Procedures API.

Một quy trình được lưu trữ điển hình chứa mã tương tự như mã giả sau:

var my_sql_command1 = "delete from history_table where event_year < 2016";
var statement1 = snowflake.createStatement(my_sql_command1);
statement1.execute();

var my_sql_command2 = "delete from log_table where event_year < 2016";
var statement2 = snowflake.createStatement(my_sql_command2);
statement2.execute();

Mã này sử dụng một đối tượng có tên

CREATE OR REPLACE PROCEDURE right_bind(TIMESTAMP_VALUE VARCHAR)
RETURNS BOOLEAN
LANGUAGE JAVASCRIPT
AS
$$
var cmd = "SELECT CURRENT_DATE() > TO_TIMESTAMP(:1, 'YYYY-MM-DD HH24:MI:SS')";
var stmt = snowflake.createStatement(
          {
          sqlText: cmd,
          binds: [TIMESTAMP_VALUE]
          }
          );
var result1 = stmt.execute();
result1.next();
return result1.getColumnValue(1);
$$
;

Các biến khác (ví dụ:

CREATE OR REPLACE PROCEDURE right_bind(TIMESTAMP_VALUE VARCHAR)
RETURNS BOOLEAN
LANGUAGE JAVASCRIPT
AS
$$
var cmd = "SELECT CURRENT_DATE() > TO_TIMESTAMP(:1, 'YYYY-MM-DD HH24:MI:SS')";
var stmt = snowflake.createStatement(
          {
          sqlText: cmd,
          binds: [TIMESTAMP_VALUE]
          }
          );
var result1 = stmt.execute();
result1.next();
return result1.getColumnValue(1);
$$
;

CREATE OR REPLACE PROCEDURE right_bind(TIMESTAMP_VALUE VARCHAR)
RETURNS BOOLEAN
LANGUAGE JAVASCRIPT
AS
$$
var cmd = "SELECT CURRENT_DATE() > TO_TIMESTAMP(:1, 'YYYY-MM-DD HH24:MI:SS')";
var stmt = snowflake.createStatement(
          {
          sqlText: cmd,
          binds: [TIMESTAMP_VALUE]
          }
          );
var result1 = stmt.execute();
result1.next();
return result1.getColumnValue(1);
$$
;

Như được hiển thị trong mẫu mã ở trên, đối tượng

CREATE OR REPLACE PROCEDURE right_bind(TIMESTAMP_VALUE VARCHAR)
RETURNS BOOLEAN
LANGUAGE JAVASCRIPT
AS
$$
var cmd = "SELECT CURRENT_DATE() > TO_TIMESTAMP(:1, 'YYYY-MM-DD HH24:MI:SS')";
var stmt = snowflake.createStatement(
          {
          sqlText: cmd,
          binds: [TIMESTAMP_VALUE]
          }
          );
var result1 = stmt.execute();
result1.next();
return result1.getColumnValue(1);
$$
;

CREATE OR REPLACE PROCEDURE right_bind(TIMESTAMP_VALUE VARCHAR)
RETURNS BOOLEAN
LANGUAGE JAVASCRIPT
AS
$$
var cmd = "SELECT CURRENT_DATE() > TO_TIMESTAMP(:1, 'YYYY-MM-DD HH24:MI:SS')";
var stmt = snowflake.createStatement(
          {
          sqlText: cmd,
          binds: [TIMESTAMP_VALUE]
          }
          );
var result1 = stmt.execute();
result1.next();
return result1.getColumnValue(1);
$$
;

Dưới đây, một ví dụ lấy lại

CREATE OR REPLACE PROCEDURE right_bind(TIMESTAMP_VALUE VARCHAR)
RETURNS BOOLEAN
LANGUAGE JAVASCRIPT
AS
$$
var cmd = "SELECT CURRENT_DATE() > TO_TIMESTAMP(:1, 'YYYY-MM-DD HH24:MI:SS')";
var stmt = snowflake.createStatement(
          {
          sqlText: cmd,
          binds: [TIMESTAMP_VALUE]
          }
          );
var result1 = stmt.execute();
result1.next();
return result1.getColumnValue(1);
$$
;

create or replace procedure read_result_set()
  returns float not null
  language javascript
  as     
  $$  
    var my_sql_command = "select * from table1";
    var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
    var result_set1 = statement1.execute();
    // Loop through the results, processing one row at a time... 
    while (result_set1.next())  {
       var column1 = result_set1.getColumnValue(1);
       var column2 = result_set1.getColumnValue(2);
       // Do something with the retrieved values...
       }
  return 0.0; // Replace with something more useful.
  $$
  ;

Phần ví dụ (ở cuối chủ đề này) cung cấp các ví dụ bổ sung rằng thực hiện từng đối tượng và nhiều phương pháp, trong quy trình được lưu trữ API JavaScript.Examples section (at the end of this topic) provides additional examples that exercise each of the objects, and many of the methods, in the stored procedure JavaScript API.

Ánh xạ kiểu dữ liệu SQL và JavaScript

Khi gọi, sử dụng và nhận lại các giá trị từ các quy trình được lưu trữ, bạn thường cần chuyển đổi từ kiểu dữ liệu SQL của Snowflake sang kiểu dữ liệu JavaScript hoặc ngược lại.

Chuyển đổi SQL sang JavaScript có thể xảy ra khi:

• Gọi một thủ tục được lưu trữ với một đối số. Đối số là kiểu dữ liệu SQL; Khi nó được lưu trữ bên trong một biến JavaScript bên trong quy trình được lưu trữ, nó phải được chuyển đổi.

• Khi lấy một giá trị từ một đối tượng kết quả vào một biến JavaScript. Kết quả giữ giá trị dưới dạng kiểu dữ liệu SQL và biến JavaScript phải lưu trữ giá trị là một trong các loại dữ liệu JavaScript.

Chuyển đổi JavaScript sang SQL có thể xảy ra khi:

• Trả lại một giá trị từ thủ tục được lưu trữ. Câu lệnh

  create or replace procedure read_result_set()
    returns float not null
    language javascript
    as     
    $$  
      var my_sql_command = "select * from table1";
      var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
      var result_set1 = statement1.execute();
      // Loop through the results, processing one row at a time... 
      while (result_set1.next())  {
         var column1 = result_set1.getColumnValue(1);
         var column2 = result_set1.getColumnValue(2);
         // Do something with the retrieved values...
         }
    return 0.0; // Replace with something more useful.
    $$
    ;
  

  01 thường chứa biến JavaScript phải được chuyển đổi thành kiểu dữ liệu SQL.

• Khi tự động xây dựng câu lệnh SQL sử dụng giá trị trong biến JavaScript.

• Khi ràng buộc một biến JavaScript giá trị với một câu lệnh đã chuẩn bị.

Các phần dưới đây giải thích cách dữ liệu được chuyển đổi từ SQL sang JavaScript hoặc từ JavaScript sang SQL.

Chuyển đổi từ SQL sang JavaScript¶

Bảng sau đây cho thấy các loại dữ liệu SQL của Snowflake và các loại dữ liệu JavaScript tương ứng:

create or replace procedure read_result_set()
  returns float not null
  language javascript
  as     
  $$  
    var my_sql_command = "select * from table1";
    var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
    var result_set1 = statement1.execute();
    // Loop through the results, processing one row at a time... 
    while (result_set1.next())  {
       var column1 = result_set1.getColumnValue(1);
       var column2 = result_set1.getColumnValue(2);
       // Do something with the retrieved values...
       }
  return 0.0; // Replace with something more useful.
  $$
  ;

create or replace procedure read_result_set()
  returns float not null
  language javascript
  as     
  $$  
    var my_sql_command = "select * from table1";
    var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
    var result_set1 = statement1.execute();
    // Loop through the results, processing one row at a time... 
    while (result_set1.next())  {
       var column1 = result_set1.getColumnValue(1);
       var column2 = result_set1.getColumnValue(2);
       // Do something with the retrieved values...
       }
  return 0.0; // Replace with something more useful.
  $$
  ;

create or replace procedure read_result_set()
  returns float not null
  language javascript
  as     
  $$  
    var my_sql_command = "select * from table1";
    var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
    var result_set1 = statement1.execute();
    // Loop through the results, processing one row at a time... 
    while (result_set1.next())  {
       var column1 = result_set1.getColumnValue(1);
       var column2 = result_set1.getColumnValue(2);
       // Do something with the retrieved values...
       }
  return 0.0; // Replace with something more useful.
  $$
  ;

create or replace procedure read_result_set()
  returns float not null
  language javascript
  as     
  $$  
    var my_sql_command = "select * from table1";
    var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
    var result_set1 = statement1.execute();
    // Loop through the results, processing one row at a time... 
    while (result_set1.next())  {
       var column1 = result_set1.getColumnValue(1);
       var column2 = result_set1.getColumnValue(2);
       // Do something with the retrieved values...
       }
  return 0.0; // Replace with something more useful.
  $$
  ;

create or replace procedure read_result_set()
  returns float not null
  language javascript
  as     
  $$  
    var my_sql_command = "select * from table1";
    var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
    var result_set1 = statement1.execute();
    // Loop through the results, processing one row at a time... 
    while (result_set1.next())  {
       var column1 = result_set1.getColumnValue(1);
       var column2 = result_set1.getColumnValue(2);
       // Do something with the retrieved values...
       }
  return 0.0; // Replace with something more useful.
  $$
  ;

create or replace procedure read_result_set()
  returns float not null
  language javascript
  as     
  $$  
    var my_sql_command = "select * from table1";
    var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
    var result_set1 = statement1.execute();
    // Loop through the results, processing one row at a time... 
    while (result_set1.next())  {
       var column1 = result_set1.getColumnValue(1);
       var column2 = result_set1.getColumnValue(2);
       // Do something with the retrieved values...
       }
  return 0.0; // Replace with something more useful.
  $$
  ;

CREATE OR REPLACE PROCEDURE right_bind(TIMESTAMP_VALUE VARCHAR)
RETURNS BOOLEAN
LANGUAGE JAVASCRIPT
AS
$$
var cmd = "SELECT CURRENT_DATE() > TO_TIMESTAMP(:1, 'YYYY-MM-DD HH24:MI:SS')";
var stmt = snowflake.createStatement(
          {
          sqlText: cmd,
          binds: [TIMESTAMP_VALUE]
          }
          );
var result1 = stmt.execute();
result1.next();
return result1.getColumnValue(1);
$$
;

create or replace procedure read_result_set()
  returns float not null
  language javascript
  as     
  $$  
    var my_sql_command = "select * from table1";
    var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
    var result_set1 = statement1.execute();
    // Loop through the results, processing one row at a time... 
    while (result_set1.next())  {
       var column1 = result_set1.getColumnValue(1);
       var column2 = result_set1.getColumnValue(2);
       // Do something with the retrieved values...
       }
  return 0.0; // Replace with something more useful.
  $$
  ;

CREATE OR REPLACE PROCEDURE right_bind(TIMESTAMP_VALUE VARCHAR)
RETURNS BOOLEAN
LANGUAGE JAVASCRIPT
AS
$$
var cmd = "SELECT CURRENT_DATE() > TO_TIMESTAMP(:1, 'YYYY-MM-DD HH24:MI:SS')";
var stmt = snowflake.createStatement(
          {
          sqlText: cmd,
          binds: [TIMESTAMP_VALUE]
          }
          );
var result1 = stmt.execute();
result1.next();
return result1.getColumnValue(1);
$$
;

CREATE OR REPLACE PROCEDURE right_bind(TIMESTAMP_VALUE VARCHAR)
RETURNS BOOLEAN
LANGUAGE JAVASCRIPT
AS
$$
var cmd = "SELECT CURRENT_DATE() > TO_TIMESTAMP(:1, 'YYYY-MM-DD HH24:MI:SS')";
var stmt = snowflake.createStatement(
          {
          sqlText: cmd,
          binds: [TIMESTAMP_VALUE]
          }
          );
var result1 = stmt.execute();
result1.next();
return result1.getColumnValue(1);
$$
;

CREATE OR REPLACE PROCEDURE right_bind(TIMESTAMP_VALUE VARCHAR)
RETURNS BOOLEAN
LANGUAGE JAVASCRIPT
AS
$$
var cmd = "SELECT CURRENT_DATE() > TO_TIMESTAMP(:1, 'YYYY-MM-DD HH24:MI:SS')";
var stmt = snowflake.createStatement(
          {
          sqlText: cmd,
          binds: [TIMESTAMP_VALUE]
          }
          );
var result1 = stmt.execute();
result1.next();
return result1.getColumnValue(1);
$$
;

create or replace procedure read_result_set()
  returns float not null
  language javascript
  as     
  $$  
    var my_sql_command = "select * from table1";
    var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
    var result_set1 = statement1.execute();
    // Loop through the results, processing one row at a time... 
    while (result_set1.next())  {
       var column1 = result_set1.getColumnValue(1);
       var column2 = result_set1.getColumnValue(2);
       // Do something with the retrieved values...
       }
  return 0.0; // Replace with something more useful.
  $$
  ;

create or replace procedure read_result_set()
  returns float not null
  language javascript
  as     
  $$  
    var my_sql_command = "select * from table1";
    var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
    var result_set1 = statement1.execute();
    // Loop through the results, processing one row at a time... 
    while (result_set1.next())  {
       var column1 = result_set1.getColumnValue(1);
       var column2 = result_set1.getColumnValue(2);
       // Do something with the retrieved values...
       }
  return 0.0; // Replace with something more useful.
  $$
  ;

Ví dụ: nếu câu lệnh SQL của bạn chọn một cột biến thể từ bảng Bông tuyết, thì khi bạn sao chép giá trị từ

CREATE OR REPLACE PROCEDURE right_bind(TIMESTAMP_VALUE VARCHAR)
RETURNS BOOLEAN
LANGUAGE JAVASCRIPT
AS
$$
var cmd = "SELECT CURRENT_DATE() > TO_TIMESTAMP(:1, 'YYYY-MM-DD HH24:MI:SS')";
var stmt = snowflake.createStatement(
          {
          sqlText: cmd,
          binds: [TIMESTAMP_VALUE]
          }
          );
var result1 = stmt.execute();
result1.next();
return result1.getColumnValue(1);
$$
;

create or replace procedure read_result_set()
  returns float not null
  language javascript
  as     
  $$  
    var my_sql_command = "select * from table1";
    var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
    var result_set1 = statement1.execute();
    // Loop through the results, processing one row at a time... 
    while (result_set1.next())  {
       var column1 = result_set1.getColumnValue(1);
       var column2 = result_set1.getColumnValue(2);
       // Do something with the retrieved values...
       }
  return 0.0; // Replace with something more useful.
  $$
  ;

Không phải tất cả các loại dữ liệu SQL của Snowflake đều có loại dữ liệu JavaScript tương ứng. Ví dụ: JavaScript không hỗ trợ trực tiếp các loại dữ liệu số nguyên hoặc số. Trong những trường hợp này, bạn nên chuyển đổi kiểu dữ liệu SQL thành loại dữ liệu thay thế thích hợp. Ví dụ: bạn có thể chuyển đổi số nguyên SQL thành phao SQL, sau đó có thể được chuyển đổi thành giá trị JavaScript của loại dữ liệu

create or replace procedure read_result_set()
  returns float not null
  language javascript
  as     
  $$  
    var my_sql_command = "select * from table1";
    var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
    var result_set1 = statement1.execute();
    // Loop through the results, processing one row at a time... 
    while (result_set1.next())  {
       var column1 = result_set1.getColumnValue(1);
       var column2 = result_set1.getColumnValue(2);
       // Do something with the retrieved values...
       }
  return 0.0; // Replace with something more useful.
  $$
  ;

Bảng bên dưới hiển thị các chuyển đổi thích hợp cho các loại dữ liệu SQL không tương thích:

SỰ VẬT

Chuyển đổi từ JavaScript sang SQL¶

Khi trả về các giá trị

Nếu không có diễn viên hợp lệ cho chuyển đổi tồn tại, thì xảy ra lỗi.

Khi các giá trị ràng buộc

Khi bạn liên kết các biến JavaScript với các câu lệnh SQL, Snowflake đã chuyển đổi từ các loại dữ liệu JavaScript thành các loại dữ liệu SQL. Bạn có thể liên kết các biến của các loại dữ liệu JavaScript sau:

• number.

• string.

• Sfdate. .JavaScript Stored Procedures API.)

Để biết thêm thông tin về ràng buộc, bao gồm một số ví dụ, xem các biến ràng buộc.Binding Variables.

Mẹo chung¶

Tiếp tục dòng

Các câu lệnh SQL có thể khá dài, và không phải lúc nào cũng thực tế để phù hợp với chúng trên một dòng. JavaScript coi một dòng mới là kết thúc của một tuyên bố. Nếu bạn muốn chia một câu lệnh SQL dài trên nhiều dòng, bạn có thể sử dụng các kỹ thuật JavaScript thông thường để xử lý các chuỗi dài, bao gồm:

• Đặt một dấu gạch chéo ngược (ký tự tiếp tục dòng) ngay trước khi kết thúc dòng. Ví dụ:

  var sql_command = "SELECT * \
                         FROM table1;";
  

• Sử dụng Backticks (Mặt sau đơn) thay vì Double Trích dẫn xung quanh Chuỗi. Ví dụ:

  var sql_command = `SELECT *
                         FROM table1;`;
  

• Tích lũy chuỗi. Ví dụ:

  var sql_command = "SELECT col1, col2"
  sql_command += "     FROM table1"
  sql_command += "     WHERE col1 >= 100"
  sql_command += "     ORDER BY col2;"
  

Cân nhắc thủ tục lưu trữ JavaScript

Phạm vi số JavaScript

Phạm vi cho các số có chính xác còn nguyên vẹn là từ

-(2^53 -1)

đến

(2^53 -1)

Phạm vi của các giá trị hợp lệ trong số lượng tuyết (P, S) và các loại dữ liệu kép lớn hơn. Lấy một giá trị từ Bông tuyết và lưu trữ nó trong một biến số JavaScript có thể dẫn đến mất độ chính xác. Ví dụ:

CREATE OR REPLACE FUNCTION num_test(a double)
  RETURNS string
  LANGUAGE JAVASCRIPT
AS
$$
  return A;
$$
;

select hash(1) AS a, 
       num_test(hash(1)) AS b, 
       a - b;
+----------------------+----------------------+------------+
|                    A | B                    |      A - B |
|----------------------+----------------------+------------|
| -4730168494964875235 | -4730168494964875000 | -235.00000 |
+----------------------+----------------------+------------+

Hai cột đầu tiên phải khớp và thứ ba phải chứa 0,0.

Vấn đề áp dụng cho các chức năng do người dùng JavaScript xác định (UDFS) và các thủ tục được lưu trữ.

Nếu bạn gặp vấn đề trong các quy trình được lưu trữ khi sử dụng

create or replace procedure read_result_set()
  returns float not null
  language javascript
  as     
  $$  
    var my_sql_command = "select * from table1";
    var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
    var result_set1 = statement1.execute();
    // Loop through the results, processing one row at a time... 
    while (result_set1.next())  {
       var column1 = result_set1.getColumnValue(1);
       var column2 = result_set1.getColumnValue(2);
       // Do something with the retrieved values...
       }
  return 0.0; // Replace with something more useful.
  $$
  ;

Sau đó, bạn có thể trả lại chuỗi từ quy trình được lưu trữ và chuyển chuỗi vào một loại dữ liệu số trong SQL.

Xử lý lỗi JavaScript

Vì một quy trình được lưu trữ được viết bằng JavaScript, nó có thể sử dụng cú pháp thử/bắt cú pháp thử JavaScript.

Quy trình được lưu trữ có thể ném một ngoại lệ được xác định trước hoặc ngoại lệ tùy chỉnh. Một ví dụ đơn giản về việc ném một ngoại lệ tùy chỉnh là ở đây.here.

Bạn có thể thực hiện các câu lệnh SQL của mình trong một khối thử. Nếu xảy ra lỗi, thì khối bắt của bạn có thể quay lại tất cả các câu lệnh (nếu bạn đặt các câu lệnh vào một giao dịch). Phần ví dụ chứa một ví dụ về việc quay lại một giao dịch trong một quy trình được lưu trữ.rolling back a transaction in a stored procedure.

Hạn chế đối với các thủ tục được lưu trữ Jo

Các thủ tục được lưu trữ có các hạn chế sau:

• Mã JavaScript không thể gọi hàm JavaScript

  create or replace procedure read_result_set()
    returns float not null
    language javascript
    as     
    $$  
      var my_sql_command = "select * from table1";
      var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
      var result_set1 = statement1.execute();
      // Loop through the results, processing one row at a time... 
      while (result_set1.next())  {
         var column1 = result_set1.getColumnValue(1);
         var column2 = result_set1.getColumnValue(2);
         // Do something with the retrieved values...
         }
    return 0.0; // Replace with something more useful.
    $$
    ;
  

  21.

• Các thủ tục lưu trữ JavaScript hỗ trợ truy cập vào thư viện JavaScript tiêu chuẩn. Lưu ý rằng điều này loại trừ nhiều đối tượng và phương thức thường được cung cấp bởi các trình duyệt. Không có cơ chế để nhập, bao gồm hoặc gọi các thư viện bổ sung. Cho phép các thư viện bên thứ 3 có thể tạo các lỗ hổng bảo mật.

• Mã JavaScript được thực thi trong một công cụ bị hạn chế, ngăn chặn các cuộc gọi hệ thống từ bối cảnh JavaScript (ví dụ: không truy cập mạng và đĩa) và ràng buộc các tài nguyên hệ thống có sẵn cho động cơ, cụ thể là bộ nhớ.

Độ nhạy của trường hợp trong các đối số JavaScript

Tên đối số không nhạy cảm trường hợp trong phần SQL của mã thủ tục được lưu trữ, nhưng nhạy cảm trường hợp trong phần JavaScript.

Đối với các quy trình được lưu trữ (và UDF) sử dụng JavaScript, số nhận dạng (như tên đối số) trong phần SQL của câu lệnh được chuyển đổi thành tự động chữ hoa Còn lại trong trường hợp ban đầu của họ. Điều này có thể khiến thủ tục được lưu trữ của bạn thất bại mà không trả lại thông báo lỗi rõ ràng vì các đối số không thấy.

Dưới đây là một ví dụ về quy trình được lưu trữ trong đó tên của một đối số trong mã JavaScript không khớp với tên của đối số trong mã SQL chỉ vì trường hợp sẽ khác nhau:

Trong ví dụ dưới đây, câu lệnh gán đầu tiên là không chính xác vì tên

create or replace procedure read_result_set()
  returns float not null
  language javascript
  as     
  $$  
    var my_sql_command = "select * from table1";
    var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
    var result_set1 = statement1.execute();
    // Loop through the results, processing one row at a time... 
    while (result_set1.next())  {
       var column1 = result_set1.getColumnValue(1);
       var column2 = result_set1.getColumnValue(2);
       // Do something with the retrieved values...
       }
  return 0.0; // Replace with something more useful.
  $$
  ;

CREATE PROCEDURE f(argument1 VARCHAR)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS
$$
var local_variable1 = argument1;  // Incorrect
var local_variable2 = ARGUMENT1;  // Correct
$$;

Sử dụng các định danh chữ hoa (đặc biệt là tên đối số) một cách nhất quán trên các câu lệnh SQL của bạn và mã JavaScript có xu hướng giảm các lỗi im lặng.

DELIMITERS JAVASCRIPT

Phần JavaScript của mã thủ tục được lưu trữ phải được đặt trong một trong hai trích dẫn

create or replace procedure read_result_set()
  returns float not null
  language javascript
  as     
  $$  
    var my_sql_command = "select * from table1";
    var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
    var result_set1 = statement1.execute();
    // Loop through the results, processing one row at a time... 
    while (result_set1.next())  {
       var column1 = result_set1.getColumnValue(1);
       var column2 = result_set1.getColumnValue(2);
       // Do something with the retrieved values...
       }
  return 0.0; // Replace with something more useful.
  $$
  ;

create or replace procedure read_result_set()
  returns float not null
  language javascript
  as     
  $$  
    var my_sql_command = "select * from table1";
    var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
    var result_set1 = statement1.execute();
    // Loop through the results, processing one row at a time... 
    while (result_set1.next())  {
       var column1 = result_set1.getColumnValue(1);
       var column2 = result_set1.getColumnValue(2);
       // Do something with the retrieved values...
       }
  return 0.0; // Replace with something more useful.
  $$
  ;

Sử dụng

create or replace procedure read_result_set()
  returns float not null
  language javascript
  as     
  $$  
    var my_sql_command = "select * from table1";
    var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
    var result_set1 = statement1.execute();
    // Loop through the results, processing one row at a time... 
    while (result_set1.next())  {
       var column1 = result_set1.getColumnValue(1);
       var column2 = result_set1.getColumnValue(2);
       // Do something with the retrieved values...
       }
  return 0.0; // Replace with something more useful.
  $$
  ;

Các biến ràng buộc

Liên kết một biến với câu lệnh SQL cho phép bạn sử dụng giá trị của biến trong câu lệnh.

Bạn có thể liên kết các giá trị null cũng như các giá trị không null.

Kiểu dữ liệu của biến phải phù hợp cho việc sử dụng giá trị trong câu lệnh SQL. Hiện tại, chỉ có thể bị ràng buộc các biến JavaScript của số, chuỗi và SFDATE. .SfDate can be bound. (For details about the mapping between SQL data types and JavaScript data types, see SQL and JavaScript Data Type Mapping.)

Dưới đây là một ví dụ ngắn về ràng buộc:

var stmt = snowflake.createStatement(
   {
   sqlText: "INSERT INTO table2 (col1, col2) VALUES (?, ?);",
   binds:["LiteralValue1", variable2]
   }
);

Đây là một ví dụ đầy đủ hơn. Ví dụ này liên kết thông tin dấu thời gian. Vì liên kết trực tiếp của dữ liệu dấu thời gian SQL không được hỗ trợ, ví dụ này vượt qua dấu thời gian như một varchar, sau đó liên kết điều đó với câu lệnh. Lưu ý rằng bản thân câu lệnh SQL đã chuyển đổi varchar thành dấu thời gian bằng cách gọi hàm to_timestamp ():

Hàm đơn giản này trả về đúng nếu dấu thời gian được chỉ định là trước bây giờ và sai.

CREATE OR REPLACE PROCEDURE right_bind(TIMESTAMP_VALUE VARCHAR)
RETURNS BOOLEAN
LANGUAGE JAVASCRIPT
AS
$$
var cmd = "SELECT CURRENT_DATE() > TO_TIMESTAMP(:1, 'YYYY-MM-DD HH24:MI:SS')";
var stmt = snowflake.createStatement(
          {
          sqlText: cmd,
          binds: [TIMESTAMP_VALUE]
          }
          );
var result1 = stmt.execute();
result1.next();
return result1.getColumnValue(1);
$$
;

create or replace procedure read_result_set()
  returns float not null
  language javascript
  as     
  $$  
    var my_sql_command = "select * from table1";
    var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
    var result_set1 = statement1.execute();
    // Loop through the results, processing one row at a time... 
    while (result_set1.next())  {
       var column1 = result_set1.getColumnValue(1);
       var column2 = result_set1.getColumnValue(2);
       // Do something with the retrieved values...
       }
  return 0.0; // Replace with something more useful.
  $$
  ;

0

Điều này cho thấy cách liên kết một varchar, dấu thời gian_ltz và các loại dữ liệu khác với câu lệnh

create or replace procedure read_result_set()
  returns float not null
  language javascript
  as     
  $$  
    var my_sql_command = "select * from table1";
    var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
    var result_set1 = statement1.execute();
    // Loop through the results, processing one row at a time... 
    while (result_set1.next())  {
       var column1 = result_set1.getColumnValue(1);
       var column2 = result_set1.getColumnValue(2);
       // Do something with the retrieved values...
       }
  return 0.0; // Replace with something more useful.
  $$
  ;

Tạo một bảng.

create or replace procedure read_result_set()
  returns float not null
  language javascript
  as     
  $$  
    var my_sql_command = "select * from table1";
    var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
    var result_set1 = statement1.execute();
    // Loop through the results, processing one row at a time... 
    while (result_set1.next())  {
       var column1 = result_set1.getColumnValue(1);
       var column2 = result_set1.getColumnValue(2);
       // Do something with the retrieved values...
       }
  return 0.0; // Replace with something more useful.
  $$
  ;

1

Tạo một thủ tục được lưu trữ. Quy trình này chấp nhận

create or replace procedure read_result_set()
  returns float not null
  language javascript
  as     
  $$  
    var my_sql_command = "select * from table1";
    var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
    var result_set1 = statement1.execute();
    // Loop through the results, processing one row at a time... 
    while (result_set1.next())  {
       var column1 = result_set1.getColumnValue(1);
       var column2 = result_set1.getColumnValue(2);
       // Do something with the retrieved values...
       }
  return 0.0; // Replace with something more useful.
  $$
  ;

27 và chuyển đổi varchar thành

create or replace procedure read_result_set()
  returns float not null
  language javascript
  as     
  $$  
    var my_sql_command = "select * from table1";
    var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
    var result_set1 = statement1.execute();
    // Loop through the results, processing one row at a time... 
    while (result_set1.next())  {
       var column1 = result_set1.getColumnValue(1);
       var column2 = result_set1.getColumnValue(2);
       // Do something with the retrieved values...
       }
  return 0.0; // Replace with something more useful.
  $$
  ;

28 bằng cách sử dụng SQL. Quy trình sau đó lấy lại giá trị được chuyển đổi từ một kết quả. Giá trị được lưu trữ trong một biến JavaScript của loại SFDATE. Quy trình được lưu trữ sau đó liên kết cả bản gốc

create or replace procedure read_result_set()
  returns float not null
  language javascript
  as     
  $$  
    var my_sql_command = "select * from table1";
    var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
    var result_set1 = statement1.execute();
    // Loop through the results, processing one row at a time... 
    while (result_set1.next())  {
       var column1 = result_set1.getColumnValue(1);
       var column2 = result_set1.getColumnValue(2);
       // Do something with the retrieved values...
       }
  return 0.0; // Replace with something more useful.
  $$
  ;

27 và

create or replace procedure read_result_set()
  returns float not null
  language javascript
  as     
  $$  
    var my_sql_command = "select * from table1";
    var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
    var result_set1 = statement1.execute();
    // Loop through the results, processing one row at a time... 
    while (result_set1.next())  {
       var column1 = result_set1.getColumnValue(1);
       var column2 = result_set1.getColumnValue(2);
       // Do something with the retrieved values...
       }
  return 0.0; // Replace with something more useful.
  $$
  ;

28 với câu lệnh

create or replace procedure read_result_set()
  returns float not null
  language javascript
  as     
  $$  
    var my_sql_command = "select * from table1";
    var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
    var result_set1 = statement1.execute();
    // Loop through the results, processing one row at a time... 
    while (result_set1.next())  {
       var column1 = result_set1.getColumnValue(1);
       var column2 = result_set1.getColumnValue(2);
       // Do something with the retrieved values...
       }
  return 0.0; // Replace with something more useful.
  $$
  ;

26. Điều này cũng cho thấy sự ràng buộc của dữ liệu số JavaScript.SfDate. The stored procedure then binds both the original

create or replace procedure read_result_set()
  returns float not null
  language javascript
  as     
  $$  
    var my_sql_command = "select * from table1";
    var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
    var result_set1 = statement1.execute();
    // Loop through the results, processing one row at a time... 
    while (result_set1.next())  {
       var column1 = result_set1.getColumnValue(1);
       var column2 = result_set1.getColumnValue(2);
       // Do something with the retrieved values...
       }
  return 0.0; // Replace with something more useful.
  $$
  ;

27 and the

create or replace procedure read_result_set()
  returns float not null
  language javascript
  as     
  $$  
    var my_sql_command = "select * from table1";
    var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
    var result_set1 = statement1.execute();
    // Loop through the results, processing one row at a time... 
    while (result_set1.next())  {
       var column1 = result_set1.getColumnValue(1);
       var column2 = result_set1.getColumnValue(2);
       // Do something with the retrieved values...
       }
  return 0.0; // Replace with something more useful.
  $$
  ;

28 to an

create or replace procedure read_result_set()
  returns float not null
  language javascript
  as     
  $$  
    var my_sql_command = "select * from table1";
    var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
    var result_set1 = statement1.execute();
    // Loop through the results, processing one row at a time... 
    while (result_set1.next())  {
       var column1 = result_set1.getColumnValue(1);
       var column2 = result_set1.getColumnValue(2);
       // Do something with the retrieved values...
       }
  return 0.0; // Replace with something more useful.
  $$
  ;

26 statement. This also demonstrates binding of JavaScript numeric data.

create or replace procedure read_result_set()
  returns float not null
  language javascript
  as     
  $$  
    var my_sql_command = "select * from table1";
    var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
    var result_set1 = statement1.execute();
    // Loop through the results, processing one row at a time... 
    while (result_set1.next())  {
       var column1 = result_set1.getColumnValue(1);
       var column2 = result_set1.getColumnValue(2);
       // Do something with the retrieved values...
       }
  return 0.0; // Replace with something more useful.
  $$
  ;

2

Gọi thủ tục.

create or replace procedure read_result_set()
  returns float not null
  language javascript
  as     
  $$  
    var my_sql_command = "select * from table1";
    var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
    var result_set1 = statement1.execute();
    // Loop through the results, processing one row at a time... 
    while (result_set1.next())  {
       var column1 = result_set1.getColumnValue(1);
       var column2 = result_set1.getColumnValue(2);
       // Do something with the retrieved values...
       }
  return 0.0; // Replace with something more useful.
  $$
  ;

3

Xác minh rằng hàng đã được chèn.

create or replace procedure read_result_set()
  returns float not null
  language javascript
  as     
  $$  
    var my_sql_command = "select * from table1";
    var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
    var result_set1 = statement1.execute();
    // Loop through the results, processing one row at a time... 
    while (result_set1.next())  {
       var column1 = result_set1.getColumnValue(1);
       var column2 = result_set1.getColumnValue(2);
       // Do something with the retrieved values...
       }
  return 0.0; // Replace with something more useful.
  $$
  ;

4

Để biết các ví dụ bổ sung về dữ liệu liên kết trong JavaScript, hãy xem các tham số câu lệnh Binding.Binding Statement Parameters.

Yêu cầu mã

Mã JavaScript phải xác định một đối tượng JavaScript theo nghĩa đen cho quy trình được lưu trữ có hiệu lực.

Nếu mã JavaScript không đáp ứng yêu cầu này, quy trình được lưu trữ sẽ được tạo; Tuy nhiên, nó sẽ thất bại khi được gọi.

Kích thước mã

Bông tuyết giới hạn kích thước tối đa của mã nguồn JavaScript trong phần thân của quy trình lưu trữ JavaScript. Bông tuyết khuyến nghị giới hạn kích thước đến 100 kb. (Mã được lưu trữ ở dạng nén và giới hạn chính xác phụ thuộc vào khả năng nén của mã.)

Lỗi thời gian chạy lor

Hầu hết các lỗi trong các thủ tục được lưu trữ hiển thị trong thời gian chạy vì mã JavaScript được giải thích tại thời điểm quy trình được lưu trữ chạy thay vì khi quy trình được lưu trữ được tạo.

Hỗ trợ cho SQL linh động

Các thủ tục được lưu trữ có thể được sử dụng để xây dựng các câu lệnh SQL tự động. Ví dụ: bạn có thể xây dựng một chuỗi lệnh SQL có chứa hỗn hợp SQL được cấu hình sẵn và đầu vào người dùng (ví dụ: số tài khoản người dùng).

Đối với các ví dụ, hãy xem động tạo ra một câu lệnh SQL và phần ví dụ.Dynamically Creating a SQL Statement and the Examples section.

API đồng bộ

API cho các quy trình lưu trữ của bông tuyết là đồng bộ. Trong một quy trình được lưu trữ, bạn chỉ có thể chạy một luồng tại một thời điểm.

Lưu ý rằng điều này khác với quy tắc cho thực thi javaScript với đầu nối Node.js, cho phép bạn chạy các luồng không đồng bộ.

Ví dụ;

Ví dụ cơ bản

Ví dụ sau đây cho thấy cú pháp cơ bản của việc tạo và gọi một quy trình được lưu trữ. Nó không thực hiện bất kỳ SQL hoặc mã thủ tục. Tuy nhiên, nó cung cấp một điểm khởi đầu cho các ví dụ thực tế hơn sau:

create or replace procedure read_result_set()
  returns float not null
  language javascript
  as     
  $$  
    var my_sql_command = "select * from table1";
    var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
    var result_set1 = statement1.execute();
    // Loop through the results, processing one row at a time... 
    while (result_set1.next())  {
       var column1 = result_set1.getColumnValue(1);
       var column2 = result_set1.getColumnValue(2);
       // Do something with the retrieved values...
       }
  return 0.0; // Replace with something more useful.
  $$
  ;

5

Lưu ý rằng dấu phân cách

create or replace procedure read_result_set()
  returns float not null
  language javascript
  as     
  $$  
    var my_sql_command = "select * from table1";
    var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
    var result_set1 = statement1.execute();
    // Loop through the results, processing one row at a time... 
    while (result_set1.next())  {
       var column1 = result_set1.getColumnValue(1);
       var column2 = result_set1.getColumnValue(2);
       // Do something with the retrieved values...
       }
  return 0.0; // Replace with something more useful.
  $$
  ;

24 đánh dấu sự khởi đầu và kết thúc của mã JavaScript.

Bây giờ hãy gọi thủ tục bạn vừa tạo:

create or replace procedure read_result_set()
  returns float not null
  language javascript
  as     
  $$  
    var my_sql_command = "select * from table1";
    var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
    var result_set1 = statement1.execute();
    // Loop through the results, processing one row at a time... 
    while (result_set1.next())  {
       var column1 = result_set1.getColumnValue(1);
       var column2 = result_set1.getColumnValue(2);
       // Do something with the retrieved values...
       }
  return 0.0; // Replace with something more useful.
  $$
  ;

6

Ví dụ sau đây minh họa cách thực thi câu lệnh SQL bên trong quy trình được lưu trữ:

1. Tạo một bảng:

   create or replace procedure read_result_set()
     returns float not null
     language javascript
     as     
     $$  
       var my_sql_command = "select * from table1";
       var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
       var result_set1 = statement1.execute();
       // Loop through the results, processing one row at a time... 
       while (result_set1.next())  {
          var column1 = result_set1.getColumnValue(1);
          var column2 = result_set1.getColumnValue(2);
          // Do something with the retrieved values...
          }
     return 0.0; // Replace with something more useful.
     $$
     ;
   

   7

2. Tạo một thủ tục được lưu trữ. Điều này chèn một hàng vào một bảng hiện có có tên

   create or replace procedure read_result_set()
     returns float not null
     language javascript
     as     
     $$  
       var my_sql_command = "select * from table1";
       var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
       var result_set1 = statement1.execute();
       // Loop through the results, processing one row at a time... 
       while (result_set1.next())  {
          var column1 = result_set1.getColumnValue(1);
          var column2 = result_set1.getColumnValue(2);
          // Do something with the retrieved values...
          }
     return 0.0; // Replace with something more useful.
     $$
     ;
   

   33 và trả về giá trị thành công. Giá trị trả về không đặc biệt hữu ích từ góc độ SQL, nhưng nó cho phép bạn trả về thông tin trạng thái (ví dụ: thành công.

   create or replace procedure read_result_set()
     returns float not null
     language javascript
     as     
     $$  
       var my_sql_command = "select * from table1";
       var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
       var result_set1 = statement1.execute();
       // Loop through the results, processing one row at a time... 
       while (result_set1.next())  {
          var column1 = result_set1.getColumnValue(1);
          var column2 = result_set1.getColumnValue(2);
          // Do something with the retrieved values...
          }
     return 0.0; // Replace with something more useful.
     $$
     ;
   

   8

3. Gọi thủ tục được lưu trữ:

   create or replace procedure read_result_set()
     returns float not null
     language javascript
     as     
     $$  
       var my_sql_command = "select * from table1";
       var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
       var result_set1 = statement1.execute();
       // Loop through the results, processing one row at a time... 
       while (result_set1.next())  {
          var column1 = result_set1.getColumnValue(1);
          var column2 = result_set1.getColumnValue(2);
          // Do something with the retrieved values...
          }
     return 0.0; // Replace with something more useful.
     $$
     ;
   

   9

4. Xác nhận rằng quy trình được lưu trữ đã chèn hàng:

   var sql_command = "SELECT * \
                          FROM table1;";
   

   0

Ví dụ sau lấy kết quả:

1. Tạo một quy trình để đếm số lượng hàng trong bảng (tương đương với

   create or replace procedure read_result_set()
     returns float not null
     language javascript
     as     
     $$  
       var my_sql_command = "select * from table1";
       var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
       var result_set1 = statement1.execute();
       // Loop through the results, processing one row at a time... 
       while (result_set1.next())  {
          var column1 = result_set1.getColumnValue(1);
          var column2 = result_set1.getColumnValue(2);
          // Do something with the retrieved values...
          }
     return 0.0; // Replace with something more useful.
     $$
     ;
   

   34):

   var sql_command = "SELECT * \
                          FROM table1;";
   

   1

2. Hỏi thủ tục được lưu trữ có bao nhiêu hàng trong bảng:

   var sql_command = "SELECT * \
                          FROM table1;";
   

   2

3. Kiểm tra độc lập rằng bạn có đúng số:

   var sql_command = "SELECT * \
                          FROM table1;";
   

   3

Ví dụ về thủ tục lưu trữ đệ quy Or

Ví dụ sau đây cho thấy một thủ tục được lưu trữ cơ bản, nhưng không đặc biệt thực tế, được lưu trữ:

var sql_command = "SELECT * \
                       FROM table1;";

4

var sql_command = "SELECT * \
                       FROM table1;";

5

var sql_command = "SELECT * \
                       FROM table1;";

6

var sql_command = "SELECT * \
                       FROM table1;";

7

Tự động tạo câu lệnh SQL

Ví dụ sau đây cho thấy cách tạo tự động câu lệnh SQL:

Ghi chú

Như đã nêu trong SQL Injection (trong chủ đề này), hãy cẩn thận để bảo vệ chống lại các cuộc tấn công khi sử dụng SQL động.SQL Injection (in this topic), be careful to guard against attacks when using dynamic SQL.

1. Tạo các thủ tục được lưu trữ. Quy trình này cho phép bạn vượt qua tên của một bảng và nhận số lượng hàng trong bảng đó (tương đương với

   create or replace procedure read_result_set()
     returns float not null
     language javascript
     as     
     $$  
       var my_sql_command = "select * from table1";
       var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
       var result_set1 = statement1.execute();
       // Loop through the results, processing one row at a time... 
       while (result_set1.next())  {
          var column1 = result_set1.getColumnValue(1);
          var column2 = result_set1.getColumnValue(2);
          // Do something with the retrieved values...
          }
     return 0.0; // Replace with something more useful.
     $$
     ;
   

   35):

   var sql_command = "SELECT * \
                          FROM table1;";
   

   8

2. Gọi thủ tục được lưu trữ:

   var sql_command = "SELECT * \
                          FROM table1;";
   

   2

3. Hiển thị kết quả từ

   create or replace procedure read_result_set()
     returns float not null
     language javascript
     as     
     $$  
       var my_sql_command = "select * from table1";
       var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
       var result_set1 = statement1.execute();
       // Loop through the results, processing one row at a time... 
       while (result_set1.next())  {
          var column1 = result_set1.getColumnValue(1);
          var column2 = result_set1.getColumnValue(2);
          // Do something with the retrieved values...
          }
     return 0.0; // Replace with something more useful.
     $$
     ;
   

   36 cho cùng một bảng:

   var sql_command = `SELECT *
                          FROM table1;`;
   

   0

Bắt lỗi bằng cách sử dụng thử/Catch¶

Ví dụ này thể hiện bằng cách sử dụng khối thử/bắt JavaScript để bắt lỗi trong quy trình được lưu trữ:

1. Tạo quy trình được lưu trữ:

   var sql_command = `SELECT *
                          FROM table1;`;
   

   1

2. Gọi thủ tục được lưu trữ. Điều này sẽ trả về một lỗi hiển thị số lỗi và thông tin khác:

   var sql_command = `SELECT *
                          FROM table1;`;
   

   2

Ví dụ sau đây cho thấy việc ném một ngoại lệ tùy chỉnh:

1. Tạo quy trình được lưu trữ:

   var sql_command = `SELECT *
                          FROM table1;`;
   

   3

2. Gọi thủ tục được lưu trữ. Điều này sẽ trả về một lỗi hiển thị số lỗi và thông tin khác:

   var sql_command = `SELECT *
                          FROM table1;`;
   

   4

Ví dụ sau đây cho thấy việc ném một ngoại lệ tùy chỉnh:

Gọi thủ tục được lưu trữ với các giá trị hợp lệ và không hợp lệ:

var sql_command = `SELECT *
                       FROM table1;`;

Sử dụng các giao dịch trong các thủ tục lưu trữ lor

Ví dụ sau đây bao gồm nhiều câu lệnh liên quan trong một giao dịch và sử dụng thử/bắt để cam kết hoặc quay lại. Tham số

create or replace procedure read_result_set()
  returns float not null
  language javascript
  as     
  $$  
    var my_sql_command = "select * from table1";
    var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
    var result_set1 = statement1.execute();
    // Loop through the results, processing one row at a time... 
    while (result_set1.next())  {
       var column1 = result_set1.getColumnValue(1);
       var column2 = result_set1.getColumnValue(2);
       // Do something with the retrieved values...
       }
  return 0.0; // Replace with something more useful.
  $$
  ;

var sql_command = `SELECT *
                       FROM table1;`;

6

var sql_command = `SELECT *
                       FROM table1;`;

7

var sql_command = `SELECT *
                       FROM table1;`;

8

Đăng nhập một lỗi

Ví dụ sau đây nhật ký lỗi vào bảng. Trong một ví dụ thực tế hơn, có lẽ bạn cũng sẽ bao gồm dấu thời gian của thời gian xảy ra lỗi (sử dụng hàm

create or replace procedure read_result_set()
  returns float not null
  language javascript
  as     
  $$  
    var my_sql_command = "select * from table1";
    var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
    var result_set1 = statement1.execute();
    // Loop through the results, processing one row at a time... 
    while (result_set1.next())  {
       var column1 = result_set1.getColumnValue(1);
       var column2 = result_set1.getColumnValue(2);
       // Do something with the retrieved values...
       }
  return 0.0; // Replace with something more useful.
  $$
  ;

Đăng nhập lỗi (phiên bản 2) ¶

Điều này cho thấy cách sử dụng một quy trình được lưu trữ để đăng nhập tin nhắn vào bảng tạm thời. Ví dụ này cho phép người gọi chỉ định tên bảng nhật ký và tạo bảng nếu nó không tồn tại. Ví dụ này cũng cho phép người gọi dễ dàng bật và tắt đăng nhập.

var sql_command = `SELECT *
                       FROM table1;`;

9

Cũng lưu ý rằng một trong những quy trình được lưu trữ này tạo ra một hàm JavaScript nhỏ mà nó có thể sử dụng lại. Trong các quy trình được lưu trữ dài với mã lặp đi lặp lại, việc tạo các hàm JavaScript bên trong quy trình được lưu trữ có thể thuận tiện.

var sql_command = "SELECT col1, col2"
sql_command += "     FROM table1"
sql_command += "     WHERE col1 >= 100"
sql_command += "     ORDER BY col2;"

0

Tạo các thủ tục:

var sql_command = "SELECT col1, col2"
sql_command += "     FROM table1"
sql_command += "     WHERE col1 >= 100"
sql_command += "     ORDER BY col2;"

1

Bật đăng nhập:

var sql_command = "SELECT col1, col2"
sql_command += "     FROM table1"
sql_command += "     WHERE col1 >= 100"
sql_command += "     ORDER BY col2;"

2

Gọi thủ tục:

Kiểm tra xem bảng có được tạo không và các tin nhắn đã được ghi lại:

Quan trọng

Chèn các thông báo nhật ký vào bảng riêng lẻ có thể tốn kém và không hiệu quả, đặc biệt nếu mã ghi lại một hoặc nhiều tin nhắn mỗi hàng được xử lý.

Hơn nữa, nếu nhiều quy trình được lưu trữ thực hiện đồng thời và nếu mỗi lần nối nhiều tin nhắn vào cùng một bảng nhật ký, các tắc nghẽn đồng thời có thể xảy ra.

Để tránh các vấn đề tiềm ẩn này, mã mẫu ở trên sẽ tích lũy các tin nhắn trong một chuỗi cho đến khi thủ tục được lưu trữ kết thúc (hoặc cho đến khi bị lỗi), sau đó ghi các tin nhắn được tích lũy trong một lần chèn.Naming Conventions for Stored Procedures (in this topic), you can overload stored procedure names. For example:

1. Quá tải tên thủ tục được lưu trữ tên

   var sql_command = "SELECT col1, col2"
   sql_command += "     FROM table1"
   sql_command += "     WHERE col1 >= 100"
   sql_command += "     ORDER BY col2;"
   

   3

   var sql_command = "SELECT col1, col2"
   sql_command += "     FROM table1"
   sql_command += "     WHERE col1 >= 100"
   sql_command += "     ORDER BY col2;"
   

   4

2. Như được mô tả trong các quy ước đặt tên cho các thủ tục được lưu trữ (trong chủ đề này), bạn có thể quá tải các tên thủ tục được lưu trữ. Ví dụ:

   Đầu tiên, tạo hai quy trình được lưu trữ có cùng tên, nhưng số lượng đối số khác nhau.

   var sql_command = "SELECT col1, col2"
   sql_command += "     FROM table1"
   sql_command += "     WHERE col1 >= 100"
   sql_command += "     ORDER BY col2;"
   

   6

Tiếp theo, hãy gọi hai thủ tục:

var sql_command = "SELECT col1, col2"
sql_command += "     FROM table1"
sql_command += "     WHERE col1 >= 100"
sql_command += "     ORDER BY col2;"

Ghi chú

Bạn cũng phải chỉ định các loại dữ liệu của các đối số cho một số hoạt động khác trên các quy trình được lưu trữ. Ví dụ, Grant và thu hồi yêu cầu các loại đối số, cũng như tên thủ tục được lưu trữ.

var sql_command = "SELECT col1, col2"
sql_command += "     FROM table1"
sql_command += "     WHERE col1 >= 100"
sql_command += "     ORDER BY col2;"

7

Ví dụ tiếp theo về quá tải cho thấy cách kết hợp quá tải và chuyển đổi loại tự động có thể giúp bạn dễ dàng có được kết quả bất ngờ:

var sql_command = "SELECT col1, col2"
sql_command += "     FROM table1"
sql_command += "     WHERE col1 >= 100"
sql_command += "     ORDER BY col2;"

8

Tạo một quy trình được lưu trữ có tham số float:

var sql_command = "SELECT col1, col2"
sql_command += "     FROM table1"
sql_command += "     WHERE col1 >= 100"
sql_command += "     ORDER BY col2;"

9

Gọi thủ tục được lưu trữ hai lần. Lần đầu tiên, vượt qua một chiếc phao. Lần thứ hai, vượt qua một varchar. Varchar được chuyển đổi thành một chiếc phao và đầu ra từ mỗi cuộc gọi là giống hệt nhau:

CREATE OR REPLACE FUNCTION num_test(a double)
  RETURNS string
  LANGUAGE JAVASCRIPT
AS
$$
  return A;
$$
;

0

Bây giờ, hãy tạo một quy trình được lưu trữ quá tải có tham số Varchar:

Bây giờ sử dụng chính xác các cuộc gọi giống như trước đây. Lưu ý sự khác biệt về đầu ra giữa hai cuộc gọi này và hai cuộc gọi trước đó.RESULT_SCAN function to retrieve the result from a CALL statement:

1. Sử dụng result_scan để lấy kết quả từ một thủ tục được lưu trữ

   CREATE OR REPLACE FUNCTION num_test(a double)
     RETURNS string
     LANGUAGE JAVASCRIPT
   AS
   $$
     return A;
   $$
   ;
   

   1

   CREATE OR REPLACE FUNCTION num_test(a double)
     RETURNS string
     LANGUAGE JAVASCRIPT
   AS
   $$
     return A;
   $$
   ;
   

   2

2. Ví dụ này cho thấy cách sử dụng chức năng result_scan để truy xuất kết quả từ câu lệnh gọi:

   CREATE OR REPLACE FUNCTION num_test(a double)
     RETURNS string
     LANGUAGE JAVASCRIPT
   AS
   $$
     return A;
   $$
   ;
   

   3

3. Tạo và tải bảng:

   CREATE OR REPLACE FUNCTION num_test(a double)
     RETURNS string
     LANGUAGE JAVASCRIPT
   AS
   $$
     return A;
   $$
   ;
   

   4

Tạo các thủ tục được lưu trữ. Quy trình này trả về một chuỗi được định dạng tốt trông giống như một tập hợp ba hàng, nhưng thực sự là một chuỗi duy nhất:

Gọi quy trình được lưu trữ, sau đó truy xuất kết quả bằng cách sử dụng result_scan:

1. Bạn có thể thực hiện các hoạt động phức tạp hơn trên giá trị được trả về bởi hàm result_scan. Trong trường hợp này, vì giá trị được trả về là một chuỗi duy nhất, bạn có thể muốn trích xuất các hàng riêng lẻ, có vẻ như được chứa trong chuỗi đó và lưu trữ các hàng đó trong một bảng khác.

   CREATE OR REPLACE FUNCTION num_test(a double)
     RETURNS string
     LANGUAGE JAVASCRIPT
   AS
   $$
     return A;
   $$
   ;
   

   5

2. Gọi quy trình được lưu trữ, sau đó truy xuất kết quả bằng cách sử dụng result_scan, sau đó trích xuất ba hàng từ chuỗi và đặt các hàng đó vào bảng:

   CREATE OR REPLACE FUNCTION num_test(a double)
     RETURNS string
     LANGUAGE JAVASCRIPT
   AS
   $$
     return A;
   $$
   ;
   

   6

3. Xác minh rằng điều này đã hoạt động bằng cách hiển thị các hàng trong bảng:

   CREATE OR REPLACE FUNCTION num_test(a double)
     RETURNS string
     LANGUAGE JAVASCRIPT
   AS
   $$
     return A;
   $$
   ;
   

   7

Ở đây, khoảng cùng một mã, nhưng trong các bước nhỏ hơn:

1. Tạo một bảng có tên

   create or replace procedure read_result_set()
     returns float not null
     language javascript
     as     
     $$  
       var my_sql_command = "select * from table1";
       var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
       var result_set1 = statement1.execute();
       // Loop through the results, processing one row at a time... 
       while (result_set1.next())  {
          var column1 = result_set1.getColumnValue(1);
          var column2 = result_set1.getColumnValue(2);
          // Do something with the retrieved values...
          }
     return 0.0; // Replace with something more useful.
     $$
     ;
   

   39. Bảng này tạm thời lưu trữ kết quả của lệnh cuộc gọi. Kết quả của cuộc gọi là một chuỗi duy nhất, vì vậy bảng này chỉ lưu trữ một giá trị varchar duy nhất.

   CREATE OR REPLACE FUNCTION num_test(a double)
     RETURNS string
     LANGUAGE JAVASCRIPT
   AS
   $$
     return A;
   $$
   ;
   

   8

2. Gọi quy trình được lưu trữ, sau đó truy xuất kết quả (một chuỗi) bằng cách sử dụng result_scan, sau đó lưu trữ nó vào bảng trung gian có tên

   create or replace procedure read_result_set()
     returns float not null
     language javascript
     as     
     $$  
       var my_sql_command = "select * from table1";
       var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
       var result_set1 = statement1.execute();
       // Loop through the results, processing one row at a time... 
       while (result_set1.next())  {
          var column1 = result_set1.getColumnValue(1);
          var column2 = result_set1.getColumnValue(2);
          // Do something with the retrieved values...
          }
     return 0.0; // Replace with something more useful.
     $$
     ;
   

   39:

   CREATE OR REPLACE FUNCTION num_test(a double)
     RETURNS string
     LANGUAGE JAVASCRIPT
   AS
   $$
     return A;
   $$
   ;
   

   9

   Điều này cho thấy hàng mới trong bảng

   create or replace procedure read_result_set()
     returns float not null
     language javascript
     as     
     $$  
       var my_sql_command = "select * from table1";
       var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
       var result_set1 = statement1.execute();
       // Loop through the results, processing one row at a time... 
       while (result_set1.next())  {
          var column1 = result_set1.getColumnValue(1);
          var column2 = result_set1.getColumnValue(2);
          // Do something with the retrieved values...
          }
     return 0.0; // Replace with something more useful.
     $$
     ;
   

   39. Hãy nhớ rằng mặc dù điều này được định dạng trông giống như ba hàng, nhưng nó thực sự là một chuỗi duy nhất:

   select hash(1) AS a, 
          num_test(hash(1)) AS b, 
          a - b;
   +----------------------+----------------------+------------+
   |                    A | B                    |      A - B |
   |----------------------+----------------------+------------|
   | -4730168494964875235 | -4730168494964875000 | -235.00000 |
   +----------------------+----------------------+------------+
   

   0

   Các lệnh sau đây cho thấy cách trích xuất nhiều hàng từ chuỗi:

   select hash(1) AS a, 
          num_test(hash(1)) AS b, 
          a - b;
   +----------------------+----------------------+------------+
   |                    A | B                    |      A - B |
   |----------------------+----------------------+------------|
   | -4730168494964875235 | -4730168494964875000 | -235.00000 |
   +----------------------+----------------------+------------+
   

   1

3. Tiếp theo, tạo một bảng có tên

   create or replace procedure read_result_set()
     returns float not null
     language javascript
     as     
     $$  
       var my_sql_command = "select * from table1";
       var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
       var result_set1 = statement1.execute();
       // Loop through the results, processing one row at a time... 
       while (result_set1.next())  {
          var column1 = result_set1.getColumnValue(1);
          var column2 = result_set1.getColumnValue(2);
          // Do something with the retrieved values...
          }
     return 0.0; // Replace with something more useful.
     $$
     ;
   

   42. Bảng này sẽ giữ kết quả sau khi bạn chia nó thành các dòng/chuỗi riêng lẻ:

   select hash(1) AS a, 
          num_test(hash(1)) AS b, 
          a - b;
   +----------------------+----------------------+------------+
   |                    A | B                    |      A - B |
   |----------------------+----------------------+------------|
   | -4730168494964875235 | -4730168494964875000 | -235.00000 |
   +----------------------+----------------------+------------+
   

   2

4. Bây giờ hãy chuyển đổi một chuỗi đó trong bảng

   create or replace procedure read_result_set()
     returns float not null
     language javascript
     as     
     $$  
       var my_sql_command = "select * from table1";
       var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
       var result_set1 = statement1.execute();
       // Loop through the results, processing one row at a time... 
       while (result_set1.next())  {
          var column1 = result_set1.getColumnValue(1);
          var column2 = result_set1.getColumnValue(2);
          // Do something with the retrieved values...
          }
     return 0.0; // Replace with something more useful.
     $$
     ;
   

   39 thành ba chuỗi riêng biệt và cho thấy bây giờ nó thực sự là ba chuỗi:

   select hash(1) AS a, 
          num_test(hash(1)) AS b, 
          a - b;
   +----------------------+----------------------+------------+
   |                    A | B                    |      A - B |
   |----------------------+----------------------+------------|
   | -4730168494964875235 | -4730168494964875000 | -235.00000 |
   +----------------------+----------------------+------------+
   

   3

5. Bây giờ chuyển đổi ba chuỗi thành ba hàng trong bảng dài hạn của chúng tôi có tên

   create or replace procedure read_result_set()
     returns float not null
     language javascript
     as     
     $$  
       var my_sql_command = "select * from table1";
       var statement1 = snowflake.createStatement( {sqlText: my_sql_command} );
       var result_set1 = statement1.execute();
       // Loop through the results, processing one row at a time... 
       while (result_set1.next())  {
          var column1 = result_set1.getColumnValue(1);
          var column2 = result_set1.getColumnValue(2);
          // Do something with the retrieved values...
          }
     return 0.0; // Replace with something more useful.
     $$
     ;
   

   44:

   select hash(1) AS a, 
          num_test(hash(1)) AS b, 
          a - b;
   +----------------------+----------------------+------------+
   |                    A | B                    |      A - B |
   |----------------------+----------------------+------------|
   | -4730168494964875235 | -4730168494964875000 | -235.00000 |
   +----------------------+----------------------+------------+
   

   4

6. Hiển thị ba hàng trong bảng dài hạn:

   select hash(1) AS a, 
          num_test(hash(1)) AS b, 
          a - b;
   +----------------------+----------------------+------------+
   |                    A | B                    |      A - B |
   |----------------------+----------------------+------------|
   | -4730168494964875235 | -4730168494964875000 | -235.00000 |
   +----------------------+----------------------+------------+
   

   5

Trả về một mảng các thông báo lỗi

Quy trình được lưu trữ của bạn có thể thực thi nhiều câu lệnh SQL và bạn có thể muốn trả về thông báo trạng thái/lỗi cho mỗi câu lệnh SQL. Tuy nhiên, một thủ tục được lưu trữ trả về một hàng duy nhất; Nó không được thiết kế để trả về nhiều hàng.

Nếu tất cả các tin nhắn của bạn phù hợp với một giá trị duy nhất của mảng, bạn có thể nhận tất cả các tin nhắn từ một quy trình được lưu trữ với một số nỗ lực bổ sung.

Ví dụ sau đây hiển thị một cách để thực hiện điều này (thông báo lỗi được hiển thị không có thật, nhưng bạn có thể mở rộng mã này để hoạt động với các câu lệnh SQL thực tế của mình):

select hash(1) AS a, 
       num_test(hash(1)) AS b, 
       a - b;
+----------------------+----------------------+------------+
|                    A | B                    |      A - B |
|----------------------+----------------------+------------|
| -4730168494964875235 | -4730168494964875000 | -235.00000 |
+----------------------+----------------------+------------+

6

select hash(1) AS a, 
       num_test(hash(1)) AS b, 
       a - b;
+----------------------+----------------------+------------+
|                    A | B                    |      A - B |
|----------------------+----------------------+------------|
| -4730168494964875235 | -4730168494964875000 | -235.00000 |
+----------------------+----------------------+------------+

7

Hãy nhớ rằng, đây không phải là một giải pháp mục đích chung. Có giới hạn về kích thước tối đa của các loại dữ liệu mảng và toàn bộ tập kết quả của bạn phải phù hợp với một mảng.not a general purpose solution. There is a limit on the maximum size of ARRAY data types, and your entire result set must fit into a single ARRAY.

Trả về một bộ kết quả

Phần này mở rộng ví dụ trước được mô tả để trả về một mảng các thông báo lỗi. Ví dụ này là chung chung hơn và cho phép bạn trả về một kết quả được đặt từ một truy vấn.

Một thủ tục được lưu trữ trả về một hàng duy nhất chứa một cột duy nhất; Nó không được thiết kế để trả về một bộ kết quả. Tuy nhiên, nếu tập kết quả của bạn đủ nhỏ để phù hợp với một giá trị của biến thể loại hoặc mảng, bạn có thể trả về kết quả được đặt từ quy trình được lưu trữ với một số mã bổ sung:

select hash(1) AS a, 
       num_test(hash(1)) AS b, 
       a - b;
+----------------------+----------------------+------------+
|                    A | B                    |      A - B |
|----------------------+----------------------+------------|
| -4730168494964875235 | -4730168494964875000 | -235.00000 |
+----------------------+----------------------+------------+

8

select hash(1) AS a, 
       num_test(hash(1)) AS b, 
       a - b;
+----------------------+----------------------+------------+
|                    A | B                    |      A - B |
|----------------------+----------------------+------------|
| -4730168494964875235 | -4730168494964875000 | -235.00000 |
+----------------------+----------------------+------------+

9

CREATE PROCEDURE f(argument1 VARCHAR)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS
$$
var local_variable1 = argument1;  // Incorrect
var local_variable2 = ARGUMENT1;  // Correct
$$;

0

Điều này cho thấy cách kết hợp hai dòng trước đó thành một dòng duy nhất:

CREATE PROCEDURE f(argument1 VARCHAR)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS
$$
var local_variable1 = argument1;  // Incorrect
var local_variable2 = ARGUMENT1;  // Correct
$$;

1

Để thuận tiện, bạn có thể bọc dòng trước trong một chế độ xem. Chế độ xem này cũng chuyển đổi chuỗi ‘null, thành một null thực sự. Bạn chỉ cần tạo chế độ xem một lần. Tuy nhiên, bạn phải gọi quy trình được lưu trữ ngay trước khi chọn từ chế độ xem này mỗi khi bạn sử dụng chế độ xem. Hãy nhớ rằng, cuộc gọi đến result_scan trong chế độ xem đang rút ra từ câu lệnh gần đây nhất, đó phải là cuộc gọi:must call the stored procedure immediately prior to selecting from this view every time you use the view. Remember, the call to RESULT_SCAN in the view is pulling from the most recent statement, which must be the CALL:

CREATE PROCEDURE f(argument1 VARCHAR)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS
$$
var local_variable1 = argument1;  // Incorrect
var local_variable2 = ARGUMENT1;  // Correct
$$;

2

CREATE PROCEDURE f(argument1 VARCHAR)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS
$$
var local_variable1 = argument1;  // Incorrect
var local_variable2 = ARGUMENT1;  // Correct
$$;

3

Bạn thậm chí có thể sử dụng nó như một chế độ xem thực (nghĩa là chọn một tập hợp con của nó):

CREATE PROCEDURE f(argument1 VARCHAR)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS
$$
var local_variable1 = argument1;  // Incorrect
var local_variable2 = ARGUMENT1;  // Correct
$$;

4