sql,Code片段,代码实例,代码示例,代码片段库

sql 选择2。动态爱情，级联爱情

doc：https：//select2.github.io/select2/ <br/> https：//apex.oracle.com/pls/apex/f？p = 46011：30：104795389925364 :: NO ::: <br/> https://rimblas.com/blog/2016/01/using-select2-on-apex-tabular-forms/

var selectListShips = {
dropdownAutoWidth: true,
width: '200px',
//minimumInputLength: 1,
ajax: { url: 'wwv_flow.show',
        dataType: 'json',
        quietMillis: 500,
        data: function (term, page) {
            return {
                p_request      : 'APPLICATION_PROCESS=GET_SHIPS_SELECT2',
                p_flow_id      : $v('pFlowId'),
                p_flow_step_id : $v('pFlowStepId'),
                p_instance     : $v('pInstance'),
                x01            : $(this).parent().parent().find('select[name="f03"]').val()
            };
        },
        results: function (data, page) { 
            return { results: data.row };
        },
        cache: true 
    }, // end ajax call
initSelection: function(element, callback) {

  var id = element.val();
        if (id) {
            var data = {id: element.val(), text: shipDic[id]};
            callback(data);
        }
    }, // end init selection 

  placeholder: " ",
allowClear: true};

plsql

DECLARE
  l_cursor           sys_refcursor;
	l_param            VARCHAR2 (40)   := apex_application.g_x01; 
	l_row_id           VARCHAR2 (40)   := apex_application.g_x02;  
	l_selected_value	 varchar2 (200);
BEGIN

 
         
  open l_cursor for  select item_shtname "text"
													, item_code "id"
									     from lgms_lov_v
											where upper(lov_code) = 'SHIPS'
												and attribute3 = l_param -- 'PCL'
											order by 1; 
    
  apex_json.open_object;
  apex_json. write('results', l_cursor);
  apex_json.close_object;
END;

dynamic plsql

declare
l_need_comma boolean := false;
begin
-- Create "dictionary" arrays: 
--   deptDic for Departments
-- They are used for the populating the Select2 items
-- that already have codes and avoid an extra AJAX call for each one.
sys.htp.p('<script>');

sys.htp.prn('var shipDic={');

for d in (
	 select d.item_shtname  as description,
					d.item_code as code
		 from lgms_lov_v d
   	where upper(lov_code) = 'SHIPS'
		order by 1    
)
loop
	if l_need_comma then
		sys.htp.prn(',');
	else
		l_need_comma := true;
	end if;
	sys.htp.prn(d.code || ':"' || replace(d.description, '"','\"') || '"');
end loop;
sys.htp.p('};');

sys.htp.p('</script>');

end;

sql 如何解决Oracle“ORA-01652：无法在表空间X中用N扩展临时段”

foobar.sql

-- Identify data files (specifically, the file_name column)
SELECT * FROM dba_data_files;

ALTER TABLESPACE users ADD DATAFILE '<new data file full path>' SIZE 30G AUTOEXTEND ON NEXT 512K MAXSIZE 30G;

sql 获取SQL错误详细信息

SQL脚本添加usp_GetErrorInfo过程，该过程转储详细的错误信息。应该在TRY-CATCH块内使用。

SQL Error Details.sql

-- Verify that the stored procedure does not already exist.  
IF OBJECT_ID ( 'usp_GetErrorInfo', 'P' ) IS NOT NULL   
    DROP PROCEDURE usp_GetErrorInfo;  
GO  
  
-- Create procedure to retrieve error information.  
CREATE PROCEDURE usp_GetErrorInfo  
AS  
SELECT  
    ERROR_NUMBER() AS ErrorNumber  
    ,ERROR_SEVERITY() AS ErrorSeverity  
    ,ERROR_STATE() AS ErrorState  
    ,ERROR_PROCEDURE() AS ErrorProcedure  
    ,ERROR_LINE() AS ErrorLine  
    ,ERROR_MESSAGE() AS ErrorMessage;  
GO  
  
