写给开发者的迁移指南：如何将 Oracle 与 SQL Server 代码转换为 PostgreSQL

Volatile类型表示那些即使在单一表扫描中也能够变更的函数值，因此无法做出进一步优化。一般来说，大部分数据库函数并不会选择volatile类型，例如ndom()、currval()以及timeofday()。只有那些具有副作用的函数（即使结果具有可预测性）才会被归类为volatile，例如setval()。如果在函数创建期间未提供volatility类型，则默认情况下所有新函数都将被标记为volatile。

Create Or Replace Function add_ten_v(num int) Returns integer AS $$
Begin
  Perform pg_sleep(0.01);
  Return num + 10;
End
$$ Language 'plpgsql' Volatile;

lab=>Explain Analyze Select add_ten_v(10)FROM generate_series(1,100,1); 
Query plan
-----------------------------------------------------------------------------
 Function Scan on generate_series  (cost=0.00..260.00 rows=1000 width=4) (actual time=10.200..1015.461 rows=100 loops=1)
 Planning time: 0.030 ms
 Execution time: 1015.501 ms
(3 rows)

Time: 1016.313 ms

Stable

Create Or Replace Function add_ten_s(num int) Returns integer AS $$
Begin
  Perform pg_sleep(0.01);
  Return num + 10;
End
$$ Language 'plpgsql' Stable;

lab=> Explain Analyze Select add_ten_s(10) From generate_series(1,100,1);
                                                       Query Plan
-------------------------------------------------------------------------------------------------------------------------
 Function Scan on generate_series  (cost=0.00..260.00 rows=1000 width=4) (actual time=10.153..1013.814 rows=100 loops=1)
 Planning time: 0.031 ms
 Execution time: 1013.846 ms
(3 rows)

Time: 1014.507 ms

Immutable类型表示该函数无法修改数据库，而且在给定相同的参数值时将始终返回相同的结果。这意味着其无法执行数据库查找，也无法使用参数列表中未直接存在的信息。如果选定此选项，对该函数的一切全常数参数调用都将被立即替换为该函数的值。

Create Or Replace Function add_ten_i(num int) Returns integer AS $$
Begin
  Perform pg_sleep(0.01);
  Return num + 10;
End

$$ Language 'plpgsql' Immutable;

lab=> Explain Analyze Select Add_Ten_I(10) From Generate_Series(1,100,1);
                                                     Query Plan
--------------------------------------------------------------------------------------------------------------------
 Function Scan on generate_series  (cost=0.00..10.00 rows=1000 width=4) (actual time=0.009..0.016 rows=100 loops=1)
 Planning time: 10.185 ms
 Execution time: 0.030 ms
(3 rows)
Time: 10.681 ms

以上所有函数都将返回以下值：

lab=> Select Add_Ten_V(10), Add_Ten_S(10), Add_Ten_I(10);
 add_ten_v | add_ten_s | add_ten_i
-----------+-----------+-----------
        20 |        20 |        20
(1 row)

尽管以上各函数都返回相同的值，但根据函数的不同，我们可能需要从中选择特定一种以获得更好的数据库性能。

Explain Analyze
Select Empid,
		Empname,
		Getdeptname(Deptid),
		Salary,
		Doj,
		Address
From Emp

Where Deptid=2

Explain Analyze 
Select Empid,
		Empname,
		Getdeptnames(Deptid),
		Salary,
		Doj,
		Address
From Emp

Where Deptid=2

Explain Analyze 
Select Empid,
		Empname,
		Deptname,
		Salary,
		Doj,
		Address
From Emp E
Join Dept D On D.Deptid = E.Deptid

Where E.Deptid=2

Create Or Replace Function empsal (eid int)
Returns Integer AS $total$
Declare
	Total Integer;
Begin
   Update Emp Set Salary = Salary * 0.20 Where Empid = Eid;
   Return 1;
End;
$$ Total Language Plpgsql;


Create Or Replace Function Empsalexcep (Eid Int)
Returns Integer AS $Total$
Declare
	Total Integer;
