天道酬勤,学无止境

stored-procedures

How to unlock a row if i use FOR UPDATE clause

In procedure if we use FOR UPDATE clause, it will lock particular row and allow only one client to update whereas other client can only fetch data in the same row at that time. My question is when will it unlock the row, what should we do to unlock the row while writing procedure? Take this example here I'm using FOR UPDATE clause for client_count, when ll it unlock that particular row in this procedure. create or replace PROCEDURE newprocedur(inMerid IN VARCHAR2,outCount OUT NUMBER) AS CURSOR c1 IS select CLIENT_COUNT from OP_TMER_CONF_PARENT where MER_ID = inMerid FOR UPDATE OF CLIENT_COUNT

2021-09-24 15:56:31    分类:问答    stored-procedures   plsql   oracle10g   oracle11g   plsqldeveloper

如何知道在存储过程中插入查询是否成功?(How to know if insert query succeeded in stored proceedure?)

问题 我正在编写一个存储过程来创建一个播放器: CREATE PROCEDURE `create_player` ( firstName TEXT CHARACTER SET utf8, lastName TEXT CHARACTER SET utf8, username TEXT CHARACTER SET utf8, password TEXT CHARACTER SET utf8, email TEXT CHARACTER SET utf8, gender ENUM('m','f'), avatar INTEGER, OUT result INTEGER) BEGIN DECLARE insertVal INTEGER DEFAULT 0; INSERT INTO `Players` (`PlayerFirstName`, `PlayerLastName`, `PlayerName`, `PlayerPassword`, `PlayerEmail`, `PlayerGender`, `PlayerAvatar`, `PlayerJoinDate`) VALUES ( firstName,lastName,username,player_hash_password(password),email,gender,avatar,NOW()); END

2021-09-24 06:44:48    分类:技术分享    mysql   sql   stored-procedures

How to call stored procedures Mysql in SPRING DATA JPA

I read How to call mysql stored procedures in JPA call-stored-procedures-jpa but my procedure api no out put parament ! How to call it and return result in spring data jpa CREATE DEFINER=`tester`@`%` PROCEDURE `GET_TERMINAL_TRANSACTION_KEY` (in a_id_term integer unsigned) BEGIN declare v_trn_key bigint unsigned; set v_trn_key = null; start transaction; select seq from TERMINAL_TRANSACTION_KEY where id_term = a_id_term into v_trn_key for update; if v_trn_key is null then set v_trn_key = 1; insert into TERMINAL_TRANSACTION_KEY(id_term,seq) values(a_id_term,v_trn_key); else set v_trn_key = v_trn

2021-09-24 06:25:32    分类:问答    spring   stored-procedures   spring-data-jpa

Reversing a string using Teradata SPL

How can I reverse a string using Teradata Stored Procedure Language (SPL) ? The requirement was to replicate the SQL-Server replace() function. This could be achieved by writing a corresponding UDF in Teradata but I want to achieve the same using a Procedure. The procedure could be then used to initialize the variable before its use in the actual statement having reverse().

2021-09-24 05:43:48    分类:问答    sql   string   stored-procedures   reverse   teradata

how to write to dynamically created table in Redshift procedure

I need to write a procedure in Redshift that will write to a table, but the table name comes from the input string. Then I declare a variable that puts together the table name. CREATE OR REPLACE PROCEDURE my_schema.data_test(current "varchar") LANGUAGE plpgsql AS $$ declare new_table varchar(50) = 'new_tab' || '_' || current; BEGIN select 'somestring' as colname into new_table; commit; END; $$ This code runs but it doesn't create a new table, no errors. If I remove the declare statement then it works, creating a table called "new_table". It's just not using the declared variable name. It's

2021-09-24 04:58:43    分类:问答    stored-procedures   amazon-redshift

MySQL procedure to create or update a row in a table

I want to write a MySQL proc which takes userId and userName and does an insert or update into table users dependent on if a row exists where users.userId = userId I saw a similar question here: MySql create or update row with ip? But I want to do this in a stored procedure and wondered if that gives any additional tools?

2021-09-24 04:32:12    分类:问答    mysql   stored-procedures

在文本框中执行存储过程以获取本地化文本(Execute a stored procedure inside textbox to get a localized text)