BEGIN TRY  
    -- Generate divide-by-zero error.  
    SELECT 1/0;  
END TRY  
BEGIN CATCH  
    -- Execute error retrieval routine.  
    EXECUTE usp_GetErrorInfo;  
END CATCH;

sql sql测试数据

sql_test_data.sql

-- test table
create table sales (name varchar(10), price_yen int);

insert into sales (name, price_yen) values('Orange', 100);
insert into sales (name, price_yen) values('Apple', 150);
insert into sales (name, price_yen) values('Lemon', 200);

-- データベースの種類によってCOMMIT方法が異なる。http://www.i2kt.com/dbms/sqlref/dcl/sql_01030301.html

sql sp_demo

sp_demo

DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `buscarUsuario`(in USERID VARCHAR(50))
BEGIN
    SELECT * 
    FROM `user`
    WHERE id = USERID;
END;
DELIMITER ;

call buscarUsuario("1");

validation_attendance

DROP PROCEDURE IF EXISTS `validation_attendance`;
DELIMITER //;

CREATE PROCEDURE `validation_attendance`(in CLASSID varchar(50), in EVENTSTATUSCT INT)
BEGIN
 IF (select 1 from th_class_control where date = '2019-09-03')
 THEN
	select 'SALIO TODO CORRECTO'
 END IF;	 
END;

DELIMITER ;

sql MS SQL String Escaping

MS SQL String Escaping

MS SQL String Escaping.sql

DECLARE @info NVARCHAR(100)='{"name":"John","skills":["C#","SQL"]}'
PRINT @info
-- OUTPUT
-- {"name":"John","skills":["C#","SQL"]}
 
 
SET @info=JSON_MODIFY(@info,'$.skills','["C#","T-SQL","Azure"]')
PRINT @info
-- OUTPUT
-- {"name":"John","skills":"[\"C#\",\"T-SQL\",\"Azure\"]"}
 
 
SET @info=JSON_MODIFY(@info,'$.skills',
            JSON_QUERY('["C#","T-SQL","Azure"]'))
PRINT @info
-- OUTPUT
-- {"name":"John","skills":["C#","T-SQL","Azure"]}

sql MS SQL JSON_MODIFY

MS SQL JSON_MODIFY

MS SQL JSON_MODIFY.sql

DECLARE @json NVARCHAR(MAX);
SET @json
    = N'{
    "info":
	{
	    "address":[
		    {"town":"Belgrade"},
			{"town":"Paris"},
			{"town":"Madrid"}],
		 "phone": []
	}
}';

-- GET JSON VALUE
SELECT JSON_VALUE(@json, 
            '$.info.address[0].town') AS ADDRESS;
-- Belgrade

-- MODIFY JSON VALUE
SET @json = JSON_MODIFY(@json,
              '$.info.address[1].town', 'London');
-- Output
-- {"town":"Paris"} -->  {"town":"London"}


-- SET NEW JSON ARRAY
DECLARE @data NVARCHAR(MAX) = N'[{"p1":1,"p2": 2 }]';
SET @json = JSON_MODIFY(@json,
               '$.info.phone', JSON_QUERY(@data));
-- Output
-- "phone": []  --> "phone": [{"p1":1,"p2": 2 }]

sql MS SQL OPENJSON

MS SQL OPENJSON

MS SQL OPENJSON.sql

-- Json Collection to Row Set (TYPE 1)
DECLARE @json NVARCHAR(MAX);
SET @json
    = N'[  
    { 
	  "id" : 6,
	  "info": { "name": "John", "surname": "Doe" }, 
	  "age": 25 
	},  
    { 
	  "id" : 25,
	  "info": { "name": "Jane", "surname": "Doe" }, 
	  "dob": "2005-11-04T12:00:00" 
	 }  
 ]';

SELECT *
FROM
    OPENJSON(@json)
    WITH
    (
        id INT 'strict $.id',
        fName NVARCHAR(50) '$.info.name',
        lName NVARCHAR(50) '$.info.surname',
        age INT,
        dob DATETIME2 '$.dob'
    );