Begin
   Update Emp Set Salary = Salary * 0.20 Where Empid = Eid;
   RETURN 1;
   Exception
   	When Others Then
		Raise Notice 'Salary Update Failed ';
END;
$$ Total Language Plpgsql;


Select * From Empsal(3) 		– 	78ms	-- without exception handling
Select * From Empsalexcep(3)		-	84ms	-- with exception handling

Select Count(1) Into Count_Value
    From Tab1
    Where Tab1.A = Value 
Counter = 0
Open Dvscriptcursor For Select Id From Tab1;
While (Counter <     Count_Value)
Loop	
	Fetch Id Into Var_Id

	……..
…….
Counter = Counter +1;
End Loop

OPEN Dvscriptcursor For Select Id From Tab1;
Loop	
	Fetch Id Into Var_Id
	Exit When Not Found
	……..
…….
…….
End Loop

Create Or Replace Function Empsal (Eid Int)
Returns Integer As $Total$
Declare
	Total Integer;
Begin
  If (Select Count(*) From Emp Where Empid = Eid) > 0 Then  -- Wrong Usage
   		Update Emp Set Salary = Salary * 0.20 Where Empid = Eid  ;
	End If;
   Return 1;
End;
$Total$ Language plpgsql;

Create Or Replace Function Empsal (Eid Int)
Returns Integer AS $Total$
Declare
	Total Integer;
Begin
	If Exists (Select 1  From Emp Where Empid = Eid) Then.   – Right Usage
   		Update Emp Set Salary = Salary * 0.20 Where Empid = Eid  ;
	End If;
   RETURN 1;
END;
$$ Total Language plpgsql;

Create Or Replace Function Empsal (Eid Int)
Returns Integer AS $Total$
Declare
	Total Integer;
	Rows_Affected Int;
Begin
	If Exists (Select 1 From Emp Where Empid = Eid) Then 
   	   Update Emp Set Salary = Salary * 0.20 Where Empid = Eid  ;
	   Get Diagnostics Rows_Affected = ROW_COUNT;
	End If;
         RETURN 1;
END;

$$ Total Language plpgsql;

Select Rownum As Rownum_Value From Dual Connect By Level <= 64

With t(n) As (
  Select 1 from dual
  Union All
    Select n+1 From t Where n < 64
)
Select * From t;

Select Generate_Series(1,64) AS Rownum_Value

;With n(n) As
(
    Select 1
    Union All
    Select n+1 From n Where n < 64
)
Select n From n Order By n

Select b.id, b.title,  b.author, b.year_published, l.name language
From  books b, ibrary.languages l
Where  l.id (+)= b.language_id

Order By  b.id 

Select b.id, b.title,  b.author, b.year_published, l.name language
From  books b, ibrary.languages l
Where  l.id = b.language_id (+)
Order BY  b.id

Select b.id, b.title,  b.author, b.year_published, l.name language
From  books b, 
Left join ibrary.languages l 
On   l.id = b.language_id
Order BY  b.id 

Select b.id, b.title,  b.author, b.year_published, l.name language
From  books b, 
Right join ibrary.languages l
On   l.id = b.language_id 
Order By  b.id

Create Table emptable1
(
    empid integer,
    last_name varchar(100),
    first_name varchar(100),
    deptid integer,
    salary double precision
)

DECLARE 
 StructType	Varchar2(1000) Default '[{"empid" : 1, "last_name":"AccName1", "first_name":"AccName1", "deptid":"1", "salary":"1234.578"}
 					,{"empid" : "2", "last_name":"AccName2", "first_name":"AccName2", "deptid":"2", "salary":"4567.578"}
                    ]';
Begin
Insert Into emptable1 (empid,last_name,first_name,deptid,salary)
With Json As  
( Select StructType  --'[{"firstName": "Tobias", "lastName":"Jellema"},{"firstName": "Anna", "lastName":"Vink"} ]' doc  
  from   dual  
)  
Select empid,last_name,first_name,deptid,salary
From  json_table( (Select StructType from json) , '$[*]'  
                Columns ( empid PATH '$.empid'
                        ,last_name Path '$.last_name'  
                        , first_name Path '$.first_name'  
                        ,deptid Path '$.deptid'
                        ,salary Path '$.salary'
                        )  
               );
               End;