问题 是否可以在文本框中执行存储过程? 我们需要这个来本地化我们的报告。 例如,我们有一个存储过程,它返回给定Key和给定LanguageId的本地化文本。 我想在我的报告中使用不同的键为每个标签(文本框)执行此存储过程。 我们正在使用 SSRS 2008。 回答1 我认为您有些混乱,您不能“在文本框中执行 sproc” 。 你可以做的是创建一个数据集,获取当前语言所需的所有键/值对,如下所示: EXEC usp_GetReportLabels 'en-US' /* Returns: Key Val --------- ------------ lbl1 Firstname lbl2 Surname etc etc */ 在您的文本框中,您可以使用利用查找函数的表达式从该数据集中检索正确的行,并显示标签值。 注意:您提到了 ssrs-2008 而不是 ssrs-2008-r2 版本,我认为普通 2008 中没有Lookup功能。 在这种情况下,您需要稍微重组您的数据集以获得相同的效果。 一种解决方案是对数据集进行PIVOT并将Key放入列中(在这种情况下,数据集将只包含一行,因此您可以执行First(Fields!lbl1.Value) )。 虽然有点解决方法。

2021-09-24 04:27:18    分类:技术分享    stored-procedures   reporting-services   localization   ssrs-2008   multilingual

访问和传递参数中的存储过程/参数化查询(Stored Procedure/Parameterized Query in Access And Pass Parameter)

问题 我在 Access 中创建了一个查询,如下所示... SELECT project_master.ProjectID, project_master.OracleNum, project_master.BudgetYear, project_master.ProjectNum, project_master.Description, project_master.Description, project_master.Location, project_master.Region, project_master.ContactOwner, project_master.ContactDesigner, project_master.ProjectPriority, project_master.StartDate, project_master.InitialCloseDate, project_master.CurrentBonus, project_master.CurrentQty, project_master.TotalQty, project_master.TotalQty, project_master.SpendingYTD, project_master.SpendingLTD, project_master.SpendingAnnualBudget

2021-09-24 04:03:52    分类:技术分享    .net   ms-access   stored-procedures   parameter-passing

无法使用我的存储过程(Trouble using my stored procedure)

问题 我有以下方法,它应该是我的应用程序的通用“保存到 SQL”方法。 protected void EjecutarGuardar(string ProcedimientoAlmacenado, object[] Parametros) { SqlConnection Connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString); SqlCommand Command = Connection.CreateCommand(); Command.CommandType = CommandType.StoredProcedure; foreach (object X in Parametros) { Command.Parameters.Add(X); } Connection.Open(); Command.ExecuteNonQuery(); Connection.Close(); Connection.Dispose(); } 我必须传递 StoredProcedure 的 NAME 和一个填充了参数的数组。 我有点迷失在这一点上。 我应该在哪里使用存储过程“ProcedimientoAlmacenado”的名称?

2021-09-24 03:47:09    分类:技术分享    c#   sql   sql-server   stored-procedures

在 LINQ-to-SQL 中运行映射的存储过程方法时生成奇怪的 system.void 返回异常(Strange system.void return exception generated when running a mapped stored procedure method in LINQ-to-SQL)

问题 我有一个 SQL Server 2008 存储过程(下面的伪代码),如下所示: CREATE PROCEDURE MyProc AS BEGIN CREATE DummyTable INSERT INTO DummyTable SELECT xxx INSERT INTO DummyTable SELECT yyy IF EXISTS FinalTable DROP FinalTable EXEC sp_RENAME 'DummyTable', 'FinalTable' END GO 请注意,此存储过程没有返回类型/值。 我在我的 LINQ-TO-SQL 设计器(ASP.NET MVC2 应用程序)中映射了这个存储过程。 但是,当我按如下方式调用该方法时: using (MyContext context = new MyContext()) { context.MyProc(); } 但是,在我的日志中,我发现运行此方法时正在生成此异常: System.InvalidOperationException: 'System.Void' 不是映射存储过程方法的有效返回类型。 在 System.Data.Linq.SqlClient.QueryConverter.TranslateStoredProcedureCall(MethodCallExpression mce

2021-09-24 01:59:57    分类:技术分享    asp.net   linq-to-sql   stored-procedures