Unlike actual programming language code, they: not portable (every db has its own version of PL/SQL. Sometimes different versions of the same database are incompatible – I’ve seen it!) not easily testable – you need a real (dev) database instance to test them and thus unit testing their code as part of a build is … Read more
In the simple case you can do: var user = cnn.Query<User>(“spGetUser”, new {Id = 1}, commandType: CommandType.StoredProcedure).First(); If you want something more fancy, you can do: var p = new DynamicParameters(); p.Add(“@a”, 11); p.Add(“@b”, dbType: DbType.Int32, direction: ParameterDirection.Output); p.Add(“@c”, dbType: DbType.Int32, direction: ParameterDirection.ReturnValue); cnn.Execute(“spMagicProc”, p, commandType: CommandType.StoredProcedure); int b = p.Get<int>(“@b”); int c = p.Get<int>(“@c”); … Read more
The most general difference between procedures and functions is that they are invoked differently and for different purposes: A procedure does not return a value. Instead, it is invoked with a CALL statement to perform an operation such as modifying a table or processing retrieved records. A function is invoked within an expression and returns … Read more
Generally speaking I always look for a set based approach (sometimes at the expense of changing the schema). However, this snippet does have its place.. — Declare & init (2008 syntax) DECLARE @CustomerID INT = 0 — Iterate over all customers WHILE (1 = 1) BEGIN — Get next customerId SELECT TOP 1 @CustomerID = … Read more
Escape the square brackets: … WHERE m.definition Like ‘%\[ABD\]%’ ESCAPE ‘\’ Then the square brackets will be treated as a string literals not as wild cards.
You can’t access PLSQL objects (cases 2 & 5 = package-level objects) from java, see “java – passing array in oracle stored procedure”. You can however access SQL types (case 1 and 4). To get OUT parameters from PL/SQL to java, you can use the method described in one of Tom Kyte’s thread using OracleCallableStatement. … Read more
Faster with hstore Since Postgres 9.0, with the additional module hstore installed in your database there is a very simple and fast solution with the #= operator that … replace[s] fields in record with matching values from hstore. To install the module: CREATE EXTENSION hstore; Examples: SELECT my_record #= ‘”field”=>”value”‘::hstore; — with string literal SELECT … Read more
I always use the return value to pass back error status. If you need to pass back one value I’d use an output parameter. sample stored procedure, with an OUTPUT parameter: CREATE PROCEDURE YourStoredProcedure ( @Param1 int ,@Param2 varchar(5) ,@Param3 datetime OUTPUT ) AS IF ISNULL(@Param1,0)>5 BEGIN SET @Param3=GETDATE() END ELSE BEGIN SET @Param3=’1/1/2010′ END … Read more
Specify the maxrecursion option at the end of the query: … from EmployeeTree option (maxrecursion 0) That allows you to specify how often the CTE can recurse before generating an error. Maxrecursion 0 allows infinite recursion.
Have you tried picking one of the resultsets? for result in cursor.stored_results(): people = result.fetchall() It could be that it’s allocating for multiple resultsets even though you only have one SELECT stmt. I know in PHP’s MySQLi stored procedures do this to allow for INOUT and OUT variable returns (which again, you have none of, … Read more