--Create Type structure

Create Type empTableType as Table
(
   empid integer,
    last_name varchar(100),
    first_name varchar(100),
    deptid integer,
    salary double precision
);

--Create Procedure

Create Procedure InsertEmpTable
@InsertEmpt_TVP empTableType READONLY
As
Insert Into emptable1(empid,last_name,first_name,deptid,salary)
Select * FROM @InsertEmpt_TVP;
Go

--Calling the SP with dynamic block and type

Declare @EmpTVP AS empTableType;
Insert Into @EmpTVP(empid,last_name,first_name,deptid,salary)
Values (1,'FirstName','Last_name',1,1234.566),
(2,'FirstName','Last_name',1,1234.566),
(3,'FirstName','Last_name',1,1234.566),
(4,'FirstName','Last_name',1,1234.566),
(5,'FirstName','Last_name',1,1234.566);
Exec InsertEmpTable @EmpTVP;

Go

Do $$
Declare 
 StructType	Text Default '[{"empid" : "1", "last_name":"AccName1", "first_name":"AccName1", "deptid":"1", "salary":"1234.578"},
 					{"empid" : "2", "last_name":"AccName2", "first_name":"AccName2", "deptid":"2", "salary":"4567.578"}]';

Begin
Insert Into emptable
Select * From json_to_recordset(StructType::json) 
	as x("empid" Int, "last_name" Varchar, "first_name" Varchar, "deptid" Int, "salary" Double Precision);

End $$

Create Table crosstabFunc (
  id            Number,
  customer_id   Number,
  product_code  Varchar2(5),
  quantity      Number
);

Insert Into crosstabFunc values (1, 1, 'A', 10);
Insert Into crosstabFunc Values (2, 1, 'B', 20);
Insert Into crosstabFunc Values (3, 1, 'C', 30);
Insert Into crosstabFunc Values (4, 2, 'A', 40);
Insert Into crosstabFunc Values (5, 2, 'C', 50);
Insert Into crosstabFunc Values (6, 3, 'A', 60);
Insert Into crosstabFunc Values (7, 3, 'B', 70);
Insert Into crosstabFunc Values (8, 3, 'C', 80);
Insert Into crosstabFunc Values (9, 3, 'D', 90);
Insert Into crosstabFunc Values (10, 4, 'A', 100);

Select *
From   (Select customer_id, product_code, quantity
        From   crosstabFunc)
Pivot  (Sum(quantity) As sum_quantity For (product_code) In ('A' AS a, 'B' AS b, 'C' AS c))

Order By customer_id;

Select * From   
(Select customer_id, product_code, quantity
        From   crosstabFunc) as cf
Pivot  (Sum(quantity) For product_code In (A,B,C))
as cf1

Order By customer_id

Create Extension tablefunc;

