Sqline Online
Sqlines proporciona herramientas de código abierto para ayudarlo a migrar bases de datos en la nube, transferencia de datos, convertir esquema de base (DDL), vistas, procedimientos almacenados, paquetes, funciones, disparadores, consultas SQL scripts, incrustado de SQL y bases de datos del API entre base de datos.
La herramienta SQLines está disponible en las ediciones Online y Desktop:
Pros: La herramienta de migracion de sentencias que proporciona SQLines nos permite hacer cambios en distintas bases de datos con una compatibilidad y exactitud muy alta comparadas con otras herramientas online.
Contras: SQLines online no posee una API ni una linea de comandos para su uso y experimentacion de la sentencias, no posee una interfaz muy amigable ni actual(sencilla).
SQLines.com posee una alta gamma de compatibilidad con la mayoria de las bases de datos.
- Oracle
- MS SQL Server
- IBM DB2
- Sybase ASE
- Sybase SQL Anywhere
- Sybase Advantage
- Informix
- Hive
- MySQL
- MariaDB
- PostgreSQL
- Redshift
- Teradata
- Greenplum
- Netezza
Ejemplos
Les daremos muestras del uso de la herramienta de conversion de sentencias, mostraremos diferentes conversiones a distintos bases de datos, como Oracle MySql, MS Sql, entre otros.
Ejemplo 1
SQL
1. CREATE FUNCTION dbo.ISOweek (@DATE datetime)2. RETURNS int
3. WITH EXECUTE AS CALLER
4. AS
5. BEGIN
6. DECLARE @ISOweek int;
7. SET @ISOweek= DATEPART(wk,@DATE)+1
8. -DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104');
9. --Special cases: Jan 1-3 may belong to the previous year
10. IF (@ISOweek=0)
11. SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1
12. AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1;
13. --Special case: Dec 29-31 may belong to the next year
14. IF ((DATEPART(mm,@DATE)=12) AND
15. ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))
16. SET @ISOweek=1;
17.RETURN(@ISOweek);
18. END;
19. GO
20. SET DATEFIRST 1;
21. SELECT dbo.ISOweek(CONVERT(DATETIME,'12/26/2004',101)) AS 'ISO Week';
Oracle
1. CREATE OR REPLACE FUNCTION dbo.ISOweek (@DATE timestamp)2. RETURN number IS
3. BEGIN
4. WITH
5. END; EXECUTE IMMEDIATE AS CALLER
6. AS
7. BEGIN
8. @ISOweek number(10);
9. @ISOweek:= TO_NUMBER(TO_CHAR(@DATE, 'wk'))+1
10. -TO_NUMBER(TO_CHAR(CAST, 'wk'(TO_NUMBER(TO_CHAR(@DATE, 'yy')) as CHAR(4))+'0104'));
11. --Special cases: Jan 1-3 may belong to the previous year
12. IF (@ISOweek=0)
13. SET @ISOweek=dbo.ISOweek(CAST(TO_NUMBER(TO_CHAR(@DATE, 'yy'))-1
14. AS CHAR(4))+'12'+ CAST(24+TO_NUMBER(TO_CHAR(@DATE, 'DAY')) AS CHAR(2)))+1;
15. --Special case: Dec 29-31 may belong to the next year
16. IF ((TO_NUMBER(TO_CHAR(@DATE, 'mm'))=12) AND
17. ((TO_NUMBER(TO_CHAR(@DATE, 'dd'))-TO_NUMBER(TO_CHAR(@DATE, 'dw')))>= 28))
18. SET @ISOweek=1;
19. RETURN(@ISOweek);
20. END;
21. GO
22. SET DATEFIRST 1;
23. SELECT dbo.ISOweek(CONVERT(DATETIME,'12/26/2004',101)) AS 'ISO Week' FROM dual;
Ejemplo 2
SQL
1. IF OBJECT_ID ( 'Production.uspGetList', 'P' ) IS NOT NULL2. DROP PROCEDURE Production.uspGetList;
3. GO
4. CREATE PROCEDURE Production.uspGetList @Product varchar(40)
5. , @MaxPrice money
6. , @ComparePrice money OUTPUT
7. , @ListPrice money OUT
8. AS
9. SET NOCOUNT ON;
10. SELECT p.[Name] AS Product, p.ListPrice AS 'List Price'
11. FROM Production.Product AS p
12. JOIN Production.ProductSubcategory AS s
13. ON p.ProductSubcategoryID = s.ProductSubcategoryID
14. WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice;
15. -- Populate the output variable @ListPprice.
16. SET @ListPrice = (SELECT MAX(p.ListPrice)
17. FROM Production.Product AS p
18. JOIN Production.ProductSubcategory AS s
19. ON p.ProductSubcategoryID = s.ProductSubcategoryID
20. WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice);
21. -- Populate the output variable @compareprice.
22. SET @ComparePrice = @MaxPrice;
23. GO
MariaDB
1. IF OBJECT_ID ( 'Production.uspGetList', 'P' ) IS NOT NULL2. DROP PROCEDURE Production.uspGetList;
3. GO
4. DELIMITER //
5. 6. CREATE PROCEDURE Production.uspGetList ( @Product varchar(40)
7. , @MaxPrice decimal
8. , @ComparePrice decimal) OUTPUT
9. //
10. 11. DELIMITER ;
12. 13. 14. , @ListPrice money OUT
15.AS
16. SET NOCOUNT ON;
17. SELECT p.[Name] AS Product, p.ListPrice AS 18. 'List Price'
18. FROM Production.Product AS p
19. JOIN Production.ProductSubcategory AS s
20. ON p.ProductSubcategoryID = s.ProductSubcategoryID
21. WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice;
22. -- Populate the output variable @ListPprice.
23. SET @ListPrice = (SELECT MAX(p.ListPrice)
24. FROM Production.Product AS p
25. JOIN Production.ProductSubcategory AS s
26. ON p.ProductSubcategoryID = s.ProductSubcategoryID
27. WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice);
28. -- Populate the output variable @compareprice.
29. SET @ComparePrice = @MaxPrice;
30. GO
Ejemplo 3
SQL
1. CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INTEGER)2. RETURNS @retFindReports TABLE
3. (
4. EmployeeID int primary key NOT NULL,
5. FirstName nvarchar(255) NOT NULL,
6. LastName nvarchar(255) NOT NULL,
7. JobTitle nvarchar(50) NOT NULL,
8. RecursionLevel int NOT NULL
9. )
10. --Returns a result set that lists all the employees who report to the
11. --specific employee directly or indirectly.*/
12. AS
13. BEGIN
14. WITH EMP_cte(EmployeeID, OrganizationNode, FirstName, LastName, JobTitle, RecursionLevel) -- CTE name and columns
15. AS (
16. -- Get the initial list of Employees for Manager n
17. SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, 0
18. FROM HumanResources.Employee e
19. INNER JOIN Person.Person p
20. ON p.BusinessEntityID = e.BusinessEntityID
21. WHERE e.BusinessEntityID = @InEmpID
22. UNION ALL
23. -- Join recursive member to anchor
24. SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, RecursionLevel + 1
25. FROM HumanResources.Employee e
26. INNER JOIN EMP_cte
27. ON e.OrganizationNode.GetAncestor(1) = EMP_cte.OrganizationNode
28. INNER JOIN Person.Person p
29. ON p.BusinessEntityID = e.BusinessEntityID
30. )
31. -- copy the required columns to the result of the function
32. INSERT @retFindReports
33. SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel
34. FROM EMP_cte
35. RETURN
36. END;
37. GO
38. -- Example invocation
39. SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel
40. FROM dbo.ufn_FindReports(1);
41.
42. GO
PostgreSQL
1. CREATE OR REPLACE FUNCTION dbo.ufn_FindReports (@InEmpID INTEGER)2. RETURNS @retFindReports TABLE
3.
4. BEGIN TABLE
5. (
6. EmployeeID
7. $$ LANGUAGE plpgsql; int primary key NOT NULL,
8. FirstName nvarchar(255) NOT NULL,
9. LastName nvarchar(255) NOT NULL,
10. JobTitle nvarchar(50) NOT NULL,
11. RecursionLevel int NOT NULL
12. )
13. --Returns a result set that lists all the employees who report to the
14. --specific employee directly or indirectly.*/
15. AS
16. BEGIN
17. WITH EMP_cte(EmployeeID, OrganizationNode, FirstName, LastName, JobTitle, RecursionLevel) -- CTE name and columns
18. AS (
19. -- Get the initial list of Employees for Manager n
20. SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, 0
21. FROM HumanResources.Employee e
22. INNER JOIN Person.Person p
23. ON p.BusinessEntityID = e.BusinessEntityID
24. WHERE e.BusinessEntityID = @InEmpID
25. UNION ALL
26. -- Join recursive member to anchor
27. SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, RecursionLevel + 1
28. FROM HumanResources.Employee e
29. INNER JOIN EMP_cte
30. ON e.OrganizationNode.GetAncestor(1) = EMP_cte.OrganizationNode
31. INNER JOIN Person.Person p
32. ON p.BusinessEntityID = e.BusinessEntityID
33. )
34. -- copy the required columns to the result of the function
35. INSERT @retFindReports
36. SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel
37. FROM EMP_cte
38. RETURN
39. END;
40. GO
41. -- Example invocation
42. SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel
43. SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel
44.
45. GO
Si te ayudo esta informacion, compartela y reacciona!!