-- OUTPUT
-- id | fName | lName | age  | dob
-- 6  | John  | Doe   | 25   | NULL
-- 5  | Jane  | Smith | NULL | 2005-11-04 12:00:00.0000000


-- Json Collection to Row Set (TYPE 2)
DECLARE @json NVARCHAR(MAX);
SET @json
    = N'[  
    { 
	  "id" : 23,
	  "info": { "name": "John", "surname": "Doe" },
	  "age": 25, 
	  "Phones": ["123","345","678"] 
	},
    {
	  "id" : 33,
	  "info": { "name": "Jane", "surname": "Doe" },
	  "dob": "2005-11-04T12:00:00", 
	  "Phones": ["321","543"] 
	}
]';

SELECT id, [name], [surname], age, dob, number
FROM
(
    SELECT *
    FROM
        OPENJSON(@json)
        WITH
        (
            id INT 'strict $.id',
            [name] NVARCHAR(50) '$.info.name',
            [surname] NVARCHAR(50) '$.info.surname',
            age INT,
            dob DATETIME2 '$.dob',
            Phones NVARCHAR(MAX) AS JSON
        )
) AS people
    OUTER APPLY
    OPENJSON(Phones)
    WITH
    (
        number NVARCHAR(50) '$'
    );

-- OUTPUT
--  id | name | surname | age |    dob |                       number
--  2  	 John   Smith     25	   NULL                       	123
--  2	 John	Smith	  25	   NULL	                        345
--  2	 John	Smith	  25	   NULL	                        678
--  3	 Jane	Smith	  NULL   2005-11-04 12:00:00.0000000	321
--  3	 Jane	Smith	  NULL   2005-11-04 12:00:00.0000000	543

sql MS SQL JSON_VALUE

MS SQL JSON_VALUE

MS SQL JSON_VALUE.sql


DECLARE @browsers NVARCHAR(1000)
    = N'
{ "Browsers": [
    { 
      "Name": "Firefox", 
      "Info": { "Version": "1.0.1", "Category": "Major" },
      "LogDate": 2003, "AddedOn": "2004-11-09" \
    },
    {
      "Name": "Opera", 
      "Info": { "Version": "1.0.5", "Category": "Dev" },
      "LogDate": 2004, "AddedOn": "2008-11-09" 
    }] 
}';

-- Name of the first item
SELECT JSON_VALUE(@browsers, '$.Browsers[0].Name');
-- Output: Firefox

-- Return the Added On Date of the first browser in our array 
-- and convert it into a DateTime2 datatype
SELECT CAST(JSON_VALUE(@browsers,
              '$.Browsers[0].AddedOn') AS DATETIME2);
-- Output: 2004-11-09 00:00:00.0000000

-- This returns NULL because the 'Browsers' is 
-- an array instead of a simple object
SELECT JSON_VALUE(@browsers, '$.Browsers');
-- Output: NULL

-- This is also invalid because JSON_VALUE cannot return
-- an array...only scalar values allowed!
SELECT JSON_VALUE(@browsers, '$.Browsers[1].Info');
-- Output: NULL

-- Much better
SELECT JSON_VALUE(@browsers, '$.Browsers[1].Info.Category');
-- Output: Dev

sql MS SQL IS JSON

MS SQL IS JSON

MS SQL IS JSON.sql

-- Returns 1, valid
SELECT ISJSON('{ "UserName" : "John" }') 
-- Output: 1
 
-- Returns 0, invalid, missing quotes
SELECT ISJSON('{ "UserName" : John }')
-- Output: 0
 
-- Returns 1, valid, numbers are allowed
SELECT ISJSON('{ "Number" : 1 }')
-- Output: 1
 
-- Returns 1, valid, dates are just strings 
-- in ISO 8601 date format 
-- https://en.wikipedia.org/wiki/ISO_8601
SELECT ISJSON('{ "AddedOn" : "20185-05-22T00:00:00.000Z" }') 
-- Output: 1
 
-- Returns 0, invalid
SELECT ISJSON('{ "AddedOn" : 2015-08-18 }') 
-- Output: 0