Select * From  Crosstab
(' Select customer_id, product_code, quantity
   From   crosstabFunc' )
   as T ( customer_id Int, "A" Int, "B" Int, "C" Int)

Create Table Students
(
	Id Int Primary Key Identity,
	Student_Name Varchar (50),
	Math_marks Int,
	English_marks Int,
	History_marks Int,
	Science_marks Int
)
Go
 
Insert Into Students Values ('Sally', 87, 56, 78, 91 )
Insert Into Students Values ('Edward', 69, 80, 92, 98)

Select StudentName, course,score
From   Students
Unpivot (score For course In (Math_marks AS 'Maths', English_marks AS 'English', History_marks AS 'History', Science_marks As 'Science'));

Create Procedure Spgetdept23
(P_Cur Out Sys_Refcursor, P_Cur12 Out Sys_Refcursor)
Is
Begin
Open P_Cur For
Select * From employees;


Open P_Cur12 For
Select * From dept;

End;


  var cur Refcursor
  var cur2 Refcursor
Exec Spgetdept23(:cur,:cur2);
Print cur;

Print cur2;

Create  Procedure Dbo.Multiple_Reseultset
As

Begin 
  
	Select * From HumanResources.Employee
	
	Select * From HumanResources.Department
	
End

To execute the procedure in SQL Server, enter the following code.

Exec Dbo.Multiple_Reseultset

Exec Dbo.Multiple_Reseultset

Create Or Replace Function Multiple_Reseultset() 
Returns Setof Refcursor As
$$
Declare
   cur1 Refcursor;
   cur2 Refcursor;
Begin 
   Open cur1 For
	Select * From HumanResources.employee;
	Return Next cur1;
	
	Open cur2 For
	Select * From HumanResources. Department;
	Return Next cur2;
End

$$ Language 'plpgsql';

Begin
Select * From Public.Multiple_Reseultset( )
Fetch All In "<unnamed portal 1>"
Fetch All In "<unnamed portal 2>"
End

Select a.col1, col2_fromSubquery  -- you can specify the columns directly from the subquery with out any prefix of subquery unless have common columns names.
from emplyee a,
  (select * from salary ) 

where  active=true

Select a.col1, b.col2_fromSubquery
from emplyee a,
  (select * from salary ) b

where  active=true

Create Extension postgres_fdw;

Create Server server_name1 Foreign Data Wrapper
postgres_fdw Options (host abcd.rds.amazonaws.com' dbname abcd, port '5432');

Create User Mapping For Current_User
Server server_name1
Options  (user 'pgar1234', password 'pgar1234'); 

Create Schema imported_public2 -- created local schema


Import Foreign Schema public From Server server_name1
Into imported_public2; -- This will import all the tables

Select * From imported_public2.emptable

dblink属于一项PostgreSQLcontrib扩展，可帮助用户在其他数据库中执行简短的即席查询。要使用dblink选项，大家必须以对其他用户可见的明文形式提供并保存密码。除非别无选择，否则我们不建议您使用这一选项。

Create Or Replace View emptable_dblink As
Select emptable.empid, emptable.last_name , emptable.first_name
   	From Dblink('host=abcd.rds.amazonaws.com user=abcd 
		 password=abcd dbname=abcd  port=5432',
		Select empid,last_name,first_name FROM emptable')
		AS emptable(empid Int,last_name Varchar , first_name Text );

Select * From emptable_dblink;

Select Dblink_Connect('conName','dbname=abcd user=abcd 
		password=abcd host= abcd.rds.amazonaws.com ');
	
Create Or Replace View mytabview1 As
Select mytable.* From
    	Dblink('conName', Select empid,last_name,first_name FROM emptable')
As mytable(empid Int,last_name Varchar , first_name Text);

Select * From mytabview1;

Select * From 
Dblink('host=abcd.rds.amazonaws.com user=abcd 
		 password=abcd dbname=postgres  port=5432',
		'Select public.add_ten(10)')
		As add_ten(a Int);

Select Dblink_Connect('conName','dbname=pgar1234 user=pgar1234 
		password=pgar1234 host=pgar1234.ctegx79rcs0q.ap-south-1.rds.amazonaws.com');

Select Dblink_Open('conName','foo2',
		'Select * From public.tabletypetest(10)');
	
Select * From Dblink_Fetch('conName','foo2', 5) As (empid Int, last_name Varchar);

Select Greatest(1,2,3,50,100)
-> 100

Select Least(1,2,3,50,100) 
-> 1

Update employee 
		Set salary = employee.salary + employee.salary * 0.10
	From Employee e
	Join dept d on d.deptid = e.deptid

	Where d.deptid=1 

Update Employee e
		Set salary = e.salary + e.salary * 0.10
	From dept d  
	Where d.deptid = e.deptid
 		And d.deptid=1 

Update Employee e
		Set Salary = e.salary + e.salary * 0.10
	Where Exists (Select 1 from dept d where d.deptid = e.deptid
 		And d.deptid=1 )